Multi-tenant Postgres for SaaS: row-level security with Prisma

When you're a SaaS, sooner or later you face multi-tenancy: how do you keep thousands of clients' data in one DB without tenants ever seeing each other's data. In SME Financial OS I went with shared schema + row-level security on Postgres. Half a year into production, I don't regret the call - even though the path wasn't quite straight.

Here's the full setup, including Prisma integration, demo mode and benchmarks.

Three multi-tenancy paths

For SME Financial OS - hundreds of firms, not thousands, shared metrics, shared pricing tier - shared schema + RLS is the sweet spot. Database per tenant would cost 10× on infra and ops.

Step 1: Schema with org_id everywhere

Every tenanted table has an org_id foreign key to organizations. No exceptions. A new table without org_id is a bug.

CREATE TABLE organizations (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  slug TEXT UNIQUE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);
 
CREATE TABLE invoices (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  org_id UUID NOT NULL REFERENCES organizations(id) ON DELETE CASCADE,
  number TEXT NOT NULL,
  amount_cents BIGINT NOT NULL,
  customer_name TEXT,
  issued_at DATE NOT NULL,
  created_at TIMESTAMPTZ DEFAULT now()
);
 
CREATE INDEX idx_invoices_org ON invoices(org_id);

ON DELETE CASCADE on org_id means: when you delete an organization, all its data goes with it. For GDPR cleanup it's a single SQL command.

Step 2: RLS policies

This is where shallow implementations break. Postgres has had RLS since 9.5, but the policy must be explicit - there's no "default deny" without setup.

ALTER TABLE invoices ENABLE ROW LEVEL SECURITY;
ALTER TABLE invoices FORCE ROW LEVEL SECURITY; -- enforces even for table owner
 
CREATE POLICY tenant_isolation ON invoices
  USING (org_id::text = current_setting('app.current_org_id', true));
 
CREATE POLICY demo_read_only ON invoices FOR SELECT
  USING (org_id::text = 'demo-public-uuid');

What's happening:

• ENABLE - RLS is active
• FORCE - even superuser must comply (otherwise Prisma client ignores it)
• current_setting('app.current_org_id', true) - reads a session variable; true means "return NULL when unset" instead of erroring
• The second policy enables demo mode, where anyone can see read-only data behind a special org_id

Without FORCE, RLS only applies to roles that don't have bypass. When Prisma connects as the schema owner, the policy silently skips. Golden debug moment.

Step 3: Prisma middleware sets the session variable

Prisma doesn't know which tenant is active on its own. You have to tell it via a session variable set before each query. Best place: a Prisma extension (Prisma 5+) or classic middleware.

// db.ts
import { PrismaClient } from '@prisma/client';
import { AsyncLocalStorage } from 'node:async_hooks';
 
export const orgContext = new AsyncLocalStorage<{ orgId: string }>();
 
const base = new PrismaClient();
 
export const prisma = base.$extends({
  query: {
    async $allOperations({ args, query }) {
      const ctx = orgContext.getStore();
      if (!ctx?.orgId) {
        // explicit opt-out for admin/cron tasks
        return query(args);
      }
      // Postgres session level - sticks for the entire connection
      await base.$executeRawUnsafe(
        `SELECT set_config('app.current_org_id', $1, true)`,
        ctx.orgId,
      );
      return query(args);
    },
  },
});

And in the request handler:

// app/api/invoices/route.ts
import { auth } from '@/lib/auth';
import { orgContext, prisma } from '@/db';
 
export async function GET() {
  const session = await auth();
  if (!session?.orgId) return new Response('unauthorized', { status: 401 });
 
  return orgContext.run({ orgId: session.orgId }, async () => {
    const invoices = await prisma.invoice.findMany();
    // policy automatically filters to org_id
    return Response.json(invoices);
  });
}

AsyncLocalStorage carries the tenant context through the entire request tree - even across async boundaries (e.g. await Promise.all([...])). No prop drilling, no req.orgId argument everywhere.

Demo mode as a policy

Clients wanted public demo without signup - pre-loaded invoices, dashboard, all read-only. I could have built fake routes with mock data. Instead: demo is a tenant like any other, just with a dedicated org_id and a read-only policy.

const DEMO_ORG_ID = 'demo-public-uuid-static';
 
export async function getDemoData() {
  return orgContext.run({ orgId: DEMO_ORG_ID }, async () => {
    return prisma.invoice.findMany({ take: 50 });
  });
}

Plus an SQL policy disallowing INSERT/UPDATE/DELETE for demo:

CREATE POLICY demo_no_writes ON invoices FOR INSERT
  WITH CHECK (org_id::text != 'demo-public-uuid-static');

When a demo user clicks "Create invoice", the check fails with an RLS exception. The UI catches it and shows "Demo mode - sign up for full functionality". No separate code path.

Admin bypass: opt-out of RLS

For admin endpoints (cross-tenant analytics, support tools) RLS is in the way. I don't use "superuser bypass" (risky). Instead: explicit opt-out in the Prisma extension - if you don't call orgContext.run, the query runs without the session variable and RLS blocks it (returns 0 rows).

For admin login I use a second Postgres role:

CREATE ROLE admin_role;
GRANT admin_role TO app_user;
ALTER POLICY tenant_isolation ON invoices
  USING (
    org_id::text = current_setting('app.current_org_id', true)
    OR pg_has_role(current_user, 'admin_role', 'MEMBER')
  );

The connection string for the admin endpoint runs SET ROLE admin_role at the start of the transaction. That gives bypass only inside that specific handler.

Performance: ~5% overhead

Benchmark on a production DB (~12 firms, 280,000 invoices, AWS RDS db.t3.medium):

~5% overhead at p95. For the peace of mind that RLS gives - the compiler (Postgres) won't let you cross a policy without an explicit admin role - it's a cheap trade.

Common gotchas

1. Forgotten FORCE. Without it, the policy doesn't apply to the Prisma migrations connection (which is owner). Demo: on staging you see all tenants' data, on prod you don't. Always FORCE.

2. set_config with false as the third arg. With false it sets at the connection level (sticks on pooled connections). With true it's transaction-scoped. Always true for multi-tenant - otherwise the next request on a recycled connection sees someone else's data.

3. Forgotten table. You add a new table, forget to enable RLS. Test passes, prod is a massive leak. Fix: a CI lint script that greps for CREATE TABLE in migrations and checks for ENABLE ROW LEVEL SECURITY.

# scripts/check-rls.sh
for f in migrations/*.sql; do
  if grep -q "CREATE TABLE" "$f" && ! grep -q "ROW LEVEL SECURITY" "$f"; then
    echo "MISSING RLS in $f" >&2
    exit 1
  fi
done

Lessons

• FORCE ROW LEVEL SECURITY always. Owner bypass is more often a bug than a feature.
• AsyncLocalStorage > prop drilling for tenant context. No orgId in every function signature.
• Demo mode as a tenant saves you a parallel code path with mock data. No drift between prod and demo.
• CI lint for RLS is cheap insurance against forgotten tables.
• Per-request session variable is the right pattern; NEVER hard-code org_id in queries - one bug and the leak is done.
• ~5% perf overhead is a steal for peace of mind. Customer trust > 5 ms.
• Admin bypass via role is cleaner than superuser or bypassRowLevelSecurity flags.

What's next

• SME Financial OS case study → - the product built on this architecture
• WhatsApp Business API → - related backend integration
• Stripe Checkout vs Subscriptions → - billing layer on top of multi-tenant DB

If you're doing a multi-tenant SaaS architecture review or migrating from schema-per-tenant to shared + RLS, drop me a line. Migrating existing data is the biggest risk, but plannable.