Multi-Tenant SaaS Database Schema
The shared-schema pattern keeps every tenant's rows in the same tables, distinguished by a leading tenant_id column on all eight tables: tenants, users, tenant_users, projects, tasks, invitations, api_keys, and events. Primary keys and foreign keys are composite (tenant_id, id) following the Citus and Azure guidance, which makes cross-tenant joins impossible at the database level and lets the tables be hash-sharded on tenant_id later. Every operational table indexes tenant_id first, so row-level-security policies and tenant-scoped queries never scan other tenants' rows. The events table rounds out the design with a jsonb metadata column for tenant-specific fields without schema churn.
Last updated: 2026-09-27
Source: Original design by dbdiagramr, following the Citus / Azure shared-schema pattern
Tables in the Multi-Tenant SaaS schema
| Column | Type | Nullable | Key |
|---|---|---|---|
| tenants | |||
| id | uuid | No | PK |
| name | varchar | No | - |
| slug | varchar | No | - |
| plan | varchar | No | - |
| created_at | timestamptz | No | - |
| users | |||
| id | uuid | No | PK |
| varchar | No | - | |
| name | varchar | Yes | - |
| created_at | timestamptz | No | - |
| tenant_users | |||
| tenant_id | uuid | No | - |
| user_id | uuid | No | - |
| role | varchar | No | - |
| joined_at | timestamptz | No | - |
| projects | |||
| tenant_id | uuid | No | - |
| id | uuid | No | PK |
| name | varchar | No | - |
| status | varchar | No | - |
| created_by | uuid | Yes | - |
| created_at | timestamptz | No | - |
| tasks | |||
| tenant_id | uuid | No | - |
| id | uuid | No | PK |
| project_id | uuid | No | - |
| title | varchar | No | - |
| status | varchar | No | - |
| assignee_id | uuid | Yes | - |
| due_at | timestamptz | Yes | - |
| created_at | timestamptz | No | - |
| invitations | |||
| tenant_id | uuid | No | - |
| id | uuid | No | PK |
| varchar | No | - | |
| role | varchar | No | - |
| token | varchar | No | - |
| expires_at | timestamptz | No | - |
| accepted_at | timestamptz | Yes | - |
| api_keys | |||
| tenant_id | uuid | No | - |
| id | uuid | No | PK |
| name | varchar | No | - |
| key_hash | varchar | No | - |
| key_prefix | varchar | Yes | - |
| revoked_at | timestamptz | Yes | - |
| created_at | timestamptz | No | - |
| events | |||
| tenant_id | uuid | No | - |
| id | uuid | No | PK |
| actor_id | uuid | Yes | - |
| action | varchar | No | - |
| entity | varchar | Yes | - |
| entity_id | uuid | Yes | - |
| metadata | jsonb | Yes | - |
| created_at | timestamptz | No | - |
Frequently asked questions
What is the shared-schema multi-tenancy pattern?
All tenants share one database and one set of tables, with a tenant_id column on every table identifying the owner of each row. It has the lowest operational overhead - one migration covers all tenants - at the cost of relying on the application and row-level security for isolation.
Why make primary keys composite (tenant_id, id)?
Composite keys guarantee uniqueness per tenant rather than globally, and composite foreign keys (tenant_id, project_id) make it structurally impossible for one tenant's rows to reference another tenant's rows. It also prepares the tables for hash-sharding on tenant_id, which colocates each tenant's data for cheap distributed joins.
How do tenant-specific custom fields work in a shared schema?
With a jsonb metadata column (as on the events table) for flexible per-tenant fields, indexed with GIN or expression indexes where queried. This avoids per-tenant schema churn while keeping the relational core strict.
Explore more schemas
Visualize your own database
Paste your PostgreSQL connection string and get an interactive ER diagram of your own schema in under 10 seconds. No signup required.
Try it free →