apps/backend/src/infrastructure/database/migrations/0026_sprint_m_operations_haccp.sqlSQL
-- Sprint M — operations, handover, incidents, equipment and safety/HACCP.
create table if not exists task_templates (
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,
name text not null,
description text,
category text not null,
recurrence_rule text,
default_assignee_role_id uuid references roles(id) on delete set null,
evidence_required boolean not null default false,
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 task_template_category_check check(
category in ('opening','closing','cleaning','prep','service','inventory','maintenance','safety','admin','other')
)
);
create table if not exists operational_tasks (
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,
task_template_id uuid references task_templates(id) on delete set null,
occurrence_key text,
title text not null,
description text,
category text not null,
status text not null default 'open',
priority text not null default 'normal',
assigned_employee_id uuid references employees(id) on delete set null,
assigned_role_id uuid references roles(id) on delete set null,
related_entity_type text,
related_entity_id uuid,
due_at timestamptz,
business_date date not null,
created_at timestamptz not null default now(),
started_at timestamptz,
completed_at timestamptz,
cancelled_at timestamptz,
version bigint not null default 1,
constraint operational_task_category_check check(
category in ('opening','closing','cleaning','prep','service','inventory','maintenance','safety','admin','other')
),
constraint operational_task_status_check check(
status in ('open','in_progress','completed','cancelled')
),
constraint operational_task_priority_check check(
priority in ('low','normal','high','critical')
)
);
create unique index if not exists idx_task_generation_idempotency
on operational_tasks(task_template_id, location_id, business_date, occurrence_key)
where task_template_id is not null and occurrence_key is not null;
create index if not exists idx_operational_tasks_location_date_status
on operational_tasks(location_id,business_date,status,due_at);
create table if not exists task_evidence (
id uuid primary key default gen_random_uuid(),
operational_task_id uuid not null references operational_tasks(id) on delete cascade,
evidence_type text not null,
file_asset_id uuid,
text_value text,
numeric_value numeric(24,8),
captured_at timestamptz not null default now(),
captured_by_employee_id uuid references employees(id) on delete set null,
constraint task_evidence_type_check check(
evidence_type in ('photo','text','number','signature','checklist')
)
);
create table if not exists shift_handovers (
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,
business_date date not null,
from_employee_id uuid references employees(id) on delete set null,
to_employee_id uuid references employees(id) on delete set null,
status text not null default 'draft',
summary text,
version_number integer not null default 1,
supersedes_handover_id uuid references shift_handovers(id) on delete set null,
created_at timestamptz not null default now(),
published_at timestamptz,
acknowledged_at timestamptz,
closed_at timestamptz,
version bigint not null default 1,
constraint handover_status_check check(
status in ('draft','published','acknowledged','closed')
)
);
create index if not exists idx_handovers_location_date
on shift_handovers(location_id,business_date,created_at desc);
create table if not exists shift_handover_items (
id uuid primary key default gen_random_uuid(),
shift_handover_id uuid not null references shift_handovers(id) on delete cascade,
item_type text not null,
title text not null,
body text,
priority text not null default 'normal',
related_entity_type text,
related_entity_id uuid,
resolved boolean not null default false,
sort_order integer not null default 0,
created_at timestamptz not null default now(),
constraint handover_item_type_check check(
item_type in ('guest_issue','maintenance','stock','staffing','reservation','cash','safety','general')
),
constraint handover_item_priority_check check(
priority in ('low','normal','high','critical')
)
);
create table if not exists incidents (
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,
incident_type text not null,
severity text not null,
status text not null default 'open',
title text not null,
description text not null,
occurred_at timestamptz not null,
reported_at timestamptz not null default now(),
reported_by_employee_id uuid references employees(id) on delete set null,
assigned_to_employee_id uuid references employees(id) on delete set null,
related_entity_type text,
related_entity_id uuid,
resolution text,
resolved_at timestamptz,
closed_at timestamptz,
version bigint not null default 1,
constraint incident_type_check check(
incident_type in ('guest','employee','safety','equipment','security','cash','food_quality','allergen','other')
),
constraint incident_severity_check check(
severity in ('low','medium','high','critical')
),
constraint incident_status_check check(
status in ('open','investigating','resolved','closed')
)
);
create index if not exists idx_incidents_location_status
on incidents(location_id,status,severity,reported_at desc);
create table if not exists equipment (
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,
name text not null,
equipment_type text not null,
manufacturer text,
model text,
serial_number text,
status text not null default 'active',
area_id uuid references areas(id) on delete set null,
installed_at date,
notes text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint equipment_status_check check(
status in ('active','out_of_service','maintenance','retired')
)
);
create index if not exists idx_equipment_location_status
on equipment(location_id,status);
create table if not exists maintenance_records (
id uuid primary key default gen_random_uuid(),
equipment_id uuid not null references equipment(id) on delete cascade,
maintenance_type text not null,
description text not null,
performed_at timestamptz not null,
performed_by text,
provider text,
cost_minor bigint,
currency_code char(3),
next_due_at timestamptz,
created_at timestamptz not null default now()
);
create table if not exists safety_plans (
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,
name text not null,
description text,
status text not null default 'draft',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint safety_plan_status_check check(status in ('draft','active','archived'))
);
create table if not exists safety_check_templates (
id uuid primary key default gen_random_uuid(),
safety_plan_id uuid not null references safety_plans(id) on delete cascade,
name text not null,
check_type text not null,
instructions text,
target_entity_type text,
target_entity_id uuid,
measurement_type text,
minimum_value numeric(24,8),
maximum_value numeric(24,8),
unit_code text references units(code) on delete restrict,
recurrence_rule text,
corrective_action_required_on_failure boolean not null default true,
is_active boolean not null default true,
version bigint not null default 1,
constraint safety_check_type_check check(
check_type in ('temperature','cleanliness','opening_check','closing_check','equipment','food_storage','manual')
)
);
create table if not exists safety_checks (
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,
safety_check_template_id uuid not null references safety_check_templates(id) on delete restrict,
occurrence_key text,
business_date date not null,
status text not null default 'open',
due_at timestamptz,
performed_at timestamptz,
performed_by_employee_id uuid references employees(id) on delete set null,
result text,
notes text,
created_at timestamptz not null default now(),
version bigint not null default 1,
constraint safety_check_status_check check(
status in ('open','submitted','failed','corrective_action_required','resolved','cancelled')
),
constraint safety_check_result_check check(
result is null or result in ('pass','fail','not_applicable')
)
);
create unique index if not exists idx_safety_check_generation_idempotency
on safety_checks(safety_check_template_id,location_id,business_date,occurrence_key)
where occurrence_key is not null;
create table if not exists safety_measurements (
id uuid primary key default gen_random_uuid(),
safety_check_id uuid not null references safety_checks(id) on delete cascade,
measurement_type text not null,
value_decimal numeric(24,8) not null,
unit_code text not null references units(code) on delete restrict,
capture_method text not null default 'manual',
captured_at timestamptz not null default now(),
device_id text,
constraint safety_capture_method_check check(
capture_method in ('manual','bluetooth','iot','import')
)
);
create table if not exists corrective_actions (
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,
safety_check_id uuid references safety_checks(id) on delete set null,
incident_id uuid references incidents(id) on delete set null,
title text not null,
description text not null,
status text not null default 'open',
assigned_employee_id uuid references employees(id) on delete set null,
due_at timestamptz,
completed_at timestamptz,
completed_by_employee_id uuid references employees(id) on delete set null,
evidence_required boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint corrective_action_status_check check(
status in ('open','in_progress','completed','cancelled')
)
);
create index if not exists idx_corrective_actions_location_status
on corrective_actions(location_id,status,due_at);
create table if not exists safety_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,
alert_type text not null,
severity text not null,
safety_check_id uuid references safety_checks(id) on delete cascade,
incident_id uuid references incidents(id) on delete cascade,
message text not null,
status text not null default 'open',
created_at timestamptz not null default now(),
resolved_at timestamptz,
constraint safety_alert_severity_check check(severity in ('warning','critical')),
constraint safety_alert_status_check check(status in ('open','resolved'))
);
-- Submitted safety facts are immutable.
create or replace function prevent_safety_measurement_mutation()
returns trigger language plpgsql as $$
declare check_status text;
begin
select status into check_status from safety_checks where id=old.safety_check_id;
if check_status in ('submitted','failed','corrective_action_required','resolved') then
raise exception 'submitted safety measurements are immutable'
using errcode='55000';
end if;
return new;
end;
$$;
drop trigger if exists trg_safety_measurements_no_update on safety_measurements;
create trigger trg_safety_measurements_no_update
before update on safety_measurements
for each row execute function prevent_safety_measurement_mutation();
drop trigger if exists trg_safety_measurements_no_delete on safety_measurements;
create trigger trg_safety_measurements_no_delete
before delete on safety_measurements
for each row execute function prevent_safety_measurement_mutation();
insert into permissions(permission_key, description) values
('operations.read', 'Read operational tasks and handovers'),
('operations.manage', 'Manage operational task templates and tasks'),
('operations.complete', 'Complete operational tasks'),
('operations.handover.manage', 'Manage shift handovers'),
('incidents.read', 'Read incidents'),
('incidents.sensitive.read', 'Read sensitive incident details'),
('incidents.manage', 'Create/manage incidents'),
('equipment.read', 'Read equipment'),
('equipment.manage', 'Manage equipment and maintenance'),
('safety.read', 'Read safety plans/checks'),
('safety.manage', 'Manage safety plans/templates'),
('safety.submit', 'Submit safety checks'),
('safety.corrective.manage', 'Manage corrective actions')
on conflict(permission_key) do nothing;