> **Note (2026-07-02)** : shippé sur dev le 2026-06-27 via la réconciliation `ac2efc8`+`f9184c4` — les migrations citées 0042/0043 ont été renumérotées **0045_hard_tarot / 0046_fix_keyword_fks** (SQL identique). Ce document est conservé comme trace de décision.

# Design — Hash-partition `keywords` by `site_id` (local only)

- **Date:** 2026-06-22
- **Status:** design approved; spec pending user review
- **Branch:** `feature/duckdb-ingest-bench`
- **Related:** `2026-06-22-ingest-scalability-adr.md` (the measured decision).
- **Scope guard:** applied to **LOCAL databases only** (dev + test). **NOT prod** — no
  server deploy, no prod migration run. Brief-downtime cutover is therefore a non-issue.

## Goal

Convert `keywords` to a HASH-partitioned table by `site_id` (16 partitions) to relieve
the shared-index contention measured under concurrent multi-tenant ingest (~1.6×
throughput, lock-waits 6→1), **while preserving referential integrity** via composite
foreign keys.

## Non-goals

- No production rollout (local dev + test only).
- No zero-downtime/dual-write cutover (a brief exclusive lock on a small local DB is fine).
- No read-path lake (premortem, separate track). No DuckDB on the write path (ADR: rejected).

## The core problem

`keywords` PK is `id` alone. Postgres requires the partition key in every unique
constraint, so a partitioned `keywords` cannot have `UNIQUE(id)` — its PK must become
`(id, site_id)`. That invalidates the 4 FKs that reference `keywords.id` and every
`onConflict(target: id)`. The chosen resolution (user decision) is **composite FKs** —
preserve DB-level RI.

## Schema changes

### `keywords`
- PK `id` → composite **`(id, site_id)`**.
- `PARTITION BY HASH (site_id)`, **16 partitions** `keywords_0..15`.
- The 3 existing indexes (`keywords_site_idx` on `site_id`, `keywords_site_term_idx` on
  `(site_id, term)`, `keywords_stages_gin` GIN on `stages`) declared on the partitioned
  parent → propagate to each partition. (`keywords_site_term_idx` from the earlier index
  batch is retained.)

### Referencing tables → composite FK `(keyword_id, site_id) → keywords(id, site_id)`
| table | has `site_id` today? | change |
|---|---|---|
| `keyword_targets` | **no** | add `site_id text NOT NULL` (backfilled); FK composite |
| `content_keywords` | **no** | add `site_id text NOT NULL` (backfilled); FK composite |
| `content_items.primary_keyword_id` | yes (`site_id`) | FK `(primary_keyword_id, site_id)` composite; nullable → MATCH SIMPLE keeps set-null-on-delete |
| `keyword_shortlist` | yes (`site_id`) | FK `(keyword_id, site_id)` composite |

Backfill `site_id` from the parent keyword: `UPDATE t SET site_id = k.site_id FROM
keywords k WHERE t.keyword_id = k.id`. (Every keyword id encodes its site as
`${siteId}-${slug}`, but we backfill from the actual `keywords.site_id` column, not by
parsing the id.) All FK `onDelete` semantics preserved: cascade for
targets/content_keywords/shortlist, set-null for content_items.

## Code changes — `onConflict` targets

Change `onConflict(target: keywords.id)` → `target: [keywords.id, keywords.siteId]` at:
- `services/analytics/src/upsert.ts` — `upsertCanonical`
- `src/lib/gsc/discovery.ts:56`, `src/lib/gsc/sync.ts` (createGscKeywordsForSite)
- `src/lib/keywords/actions.ts` (the keyword upserts/insert-no-conflict, ~108/163/231/287)
- `src/lib/keywords/migrate-slugs.ts` (keyword conflict targets)

(Conflict clauses that target other tables — `keywordThemes.id`, `keyword_shortlist`,
etc. — are unaffected.) Drizzle accepts a multi-column `target` array natively.

## Cutover migration (hand-written SQL)

drizzle-kit cannot emit `PARTITION BY`, so this is a hand-authored migration file under
`drizzle/` (consistent with the repo's existing hand-written partial-index migrations).
Steps, run inside the migration:

1. `CREATE TABLE keywords_p (LIKE keywords INCLUDING DEFAULTS, PRIMARY KEY (id, site_id))
   PARTITION BY HASH (site_id)`; create 16 partitions; create the 3 indexes on the parent.
2. `INSERT INTO keywords_p SELECT * FROM keywords;` (copy existing rows).
3. `ALTER TABLE keyword_targets ADD COLUMN site_id text;` + backfill + `SET NOT NULL`.
   Same for `content_keywords`.
4. In one transaction: drop the 4 old FKs → `DROP TABLE keywords` → `ALTER TABLE
   keywords_p RENAME TO keywords` → recreate the 4 composite FKs + rename the
   partition/index objects to their canonical names. Brief `ACCESS EXCLUSIVE` lock
   (instant on small local data).

## Drizzle-sync (the riskiest part)

The Drizzle schema (`domain-schema.ts`) is updated to declare: the composite PK on
`keywords`, the new `site_id` columns on `keyword_targets`/`content_keywords`, and the 4
composite FK references. It does **NOT** (cannot) express `PARTITION BY`/partitions —
those exist only in the hand migration and are transparent to the ORM (PG routes
inserts/selects; Drizzle treats `keywords` as an ordinary table).

**Acceptance gate:** after applying the hand migration, `bunx drizzle-kit generate` must
produce an **empty diff** — i.e. the schema declarations exactly match the hand-built
table (column types, composite PK, FK definitions, index names). If it wants to alter
anything, the hand migration and the schema disagree and must be reconciled before
proceeding. This prevents a future autogenerated migration from fighting the partitioning.

## Testing

- DB tests (`bun run test` against `seo_insight_test`, migrated): FK cascade on keyword
  delete (→ keyword_targets, content_keywords, keyword_shortlist removed; content_items
  primary_keyword_id set null), composite `(id, site_id)` upsert/`DO NOTHING`, and an
  ingest round-trip via `upsertCanonical`.
- Re-bench partitioned `keywords` vs the pre-migration flat baseline **in isolation** (no
  other bench running) on a throwaway DB to reconfirm the ~1.6× the ADR measured under
  session contention.

## Rollout (LOCAL ONLY)

1. `bun run db:generate` is NOT used for the partition step (hand migration). Apply via
   `bun run db:migrate` (dev) and `bun run db:migrate:test` (test).
2. Run the full green gate (`bun run test:all`) + `drizzle-kit generate` empty-diff check.
3. **Stop there.** No prod deploy. The migration file ships on the branch; whether/when
   it reaches the server is a separate, later, explicit decision.

## Risks & mitigations

- **Drizzle drift** (schema ≠ hand table) → the empty-diff gate catches it before commit.
- **Backfill correctness** (new `site_id` on targets/content_keywords) → verified by a
  count/consistency check in the migration + a DB test asserting no null `site_id`.
- **FK match mode** on nullable `primary_keyword_id` → MATCH SIMPLE (Postgres default)
  leaves null rows unconstrained; verified by a set-null-on-delete test.
- **Composite-PK row identity** — `id` is still globally unique in practice
  (`${siteId}-${slug}`); the composite PK only relaxes the DB-enforced uniqueness scope
  to per-partition, which is fine since site_id is part of the id.

## Acceptance criteria

1. `keywords` is hash-partitioned (16 partitions), PK `(id, site_id)`, on dev + test.
2. The 4 referencing tables have composite FKs; cascades/set-null verified by tests.
3. All keyword `onConflict` sites target `(id, site_id)`; ingest + GSC sync tests pass.
4. `bun run test:all` green; `drizzle-kit generate` empty diff.
5. Nothing applied to prod.