apps/backend/src/infrastructure/database/migrations/0013_sprint_f_completion.sqlSQL
-- Sprint F completion — idempotency, explicit post-submit changes, table movement history.
create table if not exists idempotency_records (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
idempotency_key text not null,
command_name text not null,
request_hash text not null,
response_status integer,
response_body jsonb,
completed_at timestamptz,
expires_at timestamptz not null default (now() + interval '24 hours'),
created_at timestamptz not null default now(),
constraint idempotency_org_key_command_unique
unique (organization_id, idempotency_key, command_name)
);
create index if not exists idx_idempotency_expires
on idempotency_records(expires_at);
create table if not exists service_session_table_events (
id uuid primary key default gen_random_uuid(),
service_session_id uuid not null references service_sessions(id) on delete cascade,
service_table_id uuid not null references service_tables(id) on delete restrict,
event_type text not null,
actor_user_id uuid references user_accounts(id) on delete set null,
metadata jsonb not null default '{}'::jsonb,
occurred_at timestamptz not null default now(),
constraint service_session_table_event_type_check
check (event_type in ('joined','released','moved_from','moved_to'))
);
alter table order_items
add column if not exists void_reason text,
add column if not exists comp_reason text,
add column if not exists remake_of_order_item_id uuid references order_items(id) on delete set null,
add column if not exists returned_at timestamptz,
add column if not exists changed_by uuid references user_accounts(id) on delete set null,
add column if not exists changed_at timestamptz;
create table if not exists order_item_safety_snapshots (
order_item_id uuid primary key references order_items(id) on delete cascade,
guest_dietary_note_present boolean not null default false,
guest_dietary_note_snapshot text,
product_allergens_snapshot jsonb not null default '[]'::jsonb,
captured_at timestamptz not null default now()
);