apps/backend/src/infrastructure/database/migrations/0025_sprint_l_completion.sqlSQL
-- Sprint L completion — waste factors, modifier costing, tax/net price rules and impact projections.
alter table recipes
add column if not exists waste_factor_percent numeric(9,4) not null default 0;
alter table product_margin_projections
add column if not exists tax_rate_basis_points integer,
add column if not exists tax_inclusive_price boolean not null default true;
create table if not exists modifier_cost_projections (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
modifier_id uuid not null references modifiers(id) on delete cascade,
recipe_id uuid references recipes(id) on delete set null,
cost_delta_minor numeric(24,8),
price_delta_minor bigint,
margin_delta_minor numeric(24,8),
margin_percent numeric(12,6),
currency_code char(3) not null,
quality_state text not null,
calculated_at timestamptz not null default now(),
stale_at timestamptz,
projection_version bigint not null default 1,
primary key(location_id, modifier_id),
constraint modifier_cost_quality_check check(
quality_state in ('verified','estimated','stale','insufficient_data')
)
);
create table if not exists supplier_cost_impact_projections (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
supplier_id uuid not null references suppliers(id) on delete cascade,
stock_item_id uuid not null references stock_items(id) on delete cascade,
old_cost_per_unit_minor numeric(24,8),
new_cost_per_unit_minor numeric(24,8),
affected_recipe_count integer not null default 0,
affected_product_count integer not null default 0,
average_margin_change_percent numeric(12,6),
calculated_at timestamptz not null default now(),
primary key(location_id, supplier_id, stock_item_id)
);
create table if not exists tax_profiles (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid references locations(id) on delete cascade,
code text not null,
display_name text not null,
rate_basis_points integer not null,
is_active boolean not null default true,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
constraint tax_rate_valid check(rate_basis_points >= 0 and rate_basis_points <= 10000)
);
create unique index if not exists idx_tax_profile_scope_code
on tax_profiles(organization_id,coalesce(location_id,'00000000-0000-0000-0000-000000000000'::uuid),code)
where is_active=true;
alter table products
add column if not exists tax_profile_id uuid references tax_profiles(id) on delete set null;
insert into permissions(permission_key, description) values
('costing.supplier_impact.read', 'Read supplier cost impact analysis'),
('costing.modifiers.read', 'Read modifier profitability')
on conflict(permission_key) do nothing;