Supabase Multi-Tenancy in Production: Schema, RLS, and the Patterns That Actually Work

Putting the Tenant in the JWT#

The cleanest way to pass the current tenant to RLS policies is via the JWT app_metadata. Set it when the user authenticates or when they join a workspace:

Populate app_metadata from your backend using the Admin SDK:

The caveat: JWT claims are baked at auth time. If a user's tenant changes mid-session, they need a new token. For most apps this is fine. If users switch between multiple tenants in one session, use request-time config instead.

Request-Time Tenant Config#

For background jobs, service-role API calls, or multi-tenant switching, set the tenant as a Postgres config variable at the start of each request:

From your server action or route handler:

The true flag on set_config makes the setting transaction-scoped so it resets automatically. The true flag on current_setting returns null instead of throwing when the variable isn't set — safer default.

Wrap JWT Functions in SELECT#

This is the same pattern from RLS basics but worth repeating because it's even more important in multi-tenant schemas where policies fire on every row.

The SELECT wrapper tells Postgres to treat the function as an initPlan — computed once, reused for every row. On tables with thousands of rows per tenant this difference is measurable.

Resolving the Tenant in Next.js Middleware#

In App Router, resolve the tenant once in middleware and attach it to a request header:

Then in any server component or action:

Cache the slug→tenant lookup. Without caching you're hitting the DB on every page render. A 60-second TTL is usually fine — tenant records change infrequently, and a briefly stale lookup is harmless.

The Gotchas That Cost Time#

Tables without RLS

Every new table needs ENABLE ROW LEVEL SECURITY — and FORCE ROW LEVEL SECURITY so the postgres superuser doesn't bypass it silently. Easy to miss when you're moving fast.

Run this before every deploy. Anything unexpected means a tenant can read another tenant's rows.

UPDATE without a SELECT policy

PostgreSQL evaluates SELECT policies when processing UPDATEs. If there's no SELECT policy, the UPDATE returns 0 affected rows — no error, no warning, just silence. Always define both:

Cross-tenant admin queries

Your admin dashboard needs to see all tenants. RLS blocks it. Pattern: use the service role key for admin routes only, never expose it to the client, and set tenant context explicitly when narrowing to a specific tenant.

Deleting a tenant

Don't rely entirely on ON DELETE CASCADE for complex schemas — it's hard to debug when it fires in an unexpected order. Write an explicit deletion function:

How Do You Test Tenant Isolation?#

Create two test tenants, insert rows for each, then query as each tenant to verify they can't see each other's data.

Also run the rowsecurity = false check against your schema before every release. RLS misconfiguration is silent and the consequences are serious — one missing policy and every tenant can read all rows.

When Should You Split a Tenant Into Its Own Project?#

When a tenant's load starts degrading everyone else, or when they need a different schema, a different region, or a contractual isolation guarantee, it's time to move them to their own project.

The migration path: COPY TO for the data snapshot, restore to the new project with COPY FROM, sync rows written during migration, cut over the connection string. Budget a maintenance window.

Build the export tooling early: a export_tenant function that snapshots a tenant's data to CSV is useful for debugging, migration, and churned tenants asking for their data. You don't want to be writing this under pressure when an enterprise client is waiting.