# Shared Tables with a tenant_id — Multi-Tenant SaaS Architecture

Source: https://www.skillbyai.com/en/multi-tenant-saas/mt-shared-schema

> Design shared tables with tenant_id in keys, indexes and foreign keys so that the database itself prevents cross-tenant references.

## Every row knows its tenant

In the **shared-schema** (pool) pattern, all tenants' rows live in the same tables, and each row carries a `tenant_id` column. This is the densest and simplest option to operate: one schema, one migration run, one set of indexes. Its risk is obvious: isolation depends on every query filtering by `tenant_id`. Good schema design reduces that risk. Put `tenant_id` **first in primary keys and indexes**, for example `(tenant_id, id)`, so per-tenant queries use the index efficiently. Make **foreign keys composite**, `(tenant_id, project_id)` referencing `(tenant_id, id)`, so a task physically cannot point at another tenant's project. Make uniqueness per tenant, `UNIQUE (tenant_id, name)`, because two companies can both have a project called "Website". Finally, add database-enforced filtering such as row-level security, which the next section covers.

## A shared schema with composite keys

PostgreSQL. The composite foreign key makes cross-tenant links impossible, not just unlikely.

```sql
CREATE TABLE tenants (
  id   uuid PRIMARY KEY,
  name text NOT NULL
);

CREATE TABLE projects (
  tenant_id uuid   NOT NULL REFERENCES tenants (id),
  id        bigint GENERATED ALWAYS AS IDENTITY,
  name      text   NOT NULL,
  PRIMARY KEY (tenant_id, id),
  UNIQUE (tenant_id, name)          -- names unique per tenant, not globally
);

CREATE TABLE tasks (
  tenant_id  uuid   NOT NULL,
  id         bigint GENERATED ALWAYS AS IDENTITY,
  project_id bigint NOT NULL,
  title      text   NOT NULL,
  PRIMARY KEY (tenant_id, id),
  FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id)
);
```

## Global uniqueness surprises

A `UNIQUE (email)` on a shared users table means one tenant can block another from inviting the same address, and the error message even reveals that the email exists elsewhere. Decide deliberately which values are unique globally and which are unique per tenant.

**Quiz:** Why make the foreign key from tasks to projects `(tenant_id, project_id)` instead of just `project_id`?

- [x] So that a task can only reference a project in the same tenant
- [ ] Because PostgreSQL forbids single-column foreign keys
- [ ] To make the tasks table smaller
- [ ] So that projects can be deleted without deleting tasks

*Answer:* So that a task can only reference a project in the same tenant. With the composite key, the referenced project must have the same tenant_id as the task, so the database rejects cross-tenant links even if application code is buggy.