Multi-tenant SaaS Backend with NestJS and PostgreSQL

Overview#

Most B2B products I have worked on store every customer in one database with a tenant_id column. It is the cheapest model to run and the easiest to report on. Its weak point is obvious: one missing WHERE tenant_id = … and a customer sees another customer's data.

This concept is the tenancy layer I would build for a NestJS and PostgreSQL SaaS. The live demo is an API simulator with three fictional companies. You can switch users and tenants, call the API, and turn the safety layers off one at a time to see which one stops a leak.

My role#

Full build: data model, the tenant context design, the RLS policies, plan limits, invitations, the simulator and this write-up. It is a concept project, not client work, and all companies and users in it are invented.

The problem#

Requirements for the tenancy layer:

• A user can belong to several organisations and switch between them without logging out.
• No request can read or write another tenant's rows, even if a developer forgets the tenant filter in a new query.
• Guessing an id from another tenant must not reveal that the row exists.
• Each tenant's plan limits members and projects, and the error must say so clearly.
• Owners and admins need a record of who did what inside their organisation.

The second point is the important one. Code review catches most missing filters, but not all of them, and the cost of the one it misses is a data breach.

Approach & architecture#

The active tenant lives in the token. POST /auth/switch-tenant checks the membership and issues a new token with tid and the role in that tenant. The guard still re-checks membership on every request, so removing someone takes effect before their token expires. An X-Tenant-Id header that disagrees with the token is refused with 403.

Two independent isolation layers. Services always add WHERE tenant_id = $1. Underneath, every tenant table has a row-level security policy:

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

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id', true)::text)
  WITH CHECK (tenant_id = current_setting('app.tenant_id', true)::text);

The API connects as a role without BYPASSRLS. If the setting is missing, current_setting(..., true) returns null and the policy matches nothing, so a forgotten context fails closed.

Transaction-local tenant context. In the production design an interceptor wraps the handler in a Prisma interactive transaction and sets the tenant first:

return this.prisma.$transaction(async (tx) => {
  await tx.$executeRaw`SELECT set_config('app.tenant_id', ${tenantId}, true)`;
  return firstValueFrom(next.handle().pipe(/* services use tx from AsyncLocalStorage */));
});

The third argument true makes the setting local to the transaction. With a connection pool this matters: a session-level SET would leak one tenant's id into the next request on the same connection.

Plan limits in a guard. Routes that create members or projects declare the limit they consume. The guard counts current usage (pending invitations count as members) and returns 402 Payment Required with limit, used and max in the body so the front end can show an upgrade prompt.

Audit log in the same transaction. Every write inserts an audit_log row before commit. If the write fails, the audit row rolls back with it.

Key challenges#

• Proving the second layer works. The demo has a switch that makes the projects service drop its tenant filter. With RLS on, the SQL log shows the policy removing the other tenants' rows and the response is unchanged. Only when RLS is also off does data leak, and the demo flags that in red. The production version of this check is an e2e test that runs a raw query without a tenant setting and expects zero rows.
• 404, not 403, across tenants. Asking for project 105 from Acme returns 404 because, as far as the Acme session can tell, the row does not exist.
• Invitations before a tenant is known. Accepting an invite starts with no tenant context. The lookup runs under a narrow service role, and the tenant setting is applied once the invitation says which tenant it belongs to. Tokens expire after 48 hours (410) and can be used once (409).
• Platform admin access. Support staff need to list all tenants. That route uses a separate read-only database role with BYPASSRLS, and it is the only route that does.

Tech stack#

• Demo: TypeScript, React and Next.js. app.ts holds the guards, interceptor and controllers; db.ts holds the rows and a small query helper that applies the RLS policy where PostgreSQL would. Both are readable under "View source" on the demo page.
• Production design: NestJS on Node.js, PostgreSQL with RLS policies created in Prisma migrations (raw SQL), Prisma interactive transactions with AsyncLocalStorage for the tenant-bound client, JWT access tokens, hashed invitation tokens, and Jest e2e tests that log in as tenant A and request tenant B's ids.

Results#

• A working simulator with 14 endpoints covering auth, tenant switching, projects, members, invitations, plan usage, plan changes, the audit log and a platform admin route.
• Cross-tenant reads return 404, spoofed tenant headers 403, wrong in-tenant roles 403 and plan limits 402, each with the guard trace and SQL visible.
• The two safety layers can be switched independently, and the demo shows that either one alone keeps tenants apart.

This is a concept build without real users or tenants, and no performance claims are made.

Try the demo#

Open the multi-tenant backend demo. Things to try:

1. Sign in as sara@acme.test and run "Read another tenant's project". You get 404. Switch the active tenant to Bluebird and send it again.
2. Tick "bug: service forgets WHERE tenant_id", send GET /projects, and read the SQL log. Then untick RLS too and send again.
3. As Sara in Acme, send "Create a project" twice. The second call hits the free plan's limit of 5 and returns 402.
4. Send "Invite a member", accept it from the invitations table, then press accept again.

What I would do next#

• Replace the count-then-insert plan check with a locked per-tenant usage row, so two parallel requests cannot both pass.
• Add per-tenant API keys for server-to-server access, stored hashed and scoped to one tenant.
• Add a tenant export and delete flow for data requests.