Multi-tenant SaaS architecture: the decisions that are expensive to change later

Most architecture decisions in a SaaS product can be revisited. You can swap a queue library or split a service without customers noticing. A handful of multi-tenant decisions are different. They're baked into every table, query and background job, and changing them later means a migration that touches all of it while customers are using the product.

For each of them below: what to decide on day one, why it gets expensive later, and the default I reach for.

Put the tenant key on every row

Every tenant-owned table gets a non-null tenant_id from the first migration, including tables that feel global but aren't: tags, custom fields, settings, saved views, uploaded files.

The key also belongs in every unique constraint and leads almost every index. An email address is unique within a tenant, not across the platform. If a unique index covers email alone, the second customer whose contact list overlaps the first hits a constraint error, and the tempting fix is to drop uniqueness altogether.

CREATE TABLE contacts (
  id         uuid PRIMARY KEY,
  tenant_id  uuid NOT NULL REFERENCES tenants (id),
  email      text NOT NULL,
  created_at timestamptz NOT NULL DEFAULT now(),
  UNIQUE (tenant_id, email),
  UNIQUE (tenant_id, id)          -- lets other tables reference (tenant_id, id)
);
 
CREATE INDEX contacts_tenant_created ON contacts (tenant_id, created_at DESC);
 
-- A composite foreign key makes cross-tenant references impossible:
-- a deal can only point at a contact in the same tenant.
CREATE TABLE deals (
  id         uuid PRIMARY KEY,
  tenant_id  uuid NOT NULL REFERENCES tenants (id),
  contact_id uuid NOT NULL,
  FOREIGN KEY (tenant_id, contact_id) REFERENCES contacts (tenant_id, id)
);

VetRealm, a multi-tenant practice-management SaaS for veterinary clinics, treats the clinic as the tenant boundary: inventory SKUs are unique per clinic, and appointment overlap checks run on a compound index that starts with the clinic.

Why it's expensive later: backfilling a tenant key means touching every row, rebuilding every index and auditing every query. Cheap default: tenant_id on every tenant-owned table, in every unique constraint, leading every composite index.

Choose an isolation model, and keep a way out

There are three ways to lay tenants out, here using the names from AWS's SaaS guidance.

The mistake isn't picking the wrong one. It's picking one you can't leave. Pool is the right start for almost everyone: cheapest to run, simplest to migrate. Eventually, though, an enterprise customer asks for their own database. With a way out, isolation becomes a pricing tier rather than an engineering crisis.

The same lookup becomes your sharding key later, with cross-tenant reporting moved to a warehouse fed by change data capture.

Model users, memberships and tenants separately

The most common identity mistake is a tenant_id column on users. It works until a consultant, or a vet who covers two clinics, needs access to more than one tenant. Then you have duplicate accounts, two passwords and a support queue.

Model three things. A user is a person with credentials. A tenant is an organization. A membership joins them and carries the role. The session holds the user and the active tenant; switching tenants changes the active membership, not the login.

CREATE TABLE users (
  id    uuid PRIMARY KEY,
  email text NOT NULL UNIQUE      -- stored lower-cased
);
 
CREATE TABLE memberships (
  tenant_id uuid NOT NULL REFERENCES tenants (id),
  user_id   uuid NOT NULL REFERENCES users (id),
  role_id   uuid NOT NULL,
  status    text NOT NULL DEFAULT 'active',  -- invited | active | suspended
  PRIMARY KEY (tenant_id, user_id)
);

Invitations live next to memberships. VetRealm's employee invites use a partial unique index on pending invitations per email and clinic, so the same address can be invited again after an invite is revoked.

Why it's expensive later: splitting users from memberships means merging duplicate accounts, as much a customer-communication problem as a migration. Cheap default: users, tenants and memberships from the first migration, even if every user has one membership for a year.

Decide where authorization lives

Roles belong to memberships, not users: someone can be an owner in one tenant and read-only in another. The harder question is where the checks run.

My default is one layer of composable route guards in a fixed order: authenticate the user, resolve and verify the tenant, check the role, check the specific permission. VetRealm runs three gates on its routes: JWT authentication, a role gate, and a permission gate such as hasPermission('INVENTORY_MANAGE'). The client mirrors them so forbidden UI never renders, but client guards are for experience. Server guards are the security.

Why it's expensive later: checks scattered through controllers can't be audited, and custom roles mean finding every one. Cheap default: check named permissions, not role names, in the guard layer, with roles stored per tenant as bundles of permissions.

Carry tenant context everywhere, not just in requests

The tenant should come from one place on a request: middleware that reads the session, verifies the caller belongs to the claimed tenant, and stores the result for downstream code. In Node that's AsyncLocalStorage.

import { AsyncLocalStorage } from "node:async_hooks";
import type { NextFunction, Request, Response } from "express";
 
type TenantContext = { tenantId: string; userId: string };
const tenantStore = new AsyncLocalStorage<TenantContext>();
 
export async function withTenant(req: Request, res: Response, next: NextFunction) {
  const userId = req.session?.userId;
  const tenantId = req.session?.activeTenantId;
  if (!userId) return res.status(401).end();
  if (!tenantId) return res.status(400).json({ error: "no active tenant" });
 
  // Never trust a tenant id from the client without checking membership.
  const member = await db.memberships.findActive({ userId, tenantId });
  if (!member) return res.status(403).end();
 
  tenantStore.run({ tenantId, userId }, () => next());
}
 
export function currentTenant(): string {
  const ctx = tenantStore.getStore();
  if (!ctx) throw new Error("No tenant context");   // fail closed
  return ctx.tenantId;
}
 
// Every tenant-scoped query runs in a transaction with the tenant set locally,
// so row-level security sees it and a pooled connection can't leak it.
export async function tenantQuery<T>(sql: string, params: unknown[] = []): Promise<T[]> {
  const tenantId = currentTenant();
  return db.transaction(async (tx) => {
    await tx.query("SELECT set_config('app.tenant_id', $1, true)", [tenantId]);
    return tx.query<T>(sql, params);
  });
}

VetRealm does this in an ensureClinicAccess middleware that attaches the clinic from the authenticated user and scopes every downstream query to it.

HTTP requests aren't the only entry point:

• Background jobs. Put the tenant in the payload and re-establish context in the worker. A job without a tenant should fail loudly.
• Caches. Prefix every key with the tenant, such as t:${tenantId}:dashboard.
• Search indexes. A tenant filter on every query, enforced in one wrapper.
• File storage. Object keys under a tenant prefix, so policies, export and deletion work per tenant.
• Real-time channels. Rooms keyed by tenant. VetRealm's Socket.IO rooms are keyed by clinic, so events never broadcast globally.
• Logs and traces. The tenant on every line.

Any helper that takes an ID and returns a record should also take or read the tenant. A bare getInvoice(id) invites the bug where a list is scoped correctly and the enrichment lookup behind it isn't.

Use row-level security as defense in depth

Application scoping is the first control. Postgres row-level security is the second: it filters every row whether or not a query remembered its tenant predicate.

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 = nullif(current_setting('app.tenant_id', true), '')::uuid)
  WITH CHECK (tenant_id = nullif(current_setting('app.tenant_id', true), '')::uuid);

Connect as a role that doesn't own the tables and lacks BYPASSRLS; superusers and bypass roles skip policies entirely. Set the tenant with set_config(..., true) inside a transaction, as in the helper above. Behind PgBouncer in transaction mode, session-level settings are exactly what you must not rely on.

On MongoDB, which VetRealm uses, there's no database-level equivalent, so the middleware and a scoped data-access layer carry the whole load. I compare the two in MongoDB vs PostgreSQL for SaaS.

Decide what the billing entity is

Billing attaches to something: usually the tenant, sometimes a parent account that owns several tenants, occasionally a user. Decide which, and what you count, before you write the subscriptions table: seats, usage, locations or a flat plan. VetRealm sells clinics tiered plans through PayTabs, with feature limits per plan.

The expensive mistakes are billing the user when the customer is the organization, or each tenant when the customer is a group that wants one invoice.

Cheap default: the subscription belongs to the tenant, with an optional billing_account_id that several tenants can share. Record usage as events with a tenant and a timestamp from day one, even if you bill flat, because you can't backfill usage you never recorded. Pricing models are in usage, per-seat and hybrid pricing.

Pick an ID format you won't regret

Sequential IDs, per tenant or global, leak volume, are guessable, and collide when you move tenants between databases.

Cheap default: globally unique, time-ordered IDs such as UUIDv7 for every primary key. They're unique across databases, so moving a tenant to a silo renumbers nothing, and roughly time-ordered, which avoids the random-insert penalty UUIDv4 puts on B-tree indexes. Postgres 18 ships a uuidv7() function; on older versions, generate them in the application. Human-friendly invoice numbers go in a separate per-tenant display column, never the key.

Contain noisy neighbors from the start

In a pool, one tenant's import or runaway integration can starve everyone else. Containment is cheap to design in and painful to retrofit, because it needs the tenant at every layer.

• Per-tenant rate limits at the edge, keyed on tenant rather than IP, with ceilings by plan. See rate limiters at scale.
• Statement timeouts, so a runaway query can't hold a CPU.
• Connection quotas per tenant, so one leaking tenant can't exhaust the pool.
• Queue lanes or priorities, so one tenant's backfill doesn't delay everyone's emails.
• Per-tenant metrics for request rate, latency and database time.

Know where tenant data lives

If customers may need data kept in a region, the pool_id indirection gives you most of the answer: a region is a pool somewhere else. Residency is usually lost in side systems, such as the search cluster, the analytics pipeline or a third-party tool, not in the primary database.

Cheap default: a region attribute on the tenant, one region in practice, and a written list of every place tenant data is copied.

Audit logs, export and deletion

Customers ask for all three after launch. Each depends on knowing what belongs to a tenant.

• Audit logs. Append-only, with actor, tenant, action, target, before and after values, IP and timestamp. VetRealm keeps an immutable log of sensitive admin actions such as support impersonation and subscription changes. Start with money, permissions and impersonation.
• Export. With a tenant key on every row and a tenant prefix on every file, export is a query per table and a prefix copy. Without them, it's archaeology.
• Deletion. Decide soft or hard delete per entity, and how long backups keep deleted data. Deleting a tenant should be a scripted, tested operation.

Subdomains and custom domains

How a request finds its tenant has architectural consequences. Paths, subdomains and custom domains each change cookies, TLS certificates, OAuth redirect URIs and CORS. Subdomains need wildcard certificates and careful cookie scoping. Custom domains need automated certificates and domain verification.

Cheap default: resolve the host to a tenant through one tenant_domains table, then verify membership from the session as usual. Custom domains later become a feature, not a refactor.

Where I'd start

If you're about to build a multi-tenant product, or you're running one and suspect a few of these went the wrong way, I'd start with a Discovery Sprint. It's a short, fixed-fee engagement that settles these decisions explicitly and ends with a scoped brief, a risk map, an architecture and a fixed-price proposal. The fee is credited toward the build. Start a conversation.