Multi-tenant Supabase architecture for UK B2B SaaS

Multi-tenant SaaS on Supabase is one of the cleanest setups available when you nail row-level security, tenant scoping and audit logs from day one. This post is the pattern we use across every UK B2B SaaS we ship — and the three production-grade failure modes that catch most teams.

The three architectural choices that matter

1. Schema-per-tenant vs row-per-tenant

You can give each tenant their own Postgres schema (schema-per-tenant) or share a schema and partition by a tenant_id column (row-per-tenant).

Schema-per-tenant sounds safer but causes operational pain past about 50 tenants — every schema migration runs N times, backups are larger, indexes are duplicated, connection-pooling gets weird. Row-per-tenant is what every mature B2B SaaS converges on.

Row-per-tenant with RLS, in Postgres, is the right default for UK B2B SaaS up to roughly the 10,000-tenant scale. Past that, you start partitioning physically and the conversation changes.

2. Where the tenant_id comes from

The tenant_id has to be available everywhere a query runs. The clean approach is to put it in the user's JWT as a custom claim, so RLS policies can read it via auth.jwt() -> 'tenant_id' without a second round-trip to the database.

Supabase makes this clean: write an Auth hook (a Postgres function) that runs before token issuance and adds the user's tenant_id to the JWT. Refresh tokens carry it; access tokens carry it; everything downstream knows who the user is and what tenant they belong to.

3. How users belong to tenants

Three common patterns:

• One user, one tenant — simplest, fine for most B2B SaaS where each person represents one company
• One user, many tenants — needed when consultants or accountants log into multiple client tenants
• One user, many tenants with active selection — the user has multiple memberships and switches between them (Slack-style workspace switcher)

For most UK B2B SaaS, pattern 1 covers v1. Build pattern 3 only when you have a real customer asking for it.

The schema

Minimal multi-tenant schema:

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

-- memberships (join table user <-> tenant)
create table public.memberships (
  user_id uuid references auth.users(id) on delete cascade,
  tenant_id uuid references public.tenants(id) on delete cascade,
  role text not null check (role in ('owner','admin','member')),
  created_at timestamptz not null default now(),
  primary key (user_id, tenant_id)
);

-- example domain table
create table public.projects (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null references public.tenants(id) on delete cascade,
  name text not null,
  created_at timestamptz not null default now()
);

-- index on tenant_id for every domain table
create index on public.projects (tenant_id);

The RLS policies

Every domain table gets RLS enabled, then a policy that says "you can only see rows where tenant_id matches the JWT claim":

alter table public.projects enable row level security;

create policy "tenant isolation"
  on public.projects
  for all
  using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid)
  with check (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

The using clause governs reads; the with check clause governs writes. Both must reference the same tenant_id, otherwise users can read from one tenant and write to another — which is the most common RLS bug in production.

The JWT custom claim

Supabase Auth Hooks let you customise the JWT before issuance. Create a Postgres function that adds tenant_id:

create function public.custom_access_token_hook(event jsonb)
returns jsonb
language plpgsql
as $$
declare
  claims jsonb;
  tenant uuid;
begin
  claims := event -> 'claims';

  select tenant_id into tenant
  from public.memberships
  where user_id = (event ->> 'user_id')::uuid
  limit 1;

  if tenant is not null then
    claims := jsonb_set(claims, '{tenant_id}', to_jsonb(tenant::text));
  end if;

  event := jsonb_set(event, '{claims}', claims);
  return event;
end;
$$;

Register that function as the Auth access-token hook in Supabase Dashboard. Every token issued from that point onward carries the user's tenant_id as a custom claim.

Audit logs — write them in the database

Application code can't be trusted to write audit logs reliably. A bug, a forgotten path, or a script that bypasses your service layer means audit gaps. Write audit logs via Postgres triggers so the database itself records every change:

create table public.audit_log (
  id uuid primary key default gen_random_uuid(),
  tenant_id uuid not null,
  user_id uuid,
  table_name text not null,
  operation text not null,
  row_id uuid,
  diff jsonb,
  occurred_at timestamptz not null default now()
);

create function public.audit_trigger()
returns trigger
language plpgsql
as $$
begin
  insert into public.audit_log (tenant_id, user_id, table_name, operation, row_id, diff)
  values (
    coalesce(new.tenant_id, old.tenant_id),
    (auth.jwt() ->> 'sub')::uuid,
    tg_table_name,
    tg_op,
    coalesce(new.id, old.id),
    case
      when tg_op = 'INSERT' then to_jsonb(new)
      when tg_op = 'UPDATE' then jsonb_build_object('before', to_jsonb(old), 'after', to_jsonb(new))
      when tg_op = 'DELETE' then to_jsonb(old)
    end
  );
  return coalesce(new, old);
end;
$$;

create trigger audit_projects
  after insert or update or delete on public.projects
  for each row execute function public.audit_trigger();

Now every change to projects is logged in audit_log automatically. Apply the same trigger to every domain table.

Billing — Stripe + Supabase + RLS

Add a subscriptions table joined to tenants. Stripe webhooks write to it. Your app reads from it.

create table public.subscriptions (
  tenant_id uuid primary key references public.tenants(id) on delete cascade,
  stripe_customer_id text not null,
  stripe_subscription_id text,
  status text not null,
  current_period_end timestamptz,
  plan text not null,
  updated_at timestamptz not null default now()
);

create policy "members can read their tenant subscription"
  on public.subscriptions
  for select
  using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

Stripe webhooks run server-side with the service-role key, bypassing RLS. They update the subscription row when payments succeed, fail or renew. Your app reads subscriptions.status to gate features.

The three failure modes that catch teams

1. Forgetting with check on policies

If you write a policy with only using, users can write rows with the wrong tenant_id. They'll show up to no-one immediately (because their RLS read filters them out), but you'll find them in audit logs months later when a customer reports missing data that's actually been silently inserted into the wrong tenant.

Always: using (...) with check (...) — the same predicate on both.

2. Trusting your app code with the tenant_id

Some teams add tenant_id via app code on every insert. That works until somebody writes a script that bypasses the service layer. The JWT custom claim approach means RLS reads tenant_id from the token directly — no app code involved.

3. Service-role queries that forget tenant scoping

Background jobs, cron tasks and webhooks run with the service-role key, which bypasses RLS. Every such query must include an explicit where tenant_id = ?clause, because nothing else is filtering. The most common production data leak we've audited is a Stripe webhook that updated "the" subscription without specifying which tenant.

What we ship with

Every BozApps multi-tenant SaaS ships with: the schema above, JWT hook configured, RLS on every domain table with using + with check, audit triggers on every domain table, Stripe webhook with explicit tenant scoping, and a unit-test suite that asserts cross-tenant queries return zero rows. Roughly two weeks of engineering for the v1 architecture, then everything builds on top of it cleanly.