7 · Multi-tenancy patterns

The question this chapter answers: which tenancy model fits my product — and what does each one cost in policy shape?

TaskHarbor uses the pattern most Supabase projects should: every row carries its tenant, and policies resolve membership. It is not the only pattern, and choosing deliberately beats inheriting one. The three candidates differ on isolation strength, operational cost, and how much work falls to policies versus to structure.

Pattern A: shared schema, tenant_id column

One database, one schema, a workspace id on every tenant-owned table. TaskHarbor's shape. Policies do the isolating:

create policy "tasks: members read" on public.tasks
  for select to authenticated
  using (private.is_member(workspace_id));   -- or the join form from Chapter 3

Isolation strength: entirely dependent on policy correctness. One tautology and tenants share everything — which is why this book spends Chapters 2–6 making correctness provable.

Cost profile: cheapest to operate by far. Migrations run once; indexes serve all tenants; cross-tenant analytics are ordinary SQL. The price is paid at the boundary: every new table needs its tenant column, its grants, and its policies, and nothing structural stops the first developer who forgets.

When it fits: most SaaS products. Dozens to thousands of tenants, routine product iteration, a team that can maintain the discipline of Chapters 3 and 10.

Pattern B: schema per tenant

Each tenant gets a schema with identical tables; the application sets search_path per request. Isolation moves down a layer — Postgres itself separates the namespaces:

create schema tenant_aster;
create table tenant_aster.tasks (like public.tasks including all);

-- Per-request role/grant discipline replaces most RLS:
grant usage on schema tenant_aster to authenticated;
grant select, insert, update, delete on all tables in schema tenant_aster to authenticated;

Policies become optional rather than central; grants and default privileges carry the load, and a mistake exposes one schema, not one column predicate.

Isolation strength: stronger blast-radius behavior — a missing grant leaks one tenant's schema, not the whole table — though it is still one database, one credential store, one noisy neighbor.

Cost profile: every migration multiplies by tenant count, so tooling must generate and apply per-tenant DDL; connection pools must manage search_path correctly; cross-tenant reporting needs union scaffolding or an aggregation pipeline. The Postgres schema docs cover the mechanics; none of them automate the multiplication.

When it fits: few large tenants with divergent customization needs, or requirements that demand namespace-level separation without separate infrastructure.

Pattern C: project per tenant

Each tenant gets its own Supabase project — own database, own API keys, own backups. There is nothing to leak across tenants because there is no path between them.

Isolation strength: strongest available. Blast radius of any bug, outage, or compromise is one tenant's project.

Cost profile: the heaviest. Provisioning, migrations, key management, monitoring, and billing all multiply; aggregates require extracting data out-of-band; free-tier realities apply per project. This is enterprise-shaped: high-value tenants with contractual isolation demands.

When it fits: a handful of major accounts, regulated industries, or white-label deployments where tenants expect dedicated infrastructure.

Choosing, honestly

Two decision rules fall out. First, choose the weakest pattern whose failure modes you can live with and detect: Pattern A plus Chapter 6's proofs is more defensible than Pattern B adopted to avoid writing tests. Second, migration cost compounds silently — teams routinely underestimate B and C because provisioning is fun and maintaining forty copies of migration 57 is not.

Also worth naming: hybrid. Many products run Pattern A for the long tail and give one whale Pattern C. The discipline that matters is that each surface knows which model it uses — a tenant_id column nobody populates on a single-tenant project is pure liability.

Whatever you choose, the proofs from Chapter 6 translate unchanged: identity, statement, expected outcome. Only the expected outcomes differ ("bo cannot read Aster's rows" becomes "the tenant_aster schema rejects bo entirely").

Check on your project

1. Write down which pattern your project actually uses today — including accidental hybrids — and whether anyone chose it deliberately.
2. If you are on Pattern A: verify every tenant-owned table carries the tenant id (or an unambiguous path to it) and that each has policies from every angle in Chapter 5.
3. If you are on B: audit grants and default privileges per schema, and confirm your request path sets search_path server-side only.
4. If you are on C: inventory where each project's keys live and who can rotate them; per-project isolation also means per-project key hygiene.
5. Sketch what a new tenant's first five minutes look like operationally (rows created vs schema created vs project provisioned). The answer reveals maintenance costs you may be paying invisibly.