Multi-tenant isolation belongs in the data layer, not in your queries

Multi-tenancy sounds like a feature. It is closer to a structural property, like whether a building has foundations, and it is priced accordingly: cheap at the start, brutally expensive once real customer data is in the database.

Nearly every tenant data leak has the same shape. Not a sophisticated attack — one query, written on a Thursday, that forgot WHERE tenant_id = @tenant. It passes review because reviewers read for logic, not for a missing clause. It passes tests because the test database has one tenant.

Remembering is not a strategy

If correctness depends on every developer remembering a filter on every query forever, the system will leak. Not because anyone is careless, but because that is what happens to any rule enforced by attention rather than structure.

So the question is not "how do we make sure people remember". It is "where do we put the boundary so forgetting is impossible".

Three places the boundary can go

Database per tenant. The strongest isolation and the simplest mental model. It becomes painful at scale: migrations run N times, connection pools multiply, and cross-tenant reporting turns into an ETL project. Good for a small number of large, high-assurance customers; poor for many small ones.

Schema per tenant. Fewer databases, still real separation. Migration cost is the same problem in a smaller coat, and PostgreSQL will not thank you for thousands of schemas.

Shared schema with a tenant column. The default for most B2B SaaS. One migration, one pool, straightforward reporting — and the entire safety argument rests on the filter always being applied. This is the model worth getting right, because it is the one most products land on.

Two layers, and why one is not enough

In EF Core, a global query filter applies the tenant predicate to every query for an entity automatically. That removes the class of bug where someone forgets. It is the first layer and it does most of the work.

It is not sufficient on its own, for reasons worth knowing before you rely on it:

• Raw SQL bypasses it entirely.
• IgnoreQueryFilters() exists, and is exactly the kind of thing added during a debugging session and left behind.
• It filters reads. Writes are your problem — nothing stops code setting the wrong tenant id on insert, or updating a row it fetched with a filter that was ignored.
• Anything reaching the database outside the ORM — a migration script, an admin tool, a reporting job — is unaffected.

The second layer is in the database. PostgreSQL row-level security enforces the predicate regardless of who is asking or how, so raw SQL and stray tooling are covered by the same rule. The cost is operational: the tenant context has to be set per connection, which requires care with pooling, and a policy that is subtly wrong is harder to debug than a missing WHERE clause.

A reasonable position for most teams: query filters as the working layer, RLS as the backstop on the tables that would actually hurt.

Resolve tenancy once, early, and never from the payload

The tenant should be established once per request — from the authenticated principal, at the edge of the API, before any handler runs — and carried through as ambient context.

Never take it from the request body or a query parameter. A tenant id the caller can set is not a boundary; it is a suggestion. This sounds obvious and still shows up regularly, usually on an endpoint written for an internal tool and later exposed.

The parts people skip

• Background jobs. There is no request, so there is no ambient tenant, so the filter quietly resolves to nothing. Jobs need the tenant passed explicitly and asserted, not inferred.
• Caching. A cache key without the tenant in it will eventually serve one customer another customer's data — and it will look like a ghost, because the database was never wrong.
• Uploaded files. Isolation in the database means little if documents sit in one bucket behind guessable keys.
• Background exports and emails. The place a leak becomes external rather than theoretical.

Test it with two tenants, always

A test suite with one tenant cannot detect a missing filter — every query returns the right rows by accident. Seed two, and assert that tenant B's requests cannot see tenant A's records, including through search, exports, and any endpoint that takes an id.

That last one is worth a dedicated test. Fetch-by-id is where filters are most often skipped, because the id feels specific enough to be safe on its own. It is not.

If you already shipped without it

Not a lost cause, but be realistic: it is a migration, an audit of every data path, and some downtime. Add the column and backfill it, put the filter in at the data layer, then go looking for everything that bypasses it — raw SQL, jobs, caches, files. Enable RLS last, on the tables that matter most, once the application is already behaving.

The order matters. Turning on enforcement before the application is correct produces a system that fails in production instead of one that leaks quietly, which is better, but not on a Tuesday afternoon with customers watching.