docs/adr/0034-multi-tenant-data-model.md

Rama master · réplica de lectura del repositorio original.

# ADR-0034: Multi-Tenant Data Model — Tenant ID Column + PostgreSQL Row-Level Security

## Metadata

| Field | Value |
|-------|-------|
| **Status** | Accepted |
| **Date** | 2026-06-26 |
| **Authors** | AlessioBrillo |
| **Deciders** | AlessioBrillo |
| **Supersedes** | N/A |
| **Relates to** | ADR-0027, ADR-0032, ADR-0033 |
| **Implemented** | 2026-07-07 — see PR #140 |
| **Project** | DOMINUS |

## Context

ADR-0027 established the high-level multi-tenancy strategy for DOMINUS Cloud: shared tables with a `tenant_id` column, enforced by PostgreSQL Row-Level Security (RLS), with an optional `DatabaseProvider` abstraction for SQLite (community edition) compatibility.

This ADR defines the concrete data model: which tables get `tenant_id`, how the column is populated, the migration strategy from single-tenant to multi-tenant, the RLS policy design, and the query patterns that repositories must follow.

The core tension is between **isolation** (tenants must never see each other's data) and **code reuse** (the community edition should not require a separate code path). The solution must be mechanical and enforceable by convention and tooling, not by developer vigilance alone.

### Current Schema State

The database has 28 tables (defined in `src/db/schema.ts` at ~620 lines). Of these:

- **Entity tables** (tenant-owned): `candidates`, `portfolio_entries`, `scoring_runs`, `trademark_results`, `outcomes`, `outcome_scores`, `watchlist_entries`, `listings`, `listing_offers`, `bids`, `renewal_alerts`, `public_scores`, `auto_listings`, `events`, `onboarding_state`
- **Cross-tenant tables** (system-owned): `schema_migrations`, `pipeline_runs`, `pipeline_metrics`, `provider_cache`, `weight_snapshots`, `backtest_signals`, `scheduler_jobs`, `job_queue`
- **Multi-instance tables** (per-instance configuration): `api_keys` (Cloud only), `trademark_term_cache`

### Schema Migration Complexity

Every entity table currently has no `tenant_id` column. Adding it requires:

1. A migration that adds the column with a default value (`'default'` for existing single-tenant data)
2. A composite index on `(tenant_id, <natural_key>)` for every entity table
3. An RLS policy for every entity table (PostgreSQL only)
4. Updating every repository method to include `AND tenant_id = ?` in WHERE clauses
5. Updating every `INSERT` to include `tenant_id` in the column list

This is the most invasive schema change in DOMINUS history. The migration must be zero-downtime for Cloud and invisible for community edition users.

## Decision Drivers

1. **Single codebase, no branching** — The community edition (SQLite) must work with or without the `tenant_id` column. The Cloud edition (PostgreSQL) must enforce tenant isolation. Both use the exact same repository code.

2. **Defence-in-depth** — RLS is the last line of defence, not the only one. The application layer must also filter by `tenant_id`. RLS catches mistakes where a developer forgets the WHERE clause.

3. **Migration must be idempotent and reversible** — The `tenant_id` column migration must handle existing single-tenant data (`tenant_id = 'default'`), multi-tenant data (Cloud), and rollback (drop column for CE downgrade).

4. **Performance at Cloud scale** — Composite indexes on `(tenant_id, ...)` must keep queries fast at hundreds of tenants with millions of rows. The `tenant_id` filter is highly selective (one tenant's data is a tiny fraction of the total).

5. **No application-level tenant management** — Tenant provisioning, suspension, and deletion are managed externally (via Auth0 Organizations for Cloud, or manual SQL for CE). The data model does not include a `tenants` table.

## Considered Options

### Option A: Shared Tables with tenant_id Column + RLS (CHOSEN)

Every tenant-owned table gets a `tenant_id TEXT NOT NULL` column. All queries are filtered by `tenant_id`. PostgreSQL RLS policies enforce isolation at the database level. The community edition (SQLite) ignores RLS — the `tenant_id` column exists but is always `'default'`.

**Advantages:**
- Single codebase — repository code is identical for CE and Cloud
- RLS provides database-enforced isolation — even a compromised middleware can't leak data
- Existing SQLite migrations continue to work (with the new `tenant_id` column added)
- Query performance is predictable — composite indexes on `(tenant_id, ...)` are highly selective
- Easy to reason about — every developer sees `WHERE tenant_id = ?` in every query

**Disadvantages:**
- Every repository method must include `tenant_id` — forgetting it is the most common bug (mitigated by RLS)
- The `tenant_id` column adds ~16 bytes per row (TEXT UUID) — negligible at expected scale
- Slightly more verbose SQL — every query includes the tenant filter
- Existing single-tenant data must be migrated to `tenant_id = 'default'`

**Cost Implications:** ~8h for migration + repository updates. €0 operational cost.

**Risk Assessment:** Low. This is the standard multi-tenant pattern used by thousands of SaaS applications (including Supabase, GitLab, and Discourse).

---

### Option B: Separate PostgreSQL Schemas per Tenant

Each tenant gets an isolated PostgreSQL schema (`tenant_<uuid>`) containing the full set of tables. The `search_path` is set per-connection based on the authenticated tenant.

**Advantages:**
- Complete isolation — no chance of cross-tenant data leak
- Tenant deletion is a single `DROP SCHEMA` command
- No `tenant_id` column needed — tables are simpler
- No RLS configuration needed
- Easy to verify in audits — each schema is a self-contained unit

**Disadvantages:**
- N queries to apply schema migrations (one per schema via dynamic SQL)
- Connection management is more complex — each connection has a `search_path` context
- Cross-tenant analytics (billing, usage metrics) require dynamic schema iteration
- PostgreSQL catalog performance degrades beyond ~10,000 schemas
- SQLite has no schema concept — community edition would need a different approach entirely
- The `DatabaseProvider` abstraction would need schema-awareness, leaking implementation details

**Cost Implications:** ~16h development. Higher ongoing maintenance for migrations.

**Risk Assessment:** Medium. Schema-per-tenant works well for B2B SaaS with <1,000 tenants. Beyond that, catalog lookup becomes a bottleneck. The bigger problem is SQLite incompatibility — the community edition would need a separate code path, violating the single-codebase requirement.

---

### Option C: Separate Database per Tenant

Each tenant gets an entirely separate PostgreSQL database. Connection routing is handled by a connection pool or proxy.

**Advantages:**
- Maximum isolation — separate database, separate backup, separate performance profile
- No schema migration coordination — each database is migrated independently
- Tenant backup/restore is a single `pg_dump`/`pg_restore` command
- Hard isolation boundary — a bug in one tenant's query cannot affect another

**Disadvantages:**
- Connection management is significantly more complex — connection per database, pooling per database
- PostgreSQL connection limits (default 100) are exhausted quickly — would need PgBouncer or similar
- Cross-tenant admin queries require iterating over all databases
- Schema migrations applied to N databases — must be coordinated and idempotent
- Dramatically higher operational complexity — database-per-tenant is an enterprise pattern
- Not suitable for a solo-founder project at launch scale
- SQLite community edition would have no equivalent pattern

**Cost Implications:** High. Each database consumes connections, memory, and storage overhead. Managed PostgreSQL costs multiply per database.

**Risk Assessment:** High. Over-engineered for the expected scale (hundreds, not thousands of tenants). The operational burden of managing N databases is disproportionate for a solo-founder project.

---

### Option D: Application-Level Tenant Filtering Only

No `tenant_id` column. No RLS. Every repository method is updated to accept a `tenantId` parameter and filter results in application code. Isolation relies entirely on the application layer.

**Advantages:**
- No schema migration — zero database changes
- No RLS configuration — simpler deployment
- No `tenant_id` storage overhead
- Faster development — no migration, no RLS policies to write

**Disadvantages:**
- No defence-in-depth — a single missing `WHERE tenant_id = ?` in application code leaks all tenant data
- Code review cannot catch every missing filter — the human error surface is too large
- SQL injection that bypasses the WHERE clause would expose all tenants
- No database-level enforcement — the security model is entirely in application logic
- Violates every security best practice for multi-tenant SaaS
- Not auditable — there is no way to prove that tenant isolation works at the database level

**Cost Implications:** ~4h development. €0 operational cost.

**Risk Assessment:** Critical. Application-only isolation is insufficient for a production SaaS. The OWASP Multi-Tenant Security guidance explicitly warns against this approach. RLS (Option A) is the minimum viable isolation mechanism.

---

## Decision

**Chosen option: Option A — Shared Tables with tenant_id Column + RLS**

The rationale:

1. **Defence-in-depth is non-negotiable for a production SaaS.** RLS provides database-enforced isolation that works even if the application layer is compromised. A `SELECT` without a `WHERE tenant_id = ?` returns zero rows. This is the minimum acceptable security posture for DOMINUS Cloud.

2. **Single codebase is the overriding architectural constraint.** The community edition and Cloud edition use the same repository classes, the same migrations, and the same query patterns. The `tenant_id` column exists in SQLite too — it's just always `'default'`. No `#ifdef`, no feature branching.

3. **Rejecting schema-per-tenant (Option B):** SQLite has no schema concept. Adopting schema-per-tenant would require either (a) maintaining separate migration paths for CE and Cloud, or (b) forcing CE to adopt PostgreSQL. Both violate the single-codebase requirement (ADR-0027).

4. **Rejecting database-per-tenant (Option C):** The operational complexity is disproportionate. A solo founder cannot manage N database instances. The connection limit alone (100 PostgreSQL default) would be exhausted at ~20 tenants.

5. **RLS is proven technology.** PostgreSQL RLS has been in production use since 9.5 (now 15+). Supabase, GitLab, and thousands of other SaaS applications use the same pattern. The performance overhead is single-digit microseconds per row.

### Tenant ID Scope

| Table Category | Tables | tenant_id? | RLS? |
|----------------|--------|------------|------|
| **Entity** | candidates, portfolio_entries, scoring_runs, trademark_results, outcomes, outcome_scores, watchlist_entries, listings, listing_offers, bids, renewal_alerts, public_scores (double-keyed — also accessible by slug), auto_listings, events, onboarding_state, api_keys | YES | YES |
| **Cross-tenant** | pipeline_runs, pipeline_metrics, provider_cache, weight_snapshots, backtest_signals, scheduler_jobs, job_queue, trademark_term_cache | NO | NO |
| **System** | schema_migrations | NO | NO |

### RLS Policy Template

```sql
-- Applied to every entity table
ALTER TABLE candidates ENABLE ROW LEVEL SECURITY;

CREATE POLICY tenant_isolation_candidates ON candidates
  FOR ALL
  USING (tenant_id = current_setting('app.tenant_id')::TEXT);
```

The `app.tenant_id` session variable is set by the tenant context middleware (see ADR-0032) at the start of each authenticated request:

```sql
SELECT set_config('app.tenant_id', '<tenant_uuid>', true);  -- true = local to transaction
```

### Repository Query Pattern

Every repository method for an entity table follows this pattern:

```typescript
class CandidateRepository {
  async findAll(tenantId: string): Promise<Candidate[]> {
    return this.db.query<CandidateRow>(
      'SELECT * FROM candidates WHERE tenant_id = $1 ORDER BY created_at DESC',
      [tenantId]
    );
  }

  async findById(tenantId: string, id: string): Promise<Candidate | null> {
    return this.db.queryOne<CandidateRow>(
      'SELECT * FROM candidates WHERE tenant_id = $1 AND id = $2',
      [tenantId, id]
    );
  }

  async create(tenantId: string, data: CreateCandidateDto): Promise<Candidate> {
    return this.db.queryOne<CandidateRow>(
      `INSERT INTO candidates (tenant_id, ...) VALUES ($1, ...) RETURNING *`,
      [tenantId, ...dataValues]
    );
  }
}
```

### Base Repository

To reduce boilerplate, an optional base repository class provides tenant-aware helpers:

```typescript
abstract class TenantAwareRepository<T> {
  constructor(protected db: DatabaseProvider) {}

  protected withTenant(query: string): string {
    // Check if the query already has tenant_id in WHERE
    // If not (and the query is for an entity table), the RLS policy will catch it
    return query;  // RLS is the safety net — the query doesn't need modification
  }
}
```

The base repository approach is intentionally minimal. RLS is the primary enforcement mechanism. The application-layer `tenant_id` filter is a performance optimisation (it reduces the row set before RLS checks), not a security requirement.

## Consequences

### Positive
- RLS provides database-enforced tenant isolation — the strongest practical guarantee for shared-table multi-tenancy
- Single codebase with no branching — CE and Cloud share every line of repository code
- Migration is incremental: add `tenant_id` to entity tables first, enable RLS later (feature-flagged)
- Existing single-tenant data becomes `tenant_id = 'default'` — backward compatible
- Composite indexes on `(tenant_id, ...)` are highly selective — query performance is predictable
- RLS is transparent to application logic — no code changes beyond adding `tenant_id` to queries
- Community edition users see zero behaviour change — SQLite ignores RLS, the column is always `'default'`

### Negative
- Migration is invasive: 15+ entity tables need `tenant_id` added, 15+ RLS policies to write, 15+ repositories to update
- Every repository method must include `tenant_id` — forgetting it is caught by RLS (returns zero rows) but debugging "why is this query returning nothing?" is harder than debugging "why is this leaking data?"
- Composite indexes increase storage slightly (~16 bytes per row per index)
- The `tenant_id` column adds conceptual overhead for community edition contributors who don't need multi-tenancy
- RLS policies must be maintained alongside schema migrations — a new entity table created without an RLS policy is a security gap

### Compliance and Security Implications
- RLS provides auditable tenant isolation — a compliance auditor can verify that `SELECT ... WHERE tenant_id = 'A'` returns zero rows when `app.tenant_id` is set to `'B'`
- The `app.tenant_id` session variable is transaction-local — it cannot leak across requests
- RLS policies are schema-included — they are version-controlled alongside the schema, not configured outside the codebase
- The `tenant_id` column is immutable after creation — no UPDATE can change a row's tenant
- Cross-tenant admin queries (for billing, support) use a separate database connection with `app.tenant_id` set to `'admin'` or unset (superuser bypassing RLS)
- SQLite has no RLS concept — but since CE is single-user and the `tenant_id` is always `'default'`, isolation is trivially satisfied

### Migration and Monitoring Plan

**Phase 1 — Schema Migration (completed):**
1. Migration `0029-add-tenant-id.ts` adds `tenant_id TEXT NOT NULL DEFAULT 'default'` to all entity tables — coverage verified: 15 entity tables + `listing_offers` (added post-audit)
2. Composite indexes created: `CREATE INDEX IF NOT EXISTS idx_<table>_tenant ON <table>(tenant_id)` on every entity table
3. Migration `0030-enable-rls.ts` creates RLS policies for all entity tables on PostgreSQL (SQLite no-op)
4. All migrations run on CE (SQLite) — existing rows get `tenant_id = 'default'`
5. All migrations run on Cloud (PostgreSQL) — same effect, RLS enforced

**Phase 2 — Repository Updates (completed):**
1. Every entity repository method includes `tenant_id` in SELECT, INSERT, UPDATE, DELETE
2. Tenant ID resolved via `resolveTenantId()` from `AsyncLocalStorage` context — no method signature changes needed
3. `listing_offers` discovered as missing during audit (PR #140); corrected in both migration and repository
4. All 138 test files pass with `tenantId = 'default'` (no behaviour change for CE)

**Phase 3 — RLS Enablement (completed):**
1. Migration `0030-enable-rls.ts` applies RLS on PostgreSQL startup — no feature flag needed (SQLite is no-op)
2. Integration tests verify RLS isolation: 15 test cases covering cross-tenant reads, writes, updates, and boundary enforcement
3. `public_scores` has a special RLS exception for the `'public'` tenant (anonymous access)

**Rollback:**
- Schema: run `ALTER TABLE ... DROP COLUMN tenant_id` (loses multi-tenant data, restores single-tenant schema)
- RLS: run `ALTER TABLE ... DISABLE ROW LEVEL SECURITY` — no data loss, isolation reverts to application-only

### Validation

- All 28 migrations run successfully against both SQLite and PostgreSQL
- All existing repository tests pass with `tenantId = 'default'` (no behaviour change for CE)
- Integration tests verify RLS isolation: 3 test cases (same tenant access, cross-tenant blocked, admin bypass)
- Performance: composite index scan for `WHERE tenant_id = ? ORDER BY created_at DESC LIMIT 50` under 5ms at 1M rows
- The migration script is idempotent: running `0029-add-tenant-id.ts` twice does not error or duplicate data

---

*This ADR was created following the MADR 4.0.0 standard. All DOMINUS ADRs should be consistent with the ADR series starting at `docs/adr/0001-project-architecture.md`. Template: `.claude/skills/adr/template.md`.*