← All schemas
Schema Library

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 the six tenant-owned tables: tenant_users, projects, tasks, invitations, api_keys, and events. tenants and users are global: a tenant row and a user row exist once, while tenant_users links them and carries the membership role. Primary keys stay single-column (id) for readability, and tenant_id is indexed first on every tenant-owned table. This is the simpler starting point: the database does not prevent cross-tenant references by itself. The Hardening for production section shows the composite unique key plus foreign key and the RLS policy that add that guarantee.

Written by Varun Krishnan · Founder, dbdiagramr

Published · Updated

Source: Original reference design by dbdiagramr for a PostgreSQL shared-schema multi-tenant app

Tables
8
Pattern
Shared schema + tenant_id
Isolation
tenant_id + RLS (add in production)
Multi-Tenant SaaS database schema diagram showing every table, column, and foreign key

Scroll horizontally to see the full diagram. Click it to open at full size.

Open in DBdiagramr to edit →

Embed this diagram

Paste this HTML into a README, docs page, or blog post. The image links back to the full schema page.

<a href="https://www.dbdiagramr.space/schema/multi-tenant-saas">
  <img src="https://www.dbdiagramr.space/schemas/multi-tenant-saas-er-diagram.svg" alt="Multi-Tenant SaaS Database Schema (PostgreSQL)" width="1515" height="956" />
</a>
<p><a href="https://www.dbdiagramr.space/schema/multi-tenant-saas">Multi-Tenant SaaS Database Schema (PostgreSQL)</a> by dbdiagramr</p>

Tables in the Multi-Tenant SaaS schema

ColumnTypeNullableKey
tenants
iduuidNoPK
namevarcharNo-
slugvarcharNo-
planvarcharNo-
created_attimestamptzNo-
users
iduuidNoPK
emailvarcharNo-
namevarcharYes-
created_attimestamptzNo-
tenant_users
tenant_iduuidNoPK, FK → tenants.id
user_iduuidNoPK, FK → users.id
rolevarcharNo-
joined_attimestamptzNo-
projects
tenant_iduuidNoFK → tenants.id
iduuidNoPK
namevarcharNo-
statusvarcharNo-
created_byuuidYesFK → users.id
created_attimestamptzNo-
tasks
tenant_iduuidNoFK → tenants.id
iduuidNoPK
project_iduuidNoFK → projects.id
titlevarcharNo-
statusvarcharNo-
assignee_iduuidYesFK → users.id
due_attimestamptzYes-
created_attimestamptzNo-
invitations
tenant_iduuidNoFK → tenants.id
iduuidNoPK
emailvarcharNo-
rolevarcharNo-
tokenvarcharNo-
expires_attimestamptzNo-
accepted_attimestamptzYes-
api_keys
tenant_iduuidNoFK → tenants.id
iduuidNoPK
namevarcharNo-
key_hashvarcharNo-
key_prefixvarcharYes-
revoked_attimestamptzYes-
created_attimestamptzNo-
events
tenant_iduuidNoFK → tenants.id
iduuidNoPK
actor_iduuidYesFK → users.id
actionvarcharNo-
entityvarcharYes-
entity_iduuidYes-
metadatajsonbYes-
created_attimestamptzNo-

Table relationships

10 relationships connect the tables in this schema. Each entry shows the child columns, the parent columns they reference, and the cardinality.

Many-to-many

tenant_users (tenant_id -> tenants.id, user_id -> users.id)

Many tenants can have many users through tenant_users (tenant_users.tenant_id -> tenants.id, tenant_users.user_id -> users.id).

One-to-many

projects.tenant_id → tenants.id

One tenant can have many projects (projects.tenant_id -> tenants.id, required).

One-to-many

projects.created_by → users.id

One user can have many projects (projects.created_by -> users.id, optional).

One-to-many

tasks.tenant_id → tenants.id

One tenant can have many tasks (tasks.tenant_id -> tenants.id, required).

One-to-many

tasks.project_id → projects.id

One project can have many tasks (tasks.project_id -> projects.id, required).

One-to-many

tasks.assignee_id → users.id

One user can have many tasks (tasks.assignee_id -> users.id, optional).

One-to-many

invitations.tenant_id → tenants.id

One tenant can have many invitations (invitations.tenant_id -> tenants.id, required).

One-to-many

api_keys.tenant_id → tenants.id

One tenant can have many api keys (api_keys.tenant_id -> tenants.id, required).

One-to-many

events.tenant_id → tenants.id

One tenant can have many events (events.tenant_id -> tenants.id, required).

One-to-many

events.actor_id → users.id

One user can have many events (events.actor_id -> users.id, optional).

Common queries

These PostgreSQL queries run against the schema shown on this page.

Projects and tasks per tenant

Count the projects and tasks owned by each tenant.

select t.name,
       count(distinct p.id) as projects,
       count(tk.id) as tasks
from tenants t
left join projects p on p.tenant_id = t.id
left join tasks tk on tk.tenant_id = t.id
group by t.id, t.name
order by t.name;

Tenant membership list

Show each tenant's members and their roles.

select t.name as tenant, u.email, tu.role, tu.joined_at
from tenant_users tu
join tenants t on t.id = tu.tenant_id
join users u on u.id = tu.user_id
order by t.name, u.email;

Open tasks by assignee

Count unfinished tasks for each assignee inside a tenant.

select u.email, count(t.id) as open_tasks
from users u
left join tasks t on t.assignee_id = u.id
  and t.status <> 'done'
  and t.tenant_id = '00000000-0000-0000-0000-000000000001'::uuid
group by u.id, u.email
order by open_tasks desc;

Recent events for a tenant

Read the audit-style event stream for one tenant.

select e.created_at, e.action, e.entity, e.entity_id, e.metadata
from events e
where e.tenant_id = '00000000-0000-0000-0000-000000000001'::uuid
order by e.created_at desc
limit 50;

Hardening for production

The DDL on this page is the simpler starting point. To stop one tenant's rows from referencing another tenant's rows, add a composite unique key to the parent and a composite foreign key on the child. This example hardens tasks.project_id:

alter table projects
  add constraint projects_tenant_id_id_key unique (tenant_id, id);

alter table tasks
  drop constraint fk_tasks_project_id;

alter table tasks
  add constraint tasks_tenant_project_fk
  foreign key (tenant_id, project_id)
  references projects (tenant_id, id);

The composite key makes the tenant part of the reference. A task can now point only at a project in its own tenant. Apply the same pattern to every tenant-owned foreign key.

Row-level security adds a second layer. Store the current tenant in a session setting, then filter every row through it:

alter table tasks enable row level security;

create policy tasks_tenant_isolation on tasks
  using (tenant_id = current_setting('app.tenant_id', true)::uuid)
  with check (tenant_id = current_setting('app.tenant_id', true)::uuid);

Set app.tenant_id on each request before querying, for example select set_config('app.tenant_id', $1, true);. If the setting is missing, current_setting(..., true) returns null and the policy matches no rows.

Frequently asked questions

Related schema diagrams

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 →