SHORT ANSWER
A multi-tenant system is one application and one database serving multiple independent organizations or branches. In PostgreSQL, isolation is implemented using a tenant_id column in every table, Row Level Security policies that compare it with the identifier in the user's token, and a SECURITY DEFINER function that checks roles. A secure implementation requires three additional elements: composite indexes starting with tenant_id, an idempotent background job queue (each job knows its tenant), and automated isolation tests in CI that attempt to read another organization's data on every change and must return zero rows.
- Data isolation relies on the tenant_id column and Row Level Security policies, not on filters in application code.
- The organization identifier comes from the token (claims), never from a parameter sent by the browser.
- We store roles in a separate table and check them using a SECURITY DEFINER function — a role in the user profile is a vulnerability.
- Every index starts with tenant_id; otherwise, queries scan data from all branches.
- Background jobs run with service permissions, so they must explicitly filter by tenant and be idempotent.
- Isolation tests in CI simulate a user from another organization and require zero rows in response.
- The first working version of the system for the workshop network was built in 9 working days.
The phone call that started it all
“We have eleven workshops and eleven different ways of working. We want one system, but each branch should only see its own data. Except headquarters, which should see everything.” That was the entire brief.
It sounds like a standard app with a job list. It is not. The moment one database holds data from eleven companies, every single query error stops being a typo and becomes a GDPR incident. That changes the order of work: isolation first, features second.
We built the first working version in 9 working days. Eight of them went into features. One — the first — was spent entirely on the data model and boundaries.
Three isolation models and why we chose the middle one
A separate database for each branch provides the strongest boundary, but every migration means eleven deployments, and a report for headquarters requires stitching together data from eleven sources. For a small business, that is a maintenance cost nobody wants to pay every month.
A separate schema per branch looks elegant in a presentation but hurts in practice: migration tools, types generated for the frontend and the query planner start getting lost with several dozen schemas.
That left one database, one schema and a tenant_id column in every table, enforced by Row Level Security. This model scales to hundreds of branches, with one migration and one source of truth — provided the policies are written without exceptions.
The boundary is in the database, not the application code
The most common mistake we see in projects we take over: an organization filter added in code. It works until someone adds a new endpoint on Friday at 17:00 and forgets one `where`. Code has dozens of places where things can go wrong — the database has one.
That is why we read the organization ID from the user token, not a query parameter. A parameter can be changed in the browser in two seconds; a server-signed claim cannot.
Roles are in a separate table and checked by a SECURITY DEFINER function. A role stored in a user profile that the user can edit is a ready-made privilege escalation scenario — and we have seen it in production systems more than once.
- Every table: a tenant_id NOT NULL column with a foreign key to the organization.
- Every table: RLS enabled and separate policies for reading, writing, updating and deleting.
- GRANT permissions assigned explicitly — a policy alone without a GRANT is not enough.
- Head office is not a “user without a filter”, but a role with its own clearly defined policy.
Performance: where multi-tenancy can hurt
After a week in production, the job list at the largest branch started taking a second longer to load than at the others. The reason was simple: an index on the date alone. The planner had to scan jobs from all branches before discarding other branches’ rows through the RLS policy.
The rule we have followed without exception ever since: every index starts with tenant_id, followed by the sorting or filtering column. After the change, the query dropped from 940 ms to 24 ms on the same data.
The second trap is functions in policies. If a policy calls a function not declared as STABLE, Postgres will run it for every row. With tens of thousands of rows, that is the difference between “fast” and “the client is calling”.
Background jobs, where isolation most often breaks
Service reminders, nightly reports for head office, synchronization with the parts wholesaler — all of this runs without a logged-in user, using service permissions that bypass RLS. It is the most sensitive part of the entire system.
Our rule: a queued job always carries tenant_id and never works on “everything it finds”. The loop over branches lives in the queue code, not in the query — so a failure at one branch does not stop the other ten.
Every job also has an idempotency key made up of the tenant, job type and time window. Restarting after a network error will not send the customer a second SMS. Boring, but these are the details that determine whether, after a month, the system is trusted or is “the one that sometimes duplicates notifications”.
The test we run with every change
Isolation cannot be “checked once”. All it takes is a new table added in a hurry without RLS enabled, and the boundary disappears somewhere no one remembers. So we wrote a test suite that impersonates a user from branch A and tries to perform twenty operations on branch B’s data.
The test passes only when reads return zero rows and writes are denied. On top of that, an automated linter scans the schema and fails the build if the database has a public table without RLS enabled or without a policy. It is one of those things that cost half a day once and save a sleepless night.
For the client, the result looks ordinary: a manager logs in and sees their workshop; the owner sees eleven. All this architecture is meant to be invisible — that is exactly its job.
What we would do differently
We would add “head office mode” to the tests earlier. For two days, we wrote consolidated reports bypassing policies because it was faster — then had to go back and rewrite them properly using a dedicated role.
And one product point: from day one, it is worth showing in the interface which branch you are working in. Not for technical reasons, but because a manager handling two workshops will ask about it within the first hour of use.