apps/backend/src/infrastructure/database/migrations/0014_sprint_g_kds.sqlSQL
-- Sprint G — Kitchen Display System.
create table if not exists kitchen_stations (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
code text not null,
display_name text not null,
sort_order integer not null default 0,
status text not null default 'active',
target_seconds integer,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint kitchen_stations_location_code_unique unique(location_id, code),
constraint kitchen_stations_status_check check(status in ('active','inactive','archived')),
constraint kitchen_stations_target_check check(target_seconds is null or target_seconds > 0)
);
create table if not exists kitchen_station_translations (
kitchen_station_id uuid not null references kitchen_stations(id) on delete cascade,
locale text not null,
display_name text not null,
primary key(kitchen_station_id, locale)
);
create table if not exists product_kitchen_routes (
product_id uuid not null references products(id) on delete cascade,
kitchen_station_id uuid not null references kitchen_stations(id) on delete cascade,
priority integer not null default 0,
primary key(product_id, kitchen_station_id)
);
create table if not exists kitchen_tickets (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
location_id uuid not null references locations(id) on delete restrict,
station_id uuid not null references kitchen_stations(id) on delete restrict,
order_id uuid not null references orders(id) on delete restrict,
service_session_id uuid not null references service_sessions(id) on delete restrict,
course_number integer,
status text not null default 'queued',
priority integer not null default 0,
created_at timestamptz not null default now(),
fired_at timestamptz,
started_at timestamptz,
ready_at timestamptz,
bumped_at timestamptz,
recalled_at timestamptz,
completed_at timestamptz,
version bigint not null default 1,
constraint kitchen_ticket_status_check check (
status in ('queued','held','fired','in_progress','ready','bumped','recalled','completed','cancelled')
)
);
create index if not exists idx_kitchen_ticket_station_status
on kitchen_tickets(station_id, status, created_at);
create table if not exists kitchen_ticket_items (
id uuid primary key default gen_random_uuid(),
kitchen_ticket_id uuid not null references kitchen_tickets(id) on delete cascade,
order_item_id uuid not null references order_items(id) on delete restrict,
product_name_snapshot text not null,
quantity numeric(24,8) not null,
seat_number integer,
notes text,
modifiers_snapshot jsonb not null default '[]'::jsonb,
allergens_snapshot jsonb not null default '[]'::jsonb,
status text not null default 'queued',
created_at timestamptz not null default now(),
constraint kitchen_ticket_item_status_check check (
status in ('queued','in_progress','ready','bumped','cancelled','remake')
)
);
create unique index if not exists idx_kitchen_ticket_item_once
on kitchen_ticket_items(kitchen_ticket_id, order_item_id);
create table if not exists kitchen_ticket_events (
id uuid primary key default gen_random_uuid(),
kitchen_ticket_id uuid not null references kitchen_tickets(id) on delete cascade,
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()
);
create index if not exists idx_kitchen_ticket_events_ticket_time
on kitchen_ticket_events(kitchen_ticket_id, occurred_at);
insert into permissions(permission_key, description) values
('kitchen.read', 'Read kitchen tickets'),
('kitchen.manage_stations', 'Manage kitchen stations and product routing'),
('kitchen.start', 'Start kitchen ticket'),
('kitchen.ready', 'Mark kitchen ticket ready'),
('kitchen.bump', 'Bump kitchen ticket'),
('kitchen.recall', 'Recall bumped kitchen ticket'),
('kitchen.complete', 'Complete kitchen ticket')
on conflict(permission_key) do nothing;