Multi-Tenant Architecture with Supabase: Doing It Right with RLS

Almost every B2B SaaS ends up needing multi-tenancy - data belonging to Company A must never be visible to Company B, even though both live in the same tables. Supabase supports this well via Row Level Security, but there are a few genuinely different ways to structure it, and picking the wrong one early costs real rework later.

The two main approaches: shared schema vs schema-per-tenant

Shared schema with a tenant_id column is the right default for the overwhelming majority of SaaS products. Every tenant's rows live in the same tables, distinguished by a tenant_id (or organization_id) foreign key, with RLS policies enforcing that a user can only see rows matching their tenant.

Schema-per-tenant (a separate Postgres schema, or even a separate database, per customer) gives the strongest possible isolation and is sometimes required for specific enterprise/compliance deals, but it doesn't scale operationally past a modest number of tenants - migrations have to run against every schema, and Supabase's tooling is built around the single-schema model. Reach for this only when a specific customer contract genuinely requires physical data separation, not as a default.

The rest of this covers the shared-schema approach, since it's what the large majority of projects should actually build.

Core schema shape

create table public.organizations (
  id uuid default gen_random_uuid() primary key,
  name text not null,
  created_at timestamptz default now()
);

create table public.organization_members (
  organization_id uuid references public.organizations(id) not null,
  user_id uuid references auth.users(id) not null,
  role text not null default 'member',
  primary key (organization_id, user_id)
);

create table public.projects (
  id uuid default gen_random_uuid() primary key,
  organization_id uuid references public.organizations(id) not null,
  name text not null,
  created_at timestamptz default now()
);

Every tenant-scoped table (projects and anything below it) carries organization_id. This is the column every RLS policy will key off of.

The RLS pattern: a reusable membership check

Rather than repeating a join across organization_members in every single policy, extract it into a function:

create function public.is_organization_member(org_id uuid)
returns boolean
language sql
security definer
stable
as $$
  select exists (
    select 1 from public.organization_members
    where organization_id = org_id and user_id = auth.uid()
  );
$$;

create policy "Members can view their organization's projects"
  on public.projects for select
  using (public.is_organization_member(organization_id));

create policy "Members can insert projects into their organization"
  on public.projects for insert
  with check (public.is_organization_member(organization_id));

This keeps every table's policy simple and readable, and centralizes the membership logic in one place - if the definition of "member" ever changes (adding a suspended-user state, for example), it changes in one function instead of every policy across every table.

Role-based access within a tenant

Most B2B products need more than "is a member" - they need admin-only actions. Extend the same pattern:

create function public.has_organization_role(org_id uuid, required_role text)
returns boolean
language sql
security definer
stable
as $$
  select exists (
    select 1 from public.organization_members
    where organization_id = org_id
      and user_id = auth.uid()
      and role = required_role
  );
$$;

create policy "Admins can delete projects"
  on public.projects for delete
  using (public.has_organization_role(organization_id, 'admin'));

The JWT custom claims approach - and why to be careful with it

Supabase supports embedding custom claims (like the user's current organization) directly in the JWT via auth hooks, which lets policies check auth.jwt() directly instead of querying the membership table on every request:

create policy "Members can view their organization's projects"
  on public.projects for select
  using (organization_id = (auth.jwt() ->> 'organization_id')::uuid);

This is faster (no join needed per request) but introduces a real staleness risk: if a user is removed from an organization, their existing JWT still carries the old claim until it expires or is refreshed. For most apps, the membership-table lookup approach above is the safer default - the performance difference is rarely the actual bottleneck, and correctness (immediate revocation) usually matters more than shaving a join. Reserve the JWT-claims approach for cases where you've measured that the membership lookup is an actual hot path, and pair it with short JWT expiry so staleness windows stay small.

Pitfalls specific to multi-tenant RLS

Forgetting organization_id on a new table. Every new tenant-scoped table needs both the column and its own policies - there's no inheritance. A table added six months after the initial design, without RLS enabled or without the membership check, is invisible in testing (the developer testing it is a member of the only organization they've created) and a real leak the moment a second tenant exists.

Indirect joins that skip the tenant check. A policy on a child table (say, project_comments) that only checks project_id exists, without verifying the querying user belongs to the organization that owns that project, can leak comments across tenants even though the projects table itself is properly locked down. Every policy needs to trace back to organization membership, not just to "does this row exist and relate to something."

Testing only with a single-tenant account. Same lesson as general RLS testing: create at least two organizations in a test environment and verify a member of Org A genuinely cannot read, write, or enumerate Org B's data via direct API calls - not just through the app's UI, which won't offer the option to try.

Why this is worth getting right early

Retrofitting multi-tenancy onto a single-tenant schema, or fixing a tenant isolation bug after a customer's data has been exposed to another customer, is a substantially more painful conversation than designing the organization_id + RLS pattern from the first migration. It costs almost nothing extra to add at the start and a great deal to add later.

If you're designing multi-tenancy for a new SaaS on Supabase, or auditing an existing one for isolation gaps, this is exactly the kind of architecture review I do - book a call.