Multi-tenant foundations: the work before the feature work

There is a set of work that has to exist before feature delivery on a multi-tenant platform becomes predictable: how tenants are isolated, how identity and permission work, how usage is counted, and how you prove after the fact who did what. Teams routinely defer all four to ship faster, and every one of them is dramatically more expensive to retrofit than to build.

Isolation is the worst of them. Adding tenant scoping to a codebase that did not have it means auditing every query, every background job, and every report — and being wrong once is a disclosable incident.

This is the foundation we build first, and the reasoning behind each choice.

Choosing the tenancy model

Four options, and the decision is driven by compliance obligations and customer size distribution, not by scale in the abstract.

We default to shared schema with row-level security because it is the only option where isolation is enforced by the database rather than remembered by the application, and because it keeps one migration path. The common objection — “we will need database-per-tenant eventually for scale” — is usually wrong; what actually forces the move is a residency or separation clause in an enterprise contract, which is a commercial signal you will see coming.

What matters far more than the choice is that tenant_id is on every tenant-scoped table from the first migration, and that nothing in the application is trusted to remember it.

Row-level security, done so it actually holds

The mechanism is simple. Every tenant-scoped table gets a policy that filters on a session variable, and the application sets that variable per request.

-- Application connects as this role. It deliberately does NOT own the
-- tables, because table owners bypass RLS by default.
create role app_runtime nologin;

create table invoice (
  id          uuid primary key default gen_random_uuid(),
  tenant_id   uuid not null references tenant(id),
  number      text not null,
  amount_cents bigint not null,
  status      text not null check (status in ('draft','open','paid','void')),
  created_at  timestamptz not null default now(),

  -- Uniqueness is per tenant, never global. Getting this wrong leaks the
  -- existence of other tenants' data through constraint violations.
  unique (tenant_id, number)
);

alter table invoice enable row level security;

-- Belt and braces: without FORCE, a superuser or the table owner silently
-- bypasses the policy, which is exactly how these get shipped broken.
alter table invoice force row level security;

create policy invoice_tenant_isolation on invoice
  using      (tenant_id = current_setting('app.tenant_id', true)::uuid)
  with check (tenant_id = current_setting('app.tenant_id', true)::uuid);

grant select, insert, update, delete on invoice to app_runtime;

-- Index leading on tenant_id: every query is tenant-scoped, so every index
-- should be too.
create index invoice_tenant_status_idx on invoice (tenant_id, status, created_at desc);

Three details in that migration are the difference between working isolation and the appearance of it. force row level security, because the table owner otherwise bypasses the policy. The with check clause, because using alone filters reads but permits writing a row into another tenant. And the second argument true to current_setting, so an unset variable yields null and the policy matches nothing — failing closed rather than open.

The connection pool trap

This is the one that gets shipped to production broken, and it is worth the whole section. RLS depends on session state. Connection poolers hand out sessions to different requests. If you set the tenant with a plain SET, the value persists on that pooled connection and the next request — possibly for a different tenant — inherits it.

The failure is not a crash. It is a request quietly reading another tenant’s rows, and it is entirely load-dependent, which means it will not reproduce in your staging environment.

// WRONG. On a pooled connection this leaks into the next request.
await db.query("SET app.tenant_id = $1", [tenantId]);

// RIGHT. SET LOCAL is scoped to the transaction and is discarded on
// commit or rollback, so nothing survives onto the pooled connection.
export async function withTenant<T>(
  tenantId: string,
  fn: (tx: Transaction) => Promise<T>,
): Promise<T> {
  return db.transaction(async (tx) => {
    // Parameter binding is not permitted in SET LOCAL, so the value must be
    // validated rather than interpolated blindly. It is a UUID from a verified
    // token, and we assert that before it reaches here.
    if (!UUID_RE.test(tenantId)) throw new Error("invalid tenant id");
    await tx.query("SET LOCAL app.tenant_id = '" + tenantId + "'");
    return fn(tx);
  });
}

Then make the safe path the only path. No repository method should accept a raw connection; every data access goes through withTenant. If a background job needs to cross tenants, it loops over tenants calling withTenant for each — it does not get a bypass role. The one place a bypass exists (schema migrations) runs as a different role, on a different connection, from a different code path.

If you use PgBouncer in transaction pooling mode, SET LOCAL inside an explicit transaction is the only correct pattern. Session-level settings and transaction pooling are fundamentally incompatible.

Test the isolation by attacking it

Isolation you have not attacked is isolation you have assumed. This test goes in CI and it has caught real regressions for us — usually a new table shipped without a policy.

test("a tenant cannot read, update, or create across the boundary", async () => {
  const a = await createTenant();
  const b = await createTenant();

  const invoiceA = await withTenant(a.id, (tx) =>
    createInvoice(tx, { number: "INV-1", amountCents: 1000 }),
  );

  // Read isolation: B cannot see A's row, by id or by scan.
  await withTenant(b.id, async (tx) => {
    expect(await findInvoice(tx, invoiceA.id)).toBeNull();
    expect(await listInvoices(tx)).toHaveLength(0);
  });

  // Write isolation: B cannot mutate A's row. The policy makes it invisible,
  // so the update affects zero rows rather than raising.
  await withTenant(b.id, async (tx) => {
    const affected = await tx.query(
      "update invoice set status = 'void' where id = $1",
      [invoiceA.id],
    );
    expect(affected.rowCount).toBe(0);
  });

  // Insert isolation: B cannot plant a row into A, even naming A explicitly.
  await withTenant(b.id, async (tx) => {
    await expect(
      tx.query(
        "insert into invoice (tenant_id, number, amount_cents, status) " +
          "values ($1, 'INV-X', 1, 'draft')",
        [a.id],
      ),
    ).rejects.toThrow(/row-level security/);
  });

  // A's row is untouched.
  await withTenant(a.id, async (tx) => {
    expect((await findInvoice(tx, invoiceA.id))?.status).toBe("draft");
  });
});

Pair it with a schema-level assertion that fails the build when a table carrying tenant_id has no policy attached. That catches the next table someone adds, which is the actual risk — the first ten tables get policies because everyone is paying attention.

-- Fails CI if any tenant-scoped table is missing forced RLS.
select c.relname
from pg_class c
join pg_namespace n on n.oid = c.relnamespace
join pg_attribute a on a.attrelid = c.oid and a.attname = 'tenant_id'
where n.nspname = 'public'
  and c.relkind = 'r'
  and (c.relrowsecurity = false or c.relforcerowsecurity = false);

Identity and permissions as data

Enterprise buyers will ask for SSO, and they will ask for custom roles. Both are cheap if anticipated and expensive if not. The mistake to avoid is encoding roles as constants in application logic — the first customer who wants “an approver who cannot see salaries” turns that into a migration project.

Model permissions as rows. Roles are named bundles of grants:

create table permission (
  key         text primary key,          -- 'invoice:approve'
  description text not null
);

create table role (
  id          uuid primary key default gen_random_uuid(),
  -- Null tenant_id = a system role available to everyone.
  -- Non-null = a custom role belonging to one tenant.
  tenant_id   uuid references tenant(id),
  key         text not null,
  name        text not null,
  unique (tenant_id, key)
);

create table role_permission (
  role_id       uuid not null references role(id) on delete cascade,
  permission_key text not null references permission(key),
  primary key (role_id, permission_key)
);

create table membership (
  user_id   uuid not null references app_user(id) on delete cascade,
  tenant_id uuid not null references tenant(id) on delete cascade,
  role_id   uuid not null references role(id),
  primary key (user_id, tenant_id)
);

Permission checks then take a key rather than a role name, which is what makes custom roles a configuration change instead of a code change:

// Checks a capability, never a role name. Adding a custom role for one
// customer requires no code change.
export function requires(permission: PermissionKey) {
  return async (ctx: RequestContext, next: Next) => {
    if (!ctx.permissions.has(permission)) {
      // 404, not 403, on tenant-scoped resources: a 403 confirms the resource
      // exists, which is itself a cross-tenant information leak.
      throw new NotFoundError();
    }
    return next();
  };
}

router.post("/invoices/:id/approve", requires("invoice:approve"), approveInvoice);

On SSO: support OIDC first and SAML only when a customer requires it, and keep identity provider configuration per tenant from the start. Resolve the tenant from the authenticated token, never from a request header or subdomain alone — a header is user-controlled input, and treating it as a tenant assertion is the second most common isolation bug we find.

Metering that survives reconciliation

If pricing is usage-based, usage events are financial records. They need to be idempotent, because your emitters will retry, and append-only, because a customer will eventually dispute an invoice and you will need to show the working.

create table usage_event (
  id            uuid primary key default gen_random_uuid(),
  tenant_id     uuid not null references tenant(id),
  metric        text not null,              -- 'api.request', 'document.processed'
  quantity      bigint not null check (quantity >= 0),
  occurred_at   timestamptz not null,
  recorded_at   timestamptz not null default now(),

  -- Caller-supplied key. A retried emit collapses to one row instead of
  -- double-billing. This single constraint prevents an entire class of
  -- revenue-integrity incident.
  idempotency_key text not null,
  unique (tenant_id, metric, idempotency_key)
);

-- occurred_at, not recorded_at: late-arriving events must land in the period
-- they happened, or your monthly totals silently disagree with themselves.
create index usage_event_billing_idx
  on usage_event (tenant_id, metric, occurred_at);

Aggregate into period rollups on a schedule, and keep the raw events for at least as long as your dispute window plus your retention obligation. Never update a rollup in place — supersede it, so the history of what you believed and when remains inspectable.

An audit log that answers the auditor’s real question

Auditors do not primarily ask what happened. They ask whether the record could have been altered. A mutable table with an updated_at column does not answer that. A hash chain does, and it costs an afternoon.

create table audit_entry (
  seq         bigserial primary key,
  tenant_id   uuid not null references tenant(id),
  actor_id    uuid,                        -- null for system actions
  actor_type  text not null check (actor_type in ('user','system','integration')),
  action      text not null,               -- 'invoice.approved'
  subject     text not null,               -- 'invoice:0f9c...'
  metadata    jsonb not null default '{}',
  occurred_at timestamptz not null default now(),

  -- Each entry commits to the previous one. Altering or deleting any row
  -- breaks every hash after it, which is the property auditors want.
  prev_hash   bytea,
  entry_hash  bytea not null
);

-- Append-only, enforced by the database rather than by convention.
revoke update, delete on audit_entry from app_runtime;
grant insert, select on audit_entry to app_runtime;

create or replace function audit_chain() returns trigger as $fn$
declare
  last_hash bytea;
begin
  select entry_hash into last_hash
  from audit_entry
  where tenant_id = new.tenant_id
  order by seq desc
  limit 1;

  new.prev_hash := last_hash;
  new.entry_hash := digest(
    coalesce(encode(last_hash, 'hex'), '') ||
    new.tenant_id::text || coalesce(new.actor_id::text, '') ||
    new.action || new.subject ||
    new.metadata::text || new.occurred_at::text,
    'sha256'
  );
  return new;
end;
$fn$ language plpgsql;

create trigger audit_chain_before_insert
  before insert on audit_entry
  for each row execute function audit_chain();

Then run a scheduled verifier that walks each tenant’s chain and alerts on a break. The log is only evidence if something checks it; an unverified chain is a data structure, not a control.

Two things not to put in metadata: personal data beyond what the action requires, and full request bodies. An audit log is retained far longer than operational data, which makes it the most likely place for a retention obligation to be quietly violated.

Tenant lifecycle, including the end of it

Provisioning gets built because nothing works without it. Export and deletion get deferred, and then a data subject request or a departing customer makes them urgent under a deadline.

• Provisioning — one transaction creating the tenant, seeding system roles, and writing the first audit entry. Idempotent on an external key so a retried signup cannot create two tenants.
• Export— a complete machine-readable dump of one tenant’s data, generated by the same code path that serves the API so it cannot drift. Build it early; it doubles as your best integration test of tenant scoping, because anything it misses is a table you forgot to scope.
• Deletion— soft delete, a defined grace period, then hard delete. Enumerate every store, not just Postgres: object storage, caches, search indexes, analytics warehouse, log retention, and backups. Write down the backup expiry interval, because that is your real answer to “when is it gone”.

Foreign keys with on delete cascade to tenant(id)make the Postgres half nearly free. It is everything outside Postgres that takes the planning.

What would make us change our minds

• Move to database-per-tenant when a contract requires physical separation or per-region residency. Not for scale — that is usually an indexing problem wearing a topology costume.
• Move to cellswhen a single tenant’s load can degrade everyone and rate limiting has stopped being enough.
• Drop RLS for application-level scoping only if you cannot run an unprivileged database role — for instance on a managed platform that forces owner connections. Then the isolation tests become the sole control, and they need to be far more exhaustive.

The order we build it in

1. Tenant table, tenant_id on everything, forced RLS from migration one
2. withTenant as the only data access path, plus the isolation attack tests in CI
3. The schema assertion that fails the build on an unpolicied table
4. Identity, membership, and permissions as data
5. Audit log with the hash chain and its scheduled verifier
6. Metering with idempotency keys, if pricing is usage-based
7. Export, then deletion, before the first enterprise contract

None of this is novel, and that is rather the point. It is the boring foundation that makes the interesting work predictable, and it takes about three to four weeks to do properly at the start of a platform — against several months and an incident report to add later.