The phone call that started it
“We have eleven workshops and eleven ways of working. We want one system, but each branch must see only its own data. Except head office, which sees everything.” That was the whole brief.
It sounds like an ordinary job-list app. It is not. The moment eleven companies share one database, a single mistake in a query stops being a typo and becomes a data-protection incident. That reorders the work: isolation first, features second.
We shipped the first working version in 9 working days. Eight went into features. The first one went entirely into the data model and the boundaries.
Three isolation models, and why we picked the middle one
A separate database per branch gives the hardest boundary, but every migration becomes eleven deployments and a head-office report means stitching eleven sources together. For a small business that is a monthly cost nobody wants to pay.
A schema per branch looks elegant on a slide and hurts in practice: migration tools, generated frontend types and the query planner all start to struggle across dozens of schemas.
That left one database, one schema and a tenant_id column on every table, enforced by Row Level Security. It scales to hundreds of branches with a single migration and a single source of truth — provided the policies are written without exceptions.
The boundary lives in the database, not in the app
The most common mistake we find in inherited projects: the organisation filter lives in application code. It works until someone adds an endpoint at 5pm on a Friday and forgets one `where`. Code has dozens of places to slip; the database has one.
So the organisation identifier is read from the user's token, not from a request parameter. A parameter can be swapped in the browser in two seconds; a server-signed claim cannot.
Roles sit in a separate table and are checked by a SECURITY DEFINER function. A role stored on a profile row the user can edit is a ready-made privilege escalation — and we have seen it in production more than once.
- Every table: a NOT NULL tenant_id column with a foreign key to the organisation.
- Every table: RLS enabled, with separate policies for select, insert, update and delete.
- GRANTs issued explicitly — a policy without a grant is not enough.
- Head office is not “a user without a filter” but a role with its own, clearly written policy.
Performance: where multi-tenant starts to hurt
After a week in production, the job list in the largest branch started loading a second slower than everywhere else. The cause was mundane: an index on the date column alone. The planner had to scan every branch's jobs before RLS discarded the foreign rows.
The rule we have applied ever since, without exception: every index starts with tenant_id, then the sort or filter column. After the change the query dropped from 940 ms to 24 ms on identical data.
The second trap is functions inside policies. If a policy calls a function that is not declared STABLE, Postgres runs it for every row. At tens of thousands of rows that is the difference between “fast” and “the client is calling”.
Background jobs: where isolation usually breaks
Service reminders, nightly head-office reports, parts-wholesaler sync — all of it runs without a signed-in user, so with service privileges that bypass RLS. It is the most delicate part of the whole system.
Our rule: a queued job always carries its tenant_id and never operates on “everything it can find”. The loop over branches lives in the queue code, not in the query — so one branch failing does not stop the other ten.
Every job also has an idempotency key made of tenant, job type and time window. A retry after a network blip will not send the customer a second SMS. Boring, but these details decide whether a system is trusted after a month or known as “the one that sometimes doubles the messages”.
The test we run on every change
Isolation cannot be “checked once”. One table added in a hurry without RLS and the boundary disappears somewhere nobody remembers. So we wrote a suite that impersonates a user from branch A and attempts twenty operations against branch B's data.
The test passes only when reads return zero rows and writes are refused. On top of that, an automated linter walks the schema and fails the build if any public table has RLS disabled or no policy. It is one of those things that costs half a day once and saves a sleepless night.
On the client's side it all looks ordinary: a manager signs in and sees their workshop, the owner sees eleven. This architecture is meant to be invisible — that is precisely its job.
What we would do differently
We would have added a “head office mode” to the tests earlier. For two days we wrote the aggregate reports bypassing policies because it was faster — and had to come back and redo them properly on a dedicated role.
And one product lesson: from day one, show in the interface which branch context you are working in. Not for technical reasons, but because a manager running two workshops will ask within the first hour.
