Multi-Tenant SaaS: Data Isolation Is the Easy Part

Search for multi-tenant architecture and you'll get twenty articles about the same decision: shared database with a tenant_id column, schema per tenant, or database per tenant. They'll show you row-level security policies, explain why WHERE tenant_id = ? is dangerous if you forget it, and stop.

That decision matters. It's also the part you'll finish in a week and rarely think about again.

The problems that actually consume your time show up afterward, and they share a shape: a tenant's data is not the only data in your system. You also have a master database that knows which tenants exist, who is allowed into them, what they're being billed, and which plan they're on. Once that exists, you're not running one database design — you're running two systems that have to agree with each other, forever, across a boundary you can't put a foreign key across.

This post is about that boundary.

The Part Everyone Writes About

Briefly, because it's well covered elsewhere. You have three real options, and the deciding factor is usually compliance and blast radius, not elegance.

With Postgres, shared-database isolation is genuinely solid if you let the database enforce it rather than your application:

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;  -- applies to the table owner too

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

Then set app.tenant_id once per transaction, from a trusted source, and every query in that transaction is scoped whether the developer remembered or not.

That last part is the whole point. Isolation enforced in application code is isolation that fails the first time someone writes a query in a hurry. Isolation enforced by the database fails closed. If you take one thing from the well-trodden half of this topic, take that.

You can also combine approaches, and mature systems usually do: shared database with RLS for most customers, dedicated databases for the enterprise accounts whose contracts demand physical separation. That hybrid is common, and it means your data-access layer must be able to route to either — which is the first hint that the routing problem is bigger than the isolation problem.

Why That's the Easy Part

Because it's a decision with a small, closed set of options, it's enforced in one place, and once it's right it stays right. RLS policies don't drift. Nobody files a bug about a USING clause six months later.

Here's what does generate bugs six months later: the schema inside each tenant is identical, so tenant data is the boring part. Every tenant has invoices with the same columns. The interesting data — who exists, who pays, who's allowed where — lives outside any single tenant, and that data has no natural home in a design that only thinks about tenant isolation.

The Master Database

So you build one. Call it the master database, the control plane, the platform database — the name varies, the contents don't:

tenants           id, slug, name, status, plan_id, region, db_connection_ref
plans             id, name, limits (seats, storage, api_calls)
subscriptions     tenant_id, plan_id, period_start, period_end, status
invoices          tenant_id, amount, status        -- billing for the tenant itself
users             id, email, password_hash, name   -- global identity
memberships       user_id, tenant_id, role, status -- who can enter where
audit_log         actor_user_id, tenant_id, action, at

Two things about this table list are worth dwelling on, because they're where the difficulty comes from.

First, invoices appears twice in a full system — once here, meaning "what this tenant owes me," and once inside each tenant, meaning "what this tenant's customers owe them." Same word, different universe. Confusing them in conversation is common; confusing them in code is a very bad afternoon. Name them differently from day one. platform_invoices and invoices. You will thank yourself.

Second, users is here, not in the tenant. That's the decision that reshapes everything else.

Global Users: One Person, Many Tenants

The tutorial model of multi-tenancy assumes a user belongs to a tenant. It gives you users.tenant_id, and everything is simple.

It's also wrong for most real products. An accountant works with six client companies. A contractor is in two workspaces. Someone changes jobs and needs access to their new employer's tenant without abandoning their personal one. The moment any of that is true, users.tenant_id is a schema you have to migrate out of under pressure.

Identity is global; membership is the relationship:

-- In the master database
CREATE TABLE users (
  id            uuid PRIMARY KEY,
  email         citext UNIQUE NOT NULL,   -- global uniqueness
  password_hash text,
  created_at    timestamptz NOT NULL DEFAULT now()
);

CREATE TABLE memberships (
  user_id   uuid NOT NULL REFERENCES users(id),
  tenant_id uuid NOT NULL REFERENCES tenants(id),
  role      text NOT NULL,
  status    text NOT NULL DEFAULT 'active',
  PRIMARY KEY (user_id, tenant_id)
);

This is a small diagram with large consequences:

Email uniqueness becomes global. One account, many memberships. Which means you cannot let Tenant A's admin "create a user" with an email that already exists in the system — that email belongs to a person who may already have a login and a password you must not touch. Inviting an existing user and creating a new one are different flows, and the invite flow must not leak whether the address is already registered.

Login is now two steps. Authenticate the person, then resolve which tenant this session is acting in. If they belong to one tenant, skip the prompt. If several, they pick — and can switch without re-authenticating. Your session carries both user_id and an active tenant_id, and every authorization check needs both.

Roles are per-membership, not per-user. The same person is an admin in one tenant and read-only in another. "Is this user an admin" is not a question you can answer. "Is this user an admin in this tenant" is.

Deleting is ambiguous. Removing someone from a tenant revokes a membership. Deleting their account touches every tenant they were in, and their authorship of records in all of them. Under GDPR-style erasure the second one is a real operation, and it crosses every database you own.

The Actual Hard Part: Making Two Systems Agree

Now you have a control plane holding identity and billing, and a data plane holding tenant records. They're separate databases, possibly separate engines, possibly separate regions. Everything below follows from that split, and none of it is solved by your isolation choice.

You Can't Foreign-Key Across the Boundary

A tenant's invoices.created_by refers to a user in the master database. There is no constraint that can enforce it. Your tenant database happily stores a created_by for a user that never existed, or one deleted last Tuesday.

You get a few options, all of them trade-offs:

• Store a denormalized copy of the display fields (created_by_id, created_by_name) in the tenant. Renders fast, no cross-database join, goes stale when someone changes their name.
• Resolve at read time by collecting user ids from tenant rows and batch-fetching from the master. Always fresh, adds a hop, and every list endpoint now depends on the control plane being up.
• Both, which is what most systems land on: cached copy for display, authoritative lookup for anything that matters.

Pick deliberately, and write down which fields are cached, because "why does this show the old name" will be reported as a bug repeatedly.

Every Request Has to Resolve a Tenant Before It Can Do Anything

Subdomain, path segment, or header — something identifies the tenant, and that identifier has to become a validated tenant and a database connection before your handler runs:

// Illustrative middleware shape
async function resolveTenant(req: Request) {
  const slug = subdomainOf(req)                      // acme.app.com -> "acme"
  const tenant = await controlPlane.tenantBySlug(slug)

  if (!tenant || tenant.status !== 'active') throw new NotFound()

  const session = await getSession(req)
  const membership = await controlPlane.membership(session.userId, tenant.id)
  if (!membership) throw new Forbidden()            // authenticated, not authorized here

  return { tenant, membership }
}

Read that carefully: the control plane is now on the critical path of every single request. Its availability is your availability, and its latency is added to every page load. That is the price of global identity, and it's why tenant and membership lookups are the first thing you cache — with short TTLs, because a revoked membership that stays cached for an hour is a security incident, not a stale render.

Note also the distinction between 404 and 403 here, and think about which one you want to leak. Returning "forbidden" for a tenant the user isn't in confirms that tenant exists.

Migrations Multiply

You said the schema is the same for every tenant. That's the right design, and it's also a promise you now have to keep across N databases.

One migration against one shared database is a deploy. The same migration across 400 tenant databases is a distributed operation that can partially fail, leaving you with tenants on version 51 and tenants on version 52 — while one codebase talks to both.

What this forces you to build:

• A schema version per tenant, recorded in the control plane, so you can answer "who is behind" without connecting to all of them.
• Migrations that are safe to run twice, because you will retry the failures.
• Application code that tolerates version skew during the rollout window. In practice this means expand-then-contract: add the new column, deploy code that writes both, backfill, then drop the old one. Never a migration that requires a simultaneous code cutover.
• Drift detection, because a hotfix applied by hand to one tenant's database at 2am is how "identical schema" quietly stops being true.

Database-per-tenant is where teams most often underestimate the ongoing cost, and this is the line item they missed. The isolation was free. The fleet management was not.

Provisioning Is a Transaction That Spans Systems

Creating a tenant means: insert the tenant row, create a database or schema, run every migration, seed defaults, create the first membership, start a subscription, maybe register a subdomain and issue a certificate.

Some of those are Postgres, some are your billing provider, some are DNS. There is no transaction across that set. Any step can fail with earlier steps already committed.

Treat provisioning as a state machine with a status on the tenant row (provisioning, active, failed, suspended), make each step idempotent, and make the whole thing resumable. The alternative is half-created tenants that a human has to unpick by hand, and the person doing that unpicking will be you.

Usage Lives in One Place and Billing Lives in Another

Your plan limits say 10 seats and 100,000 API calls. Seats are countable from memberships in the control plane — easy. API calls happen in the data plane, per tenant, at volume.

So metering is a pipeline: count in the tenant context, aggregate somewhere durable, expose it to the control plane for enforcement and invoicing. And you have to decide what happens when it's late or wrong. Do you hard-block at the limit, and risk cutting off a paying customer over a lagging counter? Or allow overage and reconcile, and accept some unbilled usage? Both are defensible. Silently doing whichever one your code happens to do is not.

Support Needs Cross-Tenant Answers

"How many tenants are affected by this bug?" With a shared database and RLS, that's a query. With database-per-tenant, it's a fan-out across the fleet, and you probably build a reporting store that aggregates from all of them — which is a third system, with its own staleness and its own copy of data you were trying to keep separated.

This is the tradeoff RLS-versus-separate-databases articles rarely price in. Physical isolation is excellent for compliance and terrible for the questions your own team needs to ask.

Failure Modes Worth Naming

• Tenant context lost mid-request. Background job, retry, or async handler runs without the tenant set. With RLS as a FORCEd policy this fails closed and returns nothing. With application-level filtering it returns everything. This single difference is the strongest argument for database-enforced isolation.
• Connection pool reuse. You set app.tenant_id on a pooled connection, and the next borrower inherits it. Set it per transaction, and reset on release.
• The control plane cache outliving a revocation. Someone removed from a tenant keeps working until the TTL expires. Short TTLs plus explicit invalidation on membership change.
• Cross-tenant data in shared infrastructure. Caches, search indexes, file storage, and queues all need the tenant in the key or the path. Isolation in the database is not isolation in Redis.
• The impersonation backdoor. Support tooling that assumes any tenant's context is the highest-value target in your system. Separate audit trail, time-limited, never the same code path as normal auth.

What I'd Decide First

If I were starting this again, in order:

1. Is identity global? Can one person be in multiple tenants? Answer this before writing a users table, because retrofitting it is the migration everyone regrets.
2. What does the control plane own, exactly? Write the list. Tenants, plans, subscriptions, users, memberships. Anything ambiguous — is it platform or tenant? — resolve it now and name the tables so the answer is obvious in code review.
3. Isolation mechanism, enforced by the database. RLS with FORCE, or separate databases, or both. Not WHERE tenant_id = ? in a repository class.
4. How does a request resolve its tenant, and what happens when the control plane is slow? This is on every request forever.
5. How does a migration reach every tenant, and how do I know it did? Before you have 400 of them.

Notice that only one of those five is the question the articles answer.

Closing

Data isolation gets the attention because it's the part with a clean answer. You can put it in a table, compare three options, and pick one. It feels like the architecture.

The control plane is where the architecture actually is. It's the thing that knows who your customers are, who's allowed in, and what they owe you — and it has to stay coherent with N tenant databases across a boundary that no constraint can protect. Global users, fleet migrations, provisioning that spans three vendors, metering that crosses the divide, and support tooling that has to see everything without becoming a backdoor.

None of that is exotic. All of it is work that nobody warned you about, because the isolation question got all the airtime.