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.

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.

The answer: two locks, not one

In booxx the separation therefore sits 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.

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.

booxx even has a third layer on top, because an accountancy firm sits between platform and client: 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.