# Multitenancy Audit

> Choose a tenant isolation strategy (shared-schema+RLS / schema-per-tenant / db-per-tenant), propagate tenant context reliably per request, and keep an append-only audit log. Use when building multi-tenant SaaS, when tenants could see each other's data, or when compliance needs an audit trail. Not for per-user (non-tenant) access control (use authorization) or general OWASP review (use backend-security-audit).

- Skill: `jaykim88/multitenancy-audit` (Agent Skill)
- Install (CLI): `npx skillmds@latest add jaykim88/multitenancy-audit`
- Raw SKILL.md: https://api.skillmd.com/api/skills/jaykim88/multitenancy-audit/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: Security
- License: MIT
- Author: JayKim88 (https://skillmd.com/u/jaykim88)
- Updated: 2026-09-10
- Page: https://skillmd.com/skills/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.