Stopping double bookings in the database, not the app

There's one bug a restaurant doesn't forgive: two parties, one table, eight o'clock. The host improvises, somebody waits at the bar, and the booking app gets the blame. Fairly.

Plate is a restaurant booking platform for Lebanon. Guests book from the iOS and Android app, or from a widget on a venue's own site. Venues run their night from a web dashboard and a tablet app, and staff can act on bookings straight from WhatsApp. That's a lot of front doors into the same tables. This is about where the rule that stops a double booking lives, and why it isn't behind any of them.

The app is a suggestion

The guest app works out availability itself (it has to, to show times the moment you open a restaurant), and the code that does it is about two thousand lines long. It's good at its job: showing a guest times that will probably work, quickly.

"Probably" is the important word. Between the moment the app shows a time and the moment the guest taps it, someone else can book that table. The guest might be on an older version of the app, with older rules in it. And the widget, the dashboard and the WhatsApp flows each have their own idea of what's free.

None of that is a bug. Clients are allowed to be optimistic. They're just not allowed to decide.

One function, one transaction

So the decision lives in the database, in one Postgres function: create_booking_with_tables. The guest app and the widget don't insert a booking and then attach tables to it in a second request. They call this function, and it creates the booking and its tables together, or not at all.

It's a security-definer function, so it runs with its own privileges rather than the caller's. The function decides what gets written, not the app that called it. And before it writes anything, it checks the gap.

if exists (
  select 1
  from booking_tables bt
  join bookings b on b.id = bt.booking_id
  where bt.table_id = any(p_table_ids)
    and b.status in ('pending', 'confirmed', 'arrived', 'seated')
    and p_start < b.ends_at + gap        -- both directions,
    and b.starts_at < p_ends_at + gap    -- turn time included
) then
  raise exception 'Table not available';  -- illustrative text
end if;

The gap rule, fixed

The rule is easy to say: a table needs room between two bookings. Getting it exactly right took a migration of its own. The check was a 60-minute proximity rule, and when I went through it line by line, it was wrong in four quiet ways.

It only looked one way. A booking on one side of an existing one was caught; its mirror image wasn't. The fixed check is symmetric.

It didn't account for turn time: how long a party actually holds the table once they sit down. Now it does.

It forgot the people already there. Bookings marked arrived or seated didn't count as holding their table, which is exactly when a table is most definitely held. Now they count.

And it did its time arithmetic in whatever timezone the database session happened to be in. The function now pins its timezone, so eight o'clock means the same thing to every caller.

None of these would show up in a demo. All of them would on a busy Saturday night.

The error message is an API

Here's the part I didn't expect. When the function refuses a booking, it raises an error, and the guest app recognises that error and shows the guest something useful instead of a failure. The way the app recognises it is by matching the text of the message.

Matching on message text isn't how I'd design it today. But it's how the versions already installed on people's phones work, and those don't change because I merged something. Store releases take time, and not everyone updates the day a new version ships.

So when I rewrote the check, the error text stayed byte-for-byte identical. Same words, same punctuation, same capitalisation. The logic underneath changed completely; the string the old apps were waiting for didn't change at all.

const { error } = await supabase.rpc('create_booking_with_tables', args)
if (error && /not available/i.test(error.message)) {
  // change one word on the server and this silently stops matching
  return showPickAnotherTime()
}

Once an app is in someone's pocket, every string it reads is part of your API, whether you meant it to be or not.

If I were starting fresh, the error would carry a stable code and the app would match on that. But you don't get to start fresh with software people already have. You get to be careful.

Moving a booking without breaking another

Creating a booking is one door. Changing one is another, and it's the one people forget.

Venues can accept a booking with changes, usually a different time. That goes through its own function, accept_booking_with_modification, and the first thing it does at the new time is check the tables again. A table that was free at eight might not be free at half past.

What happens next depends on whose side the change is on. Staff who try a move that would collide are refused, and pick something else. On the guest's side, the conflicting tables are dropped from the booking instead and it's flagged, so the venue sees a booking that needs a table rather than two parties heading for the same one.

Whatever path a change takes, it meets the same rule at the bottom. That's the whole idea.

What I took from it

Put the rule where every path has to pass. For bookings that's the database. Clients can be fast and hopeful; the database is where the answer is final.

Write it once. One function that creates the booking and its tables together means there is no half-made booking to clean up afterwards.

Read old rules like a stranger. The gap check had four mistakes in it, and every one of them was quiet.

Remember the phones you've already shipped. A message, a field, a status: if an installed app reads it, it's a contract.

Plate has seated more than 115,000 guests. I'd like every one of them to have had a table of their own.