Accounting firms, bookkeeping and payroll providers and fintech products all need the same thing sooner or later: a portal where each client company signs in and sees its own invoices, statements and documents, while your staff work across the clients they look after. With financial data, “each client sees only their own” can’t rely on every developer remembering a filter.
This tutorial builds the data layer for that portal on Supabase, which is PostgreSQL with sign-in and storage on top. Isolation is enforced by the database itself with row-level security (RLS). We then run it in a working demo portal and show you what two different clients actually see. The broader architecture is covered in how to build a multi-tenant SaaS.
How it fits together
Choosing an isolation model
| Model | Isolation | Cost to run | When we use it |
|---|---|---|---|
| Shared tables + tenant_id + RLS | Strong, enforced by the database | Lowest | Our default for portals with many small and mid-size clients |
| Schema per tenant | Stronger | Medium: every change runs per schema | Rarely; migrations get tedious past a few dozen tenants |
| Database per tenant | Strongest | Highest | A few large clients with strict isolation or data-location rules |
Step 1: Tenants, members and staff
Every client company is a tenant, and every financial record belongs to exactly one tenant. People reach a tenant in one of two ways: as a member of the client company (owner, finance admin, contributor or viewer), or as one of your staff assigned to that client. Keeping the two relationships separate makes the rules much easier to read.
create table public.tenants (
id uuid primary key default gen_random_uuid(),
name text not null,
created_at timestamptz not null default now()
);
-- People at the client company
create table public.memberships (
tenant_id uuid not null references public.tenants on delete cascade,
user_id uuid not null references auth.users on delete cascade,
role text not null check (role in ('owner', 'finance_admin', 'contributor', 'viewer')),
primary key (tenant_id, user_id)
);
-- Your firm's staff, assigned to the clients they look after
create table public.staff_assignments (
tenant_id uuid not null references public.tenants on delete cascade,
user_id uuid not null references auth.users on delete cascade,
primary key (tenant_id, user_id)
);
create table public.invoices (
id uuid primary key default gen_random_uuid(),
tenant_id uuid not null references public.tenants on delete cascade,
invoice_number text not null,
customer_name text not null,
amount numeric(12, 2) not null,
currency char(3) not null,
due_date date not null,
status text not null default 'open'
);
create index on public.invoices (tenant_id, due_date);Step 2: One helper for every policy
Rather than repeating the membership logic in each policy, put it in one function. It runs with security definer so it can read the membership tables even though users can’t, and it pins search_path so it can’t be hijacked. auth.uid() is the signed-in user’s id, taken from their session token by Supabase.
create or replace function public.can_access(t uuid, roles text[] default null)
returns boolean
language sql stable security definer
set search_path = public
as $$
select exists (
select 1 from public.memberships m
where m.tenant_id = t and m.user_id = auth.uid()
and (roles is null or m.role = any (roles))
)
or (roles is null and exists (
select 1 from public.staff_assignments s
where s.tenant_id = t and s.user_id = auth.uid()
));
$$;
revoke all on function public.can_access(uuid, text[]) from public;
grant execute on function public.can_access(uuid, text[]) to authenticated;Step 3: Row-level security policies
Now switch RLS on and describe who may do what. Anyone with access to a tenant can read its invoices; only the client’s owner or finance admin can create or change them. Because the rules live in the database, they apply to the app, to reports, to the API and to whatever a developer writes next year.
alter table public.invoices enable row level security;
alter table public.memberships enable row level security;
alter table public.staff_assignments enable row level security;
create policy "read own tenant's invoices" on public.invoices
for select to authenticated
using (public.can_access(tenant_id));
create policy "finance roles add invoices" on public.invoices
for insert to authenticated
with check (public.can_access(tenant_id, array['owner', 'finance_admin']));
create policy "finance roles edit invoices" on public.invoices
for update to authenticated
using (public.can_access(tenant_id, array['owner', 'finance_admin']))
with check (public.can_access(tenant_id, array['owner', 'finance_admin']));
-- people can see their own memberships (to build the tenant switcher)
create policy "see my memberships" on public.memberships
for select to authenticated using (user_id = auth.uid());alter table public.tenants enable row level security;
create policy "see my tenants" on public.tenants
for select to authenticated
using (public.can_access(id));Step 4: Documents in storage
Uploaded statements and receipts go into a private bucket, with each file stored under a folder named after its tenant id, for example 3f2a…/2026/q3-statement.pdf. Supabase’s storage.foldername() returns the folder parts of a path, so the first folder tells the policy which tenant the file belongs to.
create policy "read own tenant's documents" on storage.objects
for select to authenticated
using (
bucket_id = 'documents'
and public.can_access(((storage.foldername(name))[1])::uuid)
);
create policy "finance roles upload documents" on storage.objects
for insert to authenticated
with check (
bucket_id = 'documents'
and public.can_access(((storage.foldername(name))[1])::uuid,
array['owner', 'finance_admin', 'contributor'])
);Step 5: An audit trail nobody can edit
Every change to a financial record should leave a trace: who, when, and what the row looked like before and after. A trigger writes to an audit table that users can read (owners only) but never change, because no policy allows it.
create table public.audit_log (
id bigint generated always as identity primary key,
tenant_id uuid not null,
table_name text not null,
action text not null,
row_id uuid not null,
actor uuid,
old_row jsonb,
new_row jsonb,
at timestamptz not null default now()
);
alter table public.audit_log enable row level security;
create policy "staff and owners read audit" on public.audit_log
for select to authenticated using (public.can_access(tenant_id, array['owner']));
create or replace function public.audit_change() returns trigger
language plpgsql security definer set search_path = public as $$
begin
insert into public.audit_log (tenant_id, table_name, action, row_id, actor, old_row, new_row)
values (coalesce(new.tenant_id, old.tenant_id), tg_table_name, tg_op,
coalesce(new.id, old.id), auth.uid(),
case when tg_op <> 'INSERT' then to_jsonb(old) end,
case when tg_op <> 'DELETE' then to_jsonb(new) end);
return coalesce(new, old);
end $$;
create trigger invoices_audit after insert or update or delete on public.invoices
for each row execute function public.audit_change();Step 6: What two clients actually see
We loaded three client companies into the schema above and built a small portal on top, where every query runs as the signed-in user. Here is the same page, at the same moment, for Acme Ltd’s owner and for Northwind Outdoor’s viewer:
Northwind’s viewer then tries to add an invoice. The app doesn’t check anything; the database refuses the row:
And your accountant, assigned to Acme and Northwind but not to the third client, Bolt Coffee Co, sees exactly those two:
Step 7: Prove it with an isolation test
Screenshots convince people; tests protect you. In Supabase you can impersonate a user inside a transaction by switching to the authenticated role and setting the session claims, which is exactly what auth.uid() reads. This script creates two clients and checks that one can’t see or write the other’s data.
begin;
-- two clients, one member each (ids are fixed for readability)
insert into auth.users (id) values
('00000000-0000-0000-0000-00000000000a'), ('00000000-0000-0000-0000-00000000000b');
insert into public.tenants (id, name) values
('10000000-0000-0000-0000-00000000000a', 'Acme Ltd'),
('10000000-0000-0000-0000-00000000000b', 'Northwind');
insert into public.memberships values
('10000000-0000-0000-0000-00000000000a', '00000000-0000-0000-0000-00000000000a', 'owner'),
('10000000-0000-0000-0000-00000000000b', '00000000-0000-0000-0000-00000000000b', 'viewer');
insert into public.invoices (tenant_id, invoice_number, customer_name, amount, currency, due_date) values
('10000000-0000-0000-0000-00000000000a', 'A-1', 'Acme customer', 500, 'USD', current_date),
('10000000-0000-0000-0000-00000000000b', 'B-1', 'Northwind customer', 900, 'GBP', current_date);
-- act as Acme's owner
set local role authenticated;
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-00000000000a"}';
select invoice_number from public.invoices; -- expect only A-1
-- Northwind's viewer may not add invoices, even to their own company
set local request.jwt.claims = '{"sub": "00000000-0000-0000-0000-00000000000b"}';
insert into public.invoices (tenant_id, invoice_number, customer_name, amount, currency, due_date)
values ('10000000-0000-0000-0000-00000000000b', 'B-2', 'x', 1, 'GBP', current_date);
-- expect: new row violates row-level security policy
rollback;
Connecting each client’s accounting system
Most portals pull figures from each client’s own accounting system rather than asking anyone to retype them. Each tenant connects its own Xero organization through OAuth, and the refresh token belongs to that tenant alone. Store it encrypted (Supabase Vault does this inside the database), never send it to the browser, and run the sync from a server-side job that sets the tenant explicitly on every write.
Show the time of the last successful sync on every screen, and flag data that has gone stale. Tokens expire and passwords change; a portal that quietly shows last month’s balance does more harm than one that says it couldn’t reach Xero since Tuesday.
What the app layer still owns
- Two-factor sign-in for everyone, and single sign-on for larger clients who ask for it.
- A clear tenant switcher for people who belong to more than one client.
- An extra confirmation before anyone changes bank details, a common route for invoice fraud.
- AI features, such as reading uploaded receipts like our invoice automation, running with the same tenant scope as the user who triggered them.
What shapes the cost
| Factor | Why it matters |
|---|---|
| User types | Client members only is simpler than members, staff, auditors and external advisers. |
| Accounting integrations | Each system (Xero and others) adds an OAuth flow, a sync job and error handling. |
| Documents and e-signature | Versioned uploads and signatures add features most portals end up needing. |
| Security requirements | SSO, strict reviews or data-location rules add time. |
| Migration from email | Bringing existing clients and documents in, in stages. |
Mistakes we’d avoid
Sources
Questions we often hear
Why enforce access in the database instead of the app?
Because every path to the data, the app, the API, reports and future features, goes through the database. A missed filter in code can’t leak another client’s records when the database itself refuses.
Can our staff see several clients?
Yes. Staff assignments grant access per client, so each accountant sees the companies they look after and partners can be assigned to all of them.
Does this work without Supabase?
Yes. The same tables and policies work in any PostgreSQL; you replace auth.uid() with a session setting your app sets on each request.
