Skip to main content

Multi Tenancy

Lumie uses shared-schema PostgreSQL multi-tenancy. Tenant isolation is enforced with Row Level Security in the public schema, not with one schema per academy.

This page is a reference document for the RLS model, the code that binds it, and the failure modes that matter when adding or changing data access.

Source Paths​

PathRole
app/src/main/resources/db/migration/public/V18__rls_baseline.sqlShared-schema RLS baseline and schema-per-tenant retirement
app/src/main/resources/db/migration/public/V65__repair_read_receipt_and_file_download_rls.sqlExample forward repair for tables that missed tenant-safe constraints or policies
libs/common/src/main/java/com/lumie/common/tenant/RlsTenantContextAspect.javaInvokes RLS binding for declarative @Transactional entry points
libs/common/src/main/java/com/lumie/common/tenant/RlsTenantTransactionBinder.javaBinds app.tenant_id to the active transaction
libs/common/src/main/java/com/lumie/common/tenant/TenantContextHolder.javaHolds tenant slug and tenant ID on the current thread
libs/common/src/main/java/com/lumie/common/domain/TenantScopedEntity.javaAuto-populates tenant_id for most tenant-scoped aggregates
app/src/main/java/com/lumie/app/config/RuntimeDbRoleGuard.javaFails startup if the runtime DB role can bypass RLS
app/src/test/java/com/lumie/app/migration/MigrationsRlsIntegrationTest.javaIntegration test for migration-chain health and hostile-tenant isolation

Storage Model​

Tenant-scoped tables​

  • live in public
  • carry tenant_id BIGINT NOT NULL
  • enable ROW LEVEL SECURITY
  • force FORCE ROW LEVEL SECURITY
  • define a tenant_isolation policy comparing tenant_id to current_setting('app.tenant_id', true)

Platform-scoped tables​

These remain cross-tenant by design and do not use RLS for runtime reads:

  • tenants
  • plans
  • event_publication
  • shedlock
  • billing platform tables created in V28__billing_platform_tables.sql

Special schema​

V27__langgraph_schema.sql creates a dedicated langgraph schema for chatbot checkpoint persistence. That is a technical worker-state exception, not a tenant-data storage model.

The Load-Bearing Predicate​

The migrations use the canonical policy shape. Source anchor: app/src/main/resources/db/migration/public/V18__rls_baseline.sql.

using      (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint)
with check (tenant_id = nullif(current_setting('app.tenant_id', true), '')::bigint)

The Java side binds that value after a Spring transaction is active. Source anchor: libs/common/src/main/java/com/lumie/common/tenant/RlsTenantTransactionBinder.java.

SELECT set_config('app.tenant_id', ?, true)

RlsTenantTransactionBinder owns this statement. The binder refuses to run without an active transaction, so is_local=true always means PostgreSQL clears the setting automatically on commit or rollback.

Runtime Flow​

Why Slug And ID Are Separate​

  • tenantSlug is the product-facing identifier used in URLs, JWT claims, logs, object keys, and worker headers.
  • tenantId is the database-facing identifier used in tenant_id columns and RLS predicates.

Setting only the slug is insufficient for data access. Source anchor: libs/common/src/main/java/com/lumie/common/tenant/TenantContextHolder.java. This is why code that re-enters tenant context should use:

TenantContextHolder.withinContext(slug, tenantId, action)

instead of only setTenant(slug).

Where Context Must Be Re-Established​

Current code restores tenant context explicitly in these boundary cases:

  • exam RabbitMQ callback listeners
  • homepage public lookup after customId -> tenant resolution
  • /internal/** HMAC-authenticated requests
  • Spring Modulith listeners that run after the original request thread is gone
  • scheduler loops over TenantService.listActiveTenants()
  • AI worker callbacks and tool execution paths

Entity And Table Patterns​

Most tenant-scoped aggregates extend TenantScopedEntity, which writes tenant_id at @PrePersist.

Some tables still manage tenant_id directly instead of using the base class. A current example is modules/staff/domain/entity/StaffPermission.java, which sets tenant_id explicitly in its own @PrePersist.

Safety Guards And Known Failure Modes​

  • If TenantContextHolder has no tenant ID, tenant-scoped rows are invisible.
  • If any path tries to bind app.tenant_id without an active transaction, RlsTenantTransactionBinder fails fast before repository access. Without that guard, the GUC could disappear after one statement and RLS would stop seeing the intended tenant.
  • If a new tenant-scoped table is added without tenant_id, RLS enablement, FORCE ROW LEVEL SECURITY, and the tenant_isolation policy, the table is a cross-tenant leak.
  • If the runtime DB role has SUPERUSER or BYPASSRLS, RLS is silently bypassed; RuntimeDbRoleGuard blocks startup to prevent that.
  • V65__repair_read_receipt_and_file_download_rls.sql shows a real repair pattern for tables that landed without tenant-safe constraints or policies.

Read-Only Replica Does Not Change The Tenancy Model​

The backend's read/write split changes which Hikari pool is used, not how tenancy works:

  • write and non-transactional code -> primary pool
  • @Transactional(readOnly = true) -> readonly pool
  • both pools still rely on the same app.tenant_id binding and runtime role

Approved programmatic transaction paths follow the same rule by calling RlsTenantTransactionBinder inside the transaction before tenant-scoped queries. The AI module's dedicated readOnlyJdbcTemplate still calls set_config('app.tenant_id', ?, true) explicitly for raw SQL reads; treat that as a legacy exception, not a pattern for new data access.

Verification Commands​

cd /Users/bluemayne/Projects/Lumie/lumie-backend
./gradlew integrationTest
./gradlew :app:test
./gradlew :libs:common:test

Most relevant tests:

  • app/src/test/java/com/lumie/app/migration/MigrationsRlsIntegrationTest.java
  • app/src/test/java/com/lumie/app/config/RlsTenantTransactionBinderIntegrationTest.java
  • app/src/test/java/com/lumie/app/config/RuntimeDbRoleGuardTest.java
  • app/src/test/java/com/lumie/app/SmsDispatchServiceWiringTest.java
  • libs/common/src/test/java/com/lumie/common/tenant/RlsTenantContextAspectIntegrationTest.java