Multi-tenant scoping in PostgreSQL that survives a rewrite

A single-tenant application that might one day serve several organisations is one of the most expensive things you can build, because the day it has to change, the change touches every query you have written.

The alternative is not complicated. It is just unforgiving about discipline, so the discipline has to be mechanical.

Every tenant table carries the organisation

Not a join away. On the row.

create table deal (
  id               serial primary key,
  organisation_id  integer not null references organisation(id),
  title            varchar(180) not null
);
create index deal_organisation_idx on deal (organisation_id);

Even when the product has exactly one organisation and will for a year. The column costs four bytes and an index; retrofitting it costs a migration across every table plus an audit of every query that ever ran.

There are always a few exemptions — the organisation table itself, roles, currencies, exchange rates, sessions. Write them down. An undocumented exemption is indistinguishable from a bug.

One place puts the organisation into the query

The important part is not the column. It is that application code never chooses the organisation.

export function createRepository(ctx: Context) {
  const organisationId = ctx.session.organisationId;  // from the session, never the caller

  return {
    async listDeals(filter: DealFilter) {
      const { organisationId: _ignored, ...safe } = filter;  // strip anything supplied
      return db.select().from(deal).where(and(eq(deal.organisationId, organisationId), …));
    },
  };
}

Two rules inside that layer, both learned the hard way:

Strip a caller-supplied organisation id rather than trusting it. If a filter object can carry one, eventually something will pass one through from a request body.

Return not-found, not forbidden, for a cross-tenant id. A "you are not allowed to see this" response confirms the record exists. Not-found tells an attacker nothing.

Make the leak impossible to commit

Discipline decays. The only version of this that holds is the one a machine checks.

In this site's codebase there is exactly one module that imports the database client, and a test walks the source tree and fails the build if anything outside the repository layer imports it:

it('only the repository layer reaches the database client', () => {
  const offenders = sources
    .filter((file) => !allowed.some((prefix) => file.startsWith(prefix)))
    .filter((file) => /from '@\/db\/client'/.test(read(file)));
  expect(offenders).toEqual([]);
});

That test has already earned its place. Building the admin screens, I wrote queries directly in a server action because it was two lines and obviously fine. The build went red, I moved the queries into the repository, and the rule stayed true. Without the test, the exception would have shipped, and the second exception would have been easier than the first.

Row-level security is the stronger version, at a price

PostgreSQL can enforce this itself: policies on every table, the tenant taken from a session setting, and no way for a query to see another tenant's rows even if the application asks.

It is genuinely stronger. It is also harder to debug, slower, and it changes how migrations and background jobs have to be written — a job that runs without a tenant context now sees nothing at all.

My rule of thumb: application-layer scoping with a build-time guard for internal systems and single-organisation products; database-enforced policies when the data is regulated or when more than one organisation's data really does sit in the same tables in production.

Two things worth doing on day one

Write down the exemptions. The list of tables that are deliberately not tenant-scoped is a design document, and it belongs next to the schema.

Decide what a missing organisation means. In my systems, a query without one is a programming error that throws — not a query that quietly returns everything. The default has to be nothing, because the failure mode of the other default is a support call you will remember for a long time.