Multi-tenant from day one, or it never gets fixed

In a SaaS product most decisions are reversible. You rebuild a screen, you version an API shape, you replace a framework if you really must. Three things are not, and the first is how you keep customers apart.

Skip it and you do not simply “get to it later”. You build a migration in which every query, every background job, every report and every export has to be walked past the ruler again — while production holds data from customers who must not learn the others exist.

What is multi-tenancy, and why does it have to be settled on day one?

Multi-tenancy is how a single installation of a SaaS product keeps the data of several customers apart. It only holds up with two locks at once: a tenant filter in the application and row-level security in PostgreSQL, so that a raw query, a background job or an import without customer context simply gets zero rows back. Up front that double separation costs about two days of work; retrofitting it costs weeks, plus the uncertainty about whether you caught everything — and that uncertainty never leaves.

The mistake everyone makes once

The usual approach is a tenant_id column on every table and a filter in the application, usually a global scope in the ORM so nobody has to type it each time.

That works until it does not. The holes are always in the same places:

• A raw query. Someone writes SQL by hand, once, for a report that was too slow. The ORM’s scope does not reach in there.
• A background job. It runs with no logged-in user, so no context, so no filter. Often nobody notices, because the job is invoked per customer and happens to be fine.
• An export or an import. Bulk operations almost always bypass the ordinary layer, because they were written for speed.
• A relation that walks one step too far. invoice.client.ledger — and that ledger belonged to somebody else.

Each of those four is a one-line slip. The result is not a bug but a breach, and in a financial product also a notification obligation.

A database or a schema per customer does close those holes, but trades them for operations that grow with every new customer. That trade-off, with the models side by side in a table, is in multi-tenant architecture: choosing an isolation model.

The answer: two locks, not one

So put the separation at two layers at once.

The first is the ordinary one: a tenant filter in the application, so day-to-day code does not have to think about it.

The second is row-level security in PostgreSQL. The database itself refuses to return rows that do not belong to the current tenant, regardless of who asks. The session sets a single variable — app.current_tenant_id — and the policy on the table does the rest. A raw query simply gets no rows. A background job without context gets nothing. An import that bypasses the ORM gets nothing. Provided it is actually on: a policy that exists but does nothing looks identical in a migration, and the five ways that happens are in five gaps in tenant isolation.

That is the whole point: the second layer protects you from the mistakes you are going to make in the first. Not from outside attackers — from yourself, a year from now, in a hurried Friday-afternoon fix.

Is row-level security actually on, and not just a policy that exists but does nothing?

• No

  Did the application's tenant filter apply to the query?

  • YesThe current tenant's rowsDay-to-day code, through the scope in the ORM.
  • NoAnother customer's rows can come backA raw query, a background job, an export or import, a relation that walks one step too far. Not a bug but a breach.
• Yes

  Has the session set app.current_tenant_id?

  • NoZero rowsA background job without context gets nothing.
  • YesOnly the current tenant's rowsWhether the filter applied or not: the database refuses the rest, regardless of who asks.

With the second lock on, the answer no longer depends on whether the first one applied.

If another party sits between platform and client — an accountancy firm, say — a third layer goes on top: a restrictive policy on app.current_firm_id. A firm sees its own clients, a client sees only itself, and the two rules do not fight because they live in the database rather than in an if.

What it costs

Honestly: it is not free.

• Every connection has to set the session variable. Forget it and you get zero rows — annoying, but failing in the safe direction.
• Migrations and admin jobs need an explicit way around it, and that way must be visible. Ours is a function called literally bypassRls, so you cannot miss it in a review.
• There is a small performance cost, and it pays to start your indexes with tenant_id.

Together that is maybe two days of work at the start of a project. Retrofitting the same separation is weeks, plus the uncertainty about whether you caught everything — and that uncertainty never leaves.

The tests that guard it

An isolation model without a test is a claim. The test that matters is simple and unpleasant: create two tenants, put data in both, and try to reach one from the other — through the ORM, through a raw query, through the background job, through the export.

For us that is a substantial share of nearly four thousand tests, and it is the first category the nightly agents are allowed to add coverage to. Not because it is exciting work, but because it is the one category where a mistake cannot be patched: once the data has been at the wrong customer, it has happened.

The other two

For completeness, because the question always follows: the other two irreversible decisions are how you calculate money and how you model permissions.

Amounts belong in whole cents, not in floats, and the tests belong on the outcome rather than on the code. A rounding error that runs for two years costs more than the entire project — and you usually find it because a customer finds it.

Permissions belong to a role rather than to a list of checkboxes per user. The latter feels flexible at first and is unexplainable after twenty customers, let alone testable.

That is the whole list. Everything outside it you are allowed to change later. The three side by side, with what they cost up front and afterwards plus four checks you can run yourself, are in the overview of the three irreversible decisions.

And if your product runs on PostgreSQL, there are three more things locked in next to these — the indexes, how you restore a single tenant, and how you migrate as the tenant count grows. All six are collected in multi-tenant SaaS on PostgreSQL.