Multi-Tenant SaaS with Row-Level Security on Supabase

Every multi-tenant application is one missing WHERE clause away from a breach.

You build a SaaS. Twenty workshops sign up. They all share a database, because twenty databases is twenty migrations, twenty backups, and twenty things to forget. Workshop A must never see Workshop B's customers, invoices, or revenue. Not "shouldn't in normal operation" — must not, ever, including the day a junior dev ships an endpoint at 11pm.

The default approach puts that guarantee in application code. Every query filters by tenant. Every endpoint checks the session. It works right up until the query somebody wrote in a hurry doesn't have the filter, and by then the data is already in a JSON response.

I built a workshop ERP on Supabase and put the guarantee somewhere the application cannot forget it: in Postgres.

Row-Level Security in one policy

Postgres has had Row-Level Security since 9.5. Turn it on for a table and every query — SELECT, INSERT, UPDATE, DELETE — is filtered by a policy the database evaluates itself.

ALTER TABLE job_cards ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation ON job_cards
  USING (tenant_id = auth_tenant_id());

That is the entire isolation mechanism for that table. The application can issue SELECT * FROM job_cards with no filter at all and get back only its own rows. Not an error — an empty result, or a filtered one. The unsafe query is not rejected, it is made safe.

The resolver function is where the real work is

auth_tenant_id() is doing more than it looks like. Supabase gives you the authenticated user's ID via auth.uid(), but the user is not the tenant — a workshop has an owner and staff, and all of them map to one tenant.

So there is a profiles table linking auth users to tenants, and the resolver looks the tenant up:

CREATE FUNCTION auth_tenant_id()
RETURNS uuid
LANGUAGE sql
STABLE
SECURITY DEFINER
SET search_path = public
AS $$
  SELECT tenant_id FROM profiles WHERE id = auth.uid()
$$;

Three keywords are load-bearing here and each one is a footgun if you get it wrong.

SECURITY DEFINER runs the function as its owner rather than the caller. Without it you have a recursion problem: the policy on job_cards calls a function that reads profiles, whose own RLS policy needs the tenant, which calls the function again. SECURITY DEFINER lets the resolver read profiles directly and breaks the loop.

SET search_path = public is the non-negotiable half of SECURITY DEFINER. A definer-rights function without a pinned search path can be hijacked by a caller who creates a same-named object in a schema earlier in their path. This is a known Postgres privilege-escalation pattern. Pin the path on every SECURITY DEFINER function you ever write.

STABLE tells the planner the result doesn't change within a statement, so it evaluates once per query instead of once per row. On a policy that runs against every row of every table, that is the difference between a fast app and a mystery.

An alternative is reading the tenant straight from a JWT claim, which skips the profiles lookup entirely:

USING (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid)

Faster, and no recursion to reason about. The tradeoff is that the tenant is now baked into a token, so moving a user between tenants means their old token is wrong until it expires. The table lookup is a join; the claim is a cache. Pick based on whether your tenancy ever changes.

Multi-tenancy is not a feature you add later

Migration #1 of 17 created the tenants table. Every table after it carries tenant_id from birth.

This is not discipline for its own sake. Retrofitting tenancy onto a live schema means backfilling a column on every table, writing policies against data that already exists and might violate them, and auditing every existing query for the assumption that the table was global. It is one of the genuinely miserable refactors, and it is completely avoidable by writing one extra column in the first migration.

If there is a chance the system will ever have two customers, it has tenant_id from day one.

Signup as a single transaction

A new workshop signing up needs a tenant row, an owner profile, and — because an empty ERP is useless — a full starting catalogue. Thirty-three services, twenty-three parts, four job templates, priced with GST and HSN codes for the Indian market.

Doing that from the client is four round trips that can each fail independently, leaving a half-created workshop. It runs as a signup trigger instead:

CREATE FUNCTION handle_new_user()
RETURNS trigger
LANGUAGE plpgsql
SECURITY DEFINER
SET search_path = public
AS $$
DECLARE
  new_tenant uuid;
BEGIN
  INSERT INTO tenants (name) VALUES (NEW.raw_user_meta_data ->> 'workshop_name')
    RETURNING id INTO new_tenant;
  INSERT INTO profiles (id, tenant_id, role) VALUES (NEW.id, new_tenant, 'owner');
  PERFORM seed_catalogue(new_tenant);
  RETURN NEW;
END;
$$;

CREATE TRIGGER on_auth_user_created
  AFTER INSERT ON auth.users
  FOR EACH ROW EXECUTE FUNCTION handle_new_user();

One transaction. Either the workshop exists complete with its catalogue, or the signup failed and nothing was written. There is no partial state to clean up, because there is no window in which partial state is visible.

The user-facing effect is that a mechanic signs up and immediately has fifty-eight items to bill against instead of an empty database and a form. That is a retention decision disguised as a database trigger.

Snapshot your line items

This one is not about tenancy, but it is the bug most invoicing systems ship with.

A job card has line items pulled from the service catalogue. The obvious model stores a foreign key to the catalogue row and joins at read time. Then, three months later, the workshop raises the price of an oil change from ₹400 to ₹500 — and every historical invoice silently rewrites itself. Bills that were printed, signed, and paid at ₹400 now display ₹500.

Line items snapshot instead. Price, GST rate, HSN code, and description are all copied onto the line at the moment it is added:

INSERT INTO job_items (job_card_id, ref_id, name, rate, gst_rate, hsn)
SELECT p_job_card_id, s.id, s.name, s.rate, s.gst_rate, s.hsn
  FROM service_types s WHERE s.id = p_service_id;

The catalogue is a template for new lines, not a source of truth for old ones. Financial records are a record of what happened, and what happened does not change because the price list did.

Money is computed in the database

create_invoice(p_job_card_id) is a single Postgres function that totals the lines, applies per-item GST, allocates the next sequential invoice number for that tenant, decrements parts stock, and writes the invoice — in one transaction.

None of that arithmetic happens in Dart. The reason is not performance; it is that invoice numbering must be gapless and sequential per tenant, which means two mechanics hitting "generate bill" at the same moment cannot be allowed to interleave. Inside a transaction with the right locking, that is a solved problem. Split across a mobile client and a network, it is a race with a compliance consequence.

The rule that fell out of it: anything that must be consistent under concurrency belongs in the database. The client is one of several, it is on a phone in a garage, and its network drops.

What it looks like in production

The architecture ends up roughly 95/5. About ninety-five percent of traffic is the Flutter app talking directly to Supabase — no API layer, RLS doing the enforcement. The remaining five percent is a small backend for things that need secrets the client must never hold: payment gateway webhooks, WhatsApp Business API calls.

That split only works because of RLS. Without database-level isolation you need a server in front of everything, purely to be the thing that remembers to filter. With it, the client can talk to the database directly and be untrusted at the same time.

Seventeen migrations, twelve core tables, every one of them tenant-scoped at the row level. A workshop cannot see another workshop's data even if the app tries.

The short version

• Put isolation in the database, not the application — application filters are a promise, policies are a guarantee
• SECURITY DEFINER needs SET search_path, always, no exceptions
• Mark policy helper functions STABLE or pay for them on every row
• Add tenant_id in migration one, even if you have one customer
• Snapshot financial line items; never join them to a mutable catalogue
• Consistency under concurrency belongs server-side

The client should be untrusted by construction, not by convention.