apps/backend/src/infrastructure/database/migrations/0024_sprint_l_costing.sqlSQL
-- Sprint L — costing configuration, projections, recalculation jobs and alerts.
create table if not exists costing_configurations (
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,
method text not null default 'weighted_average',
currency_code char(3) not null default 'EUR',
include_waste_factor boolean not null default true,
target_food_cost_percent numeric(9,4),
minimum_gross_margin_percent numeric(9,4),
is_active boolean not null default true,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint costing_method_check check(
method in ('weighted_average','last_purchase_price','standard_cost')
)
);
create unique index if not exists idx_costing_config_location
on costing_configurations(organization_id, location_id)
where is_active=true;
create table if not exists stock_item_standard_costs (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
stock_item_id uuid not null references stock_items(id) on delete cascade,
cost_per_normalized_unit_minor numeric(24,8) not null,
currency_code char(3) not null,
reason text not null,
created_by uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
is_active boolean not null default true
);
create unique index if not exists idx_standard_cost_active
on stock_item_standard_costs(location_id, stock_item_id)
where is_active=true;
create table if not exists stock_item_cost_projections (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
stock_item_id uuid not null references stock_items(id) on delete cascade,
cost_per_normalized_unit_minor numeric(24,8),
currency_code char(3) not null,
cost_method text not null,
quality_state text not null,
source_timestamp timestamptz,
calculated_at timestamptz not null default now(),
stale_at timestamptz,
projection_version bigint not null default 1,
primary key(location_id, stock_item_id),
constraint stock_cost_quality_check check(
quality_state in ('verified','estimated','stale','insufficient_data')
)
);
create table if not exists ingredient_cost_projections (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
ingredient_id uuid not null references ingredients(id) on delete cascade,
source_stock_item_id uuid references stock_items(id) on delete set null,
cost_per_base_unit_minor numeric(24,8),
currency_code char(3) not null,
cost_method text 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, ingredient_id),
constraint ingredient_cost_quality_check check(
quality_state in ('verified','estimated','stale','insufficient_data')
)
);
create table if not exists recipe_cost_projections (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
recipe_id uuid not null references recipes(id) on delete cascade,
recipe_version_id uuid not null references recipe_versions(id) on delete cascade,
total_cost_minor numeric(24,8),
cost_per_yield_unit_minor numeric(24,8),
currency_code char(3) not null,
cost_method text not null,
quality_state text not null,
breakdown jsonb not null default '[]'::jsonb,
calculated_at timestamptz not null default now(),
stale_at timestamptz,
projection_version bigint not null default 1,
primary key(location_id, recipe_version_id),
constraint recipe_cost_quality_check check(
quality_state in ('verified','estimated','stale','insufficient_data')
)
);
create index if not exists idx_recipe_cost_recipe
on recipe_cost_projections(location_id, recipe_id);
create table if not exists product_margin_projections (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
product_id uuid not null references products(id) on delete cascade,
channel text not null default 'pos',
effective_price_minor bigint,
net_price_minor numeric(24,8),
recipe_cost_minor numeric(24,8),
modifier_baseline_cost_minor numeric(24,8) not null default 0,
gross_margin_minor numeric(24,8),
gross_margin_percent numeric(12,6),
food_cost_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, product_id, channel),
constraint product_margin_quality_check check(
quality_state in ('verified','estimated','stale','insufficient_data')
)
);
create table if not exists cost_projection_history (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
entity_type text not null,
entity_id uuid not null,
metric text not null,
value numeric(24,8),
currency_code char(3),
cost_method text,
quality_state text,
reason text,
recorded_at timestamptz not null default now()
);
create index if not exists idx_cost_history_entity_time
on cost_projection_history(location_id, entity_type, entity_id, recorded_at desc);
create table if not exists cost_recalculation_jobs (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
scope_type text not null,
scope_id uuid,
reason text not null,
status text not null default 'pending',
attempts integer not null default 0,
error text,
created_at timestamptz not null default now(),
started_at timestamptz,
completed_at timestamptz,
constraint cost_job_status_check check(status in ('pending','running','completed','failed'))
);
create index if not exists idx_cost_jobs_status_created
on cost_recalculation_jobs(status, created_at);
create table if not exists cost_alerts (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
entity_type text not null,
entity_id uuid not null,
alert_type text not null,
severity text not null,
old_value numeric(24,8),
new_value numeric(24,8),
currency_code char(3),
reason text not null,
status text not null default 'open',
calculated_at timestamptz not null default now(),
resolved_at timestamptz,
constraint cost_alert_severity_check check(severity in ('info','warning','critical')),
constraint cost_alert_status_check check(status in ('open','resolved','dismissed'))
);
create index if not exists idx_cost_alerts_location_status
on cost_alerts(location_id, status, calculated_at desc);
insert into permissions(permission_key, description) values
('costing.read', 'Read recipe costs and product margins'),
('costing.manage', 'Manage costing configuration and standard cost'),
('costing.recalculate', 'Trigger costing recalculation'),
('reports.financial.read', 'Read financial management reporting')
on conflict(permission_key) do nothing;