Shared Tables with a tenant_id

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.

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.

त्वरित जाँच: Why make the foreign key from tasks to projects `(tenant_id, project_id)` instead of just `project_id`?

• 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