Multi-Tenant Isolation with Prisma and tRPC

The real multi-tenant problem

In a multi-tenant SaaS, each customer (tenant) should only ever see their own data. That sounds simple. In practice, it's one of the most critical sources of bugs: a single query that forgets the organizationId filter leaks one organization's data to another.

That's not a feature bug. It's a data breach.

The naive fix: add WHERE organizationId = ? everywhere, by hand. The problem: with 70+ routers and 160+ models, you will eventually forget. Not because you're careless, just because you're human, tired on a Friday afternoon, or moving fast. You will forget.

The robust fix: enforce the filter automatically on reads, and constrain writes with a rule simple enough to check in code review.

Approach 1: Manual filtering (fragile)

// ❌ Dangerous pattern: easy to forget
export const clientRouter = createTRPCRouter({
  getAll: staffProcedure.query(async ({ ctx }) => {
    return ctx.db.client.findMany({
      // If this line is missing → data leaks across tenants
      where: { organizationId: ctx.session.user.organizationId },
    });
  }),
});

With this approach, every developer touching a router has to remember to add the filter. One miss is a security incident.

Approach 2: Prisma $extends (recommended)

Prisma Client Extensions ($extends) let you intercept queries and inject filters into them automatically.

// src/lib/prisma-org-scope.ts
 
// Models that carry a direct organizationId
const ORG_SCOPED_MODELS = new Set([
  "client",
  "invoice",
  "quote",
  "project",
  "employee",
  // ... 30+ other models
]);
 
export function createOrgDb(db: PrismaClient, organizationId: string) {
  return db.$extends({
    query: {
      $allModels: {
        async findMany({ model, args, query }) {
          if (ORG_SCOPED_MODELS.has(model.toLowerCase())) {
            args.where = { ...args.where, organizationId };
          }
          return query(args);
        },
 
        async findFirst({ model, args, query }) {
          if (ORG_SCOPED_MODELS.has(model.toLowerCase())) {
            args.where = { ...args.where, organizationId };
          }
          return query(args);
        },
      },
    },
  });
}

Result: ctx.orgDb.client.findMany() runs WHERE organizationId = 'org_xxx' automatically. For reads, the filter no longer depends on any single developer remembering it. (Simplified excerpt: the full version also covers findFirstOrThrow, count, aggregate, and groupBy.)

Wiring it into the tRPC context

orgDb is built once per request and injected into the context:

// src/server/api/trpc.ts
import { createOrgDb } from "~/lib/prisma-org-scope";
 
async function createTRPCContext({ req }: { req: NextRequest }) {
  const session = await auth();
  const organizationId = session?.user?.organizationId;
 
  return {
    db, // Full access (admin, webhooks)
    orgDb: organizationId // Scoped access (regular routers)
      ? createOrgDb(db, organizationId)
      : null,
    session,
  };
}

Inside routers:

// src/server/api/routers/client.ts
export const clientRouter = createTRPCRouter({
  // ✅ No manual filter: orgDb handles it
  getAll: staffProcedure.query(async ({ ctx }) => {
    return ctx.orgDb.client.findMany({
      orderBy: { createdAt: "desc" },
    });
  }),
 
  getById: staffProcedure
    .input(z.object({ id: z.string() }))
    .query(async ({ ctx, input }) => {
      // findFirst (never findUnique) so the organizationId filter actually applies
      const client = await ctx.orgDb.client.findFirst({
        where: { id: input.id },
      });
      if (!client) throw new TRPCError({ code: "NOT_FOUND" });
      return client;
    }),
});

Why `findFirst`, and never `findUnique`?

This is a critical rule. The extension above doesn't cover findUnique (its where only accepts unique fields), so the organizationId filter simply does not apply:

// ❌ Dangerous: findUnique isn't covered by the extension
const client = await ctx.orgDb.client.findUnique({
  where: { id: input.id },
  // organizationId is NOT added automatically
});
 
// ✅ Safe: findFirst is covered by the extension
const client = await ctx.orgDb.client.findFirst({
  where: { id: input.id },
  // organizationId is added by $extends
});

An attacker who guesses or leaks a valid ID from another organization can read that record if you used findUnique. Always use findFirst for tenant-scoped resources.

Isolating writes

$extends covers reads. For writes (create, update, delete), you have to add organizationId by hand:

// Create: always include organizationId
create: staffProcedure
  .input(createClientSchema)
  .mutation(async ({ ctx, input }) => {
    return ctx.db.client.create({
      data: {
        ...input,
        organizationId: ctx.session.user.organizationId, // ← required
      },
    });
  }),
 
// Update: organizationId in the WHERE, not just in the data
update: staffProcedure
  .input(updateClientSchema)
  .mutation(async ({ ctx, input }) => {
    return ctx.db.client.update({
      where: {
        id: input.id,
        organizationId: ctx.session.user.organizationId, // ← IDOR protection
      },
      data: input.data,
    });
  }),

Without organizationId in the where of an update, a user who knows a valid ID from another organization could modify its data (IDOR: Insecure Direct Object Reference).

RBAC as an extra layer

Multi-tenant isolation protects the boundaries between organizations. RBAC protects the boundaries inside a single organization:

// src/lib/permissions/matrix.ts
export const PERMISSION_MATRIX = {
  ADMIN: ["*"], // Everything
  DIRECTION: ["facturation:*", "crm:*"], // Finance + CRM
  MANAGER: ["crm:read", "crm:create"], // CRM read/create
  CLIENT: ["portal:read"], // Client portal only
} as const;

// Usage inside routers. Permission keys are literal strings from the matrix
// file, kept in French like the rest of the domain vocabulary (invoices,
// quotes...) — that's what actually appears in the source, not a translation gap.
export const invoiceRouter = createTRPCRouter({
  create: requirePermission("facturation:create")
    .input(createInvoiceSchema)
    .mutation(async ({ ctx, input }) => {
      // Guaranteed: organization scoped + permission checked
    }),
});

Nested-entity pattern (no direct organizationId)

Some models don't carry a direct organizationId (an invoice line, for example, belongs to an invoice). Isolation then goes through a nested filter on the parent:

// InvoiceLine has no organizationId, but Invoice does
const line = await ctx.db.invoiceLine.findFirst({
  where: {
    id: input.id,
    invoice: {
      // ← filtered through the parent
      organizationId: ctx.session.user.organizationId,
    },
  },
});

Index is not optional

Every scoped model needs an index on organizationId:

model Invoice {
  organizationId String
  @@index([organizationId])
}

Without that index, the automatic filter still adds WHERE organizationId = ?, but Postgres has to scan the full table to apply it. That's fine with 100 rows. It isn't with 100,000.

Security test

A good isolation test verifies that a user from one organization can't reach another organization's data:

// src/__tests__/security/tenant-isolation.test.ts
it("cannot read another organization's clients", async () => {
  const clientOrg1 = await createClient({ organizationId: "org-1" });
 
  const caller = createCaller({ organizationId: "org-2" });
 
  // Must return NOT_FOUND, never org-1's data
  await expect(
    caller.client.getById({ id: clientOrg1.id }),
  ).rejects.toMatchObject({ code: "NOT_FOUND" });
});

Recap: the rule in 4 points

Reads → ctx.orgDb (auto-filtered via $extends)
Writes → ctx.db with organizationId in the where
Never findUnique on scoped resources → always findFirst
Nested entities → filter through the parent

This pattern significantly cuts the risk of a leak: reads are filtered by construction, and writes follow one rule that's easy to check in code review and in tests. In a B2B SaaS, that's the baseline for customer trust.

Pre-deployment checklist

Every multi-tenant model has organizationId
organizationId is indexed on every scoped model
No findUnique on scoped resources
Isolation tests exist for every critical router
ctx.orgDb for reads, manual filter for writes

Multi-Tenant Isolation with Prisma and tRPC: What the Automatic Filter Covers, and What Doesn't

The real multi-tenant problem

Approach 1: Manual filtering (fragile)

Approach 2: Prisma $extends (recommended)

Wiring it into the tRPC context

Why `findFirst`, and never `findUnique`?

Isolating writes

RBAC as an extra layer

Nested-entity pattern (no direct organizationId)

Index is not optional

Security test

Recap: the rule in 4 points

Pre-deployment checklist

Go further

Ready to launch your SaaS?

The real multi-tenant problem

Approach 1: Manual filtering (fragile)

Approach 2: Prisma $extends (recommended)

Wiring it into the tRPC context

Why findFirst, and never findUnique?

Isolating writes

RBAC as an extra layer

Nested-entity pattern (no direct organizationId)

Index is not optional

Security test

Recap: the rule in 4 points

Pre-deployment checklist

Go further

Ready to launch your SaaS?

Why `findFirst`, and never `findUnique`?