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. Within a tenant, client_id partitions that MSSP's own clients.

Isolation is enforced by the database, not by application code (Ron, 2026-09-17, after spiking both options on real PostgreSQL 16.14). Each tenant has its own LOGIN role and credential, so a connection physically holds no privilege on any other tenant's schema. The alternative — one shared identity permitted to assume any tenant's role — was tested and proven unfixable: PostgreSQL authorises a session, not a call site, so any privilege the guard may use, arbitrary SQL on that same connection may also use. Five separate escape mechanisms were demonstrated against it. Evidence: set-app .set/work/P0.5.md.

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; setting search_path and role per transaction is native

Tradeoffs we accept:

  • Migrations run N times — every schema change rolls across every tenant schema; requires migration automation tooling
  • One connection pool per active tenant — the price of a credential per tenant, not of the schema layout. Pools are lazy (opened on first use) and capped. search_path and role are set transaction-locally, so they end at COMMIT and ROLLBACK and cannot survive on a pooled connection.
  • Cross-tenant queries are forbidden, not merely awkward — two tenants may sit on different servers, where a join simply cannot run. Aggregate across tenants in the application, never in a query.
  • Capacity is per database server, not per database — max_connections is a server-wide setting fixed at start-up, so no data layout escapes that ceiling and more capacity always means another server. Reaching it is a partial outage with no warning phase: already-connected tenants stay healthy, new ones get hard errors, and the non-superuser migrator is locked out, so you cannot migrate your way out. The system must therefore report how close it is before the limit is reached.
  • 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
  • Every tenant has its own LOGIN role and credential (tenant_<slug>_rw), holding USAGE on exactly one schema, and the API connects as that role (P0.7, 2026-10-23). There is no shared runtime identity: each tenant gets its own lazily opened connection pool, authenticated with its own password from AWS Secrets Manager (set/<env>/<server>/tenant/<slug>, one secret per tenant, rotated every 90 days). The control plane connects as its own set_control login. No IAM database authentication is used. A connection therefore holds no privilege on any other tenant's schema, whatever SQL it runs.
  • One guarded entry point, and it accepts only a resolved identity — the tenant is resolved once into a frozen route object that the guard recognises by object identity. A slug, a cast, a spread or an inherited object is refused. Inside one transaction the guard sets both search_path and role transaction-locally; neither value is ever interpolated into SQL — the slug is schema-validated and the derived names travel as bind parameters.
  • search_path is the tenant schema alone — there is deliberately no public fallback.
  • Append-only audit enforced by grants, not by a trigger — the runtime role holds SELECT, INSERT and nothing more, so UPDATE, DELETE and TRUNCATE are refused by PostgreSQL itself.
  • A tenant becomes reachable only at the very end — its role, its grants, its routing row and its provisioning audit event all commit in one transaction, after its migrations succeed.
  • No tenant_id column needed — the schema name is the tenant identifier; client_id partitions within a tenant.

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, under an advisory lock shared with the reverse runner so that two runs cannot overlap
  • Per-tenant failure isolation — a failed migration for one schema does not roll back others; the runner reports successful/failed schemas separately and exits non-zero if any failed
  • Every migration after the baseline ships its reverse — a .down.sql beside the forward file. The baseline deliberately has none: undoing it is an uninstall, not a migration step. A reverse step destroys what its forward step created, so it is a production change requiring its own approval, and a reverse that touches audit data needs a verified export first — audit rows are kept seven years, while point-in-time recovery reaches only 35 days.
  • At deploy, migrations are approval-gated (Ron, 2026-09-28) — the deployment pauses before the database step, prints the exact SQL it intends to run, and proceeds only on explicit human approval. Deliberately neither fully automatic (an irreversible change could reach live customer data with nobody looking) nor operator-only (the schema and the code would drift apart and break the running application).
  • 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 for enterprise customers requiring instance-level physical separation. Note that database-per-tenant was evaluated in September 2026 as a general scaling strategy and rejected: max_connections is per server, not per database, so it carries an identical capacity ceiling while offering fewer options. It remains viable only as a per-customer premium offering, priced accordingly.

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). Sharding is not a later migration — it is designed in from the first commit (Ron, 2026-09-17): the second database server must never require a rewrite, so every code path that reaches tenant data resolves tenant -> { server, role, credential } through one lookup, there is no ambient "the database" for tenant data, the routing registry is control-plane data able to live apart from any tenant's server, and adding a server is a planned operation (dump, restore, update the routing row) rather than an emergency.

Current implementation​

For the Binary Networks platform implementation and verified staging status, see App development and staging. S.E.T is a module inside Binary Networks. This specification remains unchanged.