Have you ever worried about accidentally exposing one customer’s data to another? Do you lose sleep over a single forgotten WHERE tenantId = ? clause in your codebase?

If you’re building a SaaS application, multi-tenancy is a key architectural decision. Serving multiple customers (tenants) from a single application and database requires careful planning to ensure data isolation and security.

In this series, we’ve built full-text search, added semantic search, and made it all update in real-time. Now, we add the last layer: bulletproof data isolation.

The traditional solution is to manually add a tenantId filter to every single database query. But this approach is fragile. It relies on every developer, on every commit, forever, to remember to add that filter. One mistake, and you’ve leaked data.

This is where PostgreSQL’s Row-Level Security (RLS) changes the game. RLS moves data isolation from your application code (where it can be forgotten) into the database itself (where it’s enforced automatically and impossible to bypass).

What We’ll Cover:

• The simple, scalable multi-tenant schema most SaaS apps need.
• How to implement RLS policies that make data leaks impossible.
• A production-ready pattern for integrating RLS with Prisma and Next.js.
• How to build secure search queries that automatically filter by tenant, no extra code required.

By the end, data isolation won’t be something you hope you remembered—it will be the unbreakable default.

1. The Multi-Tenant Architecture: Shared Schema with RLS

There are several ways to design a multi-tenant system (like a separate database per tenant), but the most common, cost-effective, and scalable model for most SaaS applications is the shared database, shared schema approach.

Think of it like an apartment building: all tenants live on the same floor (shared tables), but each apartment has its own unique lock (the tenantId column).

This model is simple and efficient, but it carries one massive risk: if you forget to filter by tenantId, you expose one customer’s data to another.

Row-Level Security is the automated security guard for your database. It checks every single row access and ensures the current user is only allowed to see rows belonging to their own tenant. Even if your application code is buggy and asks for SELECT * FROM "Article", RLS invisibly adds WHERE "tenantId" = current_user_tenant_id.

2. Designing a Tenant-Aware Prisma Schema

Let’s update our schema.prisma to support multi-tenancy. Every piece of data that belongs to a customer must be linked back to a Tenant.

model Tenant {
  id    Int    @id @default(autoincrement())
  name  String
  slug  String @unique

  // Relations to tenant-owned data
  users    User[]
  articles Article[]
}

model User {
  id       Int    @id @default(autoincrement())
  email    String @unique
  tenantId Int

  // Relation back to the Tenant
  tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  @@index([tenantId])
}

model Article {
  id       Int    @id @default(autoincrement())
  title    String
  content  String
  tenantId Int

  // Relation back to the Tenant
  tenant Tenant @relation(fields: [tenantId], references: [id], onDelete: Cascade)

  // Index on tenantId for performance - required for fast filtering
  @@index([tenantId])
}

Key Changes:

1. Tenant Model: This represents your customer’s organization.
2. tenantId Foreign Key: Every tenant-specific model (User, Article, etc.) gets a tenantId field and a relation back to the Tenant.
3. onDelete: Cascade: If you delete a tenant, PostgreSQL automatically cleans up all their associated data—essential for data management and privacy compliance.
4. @@index([tenantId]): Don’t skip this. Without an index on tenantId, queries that filter by tenant will require a full table scan, becoming catastrophically slow as your data grows.

Run the migration to apply these schema changes:

npx prisma migrate dev --name add_multi_tenancy

3. Implementing Row-Level Security in PostgreSQL

Now for the magic. We’ll create an RLS policy that tells PostgreSQL how to enforce tenant isolation automatically.

The core idea is to set a session variable for each transaction, like app.tenant_id, and write a policy that reads this variable.

Step 1: Add the RLS Policy via Prisma Migration

Create a new, empty migration file. This is where your RLS SQL will live, ensuring it’s version-controlled and deployed consistently.

npx prisma migrate dev --name add_article_rls_policy --create-only

Now, open the newly created migration.sql file and add the following:

-- migrations/xxxxxxxx_add_article_rls_policy/migration.sql

-- 1. Enable Row-Level Security on the table
ALTER TABLE "Article" ENABLE ROW LEVEL SECURITY;

-- 2. Create a policy that enforces tenant isolation
CREATE POLICY tenant_isolation_policy ON "Article"
FOR ALL  -- The policy applies to SELECT, INSERT, UPDATE, and DELETE
USING (
  -- This is the rule for reading/updating/deleting rows.
  -- It checks if the row's tenantId matches the current session's tenant_id.
  "tenantId" = current_setting('app.tenant_id')::int
)
WITH CHECK (
  -- This is the rule for creating new rows.
  -- It ensures new rows are created with the correct tenantId.
  "tenantId" = current_setting('app.tenant_id')::int
);

What this does:

• ENABLE ROW LEVEL SECURITY: Activates RLS on the Article table. Important: Once enabled, no data will be visible until a policy is created. This is a secure default.
• FOR ALL: Applies the policy to all types of queries.
• USING (...): This clause is added to SELECT, UPDATE, and DELETE queries. It makes rows visible only if "tenantId" matches the session variable.
• WITH CHECK (...): This clause is enforced for INSERT and UPDATE queries. It prevents a user from creating or moving data into another tenant’s scope.
• current_setting('app.tenant_id'): This special PostgreSQL function reads the session variable we will set from our application.

Now, apply the migration:

npx prisma migrate dev

Your database is now locked down. Let’s integrate it with our app.

4. A Production-Ready Pattern for Prisma and RLS

The goal is to set app.tenant_id for every single query initiated by a logged-in user. The naive approach of creating a new Prisma client per request is a performance anti-pattern. Here is a robust, performant solution using Prisma Middleware.

Step 1: Create a Centralized, Tenant-Aware Prisma Client

Create a file that will manage your Prisma client instance and provide a factory function for creating tenant-specific contexts.

import { PrismaClient } from '@prisma/client';

// 1. Initialize a single global Prisma Client instance
const prismaClientSingleton = () => {
  return new PrismaClient();
};

declare global {
  var prismaGlobal: undefined | ReturnType<typeof prismaClientSingleton>;
}

const prisma = globalThis.prismaGlobal ?? prismaClientSingleton();

export default prisma;

if (process.env.NODE_ENV !== 'production') globalThis.prismaGlobal = prisma;


// 2. A factory function to create a tenant-specific Prisma client
export function createTenantPrisma(tenantId: number) {
  if (!tenantId) {
    throw new Error('Tenant ID must be provided to create a tenant-specific Prisma client.');
  }

  // Use Prisma middleware to set the tenant_id on every query
  return prisma.$extends({
    query: {
      $allModels: {
        async $allOperations({ args, query }) {
          const [, result] = await prisma.$transaction([
            prisma.$executeRawUnsafe(`SET LOCAL app.tenant_id = '${tenantId}'`),
            query(args),
          ]);
          return result;
        },
      },
    },
  });
}

Let’s break this down:

1. Singleton: The first part ensures that only one PrismaClient instance is created in your application for efficient connection pool management.
2. createTenantPrisma(tenantId): This is the key function. It doesn’t create a new client. Instead, it uses $extends to return a proxy of the global client.
3. $transaction with SET LOCAL: For every query made through this extended client, we:
   • Start a transaction.
   • Set the app.tenant_id using SET LOCAL. The LOCAL keyword scopes the variable only to the current transaction, preventing leaks between concurrent requests.
   • Execute the actual query the user requested.
   • Return the result.

This pattern is safe, performant, and ensures every single query is correctly scoped to the tenant.

Step 2: Use the Tenant-Aware Client in Your API Route

Now, let’s update our search API route. The process is simple: get the user’s session, extract their tenantId, and use our factory function.

import { createTenantPrisma } from '../../lib/prisma';
import { getServerSession } from 'next-auth/next'; // Use your auth provider
import { authOptions } from './auth/[...nextauth]';

export default async function handler(req, res) {
  // 1. Get the user session securely on the server
  const session = await getServerSession(req, res, authOptions);

  if (!session || !session.user?.tenantId) {
    return res.status(401).json({ error: 'Unauthorized' });
  }

  const { q: searchQuery } = req.query;
  if (!searchQuery) {
    return res.status(400).json({ error: 'Search query is required' });
  }

  // 2. Create a Prisma client scoped to the user's tenant
  const prisma = createTenantPrisma(session.user.tenantId);

  try {
    // 3. Run the exact same search query as before. NO CHANGES NEEDED!
    const results = await prisma.article.findMany({
      where: {
        // Your search logic here. No need to add `tenantId`.
        // For example, with full-text search:
        _search: searchQuery, // Assuming you use a Prisma extension for FTS
      },
      // ... other query options
    });

    // RLS transparently adds `AND "tenantId" = session.user.tenantId` to the query.

    res.status(200).json(results);
  } catch (error) {
    console.error('Search error:', error);
    res.status(500).json({ error: 'An error occurred during the search.' });
  }
}

The beauty of this is profound. Your application logic for searching is completely unaware of multi-tenancy. You write simple queries, and the combination of our Prisma factory and PostgreSQL RLS handles the security automatically.

5. Production Readiness: Testing and Bypassing RLS

Testing Your Policies

You must write tests to confirm your data isolation is working. Use your testing framework (like Jest) to query as different tenants and assert that they cannot see each other’s data.

import { createTenantPrisma } from '../lib/prisma';

describe('Multi-Tenant RLS Policy', () => {
  it('should only return articles for the specified tenant', async () => {
    const tenant1Prisma = createTenantPrisma(1); // Assuming tenant 1 exists
    const tenant2Prisma = createTenantPrisma(2); // Assuming tenant 2 exists

    const articlesForTenant1 = await tenant1Prisma.article.findMany();
    const articlesForTenant2 = await tenant2Prisma.article.findMany();

    // Assert that all returned articles belong to the correct tenant
    articlesForTenant1.forEach(article => expect(article.tenantId).toBe(1));
    articlesForTenant2.forEach(article => expect(article.tenantId).toBe(2));
  });

  it('should prevent a user from creating an article for another tenant', async () => {
    const tenant1Prisma = createTenantPrisma(1);

    // This should fail because the WITH CHECK clause in our RLS policy will reject it
    await expect(
      tenant1Prisma.article.create({
        data: {
          title: 'Malicious Article',
          content: '...',
          tenantId: 2, // Attempting to create data for another tenant
        },
      })
    ).rejects.toThrow();
  });
});

Bypassing RLS for Admin Operations

Sometimes, an internal admin needs to see data across all tenants. You can achieve this by creating a special database user with the BYPASSRLS attribute and using a separate connection string for admin tasks. This is safer than disabling RLS in a transaction.

CREATE ROLE admin_user WITH LOGIN PASSWORD '...' BYPASSRLS;

Your admin backend would then use a Prisma client initialized with a database URL for this admin_user.

Conclusion

You have now built a robust, secure, and scalable multi-tenant architecture. Security is no longer an afterthought—it’s the unbreakable foundation of your application.

By leveraging PostgreSQL’s Row-Level Security and a clean Prisma integration pattern, you have ensured that even with buggy application code, your customers’ data remains isolated and safe. This is the difference between an amateur SaaS and a professional, enterprise-ready product.

This architecture gives you the peace of mind to focus on building features, knowing that your database has your back.