Docs/Decisions/ADR-0002 Schema-per-Tenant

ADR-0002: Tenant isolation by Postgres schema per tenant

Status: accepted
Decided: 2026-07 (Phase 0 Stage 4)
Deciders: Platform Engineering
Motivating failures: PgBouncer wrong-schema routing (fixed July 2026); a Flyway version-collision outage (July 2026)

Context

Every tenant's application data — agents, workflows, chat history, knowledge metadata — must be isolated from every other tenant's, auditable for GDPR erasure, and cheap to provision (a signup creates a tenant in seconds). The realistic options were a discriminator column on shared tables, a database per tenant, or a schema per tenant inside one database.

Two forces shaped the choice. First, GDPR-focused research for this platform specifically recommends schema/database-per-tenant because right-to-erasure and per-tenant data residency become mechanically simple — drop a schema, or route a tenant's schema to an EU cluster at provisioning time (TenantProvisioningService is the exact seam). Second, connection economics: database-per-tenant needs a pool per tenant; schema-per-tenant can reuse the app's single pooled DataSource — which the SchemaMultiTenantConnectionProvider javadoc calls "the whole point of choosing schema-based over database-based multi-tenancy."

Decision

Tenant isolation is a Postgres schema per tenant, implemented as Hibernate multi-tenancy:

TenantContextFilter (core/security) reads the JWT's tenant_id claim and populates the thread-local TenantContext. An authenticated token with no tenant_id is rejected outright (the one legitimate exception: platform-level callers that are tenant-less by design).
TenantSchemaResolver (core/tenancy) implements Hibernate's CurrentTenantIdentifierResolver, returning the schema name for the current request, or public when no context exists.
SchemaMultiTenantConnectionProvider (core/tenancy) implements MultiTenantConnectionProvider: every checked-out connection gets connection.setSchema(tenant), which the Postgres JDBC driver implements as SET search_path. On release the schema is reset to public before the connection returns to the pool, so a pooled connection can never carry a stale tenant into the next request.
Flyway runs two migration directories — db/migration/controlplane (shared, once, into public.flyway_schema_history) and db/migration/tenant (replayed per schema; a tenant created later replays the whole tenant chain — see ExistingTenantSchemaMigrationRunner).

flowchart LR req["Request + JWT"] --> filter["TenantContextFilter
reads tenant_id claim"] filter --> ctx["TenantContext (ThreadLocal)"] ctx --> res["TenantSchemaResolver"] res --> prov["SchemaMultiTenantConnectionProvider
setSchema → SET search_path"] prov --> pool["Shared DataSource
via PgBouncer (session mode)"] pool --> pg[("Postgres: public / control_plane / <tenant> / <tenant>_kg")]

Alternatives considered

Option	Why it lost
Discriminator column on shared tables	Every query and every new table has to remember the predicate forever; one missed `WHERE tenant_id=…` is a silent cross-tenant leak. Erasure is row-by-row surgery, not a dropped schema.
Database per tenant	Needs a connection pool per tenant and per-database provisioning/migration orchestration; at tens-to-hundreds of tenants it exhausts `max_connections` and operations. Offers stronger isolation than was needed at this scale.
Schema-qualified SQL, no session state	Would make transaction pooling safe (removing the PgBouncer constraint below), but requires every statement to carry the schema — incompatible with Hibernate's model without abandoning its multi-tenancy support. Recorded in `SchemaMultiTenantConnectionProvider`'s javadoc as the real escape hatch if session pooling ever becomes the constraint.

Consequences

Positive

Erasure and residency are mechanical: a tenant's data is one schema (plus its _kg Apache AGE graph schema), provably enumerable — a cross-cloud migration verified tenant restores "table- and row-exact" per schema.
Provisioning is cheap — a schema plus a tenant-Flyway replay.
The public fallback means pre-multi-tenancy data required no migration (V1 baseline never specified a schema).
The silent-fallback hazard is instrumented, not invisible: TenantSchemaResolver counts every public-schema fallback (tenant_context_public_fallback_total, tagged by resolution path and caller) and can fail closed via app.tenancy.fail-on-missing-context — added (F-14) after approval-policy checks silently read public and a tenant-admin's "require approval" setting was never enforced.

Negative / costs

PgBouncer MUST run session pooling. SET search_path is session state in its own implicit transaction; transaction pooling rebinds each transaction to a different server connection, so the SET and its queries can land on different backends. Measured on this stack while PgBouncer was in transaction mode: 41 of 60 concurrent clients got the wrong schema, 21 of them another tenant's. The Compose pgbouncer service is therefore pinned to POOL_MODE=session, and any external pooler you put in front of Postgres must use session pooling too.
Flyway replays per schema. Tenant migrations run once per tenant schema, on demand — they must stay idempotent-safe and fast, and a new tenant created years later replays the entire chain.
One version namespace across both folders. controlplane/ and tenant/ are separate directories but a single Flyway version space: a V156 collision (two implementations of the same version) once took a hosted instance down ~40 minutes via a checksum mismatch. scripts/ci/flyway-lint.sh now gates collisions and renames of applied migrations.
Async/background work must propagate TenantContext explicitly (TenantContextPropagatingToolCallback pattern); threads that don't, silently resolve to public — the F-14 class of bug.

Operating rule

If queries are hitting the wrong tenant's data, suspect PgBouncer left in transaction pooling first — it must be POOL_MODE=session. Getting transaction pooling back means dropping session-scoped search_path altogether, not tuning the proxy.

Evidence & references

core/tenancy/.../SchemaMultiTenantConnectionProvider.java — provider + the session-pooling mandate and the 41/60 measurement in its javadoc.
core/tenancy/.../TenantSchemaResolver.java — resolver, public fallback, F-14 instrumentation.
core/security/.../TenantContextFilter.java — tenant_id claim → TenantContext; rejection of claim-less JWTs.
README.md §1 "Two architectural facts" — PgBouncer session mode and the shared Flyway namespace stated as platform invariants.
scripts/ci/flyway-lint.sh — shared-namespace collision and applied-migration rename gate.
deploy/compose/docker-compose.yml — pgbouncer POOL_MODE comment.