Branch isolation in a live Supabase app

I built Zarrar Cooling Services (opens in a new tab) (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 (opens in a new tab)).
• 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" (opens in a new tab); the page is dated 14 Oct 2026, and BleepingComputer covered it on 28 Sep 2026 (opens in a new tab)). 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 (opens in a new tab)).

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.

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 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?":

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 (opens in a new tab).

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 at 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.

Adding a nullable column with no default doesn't rewrite the table in PostgreSQL (PostgreSQL: ALTER TABLE (opens in a new tab)). 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?

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:

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 (opens in a new tab)), 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:

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 (opens in a new tab)).
• 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 (opens in a new tab)). 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 (opens in a new tab)).
• Performance. Supabase recommends indexing policy columns and wrapping auth.uid() as (select auth.uid()) (Supabase: RLS performance (opens in a new tab)). 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 (opens in a new tab)).

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:

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

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

• UpGuard, Everything, everywhere: systemic data exposure in Supabase apps (opens in a new tab) (page dated 14 Oct 2026; checked 4 Nov 2026)
• BleepingComputer, Misconfigured Supabase apps expose data in over 16,000 databases (opens in a new tab) (28 Sep 2026; checked 4 Nov 2026)
• OWASP, A01:2025 Broken Access Control (opens in a new tab)
• Supabase, API keys (opens in a new tab)
• Supabase, Row Level Security (opens in a new tab)
• Supabase, RLS performance and best practices (opens in a new tab)
• Supabase, Postgres Changes (Realtime) (opens in a new tab)
• Supabase, Storage buckets: fundamentals (opens in a new tab)
• Supabase community, RLS recursion and security definer functions, discussion #4509 (opens in a new tab)
• PostgreSQL, Row Security Policies (opens in a new tab)
• PostgreSQL, CREATE POLICY (opens in a new tab)
• PostgreSQL, ALTER TABLE (opens in a new tab)
• PostgreSQL, CREATE FUNCTION: writing SECURITY DEFINER functions safely (opens in a new tab)
• PostgreSQL, CREATE VIEW: security_invoker (opens in a new tab)