Skip to main content

Multi-Tenancy Strategy

Model: Schema-per-Tenant ✅ Locked​

Each tenant gets its own PostgreSQL schema within a shared database instance. All tables are duplicated per schema — no tenant_id column needed because the schema is the tenant boundary. The application sets search_path to the active tenant's schema per request.

Why this model​

Strengths we chose for:

  • Strong sales narrative for enterprise security buyers — "every customer has their own schema" is a clear, compelling isolation claim in security review calls
  • Cleanest GDPR / right-to-be-forgotten — DROP SCHEMA tenant_xxx CASCADE is instant and complete; no dead-tuple cleanup, no audit-log row hunting, no proof-of-deletion contortions
  • Easy per-tenant backups — pg_dump --schema=tenant_xxx produces a clean tenant-scoped dump for incident response, e-discovery, or tenant migration
  • Lower misconfiguration risk — physical schema boundary is harder to mess up than RLS policy logic that depends on a correctly-set session variable on every connection
  • Team familiarity — the set-test-claude codebase already uses this pattern in production
  • ORM fit — pairs cleanly with our locked Drizzle ORM; search_path swap per connection is native

Tradeoffs we accept:

  • Migrations run N times — every schema change rolls across every tenant schema; requires migration automation tooling
  • Connection pooling needs per-request search_path discipline — connections returned to the pool must reset to a default to prevent cross-tenant leakage between requests
  • Cross-tenant admin queries require UNION ALL across schemas, or per-schema iteration in code
  • Scale ceiling — comfortable to a few hundred tenants; painful past ~500; would need pod/shard architecture past ~2000 (Salesforce's model: many databases, each holding many tenant schemas)
  • PostgreSQL catalog overhead — many schemas = many catalog rows; manageable below ~1000 schemas, monitorable via pg_stat

Data Isolation Mechanism​

  • Each tenant = one Postgres schema (e.g., tenant_acme, tenant_aldera)
  • Schema provisioning — automated on tenant onboarding: a provisioning script applies the canonical schema definition (generated by drizzle-kit) to a new tenant_<id> schema
  • NestJS Tenant Context Middleware — extracts tenant from JWT on every request and sets Drizzle's connection search_path to that tenant's schema before any query runs
  • Connection pool discipline — connections returned to the pool must RESET search_path (or equivalent guard) so that the next request cannot accidentally read another tenant's data
  • No tenant_id column needed — the schema name is the tenant identifier

Schema Migrations​

  • drizzle-kit generates migration SQL once from the source-of-truth schema definition
  • A migration runner iterates over all tenant schemas — applying each migration in turn
  • Per-tenant failure isolation — a failed migration for one schema does not roll back others; the runner reports successful/failed schemas separately
  • Migration testing — first applied to a canary subset (or full non-production environment), validated, then rolled out
  • Long migrations use online patterns — non-blocking ALTER, batched backfills — to avoid per-schema downtime

Encryption​

  • At rest: AES-256 on the PostgreSQL volume (managed-service default)
  • In transit: TLS 1.2+ for all connections (client → API, API → DB, API → external services)
  • Per-tenant encryption keys (BYOK): future consideration for premium tier

Access Controls​

  • RBAC at application level — roles scoped per tenant schema
  • Tenant-aware auth middleware — every request validated against its tenant context before any DB query runs; queries against an unmatched tenant are rejected immediately
  • Authentication provider maps 1:1 to tenants (specific provider locked in Tech Stack Item 7)
  • Least privilege for internal staff — no direct DB access without an audited break-glass procedure

Audit & Monitoring​

  • Full audit logging of all data access with tenant context (schema name + actor + action)
  • Automated isolation tests — integration tests that attempt cross-schema access and assert failure
  • SIEM-ready audit trail for compliance evidence

Compliance Path​

  • Supports: SOC 2 Type II, ISO 27001, HIPAA, PCI-DSS, GDPR
  • Auditors validate controls (tenant enforcement, encryption, audit trails, access controls), not database topology
  • Premium tier (future): dedicated database instance (database-per-tenant) for enterprise customers requiring instance-level physical separation

Honest acknowledgment​

The compliance-SaaS giants (Vanta, Drata, Salesforce, Datadog, and CrowdStrike at FedRAMP IL5) all chose shared DB + RLS at scale, and SOC 2 by itself does not require schema-per-tenant. We chose schema-per-tenant despite this because the marketing/sales narrative, GDPR cleanliness, lower misconfiguration risk, and team familiarity outweigh the operational tax for our scale targets (a few hundred to low thousands of tenants in the foreseeable future). Migration to a pod/shard architecture remains an open evolution path if growth demands it.