Building a true multi-tenant SaaS: PostgreSQL RLS, subdomains, and Next.js middleware

Most "multi-tenant" SaaS tutorials cheat. They show you WHERE tenant_id = ? in every query and call it done. That's not multi-tenant - that's a bug waiting to happen.

True multi-tenancy means the database enforces isolation. You literally cannot read another tenant's data, even if you write a bug. Here's how I built it with Supabase + PostgreSQL RLS + Next.js middleware.

Why RLS, not app-level filtering?

Consider this "tenant-scoped" query:

// ? WRONG - easy to forget the WHERE clause
const { data } = await supabase
  .from('customers')
  .select('*')
  .eq('tenant_id', currentTenantId);

One missed .eq() in a new feature, and you've leaked data. The bug is silent - no test catches it, no type system warns you.

With Row Level Security, the database refuses the query:

-- ? RIGHT - database enforces it
CREATE POLICY tenant_isolation ON customers
  USING (tenant_id = current_setting('app.current_tenant')::uuid);

Forget the WHERE clause? Doesn't matter. RLS filters it out at the engine level. A bug in your app code can no longer become a data leak.

The 3 layers

1. DNS + edge middleware - resolve acme.example.com ? tenant_id at the edge
2. JWT with tenant claim - every request carries the tenant context
3. PostgreSQL RLS - the actual data gatekeeper

Customer ? acme.example.com
   ?
Vercel Edge Middleware
   ?? Subdomain ? tenant_id lookup (in KV or DB)
   ?? Set request header x-tenant-id
   ?
Next.js Server Action / API Route
   ?? Read x-tenant-id from headers
   ?? Generate Supabase JWT with tenant claim
   ?
Supabase (Postgres)
   ?? RLS policy reads JWT claim, filters every row

Layer 1: Subdomain ? tenant_id

Store tenant metadata in a fast read path (Vercel KV, or a small Postgres table):

// middleware.ts
import { NextResponse, type NextRequest } from 'next/server';

export async function middleware(req: NextRequest) {
  const host = req.headers.get('host') || '';          // acme.example.com
  const subdomain = host.split('.')[0];                // acme

  // Skip on the apex domain
  if (subdomain === 'www' || subdomain === 'example') {
    return NextResponse.next();
  }

  // Lookup tenant (cache in KV in production)
  const tenant = await getTenantBySlug(subdomain);
  if (!tenant) {
    return NextResponse.redirect(new URL('https://example.com/404'));
  }

  // Pass to downstream handlers
  const requestHeaders = new Headers(req.headers);
  requestHeaders.set('x-tenant-id', tenant.id);

  return NextResponse.next({ request: { headers: requestHeaders } });
}

export const config = {
  matcher: ['/((?!_next/static|_next/image|favicon.ico).*)'],
};

Layer 2: JWT with tenant claim

Generate a Supabase JWT that includes the tenant ID as a custom claim:

// lib/supabase.ts
import { createClient } from '@supabase/supabase-js';

export function getSupabaseForRequest(req: Request) {
  const tenantId = req.headers.get('x-tenant-id');
  if (!tenantId) throw new Error('Missing tenant context');

  // Sign a JWT with the tenant claim
  // (In production, do this server-side with the Supabase service role key)
  const token = signJwtWithClaim({
    sub: req.headers.get('x-user-id'),
    tenant_id: tenantId,
    role: 'authenticated',
    exp: Math.floor(Date.now() / 1000) + 60 * 60,
  });

  return createClient(
    process.env.NEXT_PUBLIC_SUPABASE_URL!,
    process.env.NEXT_PUBLIC_SUPABASE_ANON_KEY!,
    { global: { headers: { Authorization: `Bearer ${token}` } } }
  );
}

Layer 3: PostgreSQL Row Level Security

This is the actual lock. Every table that holds tenant data gets a policy:

-- Helper: get current tenant from JWT
CREATE OR REPLACE FUNCTION current_tenant_id()
RETURNS uuid AS $$
  SELECT NULLIF(
    current_setting('request.jwt.claims', true)::json->>'tenant_id',
    ''
  )::uuid;
$$ LANGUAGE sql STABLE;

-- Enable RLS on every tenant-scoped table
ALTER TABLE customers ENABLE ROW LEVEL SECURITY;
ALTER TABLE conversations ENABLE ROW LEVEL SECURITY;
ALTER TABLE messages ENABLE ROW LEVEL SECURITY;

-- Policy: users can only see rows from their tenant
CREATE POLICY tenant_isolation ON customers
  FOR ALL
  USING (tenant_id = current_tenant_id())
  WITH CHECK (tenant_id = current_tenant_id());

CREATE POLICY tenant_isolation ON conversations
  FOR ALL
  USING (tenant_id = current_tenant_id())
  WITH CHECK (tenant_id = current_tenant_id());

CREATE POLICY tenant_isolation ON messages
  FOR ALL
  USING (tenant_id = current_tenant_id())
  WITH CHECK (tenant_id = current_tenant_id());

To make this work, Supabase needs to pass the JWT claim into the Postgres session:

-- In Supabase Dashboard: Settings ? API ? JWT Settings
-- Add to "Custom Claims" or use a Postgres function hook
ALTER DATABASE postgres SET request.jwt.claims TO '{"tenant_id":""}';

-- Or, more cleanly, use Supabase's built-in auth.uid() pattern
-- and add tenant_id alongside it

Testing that RLS actually works

This is the part most tutorials skip. Write a test that proves a tenant can't see another tenant's data.

// tests/rls.test.ts
import { getSupabaseForRequest } from '@/lib/supabase';

test('tenant A cannot read tenant B data', async () => {
  // Insert data as tenant A
  const supabaseA = getSupabaseForRequest(mockRequest({ tenantId: 'A' }));
  await supabaseA.from('customers').insert({ name: 'Alice', tenant_id: 'A' });

  // Try to read as tenant B
  const supabaseB = getSupabaseForRequest(mockRequest({ tenantId: 'B' }));
  const { data, error } = await supabaseB.from('customers').select('*');

  expect(data).toEqual([]);  // empty - RLS filtered it out
});

I learned this the hard way. My first RLS deployment had a bug where the JWT claim wasn't being passed correctly. Without the test, I would have shipped a cross-tenant data leak.

The pitfalls

1. Forgetting to enable RLS on new tables. I have a CI check: any new CREATE TABLE without ENABLE ROW LEVEL SECURITY fails the build.
2. Service role bypasses RLS. The Supabase service role key has BYPASSRLS. Only use it server-side for admin tasks, never pass it to the client.
3. Bulk operations need explicit tenant_id. RLS doesn't add a default value; you have to pass it. I use a BEFORE INSERT trigger to set it from the JWT.
4. Migrations are dangerous. When you add RLS to an existing table, you must do it in a transaction with the right role checks, or you can lock yourself out.

Get the code

The full reference implementation (Next.js + Supabase + edge middleware + RLS policies + test suite) is open source:

?? github.com/Omerfaruk-aydn/crmhizmetbotu

It's the production architecture of a live SaaS handling thousands of conversations per month for multiple businesses - all on a single Postgres database with zero data leaks.

---

Originally published on omerfarukaydn.com - more details on the subdomain wildcard DNS, Vercel KV caching, and the WebSocket multi-tenant story.