Multi-Schema To RLS Migration

On May 12, 2026, Lumie retired schema-per-tenant routing and moved the backend to a single public schema protected by Postgres row-level security (RLS). The migration was a safety correction, not a cosmetic data-model cleanup.

The old model selected a tenant schema when the application borrowed a connection. That was fragile once real work moved through RabbitMQ callbacks, schedulers, transaction pooling, and code paths that did not look like normal HTTP requests.

Context

The previous tenancy model depended on session-level schema selection:

tenant context -> connection borrow -> SET search_path -> ORM query

That model was workable only while request handling and connection usage stayed simple. It became unsafe when:

• callbacks and scheduled jobs ran outside normal request filters;
• PgBouncer transaction pooling made session assumptions weaker;
• async workers needed tenant context after the original request was gone;
• schema fan-out multiplied migrations and operational checks.

The current repository keeps the replacement design in these source paths:

• lumie-backend/libs/common/src/main/java/com/lumie/common/tenant/RlsTenantContextAspect.java
• lumie-backend/libs/common/src/main/java/com/lumie/common/tenant/RlsTenantTransactionBinder.java
• lumie-backend/libs/common/src/main/java/com/lumie/common/domain/TenantScopedEntity.java
• lumie-backend/app/src/main/resources/db/migration/public/V18__rls_baseline.sql

Impact

This was not recorded as a single customer-visible outage. The impact was architectural risk: tenant isolation depended on connection lifecycle details that async execution paths could bypass.

The operational cost was also real:

Area	Cost of schema-per-tenant
Migrations	every tenant schema needed schema management
Async callbacks	tenant context had to survive outside the request path
Debugging	failures depended on which connection held which search_path
Pooling	transaction pooling and session-scoped state pulled in opposite directions
Contributor model	new code had to remember a hidden connection-binding invariant

What Broke Conceptually

The key mismatch was timing. Tenant selection happened when a connection was borrowed, but the correct isolation boundary was the transaction performing the data access.

That created a class of bugs where code could look correct at the request boundary and still execute SQL under the wrong schema or without the expected tenant binding later. RabbitMQ callbacks and scheduled jobs made this visible because they did not naturally inherit HTTP request lifecycle state.

Diagnosis

The migration followed a narrowing diagnosis:

Probe	Result	Interpretation
Inspect HTTP request paths	request filters could set tenant context	HTTP was not the only execution model to protect
Inspect async callbacks	work could reach repositories outside the original request connection lifecycle	schema routing was not a complete tenancy boundary
Compare PgBouncer behavior	transaction pooling weakens assumptions about session state	search_path was the wrong primitive for the runtime
Evaluate RLS	tenant predicate lives with the table and transaction-local setting	isolation could move closer to the data

The durable answer was to bind tenant identity at transaction entry and let Postgres enforce row visibility.

Resolution

The settled design moved isolation into the database:

• tenant-owned tables carry tenant_id;
• tenant-scoped entities extend TenantScopedEntity;
• transactions bind app.tenant_id through RlsTenantTransactionBinder;
• policies in V18__rls_baseline.sql use ENABLE ROW LEVEL SECURITY, FORCE ROW LEVEL SECURITY, and tenant predicates;
• runtime app connections use a non-bypass role, while migrations use the migrator role.

The load-bearing runtime sequence is:

TenantContextHolder has slug and tenantId
  -> @Transactional method begins
  -> RlsTenantContextAspect runs inside transaction interceptor ordering
  -> RlsTenantTransactionBinder sets app.tenant_id with transaction scope
  -> repository query is filtered by Postgres RLS policy

That ordering matters. If transaction management order or the RLS aspect order drifts, the binding can happen outside the active transaction and the policy will not see the intended value.

Current State

The migration's result is now the backend baseline:

• modular monolith;
• single public schema for tenant-owned tables;
• tenant ids stored on rows;
• RLS enforced in Postgres;
• application runtime role without BYPASSRLS;
• explicit tenant propagation across HTTP, backend-worker, and worker-backend boundaries.

Historical schema-routing classes, tenant-schema migration runners, and MultiTenantConnectionProvider-style paths are not the steady-state model anymore. This page records why that model was removed.

Verification

Future checks should prove both code and database invariants:

Check	Expected evidence
Migration baseline	V18__rls_baseline.sql enables and forces RLS on tenant tables
Runtime role	app datasource uses the non-bypass runtime user
Transaction binding	tests cover tenant context inside transactional service methods
Async path	callbacks and schedulers set tenant context before data access
Negative isolation	cross-tenant reads return no rows unless tenant context matches

The source incident record did not preserve every command output from the migration window, so this page treats the current repo state and migration files as the durable evidence.

What Went Well

• The migration fixed the isolation primitive rather than adding more request filters.
• RLS moved the final authorization boundary into Postgres, where accidental repository access still remains constrained.
• The change clarified contributor expectations: tenant context must exist before transactional work, not merely before controller entry.

What Went Poorly

• The earlier schema-per-tenant model hid too much in connection lifecycle behavior.
• Async and scheduler paths were not first-class design inputs early enough.
• Schema fan-out added migration cost while still failing to protect the hardest execution paths.

Action Items

Action	Owner / surface	Status	Evidence
Keep runtime DB connections on the non-bypass app role.	lumie-backend datasource config	Ongoing invariant	Workspace hard rule requires lumie_app without BYPASSRLS.
Keep RLS binding inside transaction boundaries.	RlsTenantContextAspect, transaction config	Ongoing invariant	Aspect and binder remain current source paths.
Add or preserve async tenant-context tests when callback paths change.	backend tests	Required for future changes	The migration was motivated by async path risk.

Lessons

• Tenant isolation has to survive the least request-shaped execution path.
• Session-scoped schema switching is a poor fit for transaction pooling and async workers.
• RLS does not remove the need for application tenant context; it makes missing context fail closed instead of leaking rows.
• Migration success is an operational contract change, not only a data layout change.