apps/backend/src/infrastructure/database/migrations/0010_sprint_e_reservations_floor_host.sqlSQL
-- Sprint E — floor, guests, reservations, waitlist, host timeline.
create table if not exists areas (
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 cascade,
name text not null,
sort_order integer not null default 0,
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint areas_location_name_unique unique (location_id, name),
constraint areas_status_check check (status in ('active','inactive','archived'))
);
create table if not exists area_translations (
area_id uuid not null references areas(id) on delete cascade,
locale text not null,
name text not null,
primary key (area_id, locale)
);
create table if not exists service_tables (
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 cascade,
area_id uuid not null references areas(id) on delete restrict,
table_number text not null,
display_name text,
min_capacity integer not null default 1,
max_capacity integer not null,
x numeric(12,4),
y numeric(12,4),
width numeric(12,4),
height numeric(12,4),
status text not null default 'active',
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint service_tables_location_number_unique unique (location_id, table_number),
constraint service_tables_capacity_check check (min_capacity > 0 and max_capacity >= min_capacity),
constraint service_tables_status_check check (status in ('active','inactive','archived'))
);
create table if not exists service_table_links (
parent_table_id uuid not null references service_tables(id) on delete cascade,
child_table_id uuid not null references service_tables(id) on delete cascade,
created_at timestamptz not null default now(),
primary key (parent_table_id, child_table_id),
constraint service_table_link_not_self check (parent_table_id <> child_table_id)
);
create table if not exists guests (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete restrict,
first_name text,
last_name text,
display_name text,
email_normalized text,
phone_normalized text,
preferred_locale text,
notes text,
marketing_consent boolean not null default false,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1
);
create index if not exists idx_guests_org_email
on guests(organization_id, email_normalized)
where email_normalized is not null;
create index if not exists idx_guests_org_phone
on guests(organization_id, phone_normalized)
where phone_normalized is not null;
create table if not exists guest_tags (
id uuid primary key default gen_random_uuid(),
organization_id uuid not null references organizations(id) on delete cascade,
code text not null,
display_name text not null,
created_at timestamptz not null default now(),
constraint guest_tags_org_code_unique unique (organization_id, code)
);
create table if not exists guest_tag_assignments (
guest_id uuid not null references guests(id) on delete cascade,
guest_tag_id uuid not null references guest_tags(id) on delete cascade,
created_at timestamptz not null default now(),
primary key (guest_id, guest_tag_id)
);
create table if not exists guest_dietary_notes (
id uuid primary key default gen_random_uuid(),
guest_id uuid not null references guests(id) on delete cascade,
note text not null,
source text not null default 'guest_reported',
created_at timestamptz not null default now(),
created_by uuid references user_accounts(id) on delete set null,
constraint guest_dietary_source_check check (source in ('guest_reported','staff_recorded'))
);
create table if not exists reservations (
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,
guest_id uuid references guests(id) on delete set null,
start_at timestamptz not null,
end_at timestamptz not null,
party_size integer not null,
status text not null default 'pending',
source text not null default 'internal',
guest_name_snapshot text,
guest_phone_snapshot text,
guest_email_snapshot text,
notes text,
internal_notes text,
requested_locale text,
created_at timestamptz not null default now(),
updated_at timestamptz not null default now(),
version bigint not null default 1,
constraint reservations_party_size_positive check (party_size > 0),
constraint reservations_time_check check (end_at > start_at),
constraint reservations_status_check check (
status in ('pending','confirmed','arrived','seated','completed','cancelled','no_show')
)
);
create index if not exists idx_reservations_location_start
on reservations(location_id, start_at);
create index if not exists idx_reservations_location_status_start
on reservations(location_id, status, start_at);
create table if not exists reservation_table_assignments (
reservation_id uuid not null references reservations(id) on delete cascade,
service_table_id uuid not null references service_tables(id) on delete restrict,
created_at timestamptz not null default now(),
assigned_by uuid references user_accounts(id) on delete set null,
primary key (reservation_id, service_table_id)
);
create index if not exists idx_reservation_table_assignments_table
on reservation_table_assignments(service_table_id);
create table if not exists waitlist_entries (
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,
guest_id uuid references guests(id) on delete set null,
guest_name_snapshot text not null,
party_size integer not null,
status text not null default 'waiting',
quoted_wait_minutes integer,
notes text,
created_at timestamptz not null default now(),
seated_at timestamptz,
cancelled_at timestamptz,
version bigint not null default 1,
constraint waitlist_party_positive check (party_size > 0),
constraint waitlist_status_check check (status in ('waiting','notified','seated','cancelled','left'))
);
create index if not exists idx_waitlist_location_status_created
on waitlist_entries(location_id, status, created_at);
create table if not exists reservation_events (
id uuid primary key default gen_random_uuid(),
reservation_id uuid not null references reservations(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()
);
insert into permissions(permission_key, description) values
('floor.read', 'Read floor plan and table availability'),
('floor.manage', 'Manage areas and service tables'),
('guests.read', 'Read guest profiles'),
('guests.manage', 'Manage guest profiles'),
('reservations.read', 'Read reservations'),
('reservations.manage', 'Create/update reservations'),
('reservations.assign_tables', 'Assign tables to reservations'),
('reservations.arrive', 'Mark reservation arrived'),
('reservations.cancel', 'Cancel reservations'),
('waitlist.read', 'Read waitlist'),
('waitlist.manage', 'Manage waitlist')
on conflict (permission_key) do nothing;
create or replace function enforce_table_area_same_location()
returns trigger
language plpgsql
as $$
declare
area_location uuid;
area_org uuid;
begin
select location_id, organization_id into area_location, area_org
from areas where id = new.area_id;
if area_location is null
or area_location <> new.location_id
or area_org <> new.organization_id then
raise exception 'table area/location organization mismatch'
using errcode='23514';
end if;
return new;
end;
$$;
drop trigger if exists trg_service_table_area_scope on service_tables;
create trigger trg_service_table_area_scope
before insert or update
on service_tables
for each row
execute function enforce_table_area_same_location();
create or replace function enforce_reservation_table_same_location()
returns trigger
language plpgsql
as $$
declare
reservation_location uuid;
reservation_org uuid;
table_location uuid;
table_org uuid;
begin
select location_id, organization_id
into reservation_location, reservation_org
from reservations where id=new.reservation_id;
select location_id, organization_id
into table_location, table_org
from service_tables where id=new.service_table_id;
if reservation_location is null
or table_location is null
or reservation_location <> table_location
or reservation_org <> table_org then
raise exception 'reservation table assignment crosses tenant/location'
using errcode='23514';
end if;
return new;
end;
$$;
drop trigger if exists trg_reservation_table_same_location on reservation_table_assignments;
create trigger trg_reservation_table_same_location
before insert or update
on reservation_table_assignments
for each row
execute function enforce_reservation_table_same_location();