apps/backend/src/infrastructure/database/migrations/0028_sprint_n_reporting.sqlSQL
-- Sprint N — reporting, KPI read models and rollups.
create table if not exists reporting_daily_location_kpis (
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,
gross_sales_minor bigint not null default 0,
net_sales_minor numeric(24,8) not null default 0,
tax_minor numeric(24,8) not null default 0,
discounts_minor bigint not null default 0,
tips_minor bigint not null default 0,
refunds_minor bigint not null default 0,
order_count integer not null default 0,
guest_count integer not null default 0,
average_check_minor numeric(24,8),
cash_sales_minor bigint not null default 0,
card_sales_minor bigint not null default 0,
theoretical_food_cost_minor numeric(24,8),
theoretical_food_cost_percent numeric(12,6),
gross_margin_minor numeric(24,8),
gross_margin_percent numeric(12,6),
waste_normalized numeric(24,8),
incident_count integer not null default 0,
critical_incident_count integer not null default 0,
task_completion_percent numeric(12,6),
safety_completion_percent numeric(12,6),
corrective_actions_open integer not null default 0,
calculated_at timestamptz not null default now(),
projection_version bigint not null default 1,
primary key(location_id,business_date)
);
create index if not exists idx_reporting_daily_org_date
on reporting_daily_location_kpis(organization_id,business_date);
create table if not exists reporting_product_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,
product_id uuid not null references products(id) on delete cascade,
quantity numeric(24,8) not null default 0,
gross_sales_minor bigint not null default 0,
net_sales_minor numeric(24,8) not null default 0,
theoretical_cost_minor numeric(24,8),
gross_margin_minor numeric(24,8),
gross_margin_percent numeric(12,6),
calculated_at timestamptz not null default now(),
primary key(location_id,business_date,product_id)
);
create index if not exists idx_product_mix_date_sales
on reporting_product_mix_daily(location_id,business_date,gross_sales_minor desc);
create table if not exists reporting_hourly_sales (
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,
hour_local smallint not null,
gross_sales_minor bigint not null default 0,
order_count integer not null default 0,
guest_count integer not null default 0,
calculated_at timestamptz not null default now(),
primary key(location_id,business_date,hour_local),
constraint reporting_hour_valid check(hour_local between 0 and 23)
);
create table if not exists reporting_jobs (
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,
report_date date,
scope text not null,
status text not null default 'pending',
attempts integer not null default 0,
reason text not null,
created_at timestamptz not null default now(),
started_at timestamptz,
completed_at timestamptz,
error text,
constraint reporting_job_scope_check check(
scope in ('daily_location','product_mix','hourly_sales','organization_rollup','all')
),
constraint reporting_job_status_check check(
status in ('pending','running','completed','failed')
)
);
create index if not exists idx_reporting_jobs_status
on reporting_jobs(status,created_at);
create table if not exists saved_reports (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
created_by uuid references user_accounts(id) on delete set null,
name text not null,
report_type text not null,
filters jsonb not null default '{}'::jsonb,
locale text not null default 'de-DE',
is_shared boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now()
);
insert into permissions(permission_key, description) values
('reporting.read', 'Read operational reporting and KPIs'),
('reporting.financial.read', 'Read financial reporting'),
('reporting.cross_location.read', 'Read cross-location comparison reports'),
('reporting.manage', 'Manage saved reports and rebuilds')
on conflict(permission_key) do nothing;