apps/backend/src/infrastructure/database/migrations/0029_sprint_n_completion.sqlSQL
-- Sprint N completion — additional reporting slices, freshness and access history.
create table if not exists reporting_category_mix_daily (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
business_date date not null,
category_id uuid not null references menu_categories(id) on delete cascade,
quantity numeric(24,8) not null default 0,
gross_sales_minor bigint not null default 0,
gross_margin_minor numeric(24,8),
calculated_at timestamptz not null default now(),
primary key(location_id,business_date,category_id)
);
create table if not exists reporting_tender_mix_daily (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
business_date date not null,
tender_type text not null,
amount_minor bigint not null default 0,
payment_count integer not null default 0,
calculated_at timestamptz not null default now(),
primary key(location_id,business_date,tender_type)
);
create table if not exists reporting_purchasing_daily (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
business_date date not null,
received_value_minor numeric(24,8) not null default 0,
receipt_count integer not null default 0,
supplier_count integer not null default 0,
calculated_at timestamptz not null default now(),
primary key(location_id,business_date)
);
create table if not exists reporting_inventory_daily (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
business_date date not null,
low_stock_count integer not null default 0,
negative_stock_count integer not null default 0,
count_adjustment_abs numeric(24,8) not null default 0,
calculated_at timestamptz not null default now(),
primary key(location_id,business_date)
);
create table if not exists reporting_projection_freshness (
organization_id uuid not null references organizations(id) on delete cascade,
location_id uuid not null references locations(id) on delete cascade,
projection_key text not null,
business_date date,
last_success_at timestamptz,
stale_after interval not null default interval '15 minutes',
last_error text,
updated_at timestamptz not null default now(),
primary key(location_id,projection_key)
);
create table if not exists reporting_access_log (
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 set null,
user_id uuid references user_accounts(id) on delete set null,
report_type text not null,
filters jsonb not null default '{}'::jsonb,
accessed_at timestamptz not null default now()
);
create index if not exists idx_reporting_access_org_time
on reporting_access_log(organization_id,accessed_at desc);
insert into permissions(permission_key, description) values
('reporting.purchasing.read', 'Read purchasing reporting'),
('reporting.inventory.read', 'Read inventory reporting')
on conflict(permission_key) do nothing;