0002 — Tenancy is enforced by Postgres

Context

Multi-tenant products leak by omission. The standard approach is where(eq(table.organizationId, ctx.organizationId)) on every query, which is correct exactly as long as every person and every agent remembers it, forever, on every code path — including the job written six months from now by someone who never read this page.

The failure is silent and total: one missing clause returns another customer's data with a 200.

Decision

Tenancy is a property of the database. Every tenant-owned table carries tenantColumns and a row-level security policy in the same migration, and the request path connects as a role that owns nothing and cannot bypass RLS.

From data-layer-and-tenancy:

Competitors enforce tenancy with where(eq(t.organizationId, orgId)) by convention, which leaks the first time anyone hand-writes a query. Here a query that forgets the filter returns zero rows, because the database refuses. The lint rule stops the bypass being accidental; the policy is what stops it being possible.

Two roles, not one: migrations run as an owner, requests run as app. A no-unscoped-db lint rule keeps withSystem() — which bypasses RLS by design — confined to jobs, webhooks, auth and scripts.

Rejected

Convention-based filtering. Rejected because it fails open. The interesting detail is that this codebase still writes the explicit filter — not for safety but for the composite index on (organization_id, created_at). RLS is the correctness backstop, not the query plan.

A tenancy toggle. There used to be a --no-tenant generator flag and it was removed rather than fixed, because it made the schema conditional — the one decision the tenancy design says never to make. Kits that "support both" end up with nullable owner columns and polymorphic scoping that poison every query, index and policy forever. Organizations exist in both tenancy modes; only the UI and routing differ, which is why switching later needs no data migration.

Consequences, and two incidents

Both of these are operational rather than theoretical, and both cost real time.

Transaction-scoped GUCs and connection pooling. set_config(..., true) is scoped to the transaction. Under session pooling it is not, so one tenant's setting leaks into another tenant's query. The pooler mode is therefore not a deployment preference — it is part of the security model.

'' is not NULL. On a pooled connection a reset GUC returns the empty string, so ''::uuid raises a 22P02 cast error instead of cleanly denying access. The policies use nullif(..., '') for exactly this, and the generator emits it so a new table cannot get it wrong.

unsafeTestContext is the one sanctioned way to mint a context outside the auth path, and is named that way on purpose.