Reservations, from the inside

The problem

Bookings arrive by phone, Instagram and WhatsApp and end up in a notebook. Nobody knows how many people are coming at 8:30, two tables get promised to the same family and the customer never knows whether they were confirmed. The system does three things: takes the request without anyone answering, shows the whole service on a floor plan, and tells the customer the status of their booking without a phone call.

Data model

A booking is one row: who, how many, which day, which time, which status and which table. The physical tables already existed from the ordering demo and are shared. The demo room is the same one, with the automatic cleanup after six hours.

Table	What it holds
demo_reservations	One row per booking: who, how many, day, time, status, table and the timestamps of each change.
demo_venue_tables	The physical tables and the seats of each. Shared with the ordering demo.
demo_rooms	The demo room that links the devices. Deleted after six hours, and the bookings with it.

Capacity on the server

The booking request is a Postgres function, not a direct insert. It adds up the seats already promised for that time, compares with the house capacity and refuses before saving. If two customers ask at the same moment, the server decides, not the browser. The booking code is born there too, short and without letters that get confused on the phone.

sql

-- O pedido é uma função: a lotação decide-se no servidor.
-- Lock por sala, dia e hora: dois pedidos ao mesmo tempo
-- entram um de cada vez, nunca passam os dois.
perform pg_advisory_xact_lock(
  hashtext(p_room || '|' || p_day::text || '|' || p_slot::text));

select coalesce(sum(party), 0) into v_booked
  from public.demo_reservations
 where room_code = p_room and day = p_day and slot = p_slot
   and status in ('pendente', 'confirmada', 'sentada');

select coalesce(sum(seats), 0) into v_capacity
  from public.demo_venue_tables;

if v_booked + p_party > v_capacity then
  raise exception 'sem lugar';
end if;

-- Código curto, legível ao telefone: sem O/0 nem I/1
v_code := 'R' || upper(translate(
  substr(md5(random()::text || clock_timestamp()::text), 1, 5),
  '01', 'XY'));

One table, one booking

The rule 'no table can hold two active bookings at the same time' does not live in application code: it is a partial unique index. It only counts confirmed or seated bookings, so a no-show or a cancellation frees the table without anyone unassigning it. If someone tries, Postgres refuses and the screen shows the warning.

sql

-- Uma mesa não pode ter duas reservas activas à mesma hora.
-- Índice parcial: uma falta ou um cancelamento liberta a mesa
-- sem ninguém ter de a desatribuir.
create unique index demo_reservations_table_slot_idx
  on public.demo_reservations (room_code, day, slot, table_id)
  where table_id is not null
    and status in ('confirmada', 'sentada');

-- A chave pública não lê nome, telemóvel nem nota da tabela:
-- só saem pela função que exige o código da sala.
grant select (id, room_code, code, party, day, slot, status, table_id, ...)
  on public.demo_reservations to anon;

-- Carimbos de tempo automáticos a cada mudança de estado
create trigger demo_reservations_stamp
  before update on public.demo_reservations
  for each row execute function public.demo_stamp_reservation();

Real time on both sides

The customer listens to their own booking; the restaurant listens to the whole service. The same subscription feeds both screens, with eight-second polling as a safety net for networks that block websockets.

typescript

const channel = supabase
  .channel(`demo-reservations-${room}`)
  .on("postgres_changes",
      { event: "*", schema: "public", table: "demo_reservations",
        filter: `room_code=eq.${room}` },
      () => void load(room))
  .subscribe((status) => {
    if (status === "SUBSCRIBED") void load(room);
  });

// Rede de segurança para redes que bloqueiam websockets
const poll = window.setInterval(() => void load(room), 8000);

On the customer side

Three decisions and a short form: guests, day, time. Full times show as crossed out before choosing, computed from the bookings that already exist, so there are no surprises after submitting. The status changes in front of the customer, without reloading.

typescript

// As horas cheias aparecem riscadas antes de escolher
const full = bookedAt(day, slot) + party > capacity;

// Do lado do restaurante, atribuir mesa a um pedido confirma-o
async function assign(r: Reservation, tableId: number | null) {
  const changes: Partial<Reservation> = { table_id: tableId };
  if (tableId && r.status === "pendente") changes.status = "confirmada";
  await patch(r.id, changes);
}

What I would do differently for a real client

SMS or WhatsApp confirmation with the code, a reminder the day before and a link to cancel. Duration per booking instead of a fixed time, so the table frees up on time. Capacity rules per zone, not only for the whole house. And authentication: today any visitor changes statuses because it is a demo; in a restaurant, only the staff.