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 CASCADEis 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_xxxproduces 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-claudecodebase already uses this pattern in production - ORM fit — pairs cleanly with our locked Drizzle ORM; setting
search_pathandroleper 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_pathandroleare set transaction-locally, so they end atCOMMITandROLLBACKand 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_connectionsis 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 newtenant_<id>schema - Every tenant has its own LOGIN role and credential (
tenant_<slug>_rw), holdingUSAGEon 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 ownset_controllogin. 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_pathandroletransaction-locally; neither value is ever interpolated into SQL — the slug is schema-validated and the derived names travel as bind parameters. search_pathis the tenant schema alone — there is deliberately nopublicfallback.- Append-only audit enforced by grants, not by a trigger — the runtime role holds
SELECT, INSERTand nothing more, soUPDATE,DELETEandTRUNCATEare 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_idcolumn needed — the schema name is the tenant identifier;client_idpartitions 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.sqlbeside 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_connectionsis 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.