Branch isolation in a live Supabase app
DEV Community

Branch isolation in a live Supabase app

Short answer: to keep branches apart in one Supabase database, give every branch-owned table a region_id, fill it in for existing rows, make it NOT NULL, stamp it on the server, and let Row Level Security decide which rows each signed-in user gets back.

Filtering in React isn't enough, because the browser holds a public key and can ask the database for anything. Check the result with row counts, because RLS hides rows without raising an error.

I built Zarrar Cooling Services (ZCS) on my own: a React and Supabase platform for an appliance repair and cooling business, with a public booking site, an admin panel, a technician panel, and branch views. Version 1 served one branch, Islamabad (which also covers Rawalpindi). Then the business opened a branch in Jhelum.

The requirement sounded simple. Staff in one branch must not see another branch's jobs, payroll, invoices, or stock. The owner must see everything. And the app was live, with real bookings coming in, so I couldn't rebuild it from an empty database.

Most Supabase guides start from an empty database. This one starts from 24 tables of real data: the model I chose, the migration in the order I ran it, the policies, and what I have and haven't tested.

Why the database has to enforce it

Three terms first: Row Level Security (RLS): a PostgreSQL feature that attaches rules (policies) to a table, so every query only sees and changes the rows the rules allow. Publishable (anon) key: the key the React app ships to every browser. Supabase's docs describe publishable keys as safe to expose because they "only reach what Row Level Security allows" (Supabase: API keys). Secret (service_role) key: a server-only key that "bypasses every Row Level Security policy you have" and must never appear in a browser (same source).

So anyone can copy the publishable key from DevTools and query the Data API directly. A region dropdown in React is a convenience; only a policy in Postgres keeps Jhelum's payroll away from an Islamabad technician. In ZCS the /admin routes only checked that you were signed in, so RLS was already the real authorisation layer.

This isn't a theoretical risk. UpGuard found 16,326 Supabase databases with publicly readable tables among about 300,000 domains showing signs of Supabase use, and more than half had indicators of personal data (UpGuard, "Everything, everywhere: systemic data exposure in Supabase apps"; the page is dated 14 Oct 2026, and BleepingComputer covered it on 28 Sep 2026). BleepingComputer summarises the causes as "missing or ineffective row-level security policies and misuse of public keys." OWASP ranks broken access control first in its 2025 Top 10 and says access control "is only effective when implemented in trusted server-side code" (OWASP A01:2025).

Region isolation is not multi-tenancy, but it uses the same tools

A tenant is a separate customer organisation sharing your infrastructure. Tenants must never see each other's data, and nobody on the tenant side sees across tenants. ZCS isn't that. It's one company with several branches, which I'll call regions because that's the name in the schema.

Multi-tenant SaaS compared with ZCS regions Aspect Multi-tenant SaaS ZCS regions Who owns the data Different organisations One company Can anyone see across the boundary? Normally no customer user can Super-admins see every region by design Shared data Rare Customer records and the services catalogue are shared; each branch only sees customers it has worked for Cross-boundary reporting Usually forbidden The owner wants it

What carries over is the mechanism: an ID column on every owned row, a table linking users to the IDs they may access, a SECURITY DEFINER helper for that lookup, and policies that use it. For real multi-tenancy the migration steps below still apply, but the trust model usually needs to be stricter, for example no cross-tenant super-users inside the app.

Choosing the model

Before writing any SQL, I compared five options:

The five options I compared Option What isolates the data Why I chose or rejected it region_id + RLS (chosen) A column and policies in one database Adding a branch is an INSERT. Existing hooks keep working, because RLS filters what they already ask for. One generated types file. Cross-region reports are a GROUP BY. Schema per region Separate Postgres schemas Every new branch re-runs the DDL for ~50 tables plus functions and policies. Schemas drift, and the client would have to pick a schema on every request. Project per region Separate Supabase projects Cost and migrations multiply per branch, and cross-region reporting inside the app becomes impossible. Hybrid Some tables split, some shared The ops cost of both approaches. Client-side filter Nothing Not isolation at all.

The deciding argument was "existing hooks keep working": my plan estimated that about 14 pages would need no code changes, because the same select simply returns fewer rows.

The model in the database

Regions are data, not an enum, so a new branch never needs a migration:

  • regions: code, name, tax rate, contact details. A partial unique index makes sure exactly one region has is_default = true; that's Islamabad, the head region.
  • region_cities: a region covers many cities (Islamabad and Rawalpindi are one region). The public booking form uses it to work out the region from the city.
  • user_regions: which region each staff user works in.
  • super_admins: the people who see every region.
  • user_active_region: a super-admin's current choice in the region switcher; NULL means "All regions".
  • 24 tables are region-owned and get a region_id: work orders, invoices, expenses, staff, inventory, suppliers, purchase orders, assets, the payroll tables, notes, reports, activity logs, and invoice settings. Child tables such as attachments and invoice items take the region of their parent. Shared tables (services, system settings) stay global. Customers are a special case: the table has no region_id, and any branch can add to it, but a policy only shows a customer to a branch that has a work order for them.

One function answers "may the current user touch this region?":

Real ZCS helper (migration 20260906101730)

create or replace function public.can_access_region(_region_id uuid)
returns boolean
language sql stable security definer set search_path = public as $$
select case when exists (select 1 from public.super_admins where user_id = auth.uid()) then coalesce ((select region_id from public.user_active_region where user_id = auth.uid()) = _region_id, true) -- no active region = "All regions" else exists (select 1 from public.user_regions where user_id = auth.uid() and region_id = _region_id) end ;
$$ ;

It's SECURITY DEFINER (it runs with its owner's rights) so it can read user_regions and super_admins without triggering their own RLS. A policy that queries another RLS-protected table can recurse into itself. ZCS had already been bitten by that on user_roles and fixed it with the same pattern, which is the standard answer in the Supabase community.

Figure: a request carries the user's JWT; Postgres runs the policy, which asks the helper whether this user may access the row's region. For super-admins, the region switcher narrows what the database returns, because the helper reads user_active_region. That's a scope they choose, not a security boundary. For everyone else, it's only a badge.

Migrating the live app

Working on a copy, then switching I never migrated the database the website was using. I made a copy of it as a new Supabase project and ran every migration below on that copy. I tested the updated app against it on my computer, and ran the same changes on a local Supabase stack. When everything passed, I put the site into maintenance mode during off-hours, so nothing new was being written. I copied across the data the live database had gained since the copy was made, then changed the production environment variables so the website points to the new project. The original database is untouched and kept as a backup. The site was in maintenance mode during the switch, and I didn't time it, so I'm not claiming zero downtime.

A baseline before touching anything The first step wasn't SQL that changed anything. I counted every row in the 24 region-owned tables as the postgres role, which bypasses RLS: a little under 300 rows in total, plus the customers and auth users. It's a small business; the method matters more than the size. Every later phase had to leave those numbers unchanged. Under RLS, a denied read returns an empty result with no error, so a broken policy looks exactly like an empty table, and "the page loads" proves nothing.

The phases All of this ran on 6 September 2026. Each phase was its own migration file, created with supabase migration new, applied through the SQL editor, and written with a ROLLBACK block in its header.

The migration phases, all run on 6 September 2026 Phase Change Why it was safe Checked 1 Region tables, helpers, Islamabad seeded Nothing existing touched Total unchanged 2 region_id uuid nullable, no default, on 24 tables Adds a column only; v1 code never mentions it Total unchanged 3 Backfill every row to Islamabad Writes one column, guarded by where region_id is null 0 nulls, total unchanged 4 Every auth user gets an Islamabad user_regions row New table only; fails loudly if a user is missed Every user covered 5 Default, FK, NOT NULL, index, stamping trigger NOT NULL fails if phase 3 missed a row 24 indexes and triggers 6a-6d New region-aware policies next to the old ones Permissive policies combine with OR, so access is unchanged Counts unchanged per group Tighten Drop the old policies Rolled-back dry run first Same counts afterwards

Adding a nullable column with no default doesn't rewrite the table in PostgreSQL (PostgreSQL: ALTER TABLE). It still takes a short exclusive lock, which is harmless at this size and something to plan for on large tables. The backfill put every row in Islamabad, even jobs that were really Jhelum work; those moved later, one at a time, through a "Move to region" action. A uniform backfill kept every check to one question: is the total still the same?

Phase 3, abridged (migration 20260906120956).

do $$ declare isb uuid ; begin select id into isb from public.regions where code = 'ISB' ; if isb is null then raise exception 'run phase 1 first' ; end if ; update public.work_orders set region_id = isb where region_id is null ; update public.invoices set region_id = isb where region_id is null ; -- … the same for the other 22 tables end $$ ;

Phase 5 is where the column became enforced. The trigger matters as much as the constraint:

Phase 5, abridged (migration 20260906122629)

create or replace function public.enforce_region_id() returns trigger language plpgsql set search_path = public as $$ declare override_txt text := current_setting ('app.region_override' , true) ; begin if tg_op = 'INSERT' then if override_txt is not null and override_txt <> '' then new.region_id := override_txt :: uuid ; -- trusted server path else new.region_id := public.default_region_for_user () ; -- ignore what the client sent end if ; return new ; end if ; if new.region_id is distinct from old.region_id and not public.is_super_admin () then raise exception 'region_id may only be changed by a super-admin' ; end if ; return new ; end $$ ;

The browser never chooses a region: on insert the trigger overwrites whatever the client sent, and only trusted server-side functions (public booking, region provisioning) pass a region through a transaction-local setting. On update, region_id is immutable for everyone except super-admins.

Additive policies, then one tightening step

The riskiest part was the policies, so I did them in two moves. In phases 6a-6d, I added a new _rgn_ policy for every operation on every table, written in its final form: the existing role condition AND can_access_region(region_id). Because Postgres combines permissive policies with OR (PostgreSQL: Row Security Policies), the old policies still granted the same access, so nothing changed for users. Work orders went last, because every page touches them.

Then a single migration dropped the old policies, keeping a short list of deliberate exceptions: the public booking insert, staff reading their own record, and the personal notifications inbox. A count check in the step before had already flagged a real problem: every inventory transaction had no location, so none of them had a region, and the new policy would have hidden all of them. So the tightening migration first made that policy accept location-less rows, then dropped the old ones. Before applying it, I ran it inside a transaction as the owner and rolled it back. The dry run showed the expected counts, and I applied it for real.

After the migration

Opening Jhelum on 7-8 September turned up three more issues. The first two came from the row-count check, not from clicking around: Islamabad showed one row more than the baseline. A per-table diff traced the extra one to a test admin account I had created after the baseline, so I updated the baseline instead of "fixing" real data. The new region had no city mapping, so bookings from Jhelum couldn't have found it. default_region_for_user() ignored a super-admin's active region, so anything I created while switched to Jhelum would have been stamped Islamabad. I fixed it and checked it inside a rolled-back transaction.

The policies I implemented

Every region-owned table follows one pattern. This is the work orders UPDATE policy, slightly simplified:

work orders update policy (slightly simplified)

create policy work_orders_rgn_update on public.work_orders for update to authenticated using ( ( assigned_to in ( select id from public.staff_members where auth_user_id = auth.uid () ) or public.has_role ( auth.uid () , 'admin' ) or public.has_role ( auth.uid () , 'manager' ) ) and public.can_access_regi
Read on DEV Community ↗ ← Back to News

Comments

No comments yet. Start the discussion.