Skip to main content

13. Shared-database multi-tenancy

Date: 2026-07-27

Status​

Accepted. Supersedes ADR-0007 for the hosted SaaS offering only — a per-client stack remains a valid deployment and simply runs with one tenant in the shared schema.

Context​

ADR-0007 gave every client its own Postgres, backend and frontend, and recorded that "the schema has zero tenant scoping — none of the tables carries a tenant_id, and no query filters by tenant." That is cheap to reason about and expensive to run: onboarding a client means provisioning a stack, and every client multiplies the fleet to upgrade, back up and monitor.

Serving many companies from one stack means the schema has to be able to say which company a row belongs to, and every read has to honour it. The three usual shapes are a shared schema with a discriminator column, a schema per tenant, or a database per tenant. The last two push the cost into operations — migrations fan out, connection pools multiply — which is the thing we are trying to escape.

Decision​

A shared database with a tenant_id discriminator, enforced in two independent layers.

Tenant registry and the default tenant​

A tenant table holds one row per customer company. Tenant 1 (default) owns every row that predates this work, so an existing single-tenant deployment upgrades in place and behaves identically. That invariant is what let the migration land in stages rather than as one commit.

What is scoped, and what deliberately is not​

Almost everything is tenant-owned. The exceptions are each a deliberate answer to "would two companies share these rows?", and TenantCoverageTest fails the build if a new entity does not answer it.

  • Statutory rate tables (SSS, PhilHealth, Pag-IBIG, withholding tax) stay global. Their cohort lookups resolve through correlated max(effective_date) subqueries with an earliest-cohort fallback; adding a tenant dimension would silently hand a tenant that has not defined its own override the wrong bracket, and the visible symptom would be a slightly wrong deduction on a real payslip. Their write endpoints move to the platform operator.
  • de_minimis_benefit_types is tenant-owned, despite sitting alongside those tables. It has no cohort mechanism at all: it is a plain editable list, any tenant's payroll admin can change it, and its ceilings feed taxable income. Shared, one tenant lowering the rice-subsidy ceiling would move every other tenant's withholding tax.
  • users and refresh_tokens carry a tenant but are never filtered by one, because both are resolved before a tenant can be known. Usernames and emails stay globally unique for the same reason: login identifies an account before it identifies a company, and the lockout bookkeeping resolves accounts by bare username.
  • role is tenant-owned with a nullable discriminator. A null marks a blueprint — the template each tenant's copy of a standard role is cloned from — so tenant admins keep the existing role-editing screen without editing everybody's roles.

Layer one: Hibernate​

Tenant-owned entities extend TenantOwned, whose @TenantId field makes Hibernate stamp tenant_id on insert and add where tenant_id = ? to every query it generates, find() included. The resolver refuses to guess: with no tenant established it throws rather than defaulting, because the plausible default is how one company reads another's payroll.

Scope is opened outermost in the filter chain, starting global — resolving who is calling is itself database work — and narrowed once the principal is known. It is always cleared, because request threads are pooled.

Layer two: PostgreSQL row-level security​

Hibernate cannot help the six services that query through JdbcTemplate or the twenty analytics views that join base tables directly. Those depend on a human writing a predicate, which is a poor place for the only line of defence: the failure is silent, and the person who forgets will be someone adding a report years from now.

So the database enforces it too. Every tenant-owned table has a policy reading an app.tenant_id session variable, set on each connection checkout because connections are pooled across tenants. The analytics views are security_invoker, so they inherit scoping from their base tables and none of them needed rewriting. A forgotten predicate now returns nothing instead of everything — visibly wrong rather than invisibly wrong.

The application connects as an unprivileged app_runtime role, since owners and superusers bypass RLS. Flyway keeps the owning role, because a migration has to be able to see every tenant's rows.

Provisioning and the platform operator​

A tenant row on its own is not a company. Six services refuse to run without a payroll settings row, an employee cannot exist without a position to hold, and a CRM pipeline with no stages cannot record a deal — so creating a tenant means creating the reference data that makes it inhabitable, and the first account able to log in to it.

That runs as three transactions, not one, because Hibernate fixes a session's tenant when the session opens. This is the right behaviour — one request serves one company — but it means a single transaction cannot create a tenant and then work inside it. So: reserve the tenant globally, seed within its scope, activate globally.

The tenant is born suspended and activated only once seeding finishes. A half-provisioned company is worse than an absent one because it looks like it works: its administrator logs in to a workspace that cannot run payroll. A failure part-way leaves it suspended rather than deleting it, and every seeding step skips what already exists, so re-running finishes the job instead of duplicating it.

The platform operator is a user with no tenant at all (users.tenant_id IS NULL), holding the Platform permission category and nothing else. The category is what keeps the two worlds apart: every role grant in the seed migrations selects by category, so Platform cannot reach a tenant role by accident, and a tenant's own System Administrator — who holds every permission in their own world — holds none of these. Running a company and running the platform are different powers.

Because a platform account belongs to no tenant, it also gets its own shell rather than the ERP one: notifications, saved views and navigation preferences are all tenant-owned, so the app shell's assumptions do not hold for it.

user_log is the one tenant-owned table Hibernate does not filter. Its tenant is nullable so that a platform action — performed in global scope, which has no owning tenant — has somewhere to go; without that, the audit write every login performs fails the foreign key and authentication returns a 500. The cost is that its isolation is a single explicit predicate, UserLogSpecifications.currentTenant(), rather than something the mapping guarantees.

Consequences​

Good. One stack serves many companies. Onboarding becomes a row rather than a deployment. A tenant leak now requires two independent mechanisms to fail together, and the second one is enforced by the database rather than by convention. Test coverage exists for both, on a real Postgres.

Bad. A shared database means a shared blast radius: one bad migration, or one exhausted connection pool, affects everyone. Per-tenant backup and restore is no longer pg_dump of a container — that tooling has to be rebuilt around the discriminator. The app_runtime role is an operational step that has to exist in every environment.

Watch. Blueprint roles are a snapshot taken when V104 ran, so a future migration granting a new permission has to reach the blueprints and every tenant's copy — role.is_system marks which rows are clones of a standard role, which is how such a migration finds them. See ADR-0011.

Two further decisions were deferred rather than settled. billing_subscription and portal_user carry a tenant but are not filtered — the first because the webhook that writes it is unauthenticated, the second because portal login resolves it before a tenant is known; both are named in TenantCoverageTest so they cannot be forgotten. And no tenant-leading indexes have been added: with one large tenant the discriminator is not selective, so the right set should come from real query plans once there are several, not from guessing now.

Activation​

The policies deploy inert, because the owning role bypasses them. Setting APP_DB_USER=app_runtime and APP_DB_PASSWORD switches the application behind RLS, and unsetting them switches it back — a reversible configuration change rather than a migration, so it can be rolled out per environment and verified before anything depends on it.

Inert is not a safe resting state, and this was learned the hard way. The reporting and analytics screens are SQL views read through raw JDBC, which Hibernate's filter never sees; RLS is the only thing scoping them. Left inert, a freshly provisioned tenant's dashboard showed the default tenant's headcount, payroll totals and leave figures — as data, with no error anywhere. The gap between "RLS deployed" and "RLS enforced" is a single environment variable, and nothing was saying which side of it a deployment was on.

RowLevelSecurityCheck now reports at every startup whether the connected role bypasses RLS, at WARN when it does. A deployment that is not enforcing isolation says so in its logs rather than looking healthy while serving one tenant another's numbers.

Longer term the raw statements and views should also carry their own tenant predicates, so that isolation does not rest on one configuration flag. Until then, treat APP_DB_USER as required rather than optional in any deployment serving more than one tenant.