Multi-tenancy & RLS

Tenant isolation is a property of the database, not just the application. Every tenant-owned row carries client_account_id, and Postgres Row-Level Security (RLS) is the final safety net: even a query bug or a missing WHERE clause cannot return another tenant's rows.

The model

The RLS regime is applied in db/migrations/0002_rls.sql (and re-applied per new table in later migrations). Three pieces work together:

1. Default-deny, FORCEd. Every tenant-owned table has RLS ENABLEd and FORCEd. With RLS on and no permissive policy, the table denies all access — the policy is the only door.
2. One policy per table, USING + WITH CHECK, keyed on the app.client_account_id GUC. USING filters reads/updates/deletes to the current tenant; WITH CHECK stops a write from landing a row in another tenant.
3. The tenant id comes from a session GUC, set per transaction from the verified claim — never from a client-supplied parameter.

RLS controls which rows a request may read or change. Tenant-safe relationships add a separate structural guarantee: every foreign key between tenant-owned tables includes both the entity identifier and client_account_id, for example (survey_id, client_account_id) REFERENCES survey.survey(id, client_account_id). This prevents a child row from referencing a parent owned by another tenant even if a future application bug supplies a valid cross-tenant identifier. Migration 0114 upgraded existing relationships to this composite form, and 0115 added relationships that had previously been omitted.

A helper applies the standard policy uniformly:

-- db/migrations/0002_rls.sql
create or replace function _apply_tenant_rls(p_table regclass, p_col text default 'client_account_id')
returns void language plpgsql as $$
begin
  execute format('alter table %s enable row level security', p_table);
  execute format('alter table %s force  row level security', p_table);
  execute format('drop policy if exists tenant_isolation on %s', p_table);
  execute format(
    'create policy tenant_isolation on %s using (%I = current_setting(''app.client_account_id'')::bigint) '
    || 'with check (%I = current_setting(''app.client_account_id'')::bigint)',
    p_table, p_col, p_col
  );
end $$;

select _apply_tenant_rls('response.transaction');
select _apply_tenant_rls('tenant.client_account', 'id');  -- its own id IS the discriminator

current_setting('app.client_account_id') is read with no missing-ok second argument, so an unset tenant context is a hard error — never a silent zero-row read.

The role switch is the mechanism

A subtlety that catches people: FORCE does not override BYPASSRLS. The Neon owner / Hyperdrive login role has BYPASSRLS, which skips RLS entirely. So tenant traffic must run as app_tenant (no BYPASSRLS) — the role switch, not the GUC, is what makes the policies bind. Two roles exist:

• app_tenant — nologin, no BYPASSRLS. RLS applies to it; Workers run all tenant traffic as this role. It receives only the table operations the application needs; identity.permission is read-only and, from migration 0118, compliance.deletion_record is append-only (select/insert).
• app_admin — nologin bypassrls. Reserved for genuine cross-tenant/internal ops (provisioning, the synthetic-data generator, the RLS verifier) — never a tenant request.

The login role is granted membership of both so it can SET ROLE into them.

How forTenant() pins the tenant

Every tenant-scoped transaction calls pinTenant (in db/src/client.ts) before any query. Both statements are SET LOCAL / transaction-scoped, so a pooled Hyperdrive connection never carries stale state to the next request:

// db/src/client.ts
export async function pinTenant(tx: Tx, clientAccountId: bigint): Promise<void> {
  await tx.execute(sql`set local role app_tenant`);                 // 1) drop BYPASSRLS
  await tx.execute(
    sql`select set_config('app.client_account_id', ${clientAccountId.toString()}, true)`, // 2) pin tenant
  );
}

The Neon adapter (packages/adapters/src/neon.ts) wires this into the Database port. forTenant(ctx) is the only way to get a tenant handle, and it validates the id (fail-closed: a malformed tenant id throws rather than coercing to 0n) before every transaction runs pinTenant:

// packages/adapters/src/neon.ts (paraphrased)
forTenant(ctx) {
  const tenantId = BigInt(String(ctx.clientAccountId));   // validated /^[1-9]\d*$/
  return {
    transaction(fn) {
      return drizzleDb.transaction(async (tx) => {
        await pinTenant(tx, tenantId);
        return fn(makeRepositories(tx, tenantId));
      });
    },
  };
}

Reaching a tenant-owned table outside this path is rejected by the withTenant ESLint rule, and the CI RLS test suite asserts default-deny (a wrong-tenant read returns zero rows; a cross-tenant write is rejected by WITH CHECK). The catalogue checks also fail the build if a tenant-owned table lacks an enabled policy, a tenant relationship lacks its validated composite foreign key, or a protected privilege shape drifts.

Owner-level / system methods resolve the tenant from the row

Some legitimate operations have no tenant context yet — a respondent submitting a public survey, an inbound Twilio webhook, a bounce event. These are explicit methods on the Database port (not on TenantRepositories), and they resolve the owning tenant from the row first, then pin RLS internally. For example capturePublic looks up the survey's client_account_id and runs the capture under that tenant's scope; handleEmailBounce finds the email_send row, then suppresses the recipient inside its owning tenant. The single sanctioned cross-tenant read is publicBadge, which exposes only an anonymity-thresholded NPS for a survey that has opted into a public badge.

purgeTenant is the one cross-cutting owner operation: it deletes every tenant-scoped row (discovered by scanning information_schema.columns for client_account_id across the allow-listed schemas) and finally the tenant.client_account anchor — so it runs on the owner connection, outside RLS, by design.

See also Schema & conventions and the request lifecycle in Overview.