Tenant data ownership

Tenant Model & Data Ownership

When to Use

Use when designing schemas, repositories, or domain models for multi-tenant SaaS applications (organizations, workspaces, teams, projects, and tenant-scoped resources).

Four-Tier Data Ownership Taxonomy

Never leave a table or entity's ownership ambiguous. Classify every record into one of four explicit scopes:

1. System / Global Data: Shared reference data (plans, currencies, feature_definitions, system_migrations)—never writable by tenant requests.
2. User / Personal Identity Data: Account-level identity (users, user_sessions, passkeys, mfa_devices, personal_notifications)—owned by user_id across all workspaces the user belongs to.
3. Organization / Billing Account Data: Top-level customer entity (organizations, billing_subscriptions, invoices, sso_domains).
4. Workspace / Tenant-Scoped Operational Data: Projects, tasks, documents, files, webhooks, API keys, and audit events—strictly owned by a single workspace_id / tenant_id.

Never mix a user's personal integration credentials or settings with workspace-owned credentials.

Database Isolation Topologies & Defense in Depth

1. Pool, Schema, or Silo Selection

Match the physical isolation model to the project's architecture and compliance needs:

• Shared Database, Shared Schema (Pool Model): Every tenant-scoped table carries a NOT NULL tenant_id (workspace_id) column. Most common and operationally simple, but requires strict query scoping and relational constraints.
• Shared Database, Separate Schema / Separate Database (Bridge / Silo Model): Used only when contractually or architecturally required. Never silently migrate a shared-schema app into a multi-DB architecture without an explicit architectural decision.

2. Composite Foreign Keys to Prevent Cross-Tenant Graph Corruption

Relying solely on project_id REFERENCES projects(id) allows a bug in tasks insertion to attach tenant_id = A to a project_id owned by tenant_id = B!

• In relational databases (PostgreSQL, MySQL, SQLite), expose (tenant_id, id) as a UNIQUE or composite PRIMARY KEY on parent tables and enforce Composite Foreign Keys on child tables:

  CREATE TABLE projects (
    id UUID NOT NULL,
    tenant_id UUID NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
    name TEXT NOT NULL,
    PRIMARY KEY (tenant_id, id)
  );
  CREATE TABLE tasks (
    id UUID NOT NULL,
    tenant_id UUID NOT NULL,
    project_id UUID NOT NULL,
    title TEXT NOT NULL,
    PRIMARY KEY (tenant_id, id),
    FOREIGN KEY (tenant_id, project_id) REFERENCES projects (tenant_id, id) ON DELETE CASCADE
  );
  

• Place tenant_id as the leading column in composite B-tree indexes ((tenant_id, status, created_at)) so tenant queries and uniqueness checks (UNIQUE (tenant_id, slug)) scan only a single tenant's slice.

3. PostgreSQL Row-Level Security (RLS) & SET LOCAL Pooling Safety

When PostgreSQL RLS is used as defense-in-depth alongside application query filters:

• Enable both ALTER TABLE ... ENABLE ROW LEVEL SECURITY; and ALTER TABLE ... FORCE ROW LEVEL SECURITY; (without FORCE, the table owner role bypasses RLS completely!).
• Enforce both USING (reads/updates/deletes) and WITH CHECK (inserts/updates) policies against current_setting('app.current_tenant_id', true).
• Connection Pool Rule: Never run session-level SET app.current_tenant_id = ... on a pooled connection—when returned to the pool, the next request inherits the previous tenant! Always bind the setting inside an explicit transaction using SET LOCAL or SELECT set_config('app.current_tenant_id', $1, true).