Multi-tenant data isolation without the leak

Most bugs cost you a patch and an apology. This one costs you the customer, and possibly their lawyer: a list endpoint returns twenty records and three of them belong to somebody else.

What makes it dangerous is that the usual defence is a convention. Every query includes WHERE tenant_id = ?, and every developer remembers, every time, forever. That holds right up until the afternoon someone writes a reporting query at speed.

Three models, honestly compared

The honest default is shared tables. Costs do not grow per customer, one migration covers everyone, and the thing people fear about it — the leak — is fixable in the database rather than by discipline.

The honest exception is a contractual one. "Our data must be physically separate" is a requirement you cannot satisfy with a column, and discovering that after signing is how a quarter disappears.

Make the filter structural

If you choose shared tables, the single most valuable thing you can do is make the tenant filter something other than a thing to remember.

Row-level security puts it in the database:

ALTER TABLE lead ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON lead
  USING (tenant_id = current_setting('app.tenant_id')::bigint);

Then set the tenant once, where the request is identified:

await db.execute(sql`SET LOCAL app.tenant_id = ${tenantId}`);
// every query on this transaction is now scoped, whether it says so or not

The property this buys is the one that matters: a forgotten filter fails closed. The careless query returns nothing instead of everything, and an empty list gets noticed in development rather than in somebody else's account.

Two cautions. The policy does not apply to the table owner or to superusers, so the application must connect as a role that is subject to it — the usual way this is silently disabled is connecting as the owner in production. And SET LOCAL is scoped to the transaction, so a pooled connection that runs queries outside one is not covered.

If row-level security is not available, make the data layer the only door:

export function repoFor(tenantId: number) {
  return {
    leads: {
      list: (limit = 20) =>
        db.select().from(lead).where(eq(lead.tenantId, tenantId)).limit(limit),
      byId: (id: number) =>
        db.select().from(lead).where(and(eq(lead.id, id), eq(lead.tenantId, tenantId))),
    },
  };
}

Nothing outside this module imports the client. The tenant is a constructor argument, not a parameter someone can omit, and a code review question becomes a lint rule.

Note byId filters on tenant too. Fetching by primary key feels safe because ids are unique — but an id from a guessed URL is still an id, and without the second predicate it reads another tenant's row perfectly.

The paths that are usually forgotten

The request path gets scoped early, because that is where the thinking happens. These four do not.

Background jobs are first. A worker processing a queue has no request and therefore no tenant, unless the job payload carries one and the worker explicitly sets it. Nothing about the code looks wrong.

Admin and support tooling is legitimately cross-tenant, which makes it the place cross-tenant queries live, and therefore the place they get copied from when someone needs something similar in a hurry.

Exports and reports get written fast, often by whoever needed the number, frequently as raw SQL that never passes through the repository at all.

And caches, which are the worst of the four because no database policy can catch them. A cache key without a tenant in it will eventually serve one tenant's data to another, and the query never ran, so row-level security never saw it and your isolation test never covered it.

That last one deserves attention in proportion to how invisible it is. Every key gets the tenant in it, no exceptions, and nobody gets to argue that this particular cache is fine. If the payloads are large, size them before they surprise you, though compressing a cache only matters once the keys are correct.

Test it like a security property

Isolation is not something you verify by reading code, because reading code is how it got written. Write the test that tries to break it.

it("never returns another tenant's lead", async () => {
  const a = await createLead({ tenantId: 1 });
  const b = await createLead({ tenantId: 2 });

  const asTenantOne = repoFor(1);
  expect(await asTenantOne.byId(b.id)).toHaveLength(0);   // not 404-by-accident — actually empty
  expect(await asTenantOne.list()).toEqual([expect.objectContaining({ id: a.id })]);
});

Run it against every repository method and add one for each new table. It is the rare test that pays for itself the first time somebody adds a query in a hurry, which will be sooner than you expect.

The question worth asking instead

Tenancy is an early decision because it is expensive to reverse and cheap to get right at the start. Pick shared tables unless a contract says otherwise, then make the tenant filter structural, through row-level security or a repository nobody can bypass.

And stop asking whether you remembered the filter. Ask what happens when somebody forgets.

If the answer is an empty result, you have isolation. If the answer is somebody else's data, you have a convention, and every convention has a failure rate you are not measuring.