Multi-tenant SaaS on Supabase: isolating tenants with Row-Level Security

Hodori is a school attendance platform. Every school that signs up is a tenant, and every tenant must be invisible to every other one, even though they all live in one Supabase project and one Postgres database. This is how that isolation is built, and the one place it leaked.

Rule 1: every tenant table carries organization_id, denormalised

It is tempting to store the organisation only on the top-level entity (the school) and reach it through joins from students, attendance rows, and audit logs. Do not. Every tenant-scoped table in Hodori has its own organization_id UUID NOT NULL column, even when it could be derived. Policies then never need a join, which keeps them fast and, more importantly, keeps them simple enough to read and audit in one line.

Callers never set the column. A BEFORE INSERT trigger fills it from the caller's JWT, so a client bug cannot write a row into the wrong tenant by passing the wrong id.

create or replace function public.set_organization_id_default()
returns trigger language plpgsql as $$
begin
  if new.organization_id is null then
    new.organization_id := public.get_my_org_id();
  end if;
  return new;
end $$;

Rule 2: policies read the organisation from the JWT

Supabase puts the user's metadata in the JWT. A small helper, get_my_org_id(), reads user_metadata.organization_id from the request's claims. Every RLS policy compares the row's organization_id to that value. Because the JWT is signed by Supabase Auth, the client cannot forge it, and because the helper is one function, changing where the org id lives is a one-line change.

create policy "tenant read" on public.students
  for select to authenticated
  using (organization_id = public.get_my_org_id());

Rule 3: writes go through SECURITY DEFINER RPCs

The mobile app talks to Supabase directly with the user's JWT; there is no API server in between. That is fine for reads, but attendance submission has rules: a teacher may only submit for their own class, only before the school's lock time, and the write must produce an audit row. Those rules live in Postgres functions marked SECURITY DEFINER that re-check the caller's role and organisation before touching anything. Web and mobile call the same function, so the rule exists once.

The trap: super-admin bypass

The operator role, super_admin, needs to see every school. An early migration gave it a blanket bypass on every policy, reads and writes. That meant the operator's browser session, holding only the ordinary anon key, could delete any school's students or change the lock time that every attendance RPC enforces. Nothing exploited it, but it was there for months.

The fix, migration 070, narrowed the bypass to reads only. Cross-tenant writes now go through the service-role key on the server or through an RPC that audits itself. If a future feature needs a direct tenant-table write from the operator console, the answer is another audited RPC, not restoring the bypass.

Things that made this maintainable

• Migrations are applied by hand and numbered; an RPC's current definition can be spread across several files, so a backend reference doc lists the latest signature of each.
• A restore drill anchored on the oldest tenant proves backups actually restore.
• A prebuilt knowledge graph of both apps answers 'what calls this RPC' in seconds instead of a grep across two repos.
• Types are synced by hand between dashboard and mobile; they intentionally differ in shape, and the divergence is documented rather than papered over.

When to use this pattern

One database with RLS is right when tenants are many and small, share one schema, and you want one deployment. It is wrong when a tenant needs its own retention policy, region, or performance envelope; then you want a database per tenant. Most SaaS products for schools, clinics, agencies, and shops in the region are the first case.