Multi-Tenant SaaS with Next.js, Prisma & PostgreSQL: A Practical Architecture Guide

Multi-tenancy is the part of building a SaaS that quietly decides whether your product scales cleanly or turns into a security incident waiting to happen. Get it right early and you barely think about it again. Get it wrong and you're rewriting your data layer at the worst possible time.

In this guide I'll walk through how I approach multi-tenancy for a SaaS built on Next.js, Prisma, and PostgreSQL - the isolation strategy to pick, how to scope every query to the right tenant in the App Router, and a connection-pooling gotcha that catches a lot of teams in production.

First, what "multi-tenant" actually means

A multi-tenant app serves many customers (tenants) from one application and database, while keeping each tenant's data completely isolated from the others. The whole game is isolation: tenant A must never, under any circumstances, see tenant B's data. Everything below is in service of that one rule.

The three isolation strategies

1. Shared schema with a tenant_id column (recommended default)

One database, one schema, and every business table carries a tenant_id (or orgId) column. Every query filters by it. This is the simplest to build and operate, and it's the right choice for most early-stage products with anywhere from 100 to 10,000 tenants.

2. Schema-per-tenant

One database, but a separate PostgreSQL schema per tenant. It allows per-tenant customization and easier per-tenant migrations, but connection pooling gets complicated (the search_path must switch per request) and PostgreSQL catalogs bloat as tenant count grows. Good for mid-market products that need schema-level customization.

3. Database-per-tenant

A separate database per tenant. Maximum isolation, usually reserved for enterprise or strict-compliance customers. Operationally the heaviest.

My advice: start with shared schema and a tenant_id. You can graduate specific large or compliance-sensitive tenants to their own database later without rebuilding everything.

Step 1: Model the tenant in Prisma

Add an organization (tenant) and tie every business model to it. Crucially, add a composite index on (tenantId, createdAt) for every high-volume table from day one - your most common query is "give me this tenant's recent rows," and this index makes it fast.

model Organization {

  id        String    @id @default(cuid())

  slug      String    @unique

  projects  Project[]

}

model Project {

  id        String   @id @default(cuid())

  name      String

  orgId     String

  createdAt DateTime @default(now())

  org       Organization @relation(fields: [orgId], references: [id])

  @@index([orgId, createdAt])

}

Step 2: Identify the tenant in middleware

Before any query runs, you need to know which tenant the request belongs to. In the Next.js App Router, the right place is middleware - typically by reading the subdomain (acme.yourapp.com → tenant "acme").

import { NextResponse } from "next/server";

import type { NextRequest } from "next/server";

export function middleware(req: NextRequest) {

  const host = req.headers.get("host") || "";

  const subdomain = host.split(".")[0];

  const res = NextResponse.next();

  res.headers.set("x-tenant-slug", subdomain);

  return res;

}

From there you resolve the slug to an organization id (once, then cache it) and make it available to your data layer for the rest of the request.

Step 3: Scope every query to the tenant

This is the rule you never break: no business query runs without a tenant filter. With the shared-schema approach, that means every Prisma call includes the orgId:

const projects = await prisma.project.findMany({

  where: { orgId: currentOrgId },

  orderBy: { createdAt: "desc" },

});

The risk with manual filtering is human error - one forgotten where clause leaks data. Two ways to harden it: wrap your data access in tenant-aware helper functions so the filter is never optional, or push isolation down into the database with Row-Level Security.

Step 4 (recommended): Add PostgreSQL Row-Level Security

RLS makes the database itself enforce isolation, so a forgotten filter can't leak data. You set the current tenant on each request and define a policy that only returns matching rows:

-- once, on the table

ALTER TABLE "Project" ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON "Project"

  USING (org_id = current_setting('app.current_tenant_id'));

-- per request, before querying

await prisma.$executeRaSET app.current_tenant_id = ${currentOrgId};

Now even if application code forgets a filter, PostgreSQL won't hand over another tenant's rows. Defense in depth.

The production gotcha: connection pooling + RLS

Here's the one that bites people. If you use a pooler like PgBouncer in transaction mode, it resets session variables between transactions - which means your SET app.current_tenant_id can vanish before the query runs, breaking isolation or queries entirely. The fixes: use session mode, or set the tenant context explicitly inside the same transaction as your query (wrap both in prisma.$transaction). Test this under real pooled connections, not just on your laptop.

A sensible default stack

For most new SaaS products this combination works well: Next.js (App Router) for the app, Prisma + PostgreSQL with shared-schema multi-tenancy and RLS, a subdomain-based tenant resolver in middleware, Auth.js for authentication, and Stripe for subscription billing.

Frequently asked questions

Which multi-tenancy approach should I start with?

Shared schema with a tenant_id column. It's the simplest to build and scales comfortably for most early-stage products. You can isolate specific large tenants into their own database later.

Do I need Row-Level Security, or is filtering by tenant_id enough?

Filtering works, but it relies on never forgetting a where clause. RLS adds a database-level safety net so a single mistake can't leak data. For anything handling sensitive data, use both.

How do I identify the tenant in Next.js?

Resolve it in middleware, usually from the subdomain, then pass the tenant context through to your data layer for the rest of the request.

Why does my tenant context disappear in production?

Almost always connection pooling. PgBouncer in transaction mode resets session variables between transactions. Use session mode or set the tenant context inside the same transaction as your query.

Building a multi-tenant SaaS and want the data layer done right the first time? This is exactly the kind of architecture I build for founders. Tell me about your product (https://osamahabib.com/contact) and I'll help you design it to scale safely.