Designing a multi-tenant data model with PostgreSQL and Prisma

Every SaaS product eventually has to answer one question: how do we keep each customer's data separate? The answer shapes your schema, your queries and your incident reports.

Three common approaches

For most B2B products, shared tables with a tenantId column is the pragmatic default. The rest of this post is about making it safe.

The schema

Every tenant-owned table gets a required tenantId and composite indexes that start with it:

model Tenant {
  id        String     @id @default(cuid())
  name      String
  employees Employee[]
}
 
model Employee {
  id        String   @id @default(cuid())
  tenantId  String
  tenant    Tenant   @relation(fields: [tenantId], references: [id])
  email     String
  name      String
  createdAt DateTime @default(now())
 
  @@unique([tenantId, email])
  @@index([tenantId, createdAt])
}

Two details matter here:

• Uniqueness is per tenant. @@unique([tenantId, email]) lets two companies each have a hr@ address.
• Indexes lead with tenantId. Almost every query filters by tenant first, so the index should too.

Never trust the client with the tenant

The tenant id must come from the authenticated session, never from the request body:

// ✅ tenant comes from the verified token
const tenantId = req.auth.tenantId;
 
// ❌ never do this
const tenantId = req.body.tenantId;

Make the safe query the easy query

Forgetting a where: { tenantId } once is all it takes. Instead of relying on memory, scope the client per request with a Prisma extension:

import { PrismaClient } from "@prisma/client";
 
const prisma = new PrismaClient();
 
const WRITES_WITH_DATA = new Set(["create", "createMany", "createManyAndReturn"]);
 
export function forTenant(tenantId: string) {
  return prisma.$extends({
    query: {
      $allModels: {
        async $allOperations({ model, operation, args, query }) {
          if (model === "Tenant") return query(args);
          const a = args as Record<string, any>;
 
          if (WRITES_WITH_DATA.has(operation)) {
            // Stamp the tenant on everything we insert.
            a.data = Array.isArray(a.data)
              ? a.data.map((row: object) => ({ ...row, tenantId }))
              : { ...a.data, tenantId };
          } else if (operation === "upsert") {
            a.where = { ...a.where, tenantId };
            a.create = { ...a.create, tenantId };
          } else {
            // Reads, updates, deletes, counts and aggregates are filtered by tenant.
            // (Prisma 5+ accepts extra filters on findUnique/update/delete.)
            a.where = { ...a.where, tenantId };
          }
          return query(a as typeof args);
        },
      },
    },
  });
}

Routes then use forTenant(req.auth.tenantId) and can't accidentally read across tenants.

Add a second line of defence

Application-level scoping is good; database-level enforcement is better. PostgreSQL row-level security can reject cross-tenant reads even if a query is wrong:

ALTER TABLE "Employee" ENABLE ROW LEVEL SECURITY;
 
CREATE POLICY tenant_isolation ON "Employee"
  USING ("tenantId" = current_setting('app.tenant_id'));

Set app.tenant_id at the start of each transaction and the database does the checking for you.

Things that bite later

• Background jobs have no request — pass the tenant id explicitly into every job payload.
• Caches and file storage need the tenant in the key or path, too.
• Bulk imports should validate that referenced ids belong to the same tenant.
• Analytics across tenants should run through a separate, audited path.

Summary

Shared tables scale well and keep operations simple, as long as tenant scoping is automatic rather than remembered. Put tenantId everywhere, lead indexes with it, derive it from the session, and let the database enforce it as a backstop.