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.

Request flow: the browser sends the publishable key and the user's JWT to the Supabase Data API; Postgres runs the query as the authenticated role; the RLS policy checks the user's role and calls can_access_region(), which looks up user_regions, super_admins and user_active_region; only permitted rows come back.

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_region(region_id)) with check ( (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_region(region_id)); 
  • USING filters the rows you can see or target. A row that fails it is skipped without an error.
  • WITH CHECK tests the row as it will look after an insert or update. A row that fails it raises an error and aborts the whole command (PostgreSQL: CREATE POLICY).
  • TO authenticated keeps anonymous visitors out of these policies entirely.

Authorisation never comes from a value the client sends. The region comes from auth.uid(), the user ID inside the JWT that Supabase has already verified, looked up against user_regions on the server.

Child tables follow their parent through a second SECURITY DEFINER helper, region_of_work_order(id), so an attachment is visible only if its work order's region is.

What I'd change (recommended, not yet done):

  • Move the helpers out of public. Supabase says never to create SECURITY DEFINER functions in an exposed schema, because they become callable through the Data API (Supabase: Row Level Security). Mine only answer yes/no about the caller's own access, but they belong in a private schema.
  • Pin search_path to ''. They use search_path = public; PostgreSQL's guidance is to exclude any schema untrusted users can write to (PostgreSQL: CREATE FUNCTION).
  • Performance. Supabase recommends indexing policy columns and wrapping auth.uid() as (select auth.uid()) (Supabase: RLS performance). region_id is indexed on all 24 tables. I haven't benchmarked the policies; at this size there was nothing to measure.

The gap RLS can't close: foreign keys

PostgreSQL is explicit: "Referential integrity checks, such as unique or primary key constraints and foreign key references, always bypass row security" (PostgreSQL: Row Security Policies).

In practice, if invoices.work_order_id is a plain foreign key, a user in region A who knows the ID of a region B work order can create an invoice in region A that points at it. RLS checks the invoice's own region_id, which is A, so the insert passes. The foreign key check only asks "does this work order exist?", without RLS.

Status in ZCS: child tables inherit their parent's region through policy joins, but the foreign keys between region-owned tables are still plain. Same-region composite foreign keys were deferred during the migration. UUIDs make guessing impractical, but IDs leak through URLs and exports, so I treat this as an open gap. I haven't tested it on the real app.

The fix is to make the foreign key carry the region:

Simplified example (companion demo)

alter table public.work_orders add constraint work_orders_region_id_id_key unique (region_id, id); alter table public.invoices add constraint invoices_work_order_same_region_fkey foreign key (region_id, work_order_id) references public.work_orders (region_id, id) on update cascade; -- invoices follow when a super-admin moves the work order 

In the companion demo (plain PostgreSQL 16 with a Supabase-style mock, not a real Supabase stack), swapping this back to a plain foreign key made the isolation test fail on exactly this case.

Attack scenarios

Attack scenarios and their status in ZCS

What could go wrong What ZCS does Status
Client sends another region's region_id Trigger overwrites it on insert Implemented; phase 5 checks confirmed inserts are stamped with the caller's region
Fetch another region's row by ID USING filters it out Implemented; checked indirectly by the region-removal test below
Move a row to another region Trigger rejects unless super-admin; WITH CHECK also applies Implemented; a region change by a user who isn't a super-admin was rejected in phase 5 checks
Cross-region link through a foreign key Plain foreign keys between region-owned tables Open gap; not tested on the real app
A table left out of the rollout user_roles was missed (see lessons) Found and fixed 12 Sep 2026
Storage The inventory-documents bucket allows any signed-in user Not region-scoped
Realtime postgres_changes checks RLS per subscriber, but Supabase says DELETE events aren't RLS-filtered (see Sources) Known limitation; not tested
Views and RPC The one view uses security_invoker = on; region-changing functions check super-admin status inside Implemented
Setting app.region_override from a client Only set inside server-side functions; the Data API doesn't run arbitrary SQL Reasoned, not tested
service_role key in the browser Only in two Edge Functions; the browser client has the publishable key only Verified by reading the code

Testing: what I did and what's still to do

Done, before the switch: on a local Supabase stack and on the migrated copy, I worked through every table, form and field by hand, including a newly created region. I didn't keep a written checklist of those runs, so I'm not listing individual results.

Done, on the migrated project (now the live one):

  • Row counts against the baseline after every phase, as listed above.
  • A structural check that every region-owned table has region_id NOT NULL, an index on it, and RLS enabled. I proved the check could fail by running it against a table that should fail.
  • The rolled-back dry run before dropping the old policies, then the same counts after applying it.
  • The region-removal test. An admin who isn't a super-admin, assigned to Islamabad, saw all of the roughly 160 work orders (at that point every work order was Islamabad's). With their region assignment removed inside a rolled-back transaction, the same admin saw 0. The role stayed the same; only the region changed, so the region check controls access.

Not done yet: a two-region test where an Islamabad user and a Jhelum user each try to read, insert, update, delete, move and cross-link the other's data. That's the next test I'd add:

  1. Run it on a local stack or a Supabase branch, never production.
  2. Act as real users (the authenticated role plus JWT claims, or real sign-ins). Running as postgres or with the service_role key bypasses RLS and proves nothing.
  3. Count rows: assert "0 rows affected", not just "no exception".
  4. Check each user still sees their own data; a policy that hides everything passes every isolation check.
  5. Cover anonymous access, joins, views, RPC functions and the foreign key case.

The companion demo does this on a simplified four-table schema: eight checks, including a super-admin moving a work order and its invoice following along. All eight pass. I also planted one leak per run to show the test can fail. A plain foreign key, an extra using (true) policy, a region helper that always says yes, and an anon grant were each caught. Making the stamping trigger a no-op did not fail the test, because WITH CHECK blocked the cross-region writes on its own. That's a useful result in itself: the trigger keeps data correctly filed; RLS is the security boundary.

Lessons

  1. Counts beat clicking around. Comparing row counts caught the location-less inventory rows, the extra admin and the missing city mapping. None of them would have shown up as an error.
  2. Add first, then tighten. Running new policies alongside the old ones made the risky step a single, reversible migration with a dry run.
  3. The table you forget is the hole. Phase 6 covered 24 region-owned tables and their children, but not user_roles. Until I fixed it on 12 September, any admin in any region could read and change any role row, including the owner's. The fix added region scoping and made super-admin rows untouchable by anyone else.
  4. Backstops have to actually stop. I expected NOT NULL to catch records created in "All regions" mode, where there's no single region to inherit. It didn't, because the default falls back to the head region, so a few create buttons the UI guard missed filed assets and suppliers into Islamabad without telling anyone. I've since routed every create button through the guard; a live walk-through in All mode is still on my list. Silent fallbacks need a visible marker; work orders that land in the head region by fallback now carry a "Default" badge.
  5. Authentication isn't authorisation. Being signed in got you into /admin. Only the policies decide what you see once you're there.

Checklist

  • Baseline row counts captured, unfiltered, before any change
  • region_id added nullable, backfilled with an idempotent update, then made NOT NULL with an index and a foreign key
  • Region stamped server-side; client-supplied values ignored; changes allowed only for trusted roles
  • New policies added alongside the old ones, then the old ones dropped after a rolled-back dry run
  • Every table checked, including role, audit and join tables
  • Foreign keys between region-owned tables include region_id
  • Helper functions are SECURITY DEFINER, in a non-exposed schema, with a pinned search_path
  • Views use security_invoker; Storage buckets and Realtime reviewed
  • service_role key on the server only
  • Isolation test as real users, counting rows, run before every schema change

Conclusion

Adding branches to a live Supabase app came down to a small number of database rules, applied in an order where each step could be checked and undone. The SQL was the easy part. The practical takeaway: write down your row counts before you start, and treat any count that changes unexpectedly as a stop sign, because under RLS a mistake doesn't throw an error. It just shows less data.

FAQ

Is the Supabase publishable (anon) key safe to put in the browser?
Yes, it's designed to be public, but only because Row Level Security decides what it can reach. With RLS off or a policy missing, anyone holding the key can read that table through the Data API. The secret (service_role) key bypasses RLS and must stay on the server.

Is region or branch isolation the same as multi-tenancy?
No. Multi-tenancy separates different organisations that must never see each other's data. Branch isolation separates parts of one company, often with super-users who see everything and some shared data. Both use the same tools: an ID column, a membership table and RLS policies.

Do foreign keys bypass RLS in Supabase?
Yes. PostgreSQL runs foreign key checks without applying row security, so a row can reference another branch's row if its ID is known. Including the branch column in the foreign key, (region_id, parent_id) → parent(region_id, id), keeps references inside one branch.

How do I test Supabase RLS policies?
Act as a real user by switching to the authenticated role with that user's JWT claims, never as postgres or service_role. Try to read, insert, update, delete and link another branch's rows, and assert on row counts, because RLS hides rows without raising errors. Also confirm each user still sees their own data.

Can I add a region_id column to a live Supabase table safely?
Adding a nullable column with no default only changes PostgreSQL's catalogue and doesn't rewrite the table, though it briefly takes an exclusive lock. Backfill existing rows next, then add NOT NULL, the foreign key and an index, which all validate the data and fail if anything was missed. Rehearse on a copy first and compare row counts before and after.

Code: the foreign key example comes from a simplified companion demo (four tables, not the ZCS schema), run on plain PostgreSQL with a Supabase-style mock.

AI note: I built ZCS with AI coding agents, mainly Claude Code, alongside Cursor and Antigravity. I made the design decisions, ran each migration and checked it with the counts described here.

Sources

This post first appeared on my site: Branch isolation in a live Supabase app.