Multi-Tenant SaaS Architecture with Next.js and Postgres

Multi-tenancy is the architectural decision that quietly governs your entire SaaS roadmap: how you isolate customer data, how you onboard accounts, how you bill, and how badly a single bug can hurt you. Get it right and you scale to thousands of tenants on a modest Postgres box; get it wrong and you spend a year untangling shared tables and leaky queries. This guide walks through the isolation models that actually work in production with Next.js and Postgres, and the tradeoffs that should drive your choice.

What "Multi-Tenant" Actually Means

A tenant is a customer organization, not an individual user. A tenant has many users, its own data, its own billing relationship, and an expectation that no other tenant can ever see its rows. The central engineering problem is isolation: guaranteeing that tenant A's request can never read or write tenant B's data, even when a developer forgets a WHERE clause.

There are three canonical strategies, and they sit on a spectrum from "shared everything" to "shared nothing":

• Row-level: all tenants share the same tables; every row carries a tenant_id.
• Schema-per-tenant: one Postgres database, one schema per tenant, identical table structure repeated.
• Database-per-tenant: a separate database (or cluster) per tenant.

Most teams should start at row-level and only move outward when a concrete constraint forces them to. Premature isolation is one of the most expensive mistakes in early-stage SaaS.

Strategy 1: Row-Level Isolation (the Default)

Every tenant-scoped table gets a tenant_id column. Queries filter on it. This is the cheapest model to operate: one connection pool, one migration to run, one backup job, near-infinite tenant density.

The risk is obvious: isolation lives entirely in your application code. One missing predicate and you leak data. The mitigation is to push isolation into the database with Postgres Row-Level Security (RLS), so the database refuses to return foreign rows regardless of what your ORM emits.

Enforcing it with Postgres RLS

RLS attaches a policy to a table that the planner injects into every query. You set the current tenant in a session variable at the start of each request, and the policy does the filtering.

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.tenant_id')::uuid)
  WITH CHECK (tenant_id = current_setting('app.tenant_id')::uuid);

USING filters reads; WITH CHECK blocks writes that would assign the wrong tenant. FORCE ROW LEVEL SECURITY ensures even the table owner is subject to the policy. Critically, the application must connect as a non-superuser role that does not have `BYPASSRLS`, or the policies are silently ignored.

In Next.js, set the tenant on the connection per request. With the postgres driver and a transaction:

// db/withTenant.ts
import { sql } from "@/db/client";

export async function withTenant<T>(
  tenantId: string,
  fn: (tx: typeof sql) => Promise<T>,
): Promise<T> {
  return sql.begin(async (tx) => {
    // set_config is parameterized — never string-concat tenant ids
    await tx`select set_config('app.tenant_id', ${tenantId}, true)`;
    return fn(tx);
  });
}

The true third argument scopes the setting to the transaction, so a pooled connection can't leak app.tenant_id into the next request. This is the single most common RLS bug: setting the GUC at session scope on a pooled connection and having it bleed across tenants. Always use transaction-local config.

A practical caveat for 2026 stacks: if you front Postgres with a connection pooler in transaction mode (PgBouncer, Supabase's pooler, RDS Proxy), session-level settings don't survive across statements. Transaction-local set_config inside an explicit transaction is the pattern that works in both pooled and direct modes.

Strategy 2: Schema-per-Tenant

Here each tenant gets its own Postgres schema (tenant_acme.projects, tenant_globex.projects) with identical tables. You route a request by setting search_path to the tenant's schema.

This buys you stronger isolation than raw row-level — a query simply has no path to another tenant's tables — and lets you do per-tenant operations like restoring a single tenant's data or running schema migrations in waves. It also keeps tenant tables small, which can help query plans.

The cost is migration fan-out. A schema change must run across every schema. At 50 tenants this is a loop; at 10,000 it's an operational project with batching, monitoring, and partial-failure handling. Postgres also keeps catalog metadata per object, so tens of thousands of schemas inflate pg_catalog and slow down connections and pg_dump. As a rough planning heuristic, schema-per-tenant is comfortable into the low thousands of tenants and painful beyond that.

Strategy 3: Database-per-Tenant

A full database (or its own cluster) per tenant. This is shared-nothing: the strongest isolation, the cleanest "delete a customer" story, per-tenant backups and restores, and the ability to place a tenant's data in a specific region for compliance.

It is also the most expensive to operate. You need a tenant catalog that maps tenant to connection string, dynamic connection pooling (you can't keep 5,000 idle pools open), and provisioning automation. It shines in two situations: enterprise B2B where a handful of large customers demand contractual data separation, and regulated/regional workloads (health, finance, data-residency laws) where physical separation is the requirement, not a preference.

Choosing a Strategy: A Decision List

Pick based on constraints you can name today, not hypotheticals:

• Default to row-level + RLS if you have many small/medium tenants, want maximum density, and your team is comfortable enforcing isolation in the database. This covers the vast majority of B2B SaaS.
• Choose schema-per-tenant if you need per-tenant restore/migration control or noisy-neighbor table sizes are hurting plans, and you'll stay in the hundreds-to-low-thousands of tenants.
• Choose database-per-tenant if you have data-residency requirements, a small number of large enterprise tenants, or contractual physical isolation. Accept the provisioning and ops cost as the price of admission.

You can also mix: row-level for self-serve plans, a dedicated database for enterprise customers who pay for it. The tenant catalog makes this transparent to application code if you design the data-access layer around a getTenantConnection(tenantId) abstraction from day one.

Subdomains and Tenant Resolution

Tenant routing usually happens at the subdomain (acme.app.com) or path (app.com/acme) level. Subdomains are cleaner for branding, cookies, and per-tenant SSO. In Next.js, resolve the tenant in middleware so every downstream handler already knows who's asking.

// middleware.ts
import { NextRequest, NextResponse } from "next/server";

export function middleware(req: NextRequest) {
  const host = req.headers.get("host") ?? "";
  const sub = host.split(".")[0];

  if (!sub || sub === "app" || sub === "www") {
    return NextResponse.next(); // marketing / root
  }

  const res = NextResponse.next();
  res.headers.set("x-tenant-slug", sub);
  return res;
}

export const config = { matcher: ["/((?!_next|favicon.ico).*)"] };

Resolve the slug to a tenant record (with a cache) inside your request context, then map it to the right isolation mechanism: set app.tenant_id for RLS, set search_path for schema-per-tenant, or pick a connection for database-per-tenant. For wildcard subdomains in production you need a wildcard DNS record and a wildcard TLS certificate (*.app.com); most platforms support this, but verify before you promise customers vanity domains.

Authentication Across Tenants

Authentication and tenancy are separate axes, and conflating them causes painful rework. Decide early whether a user identity is global (one login works across multiple tenants they belong to) or tenant-scoped (the same email can be a different account in each tenant). Global identity with a membership join table is the more flexible default:

• users — the human, one row per real person.
• tenants — the organizations.
• memberships — (user_id, tenant_id, role), the many-to-many that grants access.

On login, issue a session that records the active tenant and the user's role within it. On every request, verify that the resolved tenant from the subdomain matches a membership for the authenticated user — before you set app.tenant_id. The subdomain is a hint from the client; membership is the authority. Treat RLS as defense in depth, not as your authorization layer: it stops accidental cross-tenant reads, but you still need explicit membership checks so a logged-in user can't simply point their browser at another tenant's subdomain.

Billing Per Tenant

Billing attaches to the tenant, not the user, because the tenant is the paying entity. Store a stripe_customer_id on the tenant and a subscription_status you can read cheaply on every request to gate features. Keep the gate in your own database — don't call Stripe on the hot path.

• One Stripe Customer per tenant, with subscriptions and usage records under it.
• Webhooks are the source of truth. A checkout.session.completed or customer.subscription.updated event is what flips the tenant to active, not the client redirect. Persist the resulting state to your tenant row.
• Usage-based billing (seats, API calls, storage) aggregates per tenant; report usage to Stripe on a schedule rather than synchronously.
• Plan limits belong next to the tenant so middleware can enforce them without a network round trip.

A note from running this in production: make webhook handlers idempotent (Stripe retries) and reconcile with a periodic poll, because a dropped webhook should never silently leave a paying tenant locked out.

Operational Realities

The strategy you choose shapes your day-2 operations more than your code:

• Migrations are trivial at row-level, a fan-out job at schema-per-tenant, and a fleet operation at database-per-tenant.
• Backups and restores get easier to scope per tenant as you move toward shared-nothing — restoring one tenant from a shared table is genuinely hard.
• Noisy neighbors: one tenant's heavy queries affect everyone on shared infrastructure. Connection limits, statement timeouts, and per-tenant rate limiting matter more in row-level setups.
• The "delete this customer" request (GDPR, contract end) is a DELETE ... WHERE tenant_id at row-level versus dropping a database at the other end — and auditors prefer the latter.

Frequently Asked Questions

Is Postgres RLS enough to guarantee tenant isolation?

RLS is excellent defense in depth but not a complete solution on its own. It only works if your app connects as a non-superuser role without BYPASSRLS, and if you set the tenant context per transaction. You still need application-level authorization to confirm the user belongs to the tenant. Treat RLS as a safety net that catches missing WHERE clauses, not as your only line of defense.

When should I move from row-level to database-per-tenant?

Move when a concrete constraint demands it: data-residency law, a contractual requirement for physical separation, or a large enterprise tenant whose load threatens shared infrastructure. Don't migrate speculatively. Most SaaS products run comfortably on row-level isolation well past their first thousand tenants. Design a tenant-connection abstraction early so a later move is a routing change, not a rewrite.

How do I handle Postgres connection pooling with per-tenant context?

Use transaction-local settings with set_config('app.tenant_id', $1, true) inside an explicit transaction. The true flag scopes the value to that transaction, so a pooled connection cannot leak tenant context into the next request. This pattern works with transaction-mode poolers like PgBouncer, where session-level GUCs do not persist across statements.

Should authentication be global or tenant-scoped?

Prefer global identity with a memberships table joining users to tenants. It supports users who belong to multiple organizations, single sign-on, and clean role management per tenant. Tenant-scoped identity (same email as separate accounts per tenant) is harder to evolve. Whichever you choose, always verify membership on the server before trusting a subdomain to grant access.

How does billing work when one user belongs to several tenants?

Billing attaches to the tenant, not the user, so each tenant has its own Stripe Customer and subscription regardless of which users belong to it. A user who belongs to three tenants simply has access governed by three independent billing relationships. Store subscription status on the tenant row and treat Stripe webhooks as the source of truth for activation.

Working with CodeAustral

We build multi-tenant platforms for clients worldwide and have shipped every isolation model described here in production. If you're designing a new SaaS or untangling a shared-database architecture that has outgrown its assumptions, send us a short brief at codeaustral.com/contact and we'll tell you, candidly, which approach fits your constraints.