Multi-tenant SaaS

Design the schema for a B2B SaaS where every row belongs to a tenant (workspace). Pick an isolation strategy and justify it. The product has users, projects, and tasks; tenants range from 5 to 50,000 seats.

Clarifying questions

Are any tenants 'whales' large enough to need a dedicated database, or do all fit in shared infra?
Do we need tenant-level encryption keys for compliance (HIPAA, SOC2)?
Can users belong to multiple tenants (Slack-style) or strictly one (Salesforce-style)?
What's the noisy-neighbor tolerance — do we need per-tenant query budgets?
How frequent are tenant exports / deletes for GDPR — is per-tenant logical isolation enough?

Core requirements

Enforce that no query accidentally crosses tenant boundaries.
Support per-tenant data export and full deletion within a bounded window.
Allow most queries to be served from a single tenant's working set in memory.
Permit a user to belong to multiple tenants without row duplication.
Make schema migrations apply to all tenants atomically.
Support per-tenant configuration without per-tenant columns.

Canonical schema (PostgreSQL)

CREATE TABLE tenants (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  slug TEXT UNIQUE NOT NULL,
  name TEXT NOT NULL,
  plan TEXT NOT NULL,
  settings JSONB NOT NULL DEFAULT '{}'::jsonb,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  deleted_at TIMESTAMPTZ
);

CREATE TABLE users (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  email CITEXT UNIQUE NOT NULL,
  display_name TEXT NOT NULL,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);

CREATE TABLE memberships (
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  user_id UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
  role TEXT NOT NULL CHECK (role IN ('owner', 'admin', 'member', 'guest')),
  invited_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  accepted_at TIMESTAMPTZ,
  PRIMARY KEY (tenant_id, user_id)
);

CREATE TABLE projects (
  tenant_id UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
  id UUID NOT NULL DEFAULT gen_random_uuid(),
  name TEXT NOT NULL,
  archived BOOLEAN NOT NULL DEFAULT false,
  created_by UUID NOT NULL REFERENCES users(id),
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, id)
);

CREATE TABLE tasks (
  tenant_id UUID NOT NULL,
  id BIGSERIAL,
  project_id UUID NOT NULL,
  title TEXT NOT NULL,
  status TEXT NOT NULL,
  assignee_id UUID REFERENCES users(id),
  due_at TIMESTAMPTZ,
  created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, project_id) REFERENCES projects(tenant_id, id) ON DELETE CASCADE
);

ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_projects ON projects
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

CREATE POLICY tenant_isolation_tasks ON tasks
  USING (tenant_id = current_setting('app.tenant_id')::uuid);

Key indexes

CREATE INDEX idx_tasks_tenant_project_status ON tasks(tenant_id, project_id, status) WHERE status != 'done'

Active task list is the hottest query and benefits from a partial composite that excludes done work.

CREATE INDEX idx_tasks_tenant_assignee_due ON tasks(tenant_id, assignee_id, due_at) WHERE due_at IS NOT NULL

'My upcoming tasks' is a per-user dashboard query; tenant prefix keeps it shard-local.

CREATE INDEX idx_memberships_user ON memberships(user_id, tenant_id)

CREATE INDEX idx_projects_tenant_active ON projects(tenant_id) WHERE NOT archived

Project picker only ever shows live projects; partial index halves the read set.

Key decisions to defend

Composite primary key (tenant_id, id) on every tenant-scoped table — makes tenant_id the natural prefix on every index and the natural shard key the day you outgrow one DB.
Shared schema with row-level security beats schema-per-tenant: 50k schemas means 50k migrations, broken pg_dump, and exploded pg_catalog. RLS gives you isolation without the operational tax.
Users are global (one row per human) and memberships join them to tenants — a single Google account joining 12 workspaces is one user row, not twelve.
Tenant settings in JSONB column instead of a settings table — avoids a join on every request and lets feature flags ship without migrations.
Soft-delete tenants (deleted_at) with a scheduled hard-purge job — GDPR's 30-day window is exactly this pattern, and it lets you reverse accidental deletions.

Likely follow-ups

· A single tenant hits 80% of the DB's IOPS (Input/Output Operations Per Second) — how do you isolate them?
· Add per-tenant encryption keys without rebuilding queries.
· Run a migration that adds a column on a 50TB tenant table without locking.
· Tenant requests full export — what's the read pattern and how do you avoid the noisy neighbor problem during it?

Dive deeper

Multi-tenant questions are really about the isolation/density tradeoff: schema-per-tenant gives strong isolation but breaks at scale, shared schema gives density but demands disciplined enforcement. A senior candidate names the tradeoff out loud, picks shared+RLS for most cases, and reserves DB-per-tenant for the largest whales. They also recognize that tenant_id is the universal shard key and should prefix every index from day one.

All database design prompts