Supabase Multi-Tenant Schema Design: The Complete Guide

If you built your Supabase multi-tenant schema the way the quickstart shows, you've probably already hit the wall. A query that ran in 12 milliseconds against a handful of test rows suddenly takes close to two seconds once real customer data shows up. That's not bad luck. It's what happens when a Row Level Security policy gets re-evaluated on every row instead of once per query.

This guide covers the full architecture, not the quickstart version. You'll see how to choose between shared schema, schema-per-tenant, and database-per-tenant models, how to write RLS policies that don't fall into the infinite recursion trap, exactly why that 12ms-to-1.8s slowdown happens and how to fix it, and how to handle the parts most tutorials skip: users who belong to more than one organization, data that needs to stay shared across tenants, connection pooling at scale, and what RLS actually does and doesn't guarantee for enterprise compliance.

Table of Contents

1. What Multi-Tenancy Means in Supabase (and Why RLS Is the Default Choice)
2. Shared Schema vs Schema-per-Tenant vs Database-per-Tenant: Which Should You Use?
3. Designing the Core Multi-Tenant Tables
4. Writing RLS Policies Without Triggering Infinite Recursion
5. Why Is Your Supabase RLS Query So Slow?
6. Fixing RLS Performance: Indexes, initPlan Caching, and JWT Claims
7. Supporting Users Who Belong to Multiple Organizations
8. Sharing Data Across Tenants Without Breaking Isolation
9. Connection Pooling at Scale: Supavisor vs PgBouncer vs pgcat
10. Is RLS Alone Enough for Enterprise Compliance?
11. Quick Answers About Supabase Multi-Tenant Schema Design
12. Frequently Asked Questions

What Multi-Tenancy Means in Supabase (and Why RLS Is the Default Choice)

Multi-tenancy means multiple customer organizations, or tenants, share the same Postgres database and the same tables, while each tenant can only see and modify its own rows. Simply put, a tenant is any organization, account, or workspace whose data has to stay invisible to every other tenant using the same application.

Most frameworks solve this with application-layer filtering: middleware that adds a WHERE tenant_id = ? clause to every query. That works until someone writes a raw SQL report, an admin tool skips the middleware, or an ORM's eager loading pulls in a related row nobody filtered. Row Level Security moves the check into Postgres itself, so it's enforced no matter what code path touches the table, including the Supabase dashboard, a cron job, or a teammate's one-off script.

This is why RLS, not tenant-routing middleware, is the right default for a Supabase multi-tenant schema. The official PostgreSQL row security documentation is worth reading once in full since a few behaviors surprise people: if you enable RLS on a table and never add a policy, Postgres defaults to deny-all, not allow-all. No rows are visible or modifiable to anyone except roles that bypass RLS, such as superusers.

Shared Schema vs Schema-per-Tenant vs Database-per-Tenant: Which Should You Use?

For most B2B SaaS teams building on Supabase, a single shared schema secured with RLS is the right starting point. The other two models exist and solve real problems, but each comes with a Supabase-specific cost that generic Postgres tutorials never mention.

The migration point matters more than most architecture write-ups admit. Supabase's CLI and migration tooling are built around a single schema history. Once you split into per-tenant schemas, every DDL change has to be replayed across every tenant schema by hand or with custom orchestration, and that maintenance cost compounds with every tenant you add.

A practical middle ground: stay on shared schema for the vast majority of tenants, and reserve a dedicated schema or project only for the handful of enterprise accounts whose contracts demand physical isolation. You get the operational simplicity of one model without turning down enterprise deals that require the other.

Designing the Core Multi-Tenant Tables

Every multi-tenant schema needs three things at minimum: a table for tenants themselves, a table for who belongs to which tenant, and a consistent pattern for scoping every other table to a tenant.

-- Core tenant table: every organization using the platform
create table organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamptz not null default now()
);

-- Junction table: which users belong to which organization, and their role.
-- This is a many-to-many relationship, not a single tenant_id on the user.
create table organization_members (
  organization_id uuid not null references organizations(id) on delete cascade,
  user_id uuid not null references auth.users(id) on delete cascade,
  role text not null default 'member' check (role in ('owner', 'admin', 'member')),
  created_at timestamptz not null default now(),
  primary key (organization_id, user_id)
);

-- A tenant-scoped table. Notice organization_id is a foreign key,
-- not the primary key, and it appears on every table tenants own.
create table projects (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references organizations(id) on delete cascade,
  name text not null,
  sku text,
  created_at timestamptz not null default now()
);

The role column above is intentionally simple, a flat three-value check constraint. It won't hold up once a customer asks for a Manager tier that can approve invoices but not delete them, or an Admin in one workspace who's only a Viewer in another. For that level of hierarchy, plus the RLS policies and indexing to enforce it without a per-row join, see our guide to hierarchical Postgres RBAC for B2B SaaS.

One mistake shows up constantly once real customer data lands: a plain unique(sku) constraint. Two different tenants legitimately want to use the same SKU, invoice number, or slug. A single-column unique constraint breaks the moment your second tenant signs up and tries to reuse a value the first tenant already claimed.

-- Wrong: blocks every tenant except the first one from using this SKU
alter table projects add constraint projects_sku_unique unique (sku);

-- Right: scope the uniqueness check to the tenant
alter table projects add constraint projects_org_sku_unique unique (organization_id, sku);

Scope every uniqueness rule to organization_id from day one. Retrofitting this after tenants have collided on a supposedly-unique value means a painful data cleanup, not just a schema change.

Writing RLS Policies Without Triggering Infinite Recursion

The most common error developers hit when securing the membership table itself is infinite recursion. It happens when a policy on organization_members tries to check organization_members from inside its own policy definition, creating a circular dependency Postgres refuses to resolve.

The exact error, Postgres code 42P17, looks like this:

ERROR: infinite recursion detected in policy for relation "organization_members"

The fix is a SECURITY DEFINER function placed in a separate, non-public schema. A SECURITY DEFINER function runs with the privileges of whoever owns it rather than the calling user, which means it bypasses the caller's RLS on that table and breaks the circular check.

-- A dedicated schema for privileged helper functions
create schema if not exists private;

-- STABLE: Postgres can cache the result within a single statement.
-- SECURITY DEFINER: bypasses RLS on organization_members, which is
-- exactly what breaks the recursion.
create or replace function private.member_org_ids()
returns setof uuid
language sql
security definer
set search_path = public
stable
as $$
  select organization_id
  from organization_members
  where user_id = auth.uid();
$$;

-- The policy calls the function instead of querying
-- organization_members directly inside its own policy
create policy "members can view their org roster"
on organization_members
for select
using (organization_id in (select private.member_org_ids()));

This same helper function becomes the backbone for every other tenant-scoped table's policies, which is exactly what the next two sections build on.

Why Is Your Supabase RLS Query So Slow?

RLS policies are evaluated per row, not once per query. If a policy's condition can't be reduced to a static value before Postgres starts scanning, the planner has to re-check it for every candidate row.

Developers troubleshooting this exact symptom have reported a permission-check function firing thousands of times in a single query, once for every row scanned before the results even get filtered down. That's the mechanism behind the commonly cited "12ms to 1.8 seconds" story that shows up across Supabase community threads: the query itself isn't slow, the per-row policy evaluation is.

Two things cause this most often. First, a policy that calls auth.uid() or a similar function directly, without wrapping it, forces Postgres to re-invoke that function on every row even though the result never changes within the query. Second, a correlated subquery, one that references a column from the outer table being filtered, can't be cached and has to re-run per row by definition.

The moral: RLS isn't inherently slow. An RLS policy with no index to lean on, and no way for Postgres to cache a value that doesn't actually change per row, is slow.

For a deeper breakdown of the query planner internals behind this, including the InitPlan vs SubPlan distinction, function volatility, and the SECURITY DEFINER linter blind spot, see our full guide to Supabase RLS performance.

Fixing RLS Performance: Indexes, initPlan Caching, and JWT Claims

Fix 1: Index What Your Policies Actually Touch

The helper function from the previous section, private.member_org_ids(), queries organization_members filtered by user_id. Without an index on that column, every call to the function is a sequential scan.

-- Speeds up the membership lookup inside private.member_org_ids()
create index idx_org_members_user_id on organization_members (user_id, organization_id);

-- Speeds up the outer filter once membership is resolved
create index idx_projects_org_id on projects (organization_id, created_at desc);

Supabase's own RLS performance and best practices documentation reports improvements over 100x on large tables from adding the right index alone. One of their documented tests ran a policy against a 1 million row table filtered through a 1,000-row membership table. Unindexed, the query timed out past three minutes. Indexed correctly, it returned in normal range.

This IN() pattern, the same one private.member_org_ids() uses above, is also the fix for a much more specific and common performance complaint: slow queries scoped by a tenant or team ID. We cover the composite indexing and query planner mechanics behind it in more depth in our dedicated guide to the tenant_id IN() fix.

Fix 2: Stop Re-Evaluating Functions Per Row

Wrapping an auth function in a select statement causes Postgres to run it as an initPlan, a single cached execution for the whole statement, instead of once per row.

-- ❌ SLOWER: auth.uid() is (re-)evaluated per row in some query plans.
-- On a 100,000 row table, this function could run 100,000 times!
create policy "owner can view profile"
on profiles
for select
using (auth.uid() = user_id);

-- ✅ FASTER: wrapping in (select ...) forces Postgres to treat it as an initPlan.
-- It evaluates auth.uid() exactly ONCE, caches it, and uses that static value for every row.
create policy "owner can view profile"
on profiles
for select
using ((select auth.uid()) = user_id);

Because private.member_org_ids() is already both stable and structured as a non-correlated subquery (it only depends on auth.uid(), never on the outer table's columns), Postgres can already cache it efficiently. That structure, not just the index, is doing real work. If you write a simpler policy directly against auth.uid() without a helper function, always add the select wrapper.

Supabase's dashboard includes a Database Linter that flags policies using the unwrapped pattern under an auth_rls_initplan warning. Running it after writing new policies catches this before it reaches production.

Fix 3: Move Membership Into the JWT for Read-Heavy Tables

For tables read far more often than organization membership changes, you can skip the membership lookup at query time entirely by embedding a tenant's organization IDs directly into the JWT using a Custom Access Token Hook.

-- Runs in Supabase Auth before every access token is issued
create or replace function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
stable
as $$
declare
  claims jsonb;
  org_ids uuid[];
begin
  select array_agg(organization_id) into org_ids
  from organization_members
  where user_id = (event ->> 'user_id')::uuid;

  claims := coalesce(event -> 'claims', '{}'::jsonb);
  claims := jsonb_set(claims, '{app_metadata,org_ids}', to_jsonb(coalesce(org_ids, '{}'::uuid[])));
  event := jsonb_set(event, '{claims}', claims);

  return event;
end;
$$;

Enable it from Dashboard > Authentication > Hooks. The RLS policy then reads straight from the token instead of touching organization_members at all:

create policy "tenant isolation via jwt claim"
on projects
for select
using (
  organization_id = any (
    select jsonb_array_elements_text(auth.jwt() -> 'app_metadata' -> 'org_ids')::uuid
  )
);

The trade-off: membership changes only take effect the next time the user's token refreshes, not instantly. For most B2B SaaS apps, where role changes aren't second-by-second events, that delay is acceptable. For anything that needs to revoke access immediately, keep the membership-table lookup instead.

For the full setup, including how to fix that revocation gap without bringing back a per-row join, see our guide to writing RLS policies with custom JWT claims.

Indexing, initPlan caching, and JWT claims each solve one piece of the same problem. For how they fit together as a single production architecture, with benchmark numbers for each, see our guide to RLS query optimization.

Supporting Users Who Belong to Multiple Organizations

Don't store a single organization_id on the user record. Real B2B SaaS users, especially consultants, agency staff, and enterprise admins, routinely belong to more than one tenant. That's exactly why organization_members is a many-to-many junction table rather than a foreign key on auth.users.

The part most tutorials skip is tracking which organization is currently active in a given session. A user with three organizations shouldn't see all three tenants' data merged in one query. Pass the active organization as a header or client-side selection when the user switches workspace, and scope queries to that one tenant, even though the underlying policy still allows access to all organizations the user belongs to.

-- Combine "is a member of this org" with "this is the currently active org"
create policy "read active organization only"
on projects
for select
using (
  organization_id in (select private.member_org_ids())
  and organization_id = current_setting('request.active_org_id', true)::uuid
);

One edge case worth planning for: if a user's membership list grows very large, the IN list feeding a policy can get expensive to evaluate past roughly 10,000 items. That's rare for a typical SaaS user, but worth knowing if you're building something like an internal admin role that belongs to hundreds of organizations at once.

Sharing Data Across Tenants Without Breaking Isolation

B2B SaaS data is rarely 100% siloed. A shared template library, a public product catalog, or content a super-admin manages across every tenant all need to be visible to everyone without abandoning tenant isolation for the rest of the schema.

The pattern: make organization_id nullable on tables that need this, and treat NULL as "global."

alter table templates add column organization_id uuid references organizations(id);

create policy "read own tenant templates plus global templates"
on templates
for select
using (
  organization_id is null
  or organization_id in (select private.member_org_ids())
);

For super-admin access across every tenant, avoid bolting an is_admin check onto every existing policy. Add it as its own separate policy instead. Postgres combines multiple permissive policies on the same table with OR, so an admin-only policy can grant broader access without you having to rewrite every tenant-scoped policy you already wrote.

Connection Pooling at Scale: Supavisor vs PgBouncer vs pgcat

Serverless frontends like Vercel functions or Lambda open and close database connections constantly. A multi-tenant app without proper pooling exhausts Postgres's connection limit fast, especially once you have dozens of tenants generating concurrent traffic.

Supabase now runs both pooler types side by side rather than forcing a single choice. Supavisor, the Shared Pooler, handles high connection counts across the platform but doesn't support prepared statements in transaction mode. The Dedicated Pooler, Supabase's managed PgBouncer, runs alongside it on paid compute tiers specifically to cover that gap.

If your ORM throws prepared-statement errors under Supavisor's transaction mode, that's the expected trade-off, not a bug. Switch that specific workload to session mode or the Dedicated Pooler rather than disabling pooling altogether.

Is RLS Alone Enough for Enterprise Compliance?

No. RLS enforces logical isolation, who can query which rows, but enterprise compliance and disaster recovery often demand guarantees RLS was never designed to provide.

The Disaster Recovery Problem in a Shared Schema

If one enterprise customer needs a point-in-time recovery rollback because of data corruption on their side, a shared-schema architecture makes that nearly impossible without also rolling back, and destroying, every other tenant's data in the same tables. Point-in-time recovery in Postgres operates at the database level, not the row level.

Realistic mitigations include soft-delete columns with full audit history instead of relying on PITR for tenant-level recovery, and moving specifically the tenants who contractually need this guarantee into their own schema or project, while the rest of your tenants stay on the cheaper shared model.

Compliance Needs More Than Access Control

GDPR (UK/EU), SOC 2 (US), and PIPEDA (Canada) all expect more than "only the right rows are returned." Auditors look for logging of who accessed what, encryption at rest, documented recovery procedures, and evidence of regular access reviews. RLS satisfies the access-control piece of that picture. It doesn't generate an audit trail, encrypt anything, or prove you can recover a single tenant's data independently of the rest.

Treat RLS as necessary infrastructure for a multi-tenant Supabase schema, not as a complete compliance answer by itself.

That's a separate question from whether the RLS you already have is configured correctly in the first place. 5 Silent RLS Mistakes That Leave Your Database Exposed audits five specific, common ways a policy setup that passes code review still lets tenant data through.

Quick Answers About Supabase Multi-Tenant Schema Design

What Causes Supabase RLS to Slow Down at Scale?

Simply put, RLS slows down when a policy calls an unwrapped auth function or a correlated subquery, forcing Postgres to re-evaluate it once for every row scanned instead of once per query. On a table with a few thousand rows, that can mean thousands of redundant function calls before a single result comes back. It matters most once a tenant-scoped table grows past the point where a sequential scan becomes the planner's cheapest option.

Supabase Multi-Tenant Schema at a Glance

When Does This Apply?

This applies to any Supabase project storing more than one customer's data in the same tables, especially once a tenant-scoped table passes roughly 10,000 to 100,000 rows. A small side project with a handful of users won't notice the difference. A production B2B SaaS platform will.

Pros and Cons of Shared Schema + RLS

• Pro: Full compatibility with Supabase Realtime, PostgREST, and the Data API with no extra configuration
• Pro: One migration history, no per-tenant schema drift, works cleanly with supabase db push
• Pro: Cheapest to run since every tenant shares one Postgres instance and connection pool

• Con: A missed filter or misconfigured policy is the only thing standing between tenants and each other's data
• Con: No way to give one enterprise tenant an independent point-in-time recovery without affecting everyone else
• Con: Can't satisfy customers who contractually require physical, not just logical, data isolation

Frequently Asked Questions

Can I let the client send its own tenant ID, or does it have to come from the server?

Never trust a client-supplied tenant ID for anything security-sensitive. Derive it from the authenticated session, either through auth.uid() joined against organization_members or a custom JWT claim. A client-supplied value is just a request; RLS is what actually enforces it.

Do I need a dedicated database for every enterprise customer?

Not by default. Most teams stay on shared schema with RLS until one specific contract requires physical isolation, then move only that tenant to its own schema or project, keeping everyone else on the cheaper shared model.

How do I safely test RLS policies without risking production data?

Use Supabase's RLS Tester, available from the dashboard's feature previews. It runs a query as a specific user, shows exactly which policy fired, and can even convert client-library code into the equivalent SQL for testing.

Does Realtime enforce RLS the same way normal queries do?

Yes, but per event over the websocket connection rather than once per request. A policy that's cheap for a single REST call can add noticeable overhead on a busy Realtime channel, so index those policy columns too.

Can one user account belong to more than one organization?

Yes. Model it with a many-to-many organization_members table rather than a single tenant ID on the user record, then track which organization is currently active at the session level so RLS knows which tenant's rows to return.

Is Row Level Security enough to pass a SOC 2 or GDPR audit?

RLS covers access control, one control among many. Auditors also expect logging, encryption at rest, documented recovery procedures, and evidence of regular access reviews. Treat RLS as necessary infrastructure, not a compliance checkbox by itself.

What happens if I enable RLS on a table but forget to add any policies?

Postgres defaults to deny-all, not allow-all. With RLS enabled and zero policies, no rows are visible or modifiable to anyone except roles that bypass RLS, such as superusers or the service_role key.

Why does my RLS policy still feel slow after I added an index?

Check whether the policy calls a function like auth.uid() directly instead of wrapping it in (select auth.uid()). Without that wrapper, Postgres can re-run the function per row regardless of indexing, which is often the real bottleneck. See this troubleshooting guide for the full diagnostic matrix and fixes.

Multi-tenant schema design in Supabase comes down to a small set of decisions that compound over time: shared schema over per-tenant complexity for most teams, membership modeled as many-to-many from the start, RLS policies structured so Postgres can cache them instead of re-running them per row, and a clear-eyed view of what RLS does and doesn't cover once compliance conversations start.

None of this needs to be solved perfectly on day one. It needs to be solved in a way that doesn't require a rewrite once your first real enterprise customer shows up. Bookmark this guide and come back to it as each of these pieces becomes the thing that's actually breaking, that's usually the order teams hit them in anyway.