apps/backend/src/infrastructure/database/migrations/0035_sprint_q_completion.sqlSQL
-- Sprint Q completion — atomic reservation commit, public menu and booking message tracking.
create table if not exists public_menu_configs (
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,
is_enabled boolean not null default false,
show_prices boolean not null default true,
show_allergens 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 public_menu_config_location_unique unique(location_id)
);
create table if not exists booking_message_deliveries (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
reservation_id uuid not null references reservations(id) on delete cascade,
message_type text not null,
recipient_email text,
locale text not null,
status text not null default 'queued',
provider text,
provider_message_id text,
attempt_count integer not null default 0,
next_attempt_at timestamptz,
last_error text,
created_at timestamptz not null default now(),
sent_at timestamptz,
constraint booking_message_type_check check(
message_type in ('booking_confirmed','booking_pending','booking_cancelled')
),
constraint booking_message_status_check check(
status in ('queued','sending','sent','failed','dead_letter')
),
constraint booking_message_unique unique(reservation_id,message_type)
);
create index if not exists idx_booking_message_queue
on booking_message_deliveries(status,next_attempt_at,created_at);
-- Atomic public booking commit.
-- A location/date advisory transaction lock serializes capacity decisions for the same service day.
create or replace function public_booking_commit(
p_reservation_id uuid,
p_organization_id uuid,
p_location_id uuid,
p_guest_id uuid,
p_start_at timestamptz,
p_end_at timestamptz,
p_party_size integer,
p_status text,
p_source text,
p_guest_name text,
p_guest_phone text,
p_guest_email text,
p_notes text,
p_requested_locale text
)
returns table(reservation_id uuid, reservation_status text)
language plpgsql
as $$
declare
v_capacity integer;
v_occupied integer;
v_business_day date;
begin
if p_status not in ('pending','confirmed') then
raise exception 'public booking invalid initial status'
using errcode='22023';
end if;
select (p_start_at at time zone l.timezone)::date
into v_business_day
from locations l
where l.id=p_location_id and l.organization_id=p_organization_id and l.status='active';
if v_business_day is null then
raise exception 'public booking location unavailable'
using errcode='P0001';
end if;
perform pg_advisory_xact_lock(
hashtextextended(p_location_id::text || ':' || v_business_day::text, 0)
);
select coalesce(sum(st.max_capacity),0)::integer
into v_capacity
from service_tables st
where st.organization_id=p_organization_id
and st.location_id=p_location_id
and st.status='active';
select coalesce(sum(r.party_size),0)::integer
into v_occupied
from reservations r
where r.organization_id=p_organization_id
and r.location_id=p_location_id
and r.status in ('pending','confirmed','arrived','seated')
and r.start_at < p_end_at
and r.end_at > p_start_at;
if v_occupied + p_party_size > v_capacity then
raise exception 'public booking slot unavailable'
using errcode='P0001';
end if;
insert into reservations(
id,organization_id,location_id,guest_id,start_at,end_at,party_size,
status,source,guest_name_snapshot,guest_phone_snapshot,guest_email_snapshot,
notes,requested_locale
) values(
p_reservation_id,p_organization_id,p_location_id,p_guest_id,p_start_at,p_end_at,p_party_size,
p_status,p_source,p_guest_name,p_guest_phone,p_guest_email,p_notes,p_requested_locale
);
return query select p_reservation_id,p_status;
end;
$$;
insert into permissions(permission_key,description) values
('public_menu.settings.manage','Manage public menu settings')
on conflict(permission_key) do nothing;