Multi-Tenant SaaS Architecture with Supabase RLS: The 2026 Playbook

Building multi-tenant SaaS on Supabase — tenant isolation enforced at the Postgres layer via Row-Level Security — looks manageable in a docs tutorial. The tutorial gives you two tables, one policy, and a clean SELECT. Production gives you eight tables, six roles, a recursive policy trap, and a PgBouncer compatibility issue you find during a load test.

This is the guide I put together after finishing the Callidus build: a UK aesthetic clinic management platform where multi-tenant SaaS isolation was a compliance requirement, not an architectural nicety. Every pattern here has been tested against real application code. If you're building B2B SaaS where one customer's data must never reach another's, the multi-tenant saas supabase rls approach described here is the playbook.

Why the Tenant Model Comes Before the Policies

Most teams reach for Row-Level Security before they've locked down the tenant model. That order is wrong. RLS policies are SQL predicates — they can only filter on data that already exists in the right shape. If your tenant identifier isn't consistent across every data table from day one, you end up patching policies around schema gaps rather than writing them once and moving on.

The model that fits most B2B SaaS use cases:

• A workspaces table (or tenants, or organizations) with a UUID primary key
• A memberships join table: (user_id, workspace_id, role), composite primary key
• Every data table with a non-nullable, indexed workspace_id column. This column is load-bearing: every RLS policy and every tenant-scoped query runs through it. Missing it on even one table means a policy gap you'll find at the worst possible time.

The role column in memberships drives everything downstream. Define it as a checked constraint at the database level so invalid values are rejected before they reach your application code:

CREATE TABLE memberships (
  user_id      uuid REFERENCES auth.users NOT NULL,
  workspace_id uuid REFERENCES workspaces NOT NULL,
  role         text CHECK (role IN ('owner', 'admin', 'member', 'viewer')) NOT NULL,
  PRIMARY KEY  (user_id, workspace_id)
);

The JWT from Supabase Auth carries the user's sub. Every RLS policy compares memberships.user_id against auth.uid(). With this model locked in, the policies write without ambiguity.

Application-Layer Access Control Is Not Defense in Depth

Most teams add access checks at the application layer and call it defense in depth. The bypass surface area is enormous — and this framing is the rationalization that causes data leaks.

An application-layer check is a function call. A bug skips it. A misconfigured middleware chain skips it. A developer writing a one-off admin script skips it. A data migration running under a direct database connection skips it. None of those paths respect your Express middleware or your Next.js route handler.

The React + Supabase RLS stack enforces policies at the database engine level on every query — REST via PostgREST, GraphQL, raw SQL, ORM queries, admin scripts, migration runners. Every path. The bypass surface area shrinks to exactly one thing: the service-role key, which you've locked in a server-only environment and never exposed to client code.

Defense in depth means the database is the last line. If everything above it fails, the database still won't return rows from the wrong workspace. Application-layer-only isolation means you have no last line — just a chain of checks, each separately bypassable.

Writing Policies Without Triggering the Recursion Trap

Here's a clean read policy for a projects table:

CREATE POLICY "workspace members can read projects"
ON projects FOR SELECT
USING (
  workspace_id IN (
    SELECT workspace_id FROM memberships
    WHERE user_id = auth.uid()
  )
);

Readable. The trap lives inside that subquery.

If memberships itself has an RLS policy with a USING clause that queries memberships, Postgres sees mutual recursion. The result is either an empty set or a query error depending on the version. The fix is a SECURITY DEFINER function that the query planner treats as a trusted, non-recursive execution path:

CREATE OR REPLACE FUNCTION current_user_workspace_ids()
RETURNS SETOF uuid
LANGUAGE sql STABLE SECURITY DEFINER
AS $$
  SELECT workspace_id FROM memberships WHERE user_id = auth.uid()
$$;

Then your policy becomes:

USING (workspace_id IN (SELECT current_user_workspace_ids()))

SECURITY DEFINER runs the function as the function owner rather than the querying user, which sidesteps the recursion. STABLE lets the planner cache the result within a transaction.

Actually — that's worth qualifying. You only need this function if memberships itself has a USING clause that queries memberships. If the memberships policy is a simple user_id = auth.uid() equality check with no subquery, there's no recursion and the function is optional. Use it as belt-and-braces if your role-based policies let members see other members' records.

Mutation Policies Need a WITH CHECK Clause

Have you deployed an UPDATE policy without a WITH CHECK clause? Easy to miss. PostgREST will allow the update as long as the row passes the USING filter — it does not enforce write constraints without an explicit WITH CHECK:

CREATE POLICY "admins can update workspace settings"
ON workspace_settings FOR UPDATE
USING (workspace_id IN (SELECT current_user_workspace_ids()))
WITH CHECK (
  EXISTS (
    SELECT 1 FROM memberships
    WHERE user_id = auth.uid()
      AND workspace_id = workspace_settings.workspace_id
      AND role IN ('admin', 'owner')
  )
);

The USING clause controls which rows are visible before the update attempt. The WITH CHECK clause enforces what the write is allowed to produce. Both need to pass. Forgetting WITH CHECK on a mutation policy is the kind of bug that ships quietly and surfaces in a customer complaint months later.

The Indexes That Make RLS Fast

Two indexes. Day one.

CREATE INDEX idx_projects_workspace_id ON projects (workspace_id);
CREATE INDEX idx_memberships_user_workspace ON memberships (user_id, workspace_id);

Fine at a thousand rows without them. Noticeably slow at a hundred thousand. RLS adds predicates to every query; those predicates need index seeks or you're scanning the whole table on every authenticated request. The membership lookup in your SECURITY DEFINER function hits the second index on every request — add both before you have traffic worth measuring.

Audit Logs at the DB Layer

For any multi-tenant build handling sensitive data, an audit trail that lives in the database — enforced by triggers, not application code — is the pattern worth reaching for. A trigger on every sensitive table writes to an append-only audit_log table:

CREATE TABLE audit_log (
  id           bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  actor_id     uuid NOT NULL,
  workspace_id uuid NOT NULL,
  action       text NOT NULL,
  table_name   text NOT NULL,
  row_id       uuid NOT NULL,
  diff         jsonb,
  created_at   timestamptz DEFAULT now() NOT NULL
);

The trigger records actor_id from auth.uid() and workspace_id from the mutated row itself — not from the session claim. If a bug ever placed the wrong workspace_id on a row, the audit log reflects that fact. Audit log rows get an INSERT-only RLS policy. No UPDATE policy. No DELETE policy. Immutable by design.

Migration Path from Single-Tenant to Multi-Tenant SaaS

Converting a single-tenant app to multi-tenant Supabase RLS follows a fixed order:

1. Add workspace_id uuid NOT NULL DEFAULT '<seed-workspace-uuid>' to each data table with a backfill migration
2. Create workspaces and memberships tables
3. Assign existing users to the initial workspace as owner
4. Write all RLS policies for each table — read, write, delete — before enabling RLS
5. Enable RLS: ALTER TABLE projects ENABLE ROW LEVEL SECURITY
6. Rotate from service-role key to anon key in the front-end client
7. Create a second test workspace manually and confirm no data crosses the boundary

Step 5 before step 4 is the danger zone. Enabling RLS with zero policies locks everyone out — including the application. In Supabase, "no policies" means "no rows returned." Write the policies first, enable RLS second, in the same migration transaction.

PgBouncer Transaction Mode and Prepared Statements

One Supabase-specific gotcha worth knowing before you hit it in production: if you're using the pooler port (6543) in transaction mode, prepared statements don't work. Prisma sends them by default — add ?pgbouncer=true to your connection string. Drizzle handles this via its query builder without requiring a flag.

The subtler issue: auth.uid() reads the JWT claim from SET LOCAL request.jwt.claim.sub. PostgREST sets this automatically per request. If you're running queries directly from a server environment and need RLS to apply — for scoped admin tooling — you must set this claim manually before any query that hits a policy. Miss it and every RLS policy returns an empty set with no error, which looks exactly like a policy misconfiguration and will cost you an hour.

Build the Scoped Admin Tool Before You Need It

The gap most multi-tenant builds leave open: an admin interface that respects RLS. You'll need to inspect a workspace's data to debug a customer problem eventually. The unsafe path is pulling a service-role connection and running raw queries — that bypasses everything you just built.

The safer pattern: a server action that issues a scoped JWT for a given workspace_id, letting admin tooling run through the exact same RLS path as real users with an injected workspace claim. When the pager goes off at 2am and a clinic reports missing records, you want to be debugging inside the same isolation boundary your customers are in — not around it.

Build the scoped admin tool this week. Before the pager goes off.