A staffing platform that keeps its rules in the database

A back-office portal, an installable staff app and a read-only client portal all sit on the same Supabase Postgres. The business rules are not in the front ends: they are Postgres functions, triggers and policies, so a bug in any of the three apps cannot let a rule through. State machines are enforced twice: in the domain code and again by a trigger that rejects illegal transitions, and every edge is asserted by a database test.

The client role has no table policy at all. It reads only through owner-rights views, and each view filters itself to what that client is entitled to see. A test pins the exact set of policies, views and privileged functions, so adding one without review fails the build, and a second check fails if an app writes to an admin-only table directly. 'The client cannot see pay' is a property the pipeline proves on every merge, not a promise in the spec.

Filling an event runs hard gates first (role, language or gender requirement, blocked workers, double-bookings with a two-hour gap between venues, the weekly hours cap, do-not-return lists) and only then scores what remains: show-up rate, rating, proximity, fair rotation and venue history, with the weights editable in settings. Workers qualified for that client are exhausted before anyone else is considered, and that is an ordering, not a weight, by design.

The weekly cap depends on term time, student-visa limits, a completion-letter date and the 48-hour opt-out, so it is calculated on demand rather than kept as a number that can go stale. Pay is the overlap of check-in to check-out with the booked shift, with a grace period, a minimum paid length and unpaid breaks removed. Both the cap and the pay rules exist in TypeScript and in SQL, and both are held to the same JSON test vectors so the two cannot drift apart.

Deadlines such as the 12:05 cut-off are rows in a schedule table, run by pg_cron in UTC and gated on UK time inside the function, so the rule survives the clocks changing. Notifications go through an outbox with unique keys and row locking, so a retry never sends twice, and copy lives in a register as data. Anything not yet configured waits in the queue rather than failing quietly.

The Home Office right-to-work check is driven by a headless browser that stores the result and the photograph, and it never marks anyone verified: an administrator compares the photo with the worker's selfie and presses Verify or Reject. Document reading by a language model follows the same line. It pre-fills dates with a confidence score, flags doubtful reads, never overwrites what the worker typed and never verifies. Both are built and switched off until the client has signed off the legal and data-processing position.

Removing a worker anonymises them while keeping the history and the paperwork already issued, and queues storage deletion so an erasure request is never dropped. And in testing, every scheduled job returned 401 because the key stored in the vault and the key the function held differed byte for byte while both were valid. The fix was to confirm the key with Supabase Auth instead of comparing strings, and the test that pins it came from the failure.