apps/backend/src/infrastructure/database/migrations/0065_operational_cases.sqlSQL
-- O1 generic operational cases compose incidents and other affected truths.
create table if not exists operational_cases (
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,
case_type text not null,
severity text not null default 'medium',
status text not null default 'open',
title text not null,
summary text not null,
affected_entity_type text,
affected_entity_id uuid,
evidence jsonb not null default '[]'::jsonb,
decision jsonb,
outcome text,
opened_by_user_id uuid references user_accounts(id) on delete set null,
resolved_by_user_id uuid references user_accounts(id) on delete set null,
opened_at timestamptz not null default now(),
resolved_at timestamptz,
version bigint not null default 1,
constraint operational_case_severity_check check(severity in ('low','medium','high','critical')),
constraint operational_case_status_check check(status in ('open','investigating','resolved','closed'))
);
create index if not exists idx_operational_cases_scope_status on operational_cases(organization_id,location_id,status,severity,opened_at desc);