jaykim88/multitenancy-audit

Multitenancy & Audit

Purpose

Keep each tenant's data provably isolated and every sensitive change recorded. Pick the isolation model that matches the compliance/scale need, and make tenant-context injection bulletproof — because a single missing predicate leaks one customer's data to another.

Universal — the three isolation models, per-request tenant-context injection, and append-only audit logging are SaaS-architecture principles; Postgres RLS is the default enforcement.

Procedure

1. Choose the isolation model by isolation/scale/compliance needs

   • Shared schema + RLS (default) — one DB, tenant_id column, RLS enforces isolation; cheapest, scales to many tenants; the default for most SaaS
   • Schema-per-tenant — stronger isolation, per-tenant migration cost; moderate tenant count
   • Database-per-tenant — strongest isolation (regulatory), highest operational cost; few large/regulated tenants
   • Don't jump to DB-per-tenant without a compliance/scale driver

2. Make tenant-context injection reliable (the load-bearing detail)

   • Set app.current_tenant per request via SET LOCAL INSIDE the request transaction
   • RLS policies reference current_setting('app.current_tenant')
   • SET LOCAL (not SET) so it's scoped to the transaction and can't leak across pooled connections — this is the #1 multitenancy footgun

3. Write RLS policies on every tenant-scoped table

   • USING (tenant_id = current_setting('app.current_tenant')::uuid)
   • Enable RLS on the table (a table without RLS enabled bypasses all policies)
   • Never USING (true) (effectively disabled)

4. Append-only audit log for sensitive changes

   • Capture tenant_id, actor, action, entity, old_value, new_value, timestamp
   • Append-only: no updates/deletes on the audit table (DB-enforced — revoke UPDATE/DELETE)
   • Write the audit row in the SAME transaction as the change (see transaction-management)

5. Never trust the app layer alone for isolation

   • App-layer WHERE tenant_id = ? is the first line; RLS is the defense-in-depth that catches the forgotten filter
   • Connection pooling can leak context if you use session-level SET — always SET LOCAL

5b. Propagate tenant context to background jobs and async consumers

• Tenant context set in HTTP middleware does NOT automatically reach BullMQ workers, outbox relays, or scheduled jobs — a missing propagation here is the same leak as a missing app filter
• Stamp tenant_id into the job/event payload at enqueue; at the worker start of every job, SET LOCAL app.current_tenant = <from payload> inside the worker's DB transaction

5c. Per-tenant quotas and rate limits

• One tenant's spike must not degrade the others (noisy neighbor) — apply rate limits / connection-pool slices / job concurrency caps per tenant key
• Track usage per tenant (request count, DB time, queue depth) — surface in observability with tenant_id as a low-cardinality label

5d. Tenant data deletion (right-to-be-forgotten)

• Compliance (GDPR / similar) requires deleting a tenant's data on request
• Plan the cascade up-front: which tables, soft-delete vs hard-delete, retention of the audit log itself, deletion of derived artifacts (caches, search indexes, BI exports)
• Verify deletion completes across all stores (DB + cache + search + backups within policy)

6. Validate (validation loop)
   • As tenant A, attempt to read tenant B's rows by ID → verify denied (RLS blocks)
   • Disable the app-layer filter in a test → verify RLS STILL denies (defense in depth works)
   • Verify a pooled connection doesn't carry tenant A's context into tenant B's request (SET LOCAL scoping)
   • If cross-tenant access succeeds → RLS/context-injection broken; fix and re-test

Anti-patterns

| ❌ Anti-pattern | ✅ Correct | |---|---| | App-layer WHERE tenant_id only | RLS as defense-in-depth at the DB | | SET app.current_tenant (session-level) with pooling | SET LOCAL inside the transaction | | Table with tenant_id but RLS not enabled | ENABLE ROW LEVEL SECURITY + policy | | USING (true) policy | Real tenant_id = current_setting(...) predicate | | Mutable/deletable audit log | Append-only (revoke UPDATE/DELETE) | | Tenant context set only in HTTP middleware (not in workers / outbox) | Stamp tenant_id into job/event payload; SET LOCAL at every worker start | | No per-tenant rate limit (one tenant degrades all) | Per-tenant quotas / concurrency caps; observability labels by tenant | | No documented data-deletion cascade for tenant offboarding | Pre-planned cascade across DB / cache / search / exports for GDPR-style erasure |

Severity tiers

| Tier | Examples | Action SLA | |---|---|---| | Critical | Cross-tenant data leak (tenant A sees tenant B); RLS not enabled on a tenant table; session-level SET leaking context across pooled connections | Block release; fix immediately | | Major | Audit log mutable; tenant context not propagated to background jobs | Fix this sprint | | Minor | Audit log missing some non-sensitive actions; isolation model over-provisioned | Schedule within 2 sprints |

Stop & Ask (AI must pause for user approval)

• Before changing tenant-context propagation (middleware, SET LOCAL, connection-pool defaults) — a bug here means cross-tenant data leakage
• Before changing the isolation model (shared-schema ↔ schema-per-tenant ↔ db-per-tenant) — schema moves are largely irreversible once data lands
• Before disabling the audit log or relaxing append-only constraints — compliance impact

Completion Criteria

• [ ] Isolation model chosen with documented rationale
• [ ] Tenant context via SET LOCAL inside the transaction (pooling-safe)
• [ ] RLS enabled + correct policy on every tenant-scoped table
• [ ] Append-only audit log (DB-enforced) for sensitive changes
• [ ] Cross-tenant access test denied (with app filter disabled too)

Output

• Isolation decision ADR: model + rationale
• RLS policies per tenant table + tenant-context middleware
• Audit log table (append-only) + write hooks
• Commit format: feat(tenancy): RLS isolation for <table> / feat(audit): append-only log for <entity>

Implementation

TypeScript + Supabase / Postgres + Prisma (default)

• Supabase: RLS is first-class; auth.jwt() ->> 'tenant_id' in policies, or SET LOCAL for service-role flows
• NestJS middleware: extract tenant from token → SET LOCAL app.current_tenant at transaction start
• Audit: audit_log table; REVOKE UPDATE, DELETE ... FROM app_role; trigger or app-write in the same $transaction
• Prisma: raw SET LOCAL via $executeRaw at the start of the request transaction

Other stacks

• Python / FastAPI: SQLAlchemy SET LOCAL per session-in-request; RLS policies identical
• Go: SET LOCAL via the connection in the request context
• Universal: RLS is Postgres (and other RDBMS); the three isolation models + append-only audit + SET-LOCAL-with-pooling concern are SaaS-architecture universals

Related skills

• authorization — tenant isolation is RLS applied to a tenant_id predicate
• transaction-management — SET LOCAL tenant context lives inside the request transaction
• backend-security-audit — cross-tenant leakage is a Critical security finding

Reference

• Key insight encoded: Set app.current_tenant per request with SET LOCAL (transaction-scoped, pooling-safe) so RLS enforces isolation; back it with append-only audit rows capturing tenant_id + old/new data — never trust the app layer alone.