design-multi-tenancy

name	design-multi-tenancy
description	Architects a SaaS so many customer orgs share infrastructure without leaking into each other — picking an isolation model (shared schema + Postgres RLS, schema-per-tenant, or database-per-tenant) against an explicit cost/blast-radius/ops tradeoff table, resolving and propagating tenant context from request to DB session, and enforcing isolation in depth (app-layer query scoping PLUS RLS as the safety net) so a single forgotten tenant filter can't cross-leak. Also covers per-tenant quotas/noisy-neighbor mitigation, fan-out migrations across thousands of tenants, tenant offboarding (export + hard delete), optional per-tenant keys, and safe cross-tenant admin features.
when_to_use	Building or hardening a multi-tenant SaaS where many customer organizations share infra and must be isolated from one another — choosing an isolation model, stopping cross-tenant data leaks, scoping every query by tenant, or scaling migrations/quotas across many tenants. Distinct from design-relational-schema (general table/normalization modeling — this is the tenancy/isolation layer built on top of that), design-authorization-model (what a user may do WITHIN one tenant — RBAC/ABAC — vs separating tenants from each other), and map-privacy-data-gdpr (PII rights/consent — referenced for export/delete mechanics but not the focus).

When to Use

Reach for this skill when the question is "how do I keep tenant A's data away from tenant B while they share the same stack?" — the isolation architecture, not the per-user permissions inside one org:

• "We're going multi-tenant — shared tables with a tenant_id, a schema per customer, or a DB per customer?"
• "How do I make sure one missing WHERE tenant_id can't leak another org's data?"
• "Resolve the tenant from the subdomain / X-Tenant header / JWT org claim and scope every query to it"
• "We have 4,000 tenants — how do I run a schema migration across all of them safely?"
• "Enterprise customer wants their data in a separate database / their own encryption key"
• "One big tenant is hammering the DB and starving everyone else (noisy neighbor)"
• "Build admin impersonation / global analytics without accidentally bypassing isolation"

NOT this skill:

• Designing the tables/keys/normalization themselves (PKs, 1:N, constraints) → design-relational-schema (this skill adds the tenant_id + RLS layer on top of that model)
• Roles/permissions for users within a single tenant (admin vs viewer, per-resource sharing) → design-authorization-model (authZ within a tenant ≠ isolating tenants from each other)
• DSAR export format, consent capture, lawful basis, erasure-across-backups policy → map-privacy-data-gdpr (referenced in step 7 for the offboarding mechanics)
• Capping request rate/volume per caller mechanics (token bucket, 429, Redis counters) → rate-limiting (referenced in step 6 for per-tenant quotas)
• Running one risky ALTER on one large live table safely (locks, backfill) → db-migration-safety (referenced in step 8 for the fan-out)
• Cache patterns/TTLs/stampede in general → caching-strategy (referenced in step 9 for tenant-keyed caches)

Steps

1. Pick the isolation model from a tradeoff table — default shared+RLS, escalate per tenant only when a reason demands it. The three models are not all-or-nothing; a mature SaaS often runs hybrid pods.

   Dimension	Shared schema + RLS	Schema-per-tenant	Database-per-tenant
   Isolation	Logical (one bug from leak)	Stronger (namespace)	Strongest (physical)
   Cost / tenant	Lowest (one DB, shared)	Low–medium	Highest (conn pool, idle DB, backups each)
   Ops / migration burden	One migration, all tenants	Loop over N schemas	Loop over N databases (heaviest)
   Blast radius	All tenants (shared)	Per-schema	Per-tenant only
   Noisy neighbor	Worst — shared buffers/CPU/locks	Some sharing	Isolated resources
   Per-tenant restore / PITR	Hard (row-level surgery)	Medium	Trivial (restore that DB)
   Tenant count it scales to	100k+	hundreds–low thousands	tens–low hundreds

   Pick: shared schema + RLS by default (cheapest, scales to many small tenants); schema-per-tenant when you want per-tenant restore/customization without N databases' connection overhead; database-per-tenant for enterprise/compliance (HIPAA/SOC2 data-residency), per-tenant encryption/restore, or one tenant so large it deserves its own resources. Hybrid pods: small tenants share a pool, large/enterprise tenants get dedicated DBs — a tenant → shard/connection routing map (a "tenant catalog" table in a control-plane DB) decides at request time. Write the decision in an ADR (see write-adr); migrating models later is a large project.

2. Add tenant_id to every tenant-owned table — non-null, indexed, first column of composite indexes. In the shared model, every table carrying tenant data gets tenant_id uuid NOT NULL REFERENCES tenant(id). Make it the leading column of relevant indexes and most composite PKs/uniques ( not — email is unique , not globally). Global/system tables (plans, feature flags, the registry itself) have no . Never let be nullable or default — a null tenant row is an isolation hole.