How to Add Multi-Tenancy to an AI-Built App

If you're building a SaaS app with an AI coding tool and about to add a second paying customer, you need multi-tenancy: a way to keep each customer's data separate inside one running application. There are three standard patterns for this on Postgres: a shared schema with a tenant_id column enforced by row-level security, one schema per tenant, or one database per tenant. For a solo builder or small team, the shared-schema-plus-RLS pattern is almost always the right starting point. It costs the least to run, it's what most AI-generated backends default to already, and it scales further than people expect before you need to reconsider it.

Every one of these patterns trades off in the same three places: what it costs you in infrastructure per new signup, how far a bug in your query code can leak, and how painful a schema migration becomes once you have real customers with real data. Here's how to reason through each one, plus a working row-level security policy you can copy directly.

The three patterns, briefly

• Shared schema, tenant_id column: one set of tables for every customer, with every row tagged by a tenant_id and Postgres row-level security enforcing who can see or write which rows.

• Schema-per-tenant: the same Postgres database, but each tenant gets its own schema, a separate namespace holding an identical copy of every table.

• Database-per-tenant: each tenant gets a fully separate Postgres database, sometimes on entirely separate compute.

Pattern 1: shared schema with tenant_id and RLS

In this pattern, every table that holds customer data gets a tenant_id column, and a Postgres row-level security policy filters every query so a session can only touch rows matching its own tenant_id, usually read from a JWT claim or a session variable set at connection time. AI app builders that generate a Postgres or Supabase backend, tools like Swarmz among others, typically scaffold a tenant_id column on user-owned tables by default, because it's the cheapest pattern to generate and the one that fits the widest range of apps.

• Cost: lowest of the three. One database, one connection pool, all tenants share the same compute, so a new signup costs you rows, not infrastructure.

• Isolation: enforced by the database itself when policies are complete, but it depends on discipline. Miss a policy on one table and that table is wide open across every tenant.

• Migration complexity: lowest. One migration script runs once and applies to every tenant simultaneously.

• Blast radius: the largest of the three if something goes wrong. A policy bug can expose every tenant's data at once, and a single tenant running an expensive query can slow the database for everyone else.

Pattern 2: schema-per-tenant

Same Postgres instance, but each tenant gets its own schema, essentially a private namespace holding its own copy of every table. Your application switches schemas (or search_path) based on which tenant is making the request.

• Cost: moderate. Still one database server, but connection pooling and query planning get harder as schema count climbs into the hundreds.

• Isolation: stronger than RLS at the query level, a bug can't accidentally cross a schema boundary the way it can cross a row filter, but tenants still share the same instance, so a noisy tenant can still degrade everyone's performance.

• Migration complexity: meaningfully higher. Every migration has to run once per schema, and a failure partway through a hundred-schema rollout leaves you with a fleet in inconsistent states.

• Blast radius: a data leak is scoped to one tenant's schema, but an instance-level failure, a full disk or a replication problem, still takes every tenant down together.

Pattern 3: database-per-tenant

Each tenant gets a fully separate Postgres database, possibly on separate infrastructure entirely. This is the pattern regulated industries and large enterprise contracts tend to ask for by name.

• Cost: highest by far. You pay for idle compute per tenant, backups multiply, and connection limits multiply along with them.

• Isolation: strongest available. A bug in your application code can only reach the one database it's connected to at that moment.

• Migration complexity: highest. You need real tooling to run schema changes across potentially hundreds of independent databases and to handle the ones that fail partway through.

• Blast radius: smallest for leaks and noisy neighbors, but the operational overhead, monitoring, backups, on-call, scales linearly with every single signup, which is exactly what makes it wrong for most early-stage apps.

Which pattern fits your stage

1. Pre-launch through your first handful of customers: shared schema plus RLS. Anything more is premature optimization for a problem you don't have yet, and it's usually what your backend already looks like if an AI tool generated it from a prompt like "add customer accounts."

2. Tens to low hundreds of customers: still shared schema plus RLS, but this is when you audit it properly. Add automated tests that log in as one tenant and assert every query against another tenant's data returns nothing, and confirm RLS is enabled and forced on every table that touches customer data, not just the obvious ones.

3. A specific customer requires stronger isolation: a prospective enterprise account asks for a dedicated database for compliance reasons, or one tenant's usage pattern is measurably degrading performance for everyone else. Treat schema-per-tenant, or database-per-tenant for that one account, as a targeted exception, not a wholesale rewrite of your architecture.

4. Regulatory or contractual requirements: data residency rules or a business associate agreement demanding physical separation. Database-per-tenant, but usually still scoped to the customers who actually require it.

These stages aren't mutually exclusive. Most SaaS apps that reach meaningful scale end up hybrid: shared schema for the large majority of customers, with an isolated schema or database carved out for the small number of accounts who pay for it or contractually require it.

Setting up tenant isolation with row-level security

1. Add a tenant_id column to every table that stores tenant-owned data. Make it not null and reference a tenants table with a foreign key so orphaned rows can't exist.

2. Enable row level security on the table with ENABLE ROW LEVEL SECURITY.

3. Force it with FORCE ROW LEVEL SECURITY. Without this, the table's owner role, which is often the same role your application connects as, bypasses every policy by default. This is the single most common way teams accidentally ship a leak.

4. Write a policy that compares tenant_id against the caller's tenant identity. On Supabase this is usually a custom claim read out of the JWT with auth.jwt().

5. Include the check in both the USING clause, which governs what existing rows are visible and updatable, and the WITH CHECK clause, which governs what new or modified rows are allowed to be written. A policy with only USING can still let a bug write a row into another tenant's data.

6. Before shipping, write an automated test that authenticates as tenant A and asserts every query against tenant B's rows returns zero results. Run it on every table, not just the one you were working on.

-- Add tenant_id to a tenant-scoped table
alter table public.invoices
  add column tenant_id uuid not null references public.tenants(id);

-- Enable row-level security
alter table public.invoices enable row level security;

-- Force it even for the table owner role
-- (without this, the owning role bypasses every policy below)
alter table public.invoices force row level security;

-- A row is only visible or writable if its tenant_id matches
-- the tenant_id claim in the caller's JWT
create policy tenant_isolation on public.invoices
  using (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid)
  with check (tenant_id = (auth.jwt() ->> 'tenant_id')::uuid);

Repeat steps one through five for every table with customer data, add the tenant_id claim to your JWT at login time, and treat the test suite from step six as a required check before any schema change ships, not a one-time exercise.

Where tenancy fits with the rest of your stack

Tenant isolation only works if you actually know who's calling in the first place. If you haven't wired up authentication yet, that's the more fundamental piece, and how to add SSO login to an AI-built app covers how a login provider hands your app a verified identity to put in that JWT. Once tenants exist, most apps also need to control which users inside a tenant can do what, which is a separate concern from isolation and covered in add role-based permissions to an AI-built app. And once tenants share infrastructure under a shared schema, one tenant hammering an endpoint can degrade the experience for everyone else, which is why per-tenant rate limiting usually follows RLS as the next thing worth adding. If you're earlier than any of this and still shaping the app itself, the overview in how to build an app with AI is the better starting point.

Frequently asked questions

What is multi-tenancy in a SaaS database?

Multi-tenancy means multiple customers, or tenants, share the same running application and often the same database, while each tenant's data stays invisible to every other tenant. The three common ways to enforce that separation are a shared schema with a tenant_id column, one schema per tenant, or one database per tenant.

Is row-level security enough to isolate tenants in Postgres?

Yes, if it's configured completely. That means every tenant-scoped table has RLS enabled and forced, every policy checks both the USING and WITH CHECK clauses, and you have automated tests that try to read another tenant's rows and confirm they fail. Partial coverage is the actual risk, not RLS itself.

When should I move from shared schema to schema-per-tenant?

Usually only when one specific tenant needs it, such as a contract requiring dedicated infrastructure, rather than as a default upgrade. Migrating everyone to schema-per-tenant adds real operational cost for most apps that never actually hit the limits of a well-built shared schema.

Does Supabase support row-level security for multi-tenancy?

Yes. Supabase runs standard Postgres, so RLS is a first-class feature, and its auth layer exposes the logged-in user's claims through functions like auth.jwt() and auth.uid(), which you reference directly inside your policies.

What's the most common mistake when adding a tenant_id column?

Two mistakes show up constantly: forgetting to run FORCE ROW LEVEL SECURITY, which leaves the table's owner role able to bypass every policy, and writing a policy with only a USING clause, which lets a bug insert or update rows under the wrong tenant_id even though reads are still isolated.