13 — Tenancy II: the database enforces it

Read this first: lesson 12 put a tenant filter in the application. This lesson reads the layer underneath it — the migration arc that taught the schema about tenants, the row-level security policies that make Postgres refuse to serve another company's rows, and the provisioning service that creates a tenant in three transactions and keeps it suspended until it is whole. By the end you can read one RLS policy and say why the app-layer filter alone was not enough, and explain how a tenant is born and why it is born suspended. Code is quoted inline so you can read this without the repository open.

Time: about 40 minutes. Assumes lesson 12.

Eleven migrations, one arc

The retrofit is V95 through V105, and each migration is one deliberate step:

Migration	One line
V95	The tenant registry table; tenant 1 is the default every existing row will point at.
V96	tenant_id on 83 tenant-owned tables, backfilled to 1 with a temporary DEFAULT 1.
V97	users.tenant_id, nullable — NULL marks a platform account with no tenant at all.
V98	Natural-key unique constraints re-scoped per tenant, so "Finance" is not claimed estate-wide.
V99	The four settings singletons become one-row-per-tenant, enforced by unique constraints.
V100	Drops the temporary default now that Hibernate stamps the value.
V101	Row-level security: the app_runtime role, one policy per table, security_invoker views.
V102	user_log.tenant_id goes nullable so a platform login can be audited at all.
V103	The Platform permission category and the Super Admin role that holds it.
V104	The seven standard roles become tenant-less blueprints that provisioning clones.
V105	The TRIAL status and trial_ends_at, for self-serve signup.

V100 is four words of SQL per table, but its header is the arc in miniature — the scaffolding that made the retrofit safe becomes the thing to remove:

V96 added the column with a default so that inserts kept working while nothing knew about tenancy. That default is now a liability rather than a safety net: any write path that escapes the Hibernate mapping would quietly file another company's row under tenant 1, and the row would look perfectly ordinary forever after. Without the default the same mistake is a NOT NULL violation at the moment it happens.

And V96's header answers the question every multi-tenant design has to answer out loud — what do tenants share:

-- Intentionally NOT tenant-scoped (see docs/adr/0013-shared-db-multitenancy.md):
--   permission                   -- the fixed permission catalogue, identical for every tenant.
--   sss/philhealth/pagibig_contribution_rates, withholding_tax_brackets -- statutory rates. These
--     resolve cohorts through correlated max(effective_date) subqueries with an earliest-cohort
--     fallback; adding a tenant dimension would silently hand a tenant with no override of its own
--     the wrong cohort, so they stay shared and their write endpoints move to the platform role.
--   users, refresh_tokens        -- both are resolved before a tenant context can exist.
--   role_permission, user_role   -- join tables, scoped transitively through their owners.
--   billing_webhook_event        -- platform-level idempotency ledger.

The statutory rates are law, not company data — you meet their cohort mechanism in lesson 18 — and users must stay global because login happens before anyone knows which tenant is asking.

Why the filter was not enough

V101's header makes the case for a second layer, and it is worth reading whole:

Hibernate scopes every query it generates, but six reporting and portal services talk to the database through JdbcTemplate, and twenty analytics views join base tables directly. None of that passes through the ORM, so none of it is scoped by anything except a human remembering to write a predicate. That is a poor place to put the only line of defence for payroll data belonging to different companies: the failure mode is silent, and the person who forgets will be someone adding a report two years from now.

With these policies the database refuses to return another tenant's rows regardless of what SQL asks for them. A forgotten predicate stops being a leak and becomes an empty result — visibly wrong, rather than invisibly wrong.

That last sentence is the design in nine words. You cannot make forgetting impossible; you can change what forgetting costs.

One policy, read once

V101 then creates the same policy 85 times, once per tenant-owned table. Read the first and you have read them all:

ALTER TABLE announcement ENABLE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON announcement
    USING (current_setting('app.bypass_rls', true) = 'on'
           OR tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer)
    WITH CHECK (current_setting('app.bypass_rls', true) = 'on'
           OR tenant_id = NULLIF(current_setting('app.tenant_id', true), '')::integer);

USING filters what reads return; WITH CHECK rejects writes that would land outside the tenant. Both compare tenant_id against a session variable, not a database user — as the header says, "the application pools connections across tenants; TenantAwareDataSource sets it on every checkout" (lesson 12's checkout hook). The NULLIF(..., '') turns an unset variable into NULL, and tenant_id = NULL is never true — in the comment's words, "app.tenant_id unset matches nothing, so a connection that never established a tenant sees an empty database rather than everyone's." The app.bypass_rls escape hatch is what global scope sets for the platform operator.

One DO-loop at the bottom closes the last gap:

-- Views run with their creator's privileges by default, which would make every analytics view a
-- hole straight through the policies above. security_invoker makes them honour the caller's, so
-- the twenty reporting views inherit tenant scoping from their base tables without a single one
-- needing to be rewritten.
DO $$
DECLARE v record;
BEGIN
    FOR v IN SELECT c.relname FROM pg_class c JOIN pg_namespace n ON n.oid = c.relnamespace
             WHERE n.nspname = 'public' AND c.relkind = 'v'
    LOOP
        EXECUTE format('ALTER VIEW public.%I SET (security_invoker = true)', v.relname);
    END LOOP;
END
$$;

Predict: two thought experiments. World one: RLS stays enforced, but you delete the Hibernate discriminator from every entity. World two: the discriminator stays, but the application connects as the database owner, which bypasses RLS. What breaks in each world, and how loudly?

Resolve: in world one, reads still come back correctly scoped — the policies filter Hibernate's SQL like anyone else's. But nothing stamps tenant_id on insert anymore, and V100 removed the default, so every write dies as a NOT NULL violation. Loud, immediate, unmissable. In world two, every ORM path still behaves — the filter is doing its job — but the raw-JDBC reports and the analytics views lose the only thing scoping them, and nothing errors anywhere. The two layers fail in opposite directions: the database's absence is silent, the application's is loud. That is what defense in depth buys, and it is why the silent failure gets a dedicated alarm.

The alarm for the silent failure

That alarm is RowLevelSecurityCheck, one of lesson 03's ordered ApplicationRunners — this is its payoff. Its javadoc names the exact trap:

Row-level security is what scopes them, and it is enforced by the database only for a role that does not bypass it — connect as the owner and every policy silently becomes a no-op while the application carries on looking healthy. That is precisely the failure this exists to make audible: a dashboard showing another company's headcount looks like data, not like an error.

It asks Postgres one question — does current_user have rolsuper OR rolbypassrls — and logs the answer at every startup, WARN when isolation is off. The multi-tenancy ADR records why this runner exists, the hard way: the policies deploy inert because the owning role bypasses them, and "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 one environment variable, and now the logs say which side of it a deployment is on.

A tenant is born suspended

Enforcement settled, the other half of this lesson is creation. TenantProvisioningService's class javadoc is the whole design:

Deliberately not a single transaction. Hibernate fixes a session's tenant when the session opens — the right behaviour, since one request serves one company — so a method that created the tenant and then seeded it could not switch scope partway. Provisioning is therefore three transactions: reserve the tenant globally, fill it in inside its own scope, then release it.

The tenant is born suspended and only activated once seeding has finished. A half-provisioned company is one whose administrator can log in to a workspace with no payroll settings and no positions — functional-looking and broken. Suspension keeps everyone out until it is whole, and a failure part-way leaves it that way rather than pretending otherwise.

The three private methods carry the same story, one javadoc line each:

Transaction one, global: claim the slug and read the templates the next transaction needs.
Transaction two, inside the new tenant: everything the company will actually work with.
Transaction three, global: the tenant is whole, so let its people in.

Transaction one also reads the V104 role blueprints, and hands them to transaction two as RoleBlueprint records — plain data on purpose. The record's javadoc: "blueprints belong to no tenant and are only visible globally, while the copy has to be created in the new tenant's scope, in a separate transaction with its own Hibernate session. Carrying a managed entity across that boundary would either detach it or drag its original tenant along." And if seeding throws, the catch block does nothing clever: "Left suspended on purpose: the tenant exists but is not fit to be used, and saying so is more useful than deleting the evidence."

The mail, the password, and the one orchestrator

Only after transaction three commits does the welcome email go out, and the comment above the call explains both halves of that ordering:

        // Deliberately after the last transaction has committed: an email announcing a workspace
        // that a rollback then took away is worse than no email. MailService does not throw, which
        // matters most on the operator path -- there the password exists only in this method's
        // locals and in an Argon2id hash, so an exception escaping now would destroy the one
        // readable copy and lock the new administrator out of the workspace permanently.

The generated password itself is sixteen characters drawn from an alphabet with no confusable characters — no capital i, lowercase L, capital o, or the digits that mimic them — because, as its javadoc puts it, this password "is read off a screen and typed by hand, often transcribed in between, and a character someone cannot tell apart is a support call."

Two callers reach this code — the operator creating a workspace and a customer signing themselves up — and the differences travel through a ProvisioningOptions record instead of a second method. Its javadoc says why: the orchestration encodes three non-obvious contracts, and "a second orchestrator would restate all three, and they would drift. So the orchestration has one copy and the differences travel through here."

The operator side

A platform account is a row in users with tenant_id NULL — V97's convention, "platform accounts (super admins) that operate across all tenants" — and a null tenant is what puts a request into global scope. What such an account may do is V103's business, and its header calls its own category choice load-bearing: every existing role grant is category-based, "so a new category can never leak into a tenant role by accident. The reverse is equally deliberate — Super Admin gets exactly these three permissions and nothing from the tenant domain. Operating the platform and reading a tenant's payroll are different powers, and the second is not implied by the first." There is deliberately no impersonation feature: the Super Admin role is "Not a blueprint: provisioning never clones this into a tenant", and it holds nothing a tenant screen would honour.

Suspension, and the one hand-scoped table

Suspending a tenant only means something if its people are actually kept out, and that check lives in UserDetailsServiceImpl.isTenantActive — because, its javadoc argues, "the check belongs where every authentication path already passes — here, one lookup per principal load, rather than scattered through the login controller and the refresh flow and the WebSocket handshake." And: "A tenant that has gone missing is treated as inactive: the safe reading of a dangling reference is that the company should not be served."

One table opts out of the automatic discriminator: user_log. Its tenantId is "deliberately a plain column rather than @TenantId", because a super admin's login must be auditable and Hibernate's discriminator "would stamp the global sentinel — which is not a real tenant, so the foreign key rejects it and the audit write that every login performs fails mid-authentication. Null says 'no tenant' honestly." The price is paid in UserLogSpecifications.currentTenant(), whose javadoc admits the scoping "is written here, and this is the only place it exists. Anything that queries user_log without it returns every tenant's history." One hand-written predicate is the whole of that table's application-layer isolation — RLS still backstops it underneath.

See it refuse with your own eyes

The pgAdmin guide registers the same database twice — once as the owner, once as app_runtime — and its demo is a ready-made exercise. Connect as app_runtime:

SELECT count(*) FROM employee;   -- 0
SELECT set_config('app.tenant_id', '1', false);
SELECT count(*) FROM employee;   -- 101, for the default tenant

In the guide's words, that first zero "is not an empty database. It is row-level security doing its job." Re-run set_config with a different id to become a different tenant — if a query ever returns another tenant's rows there, isolation is broken and you have found a real bug.

Where this shows up in MotorPH

• V96__tenant_id_columns.sql and V100__drop_tenant_id_defaults.sql — the column, the shared-table list, and the default's removal.
• V101__row_level_security.sql — the 85 policies, app_runtime, and the security_invoker loop.
• V103__platform_permissions.sql — the Platform category and Super Admin.
• RowLevelSecurityCheck.java — the startup alarm; the war story behind it is in the multi-tenancy ADR.
• TenantProvisioningService.java, RoleBlueprint.java and ProvisioningOptions.java — the three transactions and the one orchestrator.
• UserDetailsServiceImpl.java — isTenantActive; UserLog.java and UserLogSpecifications.java — the one hand-scoped table.

Recap

• Two layers, opposite failure modes. The app filter fails loud on writes; missing RLS fails silent on reads. A forgotten predicate becomes "an empty result — visibly wrong, rather than invisibly wrong."
• A safety default becomes a liability. V96's DEFAULT 1 kept the upgrade safe; V100 removes it so a missed write path is a NOT NULL violation, not a silent tenant-1 row.
• RLS is only enforced for roles that do not bypass it. RowLevelSecurityCheck says at every startup which side of that line the deployment is on — because inert-but-deployed once served one tenant another's dashboard.
• A tenant is born suspended, provisioned in three transactions because Hibernate fixes a session's tenant at open, and released only when it is whole — never "functional-looking and broken."
• Platform power is not tenant power. Super Admin holds three Platform permissions and nothing from the tenant domain; reading a tenant's payroll "is not implied" by operating the platform.

Next: 14 — Schedulers, sockets, and mail: the work between requests.