ADR-001: Multi-Tenant Schema-Per-Tenant PostgreSQL

Status: ✅ Accepted Date: 2026-03-26 Decision Makers: Tilak Kumar

---

Context

ThreatWeaver needs to support multiple organizations (tenants) sharing the same infrastructure while keeping their security data completely isolated. The two dominant approaches in PostgreSQL are:

• Row-Level Security (RLS): All tenants share the same tables; a tenant_id column + RLS policy filters rows.
• Schema-per-Tenant: Each tenant gets their own PostgreSQL schema (e.g., blucypher.assets, acme.assets), with a public schema for shared system tables.

The platform handles highly sensitive data — vulnerability findings, scan results, asset inventories. Any data leak across tenant boundaries would be a critical security incident.

---

Decision

Use schema-per-tenant isolation in PostgreSQL.

Each tenant is provisioned with a dedicated PostgreSQL schema. TypeORM connects to the correct schema by setting search_path on the connection. Shared system-level data (tenant registry, licensing, user accounts) lives in the public schema.

public/
  tenants        — tenant registry
  users          — cross-tenant auth
  licenses       — entitlements

blucypher/       — tenant: BluCypher
  assets
  findings
  scans
  ...

acme/            — tenant: Acme Corp
  assets
  findings
  scans
  ...

Migration is handled by npm run migrate:dev which creates schemas and seeds data per-tenant. Provisioning is orchestrated via the TLM (Tenant Lifecycle Manager).

---

Consequences

Positive:

• Complete data isolation by default — no risk of a missing WHERE tenant_id = ? leaking cross-tenant data
• Easy to implement per-tenant backups, restores, and data exports
• Schema can be dropped atomically to fully deprovision a tenant
• Simpler query logic — no tenant_id columns needed on every table
• PostgreSQL search_path manipulation is transparent to TypeORM entities

Negative / Trade-offs:

• Schema proliferation at scale (1,000 tenants = 1,000 schemas) requires careful connection pooling
• Cross-tenant analytics require UNION ALL across schemas or a separate data warehouse
• TypeORM migrations must be applied to all schemas — requires custom migration runner
• Connection pool must be schema-aware (PgBouncer configuration more complex)
• Onboarding a new tenant requires schema creation + migration + seeding (automated, but more steps than inserting a row)

---

Alternatives Considered

Option	Why Rejected
Row-Level Security (RLS)	Any application-level bug (missing tenant_id filter) leaks all tenant data. Higher developer discipline required. Security surface is larger.
Separate database per tenant	Extreme operational overhead. Separate connection pools, separate migration runs, higher cloud DB costs. Not practical for SaaS.
Single schema with tenant_id	Same issue as RLS — requires disciplined filtering everywhere. Not appropriate for security-sensitive data.