Isolation Is a Contract: Multi-Tenant Data Boundaries

This is the last post in the series, and it is about the promise with the least tolerance for being broken. Most contract violations cost you a bug report or an awkward migration. This one costs you a breach notification. In any system where more than one customer’s data lives in the same place, isolation is the promise that one tenant never sees another’s data, and unlike most of the contract it is not enough for it to usually hold. It has to hold on every query, including the one a tired developer wrote on a Friday.

That last clause is the whole problem, and it is why isolation has to be enforced by the database, not hoped for in application code.

Three Ways to Separate Tenants

There is a spectrum of how physically separate you keep tenants, and Microsoft’s guidance on multitenant SaaS patterns lays it out well.^[1] The trade is always the same: stronger isolation costs more per tenant and scales to fewer of them; denser sharing is cheaper and scales further but puts more weight on getting the logical boundary exactly right.

Database per tenant. Each tenant gets its own database. Isolation is as strong as it gets – a query in one database simply cannot reach another – and you can restore, move, or size a single tenant independently. The cost is operational: a thousand tenants is a thousand databases to patch, back up, deploy schema changes to, and monitor. This model fits a smaller number of larger tenants, especially where one of them can demand its data be physically separate.

Schema or table set per tenant. One database, with each tenant’s data in its own schema. Cheaper than a database each, still a clear boundary, but schema changes now have to be applied many times within one database and the count still grows with tenants. It is a middle ground that fits fewer situations cleanly than either neighbor, and it tends to be a stop on the way rather than a destination.

Shared schema, tenant key per row. All tenants share the same tables, and every tenant-owned row carries a tenant_id. This is the densest and cheapest model, the one that scales to tens of thousands of tenants on shared infrastructure, and it is the default for most SaaS at scale. It is also the one where isolation stops being physical and becomes entirely logical – a predicate that has to be on every single query that touches a shared table. Which is exactly the thing humans cannot be trusted to do perfectly forever.

Isolation by Hope Does Not Scale

In the shared-schema model, the naive approach is a rule: “every query must include WHERE tenant_id = @current_tenant.” It works right up until it does not. Someone writes a report and forgets the clause. An ORM generates a query that filters in the application after fetching. A new join pulls in a table whose tenant column nobody added to the predicate. A bug in how the current tenant is resolved sets it to the wrong value. Every one of those is a cross-tenant data leak, and every one of them is a single missing or wrong predicate away at all times.

This is the same lesson as the idempotency post, where the unique constraint, not the procedural check, was the real guard. A promise that depends on every developer remembering to write the same clause on every query is not enforced; it is merely encouraged. To make isolation part of the contract you have to move it to where it cannot be forgotten: into the database engine, applied to every query automatically whether the author remembered it or not.

Row-Level Security: The Engine Enforces the Boundary

Row-Level Security does exactly that. You write a predicate function that decides which rows the current context may see, attach it to the table through a security policy, and from then on SQL Server applies it to every query against that table, transparently, with no change to the query text.^[2] The developer who forgets the WHERE clause no longer leaks data, because the engine adds the boundary they forgot.

The current tenant is carried in session context – the same mechanism the previous post used to capture who made a change – set once when the application opens its connection.^[3]

Two things in that policy matter. The filter predicate silently removes other tenants’ rows from every read, so a SELECT with no tenant clause returns only the current tenant’s rows. The block predicate stops a write from inserting a row stamped with someone else’s tenant_id, closing the gap where a leak becomes a corruption. Together they make isolation a property of the table, not of the queries against it.

Row-Level Security Is Not a Force Field

It would be dishonest to present RLS as the end of the conversation, so here are the edges. It depends utterly on the session context being set correctly and on the application connecting as a principal the policy applies to; if the app pools connections, it must set the tenant on every checkout, because a pooled connection can carry the previous borrower’s context. A principal with enough privilege can be exempted from the policy, so your application login must not be one that bypasses it. RLS predicates run on every query and a careless one can hurt performance, so the predicate and the tenant_id column want to be indexed and simple. And researchers have shown that side channels – error messages, timing, unique-constraint violations – can sometimes let a determined attacker infer the existence of rows they cannot read, so for the highest isolation requirements a physical boundary still beats a logical one.

None of that is a reason to skip RLS. It is a reason to treat it as the enforcing layer of a defense in depth, not the only layer – the database-side guarantee underneath an application that still tries to do the right thing, exactly the posture this whole series has argued for.

The Whole Series, in One Idea

Multi-tenant isolation is a fitting place to end, because it is the contract idea at its most consequential. Across eight posts the database has turned out to be a dense stack of promises, most of them invisible in a column list:

• The shape of the published interface, versioned and deprecated on purpose rather than reshaped on a whim.
• The read surface an ORM binds to, kept stable so the tables underneath stay free.
• The promise that the contract can be deployed the same way twice and proven correct before it ships.
• The behavioral promises that span calls: safe retries, reliable events, an honest account of the past, and now a boundary between tenants.

The through-line is a single posture. A database is not a private pile of tables you reshape at will; it is a set of promises that other code, other teams, and other companies are built on. The difference between a system that is calm to change and one that breaks every Thursday is whether those promises are written down, versioned, tested, and – wherever the engine will let you – enforced by the database itself rather than by every developer remembering. Treat the schema like the contract it already is, and most of the 2 a.m. pages in this series simply never get sent.

That is the end of the series. If you arrived here in the middle, it starts at Your Database Is an API.

How does your team enforce tenant isolation, and how much of your database contract is written down versus remembered? I would genuinely like to hear about it on Bluesky or LinkedIn.

References and Footnotes

1. Multitenant SaaS Patterns – Microsoft Learn. The data-architecture models for multitenant SaaS – database per tenant, sharded multi-tenant, and shared – and the isolation, cost, and scale trade-offs among them. ↩
2. Row-Level Security – Microsoft Learn. How filter and block predicates attached through a security policy restrict the rows a query can read or write, enforced transparently by the engine. ↩
3. SESSION_CONTEXT (Transact-SQL) – Microsoft Learn. Reading the session-scoped tenant identity set by sp_set_session_context, which the Row-Level Security predicate uses to decide row visibility. ↩