On this page

SaaS Multi-Tenant Schema Design

Multi-tenancy is the defining architectural challenge of SaaS applications. Every table, every query, every migration must account for tenant isolation. Get it wrong and you leak data between tenants — the kind of bug that ends companies. This page covers the three major strategies and provides a production-grade schema for the most common approach.

It follows the order you would design in: requirements and access patterns, the isolation strategy (and what each costs to operate and to migrate away from), the entities and tables, row-level security and how it interacts with connection pools, the hot-path queries and their indexes, the concurrency and noisy-neighbour hot spots, billing and entitlements, audit logs, and finally how the schema evolves as the largest tenants outgrow a shared database. If ER modelling is new, read ER Modeling & Schema Design first; for general OLTP principles see OLTP Database Design.

Requirements & Access Patterns

Assume a B2B project-management product: 25,000 tenants (about 22,000 on the free plan, 2,900 on paid self-serve plans, 100 enterprise contracts), 2 million users, and a heavily skewed distribution where the largest tenant holds 8% of all rows and the median tenant holds a few thousand.

OperationSteady ratePeak rateRead or writeLatency targetNotes
Resolve tenant from subdomain / token20,000/s60,000/sReadp99 < 5 msEvery request; cached
API key authentication8,000/s25,000/sReadp99 < 5 msLookup by key hash; cached
Permission check20,000/s60,000/sReadp99 < 5 msPer request; cached per session
Task list for a project (filter, sort, page)5,000/s15,000/sReadp99 < 100 msThe main screen
Create / update task1,500/s5,000/sWritep99 < 150 msWrites an audit row too
"My tasks" across projects1,000/s3,000/sReadp99 < 150 msBy assignee within tenant
Usage metering events50,000/s200,000/sWriteAsyncBuffered, aggregated before Postgres
Audit log writes3,000/s10,000/sWriteAsync-tolerantAppend-only, retained for years
Audit log search (admin console)20/s100/sReadp99 < 2 sPer tenant, time-bounded
Plan-limit checks (seats, projects)200/s1,000/sRead + writep99 < 50 msMust not be bypassable by concurrency
Billing run / invoice generationMonthly batchReadHoursReads usage aggregates
Tenant provisioning10/min500/minWriteSecondsSpikes after marketing campaigns
Cross-tenant support / analyticsRareReadSecondsSeparate role, ideally separate store

What this shapes:

  1. Every hot query is scoped to one tenant. That makes tenant_id the leading column of nearly every index and, later, the natural shard key.
  2. Tenants are wildly unequal. The schema, the planner statistics, and the capacity plan must all cope with one tenant being 10,000 times bigger than another. This is the noisy-neighbour problem.
  3. Isolation is a correctness property, not a performance one. One missing predicate is a data breach. The design must make the safe path the default path, enforced by the database.
  4. High-volume streams (usage, audit) behave differently from the core tables. They are append-only, time-ordered, and the first candidates for partitioning or a different store.

Hot queries and the indexes they need:

#Hot queryIndex
Q1Tenant by slugUNIQUE (slug) on tenants
Q2API key by hashUNIQUE (key_hash) on api_keys
Q3User by email within tenant (login)UNIQUE (tenant_id, email)
Q4Tasks in a project by status, by due date, paginated(tenant_id, project_id, status, due_date, id)
Q5Open tasks assigned to mepartial (tenant_id, assignee_id, due_date) WHERE status <> 'done'
Q6Roles and permissions for a useruser_roles (user_id) plus roles PK
Q7Audit log for a tenant, newest first, optional resource filter(tenant_id, created_at DESC), (tenant_id, resource_type, resource_id)
Q8Current-period usage for a tenant and metric(tenant_id, metric, period_start) on an aggregate table
Q9Active subscription and entitlements for a tenantsubscriptions exclusion index; plan_entitlements PK

Multi-Tenancy Strategies

graph TD
    subgraph "Strategy 1: Shared Database, Shared Schema"
        A["All tenants share tables<br/>tenant_id column on every row<br/>RLS enforces isolation"]
    end

    subgraph "Strategy 2: Shared Database, Separate Schemas"
        B["Each tenant gets a PostgreSQL schema<br/>tenant_a.users, tenant_b.users<br/>set search_path per connection"]
    end

    subgraph "Strategy 3: Separate Databases"
        C["Each tenant gets own database<br/>Complete isolation<br/>Independent scaling"]
    end

    style A fill:#e8f5e9,color:#000
    style B fill:#fff3e0,color:#000
    style C fill:#fce4ec,color:#000
StrategyIsolationComplexityCostWhen to Use
Shared schema + tenant_idLow (RLS-enforced)LowLowestMost SaaS apps, any number of tenants (thousands to millions)
Schema-per-tenantMediumMediumMediumRegulated industries, custom schemas; practical up to a few thousand tenants per database
Database-per-tenantHighestHighestHighestEnterprise customers requiring full isolation

Correction (2026-11-08)

An earlier version of this table recommended the shared schema for "<1000 tenants". That has it backwards. The shared schema is the model that scales best in tenant count: adding a tenant is inserting a row, and a million tenants is no harder than a thousand. It is schema-per-tenant and database-per-tenant that run into limits as tenants multiply, because every tenant adds catalog entries, migration runs, connections and backups. What limits the shared schema is the size of the largest tenants and the blast radius of a mistake, which is what the rest of this page addresses.

The #1 Multi-Tenant Bug

Forgetting WHERE tenant_id = ? on a single query leaks data across tenants. Row-Level Security (RLS) is your safety net — it enforces filtering at the database level, even if the application forgets.

Comparing the strategies in operation

The three options differ less in how the tables look and more in what they cost to run: schema changes, connections, backups, performance isolation, and moving a tenant later.

ConcernShared schema + tenant_idSchema per tenantDatabase per tenant
Add a tenantINSERT a rowCREATE SCHEMA + run all DDL (seconds)Provision a database (minutes), or pick from a warm pool
Schema migrationOnceN times; one failure leaves tenants on mixed versionsN times across N databases; needs an orchestrator and version tracking per tenant
Catalog sizeConstantGrows with N × tables × indexes; pg_dump, autovacuum and planning slow past a few thousand schemasPer database, fine
ConnectionsOne pool for everyonesearch_path per checkout; pool still sharedOne pool per database; connection count multiplies with tenants
Cross-tenant queries (support, analytics)Plain SQL with a bypass roleUNION ALL over N schemas, or ETLETL only
Restore one tenantHard: point-in-time restore elsewhere, copy rows backMedium: pg_dump -n schemaEasy: restore that database
Delete a tenant (GDPR)Batched deletes across tablesDROP SCHEMA ... CASCADEDROP DATABASE
Noisy neighbour isolationWeakest; shared buffers, CPU, locks, vacuumWeak; same instanceStrong if on separate instances
Per-tenant customizationVia JSONB or extension tablesTenant-specific columns possible (and dangerous)Anything
Data residencyNeeds a regional deployment per regionSamePer-tenant placement
Leak risk from a missing filterHighest, so RLS is mandatoryLow (wrong search_path still leaks)Lowest

Migration costs between models are the decision people underestimate:

  • Shared to database-per-tenant (moving one big customer out) is a copy: select every row with that tenant_id from every table, load it into a fresh database, then cut over. Because every row carries tenant_id and every foreign key is tenant-scoped, this is mechanical. This is the cheapest direction, and the reason to start shared.
  • Schema-per-tenant to shared means adding tenant_id to every table in every schema, merging N copies of each table into one, and resolving id collisions (each schema had its own sequences, so id = 1 exists 5,000 times). It is a rewrite of the data layer, usually measured in quarters.
  • Database-per-tenant to shared has the same id-collision problem plus N connection strings to retire. Teams that start here to get "isolation" often regret it at a few hundred tenants, when a single migration takes a day to roll out.

The pragmatic production pattern is a hybrid: everyone starts in the shared pool; tenants that need it (size, contract, residency) are moved to a dedicated database or shard, using the same schema with tenant_id still on every row. A routing table says where each tenant lives:

CREATE TABLE tenant_placements (
    tenant_id    UUID PRIMARY KEY REFERENCES tenants(id),
    cluster      TEXT NOT NULL,              -- 'pool-us-1', 'dedicated-acme', 'eu-pool-2'
    region       TEXT NOT NULL,              -- data residency
    tier         TEXT NOT NULL CHECK (tier IN ('pooled', 'dedicated')),
    read_only    BOOLEAN NOT NULL DEFAULT FALSE,   -- set during a move
    moved_at     TIMESTAMPTZ
);

This table lives in a small global control-plane database, is cached at every API node, and is consulted before a connection is chosen. Keeping the schema identical across pooled and dedicated placements is what makes moves cheap: the same code, the same migrations, and tenant_id in every row so a tenant can be copied out and back.

Schema Design (Shared Schema + tenant_id)

This is the most common approach. All tenants share the same tables, distinguished by a tenant_id column. PostgreSQL Row-Level Security enforces isolation.

Entity Relationship Overview

erDiagram
    TENANTS ||--o{ USERS : has
    TENANTS ||--o{ ROLES : defines
    TENANTS ||--o{ PROJECTS : owns
    TENANTS ||--|| BILLING : pays

    USERS }o--o{ ROLES : assigned
    USERS ||--o{ AUDIT_LOGS : generates

    PROJECTS ||--o{ TASKS : contains

    BILLING ||--o{ INVOICES : generates
    BILLING ||--o{ USAGE_RECORDS : tracks

    TENANTS ||--o{ API_KEYS : has

The extended model with plans, subscriptions and entitlements, and with tenant_id shown as part of the keys that matter:

erDiagram
    TENANTS ||--o{ SUBSCRIPTIONS : "subscribes"
    PLANS ||--o{ SUBSCRIPTIONS : "chosen in"
    PLANS ||--o{ PLAN_ENTITLEMENTS : "grants"
    FEATURES ||--o{ PLAN_ENTITLEMENTS : "granted as"
    TENANTS ||--o{ ENTITLEMENT_OVERRIDES : "has"
    FEATURES ||--o{ ENTITLEMENT_OVERRIDES : "overridden"
    TENANTS ||--o{ USAGE_COUNTERS : "meters"
    TENANTS ||--o{ PROJECTS : "owns"
    PROJECTS ||--o{ TASKS : "contains"
    USERS ||--o{ TASKS : "assigned"

    TENANTS {
        uuid id PK
        text slug UK
    }
    PLANS {
        text id PK "free, pro, enterprise"
        int price_cents
        text interval "month, year"
    }
    FEATURES {
        text key PK "max_users, sso, api_access"
        text kind "boolean or limit"
    }
    PLAN_ENTITLEMENTS {
        text plan_id PK, FK
        text feature_key PK, FK
        bigint limit_value "null means unlimited"
    }
    SUBSCRIPTIONS {
        uuid id PK
        uuid tenant_id FK
        text plan_id FK
        tstzrange active_during "no overlaps per tenant"
        text status
    }
    ENTITLEMENT_OVERRIDES {
        uuid tenant_id PK, FK
        text feature_key PK, FK
        bigint limit_value
        timestamptz expires_at
    }
    USAGE_COUNTERS {
        uuid tenant_id PK, FK
        text metric PK
        date period_start PK
        bigint quantity
    }
    PROJECTS {
        uuid tenant_id PK, FK
        uuid id PK
    }
    TASKS {
        uuid tenant_id PK, FK
        uuid id PK
        uuid project_id FK "same tenant"
        uuid assignee_id FK "same tenant"
    }

The key modelling idea, which the tables below implement: tenant-owned entities are weak entities of the tenant. Their identity is effectively (tenant_id, id), and every relationship between two tenant-owned entities carries tenant_id through, so a foreign key cannot point across tenants.

Tenants

CREATE TABLE tenants (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    name        TEXT NOT NULL,
    slug        TEXT NOT NULL UNIQUE,         -- subdomain: acme.app.com
    plan        TEXT NOT NULL DEFAULT 'free', -- 'free', 'pro', 'enterprise'
    settings    JSONB DEFAULT '{}',           -- feature flags, limits
    is_active   BOOLEAN DEFAULT TRUE,
    trial_ends_at TIMESTAMPTZ,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_tenants_slug ON tenants(slug);

idx_tenants_slug is redundant: slug ... UNIQUE already created a unique index on it. Slugs also become subdomains, so constrain them: CHECK (slug ~ '^[a-z0-9]([a-z0-9-]{0,61}[a-z0-9])?$'), and keep a reserved list (www, api, admin, status) out of the namespace.

Users & Authentication

CREATE TABLE users (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    email       TEXT NOT NULL,
    password_hash TEXT NOT NULL,
    full_name   TEXT NOT NULL,
    avatar_url  TEXT,
    is_active   BOOLEAN DEFAULT TRUE,
    last_login  TIMESTAMPTZ,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE (tenant_id, email)  -- email unique within tenant, not globally
);

CREATE INDEX idx_users_tenant ON users(tenant_id);
CREATE INDEX idx_users_email ON users(tenant_id, email);

Email Uniqueness Scope

In single-tenant apps, email is globally unique. In multi-tenant apps, the same email can exist in different tenants (e.g., alice@company.com is a user in both Acme Corp and Globex Corp tenants). The UNIQUE (tenant_id, email) constraint enforces this correctly.

Note (2026-11-08): two redundant indexes

idx_users_email (tenant_id, email) is an exact duplicate of the index behind UNIQUE (tenant_id, email), and idx_users_tenant (tenant_id) is covered by the same index's leftmost prefix. Neither helps any query; both slow every insert. Drop them.

Tenant-local users versus global accounts. The table above gives each tenant its own user rows: Alice in Acme and Alice in Globex are two users with two passwords. That is right for products where a tenant owns its users (an HR system, an internal tool behind SSO). Products where one person belongs to many workspaces (Slack, GitHub, Notion) use a global identity plus memberships:

CREATE TABLE accounts (                   -- global, not tenant-scoped
    id            UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    email         TEXT NOT NULL,
    password_hash TEXT,
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW()
);
CREATE UNIQUE INDEX uq_accounts_email ON accounts (lower(email));

CREATE TABLE memberships (                -- tenant-scoped
    tenant_id  UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    account_id UUID NOT NULL REFERENCES accounts(id) ON DELETE CASCADE,
    status     TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('invited', 'active', 'suspended')),
    joined_at  TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    PRIMARY KEY (tenant_id, account_id)
);
CREATE INDEX idx_memberships_account ON memberships (account_id);  -- "my workspaces"

Choose this early. Converting tenant-local users to global accounts later means deduplicating people by email across tenants, which surfaces every conflicting password, name and SSO identity.

Tenant-safe primary and foreign keys

A plain REFERENCES projects(id) lets a task in tenant A point at a project in tenant B, if a bug or a malicious API call supplies the wrong id. RLS hides the row on read, but the dangling cross-tenant reference is still stored. The fix is to make (tenant_id, id) the referenced key and to carry tenant_id in every foreign key:

-- Make (tenant_id, id) referenceable on parent tables.
ALTER TABLE users    ADD CONSTRAINT uq_users_tenant_id    UNIQUE (tenant_id, id);
ALTER TABLE projects ADD CONSTRAINT uq_projects_tenant_id UNIQUE (tenant_id, id);
ALTER TABLE roles    ADD CONSTRAINT uq_roles_tenant_id    UNIQUE (tenant_id, id);

-- A task's project must belong to the task's tenant.
ALTER TABLE tasks
    ADD CONSTRAINT fk_tasks_project_same_tenant
        FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id)
        ON DELETE CASCADE,
    -- PostgreSQL 15+: SET NULL only the assignee column, not tenant_id.
    ADD CONSTRAINT fk_tasks_assignee_same_tenant
        FOREIGN KEY (tenant_id, assignee_id) REFERENCES users (tenant_id, id)
        ON DELETE SET NULL (assignee_id);

The ON DELETE SET NULL (assignee_id) column list matters: a plain SET NULL on a composite key would try to null tenant_id as well and fail its NOT NULL. On PostgreSQL versions before 15, handle assignee removal in application code or a trigger. The same reasoning applies to the junction table user_roles, which in the original schema has no tenant_id at all and therefore cannot be protected by RLS directly; it gets one below.

UUIDs or bigints? Random UUIDv4 keys (the gen_random_uuid() default) scatter inserts across the whole B-tree, so every insert touches a random leaf page and the index's working set is the whole index. Time-ordered UUIDv7 keeps the benefits listed in the design-decisions table (globally unique, no enumeration, safe to merge tenants across databases) with insert locality close to a sequence. PostgreSQL 18 ships a uuidv7() function; on older versions generate them in the application. See ID Generation.

Roles & Permissions (RBAC)

CREATE TABLE roles (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,                -- 'admin', 'editor', 'viewer'
    permissions TEXT[] NOT NULL DEFAULT '{}',  -- ['projects.read', 'projects.write']
    is_default  BOOLEAN DEFAULT FALSE,
    created_at  TIMESTAMPTZ DEFAULT NOW(),

    UNIQUE (tenant_id, name)
);

CREATE TABLE user_roles (
    user_id  UUID NOT NULL REFERENCES users(id) ON DELETE CASCADE,
    role_id  UUID NOT NULL REFERENCES roles(id) ON DELETE CASCADE,

    PRIMARY KEY (user_id, role_id)
);

-- Seed default roles per tenant
-- INSERT INTO roles (tenant_id, name, permissions, is_default) VALUES
--   ($tenant_id, 'admin', ARRAY['*'], false),
--   ($tenant_id, 'member', ARRAY['projects.read','projects.write','tasks.read','tasks.write'], true),
--   ($tenant_id, 'viewer', ARRAY['projects.read','tasks.read'], false);

CREATE INDEX idx_roles_tenant ON roles(tenant_id);
CREATE INDEX idx_user_roles_user ON user_roles(user_id);

Permission check query:

-- Check if user has a specific permission
SELECT EXISTS (
    SELECT 1
    FROM user_roles ur
    JOIN roles r ON r.id = ur.role_id
    WHERE ur.user_id = $user_id
      AND (
          r.permissions @> ARRAY['*']           -- wildcard admin
          OR r.permissions @> ARRAY[$permission] -- specific permission
      )
) AS has_permission;

Hardening the RBAC tables:

-- Give the junction table its tenant, so RLS and same-tenant FKs apply.
ALTER TABLE user_roles
    ADD COLUMN tenant_id UUID NOT NULL,
    ADD CONSTRAINT fk_user_roles_user FOREIGN KEY (tenant_id, user_id)
        REFERENCES users (tenant_id, id) ON DELETE CASCADE,
    ADD CONSTRAINT fk_user_roles_role FOREIGN KEY (tenant_id, role_id)
        REFERENCES roles (tenant_id, id) ON DELETE CASCADE;

-- Exactly one default role per tenant.
CREATE UNIQUE INDEX uq_roles_one_default ON roles (tenant_id) WHERE is_default;

idx_user_roles_user is redundant with the primary key (user_id, role_id), and idx_roles_tenant with UNIQUE (tenant_id, name); both can go. The permission check is two primary-key-driven probes and runs 20,000 times a second, so cache its result per session (keyed by user and a role-version number that bumps on any role change) rather than making it faster in SQL. If permissions grow into resource-level sharing ("Bob can edit project X"), add a grants (tenant_id, subject_id, resource_type, resource_id, permission) table; that is the point where RBAC becomes ReBAC, and dedicated authorization services (Zanzibar-style) start to make sense.

Application Data (Projects & Tasks)

CREATE TABLE projects (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    description TEXT,
    status      TEXT NOT NULL DEFAULT 'active',  -- 'active', 'archived'
    owner_id    UUID REFERENCES users(id) ON DELETE SET NULL,
    settings    JSONB DEFAULT '{}',
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE tasks (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    project_id  UUID NOT NULL REFERENCES projects(id) ON DELETE CASCADE,
    title       TEXT NOT NULL,
    description TEXT,
    status      TEXT NOT NULL DEFAULT 'todo',    -- 'todo', 'in_progress', 'done'
    priority    SMALLINT DEFAULT 0,
    assignee_id UUID REFERENCES users(id) ON DELETE SET NULL,
    due_date    DATE,
    created_at  TIMESTAMPTZ DEFAULT NOW(),
    updated_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_projects_tenant ON projects(tenant_id);
CREATE INDEX idx_tasks_tenant ON tasks(tenant_id);
CREATE INDEX idx_tasks_project ON tasks(project_id);
CREATE INDEX idx_tasks_assignee ON tasks(assignee_id) WHERE assignee_id IS NOT NULL;

The single-column indexes work at small scale. The hot queries want composites that lead with tenant_id and then follow the query's equality-then-sort shape:

ALTER TABLE tasks
    ADD CONSTRAINT chk_task_status CHECK (status IN ('todo', 'in_progress', 'done')),
    ADD COLUMN version INT NOT NULL DEFAULT 0;        -- optimistic locking for edits

-- Q4: tasks in a project, filtered by status, sorted by due date, keyset-paginated.
CREATE INDEX idx_tasks_project_board
    ON tasks (tenant_id, project_id, status, due_date, id);

-- Q5: "my open tasks", soonest due first. Done tasks (the majority) are excluded.
CREATE INDEX idx_tasks_my_open
    ON tasks (tenant_id, assignee_id, due_date)
    WHERE status <> 'done' AND assignee_id IS NOT NULL;

Why tenant_id first even though project_id is already unique to one tenant? Three reasons: the RLS predicate always supplies it, so the index serves the exact predicate the database evaluates; it keeps each tenant's entries physically clustered in the index, so a tenant's working set is contiguous pages rather than scattered ones; and it is the shard key later, so composite indexes and keys already match the distribution. With these in place, idx_tasks_project, idx_tasks_assignee and idx_tasks_tenant are redundant. The version column supports optimistic locking for the "two people edit the same task" case; see Transactions, ACID & Concurrent Updates.

Billing & Usage

CREATE TABLE billing (
    tenant_id       UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
    stripe_customer TEXT,                      -- external billing provider ID
    plan            TEXT NOT NULL DEFAULT 'free',
    billing_email   TEXT NOT NULL,
    payment_method  JSONB,                     -- last4, brand (not full card!)
    current_period_start TIMESTAMPTZ,
    current_period_end   TIMESTAMPTZ,
    is_active       BOOLEAN DEFAULT TRUE,
    created_at      TIMESTAMPTZ DEFAULT NOW(),
    updated_at      TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE invoices (
    id           UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id    UUID NOT NULL REFERENCES tenants(id),
    amount_cents INT NOT NULL,
    currency     TEXT NOT NULL DEFAULT 'USD',
    status       TEXT NOT NULL DEFAULT 'pending',  -- 'pending', 'paid', 'failed'
    period_start TIMESTAMPTZ NOT NULL,
    period_end   TIMESTAMPTZ NOT NULL,
    stripe_invoice TEXT,                       -- external reference
    paid_at      TIMESTAMPTZ,
    created_at   TIMESTAMPTZ DEFAULT NOW()
);

CREATE TABLE usage_records (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id),
    metric      TEXT NOT NULL,                 -- 'api_calls', 'storage_bytes', 'seats'
    quantity    BIGINT NOT NULL,
    recorded_at TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_invoices_tenant ON invoices(tenant_id, created_at DESC);
CREATE INDEX idx_usage_tenant ON usage_records(tenant_id, metric, recorded_at DESC);

Usage aggregation for billing:

-- Monthly API call count for a tenant
SELECT
    tenant_id,
    DATE_TRUNC('month', recorded_at) AS month,
    SUM(quantity) AS total_api_calls
FROM usage_records
WHERE tenant_id = $1
  AND metric = 'api_calls'
  AND recorded_at >= DATE_TRUNC('month', NOW())
GROUP BY tenant_id, month;

That aggregation is correct but, at 50,000 usage events per second, usage_records would gain four billion rows a day. Raw events should not land in Postgres one row each. The full billing model, including a pre-aggregated usage table, is in Plans, Subscriptions and Entitlements.

API Keys

CREATE TABLE api_keys (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    name        TEXT NOT NULL,
    key_hash    TEXT NOT NULL UNIQUE,          -- SHA-256 of the key
    key_prefix  TEXT NOT NULL,                 -- first 8 chars for identification
    permissions TEXT[] DEFAULT ARRAY['read'],
    expires_at  TIMESTAMPTZ,
    last_used   TIMESTAMPTZ,
    created_by  UUID REFERENCES users(id),
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

CREATE INDEX idx_api_keys_hash ON api_keys(key_hash);
CREATE INDEX idx_api_keys_tenant ON api_keys(tenant_id);

API key details that matter:

  • idx_api_keys_hash duplicates the unique index from key_hash ... UNIQUE; drop it.
  • API key lookup happens before the tenant is known, so it cannot go through tenant-scoped RLS. Do it through a narrow SECURITY DEFINER function that takes the hash and returns only (tenant_id, key_id, permissions, expires_at), then set the tenant context from the result.
  • A fast hash (SHA-256) is fine for API keys because they are long random strings with no dictionary to attack, unlike passwords, which need a slow hash such as Argon2 or bcrypt.
  • Do not update last_used on every request; that turns a read path into a write per call on a hot row. Update it at most once a minute per key (WHERE last_used < NOW() - INTERVAL '1 minute'), or buffer it.
  • Add revoked_at TIMESTAMPTZ rather than deleting keys, so audit logs that reference a key id stay meaningful.

Audit Logs

CREATE TABLE audit_logs (
    id          UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    user_id     UUID REFERENCES users(id) ON DELETE SET NULL,
    action      TEXT NOT NULL,                 -- 'user.created', 'project.deleted'
    resource_type TEXT NOT NULL,
    resource_id UUID,
    changes     JSONB,                         -- {"field": {"old": "x", "new": "y"}}
    ip_address  INET,
    user_agent  TEXT,
    created_at  TIMESTAMPTZ DEFAULT NOW()
);

-- Partition by month for performance (audit logs grow fast)
-- In production, use declarative partitioning:
-- CREATE TABLE audit_logs (...) PARTITION BY RANGE (created_at);

CREATE INDEX idx_audit_tenant ON audit_logs(tenant_id, created_at DESC);
CREATE INDEX idx_audit_resource ON audit_logs(tenant_id, resource_type, resource_id);

The production version, partitioned and append-only, is in Audit Logs in Depth.

Row-Level Security (RLS)

RLS is the critical safety net that prevents cross-tenant data leaks at the database level.

Setup

-- 1. Enable RLS on every tenant-scoped table
ALTER TABLE users ENABLE ROW LEVEL SECURITY;
ALTER TABLE roles ENABLE ROW LEVEL SECURITY;
ALTER TABLE projects ENABLE ROW LEVEL SECURITY;
ALTER TABLE tasks ENABLE ROW LEVEL SECURITY;
ALTER TABLE audit_logs ENABLE ROW LEVEL SECURITY;

-- 2. Create policies that filter by tenant_id
-- The application sets a session variable: SET app.current_tenant = 'uuid';

CREATE POLICY tenant_isolation ON users
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY tenant_isolation ON projects
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

CREATE POLICY tenant_isolation ON tasks
    USING (tenant_id = current_setting('app.current_tenant')::uuid);

-- 3. For the application user (not superuser), RLS is enforced
-- Even if the app forgets WHERE tenant_id = ?, RLS blocks cross-tenant access

-- 4. Set tenant context at the beginning of each request
-- In your connection pool / middleware:
SET app.current_tenant = '550e8400-e29b-41d4-a716-446655440000';

Corrections (2026-11-08): four gaps in the setup above

  1. Tables with RLS enabled but no policy return nothing. roles and audit_logs have RLS enabled but no policy, and for a non-owner role, "no policy" means default deny: every query returns zero rows. Every RLS-enabled table needs a policy.
  2. current_setting('app.current_tenant') raises an error if the setting was never set, and returns an empty string (which fails the ::uuid cast) if it was set and later reset. Use the two-argument form with NULLIF, so a missing context yields NULL, matches no rows, and fails closed.
  3. USING filters reads; WITH CHECK filters writes. A policy with only USING still applies its expression to new rows for INSERT and UPDATE (PostgreSQL falls back to USING when WITH CHECK is absent), but stating WITH CHECK explicitly makes the intent obvious and survives later edits that split policies per command.
  4. A session-level SET is dangerous with connection pools. It survives on the physical connection after your request ends and is inherited by the next request that borrows the connection, which may belong to a different tenant. Set the tenant per transaction with set_config(..., true) instead (shown below).

The corrected, complete setup:

-- One helper, so every policy reads the context the same way.
CREATE FUNCTION app_current_tenant() RETURNS uuid
LANGUAGE sql STABLE AS $$
    SELECT NULLIF(current_setting('app.current_tenant', true), '')::uuid
$$;

-- Apply to every tenant-scoped table, including the ones that were missing.
DO $$
DECLARE t text;
BEGIN
    FOREACH t IN ARRAY ARRAY['users','roles','user_roles','projects','tasks',
                             'audit_logs','api_keys','invoices','usage_records']
    LOOP
        EXECUTE format('ALTER TABLE %I ENABLE ROW LEVEL SECURITY', t);
        EXECUTE format('ALTER TABLE %I FORCE ROW LEVEL SECURITY', t);
        EXECUTE format('DROP POLICY IF EXISTS tenant_isolation ON %I', t);
        EXECUTE format(
            'CREATE POLICY tenant_isolation ON %I
                 USING (tenant_id = app_current_tenant())
                 WITH CHECK (tenant_id = app_current_tenant())', t);
    END LOOP;
END $$;

FORCE ROW LEVEL SECURITY makes the policies apply to the table owner too. Without it, if the application ever connects as the role that owns the tables (common when the same role runs migrations), RLS silently does nothing.

Two refinements worth knowing:

  • Audit logs should be insert-and-read only. Split the policy per command: FOR SELECT USING (...) and FOR INSERT WITH CHECK (...), with no UPDATE or DELETE policy, so even the application role cannot rewrite history.
  • RLS policies can do more than tenant filtering. A restrictive policy (CREATE POLICY ... AS RESTRICTIVE) is ANDed with the permissive ones, which is how you layer "and only projects the user is a member of" on top of tenant isolation without weakening it.

Connection Pool Integration

-- In Node.js with pg pool:
-- pool.on('connect', async (client) => {
--   await client.query("SET app.current_tenant = $1", [tenantId]);
-- });

-- With Prisma:
-- Use $executeRaw to set session variable before queries
-- await prisma.$executeRaw`SET app.current_tenant = ${tenantId}`;

Correction (2026-11-08): do not set the tenant on connect

The two snippets above are unsafe and, as written, do not work:

  • pool.on('connect') fires once per physical connection, not once per request. The first tenant to open a connection "owns" it forever, and every later request that borrows that connection, from any tenant, runs as that tenant. That is precisely the cross-tenant leak RLS exists to prevent.
  • SET is a utility command and does not accept bind parameters, so SET app.current_tenant = $1 fails with a syntax error, and Prisma's tagged-template $executeRaw parameterizes ${tenantId} in the same way. Use the function form set_config(name, value, is_local), which is an ordinary function call and takes parameters normally.

The safe pattern is: open a transaction per request (or per unit of work), set the tenant locally inside it, run the queries, commit. The setting disappears at commit or rollback, so a pooled connection never carries a tenant to the next borrower:

BEGIN;
SELECT set_config('app.current_tenant', $1, true);   -- true = transaction-local, like SET LOCAL
SELECT set_config('app.current_user',   $2, true);   -- for audit triggers
-- ... the request's queries ...
COMMIT;
// Node.js (pg): wrap every request's work in this helper.
async function withTenant(pool, tenantId, userId, work) {
  const client = await pool.connect();
  try {
    await client.query('BEGIN');
    await client.query("SELECT set_config('app.current_tenant', $1, true)", [tenantId]);
    await client.query("SELECT set_config('app.current_user', $1, true)", [userId]);
    const result = await work(client);
    await client.query('COMMIT');
    return result;
  } catch (err) {
    await client.query('ROLLBACK');
    throw err;
  } finally {
    client.release();
  }
}

This is also the pattern that works behind PgBouncer in transaction mode, where consecutive statements outside a transaction may run on different server connections, so a session-level SET would not even reach the next query reliably. See Connection Pooling. With Prisma, run the set_config call and the queries inside one $transaction([...]).

RLS and Superusers

RLS policies do NOT apply to superusers or the table owner. Your application should connect as a non-superuser role. Create a dedicated role:

CREATE ROLE app_user WITH LOGIN PASSWORD '...';
GRANT SELECT, INSERT, UPDATE, DELETE ON ALL TABLES IN SCHEMA public TO app_user;
-- RLS now applies to all queries from app_user

Complete the picture with three roles: a migration role that owns the tables (and is never used by the application at runtime), the app_user role above with NOBYPASSRLS (the default), and a support or analytics role with BYPASSRLS that is used only from audited internal tooling. Combined with FORCE ROW LEVEL SECURITY, no runtime path bypasses isolation by accident.

Testing RLS

Isolation deserves its own automated tests, because a policy bug fails open silently:

-- As app_user: with no tenant set, every tenant table must look empty.
BEGIN;
SET LOCAL ROLE app_user;
SELECT count(*) FROM tasks;                       -- expect 0
SELECT set_config('app.current_tenant', '<tenant A>', true);
SELECT count(*) FROM tasks WHERE tenant_id = '<tenant B>';   -- expect 0
INSERT INTO tasks (tenant_id, project_id, title)
VALUES ('<tenant B>', '<some project>', 'x');     -- expect: violates row-level security policy
ROLLBACK;

Also add a CI check that queries the catalog for any table with a tenant_id column that does not have RLS enabled and forced:

SELECT c.relname
FROM pg_class c
JOIN pg_attribute a ON a.attrelid = c.oid AND a.attname = 'tenant_id'
WHERE c.relkind IN ('r', 'p') AND c.relnamespace = 'public'::regnamespace
  AND NOT (c.relrowsecurity AND c.relforcerowsecurity);
-- Any row returned fails the build.

Hot-Path Queries with EXPLAIN Reasoning

Plans are illustrative and trimmed. For reading plans in depth see Query Planning & Optimization.

Q4: The project board

SELECT id, title, status, priority, assignee_id, due_date
FROM tasks
WHERE tenant_id = $tenant              -- explicit, even though RLS adds it too
  AND project_id = $project
  AND status = 'in_progress'
  AND (due_date, id) > ($cursor_due, $cursor_id)
ORDER BY due_date, id
LIMIT 50;
Limit  (rows=50)
  ->  Index Scan using idx_tasks_project_board on tasks
        Index Cond: ((tenant_id = 'a1..') AND (project_id = 'p9..')
                     AND (status = 'in_progress') AND (ROW(due_date, id) > ROW(...)))
        Filter: (tenant_id = app_current_tenant())

Three equality columns, then the sort columns, so the index delivers rows already in ORDER BY order and the scan stops at 50. The RLS predicate shows up as an extra Filter (or is merged into the index condition when the planner can). With only the original single-column idx_tasks_project, the plan would fetch every task in the project, filter by status, and sort.

Why to keep the explicit tenant predicate

If the query relies on RLS alone (WHERE project_id = $project), the planner sees tenant_id = app_current_tenant(), a stable function whose value it does not know at planning time. It estimates the selectivity as an average (one over the number of distinct tenants), which is wildly wrong for the 8% tenant. Wrong estimates produce wrong plans: a nested loop that is perfect for a small tenant and catastrophic for the biggest one. Passing tenant_id = $1 explicitly lets the planner use the actual value with the column's most-common-values statistics. RLS remains the safety net; the explicit predicate is for the planner.

Related: with prepared statements, PostgreSQL switches to a generic plan after five executions if it looks no worse on average than the custom ones. With skewed tenants, the average hides the outlier. For queries whose best plan differs by tenant size, set plan_cache_mode = force_custom_plan for those statements or the session; see PostgreSQL Performance Tuning.

Q5: My open tasks

SELECT id, project_id, title, due_date
FROM tasks
WHERE tenant_id = $tenant AND assignee_id = $me AND status <> 'done'
ORDER BY due_date
LIMIT 20;

The partial index idx_tasks_my_open matches the query's status <> 'done' predicate exactly, so the planner can use it. It contains only open, assigned tasks, a small fraction of the table, and is ordered by due_date within each (tenant, assignee). The result is an index scan that returns 20 rows and stops. A partial index is only used when the query's WHERE clause implies the index predicate, so keep the query's wording aligned with the index.

Q2: API key authentication

SELECT tenant_id, id, permissions, expires_at
FROM api_keys
WHERE key_hash = encode(sha256($presented_key::bytea), 'hex')
  AND revoked_at IS NULL;

One unique-index probe. In practice it is served from a cache (key hash to tenant and permissions, short TTL, invalidated on revoke), because it runs on every API call.

SELECT created_at, user_id, action, resource_type, resource_id
FROM audit_logs
WHERE tenant_id = $tenant
  AND created_at >= $from AND created_at < $to
  AND resource_type = 'project' AND resource_id = $project
ORDER BY created_at DESC
LIMIT 100;

On a monthly-partitioned table, the created_at range prunes all but one or two partitions before any index is touched (look for Subplans Removed in the plan). Within the surviving partitions, idx_audit_resource (tenant_id, resource_type, resource_id) narrows to one resource. Adding created_at DESC as a fourth column would remove the sort; for 100 rows of one resource, the sort is negligible.

Concurrency Hot Spots

Per-tenant isolation and noisy neighbours

All tenants in a pool share CPU, memory, I/O, connections, locks, and autovacuum. One tenant's bulk import or runaway report can raise everyone's latency. The schema helps, but most defences are operational:

Resource a big tenant can exhaustDefence
ConnectionsPer-tenant concurrency limits in the application pool (a semaphore keyed by tenant), separate pools for API, background jobs and reports
CPU and I/O from slow queriesSET LOCAL statement_timeout per request, lower for free plans; reports moved to replicas or the warehouse
Request volumePer-tenant rate limiting at the API gateway, sized by plan; see Rate Limiting Deep Dive
LocksBatch large writes in chunks of 1,000 rows with commits between; no long transactions
Vacuum and bloatBig tenants' bulk deletes run in batches; monitor dead tuples per table
Buffer cacheTenant-leading indexes keep each tenant's pages contiguous, so a big tenant's scan evicts less of everyone else
Planner misestimatesExplicit tenant_id predicates, extended statistics, custom plans for skewed queries

Attribution comes first. pg_stat_statements aggregates by query shape, not tenant, so you cannot see "tenant X is burning 40% of CPU". Tag queries with a comment (/* tenant=acme, route=/tasks */, the sqlcommenter convention) and log slow queries with it, or record per-tenant request time in the application. Once you can see the outlier, the long-term fix for a tenant that is consistently a large fraction of the load is to move it to dedicated capacity using tenant_placements.

Plan limits under concurrency

The limit check shown later in Plan Limits & Feature Gating counts users, then the application inserts if under the limit. Two concurrent invitations on a plan with one seat left both see 4 of 5 and both insert: 6 users on a 5-seat plan. This is the classic read-decide-write race. Two fixes:

-- Fix A: serialize limit-checked inserts per tenant by locking the tenant row.
BEGIN;
SELECT 1 FROM tenants WHERE id = $tenant FOR UPDATE;
-- Count and compare here; only one transaction per tenant gets this far at a time.
INSERT INTO users (...) SELECT ... WHERE (SELECT count(*) FROM users WHERE tenant_id = $tenant) < $limit;
COMMIT;

-- Fix B: keep a counter with a CHECK, and let the conditional update decide.
CREATE TABLE tenant_quotas (
    tenant_id  UUID PRIMARY KEY REFERENCES tenants(id) ON DELETE CASCADE,
    seats_used INT NOT NULL DEFAULT 0,
    seats_max  INT NOT NULL,
    CHECK (seats_used <= seats_max)
);

UPDATE tenant_quotas SET seats_used = seats_used + 1
WHERE tenant_id = $tenant AND seats_used < seats_max
RETURNING seats_used;
-- 0 rows: limit reached. Otherwise insert the user in the same transaction.

Fix B is the same conditional-update pattern that prevents overselling in E-Commerce Schema Design: the check and the change are one statement, and the second concurrent transaction re-evaluates the WHERE after the first commits.

Tenant-wide locks and long transactions

Avoid patterns that lock "the tenant" for long: a tenant-row FOR UPDATE held across a slow operation blocks every limit-checked action for that tenant. Keep such transactions to a few milliseconds. For large per-tenant operations (imports, bulk updates, tenant deletion), process in batches with a job table and FOR UPDATE SKIP LOCKED so several workers can share the work without stepping on each other.

Plan Limits & Feature Gating

-- Store limits in tenant settings JSONB
UPDATE tenants
SET settings = jsonb_build_object(
    'max_users', 5,
    'max_projects', 10,
    'max_storage_mb', 500,
    'features', jsonb_build_object(
        'api_access', false,
        'custom_domain', false,
        'sso', false
    )
)
WHERE plan = 'free';

-- Check limit before creating a user
SELECT
    (SELECT COUNT(*) FROM users WHERE tenant_id = $1) <
    (settings->>'max_users')::int AS can_add_user
FROM tenants
WHERE id = $1;

Copying limits into every tenant's JSONB works for a single tier, but it means a pricing change rewrites thousands of rows and nothing records which tenant has a custom deal. As pricing matures, model plans and entitlements as data.

Plans, Subscriptions and Entitlements

Three separate concepts that early schemas conflate:

  • A plan is a product in the price list: "Pro, $20 per seat per month".
  • A subscription is a tenant's contract for a plan over a period, with a status driven by the billing provider.
  • An entitlement is what the tenant may actually do right now: the plan's grants plus any per-tenant overrides (a sales deal, a trial extension, a beta feature).
CREATE EXTENSION IF NOT EXISTS btree_gist;

CREATE TABLE plans (
    id           TEXT PRIMARY KEY,                  -- 'free', 'pro_2026', 'enterprise'
    name         TEXT NOT NULL,
    price_cents  INT  NOT NULL CHECK (price_cents >= 0),
    interval     TEXT NOT NULL CHECK (interval IN ('month', 'year')),
    is_public    BOOLEAN NOT NULL DEFAULT TRUE,     -- legacy plans stay but are hidden
    created_at   TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE features (
    key   TEXT PRIMARY KEY,                         -- 'max_users', 'sso', 'api_access'
    kind  TEXT NOT NULL CHECK (kind IN ('boolean', 'limit'))
);

CREATE TABLE plan_entitlements (
    plan_id     TEXT NOT NULL REFERENCES plans(id),
    feature_key TEXT NOT NULL REFERENCES features(key),
    enabled     BOOLEAN NOT NULL DEFAULT TRUE,
    limit_value BIGINT,                             -- NULL = unlimited for 'limit' features
    PRIMARY KEY (plan_id, feature_key)
);

CREATE TABLE subscriptions (
    id                 UUID PRIMARY KEY DEFAULT gen_random_uuid(),
    tenant_id          UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    plan_id            TEXT NOT NULL REFERENCES plans(id),
    status             TEXT NOT NULL CHECK (status IN
                         ('trialing', 'active', 'past_due', 'canceled', 'expired')),
    seats              INT  NOT NULL DEFAULT 1 CHECK (seats > 0),
    active_during      TSTZRANGE NOT NULL,
    provider_sub_id    TEXT UNIQUE,                 -- Stripe subscription id
    created_at         TIMESTAMPTZ NOT NULL DEFAULT NOW(),

    -- A tenant cannot have two overlapping subscriptions.
    EXCLUDE USING gist (tenant_id WITH =, active_during WITH &&)
);

CREATE TABLE entitlement_overrides (
    tenant_id   UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    feature_key TEXT NOT NULL REFERENCES features(key),
    enabled     BOOLEAN NOT NULL DEFAULT TRUE,
    limit_value BIGINT,
    reason      TEXT NOT NULL,                      -- 'deal:ACME-2026', 'trial extension'
    expires_at  TIMESTAMPTZ,
    PRIMARY KEY (tenant_id, feature_key)
);

The effective entitlements are a view: the active subscription's plan grants, with overrides winning.

CREATE VIEW effective_entitlements AS
SELECT s.tenant_id, pe.feature_key,
       COALESCE(o.enabled, pe.enabled)         AS enabled,
       COALESCE(o.limit_value, pe.limit_value) AS limit_value
FROM subscriptions s
JOIN plan_entitlements pe ON pe.plan_id = s.plan_id
LEFT JOIN entitlement_overrides o
       ON o.tenant_id = s.tenant_id AND o.feature_key = pe.feature_key
      AND (o.expires_at IS NULL OR o.expires_at > NOW())
WHERE s.active_during @> NOW()
  AND s.status IN ('trialing', 'active', 'past_due');

Design notes:

  • Plans are versioned by id, never edited. Changing what Pro includes means creating pro_2027 and migrating tenants deliberately; existing customers keep their contract terms ("grandfathering").
  • The billing provider is the source of truth for money; your database is the source of truth for access. Stripe webhooks update subscriptions; deduplicate them with a billing_events (provider, event_id) UNIQUE table, exactly like payment webhooks in e-commerce, because they are delivered at least once and out of order. Compare the event's timestamp or the provider object's version before applying it.
  • past_due keeps access for a grace period. Revoking access the moment a card fails creates support tickets; the status models the grace period explicitly.
  • Cache entitlements per tenant (they change rarely) and invalidate on subscription or override changes. The view runs on cache misses only.

Metered usage. Raw usage events go to a buffer (Redis counters, a Kafka topic) and are flushed as aggregates, one row per tenant, metric and period, with an idempotent upsert:

CREATE TABLE usage_counters (
    tenant_id    UUID NOT NULL REFERENCES tenants(id) ON DELETE CASCADE,
    metric       TEXT NOT NULL,
    period_start DATE NOT NULL,                    -- billing day or hour bucket
    quantity     BIGINT NOT NULL DEFAULT 0 CHECK (quantity >= 0),
    PRIMARY KEY (tenant_id, metric, period_start)
);

-- Flush from the buffer, idempotent per (flush batch id) tracked by the flusher.
INSERT INTO usage_counters (tenant_id, metric, period_start, quantity)
VALUES ($tenant, 'api_calls', $day, $n)
ON CONFLICT (tenant_id, metric, period_start)
DO UPDATE SET quantity = usage_counters.quantity + EXCLUDED.quantity;

At 50,000 events per second across 25,000 tenants, flushing once a minute produces at most a few hundred upserts per second, and Q8 ("usage this period") becomes a primary-key range read. Keep the raw events in an analytics store if customers need per-call detail.

Audit Logs in Depth

Audit logs are append-only, high-volume, rarely read, and must be trustworthy. That combination suggests partitioning, write-only permissions, tamper evidence, and per-plan retention.

CREATE TABLE audit_events (
    tenant_id     UUID        NOT NULL,
    id            UUID        NOT NULL,              -- UUIDv7, time-ordered
    created_at    TIMESTAMPTZ NOT NULL DEFAULT NOW(),
    actor_type    TEXT        NOT NULL CHECK (actor_type IN ('user', 'api_key', 'system', 'support')),
    actor_id      UUID,
    action        TEXT        NOT NULL,              -- 'task.updated'
    resource_type TEXT        NOT NULL,
    resource_id   UUID,
    changes       JSONB,
    ip_address    INET,
    user_agent    TEXT,
    request_id    TEXT,
    prev_hash     BYTEA,                             -- hash chain for tamper evidence
    row_hash      BYTEA,
    PRIMARY KEY (tenant_id, created_at, id)
) PARTITION BY RANGE (created_at);

CREATE TABLE audit_events_2026_11 PARTITION OF audit_events
    FOR VALUES FROM ('2026-11-01') TO ('2026-12-01');

CREATE INDEX ON audit_events (tenant_id, resource_type, resource_id, created_at DESC);

-- The application may insert and read, never modify.
REVOKE UPDATE, DELETE, TRUNCATE ON audit_events FROM app_user;
GRANT  INSERT, SELECT ON audit_events TO app_user;

Why this shape:

  • The primary key includes the partition key (created_at), which PostgreSQL requires for unique constraints on partitioned tables, and leads with tenant_id so per-tenant time-range reads (Q7) are a single index range in each partition.
  • No foreign keys to users or tenants. Audit records must outlive the things they describe; a deleted user's actions remain attributable by id. (The original table's ON DELETE CASCADE from tenants deletes a tenant's audit trail along with the tenant, which may violate retention obligations; choose deliberately.)
  • Monthly partitions make retention a metadata operation. DETACH PARTITION ... CONCURRENTLY, export to object storage as Parquet, then drop. Retention can differ by plan (90 days on Pro, 7 years on Enterprise) by exporting per tenant before dropping, or by keeping enterprise tenants' events in a separate store.
  • Tamper evidence with a hash chain. Each row stores row_hash = sha256(prev_hash || canonical_row), chained per tenant. Changing or deleting any historical row breaks every later hash, and a periodic job that verifies the chain (and publishes the latest hash somewhere external) makes silent edits detectable even by a database administrator. Chaining per tenant serializes writes per tenant; if that becomes a bottleneck, chain per tenant per hour and link the hourly roots.
  • Write it in the same transaction as the change when the audit record is a compliance requirement, using a trigger that reads app.current_user and app.current_tenant from the transaction-local settings. If audit volume threatens the primary, write to an outbox in the same transaction and let a consumer move events to a dedicated audit store; see Transactional Outbox.

At large scale, audit search across months moves to a columnar store such as ClickHouse or a search engine, with Postgres keeping only recent partitions.

Performance Considerations

ConcernSolution
Every query needs tenant_idRLS handles it; composite indexes always start with tenant_id
Large tenants dominate shared tablesMonitor per-tenant row counts; consider sharding for outliers
Audit log growthPartition by month; archive old partitions to cold storage
API key lookup speedIndex on key_hash; cache validated keys in Redis (TTL 5 min)
Usage metering overheadBatch usage records (buffer in Redis, flush every minute)
Cross-tenant admin queriesSuperuser bypasses RLS; or create a separate admin role with different policies
Planner misestimates for skewed tenantsExplicit tenant_id = $1 predicates alongside RLS; custom plans for skewed statements
Random UUID insert localityUUIDv7 keys keep B-tree inserts on the right-hand edge
Noisy neighboursPer-tenant rate limits, statement timeouts by plan, separate pools, move outliers to dedicated capacity
Tenant deletionMark inactive immediately; delete in batches from a job; DROP if on a dedicated database

For cross-tenant admin work, prefer a dedicated role with BYPASSRLS over connecting as a superuser: it bypasses row security without also granting the power to alter the database.

Schema Design Decisions

DecisionRationale
UUID primary keysGlobally unique; safe for cross-tenant exports/imports; no sequential ID leakage
tenant_id on every tableCore tenant isolation mechanism; enables RLS
RBAC over ABACRole-based is simpler and sufficient for most SaaS; attribute-based adds complexity
permissions as TEXT[]Simple, fast array containment check; good enough for 95% of apps
JSONB for tenant settingsPlan limits vary by tier; JSONB avoids schema changes for new limits
Separate billing tableBilling data has different access patterns and sensitivity than tenant metadata
Audit log partitioningAudit logs are append-only and grow indefinitely; partitioning enables efficient pruning
key_hash not key_valueAPI keys are like passwords; store only the hash, show the key once at creation
Composite (tenant_id, id) foreign keysCross-tenant references become impossible, not just invisible
Transaction-local tenant contextPooled connections never carry one tenant's context into another's request
FORCE ROW LEVEL SECURITYPolicies apply even if the app connects as the table owner
Plans, entitlements and overrides as tablesPricing changes and custom deals are data, not code or bulk JSONB rewrites
Exclusion constraint on subscriptionsOverlapping subscriptions for one tenant are impossible
Aggregated usage countersBilling reads a few rows per tenant, not billions of raw events

Evolution as It Scales

StageSymptomChange
1. One shared databaseEarly productShared schema, RLS, tenant-leading indexes
2. Replicas and cachesRead loadCache tenant, entitlements, permissions and API keys; route reports and exports to replicas (Replication)
3. Streams outUsage and audit volumeUsage buffered and aggregated; audit partitioned, then moved to a columnar store
4. Outlier tenants outOne tenant dominates load or needs a contract-level guaranteeMove it to a dedicated database via tenant_placements
5. Shard by tenantPooled tenants exceed one primaryDistribute all tenant tables by tenant_id (Citus) or a shard directory over many Postgres clusters
6. Regional cellsData residency, blast radiusIndependent stacks per region or cell; a global control plane holds tenants and placements

tenant_id is the natural shard key for B2B SaaS: every hot query includes it, tenants rarely interact, and co-locating all of a tenant's tables on one shard keeps joins and transactions local. In Citus terms, every tenant table is a distributed table with tenant_id as the distribution column, all co-located, while small global tables (plans, features) are reference tables replicated to every node. Because primary keys and foreign keys already include tenant_id, the schema needs no changes to distribute. See Sharding for the Citus model and the hot-shard problem.

The hot-shard problem is real here: hashing tenants spreads tenants evenly, not load. The largest tenant can still overwhelm its shard, which is why stage 4 (dedicated placement) precedes or accompanies stage 5. Very large tenants can even be sub-sharded by a second key (tenant_id, project_id), at the cost of cross-shard queries within that tenant.

Moving a tenant between placements is a routine operation, so make it boring: mark the tenant read_only in tenant_placements (or use logical replication filtered by tenant_id for a near-zero-downtime move), copy every table's rows for that tenant, verify counts and checksums, flip the placement, invalidate caches, and delete from the source in batches. Every row having tenant_id and every key including it is what makes this mechanical.

Migration Strategy for Multi-Tenancy

If you're adding multi-tenancy to an existing single-tenant app:

  1. Add tenant_id column (nullable) to all tables
  2. Create a default tenant, assign all existing rows to it
  3. Make tenant_id NOT NULL
  4. Add composite indexes starting with tenant_id
  5. Enable RLS with policies
  6. Update all queries (or rely on RLS as safety net)
  7. Update application to set app.current_tenant per request

On large tables, do each step without long locks (see Database Migrations):

  • Step 2 in batches. UPDATE ... SET tenant_id = $default WHERE id BETWEEN $a AND $b in chunks of a few thousand rows with commits between, not one statement that rewrites the table in a single transaction. Better still, add the column with DEFAULT '<default tenant uuid>', which in PostgreSQL 11+ is a metadata-only change for existing rows.
  • Step 3 without a full-table lock. ALTER TABLE ... ADD CONSTRAINT tenant_id_not_null CHECK (tenant_id IS NOT NULL) NOT VALID, then VALIDATE CONSTRAINT (which takes a weaker lock), then SET NOT NULL, which PostgreSQL 12+ can do without a scan when a validated check constraint already proves it.
  • Step 4 with CREATE INDEX CONCURRENTLY, and swap unique constraints to their tenant-scoped versions (UNIQUE (email) becomes UNIQUE (tenant_id, email)) by building the new unique index concurrently, then attaching it with ADD CONSTRAINT ... UNIQUE USING INDEX.
  • Step 5 in report-only mode first. Before enabling enforcement, run the application against a staging copy as app_user with RLS forced, and log any query that returns fewer rows than expected.
  • Add the composite (tenant_id, id) foreign keys with NOT VALID and validate them afterwards; they are what keep the migrated data from ever acquiring cross-tenant references.

Common Mistakes

  1. No RLS, only WHERE tenant_id = ? in the application. One forgotten predicate is a breach. Use both.
  2. Setting the tenant on connect or with session-level SET. Pooled connections leak the context to the next tenant. Use transaction-local set_config.
  3. Connecting as the table owner or a superuser. RLS does not apply. Use a dedicated role and FORCE ROW LEVEL SECURITY.
  4. RLS enabled without a policy, or a policy only on some tables. Either empty results or a gap. Check the catalog in CI.
  5. Single-column foreign keys between tenant tables. Allow cross-tenant references; use (tenant_id, id).
  6. Junction tables without tenant_id. They cannot be protected by RLS or tenant-scoped foreign keys.
  7. Indexes that do not lead with tenant_id. Scattered pages, poor plans, and a painful shard migration later.
  8. Relying on RLS for planner statistics. Stable-function predicates get average selectivity; add the explicit predicate.
  9. Count-then-insert plan limits. Race to exceed the limit; use a conditional counter update.
  10. Limits copied into every tenant's JSONB. Price changes become bulk rewrites and custom deals are invisible; model plans and overrides.
  11. One row per usage event in Postgres. Billions of rows a day; aggregate before storing.
  12. Mutable audit logs. Revoke update and delete, partition, and chain hashes.
  13. Choosing database-per-tenant for "isolation" at the start. Migrations, connections and onboarding costs multiply with every tenant; start shared and move tenants out when they earn it.
  14. Unique constraints that ignore the tenant. UNIQUE (email) blocks the same person from joining two tenants; UNIQUE (name) on projects blocks two tenants from both having "Roadmap".

Interview Walkthrough: "Design the Database for a Multi-Tenant SaaS"

1. Scope (3 minutes). "B2B project management. Tens of thousands of tenants, most small, a few very large. Enterprise customers ask about isolation and data residency. Seat-based pricing with some metered usage."

2. Access patterns (5 minutes). Everything is per tenant. Hot reads: board views, my tasks, permission checks, API key auth. Writes: task edits with audit. Streams: usage and audit. Skew: one tenant is 8% of rows.

3. Isolation strategy (8 minutes). Compare shared schema, schema per tenant, database per tenant on isolation, operations and migration cost. Choose shared schema with RLS as the default and a placement table to move large or regulated tenants to dedicated databases. Explain why starting shared keeps that move cheap.

4. Entities and keys (8 minutes). Tenants; users (tenant-local or global accounts plus memberships, and why it matters); roles and user roles; projects and tasks; API keys; plans, subscriptions, entitlements; usage counters; audit events. Every tenant-owned table has tenant_id, composite (tenant_id, id) uniqueness, and tenant-scoped foreign keys. UUIDv7 ids.

5. RLS done right (8 minutes). Policies on every table with USING and WITH CHECK, FORCE ROW LEVEL SECURITY, a non-owner app role, transaction-local tenant context that is safe with PgBouncer, a BYPASSRLS support role, and CI tests that fail if a table is unprotected.

6. Indexes and hot queries (5 minutes). Tenant-leading composites in equality-then-sort order, a partial index for open tasks, keyset pagination, explicit tenant predicates for planner statistics.

7. Concurrency and noisy neighbours (5 minutes). Seat limits with a conditional counter update; per-tenant rate limits, statement timeouts and pools; attribution with query tags; moving outliers out.

8. Evolution (3 minutes). Caches and replicas, usage and audit to streams and columnar storage, dedicated placements, then distributing by tenant_id with co-located tables, and regional cells for residency.

Likely follow-ups:

  • "A customer wants their data deleted." Mark inactive, delete in batches across tables (every table has tenant_id, so this is enumerable), purge from backups per your retention policy, and keep the audit record of the deletion itself.
  • "A customer wants their data in the EU." Place the tenant in an EU cluster through tenant_placements; the control plane stays global but holds no customer content.
  • "How do you run a migration across a thousand dedicated databases?" Same schema everywhere, a migration orchestrator that records per-placement versions, backward-compatible expand-then-contract changes so mixed versions are safe during rollout.
  • "Why not schema per tenant?" Fine for hundreds of tenants with customization needs; at tens of thousands, catalog bloat, per-schema migrations and connection handling dominate, and moving to shared later is a rewrite.

Where to Go Next