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

Severity tiers

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.