Multi-Tenancy Architecture for SaaS

In Organizations, Teams, and Invitations we got people into the right organization: memberships, roles, invite flows, all the plumbing that decides who belongs where. That article assumed the data layer would simply honor an organization_id filter whenever asked. This one is about making that assumption actually true at the database and infrastructure level. Application code that a reviewer has to trust got every query right isn't a good enough guarantee.

This is part of the Full Stack SaaS Masterclass, a build-it-for-real series taking a multi-tenant SaaS from an empty folder to production. Module 3 covers the features that make a SaaS a SaaS, and tenancy is the one architectural decision underneath all of them: get it wrong and every other feature inherits the risk.

I want to set expectations early. There is no single "correct" multi-tenancy architecture. There's a spectrum of isolation models, each with a different cost in operational complexity, and the right one depends on your customer profile, not on which one sounds more impressive in a design doc.

Choosing a tenant isolation model

Three models cover almost every real SaaS product, and they differ mainly in where the tenant boundary lives.

Shared schema, shared tables. Every tenant's rows sit in the same tables, distinguished by an organization_id column. This is the model the previous article already assumed, and it's the right default for most products. One schema to migrate, one connection pool to size, one set of indexes to tune. The cost is that isolation is entirely a query-correctness problem: forget a WHERE organization_id = $1 clause anywhere and you have a cross-tenant data leak, not a slow query.

Schema-per-tenant. Each organization gets its own PostgreSQL schema within the same database, with identical table structures. Isolation is stronger because a query that forgets to scope by tenant simply can't see another schema's rows at all; it errors out or returns nothing rather than leaking data. The cost shows up in operations: a migration now has to run against every schema, not once, and connection routing has to resolve which schema a request belongs to before a single query runs.

Database-per-tenant. Each organization gets a fully separate database, sometimes on separate infrastructure entirely. This is the strongest isolation available short of physically separate hardware, and it's genuinely the right call for large enterprise customers with strict data-residency or compliance requirements written into a contract. It's also the most expensive model to operate: connection pooling multiplies by tenant count, a schema migration becomes a fleet-wide rollout, and "run this analytics query across all tenants" stops being a single SELECT and becomes a fan-out job.

The mistake I'd steer you away from is picking schema-per-tenant or database-per-tenant on day one because it sounds more secure. It is more secure against one specific bug class (the forgotten WHERE clause), but it trades that for a much larger set of operational failure modes: migrations that partially succeed across hundreds of schemas, connection pools that need per-tenant limits, and support tickets that require you to know which of N databases a customer's data lives in. Start shared-schema. Move a tenant to its own schema or database only when a real requirement, a contract clause, a compliance audit, a customer big enough to justify dedicated capacity, forces it. Treat it as an escape hatch, not a starting point.

Enforcing isolation at the database with row-level security

The previous article's TenantGuard checks membership and scopes queries at the application layer. That's necessary, but it puts the entire isolation guarantee on every developer remembering to pass organizationId into every query, forever. PostgreSQL's row-level security (RLS) gives you a second, independent layer that fails closed: even a query that forgets to filter by tenant simply won't see rows it shouldn't.

alter table projects enable row level security;

create policy tenant_isolation on projects
  using (organization_id = current_setting('app.current_org_id')::uuid);

With RLS enabled, that policy applies to every query against projects, including ones a future engineer writes without knowing the tenancy rules exist. The application still sets app.current_org_id per request, but now a missing filter degrades from "silent data leak" to "empty result set," which is a much better failure mode.

The part that trips people up is that current_setting needs a value to read, and that value has to be set safely per request without leaking across connections in a pool.

// prisma/tenant-context.middleware.ts
import { Injectable, NestMiddleware } from '@nestjs/common';
import { Request, Response, NextFunction } from 'express';
import { PrismaService } from './prisma.service';

@Injectable()
export class TenantContextMiddleware implements NestMiddleware {
  constructor(private readonly prisma: PrismaService) {}

  async use(req: Request, res: Response, next: NextFunction): Promise<void> {
    const orgId = (req as any).membership?.organizationId;
    if (orgId) {
      await this.prisma.$executeRawUnsafe(
        `select set_config('app.current_org_id', $1, true)`,
        orgId,
      );
    }
    next();
  }
}

That third argument to set_config, true, makes it transaction-local rather than session-local. That distinction matters more than it looks like it does, and it's the subject of the next section.

Why "session-local" settings are dangerous with pooled connections

SET app.current_org_id = 'abc' (without LOCAL) persists for the lifetime of the database connection. That's fine if every request gets its own connection for its own lifetime. It's a serious bug the moment you're using a connection pool, whether that's PgBouncer in transaction mode or Prisma's own internal pool, because the next request to reuse that connection inherits the previous tenant's context.

The fix is set_config('app.current_org_id', $1, true), the true meaning "local to the current transaction." Combined with wrapping each request's queries in an explicit transaction, this guarantees the setting resets the moment the transaction ends, before the connection goes back to the pool.

// prisma/prisma.service.ts
async runInTenantContext<T>(orgId: string, fn: (tx: Prisma.TransactionClient) => Promise<T>): Promise<T> {
  return this.$transaction(async (tx) => {
    await tx.$executeRawUnsafe(`select set_config('app.current_org_id', $1, true)`, orgId);
    return fn(tx);
  });
}

This costs you something real: every request now opens a transaction even for a single read, which adds overhead compared to a bare connection-pooled query. For most SaaS request volumes that overhead is negligible next to the correctness guarantee it buys. If you're running at a scale where it isn't, that's a signal to profile it specifically rather than assume RLS is too expensive without measuring.

Migrations and schema changes across isolation models

Shared schema keeps migrations exactly as simple as any single-tenant app: one migration, one deploy, done. This is one of the strongest arguments for staying on shared schema as long as you can.

Schema-per-tenant changes that math. A migration tool that runs "once" now has to iterate over every tenant schema, and a migration that fails halfway through leaves some tenants on the old structure and some on the new one. Plan for this explicitly if you go this route: run migrations tenant-by-tenant with retries and a status table (tenant_id, migration_version, applied_at) so you can query which tenants are behind, rather than discovering it from a production error.

create table tenant_migrations (
  organization_id uuid not null references organizations(id),
  migration_name text not null,
  applied_at timestamptz not null default now(),
  primary key (organization_id, migration_name)
);

Database-per-tenant makes this worse again: a migration is now a fleet-wide operation against N independent database connections, and rollback means rolling back N databases independently, not one transaction. Some teams handle this with a hybrid model: most tenants stay on a shared, easy-to-migrate schema, and only the handful of tenants that earned their own database get a slower, more careful migration process built around their smaller count. This is a reasonable compromise. It's much easier to build if you designed your data-access layer to be agnostic about which physical database a tenant's connection resolves to from the start.

Production pitfalls

RLS gives a false sense of complete safety if you forget that a database superuser, and by default the role your application connects as if you didn't create a dedicated one, bypasses row-level security entirely unless FORCE ROW LEVEL SECURITY is set on the table. Always run application traffic through a role that isn't a superuser, and add force row level security to any table where RLS is the safety net you're relying on.

Connection pool exhaustion is the recurring failure mode of database-per-tenant and schema-per-tenant designs at any real scale. A pool sized for ten tenants doesn't automatically work for a thousand. Each additional isolated unit either needs its own slice of a shared pool budget or its own dedicated, smaller pool, and you need monitoring that shows pool saturation per tenant, not just in aggregate, or a single noisy tenant's traffic spike looks like a global outage.

Test cross-tenant isolation the same way regardless of which model you pick: an integration test that seeds two organizations, and asserts that a query scoped to one never returns a row belonging to the other, run in CI on every change to the data layer. This test caught real bugs in the guard-based approach from the previous article, and it's just as necessary once RLS is in place, because RLS protects against a missing filter, not against a policy that was itself written incorrectly.

Finally, don't let "we have RLS now" become a reason to stop reviewing tenant-scoping in application code. RLS is a second layer specifically because a single layer of defense, however well-implemented, is a single point of failure for the worst class of bug a SaaS can ship.