0Pricing
Next.js 15 Fullstack (App Router + Server Actions) · Lesson

Row-Level Tenant Data Isolation Patterns

Enforce tenant boundaries in queries with row-level security and scoped data access layers.

Row-Level Tenant Data Isolation Patterns is a free Next.js 15 Fullstack (App Router + Server Actions) lesson on CoddyKit — lesson 2 of 4. You can read the complete lesson below for free — then practise it hands-on in the browser with a built-in code editor and a 24/7 AI tutor. It is part of the Next.js 15 Fullstack (App Router + Server Actions) learning path, one of 4 lessons in the course, and your progress syncs across the web and the CoddyKit app.

Why Tenant Isolation Is a First-Class Concern

In a multi-tenant SaaS application, every user belongs to a tenant (an organisation, workspace, or account). Without explicit isolation, a single bug in a query can expose one tenant's data to another — a catastrophic security breach.

  • Logical isolation stores all tenants in shared tables, distinguished by a tenant_id column.
  • Physical isolation gives each tenant their own schema or database — simpler security, but far more expensive to operate.
  • Most SaaS products choose logical isolation and enforce boundaries in the application layer, the database layer, or both.

This lesson focuses on row-level isolation patterns inside a Next.js 15 App Router application backed by PostgreSQL, showing how to make tenant leakage structurally impossible rather than relying on developer discipline alone.

The Naive Approach and Its Risks

The most common mistake is sprinkling WHERE tenant_id = ? manually throughout the codebase. This pattern is fragile: any query that forgets the clause leaks data.

Consider this vulnerable Server Action:

'use server';
import { db } from '@/lib/db';

// DANGEROUS: tenant_id is never checked
export async function getInvoices() {
  // This returns ALL invoices from ALL tenants!
  const invoices = await db.query(
    'SELECT * FROM invoices ORDER BY created_at DESC'
  );
  return invoices.rows;
}

Resolving the Current Tenant in Next.js 15

Before we can scope any query, we need a reliable way to identify the current tenant. In Next.js 15, the idiomatic place is a server-side utility that reads the session from a cookie or JWT and resolves the tenant.

Key rules:

  • Never trust a tenant ID supplied by the client in a request body or query string — always derive it from the verified session.
  • Throw an error early if no valid session exists, so downstream code can never accidentally execute without a tenant context.
// lib/tenant.ts
import { cookies } from 'next/headers';
import { verifyJwt } from '@/lib/auth';

export interface TenantContext {
  tenantId: string;
  userId: string;
}

export async function requireTenantContext(): Promise<TenantContext> {
  const cookieStore = await cookies();
  const token = cookieStore.get('session')?.value;

  if (!token) {
    throw new Error('Unauthenticated');
  }

  const payload = await verifyJwt(token);

  if (!payload?.tenantId || !payload?.userId) {
    throw new Error('Invalid session: missing tenant context');
  }

  return {
    tenantId: payload.tenantId as string,
    userId: payload.userId as string,
  };
}

Building a Scoped Data Access Layer (DAL)

A scoped data access layer centralises tenant filtering so that no individual query can accidentally omit it. The idea is simple: create repository functions that require a TenantContext parameter, and always apply it.

This makes the tenant boundary visible in every function signature — if you call a data function without a context, TypeScript refuses to compile.

// lib/dal/invoices.ts
import { db } from '@/lib/db';
import type { TenantContext } from '@/lib/tenant';

export interface Invoice {
  id: string;
  tenantId: string;
  amount: number;
  status: string;
  createdAt: Date;
}

export async function listInvoices(
  ctx: TenantContext,
  page = 1,
  pageSize = 20
): Promise<Invoice[]> {
  const offset = (page - 1) * pageSize;

  const result = await db.query<Invoice>(
    `SELECT id, tenant_id AS "tenantId", amount, status, created_at AS "createdAt"
     FROM invoices
     WHERE tenant_id = $1
     ORDER BY created_at DESC
     LIMIT $2 OFFSET $3`,
    [ctx.tenantId, pageSize, offset]
  );

  return result.rows;
}

export async function getInvoiceById(
  ctx: TenantContext,
  invoiceId: string
): Promise<Invoice | null> {
  const result = await db.query<Invoice>(
    `SELECT id, tenant_id AS "tenantId", amount, status, created_at AS "createdAt"
     FROM invoices
     WHERE tenant_id = $1 AND id = $2`,
    [ctx.tenantId, invoiceId]
  );

  return result.rows[0] ?? null;
}

Using the DAL in Server Actions

With the DAL in place, Server Actions become thin orchestrators: resolve the tenant context, call the DAL, and return data. The tenant filter is enforced structurally, not by convention.

Notice that requireTenantContext() is called at the top of every action — if it throws, the action aborts before any data is touched.

'use server';
import { requireTenantContext } from '@/lib/tenant';
import { listInvoices, getInvoiceById } from '@/lib/dal/invoices';

export async function fetchInvoicesAction(page: number = 1) {
  const ctx = await requireTenantContext();
  return listInvoices(ctx, page);
}

export async function fetchInvoiceAction(invoiceId: string) {
  const ctx = await requireTenantContext();

  const invoice = await getInvoiceById(ctx, invoiceId);

  if (!invoice) {
    // Could be not found OR a cross-tenant access attempt —
    // return the same error to avoid leaking existence information.
    throw new Error('Invoice not found');
  }

  return invoice;
}

PostgreSQL Row-Level Security (RLS)

The application-layer DAL is a strong first line of defence, but a second line exists at the database level: PostgreSQL Row-Level Security (RLS). With RLS enabled, the database itself rejects any query that touches rows belonging to a different tenant — even if the application forgets to filter.

The pattern works by:

  • Enabling RLS on every multi-tenant table.
  • Creating a policy that compares tenant_id against a session variable set by the application before each query.
  • Setting the variable via SET LOCAL app.current_tenant_id = '...' inside a transaction.
-- Run once per table during migrations
ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;

-- Deny all access by default (belt-and-suspenders)
ALTER TABLE invoices FORCE ROW LEVEL SECURITY;

-- Allow SELECT/INSERT/UPDATE/DELETE only for the current tenant
CREATE POLICY tenant_isolation ON invoices
  USING (tenant_id = current_setting('app.current_tenant_id', true))
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id', true));

-- The app sets this variable in every transaction:
-- SET LOCAL app.current_tenant_id = '<uuid>';

Wiring RLS into the Database Client

To make RLS work automatically, wrap every database operation in a transaction that first sets the app.current_tenant_id session variable. This can be done with a single helper that wraps pg's PoolClient.

This ensures the tenant variable is always set before any query runs, and is automatically cleared when the transaction ends.

// lib/db.ts
import { Pool, PoolClient } from 'pg';

export const pool = new Pool({
  connectionString: process.env.DATABASE_URL,
});

export async function withTenantTransaction<T>(
  tenantId: string,
  fn: (client: PoolClient) => Promise<T>
): Promise<T> {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    // Scope this setting to the current transaction only
    await client.query(
      `SET LOCAL app.current_tenant_id = $1`,
      [tenantId]
    );
    const result = await fn(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

Updating the DAL to Use RLS Transactions

Now update the DAL functions to use withTenantTransaction. The application-level WHERE tenant_id = $1 filter is kept for clarity and performance (index utilisation), while RLS provides a safety net at the database level.

This defence-in-depth strategy means tenant leakage requires two independent failures simultaneously — significantly reducing risk.

// lib/dal/invoices.ts (updated)
import { withTenantTransaction } from '@/lib/db';
import type { TenantContext } from '@/lib/tenant';
import type { Invoice } from './types';

export async function listInvoices(
  ctx: TenantContext,
  page = 1,
  pageSize = 20
): Promise<Invoice[]> {
  const offset = (page - 1) * pageSize;

  return withTenantTransaction(ctx.tenantId, async (client) => {
    const result = await client.query<Invoice>(
      `SELECT id, tenant_id AS "tenantId", amount, status, created_at AS "createdAt"
       FROM invoices
       WHERE tenant_id = $1
       ORDER BY created_at DESC
       LIMIT $2 OFFSET $3`,
      [ctx.tenantId, pageSize, offset]
    );
    return result.rows;
  });
}

Schema-Per-Tenant Isolation with Prisma

For applications requiring stronger guarantees, a schema-per-tenant approach assigns each tenant their own PostgreSQL schema (e.g., tenant_abc.invoices). This eliminates cross-tenant leakage at the structural level — policies and filters are unnecessary because the data is physically separate.

With Prisma, you can achieve this by dynamically constructing a client that uses a different schema for each request:

// lib/tenant-db.ts
import { PrismaClient } from '@prisma/client';

const clientCache = new Map<string, PrismaClient>();

export function getTenantPrismaClient(tenantId: string): PrismaClient {
  const safeSchema = tenantId.replace(/[^a-z0-9_]/gi, '_');

  if (clientCache.has(safeSchema)) {
    return clientCache.get(safeSchema)!;
  }

  const client = new PrismaClient({
    datasources: {
      db: {
        url: `${process.env.DATABASE_URL}?schema=${safeSchema}`,
      },
    },
  });

  clientCache.set(safeSchema, client);
  return client;
}

// Usage in a Server Action:
// const db = getTenantPrismaClient(ctx.tenantId);
// const invoices = await db.invoice.findMany();

Preventing Insecure Direct Object Reference (IDOR)

Tenant isolation must extend beyond list queries. A common vulnerability called Insecure Direct Object Reference (IDOR) occurs when an attacker changes a resource ID in the URL or request body to access another tenant's record.

The getInvoiceById DAL function already handles this correctly by including AND tenant_id = $1. If the invoice belongs to a different tenant, the query returns zero rows — the same response as a genuinely missing record. This prevents existence leakage.

Always verify ownership via the tenant context, never by trusting that the client-supplied ID is one the current user is allowed to see.

'use server';
import { requireTenantContext } from '@/lib/tenant';
import { withTenantTransaction } from '@/lib/db';

export async function deleteInvoiceAction(invoiceId: string) {
  const ctx = await requireTenantContext();

  await withTenantTransaction(ctx.tenantId, async (client) => {
    const result = await client.query(
      `DELETE FROM invoices
       WHERE id = $1 AND tenant_id = $2`,
      [invoiceId, ctx.tenantId]
    );

    // If rowCount is 0 the invoice either doesn't exist
    // or belongs to another tenant — treat both identically.
    if ((result.rowCount ?? 0) === 0) {
      throw new Error('Invoice not found');
    }
  });
}

Testing Tenant Isolation

Tenant isolation logic must be covered by automated tests. The key scenarios to test are:

  • Happy path: Tenant A can read their own data.
  • Cross-tenant read: Tenant A cannot read Tenant B's records (returns null or empty array).
  • Cross-tenant delete/write: Tenant A's mutation on Tenant B's ID silently does nothing (rowCount = 0).

Use a test database and real SQL to catch regressions that mocks would miss. The following shows a Vitest integration test pattern:

// tests/dal/invoices.test.ts
import { describe, it, expect, beforeAll } from 'vitest';
import { listInvoices, getInvoiceById } from '@/lib/dal/invoices';
import { seedTestData, cleanTestData } from '../helpers/db';

const tenantA = { tenantId: 'tenant-aaa', userId: 'user-1' };
const tenantB = { tenantId: 'tenant-bbb', userId: 'user-2' };

beforeAll(async () => {
  await cleanTestData();
  await seedTestData([
    { id: 'inv-1', tenantId: 'tenant-aaa', amount: 100, status: 'paid' },
    { id: 'inv-2', tenantId: 'tenant-bbb', amount: 200, status: 'pending' },
  ]);
});

describe('listInvoices', () => {
  it('returns only the requesting tenant\'s invoices', async () => {
    const invoices = await listInvoices(tenantA);
    expect(invoices).toHaveLength(1);
    expect(invoices[0].id).toBe('inv-1');
  });
});

describe('getInvoiceById', () => {
  it('returns null when the invoice belongs to a different tenant', async () => {
    // Tenant A tries to fetch Tenant B's invoice
    const invoice = await getInvoiceById(tenantA, 'inv-2');
    expect(invoice).toBeNull();
  });
});

Knowledge Check: Tenant Isolation Strategy

Test your understanding of the key design decisions covered in this lesson.

Recap: Row-Level Tenant Data Isolation Patterns

This lesson established a layered approach to enforcing tenant boundaries in a Next.js 15 App Router application:

  • Session-derived tenant context — always resolve tenantId from a server-verified JWT or session cookie, never from client-supplied input.
  • Scoped Data Access Layer (DAL) — require a TenantContext parameter in every repository function and apply WHERE tenant_id = $1 at the SQL level, making omission a compile-time or code-review error.
  • PostgreSQL Row-Level Security (RLS) — enable RLS as a database-enforced safety net using SET LOCAL app.current_tenant_id inside transactions, so even a buggy query cannot leak data.
  • IDOR prevention — include the tenant check in all single-record lookups and mutations; return identical errors for not-found and cross-tenant access to avoid existence leakage.
  • Schema-per-tenant — an alternative pattern using separate PostgreSQL schemas for stronger structural isolation, at the cost of operational complexity.
  • Integration tests — validate cross-tenant isolation with real queries against a test database, covering both read and write scenarios.

Combining application-layer filtering, database-layer RLS, and typed function signatures creates a multi-layered defence that makes tenant leakage structurally difficult even as the codebase grows.

Frequently asked questions

Is the “Row-Level Tenant Data Isolation Patterns” lesson free?

Yes — the full text of “Row-Level Tenant Data Isolation Patterns” is free to read here on the web, and the Next.js 15 Fullstack (App Router + Server Actions) course includes 4 lessons in total. To practise it interactively (a built-in code editor and a 24/7 AI tutor) and unlock the rest of the Next.js 15 Fullstack (App Router + Server Actions) course, upgrade to CoddyKit PRO.

What will I learn in “Row-Level Tenant Data Isolation Patterns”?

Enforce tenant boundaries in queries with row-level security and scoped data access layers. You practise Next.js 15 Fullstack (App Router + Server Actions) with hands-on code you run directly in the browser, and a 24/7 AI tutor answers your questions as you work through the lesson.

Do I need any experience to start Next.js 15 Fullstack (App Router + Server Actions)?

No prior experience is required. Next.js 15 Fullstack (App Router + Server Actions) on CoddyKit is structured for beginners through advanced learners; this is — lesson 2 of 4, so you can start here or from the beginning and move at your own pace.

How long does the “Row-Level Tenant Data Isolation Patterns” lesson take?

Most CoddyKit lessons take about 5–10 minutes. Each one is bite-sized and interactive, so you make steady progress and pick up exactly where you left off across the web and the app.

Can I write and run code in this Next.js 15 Fullstack (App Router + Server Actions) lesson?

Yes. Every Next.js 15 Fullstack (App Router + Server Actions) lesson includes a built-in code editor, so you write and run real code right in your browser and get instant AI feedback — no local setup required.

All lessons in this course

  1. Subdomain and Path-Based Tenant Resolution
  2. Row-Level Tenant Data Isolation Patterns
  3. Per-Tenant Theming and Feature Flags
  4. Usage Metering and Subscription Enforcement
← Back to Next.js 15 Fullstack (App Router + Server Actions)