Skip to content

0012. Single PostgreSQL database with shared schema multi-tenancy

  • Status: Accepted
  • Date: 2026-10-07

Context

BizOS is in active feature development and currently targets only the development environment (no production deployment at this stage). ADR-0011 chose a database-per-tenant architecture with separate control-plane and tenant databases.

In practice, running and testing database-per-tenant during early development introduces significant friction: - Docker Compose and local environments require managing multiple databases (bizos_control, bizos_tenant_dev). - NestJS must maintain a dynamic connection pool manager with idle eviction per tenant. - Automated migrations and provisioning require running dynamic SQL (CREATE DATABASE) and fanning out migrations across databases. - Integration tests and seeding data require orchestrating multiple databases.

For this development phase, we need maximum development velocity, rapid test feedback, and minimal operational overhead.

Options considered

  1. Keep database-per-tenant (ADR-0011): Strong physical isolation, but high operational complexity, heavy connection pooling overhead, and complex migration fan-out that slows down day-to-day development.
  2. One database with schema-per-tenant: Isolates tables per PostgreSQL schema, but still requires dynamic schema migrations and complex Drizzle schema switching.
  3. One database with shared schema and tenant_id (Row-Level): All tables reside in a single database schema. Tenant-owned records include a tenant_id column. Control-plane tables and tenant data live in the same database. Migrations run in a single pass.

Decision

Adopt Option 3: Use a single PostgreSQL database with a shared schema for the development environment.

  1. Single Database: Replace separate control-plane and tenant database connections with a single DATABASE_URL (e.g. bizos_dev).
  2. Tenant Scoping (tenant_id): Every tenant-owned table includes a tenant_id: uuid foreign key column. Control-plane tables (such as tenants, plans, operator accounts) represent global platform data without a tenant scope.
  3. Single Migration History: Drizzle manages migrations in a unified migration folder (db/migrations/), applied once at application startup or via a single migration command.
  4. Application-Level Isolation: Modules enforce tenant scoping by reading the current tenant_id from the request context and filtering all tenant queries accordingly.
  5. Supersedes ADR-0011: This decision supersedes ADR-0011.

Consequences

  • Developer experience: Setup requires only one local database in Docker Compose. Running migrations, seeding, and resetting test states become single-command operations.
  • Resource efficiency: Eliminates the dynamic tenant connection pool in NestJS; a single shared connection pool handles all requests.
  • Simplified provisioning: Registering a tenant is a simple row insertion in the tenants table; no physical CREATE DATABASE or dynamic migration execution is needed.
  • Isolation responsibility: Developers must ensure that all queries against tenant tables include the tenant_id filter (to be enforced via repository base classes, scoped query helpers, or PostgreSQL RLS policies).
  • Follow-up work:
  • Update docker-compose.yml and .env.example to use a single DATABASE_URL.
  • Update libs/api/shared database configuration to provide a single Drizzle database instance.
  • Simplify libs/api/tenancy (deprecate/refactor physical database provisioner and fan-out migration runner to row-based tenant registration).
  • Update specs/tenancy/01-tenancy-core.md and related tests.