A multi-tenant SaaS database schema has one job that a single-customer schema does not: every customer’s rows live in the same tables, and none of them may ever be read, changed or referenced by another customer. This one models a SaaS product sold to organisations in 17 tables with 19 foreign keys, 2 link tables and 5 context views: accounts, authentication, access, billing and the tenant’s own work. It was designed on PostgreSQL 18 and converted to MySQL 8.4, and both SQL files are linked beside the article, ready to import. Below: the decision the design turns on, what each table is for, one sign-in followed through four queries, what changes on MySQL, and five things to fix before you use it. Every column is in the live diagram above the article and in the reference at the end.
A row cannot point across tenants
Twelve of the seventeen tables carry tenant_id. That much every multi-tenant tutorial does. What this schema adds is that the foreign keys carry it too. members, roles, projects, subscriptions and identity_providers each declare UNIQUE (tenant_id, id), and the tables that reference them use a two-column key: tasks (tenant_id, assignee_id) references members (tenant_id, id). One tenant_id column on the child has to match the parent’s, so the pair must exist in the parent as one row. Eleven of the 19 foreign keys are built this way.
The usual shortcut is a plain assignee_id pointing at members.id, with the tenant checked in application code. Both rows then exist, the key is satisfied, and a task in one tenant can be assigned to a person in another. On PostgreSQL 18.3 this schema refuses it:
INSERT INTO tasks (tenant_id, project_id, assignee_id, title)
VALUES (1, 1, 2, 'Acme task for Ben of Globex');
-- ERROR: insert or update on table "tasks" violates foreign key constraint
-- "tasks_tenant_id_assignee_id_fkey"
-- DETAIL: Key (tenant_id, assignee_id)=(1, 2) is not present in table "members".
The same holds for giving a member another tenant’s role, and for linking a member to another tenant’s identity provider. The check is an ordinary foreign key, so it applies to migrations, admin scripts and the next service someone writes. Why row-level security alone cannot do this, and the traps in keys of this shape, are measured in composite foreign keys vs row-level security.
What tables does a multi-tenant SaaS database need?
Seventeen, in five groups. The columns worth knowing are listed here; the full reference is at the end.
| Table | One row is | Worth knowing |
|---|---|---|
tenants | a customer organisation | code unique, typed at sign-in; status: active, suspended, closed |
users | a person | global, no tenant_id; email unique ignoring case |
members | a user inside one tenant | one per user per tenant; status: active, deactivated |
password_credentials | the password a member signs in with | one per member, none for an SSO-only member; failed_attempts, locked_until |
identity_providers | an SSO provider a tenant connected | protocol: oidc, saml; name and issuer unique per tenant |
sso_identities | a member as one provider knows them | subject unique per provider; one identity per member per provider |
sessions | a signed-in member | method: password, sso; live while revoked_at is NULL and expires_at is ahead |
api_keys | a secret that acts as a member | only key_hash stored, unique; revoked_at NULL while valid |
roles | a named set of permissions in one tenant | name unique per tenant: each tenant has its own Admin |
permissions | something the product can do | global; key unique, such as projects.write |
roles_permissions | one permission granted to one role | composite key (role_id, permission_id), no id |
member_roles | one role held by one member | own id; unique on (member_id, role_id) |
plans | what is for sale | global; price, billing_interval: month, year; seat_limit |
subscriptions | a tenant on a plan for a period | one per tenant that is not canceled; status: trialing, active, past_due, canceled |
invoices | what a tenant owes for one period | copies amount_due and currency; number unique per tenant; status: draft, open, paid, void |
projects | a project owned by one member | name unique per tenant |
tasks | one piece of work in a project | assignee_id NULL when unassigned; status: todo, in_progress, done |
All 19 relationships are one-to-many, and every foreign key column is NOT NULL except tasks.assignee_id. Four tables are global and carry no tenant_id: users, permissions, plans and tenants itself. roles_permissions has none either, because its role already belongs to one tenant and a permission belongs to none. The one many-to-many inside a tenant, members to roles, and the one that reaches outside it, roles to permissions, are each resolved through a link table.
The groups are drawn as coloured boxes on the diagram, and each is also a context view: a saved, focused view of the schema that shows only a subset of entities and the relationships between them. Accounts holds tenants, users and members. Authentication holds the five tables that answer “who is this?”. Access holds the four that answer “what may they do?”. Billing holds plans, subscriptions and invoices, and workspace holds the tenant’s projects and tasks. Ten foreign keys cross between groups, and every one of them points into accounts: seven at members and three at tenants. No other two groups reference each other, so authentication, access, billing and workspace can each change without the others noticing. That is the reading the Context Map applies to a whole schema.
Following one sign-in through the SaaS
Four questions the product asks on every request or every billing run, answered by the schema as it is. Each query ran unchanged on PostgreSQL 18.3 and MySQL 8.4.11 against the two download files, with two tenants, Acme and Globex, and two people: Ana, who belongs to both, and Ben, who belongs to Globex.
Ana opens the Globex sign-in page and types the tenant code, her email with a capital letter, and a password. Who is she here, and what hash do we compare?
SELECT m.id AS member_id, pc.password_hash, pc.locked_until
FROM tenants t
JOIN members m ON m.tenant_id = t.id
JOIN users u ON u.id = m.user_id
JOIN password_credentials pc ON pc.member_id = m.id
WHERE t.code = 'globex'
AND LOWER(u.email) = LOWER('[email protected]')
AND t.status = 'active' AND m.status = 'active';
It returns member 3 and her Globex hash. Her Acme membership is member 1 with a different hash: the password belongs to the membership, not to the person. Ben has no row here at all, because he signs in through Globex’s identity provider, and his sso_identities row is what matches the subject the provider sends. Signed in, Ana asks for a page. What may this member do?
SELECT p.key
FROM member_roles mr
JOIN roles_permissions rp ON rp.role_id = mr.role_id
JOIN permissions p ON p.id = rp.permission_id
WHERE mr.tenant_id = 2 AND mr.member_id = 3
ORDER BY p.key;
Four keys come back, billing.manage, members.invite, projects.read and projects.write: she is an Admin at Globex. The same query for member 1 in tenant 1 returns only projects.read, because at Acme she is a Viewer. The same person, two tenants, two answers, and no column on users could have held both. An Admin wants to add a colleague. How many seats are used, against the plan?
SELECT t.code, COUNT(m.id) AS seats_used, p.seat_limit
FROM tenants t
JOIN subscriptions s ON s.tenant_id = t.id AND s.status <> 'canceled'
JOIN plans p ON p.id = s.plan_id
LEFT JOIN members m ON m.tenant_id = t.id AND m.status = 'active'
GROUP BY t.code, p.seat_limit
ORDER BY t.code;
Acme uses 1 of 2 and Globex 2 of 2. Seats are counted from members, not read from a stored number, so the count cannot fall out of step with the people. Finally, an owner reviews security. Who is signed in to this tenant right now?
SELECT u.email, s.method, s.created_at
FROM sessions s
JOIN members m ON m.id = s.member_id
JOIN users u ON u.id = m.user_id
WHERE s.tenant_id = 2
AND s.revoked_at IS NULL AND s.expires_at > CURRENT_TIMESTAMP
ORDER BY u.email;
Ana by password and Ben by sso. A session has no status column: it is live while it is neither revoked nor expired, and the two timestamps say so.
Why is it built this way?
A user is a person; a member is that person in one tenant. users holds the email and the name once. members is one row per user per tenant (UNIQUE (tenant_id, user_id)), and everything a person does inside a tenant points at the member: roles, sessions, API keys, projects, tasks. Deactivating someone at one company leaves their other memberships alone. The reasoning for that split is derived step by step in the multi-tenant RBAC data model.
Authentication is its own group, apart from access. Who you are and what you may do change for different reasons. A tenant that turns on single sign-on adds an identity_providers row and touches nothing in roles. The password sits in password_credentials instead of a column on members, so “this member has no password” is the absence of a row, not an empty string that has to be special-cased.
Roles belong to a tenant; permissions do not. Each tenant names and shapes its own roles, so roles carries tenant_id and UNIQUE (tenant_id, name). Permissions are defined by the code, so they are the same rows for everyone. Why a permission is not tenant data is argued in the roles and permissions design.
The two link tables have different shapes on purpose. roles_permissions is two columns, both in the primary key. member_roles has its own id, a unique constraint on (member_id, role_id), and tenant_id, with both of its foreign keys carrying the tenant. member_id alone already names one member, so the unique pair is enough; the tenant column is there so a member can only hold a role of their own tenant.
Deletion is decided per key. A member’s passwords, identities, sessions, API keys and role assignments go with the member (CASCADE). A member who owns a project cannot be deleted (RESTRICT), and neither can a tenant, a plan in use, or a subscription with invoices: billing history is the record. A task outlives its assignee: ON DELETE SET NULL (assignee_id) clears that one column and leaves tenant_id in place, where a plain SET NULL would try to clear the tenant as well. How to pick between those actions is covered in ON DELETE SET NULL vs CASCADE vs RESTRICT.
One live subscription per tenant is an index, not a hope. A partial unique index on subscriptions (tenant_id) WHERE status <> 'canceled' refused a second trial for Acme while its first subscription was active, and accepted a new one once the first was canceled. Statuses are check constraints rather than enum types or lookup tables, money is NUMERIC, and an invoice copies amount_due and currency so a later price change does not rewrite what was billed.
What changes when the schema is converted to MySQL?
The tables, columns, unique constraints and checks carry over one to one: Schemity wrote the MySQL file from the PostgreSQL design, and imported into empty databases the two files produced the same 17 tables, 19 foreign keys and 12 check constraints on MySQL 8.4.11 and PostgreSQL 18.3. Three differences change behaviour, and each is listed as a comment at the top of the MySQL file.
| PostgreSQL | MySQL | What it means |
|---|---|---|
ON DELETE SET NULL (assignee_id) | ON DELETE NO ACTION | MySQL has no column list, and refuses a plain SET NULL on a key that holds a NOT NULL column (error 1830). Deleting a member who still has tasks is refused until the tasks are unassigned |
Partial unique index on subscriptions | left out | MySQL has no partial index. A second live subscription for Acme went in until the index below was added |
Unique index on lower(email) | left out | Not needed: under the default collation utf8mb4_0900_ai_ci, the plain unique constraint already refused [email protected] |
TEXT on the 12 unique or indexed columns | VARCHAR(255) | MySQL cannot index a TEXT column without a prefix length |
TIMESTAMPTZ (40 columns) | DATETIME(6) | MySQL keeps no time zone: store UTC |
On MySQL, removing a member therefore takes one statement more, and restoring the subscription rule takes one index:
-- MySQL: unassign, then delete
UPDATE tasks SET assignee_id = NULL WHERE tenant_id = 2 AND assignee_id = 3;
DELETE FROM members WHERE id = 3;
-- MySQL: one subscription per tenant that is not canceled
CREATE UNIQUE INDEX subscriptions_tenant_id_live_key
ON subscriptions ((IF(status <> 'canceled', tenant_id, NULL)));
With the index, the second live subscription fails with Duplicate entry '1' for key 'subscriptions.subscriptions_tenant_id_live_key', and a new one is accepted after the first is canceled. MySQL also builds an index of its own for the password_credentials key to members, because none is declared that starts with tenant_id; PostgreSQL builds none and has the unique index on member_id to find the row.
Five things to fix before you use it
The foreign keys are complete. These five are rules the tables do not hold yet. Each was tested: the schema accepted what it should not, and with the fix the bad row fails and a legitimate one still goes in.
1. Reads are not isolated. The keys stop a row from pointing across tenants; they do nothing about a query that forgets its filter. SELECT tenant_id, title FROM tasks returned both tenants’ tasks. On PostgreSQL, row-level security closes that for every table you enable it on:
-- PostgreSQL
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
CREATE POLICY tasks_tenant ON tasks
USING (tenant_id = current_setting('app.tenant_id')::bigint);
As a role that does not own the table, with SET LOCAL app.tenant_id = '1', the same query returned Acme’s task only, and writing a Globex task failed with new row violates row-level security policy for table "tasks". MySQL has no row-level security, so there the filter stays in the application or in views.
2. Acme and acme are two tenants. On PostgreSQL the unique constraint on tenants.code compares exact text, so a second tenant registered Acme beside acme, and a sign-in form has to guess which was meant. users.email already has the fix; give the code the same. MySQL’s default collation refused the duplicate without it:
-- PostgreSQL
CREATE UNIQUE INDEX tenants_code_lower_key ON tenants (lower(code));
3. Deactivating a member leaves them signed in. Setting Ana’s Globex membership to deactivated blocks her next password sign-in, since the sign-in query checks m.status, but her session was still listed as live, and nothing in the schema revokes her API keys either. Revoke both in the same transaction as the status change; this runs unchanged on both databases:
UPDATE sessions SET revoked_at = CURRENT_TIMESTAMP
WHERE tenant_id = 2 AND member_id = 3 AND revoked_at IS NULL;
UPDATE api_keys SET revoked_at = CURRENT_TIMESTAMP
WHERE tenant_id = 2 AND member_id = 3 AND revoked_at IS NULL;
4. Two rows that contradict themselves were accepted. A subscription went in with its period ending a month before it started, and an invoice went in as paid with paid_at NULL. Both are facts about one row, so each is a check constraint, and both run unchanged on PostgreSQL and MySQL:
ALTER TABLE subscriptions ADD CONSTRAINT subscriptions_period_check
CHECK (current_period_end > current_period_start);
ALTER TABLE invoices ADD CONSTRAINT invoices_paid_check
CHECK ((status = 'paid') = (paid_at IS NOT NULL));
5. Two rules count rows, and no constraint can. A third active member joined Globex on a two-seat plan, and the seats query above then read 3 of 2. Removing every role assignment in Globex left it with no Admin at all. A check constraint sees one row and a foreign key sees one parent, so neither can say “at most seat_limit members” or “at least one Admin”. These belong in the transaction that adds a member or removes a role: lock the tenant’s row, run the count, then decide. Do it in application code that every path goes through, or in a trigger if more than one application writes to the database.
Open this schema in Schemity Lite
The diagram above the article is the same schema, live: open it to drag tables, read every column, right-click a table for its SQL, and export the diagram as an image. Exporting the whole schema as SQL, Mermaid or DBML is part of the desktop app. To edit your own copy, open the multi-tenant SaaS schema in Schemity Lite. To start from a real database instead, the desktop database design software reverse engineers a live PostgreSQL or MySQL schema into the same kind of diagram, and drawing a relationship onto a table with UNIQUE (tenant_id, id) creates the two-column tenant key for you.
Full column reference
Every column, type, key and default in the 17 tables, as written in the PostgreSQL file
tenants
A customer organisation. Every tenant-owned row carries its id. Closed by status, never deleted.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
code | TEXT | no | unique | |
name | TEXT | no | ||
status | TEXT | no | default active; check: active, suspended, closed | |
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
users
A person who can sign in. Global: one row whatever the number of tenants they belong to. Email is unique ignoring case.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
email | TEXT | no | unique; unique index on lower(email) | |
first_name | TEXT | no | ||
last_name | TEXT | no | ||
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
members
A user inside one tenant. Everything a person does in a tenant points here, not at users. UNIQUE (tenant_id, id) is the target of every tenant-carrying foreign key.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity; unique with tenant_id: the target of tenant keys |
tenant_id | BIGINT | FK | no | to tenants.id, ON DELETE RESTRICT |
user_id | BIGINT | FK | no | to users.id, ON DELETE RESTRICT; unique with tenant_id |
status | TEXT | no | default active; check: active, deactivated | |
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
Indexes: (user_id).
password_credentials
The password a member signs in with, next to the tenant code and email. One per member at most: a member who only uses SSO has no row. Only the hash is stored.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
member_id | BIGINT | FK | no | with tenant_id to members (tenant_id, id), ON DELETE CASCADE; unique |
tenant_id | BIGINT | FK | no | tenant half of the key to members |
failed_attempts | INTEGER | no | default 0; check: failed_attempts >= 0 | |
locked_until | TIMESTAMPTZ | yes | ||
password_hash | TEXT | no | ||
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
identity_providers
A single sign-on provider a tenant has connected, OIDC or SAML. Its secrets are kept in a secret store, not here.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity; unique with tenant_id: the target of tenant keys |
tenant_id | BIGINT | FK | no | to tenants.id, ON DELETE RESTRICT |
issuer | TEXT | no | unique with tenant_id | |
name | TEXT | no | unique with tenant_id | |
client_id | TEXT | no | default '' | |
enabled | BOOLEAN | no | default TRUE | |
protocol | TEXT | no | check: oidc, saml | |
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
sso_identities
A member as one identity provider knows them: the subject the provider sends at sign-in. The provider and the member must be of the same tenant.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
identity_provider_id | BIGINT | FK | no | with tenant_id to identity_providers (tenant_id, id), ON DELETE CASCADE; unique with subject; unique with member_id |
member_id | BIGINT | FK | no | with tenant_id to members (tenant_id, id), ON DELETE CASCADE; unique with identity_provider_id |
tenant_id | BIGINT | FK | no | tenant half of the key to identity_providers and members |
subject | TEXT | no | unique with identity_provider_id | |
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
Indexes: (tenant_id, identity_provider_id), (tenant_id, member_id).
sessions
A signed-in member. Records how they signed in, and ends at expires_at or when revoked. Only the token’s hash is stored.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
member_id | BIGINT | FK | no | with tenant_id to members (tenant_id, id), ON DELETE CASCADE |
tenant_id | BIGINT | FK | no | tenant half of the key to members |
expires_at | TIMESTAMPTZ | no | ||
method | TEXT | no | check: password, sso | |
revoked_at | TIMESTAMPTZ | yes | ||
token_hash | TEXT | no | unique | |
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
Indexes: (tenant_id, member_id).
api_keys
A secret that acts as the member who made it. Only its hash is stored. Removed with the member.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
member_id | BIGINT | FK | no | with tenant_id to members (tenant_id, id), ON DELETE CASCADE |
tenant_id | BIGINT | FK | no | tenant half of the key to members |
key_hash | TEXT | no | unique | |
key_prefix | TEXT | no | ||
name | TEXT | no | ||
last_used_at | TIMESTAMPTZ | yes | ||
revoked_at | TIMESTAMPTZ | yes | ||
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
Indexes: (tenant_id, member_id).
roles
A named set of permissions owned by one tenant. Each tenant has its own Admin.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity; unique with tenant_id: the target of tenant keys |
tenant_id | BIGINT | FK | no | to tenants.id, ON DELETE RESTRICT |
name | TEXT | no | unique with tenant_id | |
description | TEXT | no | default '' | |
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
permissions
Something the software can do, such as projects.write. Global: defined by the code, not by a tenant.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
key | TEXT | no | unique | |
description | TEXT | no | default '' | |
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
roles_permissions
One permission granted to one role.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
permission_id | BIGINT | PK, FK | no | to permissions.id, ON DELETE RESTRICT |
role_id | BIGINT | PK, FK | no | to roles.id, ON DELETE CASCADE |
Indexes: (permission_id).
member_roles
One role held by one member. Both foreign keys carry tenant_id, so a member can only hold a role of their own tenant.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
tenant_id | BIGINT | FK | no | tenant half of the key to members and roles |
member_id | BIGINT | FK | no | with tenant_id to members (tenant_id, id), ON DELETE CASCADE; unique with role_id |
role_id | BIGINT | FK | no | with tenant_id to roles (tenant_id, id), ON DELETE CASCADE; unique with member_id |
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
Indexes: (tenant_id, member_id), (tenant_id, role_id).
plans
What is for sale: a price, a billing interval and a seat limit. Global.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
code | TEXT | no | unique | |
billing_interval | TEXT | no | default month; check: month, year | |
currency | VARCHAR(3) | no | default USD | |
name | TEXT | no | ||
price | NUMERIC(10,2) | no | check: price >= 0 | |
seat_limit | INTEGER | no | check: seat_limit > 0 | |
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
subscriptions
A tenant on a plan for a billing period. One subscription that is not canceled per tenant.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity; unique with tenant_id: the target of tenant keys |
tenant_id | BIGINT | FK | no | to tenants.id, ON DELETE RESTRICT; one row per tenant where status <> 'canceled' (partial unique index) |
plan_id | BIGINT | FK | no | to plans.id, ON DELETE RESTRICT |
canceled_at | TIMESTAMPTZ | yes | ||
current_period_end | TIMESTAMPTZ | no | ||
current_period_start | TIMESTAMPTZ | no | ||
status | TEXT | no | default trialing; check: trialing, active, past_due, canceled | |
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
Indexes: (plan_id).
invoices
What a tenant owes for one period of a subscription. Copies the amount and currency, so a later price change does not rewrite it.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
subscription_id | BIGINT | FK | no | with tenant_id to subscriptions (tenant_id, id), ON DELETE RESTRICT |
tenant_id | BIGINT | FK | no | tenant half of the key to subscriptions |
number | TEXT | no | unique with tenant_id | |
amount_due | NUMERIC(12,2) | no | check: amount_due >= 0 | |
currency | VARCHAR(3) | no | ||
period_end | TIMESTAMPTZ | no | ||
period_start | TIMESTAMPTZ | no | ||
status | TEXT | no | default draft; check: draft, open, paid, void | |
paid_at | TIMESTAMPTZ | yes | ||
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
Indexes: (tenant_id, subscription_id).
projects
The tenant’s own work: a project owned by one member. Names are unique per tenant.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity; unique with tenant_id: the target of tenant keys |
owner_id | BIGINT | FK | no | with tenant_id to members (tenant_id, id), ON DELETE RESTRICT |
tenant_id | BIGINT | FK | no | tenant half of the key to members |
name | TEXT | no | unique with tenant_id | |
description | TEXT | no | default '' | |
archived_at | TIMESTAMPTZ | yes | ||
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
Indexes: (tenant_id, owner_id).
tasks
One piece of work in a project, optionally assigned to a member of the same tenant. Stays, unassigned, when the assignee is removed.
| Column | Type | Key | Nullable | Notes |
|---|---|---|---|---|
id | BIGINT | PK | no | identity |
assignee_id | BIGINT | FK | yes | with tenant_id to members (tenant_id, id), ON DELETE SET NULL (assignee_id) |
project_id | BIGINT | FK | no | with tenant_id to projects (tenant_id, id), ON DELETE CASCADE |
tenant_id | BIGINT | FK | no | tenant half of the key to members and projects |
title | TEXT | no | ||
status | TEXT | no | default todo; check: todo, in_progress, done | |
due_on | DATE | yes | ||
completed_at | TIMESTAMPTZ | yes | ||
created_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP | |
updated_at | TIMESTAMPTZ | no | default CURRENT_TIMESTAMP |
Indexes: (tenant_id, assignee_id), (tenant_id, project_id).
Frequently asked questions
What tables does a multi-tenant SaaS database need?
This schema uses 17: tenants, users and members for accounts; password_credentials, identity_providers, sso_identities, sessions and api_keys for sign-in; roles, permissions, roles_permissions and member_roles for access; plans, subscriptions and invoices for billing; and projects and tasks for the tenant's own work.
How do you stop a row from referencing another tenant's data?
Put tenant_id in the foreign key. Here members, roles, projects, subscriptions and identity_providers each have UNIQUE (tenant_id, id), and the tables that reference them use a two-column key such as (tenant_id, assignee_id) to members (tenant_id, id). On PostgreSQL 18.3, assigning an Acme task to a Globex member fails with a foreign key violation, with no policy or application check involved.
Should the password be stored on the user or on the tenant membership?
On the membership. A person signs in with a tenant code, an email and a password, and one person can belong to several tenants, so this schema keeps one password_credentials row per member. A member who signs in only through single sign-on has no row at all, which a password column on users or members could not say.
Can I use this multi-tenant schema on MySQL?
Yes, with three differences. The MySQL file imports into MySQL 8.4.11 as it is, with the same 17 tables, 19 foreign keys and 12 check constraints. MySQL has no ON DELETE SET NULL limited to one column, so the task assignee key becomes NO ACTION; it has no partial index, so the one-live-subscription rule needs a unique index on an expression; and its default collation already compares emails and tenant codes without case.