apps/backend/src/infrastructure/database/migrations/0052_inventory_trust_budget.sqlSQL
-- Owner-controlled "trust budget" for unitemized operational inventory consumption.
-- This never changes sales/payment/fiscal auditability. It only creates append-only stock movements.
alter table waste_records
drop constraint if exists waste_reason_check;
alter table waste_records
add constraint waste_reason_check check(
reason in (
'expired','damaged','prep','incorrect_order',
'guest_return','overproduction','spill','breakage',
'staff_consumption','complimentary','garnish',
'trust_budget','other'
)
);
alter table stock_movements
drop constraint if exists stock_movement_type_check;
alter table stock_movements
add constraint stock_movement_type_check check(
movement_type in (
'purchase_receipt','sale_consumption','production',
'transfer_in','transfer_out','waste','return',
'manual_adjustment','inventory_count_adjustment',
'breakage','staff_meal','trust_consumption'
)
);
create table if not exists inventory_trust_budget_policies (
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,
enabled boolean not null default false,
period text not null default 'week',
budget_minor bigint not null default 0,
currency_code char(3) not null default 'EUR',
over_limit_behavior text not null default 'require_reason',
allowed_reason_keys text[] not null default array[
'garnish','guest_return','spill','prep','staff_consumption',
'complimentary','overproduction','other'
]::text[],
created_by_user_id uuid references user_accounts(id) on delete set null,
updated_by_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint inventory_trust_budget_period_check
check(period in ('day','week','month')),
constraint inventory_trust_budget_amount_check
check(budget_minor>=0),
constraint inventory_trust_budget_over_limit_check
check(over_limit_behavior in ('notify_only','require_reason','manager_approval'))
);
create unique index if not exists idx_inventory_trust_budget_policy_location
on inventory_trust_budget_policies(organization_id,location_id);
create table if not exists inventory_trust_consumptions (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
policy_id uuid not null references inventory_trust_budget_policies(id) on delete restrict,
stock_item_id uuid not null references stock_items(id) on delete restrict,
storage_location_id uuid not null references storage_locations(id) on delete restrict,
quantity numeric(24,8) not null,
unit_code text not null references units(code) on delete restrict,
normalized_quantity numeric(24,8) not null,
normalized_unit_code text not null references units(code) on delete restrict,
estimated_cost_minor numeric(24,8) not null,
currency_code char(3) not null,
reason_key text not null default 'other',
detail_required boolean not null default false,
detail_note text,
recorded_by_employee_id uuid references employees(id) on delete set null,
business_date date not null,
recorded_at timestamptz not null default now(),
constraint inventory_trust_consumption_qty_check check(quantity>0 and normalized_quantity>0),
constraint inventory_trust_consumption_cost_check check(estimated_cost_minor>=0)
);
create index if not exists idx_inventory_trust_consumptions_period
on inventory_trust_consumptions(location_id,business_date,recorded_at);
create index if not exists idx_inventory_trust_consumptions_stock
on inventory_trust_consumptions(location_id,stock_item_id,recorded_at);
create table if not exists inventory_trust_budget_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,
policy_id uuid not null references inventory_trust_budget_policies(id) on delete cascade,
period_start date not null,
period_end date not null,
budget_minor bigint not null,
consumed_minor numeric(24,8) not null,
alert_type text not null,
acknowledged_at timestamptz,
acknowledged_by_user_id uuid references user_accounts(id) on delete set null,
created_at timestamptz not null default now(),
constraint inventory_trust_budget_alert_type_check
check(alert_type in ('approaching_limit','limit_exceeded'))
);
create unique index if not exists idx_inventory_trust_budget_alert_once
on inventory_trust_budget_alerts(policy_id,period_start,alert_type)
where acknowledged_at is null;
insert into permissions(permission_key,description) values
('inventory.trust_budget.use','Book unitemized operational stock consumption within the owner trust budget'),
('inventory.trust_budget.read','Read inventory trust budget usage'),
('inventory.trust_budget.manage','Owner-only configuration of inventory trust budget')
on conflict(permission_key) do nothing;
-- Owner template gets all three permissions. Managers/staff are intentionally not granted manage.
insert into role_permissions(role_id,permission_key)
select r.id,p.permission_key
from roles r
cross join (
values
('inventory.trust_budget.use'),
('inventory.trust_budget.read'),
('inventory.trust_budget.manage')
) as p(permission_key)
where r.organization_id is null and r.role_key='platform_org_owner'
on conflict do nothing;
-- Manager may use/read the owner-configured budget but cannot configure it.
insert into role_permissions(role_id,permission_key)
select r.id,p.permission_key
from roles r
cross join (
values
('inventory.trust_budget.use'),
('inventory.trust_budget.read')
) as p(permission_key)
where r.organization_id is null and r.role_key='platform_manager'
on conflict do nothing;
-- Staff may use it if their assigned role template/organization permission model includes the system staff role.
insert into role_permissions(role_id,permission_key)
select r.id,'inventory.trust_budget.use'
from roles r
where r.organization_id is null and r.role_key='platform_staff'
on conflict do nothing;