Architecting Multi-Tenant SaaS: Scalable Data Isolation with Next.js & PostgreSQL

Introduction & The Problem

The Solution Concept & Architecture

1. Separate Databases: Each tenant has its own dedicated database. Offers the highest isolation but can be resource-intensive and complex to manage at scale.
2. Separate Schemas: All tenants share a single database instance, but each tenant's data resides in its own dedicated schema within that database. A good balance of isolation and resource efficiency.
3. Shared Schema, Discriminator Column: All tenants share a single database and schema, with a tenant_id column on every relevant table to logically separate data. This is often the most cost-effective and scalable approach for many SaaS applications, balancing resource sharing with programmatic isolation.

Client Request
     |
     V
Next.js Frontend
     |
     V
Next.js API Route
     |
     V
Tenant Identification Middleware (Extract tenant_id)
     |
     V
Data Access Layer (Prisma with tenant_id scoping)
     |
     V
PostgreSQL Database (Tables with tenant_id column)

Step-by-Step Implementation

// schema.prisma

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

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

model Tenant {
  id          String   @id @default(cuid())
  name        String   @unique
  slug        String   @unique
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt
  products    Product[]
  users       User[]
}

model User {
  id        String   @id @default(cuid())
  email     String   @unique
  password  String
  tenantId  String
  tenant    Tenant   @relation(fields: [tenantId], references: [id])
  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

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

model Product {
  id          String   @id @default(cuid())
  name        String
  description String?
  price       Float
  tenantId    String
  tenant      Tenant   @relation(fields: [tenantId], references: [id])
  createdAt   DateTime @default(now())
  updatedAt   DateTime @updatedAt

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

// lib/middleware/withTenant.ts

import { NextApiRequest, NextApiResponse } from 'next';
import { NextHandler } from 'next-connect';

export interface TenantNextApiRequest extends NextApiRequest {
  tenantId?: string;
}

export const withTenant = (req: TenantNextApiRequest, res: NextApiResponse, next: NextHandler) => {
  const tenantId = req.headers['x-tenant-id'] as string;

  if (!tenantId) {
    return res.status(400).json({ error: 'X-Tenant-ID header is required' });
  }

  req.tenantId = tenantId;
  next();
};

// lib/db.ts

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

let prisma: PrismaClient;

declare global {
  namespace NodeJS {
    interface Global {
      prisma: PrismaClient;
    }
  }
}

if (process.env.NODE_ENV === 'production') {
  prisma = new PrismaClient();
} else {
  if (!global.prisma) {
    global.prisma = new PrismaClient();
  }
  prisma = global.prisma;
}

// This function scopes all queries to a specific tenant.
// It ensures that any find, create, update, or delete operation
// automatically includes the tenantId filter.
export const getTenantPrismaClient = (tenantId: string) => {
  return prisma.$extends({
    query: {
      $allModels: {
        async findUnique(args) {
          args.where = { ...args.where, tenantId };
          return prisma.findUnique(args);
        },
        async findMany(args) {
          args.where = { ...args.where, tenantId };
          return prisma.findMany(args);
        },
        async create(args) {
          args.data = { ...args.data, tenantId };
          return prisma.create(args);
        },
        async update(args) {
          args.where = { ...args.where, tenantId };
          return prisma.update(args);
        },
        async delete(args) {
          args.where = { ...args.where, tenantId };
          return prisma.delete(args);
        },
        // ... extend other Prisma methods as needed
      },
    },
  });
};

export default prisma;

// pages/api/products/index.ts

import type { NextApiRequest, NextApiResponse } from 'next';
import nextConnect from 'next-connect';
import { withTenant, TenantNextApiRequest } from '../../../lib/middleware/withTenant';
import { getTenantPrismaClient } from '../../../lib/db';

const handler = nextConnect()
  .use(withTenant) // Apply the tenant identification middleware
  .get(async (req, res) => {
    const tenantId = req.tenantId!;
    const tenantPrisma = getTenantPrismaClient(tenantId);

    try {
      const products = await tenantPrisma.product.findMany();
      res.status(200).json(products);
    } catch (error) {
      console.error('Failed to fetch products:', error);
      res.status(500).json({ error: 'Failed to fetch products' });
    }
  })
  .post(async (req, res) => {
    const tenantId = req.tenantId!;
    const tenantPrisma = getTenantPrismaClient(tenantId);
    const { name, description, price } = req.body;

    if (!name || !price) {
      return res.status(400).json({ error: 'Name and price are required' });
    }

    try {
      const newProduct = await tenantPrisma.product.create({
        data: { name, description, price: parseFloat(price), tenantId },
      });
      res.status(201).json(newProduct);
    } catch (error) {
      console.error('Failed to create product:', error);
      res.status(500).json({ error: 'Failed to create product' });
    }
  });

export default handler;

Optimization & Best Practices

• Row-Level Security (RLS): For an additional layer of security, leverage PostgreSQL's Row-Level Security. RLS allows you to define policies that restrict which rows a user (or database role) can see or modify based on arbitrary conditions. You can create a policy that automatically filters data based on the current session's tenant_id, making it impossible to accidentally query data outside the tenant's scope, even if an application bug bypasses your Prisma-level filtering. This is a critical defense-in-depth strategy.
• Database Indexing: Ensure that the tenant_id column on all multi-tenant tables is properly indexed. For tables frequently queried with other columns (e.g., tenantId and status), consider creating composite indexes ((tenantId, status)). This dramatically improves query performance as your tenant count and data volume grow.
• Connection Pooling: Use a robust connection pooler (e.g., PgBouncer) for your PostgreSQL database. Multi-tenant applications often have many concurrent requests, and efficient connection management is crucial to prevent database overload.
• Tenant Onboarding/Offboarding: Automate the process of provisioning new tenants (e.g., creating the tenant record, setting up default data) and securely de-provisioning them (archiving or deleting data). Ensure proper data export capabilities for compliance.
• Monitoring and Alerting: Implement tenant-specific monitoring for performance metrics, error rates, and resource usage. This allows you to identify and address issues that might be specific to a single large tenant without impacting others.
• Data Backup and Restore: Develop a strategy for backing up and restoring tenant data. While a shared schema simplifies full database backups, consider how you might restore data for a *single* tenant if needed.

Business Impact & ROI

• Reduced Infrastructure Costs: By sharing a single database instance and application stack across multiple tenants, you drastically reduce server, database, and operational costs compared to provisioning separate environments for each customer. This efficiency directly boosts your profit margins.
• Faster Scalability & Onboarding: Adding new tenants becomes a streamlined, automated process. You can scale your user base rapidly without proportional increases in infrastructure complexity or cost, enabling quicker market penetration and revenue growth.
• Enhanced Data Security & Compliance: Logical data isolation, especially when augmented with RLS, provides a strong defense against data commingling and unauthorized access. This builds customer trust and helps meet stringent regulatory compliance requirements (e.g., GDPR, HIPAA).
• Streamlined Development & Maintenance: A unified codebase and infrastructure simplify development, deployment, and maintenance. New features are rolled out to all tenants simultaneously, reducing development cycles and allowing your team to focus on innovation rather than infrastructure management.
• Higher Profitability: The combination of cost savings, operational efficiency, and rapid scalability directly contributes to a healthier bottom line and a more competitive SaaS offering.

Conclusion