apps/backend/src/infrastructure/database/migrations/0012_sprint_f_service_orders.sqlSQL
-- Sprint F — live service, seats, orders, modifiers, courses.
create table if not exists service_sessions (
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,
reservation_id uuid references reservations(id) on delete set null,
status text not null default 'open',
party_size integer not null,
opened_at timestamptz not null default now(),
payment_requested_at timestamptz,
paid_at timestamptz,
closed_at timestamptz,
cancelled_at timestamptz,
opened_by uuid references user_accounts(id) on delete set null,
version bigint not null default 1,
constraint service_sessions_party_positive check (party_size > 0),
constraint service_sessions_status_check check (
status in ('open','payment_requested','paid','closed','cancelled')
)
);
create index if not exists idx_service_sessions_location_status
on service_sessions(location_id, status);
create table if not exists service_session_tables (
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,
joined_at timestamptz not null default now(),
released_at timestamptz,
primary key (service_session_id, service_table_id)
);
create unique index if not exists idx_service_table_one_active_session
on service_session_tables(service_table_id)
where released_at is null;
create table if not exists seats (
id uuid primary key default gen_random_uuid(),
service_session_id uuid not null references service_sessions(id) on delete cascade,
seat_number integer not null,
display_name text,
created_at timestamptz not null default now(),
constraint seats_session_number_unique unique (service_session_id, seat_number),
constraint seats_number_positive check (seat_number > 0)
);
create table if not exists orders (
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,
service_session_id uuid not null references service_sessions(id) on delete restrict,
status text not null default 'draft',
created_by uuid references user_accounts(id) on delete set null,
submitted_at timestamptz,
cancelled_at timestamptz,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint orders_status_check check (status in ('draft','submitted','cancelled','voided'))
);
create index if not exists idx_orders_session_status
on orders(service_session_id, status);
create table if not exists order_courses (
id uuid primary key default gen_random_uuid(),
order_id uuid not null references orders(id) on delete cascade,
course_number integer not null,
name_snapshot text,
status text not null default 'held',
fired_at timestamptz,
completed_at timestamptz,
constraint order_courses_order_number_unique unique (order_id, course_number),
constraint order_courses_status_check check (status in ('held','fired','completed','cancelled'))
);
create table if not exists order_items (
id uuid primary key default gen_random_uuid(),
order_id uuid not null references orders(id) on delete cascade,
product_id uuid not null references products(id) on delete restrict,
seat_id uuid references seats(id) on delete set null,
course_id uuid references order_courses(id) on delete set null,
quantity numeric(24,8) not null default 1,
product_name_snapshot text not null,
product_locale_snapshot text,
unit_price_minor_snapshot bigint not null,
currency_code char(3) not null,
status text not null default 'active',
notes text,
created_at timestamptz not null default now(),
constraint order_items_quantity_positive check (quantity > 0),
constraint order_items_price_nonnegative check (unit_price_minor_snapshot >= 0),
constraint order_items_status_check check (status in ('active','removed','voided','comped','remake','returned'))
);
create table if not exists order_item_modifiers (
id uuid primary key default gen_random_uuid(),
order_item_id uuid not null references order_items(id) on delete cascade,
modifier_id uuid not null references modifiers(id) on delete restrict,
modifier_name_snapshot text not null,
price_delta_minor_snapshot bigint not null default 0,
quantity numeric(24,8) not null default 1,
created_at timestamptz not null default now(),
constraint order_item_modifier_qty_positive check (quantity > 0)
);
create table if not exists order_changes (
id uuid primary key default gen_random_uuid(),
order_id uuid not null references orders(id) on delete cascade,
change_type text not null,
actor_user_id uuid references user_accounts(id) on delete set null,
entity_id uuid,
metadata jsonb not null default '{}'::jsonb,
occurred_at timestamptz not null default now()
);
create index if not exists idx_order_changes_order_time
on order_changes(order_id, occurred_at);
insert into permissions(permission_key, description) values
('service.read', 'Read live service sessions'),
('service.open', 'Open service session'),
('service.move_table', 'Move/reassign live service tables'),
('service.close', 'Close service session'),
('orders.read', 'Read orders'),
('orders.manage', 'Manage draft orders'),
('orders.submit', 'Submit orders'),
('orders.void', 'Void order items/orders'),
('orders.comp', 'Comp order items'),
('orders.remake', 'Create remake item state'),
('orders.fire_course', 'Fire order courses')
on conflict (permission_key) do nothing;