Multi-tenancy in Prisma without RLS: a tenant guard that fails loud

The most common way a B2B SaaS leaks one customer's data to another is boring: a findMany that forgot its where: { organizationId }. It typechecks. It passes review. It ships. Then org A sees org B's members, or API keys, or webhook endpoints — because at the ORM layer, "list the members" and "list this org's members" are the same call minus one clause.

Row-Level Security in Postgres is one answer. It's also a second policy language living next to your application, a per-connection SET app.current_org dance, and a class of bugs that only show up when the session variable isn't set the way you assumed. UseDeploy takes a different bet: keep the check in TypeScript, make it a Prisma client extension, and make a missing tenant filter throw at runtime instead of returning rows. This post walks through exactly how that guard works, what it deliberately does not cover, and why "fail loud" beats "fail silent" when an AI agent is writing half your repositories.

If you want the feature-level overview first, the tenancy feature page and the bounded-contexts doc cover the org/membership/role model; this post is the layer underneath it.

The threat model, concretely

Every org-owned table in the schema carries a non-null organizationId. The danger is not writes — the schema forces you to supply an organizationId on create. The danger is reads and mutations of existing rows: findMany, update, delete, count, aggregate, groupBy. Drop the filter and you operate across every tenant at once.

So the guard has a narrow, well-defined job: for a known set of tenant-scoped models, any operation that reads or mutates existing rows must prove that its where constrains organizationId. If it can't prove it, throw.

The guard is a Prisma $extends query extension

Here's the wiring. It's a $allModels / $allOperations extension that runs a pure assertion before handing off to the real query:

export const applyTenantGuard = (client: PrismaClient): PrismaClient =>
  client.$extends({
    name: 'tenant-guard',
    query: {
      $allModels: {
        $allOperations({ model, operation, args, query }) {
          assertTenantScoped(model, operation, args);
          return query(args);
        },
      },
    },
  }) as unknown as PrismaClient;

The client that repositories and request-context code receive is the wrapped one. The unwrapped client is exported separately and named so every bypass is greppable:

/** Default client for repositories and request-context code. Guarded. */
export const getPrismaClient = (): PrismaClient => {
  if (!guardedClient) guardedClient = applyTenantGuard(getBaseClient());
  return guardedClient;
};

/** UNGUARDED client for legitimate cross-tenant access: workers,
 *  token/hash lookups, GDPR export. Never inject wholesale. */
export const getSystemPrismaClient = (): PrismaClient => getBaseClient();

That split is the whole ergonomic story. The default is safe. The escape hatch exists — a BullMQ worker processing a job has no request org, a token-lookup resolves the org from the row it's fetching — but it's a different, explicitly-named dependency. A repository takes both and uses the system client only in the methods that genuinely span tenants, so grep prismaSystem audits every place the wall is stepped over.

What "tenant-scoped" means, and what it doesn't

The guard only fires for models on an explicit allowlist:

export const TENANT_SCOPED_MODELS: Set<string> = new Set([
  'ApiKey',
  'Role',
  'OrganizationMember',
  'Invitation',
  'WebhookEndpoint',
  'UsageCounter',
  'UsageEvent',
]);

The comment next to it is load-bearing, because the interesting design decisions are the exclusions:

• Organization is not listed — it is the tenant; there's no organizationId on the tenant itself.
• User, Session, Account are user-scoped, not org-scoped.
• EventOutbox is global infrastructure: the outbox pollers drain events across every org by design (more on the outbox in the webhooks post).
• Subscription / Purchase are scoped by a billing customer id, not organizationId.
• AuditEntry's organizationId is nullable — global platform entries are legitimate.

This allowlist is a maintenance surface, and the code says so: "When you add a new org-owned model to schema.prisma, add it here too." That's a deliberate tradeoff. An "everything is guarded unless excluded" default would be safer in the abstract but would fight you constantly on the many models that are legitimately not org-scoped. An allowlist keeps the guard honest about the small set of tables that actually carry cross-tenant risk.

The guarded operations are equally explicit:

const GUARDED_OPERATIONS: Set<string> = new Set([
  'findUnique', 'findUniqueOrThrow', 'findFirst', 'findFirstOrThrow',
  'findMany', 'count', 'aggregate', 'groupBy',
  'update', 'updateMany', 'delete', 'deleteMany', 'upsert',
]);

create and createMany are absent on purpose: the schema already requires a non-null organizationId in data, so there's nothing to prove.

Proving a query is scoped is harder than where.organizationId != null

The naive check — "is there an organizationId key in where?" — is both too weak and too strong. Too weak, because { organizationId: { not: someValue } } mentions the field but doesn't constrain rows to one org. Too strong, because real queries scope through compound unique selectors like organizationId_userId, not a bare organizationId.

So the guard has an actual predicate. A value "constrains" the org if it's a non-empty string, { equals: '...' }, or a non-empty { in: [...] }:

const constrainsOrg = (value: unknown): boolean => {
  if (typeof value === 'string') return value.length > 0;
  if (value !== null && typeof value === 'object') {
    const v = value as Record<string, unknown>;
    if (typeof v['equals'] === 'string') return true;
    if (Array.isArray(v['in']) && v['in'].length > 0) return true;
  }
  return false;
};

And hasOrgConstraint walks the where for any of the accepted forms: top-level organizationId; a compound unique selector that embeds it (e.g. organizationId_name, organizationId_userId); or an AND branch that satisfies either. Crucially, OR and NOT branches do not count — they don't constrain every row the query touches:

export const hasOrgConstraint = (where: unknown): boolean => {
  if (where === null || typeof where !== 'object') return false;
  const w = where as Record<string, unknown>;
  if (constrainsOrg(w['organizationId'])) return true;
  for (const [key, value] of Object.entries(w)) {
    if (key === 'OR' || key === 'NOT' || key === 'organizationId') continue;
    if (key === 'AND') {
      const branches = Array.isArray(value) ? value : [value];
      if (branches.some((b) => hasOrgConstraint(b))) return true;
      continue;
    }
    if (key.includes('organizationId') && value !== null && typeof value === 'object') {
      if (constrainsOrg((value as Record<string, unknown>)['organizationId'])) return true;
    }
  }
  return false;
};

The whole thing collapses into one assertion, which is what the extension calls:

export const assertTenantScoped = (model: string, operation: string, args: unknown): void => {
  if (!TENANT_SCOPED_MODELS.has(model)) return;
  if (!GUARDED_OPERATIONS.has(operation)) return;
  const where = (args as { where?: unknown } | undefined)?.where;
  if (!hasOrgConstraint(where)) {
    throw new TenantScopeViolationError(model, operation);
  }
}

Because assertTenantScoped, hasOrgConstraint, and constrainsOrg are pure functions, they're unit-tested directly — no Prisma, no database. The extension is a three-line wrapper around logic you can exhaustively cover with a table of where shapes.

What it looks like in a repository

Here's the payoff at the call site. The membership repository never reaches for a session variable or a policy; it just passes the org id it already has, and the guard is satisfied:

async listByOrganization(orgId: OrganizationId): Promise<ReadonlyArray<Membership>> {
  const rows = await this.prisma.organizationMember.findMany({
    where: { organizationId: orgId },
    orderBy: { createdAt: 'asc' },
  });
  return rows.map(MembershipMapper.toDomain);
}

async findByOrgAndUser(orgId: OrganizationId, userId: UserId): Promise<Membership | null> {
  const row = await this.prisma.organizationMember.findUnique({
    where: { organizationId_userId: { organizationId: orgId, userId } },
  });
  return row ? MembershipMapper.toDomain(row) : null;
}

And the one method that genuinely can't be org-scoped — "list all orgs this user belongs to", which is user-scoped by definition — reaches for the system client on purpose, with a comment saying why:

async listByUser(userId: UserId): Promise<ReadonlyArray<Membership>> {
  // User-scoped by design (lists the user's orgs) — system client.
  const rows = await this.prismaSystem.organizationMember.findMany({
    where: { userId },
    orderBy: { createdAt: 'desc' },
  });
  return rows.map(MembershipMapper.toDomain);
}

The org id itself arrives from the request boundary. The attachOrganizationContext middleware reads the x-organization-id header, verifies the caller actually holds a membership + role in that org (and that the org isn't suspended), and only then populates req.organization. The repository layer never trusts a raw header; it works with an org id the middleware already authorized.

The honest limitations

This is a guardrail, not a formal proof, and it says so in its own docstring:

• Raw SQL is not intercepted. $queryRaw and $executeRaw bypass model-level query extensions entirely — Prisma doesn't route them through $allOperations. Raw SQL remains the author's responsibility. The one place the boilerplate uses raw UPDATEs (the usage quota enforcer) scopes them by organizationId by hand.
• It checks the shape of where, not the value. The guard proves a query is constrained to some org; it does not prove it's constrained to the caller's org. Passing the authorized org id from the request context is still on you — the guard catches the "forgot the filter entirely" bug, which is the one that actually ships.
• Interactive transactions inherit the guard (extensions propagate into $transaction callbacks), so you don't get a silent gap the moment you wrap two writes in a transaction.

Why "throw" is the right default with agents in the loop

The reason this design earns its keep isn't Postgres-vs-application-layer theology. It's the failure mode. A forgotten tenant filter that returns rows is a data leak you discover in a support ticket. A forgotten tenant filter that throws TenantScopeViolationError on first execution is a stack trace in your test run.

That matters double when an AI coding agent is writing repositories. The agent sees a slice of the codebase per session and confidently writes a findMany that mirrors the others — minus the where it didn't know was mandatory. With RLS, that query quietly succeeds in dev (session variable set by some middleware the agent didn't touch) and leaks in a context the agent never tested. With the guard, it throws the first time it runs, with a message that tells you exactly what to do:

Tenant guard: OrganizationMember.findMany executed without an organizationId constraint in where. Scope the query by organizationId, or inject getSystemPrismaClient() explicitly for a legitimate system-context access.

The wall is in the code path, not in a policy the agent can't see. That's the whole philosophy: make the boundary something the compiler and the runtime enforce, so neither a tired human nor a context-limited agent can cross it by accident.

The org/role model this sits under — memberships, RBAC, per-org API keys — is documented on the tenancy feature page and in the RBAC doc. If you're deciding whether a query needs the system client, the rule of thumb is in this post: does the operation belong to one org, or does it legitimately span all of them? Org-scoped is the default; everything else is a named, greppable exception.