How we made every tenant a database row away from impossible

The bug that would end fikadesk is a support inbox showing one company's customers to another. Every other defect is survivable. That one is not. So we built tenancy to make the wrong answer impossible at the database, not just unlikely in the code.

Every customer lives in one shared schema, with a tenant_id column on every table that holds customer data. tenant_id is the first column of every composite primary key and index. Uniqueness on anything a customer supplies is always (tenant_id, value), never global. A global unique index would answer whether an email exists somewhere else to anyone who can insert, because referential-integrity checks bypass row security.

Force RLS, and fail closed

Every tenant table ends with the same block. FORCE is the part people miss: without it, the table owner skips every policy by owning the table.

ALTER TABLE t ENABLE ROW LEVEL SECURITY;
ALTER TABLE t FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON t
  USING (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::uuid);

current_setting(..., true) reads NULL on a session that never set the variable, and an empty string on a pooled connection once the transaction that set it has ended. NULLIF collapses both to NULL, the comparison is NULL, and no row is visible. An unscoped connection fails closed with zero rows. It never falls through to another tenant's data.

We have three roles. The owner has BYPASSRLS and is used only by the migration runner. fikadesk_app runs under RLS and is what every api, worker, scheduler and ingest process connects as. fikadesk_cross is a no-login role entered with SET LOCAL ROLE inside withCrossTenant(). The app role cannot bypass RLS and is not a superuser; a test asserts both.

Application code never writes tenant_id by hand. withTenant(tenantId, fn) validates the id as a UUID, begins a transaction, calls set_config('app.tenant_id', $1, true) with the id bound, and hands the callback a branded Db<'scoped'> handle. The raw pool is not exported and the brand symbol is not either, so an unscoped handle cannot be forged. A query that forgot its tenant does not compile.

An outbox that cannot skip a number

Realtime, webhooks, search and metrics all read from one outbox table, domain_events, so they cannot disagree about order. Rows are written in the same transaction as the state change, with no sequence number. A single-writer drainer per tenant, partitioned by a hash of the tenant id, reads committed rows in commit order and assigns seq at drain time. seq is commit-ordered and gapless because one writer owns it.

We tried the obvious thing first: stamp events with nextval from a per-tenant sequence and have clients ask for everything after their watermark. Postgres says sequence values are not gapless, and nextval allocates at call time, not commit time. Writer A takes 100, writer B takes 101, B commits first, the drainer publishes 101 and advances the watermark, then A commits and 100 is never drained. The client's last_seq is 101, so on reconnect it asks for values above 101 and never learns 100 existed. In an inbox that bug reads as the reply that never arrived until you refreshed.

The drainer locks the per-tenant cursor row, then numbers the drainable rows in (xid, id) order. Rows no in-flight transaction can still precede are the ones below the snapshot's xmin.

WITH pending AS (
  SELECT tenant_id, id, row_number() OVER (ORDER BY xid, id) AS n
  FROM domain_events
  WHERE tenant_id = $1 AND seq IS NULL
    AND xid < pg_snapshot_xmin(pg_current_snapshot())
  ORDER BY xid, id
  LIMIT $2
)
UPDATE domain_events e SET seq = $lastSeq + p.n
FROM pending p
WHERE e.tenant_id = p.tenant_id AND e.id = p.id;

The drainer holds a session advisory lock for the life of its session, so a second drainer cannot number the same events. It reads the lock back off pg_locks and refuses to start if it is not on this backend, which is how it catches being pointed at a transaction pooler. That is why the worker takes a direct DATABASE_URL_DIRECT connection for the drainer and nothing else.

The app role can only append to domain_events. UPDATE and DELETE are revoked from fikadesk_app, so a bug in the api cannot rewrite history or delete the outbox rows it should have published. Only the drainer, under fikadesk_cross, assigns seq and published_at.

CI replays every read under a foreign tenant

Tests keep this honest as the table count grows. Every module registers its reads with registerReplay(name, seed, query). The seed inserts fixtures for tenant A, the query runs the read. replaySuite then runs every registration under tenant B and asserts zero rows, and again under tenant A asserting exactly the seeded rows. An empty registration cannot pass by accident, because the second half fails when it returns nothing.

A second test walks the catalog. Every table with a tenant_id column must have RLS enabled and forced, the tenant_isolation policy must deparse to the exact predicate, no other permissive policy may sit beside it, tenant_id must be uuid NOT NULL leading the primary key, and fikadesk_app must have neither rolbypassrls nor superuser. The conversation core is pinned at twenty-four tenant tables; a twenty-fifth arrives with a spec change, not a migration nobody read. The cross-tenant code is one file, and a boundary check fails if withCrossTenant appears anywhere else.

None of this removes authorization checks at the application layer. A valid but foreign tenant id is still a plain IDOR, and types cannot catch it. RLS is the backstop: a query that reached the database without a tenant returns zero rows instead of everyone's rows. The type system and the database each fail independently, and that is the property we want.