Multi-tenant SaaS database schema
A shared-database / shared-schema multi-tenant model with a tenant_id on every domain table, Postgres row-level security to enforce isolation, and tenant-aware indexes that put tenant_id first.
Copy this prompt
Paste this into Raltey's AI agent to generate the same diagram, then tweak it on the canvas.
Design a PostgreSQL database schema for: Multi-tenant SaaS database schema. A shared-database / shared-schema multi-tenant model with a tenant_id on every domain table, Postgres row-level security to enforce isolation, and tenant-aware indexes that put tenant_id first. Include these tables: workspace, membership, user_account, project, document. Use proper primary keys, foreign keys, and indexes. Export SQL DDL for PostgreSQL.
Context
- - Shared database, shared schema, with tenant_id discriminator.
- - Row-level security policies enforce tenant isolation at the database layer.
- - Every domain index is prefixed with tenant_id for partition pruning.
Patterns
- - tenant_id (workspace_id) is the first column of every PK and most indexes.
- - CREATE POLICY policy on each table for ALL USING (workspace_id = current_setting('app.workspace_id')::uuid).
- - Connection pool sets app.workspace_id per session.
The schema, table by table
workspace
Table workspace {
id uuid pk
slug text unique not null
name text not null
plan text default `'free'`
}membership
Table membership {
workspace_id uuid [ref: > workspace.id]
user_id uuid [ref: > user_account.id]
role text not null // owner, admin, member
pk (workspace_id, user_id)
}user_account
Table user_account {
id uuid pk
email text unique not null
}project
Domain table - note the leading tenant_id.
Table project {
id uuid
workspace_id uuid [ref: > workspace.id, not null]
name text not null
created_at timestamptz default `now()`
pk (workspace_id, id)
}document
Table document {
id uuid
workspace_id uuid [ref: > workspace.id, not null]
project_id uuid not null
title text not null
body text
pk (workspace_id, id)
}Relationships
| From | To | Type |
|---|---|---|
| workspace | membership | 1:N |
| user_account | membership | 1:N |
| workspace | project | 1:N |
| workspace | document | 1:N |
Notes from the field
- Make tenant_id NOT NULL everywhere - a missing tenant_id is a bug, never a default.
- Use ALTER TABLE ... ENABLE ROW LEVEL SECURITY and a FORCE policy to prevent bypass by table owners.
- Index every FK that references a tenant-scoped table with tenant_id first.
Frequently asked
Should I use a shared database or one database per tenant?
Shared database, shared schema works for the vast majority of B2B SaaS up to thousands of tenants. Switch to database-per-tenant only when you have specific isolation, compliance, or noisy-neighbor reasons.
Are row-level security policies a performance hit?
Negligible in practice if your indexes lead with tenant_id. Postgres treats RLS as an additional WHERE clause; with the right indexes, the planner produces the same access path it would without RLS.
More templates
Open this template in Raltey
Edit it on the canvas, ask the AI agent to extend it, then share or export.
Start for free