Multi-Tenancy Is Not a Schema Decision

Every article on multi-tenancy opens with the same three boxes. Shared database with a tenant_id column. A schema per tenant. A database per tenant. A comparison table follows — cost, isolation, operational overhead — and the article ends with "it depends."

That decision is real, but it's the easy one. You can make it in an afternoon, and whichever box you pick you can defend.

The hard part starts the day after. Say you chose a shared database. Every tenant's data now sits in the same tables, one missing clause away from every other tenant. And when that clause goes missing, nothing breaks. No exception is thrown. No 500 is returned. No test goes red. The query succeeds and returns rows — well-formed, correctly typed rows that belong to a different business. You don't find out from your logs. You find out when a customer asks why they can see a stranger's contact list.

A tenant boundary isn't a column. It's a guarantee, and a column can't make a guarantee.

I spent a year on a multi-tenant WhatsApp commerce platform — one codebase, one database, every business isolated from every other. 144 tables, 612 route handlers. This is about everything that comes after the schema decision.

The architecture question, briefly

Shared database, tenant_id column on every tenant-scoped table, one set of migrations, one deployment.

The case for it is operational. Schema-per-tenant and database-per-tenant buy stronger isolation by default and charge for it in work that scales with customer count rather than traffic: migrations that run N times, connection pools that fragment, backups and monitoring per tenant, and a slow onboarding path because provisioning a customer means provisioning infrastructure. A small team serving many small businesses cannot absorb that.

The trade-off is that a shared database gives you no isolation for free. Everything below is about earning it back.

Why `WHERE tenant_id = ?` isn't a boundary

The default way to isolate tenants in a shared database is a filter on every query:

SELECT * FROM contacts WHERE tenant_id = $1 AND id = $2;

This works. It is also not a boundary. It's a convention — and a convention holds only as long as every developer remembers it, in every handler, forever.

At 612 route handlers, that isn't a guarantee. It's a hope with good odds.

The failure mode is specific. The filter doesn't get forgotten often. It gets forgotten once, in one handler, by someone shipping a fix on a Friday evening. Maybe it's a new internal endpoint. Maybe it's a lookup by primary key, where id feels unique enough on its own — and it is unique, which is exactly why nothing complains. No test fails, because the test was written by the same person who forgot. Nothing is logged, because from the database's point of view nothing went wrong. The query did precisely what it was told.

If that sounds familiar, it's because I've written about this bug before in a different costume. In Authentication vs Authorization the mistake was that I filtered the contact list a user could see and called it authorization. The list was correct. The endpoint underneath it would act on any ID you sent it.

"Every query has a tenant filter" is the same claim, and it fails the same way. A filter applied by discipline is not enforcement. It's a list that happens to be right.

Resolve the tenant at the edge, not in the query

The strongest piece of that system had nothing to do with queries. It was about where the tenant came from.

An inbound WhatsApp message arrives from Meta carrying a phone-number ID. That ID belongs to exactly one tenant, and the lookup from phone-number ID to tenant isn't something a handler might remember to do — it's how the message gets routed at all. If you can't resolve the tenant, you can't process the message.

That shape matters more than it looks. The tenant stops being a parameter each handler is responsible for applying and becomes a fact established before any handler runs. By the time business logic sees the request, which tenant is this? has already been answered — by the plumbing, not by whoever wrote the handler.

The principle generalises past WhatsApp: resolve the tenant where the request enters the system, not where the query is written. A subdomain, an API key, a JWT claim, a webhook's account identifier — the mechanism varies, the shape shouldn't.

// Illustrative.
async function resolveTenant(req, res, next) {
  const phoneNumberId =
    req.body?.entry?.[0]?.changes?.[0]?.value?.metadata?.phone_number_id;

  const number = await db.phoneNumbers.findByMetaId(phoneNumberId);

  if (!number) {
    // Acknowledge, then drop. Meta retries any non-2xx response, so rejecting
    // a message you can never route turns one unroutable event into a retry
    // storm against your own webhook.
    logger.warn({ phoneNumberId }, 'webhook for unknown phone number');
    return res.sendStatus(200);
  }

  req.tenantId = number.tenantId;
  next();
}

One detail carries most of the weight: the mapping must be one-to-one in the direction that matters. A tenant may own several numbers. A number belonging to two tenants has to be impossible, not merely unlikely — and "impossible" needs something that enforces it.

ALTER TABLE phone_numbers
  ADD CONSTRAINT phone_numbers_meta_id_unique UNIQUE (meta_phone_number_id);

Application code can check whether a number is already assigned, but two requests racing to assign the same number can both pass that check and both proceed. The database can't be raced. Same reasoning as the unique constraints in preventing duplicate operations: when correctness depends on "only one of these can exist," say so in the schema, not in an if.

The write path is the dangerous direction

Resolution at the edge protects reads. Writes are worse.

If tenant A can send a message through tenant B's WhatsApp number, B's customers receive a message from a business they never contacted — carrying B's name. Note the asymmetry. A leaked read is a privacy failure between two of your customers. A leaked write reaches a third party who isn't your customer at all, with someone else's identity attached.

The lesson from the authorization post applies directly: the guard belongs at the action, not at the list. Filtering which numbers a tenant can see in the dashboard does nothing for the send endpoint underneath it. Anyone who can craft a request can put any number ID in the body, and a UI they never loaded is not a control.

// Illustrative.
async function assertCanSendFrom(tenantId, phoneNumberId) {
  const number = await db.phoneNumbers.findOne({ id: phoneNumberId, tenantId });
  if (!number) {
    throw new ForbiddenError('phone number does not belong to tenant');
  }
  return number;
}

And it has to be one function every send path calls — REST handler, queue worker, real-time socket, internal tooling. Copies of a security check do not stay identical. One gets a fix, the other doesn't, and the gap is invisible until someone finds it for you.

I know that one from experience. While fixing an authorization bug in a side project, I patched the REST route properly and then patched the WebSocket path with its own inline copy of the same rule. Within a single change the two versions already disagreed about who counted as an admin. One rule, two implementations, one commit — that's how fast drift happens.

Prefer a shape that can't express the bug

A test catches a bug after someone writes it. A shape stops it being writable. Given the choice, prefer the shape.

The clearest demonstration I've had of this was small. A function took two user IDs in an order that was easy to swap, and one call site swapped them. The fix wasn't a better test — it was restructuring the function so both halves took the subject ID first. Afterwards I deliberately mutated the code to swap the arguments back, and the swap was a semantic no-op. The bug had nowhere left to live.

Tenant isolation offers the same choice: make the unscoped query something you can't write rather than something you shouldn't.

Mechanism	Enforced by	Catches	Main cost
Query filter convention	Discipline	Nothing automatically	Free, and worth what you pay
Scoped repository	Application design	Anything using the repository	Escape hatch: raw client imports
Lint / CI rule	Static analysis	Raw queries, stray imports	False positives, blind spots
Composite foreign keys	Database	Cross-tenant writes	Schema churn; no help on reads
Row-Level Security	Database	Reads and writes, everywhere	Pooling, roles, debugging

Row-Level Security. The database enforces the filter, so application code can't forget it.

ALTER TABLE contacts ENABLE ROW LEVEL SECURITY;
ALTER TABLE contacts FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON contacts
  USING (tenant_id = current_setting('app.tenant_id', true)::uuid);

await client.query('BEGIN');
await client.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
// ...queries...
await client.query('COMMIT');

Strongest option, sharpest edges. Table owners and superusers bypass policies unless you FORCE them and connect as a separate role. With a connection pool, a session-level setting leaks into whichever request borrows the connection next — it must be transaction-local, which is what the third argument to set_config and the surrounding BEGIN are doing. And note current_setting(..., true): without that second argument an unset variable raises an error; with it, a missing tenant means the policy matches nothing and queries quietly return zero rows. Fail-closed, but baffling the first time you debug it.

A scoped repository. Handlers never touch the raw query builder. They receive a repository constructed with a tenant ID, and every method applies the filter. An unscoped query isn't forbidden — it simply isn't expressible through the API handlers are given.

Composite foreign keys. If foreign keys reference (tenant_id, id) rather than id alone, the database refuses to let a row in tenant A point at a row in tenant B:

-- The referenced columns need their own unique constraint first, or this
-- fails with "no unique constraint matching given keys".
ALTER TABLE contacts
  ADD CONSTRAINT contacts_tenant_id_id_unique UNIQUE (tenant_id, id);

ALTER TABLE conversations
  ADD FOREIGN KEY (tenant_id, contact_id) REFERENCES contacts (tenant_id, id);

This doesn't stop a bad read, but it eliminates a whole class of bad writes — the ones where a cross-tenant ID gets stored and is then trusted forever by everything downstream.

The exceptions are where isolation actually breaks

Here's the part the architecture diagrams never cover. Some code legitimately works across tenants:

Billing reconciliation that sums usage for every tenant
A nightly job that expires stale records platform-wide
Support tooling that reads a customer's data to answer a ticket
Analytics, migrations, backfills

Every one of these needs to cross the boundary, and each is a chance to punch a permanent hole in it. The failure is predictable: someone adds a skipTenantScope flag or an admin client that bypasses the repository, it works, and six months later a normal request path is using it because it was the easiest thing to import.

Three rules that hold up:

Make the bypass a different object, not a flag. A separate adminDb or an explicitly privileged role is greppable, reviewable and hard to reach by accident. A boolean parameter on the normal client is one autocomplete away from production.

Iterate tenants; don't query across them. A batch job that loops tenants and runs the ordinary scoped query per tenant keeps one code path for everything. It also gives you fault isolation for free: one tenant's failure doesn't abort the rest — which is the same per-row isolation argument as the reconcilers in Webhooks in Production.

Log every cross-tenant read with a reason. Support access to customer data should be attributable to a person and a ticket. If you can't answer "who looked at this tenant's data last month," you don't have an audit trail, you have a hope.

Migrations get harder, not easier

A shared database means one migration run — the whole point. It also means every migration touches every tenant's data at once, and a mistake has no blast radius smaller than "everyone."

Two things worth knowing before you need them.

A backfill that rewrites a tenant-scoped table should run per tenant, in batches, not as one UPDATE over millions of rows. A single statement takes locks for its entire duration and either finishes or rolls back wholesale; a batched loop can be paused, resumed and observed. See database transactions for why the long transaction is the thing that hurts.

And adding tenant_id to a table that didn't have it is a four-step migration, not one: add nullable, backfill, add the NOT NULL constraint and index, then start filtering. Attempting it in one step means either downtime or a window where half your rows have no tenant.

Testing a boundary

The isolation tests are simple to state:

Tenant A requests a resource belonging to tenant B → not found or forbidden, never B's data.
Tenant A sends through tenant B's phone number → rejected, and nothing leaves for Meta.
A list endpoint for tenant A contains no rows belonging to B, even when B has far more data.

Then the part almost nobody writes about: checking that those tests can fail.

When I fixed that authorization bug, I didn't trust the tests I'd written — I wrote five deliberately broken versions of the fix and reran the suite against each. Three of the five passed every one of the sixteen tests. Delete an entire direction of the rule, and sixteen green checkmarks said it was fine.

The reason was that the tests mocked the database. A mock returns whatever the test told it to, so the suite confirmed the code called the database correctly without ever confirming the database answered correctly.

Tenant isolation has the same trap, and worse. The rule lives in SQL — a WHERE clause, a policy, a constraint. If the database is mocked, that SQL never executes, and SQL that never executes isn't tested. A cross-tenant test running against a mock asserts that your code passed the right arguments to a fake. That is not the property you care about.

So isolation tests need a real database: a container, a disposable schema, something that actually runs the query. And at least once, break the boundary on purpose and watch a test go red. A boundary test that has never been seen to fail is decoration.

Production checklist

What I'd do differently

Turn on structural enforcement early. RLS, composite foreign keys, or a scoped repository — whichever fits — at ten tables, not a hundred. Each is a migration early and a project later. The convention feels fine right until the codebase is too large to audit, which is also the point at which you can no longer afford to replace it.

Make isolation tests hit a real database from the first one. Mocked boundary tests are worse than none, because they produce the feeling of coverage without the fact of it. I only know that because I measured it on a different codebase.

Treat every new entry point as a new boundary. A guard on the REST path is not a guard on the queue worker, the socket handler, the admin tool, or the script someone runs by hand. Every one of those is a door, and isolation is only as strong as the weakest one.

The same instinct, three times

A tenant boundary isn't a column. It's a guarantee — and the only guarantees that survive six hundred route handlers and a year of staff turnover are the ones the system makes structurally, not the ones people remember to make.

This is now the third time I've written about the same instinct wearing different clothes:

Authorization: enforce at the action, not at the list.
Webhooks: correctness lives outside the request that started it.
Multi-tenancy: the boundary has to be impossible to cross, not merely unlikely.

Each time the lesson was the same. Don't trust the path that depends on somebody remembering. Build the one where forgetting doesn't compile.

Building multi-tenant SaaS?

I've shipped tenant isolation, usage-based billing and payment reconciliation in production for a platform with paying customers, and I write about the parts that fail quietly. If you're building something where one customer's data must never reach another's, get in touch.