Multi-Tenant SaaS Architecture — Shared Schema + RLS vs Schema-per-Tenant

Multi-tenant SaaS runs all customer data in one database but isolates each tenant from the others. You could give each tenant their own schema (and 30 schemas later you're managing 30 copies of the same tables), their own database (cost scales linearly with customer count, and schema migrations are a nightmare), or put everyone in the same tables and trust row-level security to do the filtering. The internet collectively lost its mind over which approach is "right". Spoiler: shared schema + Supabase RLS is the move for 95% of SaaS. You get simplicity, single source of truth, and isolation guarantees that are good enough for most products. This guide covers all three patterns, the real tradeoffs, and how to implement shared schema with the org_id + RLS pattern that scales from day one to $1M ARR.

Three Multi-Tenant Patterns

Every multi-tenant SaaS picks one of these. Understanding the tradeoff is the entire game.

Pattern 1: Shared Schema + Row-Level Security (Recommended)

All tenants share the same tables. Every table has an organization_id column. Postgres RLS policies filter rows by org: "users can only SELECT rows where organization_id = their_org_id". One schema, one set of indexes, one set of migrations. When you add a feature (like a new projects table), it's one ALTER TABLE statement, not 30.

Pros: Simplicity. Schema migrations are one-shot. Cost is constant regardless of tenant count. Adding features is fast. Per-tenant feature rollouts are trivial (just check users.feature_flag before rendering). This is what Stripe, Slack, and most successful SaaS do. Cons: Shared infrastructure means one bad query can slow down all tenants. Tenant isolation is logical (RLS policies) not physical — if you mess up a policy, you've leaked data across tenants. Requires discipline: every table must have org_id, every policy must be correct.

Pattern 2: Schema-per-Tenant

Each tenant gets their own schema within the same database: public.projects, tenant_123.projects, tenant_456.projects. Migrations need to run on N schemas. Queries route to the right schema via set search_path = tenant_123; at the start of each request.

Pros: Cleaner isolation than shared schema (if a query leaks, it's still one schema). Per-tenant backups are simpler. You can drop a tenant's entire schema without touching others. Cons: N times the schema overhead. Adding a column means running the same migration N times. Schema versioning becomes a nightmare (some tenants are on v5, others on v7). Cost and complexity scale with tenant count. Most teams try this and regret it by their 20th tenant.

Pattern 3: Database-per-Tenant

Each tenant gets a dedicated PostgreSQL database instance. Tenants are completely isolated: separate databases, separate backups, separate performance pools. Zero risk of cross-tenant data leaks.

Pros: Maximum isolation. You can customize schemas per tenant if needed. Compliance teams love this (data residency, audit trails). High-value customers often demand it. Cons: Cost scales linearly: 100 customers = 100 databases. Connection pooling is complex (Supabase says N*20 connections). Schema migrations require orchestration (run on all 100 databases). DevOps overhead is real. Only pick this if you're selling $10k+/month per seat, or your customers demand it for compliance.

Shared Schema + RLS: The Real Implementation

Here's the pattern Velocity and most production SaaS use. Start here, migrate to schema-per-tenant if you hit scaling issues (you won't).

Step 1: org_id on Every Table

-- Every table that's org-scoped includes organization_id
create table organizations (
  id uuid primary key default gen_random_uuid(),
  name text not null,
  created_at timestamp default now()
);

create table users (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references organizations(id) on delete cascade,
  email text not null,
  role text not null default 'member',
  created_at timestamp default now()
);

create table projects (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references organizations(id) on delete cascade,
  name text not null,
  created_by uuid not null references users(id) on delete cascade,
  created_at timestamp default now()
);

create table api_keys (
  id uuid primary key default gen_random_uuid(),
  organization_id uuid not null references organizations(id) on delete cascade,
  secret text not null unique,
  created_by uuid not null references users(id) on delete cascade,
  created_at timestamp default now()
);

-- Index org_id for fast filtering
create index idx_users_org_id on users(organization_id);
create index idx_projects_org_id on projects(organization_id);
create index idx_api_keys_org_id on api_keys(organization_id);

Step 2: Helper Function for Current User's Org

-- Single source of truth: what org does the current user belong to?
create or replace function auth.org_id() returns uuid as $$
  select organization_id from users where id = auth.uid()
$$ language sql stable security definer;

-- Grant execute to authenticated users
grant execute on function auth.org_id() to authenticated;

-- Optional: helper to check if user is admin in their org
create or replace function auth.is_org_admin() returns boolean as $$
  select role = 'admin' from users where id = auth.uid()
$$ language sql stable security definer;

grant execute on function auth.is_org_admin() to authenticated;

Step 3: RLS Policies

-- Enable RLS on all tables
alter table users enable row level security;
alter table projects enable row level security;
alter table api_keys enable row level security;

-- SELECT: org members can see rows for their org
create policy "users can select their org's rows" on users
for select
using (organization_id = auth.org_id());

create policy "users can select their org's projects" on projects
for select
using (organization_id = auth.org_id());

create policy "users can select their org's api keys" on api_keys
for select
using (organization_id = auth.org_id());

-- INSERT: must belong to org and match org_id
create policy "users can insert for their org" on projects
for insert
with check (organization_id = auth.org_id());

create policy "users can insert api keys for their org" on api_keys
for insert
with check (organization_id = auth.org_id());

-- UPDATE: only creator or org admin
create policy "users can update their own projects" on projects
for update
using (
  organization_id = auth.org_id()
  and (created_by = auth.uid() or auth.is_org_admin())
)
with check (
  organization_id = auth.org_id()
  and (created_by = auth.uid() or auth.is_org_admin())
);

-- DELETE: only creator or org admin
create policy "users can delete their own projects" on projects
for delete
using (
  organization_id = auth.org_id()
  and (created_by = auth.uid() or auth.is_org_admin())
);

Step 4: Tenant Routing in Your App

// src/lib/supabase.ts
import { createClient } from '@supabase/supabase-js';

const supabase = createClient(
  process.env.VITE_SUPABASE_URL!,
  process.env.VITE_SUPABASE_KEY!
);

// Every query is automatically filtered by RLS
export async function getUserProjects() {
  const { data, error } = await supabase
    .from('projects')
    .select('*')
    .order('created_at', { ascending: false });

  if (error) throw error;
  // RLS policy filters: only returns projects where organization_id = auth.org_id()
  return data;
}

// Same for mutations
export async function createProject(name: string) {
  const { data, error } = await supabase
    .from('projects')
    .insert([{ name, organization_id: auth.org_id() }]) // You pass org_id
    .select()
    .single();

  if (error) throw error;
  return data;
}

Step 5: Testing Tenant Isolation

Create two test users in different orgs. Log in as User A, query projects — should see only Org A's projects. Log in as User B, query the same — should see only Org B's projects. If User B can see Org A's data, your policy is broken. Test this before shipping.

-- Create two test orgs and users
insert into organizations (name) values ('Org A') returning id as org_a_id;
insert into organizations (name) values ('Org B') returning id as org_b_id;

insert into users (organization_id, email, role)
values
  (org_a_id, 'user-a@example.com', 'admin'),
  (org_b_id, 'user-b@example.com', 'admin');

-- Log in as user-a, run: select * from projects;
-- Should be empty (no projects yet)

-- Switch to user-b, run the same query
-- Should still be empty

-- Create a project as user-a
insert into projects (organization_id, name, created_by)
values (org_a_id, 'Project A', user_a_id);

-- As user-a: select * from projects; → sees Project A
-- As user-b: select * from projects; → empty (policy blocks it)

When to Escalate: Schema-per-Tenant or Database-per-Tenant

Shared schema scales to millions of rows and hundreds of thousands of users. You rarely need to leave it. But:

Escalate to schema-per-tenant if: You have 100+ tenants and some are doing million-row queries that slow down the whole system. Separate schemas let you tune each one independently. Or: compliance requires per-tenant data separation (not just logical, but physical in the schema). Or: multi-geography — you want different schemas in different regions. This is rare.

Escalate to database-per-tenant if: You're selling per-seat pricing above $10k/month and customers demand guaranteed performance SLAs. Or: data residency — customer requires data in a specific region (Canada, EU, Australia). Or: you're building a platform for large enterprises (Salesforce-style) where each customer runs essentially a separate instance. Or: regulatory (GDPR data localization). 95% of SaaS never hits this. If you are, you're past the "how do I architect this" phase and need a DevOps team.

Performance Optimization for Shared Schema

At scale (millions of rows), shared schema queries can slow down if you're not careful. Here's what to do:

Use composite indexes on (org_id, other_key): Most queries filter by org_id first, then by something else (created_at, status, user_id). An index on (organization_id, created_at) is faster than separate indexes.

-- Fast queries with this index
create index idx_projects_org_created on projects(organization_id, created_at desc);
create index idx_api_keys_org_secret on api_keys(organization_id, secret);

Avoid SELECT * on wide tables: If your projects table has 50 columns and you're fetching 10,000 rows, you're moving 500K columns over the network. Specify the columns you need.

Paginate: Don't fetch all rows. Use limit and offset or cursor-based pagination.

// Bad: fetch all projects
const { data } = await supabase.from('projects').select('*');

// Good: fetch 50 at a time
const { data } = await supabase
  .from('projects')
  .select('id, name, created_at, status')
  .order('created_at', { ascending: false })
  .limit(50)
  .offset(0);

Use triggers for denormalization: If you need aggregates (like project count per user), don't calculate it on read. Create a user_stats table and update it with a trigger on projects insert/delete.

Six FAQs

Can I migrate from shared schema to schema-per-tenant later?

Yes, but it's painful. You'd need to dump each org's data, create N schemas, and restore them. You'd rewrite app code to route queries. Plan for 3–5 engineering weeks. Better to get shared schema right now than migrate later. That said, if you outgrow shared schema, you've won — you have customers and revenue to justify the migration cost.

What if one tenant's queries are slower than others? Shared schema seems unfair.

It's not unfair, it's shared infrastructure. One slow query affects everyone on a shared database (but RLS policies isolate which rows they can see). If one tenant is doing million-row scans, you either optimize the query, add indexes, or ask them to upgrade to a premium plan with dedicated infrastructure. Most SaaS handle this at the pricing tier level, not the architecture level.

How do I prevent accidental data leaks in shared schema?

Audit your RLS policies before shipping. Test as two different users in different orgs — try to break the isolation. Use Supabase's policy debugger in the dashboard. And: never use string interpolation when building queries. Always use parameterized queries (Supabase client does this). A query like supabase.from('projects').select().eq('id', projectId) is safe even if projectId is injected; the RLS policy still applies.

Can I have org-level and user-level isolation in the same table?

Yes. Add a user_id column and create policies that check both org_id and user_id. For example, team_members can see projects assigned to them (org_id + user_id match), or projects they created (org_id + created_by). Stack the checks in your WITH CHECK clause.

What happens if a user signs up and belongs to zero orgs?

They should belong to at least one org (their personal workspace). If they don't, auth.org_id() returns NULL, and the policy organization_id = NULL returns no rows. They're locked out. Always create a personal org when a user signs up. Or: create a nullable org_id column and policy handling, but that's more complex.

How do I handle API access across tenants (webhooks, integrations)?

Store the organization_id on the API key row. When a webhook or integration comes in, look up the API key, extract the org_id, and then authenticate the request as that org. Supabase lets you pass custom claims in the JWT — set the org_id claim and your policies will respect it. See Supabase Row-Level Security for the service-role pattern.

The Bottom Line

Shared schema + Supabase RLS is the default move. org_id on every table, RLS policies that filter by org, one helper function, and you've got multi-tenant isolation that scales to millions of rows. Schema-per-tenant is for edge cases (100+ tenants, per-tenant customization). Database-per-tenant is for enterprise SaaS where customers demand it. Get shared schema right now — it's 80% of the work, 100% of the value for most SaaS. Ready to ship production-grade multi-tenant architecture? Check out Aidxn Design for backend partnerships and architecture reviews.