Saas Multi Tenant | Skills Pool

Saas Multi Tenant | Skills Pool

Determine the tenancy model. Ask the user about their scale expectations and isolation requirements. For most SaaS apps under 1000 tenants, shared-schema with a tenant_id column on every table is the correct default. Schema-per-tenant adds operational overhead (migrations run N times). Database-per-tenant is only justified when tenants have regulatory data residency requirements.
Add tenant_id to every tenant-scoped table. The column must be NOT NULL, type UUID or TEXT, and included in every composite index. Never allow a tenant-scoped table to exist without this column — a missing tenant_id is a data leak waiting to happen.
Set up PostgreSQL Row-Level Security (RLS). Create a policy on each tenant-scoped table that filters rows by current_setting('app.current_tenant_id'). This acts as a database-level safety net — even if application code forgets a WHERE clause, RLS blocks cross-tenant reads.
Build tenant-aware middleware. At the start of every request, extract the tenant_id from the authenticated session or JWT claims. Set it on the database connection using SET LOCAL app.current_tenant_id = '...' inside a transaction. Every subsequent query in that request inherits the tenant scope automatically.
Scope all ORM queries by tenant. If using Prisma, apply a global middleware that injects where: { tenantId } into every findMany, findFirst, update, and delete call. If using Drizzle, create a base query builder that includes the tenant filter. Never rely on developers remembering to add the filter manually.
Handle tenant-aware migrations. Every new table migration must include tenant_id as a column. Write a linting rule or CI check that rejects any migration creating a table without tenant_id unless the table is explicitly marked as global (e.g., plans, feature_flags).
Build cross-tenant admin routes separately. Admin endpoints that aggregate data across tenants must bypass RLS explicitly using SET LOCAL role = 'admin_bypass' or a dedicated database role. These routes must be protected by a separate admin authentication flow — never reuse tenant user sessions for admin access.
Implement tenant provisioning. When a new customer signs up, create their tenant record, seed default data (roles, settings, onboarding state), and assign the founding user. Wrap this in a database transaction so partial provisioning never leaves orphan records.

-- Enable RLS on the table
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects FORCE ROW LEVEL SECURITY;

-- Policy: users can only see rows where tenant_id matches the session variable
CREATE POLICY tenant_isolation ON projects
  USING (tenant_id = current_setting('app.current_tenant_id')::uuid);

-- Policy for INSERT: new rows must match the current tenant
CREATE POLICY tenant_insert ON projects
  FOR INSERT
  WITH CHECK (tenant_id = current_setting('app.current_tenant_id')::uuid);

import { Pool } from "pg";

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

async function tenantMiddleware(req, res, next) {
  const tenantId = req.auth?.tenantId; // extracted from JWT during auth
  if (!tenantId) return res.status(403).json({ error: "No tenant context" });

  const client = await pool.connect();
  try {
    await client.query("BEGIN");
    // Use set_config — SET LOCAL does not accept bind placeholders ($1)
    await client.query("SELECT set_config('app.current_tenant_id', $1, true)", [tenantId]);
    req.db = client;
    req.tenantId = tenantId;

    // Cleanup on response finish — guarantees release even if handler skips next()
    res.on("finish", async () => {
      try { await client.query("COMMIT"); } catch { await client.query("ROLLBACK"); }
      client.release();
    });

    next();
  } catch (err) {
    await client.query("ROLLBACK").catch(() => {});
    client.release();
    next(err);
  }
}

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

// Tables that do NOT have tenant_id (global tables)
const GLOBAL_TABLES = new Set(["Plan", "FeatureFlag", "SystemConfig"]);

function createTenantPrisma(tenantId: string): PrismaClient {
  const prisma = new PrismaClient();

  prisma.$use(async (params, next) => {
    if (GLOBAL_TABLES.has(params.model ?? "")) return next(params);

    // Initialize args.where — Prisma passes undefined args for calls like findMany()
    params.args = params.args ?? {};
    params.args.where = params.args.where ?? {};

    // Inject tenant filter on reads (skip findUnique — it only accepts unique-field selectors)
    if (["findMany", "findFirst", "count", "aggregate"].includes(params.action)) {
      params.args.where = { ...params.args.where, tenantId };
    }

    // Inject tenant_id on creates
    if (["create", "createMany"].includes(params.action)) {
      params.args.data = params.args.data ?? {};
      if (params.action === "createMany") {
        params.args.data = params.args.data.map((d: any) => ({ ...d, tenantId }));
      } else {
        params.args.data = { ...params.args.data, tenantId };
      }
    }

    // Scope updates and deletes
    if (["update", "updateMany", "delete", "deleteMany"].includes(params.action)) {
      params.args.where = { ...params.args.where, tenantId };
    }

    return next(params);
  });

  return prisma;
}