How to Build a Multi-Tenant SaaS with Prisma and Postgres

Ship a multi-tenant SaaS wrong and you risk the most expensive bug of your life: a tenant data leak. This guide walks through a production-ready approach with Prisma and Postgres that keeps data isolated, scales under load, and doesn’t box you in when enterprise customers arrive.

The short version: start with a single Postgres database using Row-Level Security (RLS), scope queries via Prisma inside a transaction that sets the tenant context, and enforce composite uniqueness and indexing on tenant_id. Add predictable provisioning, billing, and monitoring. When you outgrow it, you can graduate large customers to their own database without throwing away your code.

Architecture options for multi-tenant Prisma + Postgres

You have three viable isolation models. Prisma adds a practical constraint: it doesn’t support dynamic runtime schemas, so “per-tenant schema” is awkward. In our experience as builders, teams either start with RLS in one database or allocate separate databases for very large/regulated tenants.

If you’re using Prisma, start with “Single DB + RLS.” For enterprise or regulatory needs, move specific customers to dedicated databases using a per-tenant connection string while keeping the same application code.

Reference architecture (what we actually deploy)

• API: Node.js (Fastify or Express) + Prisma Client
• DB: Postgres 14+ (RDS/Cloud SQL/Supabase), RLS enabled
• Pooling: PgBouncer (session pooling) or Prisma Accelerate for serverless
• Cache/queues: Redis (BullMQ) for background jobs and rate limits
• Storage: S3/GCS for files; signed URLs per tenant
• Auth: JWT (tenant claim), or Auth0/Cognito with app_metadata for tenant
• Billing: Stripe (subscriptions, seats) + webhooks
• Edge/CDN: Cloudflare/Akamai; turn on caching for static assets
• Observability: OpenTelemetry traces, Sentry, pg_stat_statements, pganalyze/grafana

Flow (happy path):

1. Request arrives with an Authorization header whose JWT includes tenantId.

2. API starts a short transaction; first statement SET LOCAL app.current_tenant = '{tenant-uuid}'.

3. All Prisma queries in that transaction run under Postgres RLS policies filtering on current_setting('app.current_tenant').

4. Business logic operates on tenant-scoped rows; composite unique constraints prevent cross-tenant conflicts.

5. Outgoing events are published with tenant scoping keys in Redis or your queue.

6. For long-running tasks, background workers set the same SET LOCAL before touching the DB.

One dry-wit aside: if your “tenant_id” index is missing, your database will introduce you to the concept of “meditation under 100% CPU.”

Schema design with Prisma

Make the tenant relationship explicit everywhere:

• Every business table includes tenantId (UUID) with an index
• Composite uniqueness enforces tenant-level uniqueness (e.g., tenant + email)
• Foreign keys reference tenantId consistently to stop accidental cross-tenant joins

Example schema.prisma

// schema.prisma
// Postgres + Prisma 5.x

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "postgresql"
  url      = env("DATABASE_URL")
}

model Tenant {
  id          String   @id @default(uuid()) @db.Uuid
  name        String
  slug        String
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  users       User[]
  projects    Project[]

  @@unique([slug])
}

model User {
  id          String   @id @default(uuid()) @db.Uuid
  tenantId    String   @db.Uuid
  email       String
  role        UserRole @default(MEMBER)
  hashedPw    String
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  tenant      Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  projects    Project[] @relation("ProjectMembers")

  @@index([tenantId])
  @@unique([tenantId, email])
}

enum UserRole {
  OWNER
  ADMIN
  MEMBER
}

model Project {
  id          String   @id @default(uuid()) @db.Uuid
  tenantId    String   @db.Uuid
  name        String
  slug        String
  createdById String   @db.Uuid
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

  tenant      Tenant   @relation(fields: [tenantId], references: [id], onDelete: Cascade)
  createdBy   User     @relation(fields: [createdById], references: [id])
  members     User[]   @relation("ProjectMembers")

  @@index([tenantId])
  @@unique([tenantId, slug])
}

model ProjectMembership {
  // Example join table with tenant in the PK to keep lookups cheap
  tenantId String @db.Uuid
  userId   String @db.Uuid
  projectId String @db.Uuid

  user    User    @relation(fields: [userId], references: [id], onDelete: Cascade)
  project Project @relation(fields: [projectId], references: [id], onDelete: Cascade)
  tenant  Tenant  @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  @@id([tenantId, userId, projectId])
  @@index([tenantId, userId])
  @@index([tenantId, projectId])
}

Note the repeated tenantId in every relation and the composite unique/index directives. This is critical for both correctness and query plans.

Enforcing isolation with Postgres RLS

Create a per-request tenant context using a custom GUC (Grand Unified Configuration) and write simple RLS policies. GUCs are cheap and don’t require extensions.

SQL: RLS setup

-- Enable RLS and create policies; run in a Prisma migration or a SQL migration

-- 1) A helper function to parse the current tenant from GUC
CREATE OR REPLACE FUNCTION app.current_tenant_id() RETURNS uuid AS $$
  SELECT NULLIF(current_setting('app.current_tenant', true), '')::uuid;
$$ LANGUAGE sql STABLE;

-- 2) Enable RLS on tables
ALTER TABLE "Tenant" ENABLE ROW LEVEL SECURITY; -- We'll add a strict policy for tenants you own
ALTER TABLE "User" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "Project" ENABLE ROW LEVEL SECURITY;
ALTER TABLE "ProjectMembership" ENABLE ROW LEVEL SECURITY;

-- 3) Policies
-- Tenants: allow access only if you reference your own tenant id. Owners/admins may need broader checks in app code.
CREATE POLICY tenant_isolation ON "Tenant"
  USING (id = app.current_tenant_id());

CREATE POLICY user_isolation ON "User"
  USING ("tenantId" = app.current_tenant_id())
  WITH CHECK ("tenantId" = app.current_tenant_id());

CREATE POLICY project_isolation ON "Project"
  USING ("tenantId" = app.current_tenant_id())
  WITH CHECK ("tenantId" = app.current_tenant_id());

CREATE POLICY membership_isolation ON "ProjectMembership"
  USING ("tenantId" = app.current_tenant_id())
  WITH CHECK ("tenantId" = app.current_tenant_id());

-- 4) Optional: forbid access if context is missing
ALTER TABLE "User" FORCE ROW LEVEL SECURITY;
ALTER TABLE "Project" FORCE ROW LEVEL SECURITY;
ALTER TABLE "ProjectMembership" FORCE ROW LEVEL SECURITY;

With FORCE ROW LEVEL SECURITY, queries without a tenant context read zero rows, which is preferable to leaks.

Scoping Prisma queries safely

Set the GUC for the duration of a transaction, then execute all Prisma operations inside that transaction.

// tenant-context.ts
import { PrismaClient } from '@prisma/client';
const prisma = new PrismaClient({
  // Useful in dev to see slow queries; don’t log full SQL in prod without redaction
  log: [{ level: 'query', emit: 'event' }]
});

export async function withTenant<T>(tenantId: string, fn: (tx: PrismaClient) => Promise<T>) {
  return prisma.$transaction(async (tx) => {
    // Set per-transaction GUC
    await tx.$executeRaw`SET LOCAL app.current_tenant = ${tenantId}`;
    return fn(tx as unknown as PrismaClient);
  });
}

// Usage in a route handler
// await withTenant(req.tenantId, async (db) => db.project.findMany({ where: { /* no tenantId needed */ } }));

This eliminates the risk of forgetting where: { tenantId } on every query. You still keep the tenantId field for indexes, constraints, and background validations.

If you run PgBouncer in transaction pooling, disable prepared statements or use session pooling; with Prisma, append ?pgbouncer=true to the connection string so it doesn’t rely on session-level prepared statements.

Provisioning, onboarding, and billing per tenant

A reliable tenant creation flow saves support time.

• Reserve slug per tenant with an atomic insert (@@unique([slug]))
• Create Tenant, create User as OWNER, seed default projects/roles
• Send a verify email; rate-limit invites per tenant in Redis (t:{tenantId}:invites)
• Stripe subscription: product per plan, usage per seat or per project

Provisioning endpoint

// routes/tenants.ts (Express)
import type { Request, Response } from 'express';
import { prisma } from '../prisma';
import slugify from 'slugify';

export async function createTenant(req: Request, res: Response) {
  const { name, email, password } = req.body;
  const slug = slugify(name, { lower: true, strict: true });

  const result = await prisma.$transaction(async (tx) => {
    const tenant = await tx.tenant.create({ data: { name, slug } });
    const user = await tx.user.create({
      data: {
        tenantId: tenant.id,
        email: email.toLowerCase(),
        role: 'OWNER',
        hashedPw: await hash(password)
      }
    });
    // Seed example project
    await tx.project.create({
      data: { tenantId: tenant.id, name: 'Getting Started', slug: 'getting-started', createdById: user.id }
    });
    return { tenant, user };
  });

  // Create Stripe customer + subscription out-of-band (queue a job)
  await queue.add('stripe:provision', { tenantId: result.tenant.id, email });
  res.status(201).json({ ok: true });
}

Stripe webhooks (seat counting)

Keep billing logic idempotent and tenant-aware.

// stripe-webhook.ts
import Stripe from 'stripe';
const stripe = new Stripe(process.env.STRIPE_SECRET_KEY!, { apiVersion: '2023-10-16' });

export function handleStripeWebhook(rawBody: Buffer, sig: string) {
  const evt = stripe.webhooks.constructEvent(rawBody, sig, process.env.STRIPE_WEBHOOK_SECRET!);
  switch (evt.type) {
    case 'customer.subscription.updated': {
      const sub = evt.data.object as Stripe.Subscription;
      const tenantId = sub.metadata?.tenantId as string;
      // Update plan/limits atomically
      return prisma.tenant.update({ where: { id: tenantId }, data: { /* plan fields */ } });
    }
    default:
      return Promise.resolve();
  }
}

Performance and scaling tactics

• Index discipline: every tenantId must be indexed; composite indexes for common filters, e.g., @@index([tenantId, createdAt]).
• Unique constraints: move all uniqueness to composite constraints (@@unique([tenantId, email])). Avoid global uniqueness unless truly global (e.g., public share IDs).
• Connection pooling: Prisma Accelerate or PgBouncer; for PgBouncer use session pooling to keep SET LOCAL simple. If you must use transaction pooling, ensure you set the GUC inside every transaction and avoid prepared statements.
• Hot-tenant skew: If one customer is 95% of the traffic, assign them a dedicated DB and point your request router to a per-tenant DATABASE_URL (prisma supports multiple clients or dynamic instantiation with a different URL).
• Partitioning: For write-heavy tables, consider time- or tenant-based partitioning. In Postgres native partitioning, make tenantId part of the partition key only if you have a small number of large tenants. Otherwise prefer time-based partitions with tenantId indexed.
• Caching: Namespaced keys, e.g., t:${tenantId}:projects:${id}. Never cache across tenants.
• Analytics: Don’t run cross-tenant COUNT(*) over production tables; ship events to a warehouse (BigQuery, Snowflake) or roll up per-tenant counters in a tenant_stats table.

Example: per-tenant cache pattern

// cache.ts
const key = (tenantId: string, suffix: string) => `t:${tenantId}:${suffix}`;

export async function getProjectCached(tenantId: string, projectId: string) {
  const k = key(tenantId, `project:${projectId}`);
  const cached = await redis.get(k);
  if (cached) return JSON.parse(cached);

  return withTenant(tenantId, (db) =>
    db.project.findUnique({ where: { id: projectId } })
      .then(async (p) => { if (p) await redis.setex(k, 300, JSON.stringify(p)); return p; })
  );
}

Security and compliance checklist

• RLS forced on all tenant tables; block superuser access in application roles
• TLS everywhere; rotate Postgres credentials; read-only roles for analytics
• Limit PII; if needed, encrypt columns at the application layer (libsodium) instead of relying solely on disk encryption
• Audit log table with tenantId, actor, action, payload hash; keep immutable via INSERT-only and a BEFORE UPDATE trigger that raises
• Background jobs set tenant context; test that accidental cross-tenant queries return zero rows
• Backups: daily full + WAL; test restore quarterly into a staging environment

Local development and testing

• Seed script creates 3 tenants with 20 users each; fixtures help you catch missing composite constraints early
• Write a test that proves a user from tenant A cannot fetch tenant B rows even if the app tries; RLS is the safety net
• Use EXPLAIN ANALYZE early to ensure the planner uses your tenantId indexes; add pg_stat_statements in dev to see hotspots

What breaks in production

• Missing composite uniques: You’ll see duplicate emails or slugs across tenants; fix requires data cleanup and downtime risk. Add constraints now.
• Noisy neighbors: A single large import job from one tenant saturates CPU/IO. Mitigate with job concurrency per tenant and rate limits.
• PgBouncer gotchas: Prepared statements + transaction pooling cause “cached plan must be revalidated” or silent misrouting. Disable prepared statements or switch to session pooling.
• RLS performance: RLS itself is cheap; the index strategy is what matters. Always index the predicate columns used in RLS checks (tenantId).
• Long transactions: Holding SET LOCAL only lasts for the transaction. If your handler awaits external I/O, your connection sits open. Keep DB transactions short and push side effects to queues.
• Background jobs without context: A worker that forgets to SET LOCAL reads zero rows or, worse, wrong rows if you used global scoping. Wrap every DB call with the same withTenant helper.

Cost and ROI

• Postgres: A small managed instance runs ~$50–$150/month; expect $300–$800/month by the time you host a few thousand active users with replication and storage growth.
• Prisma: The open-source client is free. If you use Prisma Accelerate, plan for low hundreds/month at moderate scale (cheaper than overprovisioning a database for connections).
• PgBouncer: Free; add ~$20–$50/month if you run it on a tiny VM with monitoring.
• Redis: $15–$100/month for a starter managed instance; worth it for rate limits and cache.
• Stripe: No monthly fee; transaction fees apply. Budget developer time for webhooks and invoice edge cases.
• Engineering time: A robust multi-tenant foundation typically takes 2–4 weeks for an experienced team (provisioning + RLS + billing + test coverage). Rushing this is a false economy.

From a business view: RLS + single DB gets you to product-market fit fastest with strong safety properties. When you sign the first enterprise with specific data-residency or performance needs, graduating them to a dedicated DB is a targeted capital expense, not a rewrite.

Migration path to dedicated databases (when you need it)

• Keep tenantId as the logical key everywhere; this lets you export/import cleanly
• Spin up a per-tenant Postgres; clone schema via migrations; copy data with COPY or logical replication
• Point that tenant’s requests at a different Prisma client instantiated with DATABASE_URL_TENANT_X
• Keep the same RLS in place so your safety net still works; you can even run without RLS in private DBs if desired, but we rarely recommend removing a seatbelt that already fits
• For analytics, aggregate across DBs by shipping events to a shared warehouse rather than querying multiple primaries

Adding AI features without breaking tenancy

Many teams add AI assistants or summarization to their SaaS. Keep prompts and outputs tenant-scoped; store model outputs with tenantId and redacted sources. If you’re choosing an LLM for production use, see our perspective on trade-offs in OpenAI GPT-5 vs Claude vs DeepSeek for production SaaS.

Common trade-offs you’ll actually debate

• Simplicity vs isolation: RLS is simple and secure when done right; separate DBs provide stronger blast-radius control but increase cost and migration complexity.
• Performance vs portability: Partitioning and advanced Postgres features make you fast, but they make cloud migration harder. Be pragmatic; YAGNI still applies.
• SaaS convenience vs enterprise contracts: You may keep 90% of customers on shared infra. Design for the 10% that demand isolation without penalizing everyone else.

FAQ

Do I really need Postgres RLS if I always filter by tenantId in Prisma?

Yes. RLS is the last line of defense when someone forgets a where clause or writes a raw SQL query. It’s cheap insurance and forces secure defaults.

Can Prisma handle per-tenant schemas?

Not elegantly. Prisma targets a single schema per client. You can generate separate clients or swap the search_path, but it’s fragile. Prefer RLS or per-DB isolation with different connection strings.

How do I avoid PgBouncer issues with Prisma?

Use session pooling if you can. If you must use transaction pooling, ensure the GUC is set inside every transaction and disable prepared statements (append ?pgbouncer=true to the connection string). Keep transactions short.

What about analytics across all tenants?

Don’t run big cross-tenant scans on your OLTP database. Stream events to a warehouse (BigQuery/Snowflake) or maintain rollups in a reporting schema. This keeps the primary fast and predictable.

How do I migrate a large tenant to its own database without downtime?

Create the target DB, apply schema migrations, start logical replication or dual-writes for that tenant, cut traffic over when lag is near-zero, and freeze writes for a few seconds during the switchover.

How should I price seats or usage?

Keep billing simple at first: a base plan per tenant plus seats. If your workload is compute-heavy, add metered usage (jobs, tokens, GB processed) with Stripe usage-based pricing.

Key takeaways

• Start with Single DB + Postgres RLS; it’s the best fit with Prisma and fastest to ship safely.
• Enforce tenantId everywhere: schema, indexes, and composite uniques to keep queries fast and data correct.
• Scope every Prisma call within a transaction that sets SET LOCAL app.current_tenant = {id}.
• Use PgBouncer session pooling or Prisma Accelerate and keep transactions short to avoid pooling pitfalls.
• Plan a clear path to dedicated databases for hot or regulated tenants—no rewrite required.
• Keep analytics off the OLTP path; ship events and build per-tenant rollups instead of cross-tenant scans.

If you’re building a multi-tenant SaaS and want senior engineers to design, implement, and stress-test the architecture, MTBYTE can help. Tell us about your product at /contact.