# Keywords Partitioning Migration Reconciliation — Implementation Plan

> **For agentic workers:** Execute task-by-task in the isolated worktree branch `worktree-agent-aa4c21e8d426a3528`. NO merge, NO push, do not touch `dev`/`main`. Steps use checkbox (`- [ ]`) syntax.

**Goal:** Produce a clean linear migration history where the `keywords` hash-partitioning (composite PK `(id, site_id)` + composite FKs) lands as migrations `0045`/`0046` AFTER dev's auth migrations `0042`–`0044`, with `domain-schema.ts` reflecting the full partitioning and zero drift.

**Architecture:** The worktree branch currently sits BELOW the dev/feature merge-base. Rebase it onto `dev` (which already carries `0041`–`0044` + the interim `f9184c4` app-code fixes), then re-author the two partitioning migrations renumbered on top of `0044`, reconcile `domain-schema.ts` to the full composite-PK model, and port the feature-only app/test deltas that `f9184c4` did not already backport.

**Tech Stack:** Drizzle ORM 0.45.2 / drizzle-kit, Postgres (partitioned tables), Bun test, biome (format/lint).

## Global Constraints
- Branch: `worktree-agent-aa4c21e8d426a3528` only. No merge commit (linear history required). No push.
- Migrations `0042_aberrant_smasher`, `0043_quick_hellion`, `0044_brief_vengeance` (auth/2FA/username/admin) are SHIPPED — never modify.
- NEVER run migrations against the dev DB. Validate the chain only against a throwaway `seo_insight_migcheck` DB. `test:all` targets `seo_insight_test`.
- Keep `f9184c4`'s gsc onConflict `(id, site_id)` + site_id-on-insert fixes (already on dev — do not regress).

## Renumbering map (old → new)
| feature (old) | reconciled (new) | content |
|---|---|---|
| `0042_hard_tarot` | `0045_hard_tarot` | hash-partition keywords + composite PK + composite FKs (rebuild+swap) |
| `0043_fix_keyword_fks` | `0046_fix_keyword_fks` | restore keywords→sites FK + content_items column-list SET NULL |

`when`: 0044=1782545382164 → 0045=1782545400000, 0046=1782545500000 (strictly ascending).

## Included vs excluded (from feature/duckdb-ingest-bench)
INCLUDE: `6e7df24` schema model; `0042/0043` SQL → 0045/0046; `6ebcde1` join-scoping; `7032d66` analytics created-count; `438ec6f` partitioning test; `6f5d311` DB-test infra; `09488e8` analytics onConflict (folded into feature's final upsert.ts).
EXCLUDE: `ac31a3d`, `5a3bcdd`, `c5a6bff` (DuckDB benchmark); `ae2b8e3`, `135add0`, `007c9aa` (docs); the untracked premortem spec.
NOT NEEDED (f9184c4 already covers, only cosmetic): `keywords/actions.ts`, `content/import-actions.ts`, `gsc/discovery.ts`, `keywords/migrate-slugs.ts`, `migrate-slugs.test.ts`.

---

### Task 1: Rebase worktree branch onto dev
- [ ] Discard worktree-noise tracked changes; `git reset --hard dev`.
- [ ] Verify 0044 migration + snapshot present; journal ends at idx44.

### Task 2: Reconcile domain-schema.ts
- [ ] `git checkout feature/duckdb-ingest-bench -- src/lib/db/domain-schema.ts`; `bun run format`; typecheck clean.

### Task 3: Generate authoritative snapshot
- [ ] `bun run db:generate` (URL set) → keep generated `0045_snapshot.json`; discard auto `.sql`.

### Task 4: Re-author 0045 + 0046
- [ ] `0045_hard_tarot.sql` = feature `0042_hard_tarot.sql`; `0046_fix_keyword_fks.sql` = feature `0043`.
- [ ] `0045_snapshot.json` = generated (prevId=0044). `0046_snapshot.json` = clone w/ fresh id, prevId=0045.
- [ ] Journal idx45/idx46 with ascending `when`.
- [ ] `bun run db:generate` → MUST say "No schema changes" (zero drift).

### Task 5: Port app + test deltas
- [ ] `git checkout feature -- content/actions.ts content/queries.ts keywords/queries.ts`.
- [ ] `git checkout feature -- services/analytics/src/upsert.ts + 5 analytics test files`.
- [ ] `git checkout feature -- db.test.ts seed.ts partitioning.test.ts`.
- [ ] `bun run format`; typecheck clean.

### Task 6: Fresh-DB replay
- [ ] `createdb seo_insight_migcheck`; migrate against it; inspect `\d+ keywords` (hash partitions + composite PK/FKs); `dropdb`.

### Task 7: `bun run test:all` green.

### Task 8: Commit in logical chunks (no push).