Designing the data model for a multi-tenant SaaS

In a multi-tenant product, many customers (tenants) share one application. The most important property of the data model is that no tenant can ever see another's data. There are three common ways to get there.

For most products, start with shared tables. It is the easiest to build and run, and you can move individual customers to dedicated databases later if a contract or a regulator demands it.

Shape the tables around the tenant

create table invoices (
  id bigserial primary key,
  tenant_id bigint not null references tenants(id) on delete cascade,
  number text not null,
  total_cents bigint not null,
  created_at timestamptz not null default now(),
  unique (tenant_id, number)
);

create index invoices_tenant_created on invoices (tenant_id, created_at desc);

Scope every query, in one place

Never rely on each developer remembering where tenant_id = .... Resolve the tenant once per request from the session, and route all data access through functions that require it.

Add row-level security as a second wall

Postgres row-level security (RLS) lets the database itself refuse rows that belong to another tenant. Even if application code forgets a filter, the query returns nothing it should not.

alter table invoices enable row level security;
alter table invoices force row level security; -- applies to the table owner too

create policy tenant_isolation on invoices
  using (tenant_id = current_setting('app.tenant_id')::bigint)
  with check (tenant_id = current_setting('app.tenant_id')::bigint);

Set the tenant at the start of each transaction, scoped to that transaction so it cannot leak into the next request that reuses the connection:

begin;
select set_config('app.tenant_id', '42', true); -- true: local to this transaction
select * from invoices;                          -- only tenant 42's rows
commit;

Have the application connect as a role that is not a superuser and does not have BYPASSRLS, otherwise the policies are skipped. Keep a separate, tightly controlled role for migrations and cross-tenant admin work.

Plan for the hard parts early

Deleting a tenant

With on delete cascade from the tenant table, removing a customer is one statement. Remember files in object storage and copies in analytics or search indexes.

Noisy neighbours

One large tenant can slow everyone down. Rate-limit per tenant, push heavy work to background jobs, and watch the slowest queries by tenant.

Reporting across tenants

Internal analytics need cross-tenant queries. Run them through the privileged role, ideally against a read replica, never through the application's normal connection.

Isolation that depends on every developer remembering a WHERE clause will fail eventually. Put it in the database too.

Checklist