Tenant Isolation: Two-Layer Defense

Goal

Make cross-tenant data leaks fail loud in the application layer and fail closed in the database, so a single mistake — ours or a dependency's — can't silently return another company's data. This is a distinct concern from multi-tenancy.md (which covers User↔Company modeling for agencies) and from prisma-caching.md (which covers Accelerate's query cache).

Two independent layers:

1. App layer — a Prisma Client Extension auto-injects companyId into every query for tenant-scoped models (getTenantPrisma(companyId)), and a second extension throws if a tenant-scoped model is queried through the base prisma client instead.
2. Database layer — Postgres Row-Level Security (RLS) enforces the same companyId scoping at the engine level, independent of whether the app layer was bypassed (a raw $queryRaw, a library-internals surprise, code that hasn't been audited yet).

Route handler
  │
  ├─ getTenantPrisma(companyId) ──► tenant extension (inject companyId) ──► SET LOCAL app.company_id ──► Accelerate ──► Postgres ──► RLS policy (companyId match)
  │
  └─ prisma (base client) ────────► throw guard ──► [withCrossTenantAccess(fn) escape hatch] ──► SET LOCAL <sentinel> ──► Accelerate ──► Postgres ──► RLS policy (sentinel match)

The extension-ordering rule (read this before adding a new $extends() call)

withAccelerate() must always be the last extension applied. Its read-method overrides (findMany, findFirst, count, aggregate, groupBy, findUnique(OrThrow), findFirstOrThrow) replace the client's model methods outright; any query/$allOperations hook from an extension applied after withAccelerate() gets bypassed for those methods. This was confirmed empirically against this codebase's own tenant extension: the same createTenantExtension, chained after a faithful reproduction of withAccelerate()'s actual composition structure, silently returned rows from every company; chained before it, filtering worked correctly.

apps/backend/src/infra/prisma.ts structures this as:

function buildRawClient(): { client: PrismaClient; isAccelerate: boolean } { /* no withAccelerate() yet */ }

function withAccelerateIfNeeded(client: PrismaClient, isAccelerate: boolean): PrismaClient {
  return isAccelerate ? client.$extends(withAccelerate()) : client;
}

// base `prisma` export:
const guarded = rawClient.$extends(crossTenantRlsBypassExtension).$extends(tenantIsolationGuardExtension);
const prisma = withAccelerateIfNeeded(withBenchmarkExtension(guarded), isAccelerate);

// getTenantPrisma(companyId):
const tenantExtended = rawClient.$extends(createTenantExtension(companyId)).$extends(createSetLocalExtension(companyId));
const client = withAccelerateIfNeeded(withBenchmarkExtension(tenantExtended), isAccelerate);

If you add a new extension anywhere in this file, it goes before withAccelerateIfNeeded(...), never after.

Layer A: app-layer guard and tenant extension

createTenantExtension(companyId) (apps/backend/src/middleware/tenant.ts) injects companyId into where/data for every model in TENANT_SCOPED_MODELS, and via a parent-relation subquery-equivalent for NESTED_TENANT_MODELS (companyAccountAnalytics → socialAccount, companyLinkInBioLink → page).

tenantIsolationGuardExtension (apps/backend/src/infra/prisma.ts) throws TenantIsolationError whenever a TENANT_SCOPED_MODELS model is queried through the base prisma client. It throws unconditionally in dev, test, and prod — there's no log-only staging period.

The escape hatch is withCrossTenantAccess(fn), an AsyncLocalStorage-backed wrapper (mirroring the pattern in apps/backend/src/services/dbops/apiInstrumentation.ts):

import prisma, { withCrossTenantAccess } from '@/infra/prisma.js';

const allCompanies = await withCrossTenantAccess(() => prisma.company.findMany());

Every legitimate cross-tenant call site wraps only its own call — background jobs iterating all companies, auth/signup before a company is known, admin/platform routes (all requireSuperAdmin-gated) — so each bypass stays a single, grep-able, individually-reviewed line rather than a blanket per-file exemption.

Gotcha found and fixed during this work: withCrossTenantAccess(fn) must construct the transaction/context inside AsyncLocalStorage.run()'s synchronous callback, not around the whole call. Prisma's findMany()/etc. return a lazy "PrismaPromise" that only runs the extension chain once awaited — wrapping the result of run() in Promise.resolve() executes that lazy work after the ALS context has already exited, silently defeating the escape hatch. The fix: crossTenantAccessStorage.run(true, () => Promise.resolve(fn())), not Promise.resolve(crossTenantAccessStorage.run(true, fn)).

Layer B: Postgres Row-Level Security

Migration: apps/backend/prisma/migrations/20260702000000_rls_tenant_isolation/migration.sql. One tenant_isolation policy per table backing a TENANT_SCOPED_MODELS entry:

ALTER TABLE "CompanyPost" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "CompanyPost" FORCE ROW LEVEL SECURITY;
CREATE POLICY tenant_isolation ON "CompanyPost"
  USING ("companyId" = current_setting('app.company_id', true) OR current_setting('app.company_id', true) = '__cross_tenant_access__')
  WITH CHECK ("companyId" = current_setting('app.company_id', true) OR current_setting('app.company_id', true) = '__cross_tenant_access__');

NESTED_TENANT_MODELS get a subquery-based policy against the parent table's companyId instead of a direct column comparison (e.g. CompanyAccountAnalytics checks UserSocialAccount.companyId via socialAccountId).

FORCE ROW LEVEL SECURITY is deliberate. This repo has no confirmed Postgres role separation between migration and runtime traffic (single connection-string resolution per environment — see apps/backend/src/infra/database-url.ts), so the runtime role may be the table owner, which bypasses RLS unless forced. FORCE has no effect on non-owner roles, so it's safe to include either way — verified empirically against a local Postgres instance with both an owning and a non-owning role (a superuser or BYPASSRLS role still bypasses regardless of FORCE; this repo's runtime role should never be granted either).

The cross-tenant sentinel. withCrossTenantAccess() satisfies the app-layer guard, but without a matching database-side signal, RLS would still see an unset app.company_id and return zero rows for every tenant-scoped table — silently breaking every legitimate cross-tenant call site the escape hatch is meant to allow. This was caught by the integration test in prisma.test.ts before it could reach production. The fix: every policy also allows through a reserved sentinel value (CROSS_TENANT_RLS_BYPASS_SENTINEL in apps/backend/src/infra/prisma.ts, '__cross_tenant_access__'), which withCrossTenantAccess() sets via SET LOCAL for the duration of the wrapped call. Real companyId values are Prisma-generated UUIDs and can never collide with this string.

Explicitly out of scope for the RLS migration (follow-up, not a silent gap):

• Segment.companyIds / Segment.excludeCompanyIds — array-typed feature-flag targeting, not tenant-owned content.
• ReviewAccessToken.targetCompanyId — not in TENANT_SCOPED_MODELS; cross-tenant by design (App Review access-grant tokens).

Auto-injected SET LOCAL

createSetLocalExtension(companyId) (apps/backend/src/middleware/tenant.ts) wraps every tenant-scoped operation — including raw $queryRaw/$executeRaw issued through a getTenantPrisma() client, which have no model for the WHERE-injection extension to key off — in $transaction([set_config('app.company_id', companyId, true), query(args)]). SET LOCAL only lasts for the current transaction, so the set_config() call and the actual operation must run in the same transaction batch; issuing them as two separate statements would let the setting reset before the query runs.

The existing app-layer WHERE companyId = ... injection stays as the fast path (avoids RLS policy evaluation for the common case); RLS is the backstop, not a replacement — both layers filtering the same way is intentional defense-in-depth.

Verification

• apps/backend/src/infra/prisma.test.ts — describe.skipIf(!hasDatabase)('cross-tenant isolation (real database)', ...): seeds two companies against a real Postgres connection, asserts getTenantPrisma() same-tenant reads return the right rows, cross-tenant reads never leak, the base-client guard throws, and withCrossTenantAccess() correctly bypasses both the guard and RLS.
• apps/backend/src/middleware/tenant.test.ts — pure logic test of createTenantExtension's WHERE/data injection (mocked Prisma.defineExtension, no real database).
• Manual RLS spike pattern (repeatable against any Postgres instance): create a non-owner app role and an owner role, ENABLE/FORCE ROW LEVEL SECURITY, confirm same-tenant reads succeed, cross-tenant reads return zero rows, and an unset session variable also returns zero rows (fails closed).

Sources

• Prisma Client Extensions
• Prisma Accelerate extension
• Postgres Row Security Policies
• Node.js AsyncLocalStorage