apps/backend/src/infrastructure/database/migrations/0027_sprint_m_completion.sqlSQL
-- Sprint M completion — amendments, evidence, scheduling and manager progress.
alter table corrective_actions
add column if not exists completion_notes text;
create table if not exists corrective_action_evidence (
id uuid primary key default gen_random_uuid(),
corrective_action_id uuid not null references corrective_actions(id) on delete cascade,
evidence_type text not null,
file_asset_id uuid,
text_value text,
captured_by_employee_id uuid references employees(id) on delete set null,
captured_at timestamptz not null default now(),
constraint corrective_action_evidence_type_check check(
evidence_type in ('photo','text','signature','document')
)
);
create table if not exists handover_amendments (
id uuid primary key default gen_random_uuid(),
shift_handover_id uuid not null references shift_handovers(id) on delete cascade,
amendment_number integer not null,
body text not null,
created_by_employee_id uuid references employees(id) on delete set null,
created_at timestamptz not null default now(),
constraint handover_amendment_unique unique(shift_handover_id,amendment_number)
);
create table if not exists safety_schedule_runs (
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,
run_key text not null,
created_checks integer not null default 0,
created_at timestamptz not null default now(),
constraint safety_schedule_run_unique unique(location_id,business_date,run_key)
);
create index if not exists idx_safety_schedule_runs_location_date
on safety_schedule_runs(location_id,business_date);