A UAE management consultancy came to us wanting a platform, and the thing they wanted was not for them. Five hundred people, and none of them were the users. They wanted a product they could stand up for each of their own client organisations, one working deployment per client in effect, every client with a different approval chain, a different working language, and a flat expectation that its ideas would never be visible to another client. That last requirement is the whole essay. Build for it in the first commit and you have a product. Decide to add it in month eight and you have a fork, then two, then one per client with your customers' names on the branches.
Multi-customer is easy. Multi-tenant is a promise you make to someone else's compliance officer.
Most of what gets called multi-tenant is really multi-customer. Many companies use your product, but you the vendor own the one set of rules, and every customer lives inside them. Two of the other things we have built are like this. A workspace where one company's team switches between AI models without losing the thread, or a video production tool where feedback hangs off timecodes and approval is a state the cut is in, are single-tenant in the way that matters: one company's people share one space, and there is exactly one vendor deciding how approval works. That is a comfortable place to be. You change the workflow and everyone gets the new workflow.
The consultancy is the opposite kind of problem. A tenant here is not a user account, it is a client organisation the consultancy has to answer to. When that client's compliance officer asks whether their board's half-formed ideas can leak to the client down the corridor, "we're careful" is not an answer anybody signs off on. The isolation has to be a property of the architecture you can point at, not a habit of the engineering team.
Row, schema, or database, and how you actually pick
There are three honest ways to keep tenants apart in Postgres. Row-level: one set of tables, a tenant_id column on every row, and every query filtered by it. Schema-per-tenant: the same database, a separate Postgres schema for each client. Database-per-tenant: a whole database each. People argue about these as if it were a performance question. It mostly is not.
You choose by sitting with four uncomfortable questions and seeing which pain you would rather own.
- Blast radius. A migration goes wrong at two in the morning. Does it take down one client, or all of them at once?
- Noisy neighbours. One client kicks off a monstrous month-end report. Can it starve everyone else's queries while it runs?
- Per-tenant restore. A client deletes something they needed and wants it back from Tuesday. Can you restore just them, without rolling back the other forty?
- Clean deletion. A client offboards and the contract says erase everything. Can you show their regulator a real deletion, not a soft-delete flag and a promise?
Row-level scores worst on all four, and best on the only resource an early product genuinely has, which is engineering time. One schema, one migration, one connection pool, one thing to reason about. Database-per-tenant is the mirror image: a clean answer to every compliance question above, bought with an operational tax you pay forever, and it gets heavier with every client you sign. Schema-per-tenant sits in between and, in my experience, collects the disadvantages of both once you have a few hundred tables to migrate across a few dozen schemas. We went row-level, with Postgres row-level security doing the actual enforcement. I will come back to whether that was the right call, because the first version of it was not.
The tenant-id-in-every-query problem
The failure mode of row-level isolation is boring, and it is guaranteed. Your isolation is only ever as good as the WHERE clause on your worst day. Every query, every join, every count, needs and tenant_id = ?, and needs it correct. You will write hundreds of these. Someone, some Friday afternoon, writes one without it. And nothing breaks in the demo, because in the demo there is one tenant, and a missing tenant filter over a single tenant changes precisely nothing.
We got this wrong the first time. Early on the tenant scope lived in a base query class in the application layer, and it held up fine until someone wrote a reporting query that reached around it, straight at the ORM, no scope applied. In staging, with two seeded tenants, a list came back with rows from both. It never reached production and no real client data was ever involved, but it was the cheapest expensive lesson we have had, because it settled the argument: if forgetting the filter is possible, it will eventually happen, and no amount of code review gets you to never. You need the machine to refuse.
So we run two defences and lean on both. Postgres row-level security is the one below the application: you set a session variable at the start of each request, and a policy on every table forces every statement to match it, inside the database, under the ORM, somewhere a forgotten WHERE clause cannot reach. Above that sits a repository layer that will not hand you a query object without a tenant, so the common mistake shows up as a compile error long before it can become a leak.
// The tenant is not an argument you can forget. It is the thing that
// constructs the repository. No tenant, no repository, no queries.
class TenantContext {
private constructor(readonly tenantId: string) {}
static from(session: Session): TenantContext {
if (!session.tenantId) throw new Error("request has no tenant");
return new TenantContext(session.tenantId);
}
}
function repositories(ctx: TenantContext, db: Pool) {
// Every request pins the RLS session variable, so Postgres itself
// rejects cross-tenant rows even when the application code slips.
const scoped = <T>(run: (c: PoolClient) => Promise<T>) =>
withClient(db, async (c) => {
await c.query("set local app.current_tenant = $1", [ctx.tenantId]);
return run(c);
});
return {
// Note what is absent: there is no findAll() that spans tenants.
ideas: {
list: () => scoped((c) => c.query("select * from ideas")),
},
};
}And the policy that makes the first line of that comment true:
alter table ideas enable row level security;
create policy tenant_isolation on ideas
using (tenant_id = current_setting('app.current_tenant')::uuid);Configurable approval, or the fork you scheduled for yourself
The approval chain was the requirement most likely to look trivial and least likely to be. One client wants idea, then line manager, then a review committee, then a sponsor. The next wants two stages and a budget threshold that routes anything above a certain figure to finance. Model the first one in code, with an enum of stages and a ladder of if-statements deciding who can act, and you have shipped a platform that fits exactly one customer. The consultancy has dozens of them. Every new client becomes a branch, and a year later nobody can merge the branches back together.
So the stages are rows, not constants. A tenant owns an ordered set of workflow stages, each carrying its own rule for who may act and what moves an item forward, and the engine reads that configuration rather than encoding any of it. An idea never knows it is on "stage three of five". It knows which stage row it currently sits at, for its tenant. Adding a client's peculiar four-eyes-plus-budget rule turns into data entry instead of a deploy, which is the only version of this that scales past the second client.
Language falls out of the same decision, and it reaches further than you expect. Those stage names are per tenant and per locale. "Under review" is a label a client reads, and one client reads it in English while another reads it in Arabic, and underneath it is the same stage. So the stage name is a lookup keyed by tenant and language, not a string sitting in the source. Language lives in the data here, the same way the ideas do. An idea submitted and argued over in Arabic has to stay legible to an approver working in English, which means the language is a property of the content and the workflow both, right down to the vocabulary of the stages. Even your workflow's own words are a table.
Promote, do not copy
When an idea clears its last approval it becomes a project. The obvious implementation is an insert into a projects table and a status flag on the old idea. We nearly did that, and I am glad we stopped.
A project that began life as an idea carries an argument with it. Who proposed it, who pushed back, what changed between the first draft and the version that got funded, whose name is on the sponsorship. Copy the idea into a fresh table and you sever that thread on precisely the day the project is most exposed to the question "why are we doing this at all". So we treated the transition as a promotion. The entity keeps its identity and its history and simply gains a project nature. One lineage, an audit trail that runs unbroken from first submission through to delivery tracking. Six months on, when a new sponsor asks why some project exists, the answer is one query back through the approvals that paid for it, rather than an archaeology dig across two tables that were never designed to be joined.
The thing I would say to anyone starting one of these: the multi-tenant call is not a feature you get to sequence on a roadmap. It is the shape of your first table. We put tenant_id and the row-level security policy in before there was a login screen, and at the time it felt like effort spent ahead of any need. It was the cheapest that work would ever be. Every week you wait, the retrofit costs a little more, until the week it stops being a retrofit and becomes a fork.