Multi-tenant Prisma 7 without database row-level security — and the race condition my first cut introduced

Every multi-tenant SaaS needs a way to keep tenants apart. The textbook answer is Postgres row-level security (RLS): you write a policy, the database enforces it, and every query is automatically scoped. It's the right answer when you can afford the operational complexity.

For Groundrun — the Next.js 16 SaaS boilerplate I'm shipping for Claude Code users — I didn't ship RLS. I shipped a query-layer enforcement pattern using Prisma 7's $extends API. This piece is the reasoning, the implementation, the race condition my first cut introduced, and the structured review pass that caught it before commit.

Why not RLS in a Next.js SaaS template?

Postgres RLS is the gold standard for tenant isolation. It moves the enforcement from your application code into the database itself, which means:

• A bug in the application layer can't leak data across tenants — the database refuses the query.
• Any code path that hits the database is enforced, including accidental ones you forgot existed.
• The enforcement is auditable in a single place (`CREATE POLICY` lines in a migration).

These are real wins. So why didn't I ship it in Groundrun?

Three reasons, in order of weight for a paid template:

(1) The handoff cost to a buyer is non-trivial. RLS requires running every connection under a tenant-scoped Postgres role, setting a session variable (SET LOCAL app.current_tenant = ...) on every request, and writing a policy per table. For a buyer who's cloning the template and expects to be in `pnpm dev` in under twenty minutes, that's three documented steps they have to get right before their first feature ships.

(2) Connection pooling tooling fights you. Statement pools (PgBouncer in `transaction` or `statement` mode, the default for most hosted Postgres) drop session-level state between statements. RLS with session variables requires session-level pooling, which constrains the buyer's hosting choices. The cleanest workaround is per-request connections — fine on a $30/month VPS, painful when you want serverless edge to work.

(3) The boilerplate's audience is shipping their first SaaS, not their fifth. A pattern that costs the buyer real debugging time on day one — "why does my `SELECT` return nothing, oh right I forgot to set the session variable in this server action" — is a worse default than a pattern that's slightly less rigorous but works the way Prisma already works.

So I shipped application-layer enforcement at the Prisma query layer, with a clear escape hatch for buyers who want to add RLS on top later as defense in depth. Most of them won't need to. The ones who do can layer RLS without changing the application code.

What query-layer enforcement looks like

Prisma 7 exposes a $extends API that lets you intercept every query operation, mutate the arguments, and decide whether to pass it through or refuse it. The shape:

prisma.$extends({
  name: 'tenant-scope',
  query: {
    $allModels: {
      async $allOperations({ model, operation, args, query }) {
        // decide what to do with this operation
        return query(args)  // run it as-is, or mutate args, or throw
      },
    },
  },
})

The interesting question is: what do you do inside that callback for each operation? Naively, you'd inject where.organizationId on every query and call it a day. But that breaks down differently for different operation shapes — and one of those shapes hides a subtle bug.

The four operation shapes — and why each needs different handling

Prisma operations split into four classes for tenant scoping:

Class 1: list reads + bulk writes

findMany, findFirst, findFirstOrThrow, count, aggregate, groupBy, updateMany, deleteMany.

These all take a where clause. Injecting where.organizationId is exactly right — Prisma compiles the predicate into the SQL and the database does the filtering.

if (LIST_READ_OPS.has(operation) || BULK_WRITE_OPS.has(operation)) {
  const a = args as { where?: Record<string, unknown> }
  return query({ ...a, where: { ...(a.where ?? {}), organizationId } })
}

Spread order matters here: ...(a.where ?? {}), organizationId means our organizationId wins over any attacker-supplied value in the caller's spread. Reverse the order and an attacker who controls the where argument can override the tenant filter — which is exactly the failure mode you're trying to prevent.

Class 2: creates

create and createMany take a data argument, not a where. The tenant constraint is on the row being inserted, not on a filter.

Validate up front, throw if mismatch, otherwise let Prisma run:

if (operation === 'create') {
  const a = args as { data: Record<string, unknown> }
  if (a.data?.organizationId !== organizationId) {
    throw new TenantViolationError(
      `${model}.create requires data.organizationId === '${organizationId}'`,
    )
  }
  return query(args)
}

createMany is the same shape with one extra wrinkle — check every row in the batch, refuse on the first mismatch.

Class 3: single-record reads

findUnique and findUniqueOrThrow are unusual. They take a where argument like list reads, but the where must hit a unique index. You can't add an arbitrary organizationId predicate to a unique-by-id lookup; Prisma will reject it as not matching the unique definition.

The fix is to post-check the result:

if (READ_SINGLE_OPS.has(operation)) {
  const result = await query(args)
  if (
    result &&
    (result as { organizationId?: string }).organizationId !== organizationId
  ) {
    if (operation === 'findUnique') return null
    throw new TenantViolationError(
      `${model}.${operation} refused: row belongs to another tenant`,
    )
  }
  return result
}

Run the query, get the row back, check whose tenant it belongs to, return null (for findUnique) or throw (for findUniqueOrThrow) on mismatch. Post-check is safe for reads. Nothing has changed in the database — the row you just read isn't different now than it was before you read it. Returning null for a mismatched read is the right semantic: the caller sees "no such row" because, as far as their tenant is concerned, there isn't one.

Class 4: single-record writes (and the subtle bug)

update, delete, and upsert are where my first cut went wrong.

These take a where hitting a unique index, same as single-record reads. So my first instinct was: do the same thing — run the query, post-check the row's organizationId after the fact, throw if it's not ours.

// FIRST CUT — UNSAFE. DO NOT DO THIS.
const result = await query(args)
if ((result as any).organizationId !== organizationId) {
  throw new TenantViolationError(`...`)
}
return result

Look at that await query(args) line. By the time the promise resolves, Prisma has already committed the write. The tenant check fires after the row has been mutated. We've thrown an exception, but we've also corrupted another tenant's data on the way to the throw.

This isn't a hypothetical. I ran two rounds of multi-agent review on the first cut (Code Reviewer + Security Engineer + Database Optimizer, in parallel). Three independent reviewers converged on the same finding: my "tenant-safe" helper had a write-then-check data-corruption vector. The vector wasn't in the original schema audit — I created it in the fix that closed the schema audit's finding.

The corrected pattern is to pre-check ownership via the base client BEFORE calling query(args):

if (WRITE_SINGLE_OPS.has(operation)) {
  const a = args as { where: Record<string, unknown> }
  // base-client lookup, NOT the extended client (which would recurse)
  const existing = await delegateFor(model).findUnique({ where: a.where })
  if (existing && existing.organizationId !== organizationId) {
    throw new TenantViolationError(
      `${model}.${operation} refused: row belongs to another tenant`,
    )
  }
  // If the row doesn't exist, let Prisma surface its own NotFound semantics.
  return query(args)
}

Now the throw happens before any mutation. The base-client lookup is a single indexed SELECT on the unique column — fast, no side effects, no risk of recursion (the extended client would itself hit the tenant interceptor and try to scope by tenant, which doesn't work on a `findUnique`).

upsert is dual-path: if the row exists, pre-check ownership; if it doesn't, validate create.organizationId exactly like a plain create.

The TOCTOU window — and why it's benign in practice

Astute readers will have noticed: there's a window between the pre-check and the actual write. In theory, another transaction could change the row's organizationId between our check and our update, defeating the protection.

In practice, this is benign in any sensible multi-tenant model because organizationId on a business row is immutable. Subscriptions don't reassign between organizations; customers don't get transplanted; memberships are deleted and recreated, not edited to point at a different org.

The pre-check result stays valid for the duration of the single-statement write that follows. If you ever build a feature that legitimately transfers rows between tenants (an admin tool, an "export my data" flow), guard it with SERIALIZABLE isolation and a dedicated migration path — but that's a deliberate exception, not a case the everyday helper needs to defend.

What the helper doesn't cover

The pattern is robust within Prisma's normal query surface. There are three documented limits:

(1) Raw SQL. $queryRaw and $executeRaw bypass the extension entirely. If your codebase uses raw SQL — for performance reasons, or to use a Postgres feature Prisma doesn't expose — you're on the hook for adding WHERE organization_id = $1 yourself. This is a real escape hatch, but it should be rare in a well-designed application layer.

(2) Nested writes. prisma.user.create({ data: { memberships: { create: { organizationId: 'other-org' } } } }) creates a nested record that the tenant extension can't intercept directly — the operation is "user.create", not "membership.create". The pattern: when you have a nested write that crosses into a tenant-scoped model, use tenantPrisma(orgId).membership.create({...}) explicitly rather than relying on the nested create.

(3) Transactions across multiple tenants. By design, the helper assumes a single active tenant per request. If you need to atomically touch two tenants in one transaction (an admin merge, say), drop down to the bare prisma client and audit the code manually. Don't extend the helper to accept multiple tenants — that turns a clear invariant into a fuzzy one.

The full pattern, in one file

The complete lib/tenancy.ts is ~150 lines. Source on GitHub. The shape:

export function tenantPrisma(organizationId: string) {
  if (!organizationId) {
    throw new TenantViolationError('tenantPrisma: organizationId is required')
  }

  return prisma.$extends({
    name: 'tenant-scope',
    query: {
      $allModels: {
        async $allOperations({ model, operation, args, query }) {
          if (!TENANT_SCOPED_MODELS.has(model)) return query(args)

          if (LIST_READ_OPS.has(operation) || BULK_WRITE_OPS.has(operation)) {
            // ... where-injection
          }
          if (operation === 'create' || operation === 'createMany') {
            // ... data.organizationId validation
          }
          if (READ_SINGLE_OPS.has(operation)) {
            // ... post-check read result
          }
          if (WRITE_SINGLE_OPS.has(operation)) {
            // ... pre-check via base client BEFORE mutating
          }
          if (operation === 'upsert') {
            // ... dual path
          }

          return query(args)
        },
      },
    },
  })
}

Plus a TENANT_SCOPED_MODELS set at the top of the file (currently Membership, Customer, Subscription — grows as you add business models), a TenantViolationError class, and a thin withTenant(orgId, fn) callback wrapper for code paths that prefer that style.

The rule the team enforces alongside

In app/(app)/* (any route under the authenticated dashboard), ALL database access goes through tenantPrisma(orgId) or withTenant(orgId, ...). The bare prisma client is reserved for webhook handlers, cron jobs, and tenant-management code.

This rule lives in the project's CLAUDE.md and is the first thing Claude Code sees at session start. Pattern adherence catches the "I forgot to scope" failure mode before it ships — when Claude writes new server code, it defaults to the helper because the rule is in its working context.

When you should still use RLS

Query-layer enforcement is the right default for a buyer-facing SaaS template. It's not always the right ceiling. Layer RLS on top when:

• You're shipping defense in depth and the cost of a logic bug in your application is high (regulated data, PII, multi-region compliance).
• You have direct DB access tools that bypass your application layer (analytics dashboards reading replicas, BI tools connecting via Postgres directly).
• You're providing read-only data exports to a third party and want tenant isolation enforced by the database, not by the exporter.

The pattern in lib/tenancy.ts doesn't conflict with RLS — it sits above it. Add RLS as a v2 hardening layer; the application code doesn't need to change. App-layer enforcement keeps the dev loop fast; DB-layer enforcement keeps the threat model honest.

The meta-lesson — second-pass review on your own fixes

The write-then-check race in my first cut wasn't a bug in the original schema audit. It was a bug I introduced while closing the schema audit's finding. The only thing that caught it was running a fresh multi-agent review on the fix itself, BEFORE committing.

This was the third confirmation of the same pattern in the same project: running specialised reviewers (Code Reviewer + Security Engineer + Database Optimizer) in parallel on a change, looking for independent convergence, catches a class of bug that no other process catches reliably. The bugs you introduce while fixing other bugs need their own audit pass.

For a paid template that amplifies every defect across every buyer, this is the cheapest insurance available.

Where this lives

The full lib/tenancy.ts + the project's docs/CLAUDE-db.md documenting the rule + the onDelete: Restrict billing FK guarantees ship in the Groundrun template. £179 at launch (Mon 6 July 2026), £99 flat for waitlist members — the price holds if you're on the list before launch.

For the companion piece on choosing the MCP servers that ship with the template, see How I picked 4 MCP servers for a Next.js SaaS boilerplate.