How to build a multi-tenant SaaS: architecture, data isolation and the mistakes to avoid

-- Tenants, plans and memberships
create table plans (
  id           text primary key,
  max_projects int  not null,
  max_seats    int  not null
);
insert into plans values ('starter', 3, 5), ('pro', 50, 25);

create table tenants (
  id        uuid primary key default gen_random_uuid(),
  subdomain text unique not null check (subdomain ~ '^[a-z0-9-]{3,30}$'),
  name      text not null,
  plan_id   text not null references plans default 'starter'
);

create table users (id uuid primary key default gen_random_uuid(), email text unique not null);

create table memberships (
  tenant_id uuid references tenants on delete cascade,
  user_id   uuid references users on delete cascade,
  role      text not null check (role in ('owner', 'admin', 'member')),
  primary key (tenant_id, user_id)
);

-- Tenant data: every table carries tenant_id, filled in from the session
create function current_tenant() returns uuid language sql stable as $$
  select nullif(current_setting('app.tenant_id', true), '')::uuid
$$;

create table projects (
  id        bigint generated always as identity primary key,
  tenant_id uuid not null references tenants default current_tenant(),
  name      text not null
);

create table documents (
  id        bigint generated always as identity primary key,
  tenant_id uuid not null references tenants default current_tenant(),
  title     text not null,
  body      text not null,
  search    tsvector generated always as (to_tsvector('english', title || ' ' || body)) stored
);
create index on documents using gin (search);
create index on projects (tenant_id);
create index on documents (tenant_id);

-- Row-level security on every tenant table
alter table projects  enable row level security;
alter table documents enable row level security;
create policy tenant_isolation on projects  using (tenant_id = current_tenant()) with check (tenant_id = current_tenant());
create policy tenant_isolation on documents using (tenant_id = current_tenant()) with check (tenant_id = current_tenant());

-- The app connects as this role. It does not own the tables, so RLS applies to it.
create role saas_app login password 'demo-only';
grant select, insert, update, delete on projects, documents to saas_app;
grant select on tenants, plans, memberships, users to saas_app;
grant usage on all sequences in schema public to saas_app;

-- One step creates everything a new tenant needs
create function create_tenant(p_subdomain text, p_name text, p_owner_email text, p_plan text default 'starter')
returns uuid language plpgsql security definer set search_path = public as $$
declare
  t uuid;
  u uuid;
begin
  insert into tenants (subdomain, name, plan_id) values (p_subdomain, p_name, p_plan) returning id into t;
  insert into users (email) values (p_owner_email)
    on conflict (email) do update set email = excluded.email returning id into u;
  insert into memberships values (t, u, 'owner');
  insert into projects (tenant_id, name) values (t, 'Getting started');
  insert into documents (tenant_id, title, body)
    values (t, 'Welcome', 'Your workspace is ready. Invite your team from Settings.');
  return t;
end $$;

-- Plan limits enforced in the database, so no API route can skip them
create function enforce_project_limit() returns trigger language plpgsql security definer set search_path = public as $$
declare
  used    int;
  allowed int;
  plan    text;
begin
  perform pg_advisory_xact_lock(hashtext(new.tenant_id::text));   -- two requests at once can't both squeeze in
  select count(*) into used from projects where tenant_id = new.tenant_id;
  select p.max_projects, p.id into allowed, plan from tenants t join plans p on p.id = t.plan_id where t.id = new.tenant_id;
  if used >= allowed then
    raise exception 'Plan limit reached: % allows % projects', initcap(plan), allowed using errcode = 'P0001';
  end if;
  return new;
end $$;
create trigger project_limit before insert on projects for each row execute function enforce_project_limit();

import pg from 'pg';

export const pool = new pg.Pool({ connectionString: process.env.DATABASE_URL });   // connects as saas_app

// Run fn inside a transaction that knows its tenant. set_config(..., true) lasts for this
// transaction only, so a pooled connection can't carry one tenant into the next request.
export async function withTenant(tenantId, fn) {
  const client = await pool.connect();
  try {
    await client.query('begin');
    await client.query("select set_config('app.tenant_id', $1, true)", [tenantId]);
    const result = await fn(client);
    await client.query('commit');
    return result;
  } catch (err) {
    await client.query('rollback');
    throw err;
  } finally {
    client.release();
  }
}

import express from 'express';
import { pool, withTenant } from './db.mjs';

const app = express();
app.use(express.json());

// Demo sessions. In production this is your auth provider's verified session.
const SESSIONS = { 'sess-maya': 'maya@acme.test', 'sess-sam': 'sam@globex.test' };

// Tenant comes from the subdomain, and the signed-in user must be a member of it.
// Nothing the browser sends in the body or query string can change it.
async function resolveTenant(req, res, next) {
  const email = SESSIONS[req.get('Authorization')?.replace('Bearer ', '')];
  if (!email) return res.status(401).json({ error: 'Not signed in' });
  const subdomain = req.hostname.split('.')[0];
  const { rows } = await pool.query(
    `select t.id, t.name, m.role from tenants t
       join memberships m on m.tenant_id = t.id
       join users u on u.id = m.user_id
      where t.subdomain = $1 and u.email = $2`, [subdomain, email]);
  if (!rows.length) return res.status(403).json({ error: `You are not a member of ${subdomain}` });
  req.tenant = rows[0];
  next();
}

app.use('/api', resolveTenant);

app.get('/api/projects', async (req, res) => {
  const { rows } = await withTenant(req.tenant.id, c => c.query('select id, name from projects order by id'));
  res.json({ tenant: req.tenant.name, projects: rows });
});

app.post('/api/projects', async (req, res) => {
  try {
    // tenant_id is filled in by the database from the session, never from req.body
    const { rows } = await withTenant(req.tenant.id, c =>
      c.query('insert into projects (name) values ($1) returning id, name', [req.body.name]));
    res.status(201).json(rows[0]);
  } catch (err) {
    if (err.code === 'P0001') return res.status(402).json({ error: err.message });
    throw err;
  }
});

// The retrieval step behind an AI assistant: only this tenant's documents can come back
app.get('/api/search', async (req, res) => {
  const { rows } = await withTenant(req.tenant.id, c => c.query(
    `select title, left(body, 70) as snippet from documents
      where search @@ websearch_to_tsquery('english', $1)
      order by ts_rank(search, websearch_to_tsquery('english', $1)) desc limit 3`, [req.query.q]));
  res.json({ tenant: req.tenant.name, results: rows });
});

app.listen(4404, () => console.log('Fieldnote API on :4404'));

// Background jobs carry the tenant they work for, exactly like requests do.
import { pool, withTenant } from './db.mjs';

const { rows: tenants } = await pool.query('select id, name from tenants order by name');

for (const t of tenants) {
  const { rows } = await withTenant(t.id, c => c.query('select count(*)::int as n from projects'));
  console.log(`weekly digest for ${t.name}: ${rows[0].n} project(s)`);
}

// The bug everyone writes eventually: a job that forgets to set the tenant
const { rows } = await pool.query('select count(*)::int as n from projects');
console.log(`job without a tenant sees ${rows[0].n} project(s)  <- fails closed, not open`);

await pool.end();