Supabase Row Level Security: The Multi-Tenant Pattern We Use Across FH Clients

Every FH client site shares the same Supabase project. BHR, CabCarpentry, Tivey, James Marina, Best Barns, fh-site itself — one Postgres, one Storage layer, one Auth instance. The isolation is enforced by Row Level Security policies that scope every read and write to a `site_id`. The service-role key never leaves a server context. The setup costs one project subscription instead of ten, the migration story is one place instead of ten, and the multi-tenant boundary is enforced at the database layer where it can’t be bypassed by a leaky API.

This is the posture we’ve been running for 18 months across the client book. Here’s how it actually works.

#Why one project, not one per client

Cost: Supabase pricing is per-project. Ten clients = ten subscriptions = $300+/month for projects that mostly sit idle. One shared project on the $25/month Pro tier covers all of them comfortably for the storage volume and request rate we run.

Operational simplicity: one place to manage migrations, one place to monitor logs, one place to set up backups. When we ship a schema change, it lands once and applies to every tenant at the same time.

Trade-off: a Supabase outage takes every client down. We mitigate with Cloudflare caching in front of every read endpoint so static content keeps serving even if Supabase is unavailable, and we run weekly backup verification.

#The schema: site_id on every row

Every tenant-scoped table has a `site_id text not null` column. The value is a slug like `"bhr"`, `"cab"`, `"fh"`. There’s a `sites` table with the canonical list. Foreign keys enforce that every row points at a real site.

create table sites (
  id text primary key,
  name text not null,
  created_at timestamptz default now()
);

create table submissions (
  id uuid primary key default gen_random_uuid(),
  site_id text references sites(id) not null,
  name text not null,
  email text not null,
  phone text,
  message text,
  created_at timestamptz default now()
);

create index on submissions(site_id, created_at desc);

#The RLS policies: scope by site_id, reject everything else

Every tenant-scoped table has RLS enabled. The policies allow reads and writes only when the caller’s JWT contains a `site_id` claim matching the row. The default is deny. There is no anon-read policy on tenant tables — anon access to client data is impossible from the client SDK.

alter table submissions enable row level security;

create policy "insert own site" on submissions
  for insert with check (site_id = (auth.jwt() ->> 'site_id'));

create policy "select own site" on submissions
  for select using (site_id = (auth.jwt() ->> 'site_id'));

#Where the site_id comes from

Two paths. For authenticated users, the `site_id` is injected into the JWT at sign-in by a Supabase auth hook. For anonymous users (a public lead form), we use the service-role key on the server side — the role bypasses RLS but our server code explicitly sets `site_id` from the per-tenant environment variable. The client never sees the service-role key.

#Storage buckets: one per tenant

Tenant data goes through RLS-scoped tables. Tenant media goes through bucket-per-tenant Storage. Each client has a public bucket (`fh-images`, `cab-images`, `tivey-images`, `bhr-images`) for marketing imagery, and a private bucket for anything sensitive. Public buckets are read-only-to-anon, write-only-to-service-role. Private buckets have RLS policies that scope to the user’s `site_id` claim.

The `imageUrl()` helper resolves bucket paths into public URLs. Swapping buckets later is a single-file change.

#Migration workflow

We use the Supabase CLI for migrations, version-controlled in the org-defaults repo. Each migration is a numbered SQL file under `supabase/migrations/`. Local development runs against a local Supabase stack via `supabase start`; migrations apply locally first, get tested in staging, then ship to prod with `supabase db push`.

# Create a new migration
supabase migration new add_lead_score_column

# Edit supabase/migrations/202605061200_add_lead_score_column.sql
# Apply locally
supabase db reset

# Apply to prod
supabase db push --linked

#The four migration rules we live by

Migrations are forward-only. We do not write down-migrations. If a migration is wrong, we ship a new migration that fixes it.
Never run a migration that takes more than 30s on a populated table without a maintenance window. ALTER TABLE … ADD COLUMN with a default value on a 5M-row table will lock the table.
Always test on a clone of production data. `supabase db dump` then restore locally before migrating in prod. We caught a 10-minute LOCK on a James Marina migration this way.
Migrations and code changes ship together. A migration that adds a column gets the code that uses it in the same PR.

#Auth: per-tenant claims

Supabase Auth uses GoTrue with custom JWT claims. We inject `site_id` into the JWT via an auth hook that runs server-side on sign-in. The hook reads the user’s row in a `user_sites` join table and adds the relevant `site_id` to the claims. Every downstream RLS check uses `auth.jwt() ->> 'site_id'`.

For anon users (public site visitors filling out a form), there’s no JWT and the public RLS policies are deliberately empty. Public form submissions go through a server action using the service-role key, which sets `site_id` from the per-tenant env var. Never trust the client to provide its own site_id.

#Backups and disaster recovery

Supabase Pro includes daily point-in-time recovery. We supplement with weekly `pg_dump` snapshots stored in a separate S3 bucket owned by FH (not Supabase) — the “three places, two formats, one offsite” rule. If Supabase ever fully fails, we can rehydrate the database into a fresh project from those snapshots.

#Monitoring and alerting

Supabase exposes query performance and connection metrics in the dashboard. We also pipe slow-query logs to Better Stack for alerting on anything over 1s. The Postgres-level monitoring catches things the application monitoring doesn’t — a missing index, a runaway sequential scan, a connection pool exhaustion.

#When NOT to use shared multi-tenancy

Regulated data: HIPAA, PCI, anything with strict data isolation requirements. Even with RLS, the regulator may insist on physical isolation. Spin up a separate project.
Tenants with wildly different load. One client doing 10k RPS will affect the database for everyone else. Pull the high-volume tenant onto their own project.
Customer-controlled schemas. If the tenant gets to define columns, the shared-schema approach breaks down. Use a different per-tenant strategy (schema-per-tenant or separate projects).

#How this maps to the FH client roster

Every site we ship writes to the shared Supabase. Lead submissions land in `fh.submissions` partitioned by `site_id`. Client team members access their site’s data through an admin UI that scopes by JWT claims. Storage buckets resolve through the shared `imageUrl()` helper. The whole thing costs $25/month plus storage overage. If you’re running multi-tenant SaaS on per-tenant databases or trying to reinvent this pattern on raw Postgres, book a consultation — there’s usually a simpler version of what you’re building.