# Multi-Tenant Data Isolation: Patterns That Actually Hold

How to keep tenants apart in a multi-tenant SaaS: server-derived tenant context, per-tenant role enforcement, why membership is not authorization, and append-only financial ledgers.

Multi-tenant data isolation is one of those requirements that sounds solved until you look closely. "Every query filters by tenant_id" fits on a slide. In a real system — dozens of API routes, multiple user roles, background jobs, admin tooling, financial ledgers — isolation is a property you have to enforce at every layer, and the failure modes are subtle. In a platform with per-merchant data, role-based access, and money-adjacent ledgers, these are the patterns that hold, and the failure mode that should worry anyone running multi-tenant software.

## 1. Tenant Context Is Server-Derived, Never Client-Supplied

The foundational rule: **the tenant identifier used in every query must come from the server-side session, never from the request.** No `tenantId` in the request body, no tenant in the URL that gets trusted, no tenant claim in a client-constructed object.

In practice this means middleware resolves the tenant before the route handler runs: the authenticated user's session yields their tenant memberships, the requested tenant is checked against those memberships, and a `TenantContext` object carrying the verified tenant ID is attached to the request. Every downstream query reads from that context.

The failure mode this prevents is obvious once stated: if a route reads `tenantId` from the request body, any authenticated user can substitute another tenant's ID and read their data. This pattern shows up in code reviews more than once — usually in an endpoint someone added quickly, where the developer "knew" the frontend only sends the right value. The frontend is not a security boundary. Enforce it in middleware, uniformly, and make the safe path the easy path: route handlers should not even have access to an unverified tenant identifier.

## 2. Membership Is Not Authorization

This is the failure mode that changes how multi-tenant authorization has to work. A system can check tenant membership correctly — every route verified the user belonged to the tenant. But **membership was the only check.** Once you were a member of a tenant, you could act with any role in that tenant.

Concretely: a user who was a `pos_operator` (cashier-level: verify redemption codes, nothing else) in Tenant A could call merchant-admin endpoints in Tenant A — view analytics, change reward rules, manage the team — because the authorization check asked "is this user a member?" and stopped there. It never asked "with what role?"

The fix is to thread the user's **per-tenant role** through the entire request context. The session loader returns membership as `{tenantId, role}` pairs, not just tenant IDs. Middleware resolves the role for the active tenant and attaches it alongside the tenant ID. Protected routes declare the minimum role they require — `requireTenantRole("client")` — and admin users bypass through a separate, explicit path.

Three lessons from this:

- **Test authorization with multiple roles, not just authenticated vs. anonymous.** The standard test — "logged out gets 403, logged in gets 200" — passes on a system with this hole. You need "logged in as operator gets 403 on admin routes."
- **Roles must be resolved per tenant, per request.** A user can be an admin in one tenant and an operator in another. A global role on the user record is wrong the moment anyone belongs to two tenants with different privileges.
- **Privilege-bearing data needs special handling in destructive operations.** When merging duplicate user accounts, for example, transferring tenant memberships automatically can silently grant roles nobody reviewed. Membership transfers on merge should be logged for manual review instead of applied automatically. Anything that moves privileges should require a human decision.

## 3. Enforce Isolation in the Database, Not Just the Application

Application middleware is the first line of defense, but it is one deploy, one missed route, one raw SQL query away from a leak. Defense in depth means the database enforces what the application intends:

- **Row-level security (RLS) policies** on tenant-scoped tables, so that even a query that forgets the tenant filter returns only the current tenant's rows. Enabling RLS without policies locks everyone out — the policies must be designed alongside the deployment role architecture, which is why this is a deliberate design step, not a checkbox.
- **Foreign keys from every tenant-scoped table to the tenant table**, with `NOT NULL` constraints. A ledger row or event row that can exist without a tenant is a row that can leak across tenants in an aggregate query.
- **Composite indexes on `(tenant_id, ...)`** for the hot paths — transactions by tenant and time, ledger entries by tenant and wallet. Isolation that is slow gets bypassed "temporarily" under load, and temporary bypasses become permanent.

## 4. Append-Only Ledgers for Anything Money-Adjacent

If your system tracks balances, points, credits, or any value that behaves like money, the ledger must be **append-only**: rows are inserted, never updated or deleted. Corrections are new rows — compensating entries — not edits to history.

We learned the sharp edge of this during a user-account merge: the original implementation rewrote the `wallet_id` on historical ledger rows to point at the surviving account. That is editing financial history. The corrected approach moves the balance with explicit transfer entries (a debit from the closed account, a credit to the surviving account) and marks the old account closed, leaving every historical row untouched.

Append-only gives you three properties that updates cannot:

- **Auditability.** The full history of how a balance reached its current value is reconstructable from the ledger alone. Regulators, auditors, and angry merchants all ask this question eventually.
- **Reconciliation.** A nightly job can sum the ledger per wallet and compare against the balance snapshot; any drift is detectable and explainable. If rows can be edited, reconciliation proves nothing.
- **Incident recovery.** When a bug credits the wrong amount, the fix is a compensating entry with a reason code — visible, reviewable, reversible. Editing the row destroys the evidence of both the bug and the fix.

Pair the ledger with a balance snapshot table for fast reads, but treat the snapshot as a cache: the ledger is the source of truth, and a reconciliation job keeps them honest.

## 5. Test Isolation Like an Attacker

The test suite for a multi-tenant system needs a dedicated isolation section, not just coverage of happy paths:

- **Cross-tenant read attempts** on every list and detail endpoint: user from Tenant A requests Tenant B's data, expects 403 or empty.
- **Cross-tenant write attempts**: same, for creates and updates.
- **Role escalation within a tenant**: operator attempts admin actions (the hole from section 2).
- **Aggregate endpoints**: analytics, search, and admin views are where tenant filters get forgotten, because the query is complex and the filter is one more predicate.
- **Background jobs and workers**: they run outside the request middleware, so they need their own tenant-scoping discipline — verify that a job processing Tenant A's events cannot touch Tenant B's rows.

## The Principle

Tenant isolation is not a feature; it is a property of every query, every route, every job, and every schema decision. The patterns above — server-derived context, per-tenant roles, database-level enforcement, append-only financial records, adversarial testing — are how you make the property hold when the system grows past the point where any one person can review every query. Build them in from the start. Retrofitting isolation onto a system that assumed it is one of the most expensive refactors in SaaS, and the alternative is finding out from a customer.

For the webhook ingestion pipeline that feeds tenant-scoped data — signature verification, idempotency, raw payload storage — see our [webhook reliability patterns](/guides/webhook-reliability-patterns/). If you are designing a multi-tenant architecture or auditing an existing one for isolation gaps, review our [engagement models](/engagements/) or [contact us](/contact/).
