A Cafe's Rules Belong in Postgres

I just finished Vivere, the ordering system for a cafe. You scan the QR code on your table, order from your phone in rounds, and pay once when you leave. Staff work from a live board on a tablet, and the owner gets a small admin console.

On paper it's a CRUD app with a menu, a cart and a bill. In practice almost every interesting problem came from the same place: several people touching the same table at the same moment, from phones on bad Wi-Fi, with money involved. That's why nearly all of the logic ended up in the database.

Yes, it's Supabase

In March I wrote about why I stopped using Supabase, and one of the three cases where I said I'd still recommend it was heavy realtime work. A cafe floor is that case. Every staff device needs a new order within a second or two, and every guest phone needs to see its round move from preparing to ready. Supabase's realtime and anonymous sign-in covered the two hardest parts of this project out of the box, so I used it.

What I didn't do is treat it as a backend I could skip writing. The app never inserts into rounds or updates tabs directly. It calls Postgres functions.

One function per move

The data model has one rule at its centre: one open tab per table. A tab is open, bill requested, paid or closed unpaid. A round is new, preparing, ready, served or cancelled. Almost everything a person can do is a move between two of those states, so each move is a function:

• place_round for a guest ordering
• request_bill for a guest tapping Pay & leave
• advance_round for staff moving a ticket along
• confirm_payment for staff taking the money
• cancel_round and close_tab_unpaid for when things go wrong

Each one is security definer, checks who's calling, locks the row it's about to change, checks that the move is allowed from the current state, and makes the change in one transaction. When it refuses, it raises a short code:

if v_status = 'bill_requested' then raise exception 'bill_already_requested'; end if;
if v_status <> 'open' then raise exception 'tab_not_payable'; end if;

On the Next.js side, one file maps every code to a sentence. bill_already_requested becomes "Bill already requested. Your server is on the way." item_unavailable:Almond croissant becomes "Almond croissant just sold out." The database decides what's allowed, and the app decides how to say it.

I could have written the same checks in Server Actions. The reason I didn't is that a Server Action checks the state, then writes, and between those two steps someone else can change the row. A function that starts with select ... for update doesn't have that gap.

Problem one: two first orders, two tabs

The obvious way to write place_round is to look for an open tab on the table and create one if there isn't one. Two friends sit down, both scan the code, and both tap Place order within the same second. Both calls see no tab, and both create one. Now table 12 has two bills.

This was the first race I planned for, before writing any of the guest app, and the answer has two layers. A partial unique index makes the bad state impossible to store:

create unique index tabs_one_live_per_table on tabs (table_id)
  where status in ('open', 'bill_requested');

And the function takes a transaction-level advisory lock on the table before it looks, so the second caller waits for the first and then finds the tab it just created:

perform pg_advisory_xact_lock(hashtext(v_table::text));

The index alone would have turned the race into an error for one of the two guests. The lock turns it into the right behaviour.

Problem two: the double tap

Cafe Wi-Fi drops, and people tap again when nothing happens. Every round carries an idempotency key, generated on the phone when the order sheet opens and replaced only after an order goes through. The first thing place_round does, before validating anything, is look for a round with that key from the same caller:

select id into v_round from rounds
  where idempotency_key = p_idempotency_key and placed_by = auth.uid();
if v_round is not null then return v_round; end if;

A retry gets the same round back. The kitchen sees one ticket.

Staff had the mirror image of this problem, and this one I didn't see coming. It turned up in a review pass near the end. Two servers glance at the same ticket on two devices and both tap Mark ready. The first tap moves it to ready, and the second moves it from ready to served, a step nobody meant to take. Now advance_round takes the status the device was showing and refuses if the row has moved on:

if v_status is null or (p_from is not null and v_status <> p_from) then
  raise exception 'round_not_advanceable';
end if;

The second server sees "This order has already moved on. Refresh to see the latest." That's an annoyance, but a wrongly served ticket is worse.

Problem three: guests without accounts

Guests never sign up. But the database has to know which phone is which, because a phone at table 12 should be able to read table 12's bill and nobody else's, and every round is labelled "Phone 1", "Phone 2" so staff know who ordered what.

Supabase's anonymous sign-in gives each phone a real user id with no login screen. The proxy signs a guest in on the first request to a table link, and from then on auth.uid() works in every function and every row-level security policy.

The bug was in when the proxy ran that sign-in. My first version only did it for full page loads, checking for an HTML request. The usual path into the menu, though, is home page, then table number, then menu, and that's a client-side navigation. It never sent an HTML request, so the menu rendered without a session and Place order failed with "Reload the page to start ordering."

Fixing that exposed the opposite problem. Next.js prefetches links, so a guest hovering over the home page could trigger several anonymous sign-ins they'd never use, and Supabase rate-limits anonymous sign-ins. The proxy now signs in on real requests, including client navigations and Server Actions, and skips anything with a prefetch header. If sign-in fails anyway, the menu still renders and ordering is switched off with a sentence explaining why.

Problem four: phone numbers in realtime

At checkout the guest enters a mobile number. Staff need it, and the other phones at the table don't. My first version got this wrong: any phone on the tab could read the number. Row-level security can hide rows, but it can't hide a column, and everyone at the table can read the tab's row because that's how they see its status.

So the column grants do it. Clients lose select on the table and get it back for named columns only:

revoke select on tabs from anon, authenticated;
grant select (id, table_id, status, payment_method, opened_at, bill_requested_at, closed_at)
  on tabs to anon, authenticated;

Staff read the number through a tab_phone() function that checks their role first. The same treatment hides table codes, so nobody can list them and order to a table they aren't sitting at.

The part I didn't trust was realtime. If the change feed broadcast whole rows, the grant would mean nothing. I checked against the hosted project that realtime applies column privileges to its payloads, and then listed the safe columns in the publication anyway, so the protection doesn't depend on one behaviour I'd verified once.

What it bought me

Two things, mostly.

Testing got simpler. There's a Vitest suite that calls these functions against a real Supabase project as a guest, as staff and as the owner, and checks both the happy paths and the refusals: a guest reading another table's bill, staff confirming the wrong amount, a second bill request. Playwright then runs the full loop from scan to paid in a browser. When the database refuses the wrong moves, the frontend tests can stay focused on whether the screens are right.

And features got cheaper to add. When the owner asked for memberships, like a yearly engraved cup with perks on later visits, I made a plan a menu item with a few extra columns. Paying for one goes through the same confirm_payment, which now also creates the membership. The discount lives in the same tab_bill function that both the guest's screen and the staff's confirm button read from. The guest's total and the amount staff confirm can't disagree, because they come from one query.

That's the case for putting the rules in Postgres. Correctness doesn't depend on every caller remembering the rules, because the database won't accept anything else.