Real-time coordination layer (live ticket locks + presence) — and the presence channel that leaked emails
Added Supabase Realtime so ticket claims and checklist edits propagate instantly instead of on a 20s poll, plus a who's-online presence chip. An adversarial review caught that the presence channel leaked member emails across tenants — fixed with private channels + Realtime Authorization RLS.
Why
The two-way cloud sync polled every 20 seconds. That's fine for one person across devices, but the founder asked the right question about a multi-person company: an SVN-style lock has to sync immediately — a 20s window where two people both think a ticket is free defeats the lock's whole purpose. So the coordination surface (claims, presence) jumped ahead of widget content as the first thing to make real-time.
What shipped
- Live ticket claims/releases: the web ticket panel subscribes to
postgres_changesonticketsfiltered byservice_id. When anyone claims (atomicclaim_ticketRPC) or releases, every viewer's panel updates instantly — no refresh, no poll. Added a 🔒 indicator and a holder-only release button. - Presence: a
who's online in this workspacechip, via a Supabase Realtime presence channel. - Live checklist: the web zone view reflects checks from other users/devices the moment they happen.
- Bug fix from the prior audit:
release()was doing a direct column update that thetickets_guardtrigger rejects; switched it to the atomicrelease_ticketRPC.
Correctness was already handled: claim is an atomic compare-and-swap in the DB, so two people can never both hold a lock even at 20s — what was missing was visibility, and that's exactly what realtime fixes.
The catch an adversarial review caught
A 3-dimension review (security / correctness / no-regression) flagged a ship-blocker. postgres_changes is gated by each table's SELECT RLS — only rows you can see arrive. But presence and broadcast events on a non-private channel are not RLS-gated at all. The presence channel was presence:<serviceId> and tracked each user's full login email. A smoke test confirmed it: an anon-key client with zero membership joined the channel and read the live roster. Any self-signed-up user who learned a service's UUID could watch its members' emails — a cross-tenant PII leak in an app that is RLS-forced on every table.
The fix
Make the presence channel private and gate it with Realtime Authorization:
- Client: call
supabase.realtime.setAuth(session.access_token)on session change, and create the channel withprivate: true. - DB: RLS policies on
realtime.messagesthat allow read/track only when the topic ispresence:%andhas_service_access(<serviceId>, 'viewer')holds — reusing the same audited access helper the rest of the schema uses. - Defense in depth: track the email prefix, not the full address.
Verified: an anon client now gets CHANNEL_ERROR / Unauthorized: You do not have permissions to read from this Channel topic, while the non-private tickets postgres_changes channel still subscribes fine. The member-allowed path uses the same access helper but wasn't tested with a second real account — worst case there is an empty roster, not a leak.
Lesson
In a multi-tenant app, re-check every realtime surface against the tenant boundary per channel type. postgres_changes inherits table RLS for free; presence and broadcast do not — they need a private channel plus a realtime.messages policy, or they silently bypass the boundary everything else enforces. Anything you track or broadcast is visible to everyone who can join the topic.