Multi-Tenancy skill for Codex

Done well means: every read and write is scoped to a tenant by construction, not by remembering to add a WHERE; the scope travels with the request into caches, jobs, files and search; and an automated test proves tenant A cannot see tenant B through any endpoint.

Choose the isolation model

Model	Use when	Costs
Shared schema, tenant_id column (default)	Many small or medium tenants, one codebase, fast onboarding	Every query must be scoped; noisy neighbours share resources
Shared schema + Postgres RLS	Same, plus defence in depth against missed filters	Pooling and migration care (below)
Schema per tenant	Tens to low thousands of tenants, per-tenant customisation or restore	Migrations run N times; catalog bloat past a few thousand schemas
Database per tenant	Regulatory isolation, data residency, very large tenants, per-tenant backup/restore	Fleet management, connection count, cross-tenant reporting needs a warehouse
Hybrid	Most tenants shared, a few enterprise tenants dedicated	Routing layer that maps tenant to database

Start shared + RLS unless a contract or regulator says otherwise. Moving one tenant out to a dedicated database later is easier than merging hundreds of schemas back.

Before changing anything

1. Read AGENTS.md and the data-access layer (ORM base repository, query helpers).
2. List every table and classify it: tenant-owned, global (plans, feature catalog), or user-level spanning tenants (a user in several orgs). rg -n "tenant|org_id|account_id|workspace".
3. Find every non-HTTP entry point: job workers, cron, webhooks, CLI scripts, admin tools. These are where tenant scope usually gets lost.

Schema rules (shared model)

CREATE TABLE invoices (
  tenant_id  uuid NOT NULL REFERENCES tenants(id),
  id         uuid NOT NULL DEFAULT gen_random_uuid(),
  customer_id uuid NOT NULL,
  number     text NOT NULL,
  PRIMARY KEY (tenant_id, id),
  UNIQUE (tenant_id, number),                                   -- uniqueness is per tenant
  FOREIGN KEY (tenant_id, customer_id) REFERENCES customers (tenant_id, id)  -- cannot point across tenants
);
CREATE INDEX ON invoices (tenant_id, created_at DESC);           -- tenant_id leads every index

• tenant_id NOT NULL on every tenant-owned table, including join tables.
• Composite foreign keys that include tenant_id make cross-tenant references impossible.
• Globally unique columns (email for login) live on global tables only.

Postgres row-level security

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;      -- applies to the table owner too
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::uuid);

Set the tenant per transaction, never per connection, because pooled connections are reused:

await db.transaction(async (tx) => {
  await tx.execute(sql`SELECT set_config('app.tenant_id', ${tenantId}, true)`); // true = transaction-local
  return handler(tx);
});

• The app role must not be a superuser, must not own the tables (or tables must use FORCE), and must not have BYPASSRLS. Migrations and admin jobs use a separate role.
• With current_setting(..., true) an unset tenant yields NULL and matches no rows: fail closed.
• PgBouncer in transaction mode is safe with set_config(..., true); session-level SET is not.
• Check plans: the policy predicate must use the tenant_id index. Run EXPLAIN as the app role.

For policy design in depth, use postgres-row-level-security.

Tenant resolution

Source	Notes
Subdomain acme.app.com	Map host to tenant in a cached lookup; reject unknown hosts
Path /t/acme/...	Easy for APIs; still verify membership
Token claim tenant_id / org_id	Set by your auth server after membership check
Header X-Tenant-ID	Only as a selector among tenants the user already belongs to

Whatever the source, verify membership: user_id belongs to tenant_id with a role. A tenant ID from the client is a request, not a fact. Resolve once in middleware and put it in a request context object (AsyncLocalStorage, contextvars, request-scoped DI), not a global.

Scope everything else

Surface	Rule
Caches	Key prefix t:{tenant_id}:...; never cache tenant data under a global key
Background jobs	Payload carries tenant_id; worker sets context (and RLS setting) before any query
Cron / batch	Iterate tenants explicitly; one failing tenant must not abort the rest
File storage	Prefix tenants/{tenant_id}/...; presigned URLs generated only after a scoped lookup
Search indexes	Filter by tenant_id on the server or with scoped keys; never trust a client filter
Logs and traces	Add tenant_id as a field for debugging and per-tenant usage
Rate limits and quotas	Per tenant, so one customer cannot starve others (api-rate-limiting)
Analytics exports	Explicit tenant filter; review any query without one

Retrofitting tenancy into a single-tenant app

1. Add tenants table and nullable tenant_id columns; backfill with the default tenant (data-backfills), then set NOT NULL and add composite keys (zero-downtime-migrations).
2. Introduce the request context and a scoped repository layer; replace raw queries.
3. Add RLS in permissive audit mode first: log queries without tenant context, fix them, then enforce.
4. Run the leak test suite (below) before onboarding a second real tenant.

Production migrations and backfills require explicit user confirmation and a tested rollback.

Leak tests

Create two tenants with identical fixtures, then for every endpoint and job:

@pytest.mark.parametrize("route", ALL_TENANT_ROUTES)
def test_no_cross_tenant_access(client, tenant_a_user, tenant_b_resource_ids, route):
    resp = client.as_user(tenant_a_user).get(route.format(id=tenant_b_resource_ids[route]))
    assert resp.status_code in (403, 404)

• Generate the route list from the router so new endpoints are covered automatically.
• Add a DB-level test: connect as the app role without setting app.tenant_id and assert every tenant table returns zero rows.
• Prefer 404 over 403 for other tenants’ resources so IDs are not confirmed to exist.

Gotchas

• Admin “impersonate tenant” tools that bypass scoping become the leak. Log every use.
• SELECT DISTINCT / aggregate endpoints (counts, dashboards) often forget the tenant filter.
• Sequential integer IDs leak tenant sizes and invite enumeration; use UUIDs or ULIDs.
• ORMs’ global scopes (Rails default_scope, Django managers, Prisma extensions) are bypassed by raw SQL and some bulk operations. RLS catches what they miss.
• Database-per-tenant multiplies connections; pool per tenant with small limits (connection-pooling).
• Deleting a tenant (offboarding) must cover DB rows, files, search docs, cache keys and backups policy. Never run it without explicit confirmation.

Output

Report: chosen model and why, tables classified, how tenant context is set and propagated, RLS status per table, surfaces scoped (cache, jobs, files, search), and leak-test coverage.