> **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.

# Keywords Partitioning Implementation Plan

> **For agentic workers:** REQUIRED SUB-SKILL: Use superpowers:subagent-driven-development (recommended) or superpowers:executing-plans to implement this plan task-by-task. Steps use checkbox (`- [ ]`) syntax for tracking.

**Goal:** Convert `keywords` to a 16-way HASH-partitioned table by `site_id` with a composite PK `(id, site_id)`, preserving referential integrity via composite foreign keys — on LOCAL dev + test DBs only.

**Architecture:** A hand-written Drizzle migration (`0042`) rebuilds `keywords` as a partitioned table and swaps it in; the Drizzle schema declares the composite PK + new `site_id` columns + composite FKs (partitioning itself is invisible to the ORM). All keyword `onConflict` targets move to `(id, site_id)`.

**Tech Stack:** PostgreSQL (declarative partitioning), Drizzle ORM + drizzle-kit, bun, postgres.js.

## Global Constraints

- **LOCAL ONLY** — apply to dev (`bun run db:migrate`) and test (`bun run db:migrate:test`). **Never run against prod / the server.**
- Migration is **hand-written** (drizzle-kit cannot emit `PARTITION BY`); the generated snapshot/journal must still describe the final logical schema so `drizzle-kit generate` yields an **empty diff**.
- Green gate: `bun run test:all` → 0 fail. Service tests: `cd services/analytics && bun --env-file=../../.env.local --conditions=react-server test`.
- Lint: `bunx biome check <files>`. Commits: conventional; end with `Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>`.
- `keywords` columns (for `LIKE`): id, site_id, term, lang, country, theme, status, intent[], stages[], primary_source, sources[], metrics jsonb, trend jsonb, trend_by_source jsonb, serp_features[], briefs jsonb, gsc jsonb, gsc_by_country jsonb, serp jsonb, enrichment_exports jsonb. (`LIKE keywords INCLUDING DEFAULTS` copies them — do not enumerate by hand.)
- Spec: `docs/superpowers/specs/2026-06-22-keywords-partitioning-design.md`.

---

## File Structure

- `src/lib/db/domain-schema.ts` — declare composite PK on `keywords`; add `siteId` to `keywordTargets` + `contentKeywords`; switch the 4 keyword FKs to composite `foreignKey(...)`; import `foreignKey`.
- `drizzle/0042_*.sql` (+ `drizzle/meta/*`) — generated by drizzle-kit, then the `keywords` portion hand-replaced with the partition rebuild+swap.
- `services/analytics/src/upsert.ts`, `src/lib/keywords/actions.ts`, `src/lib/gsc/discovery.ts`, `src/lib/gsc/sync.ts`, `src/lib/keywords/migrate-slugs.ts` — `onConflict` targets → `[keywords.id, keywords.siteId]`.
- `src/lib/db/partitioning.test.ts` (new) — FK cascade/set-null + composite-upsert + ingest round-trip.

---

## Task 1: Schema declarations + hand-written partition migration

**Files:**
- Modify: `src/lib/db/domain-schema.ts`
- Create: `drizzle/0042_*.sql` (via generate, then hand-edit) + `drizzle/meta/*`

**Interfaces:**
- Produces: `keywords` PK `(id, site_id)`; `keyword_targets.site_id`, `content_keywords.site_id` (NOT NULL); composite FKs `(<kw_col>, site_id) → keywords(id, site_id)`. Consumed by Task 2 (`onConflict` targets) and Task 3 (tests).

- [ ] **Step 1: Edit the Drizzle schema**

In `src/lib/db/domain-schema.ts`:

(a) Add `foreignKey` to the `drizzle-orm/pg-core` import (alongside `primaryKey`, `index`, …).

(b) `keywords` table — make the PK composite. Change the `id` column from `text("id").primaryKey()` to `text("id").notNull()`, and add to the table's second-arg array (which currently has `index("keywords_site_idx").on(t.siteId)`):
```ts
  (t) => [
    primaryKey({ columns: [t.id, t.siteId] }),
    index("keywords_site_idx").on(t.siteId),
  ],
```
(Leave `keywords_site_term_idx` and `keywords_stages_gin` wherever they are already declared.)

(c) `keywordTargets` — add `siteId` and a composite FK. Replace the inline `.references()` on `keywordId` with a plain column, and add the FK in the callback:
```ts
export const keywordTargets = pgTable(
  "keyword_targets",
  {
    keywordId: text("keyword_id").notNull(),
    siteId: text("site_id").notNull(),
    country: text("country").notNull(),
    status: text("status").$type<TargetStatus>().notNull(),
  },
  (t) => [
    primaryKey({ columns: [t.keywordId, t.country] }),
    foreignKey({
      columns: [t.keywordId, t.siteId],
      foreignColumns: [keywords.id, keywords.siteId],
    }).onDelete("cascade"),
  ],
);
```

(d) `contentKeywords` — add `siteId`, composite FK (replace the inline `.references(() => keywords.id …)` on `keywordId`):
```ts
    keywordId: text("keyword_id").notNull(),
    siteId: text("site_id").notNull(),
    role: text("role").$type<"primary" | "secondary">().notNull().default("secondary"),
```
and in the callback add:
```ts
    foreignKey({
      columns: [t.keywordId, t.siteId],
      foreignColumns: [keywords.id, keywords.siteId],
    }).onDelete("cascade"),
```
(keep the existing `primaryKey({ columns: [t.contentItemId, t.keywordId] })` and `content_keywords_keyword_idx`).

(e) `contentItems.primaryKeywordId` — it already has `siteId`. Replace the inline `.references(() => keywords.id, { onDelete: "set null" })` with a plain nullable column `text("primary_keyword_id")`, and add to the callback:
```ts
    foreignKey({
      columns: [t.primaryKeywordId, t.siteId],
      foreignColumns: [keywords.id, keywords.siteId],
    }).onDelete("set null"),
```

(f) `keywordShortlist` — already has `siteId`. Replace the inline `.references(() => keywords.id, { onDelete: "cascade" })` on `keywordId` with a plain column, add to callback:
```ts
    foreignKey({
      columns: [t.keywordId, t.siteId],
      foreignColumns: [keywords.id, keywords.siteId],
    }).onDelete("cascade"),
```

- [ ] **Step 2: Generate the migration + snapshot**

Run: `bun run db:generate`
Expected: a new `drizzle/0042_*.sql` plus updated `drizzle/meta/0042_snapshot.json` + `_journal.json`. The generated SQL will contain naive `ALTER TABLE "keywords" …` PK statements (WRONG for partitioning) and the `ADD COLUMN site_id` / FK statements (mostly right). **Read it.**

- [ ] **Step 3: Hand-replace the migration body with the partition rebuild**

Overwrite the generated `drizzle/0042_*.sql` body with the following (keep the filename + the `--> statement-breakpoint` separators between statements). Do NOT touch `drizzle/meta/*` — the snapshot must keep describing the final logical schema so the empty-diff gate passes.

```sql
-- Hash-partition keywords by site_id (16 partitions) + composite FKs. Local only.
CREATE TABLE "keywords_p" (LIKE "keywords" INCLUDING DEFAULTS) PARTITION BY HASH ("site_id");--> statement-breakpoint
ALTER TABLE "keywords_p" ADD PRIMARY KEY ("id", "site_id");--> statement-breakpoint
CREATE TABLE "keywords_p_0" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 0);--> statement-breakpoint
CREATE TABLE "keywords_p_1" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 1);--> statement-breakpoint
CREATE TABLE "keywords_p_2" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 2);--> statement-breakpoint
CREATE TABLE "keywords_p_3" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 3);--> statement-breakpoint
CREATE TABLE "keywords_p_4" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 4);--> statement-breakpoint
CREATE TABLE "keywords_p_5" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 5);--> statement-breakpoint
CREATE TABLE "keywords_p_6" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 6);--> statement-breakpoint
CREATE TABLE "keywords_p_7" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 7);--> statement-breakpoint
CREATE TABLE "keywords_p_8" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 8);--> statement-breakpoint
CREATE TABLE "keywords_p_9" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 9);--> statement-breakpoint
CREATE TABLE "keywords_p_10" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 10);--> statement-breakpoint
CREATE TABLE "keywords_p_11" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 11);--> statement-breakpoint
CREATE TABLE "keywords_p_12" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 12);--> statement-breakpoint
CREATE TABLE "keywords_p_13" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 13);--> statement-breakpoint
CREATE TABLE "keywords_p_14" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 14);--> statement-breakpoint
CREATE TABLE "keywords_p_15" PARTITION OF "keywords_p" FOR VALUES WITH (MODULUS 16, REMAINDER 15);--> statement-breakpoint
INSERT INTO "keywords_p" SELECT * FROM "keywords";--> statement-breakpoint
ALTER TABLE "keyword_targets" ADD COLUMN "site_id" text;--> statement-breakpoint
UPDATE "keyword_targets" t SET "site_id" = k."site_id" FROM "keywords" k WHERE t."keyword_id" = k."id";--> statement-breakpoint
ALTER TABLE "keyword_targets" ALTER COLUMN "site_id" SET NOT NULL;--> statement-breakpoint
ALTER TABLE "content_keywords" ADD COLUMN "site_id" text;--> statement-breakpoint
UPDATE "content_keywords" t SET "site_id" = k."site_id" FROM "keywords" k WHERE t."keyword_id" = k."id";--> statement-breakpoint
ALTER TABLE "content_keywords" ALTER COLUMN "site_id" SET NOT NULL;--> statement-breakpoint
DROP TABLE "keywords" CASCADE;--> statement-breakpoint
ALTER TABLE "keywords_p" RENAME TO "keywords";--> statement-breakpoint
CREATE INDEX "keywords_site_idx" ON "keywords" USING btree ("site_id");--> statement-breakpoint
CREATE INDEX "keywords_site_term_idx" ON "keywords" USING btree ("site_id","term");--> statement-breakpoint
CREATE INDEX "keywords_stages_gin" ON "keywords" USING gin ("stages");--> statement-breakpoint
ALTER TABLE "keyword_targets" ADD CONSTRAINT "keyword_targets_keyword_id_site_id_keywords_id_site_id_fk" FOREIGN KEY ("keyword_id","site_id") REFERENCES "public"."keywords"("id","site_id") ON DELETE cascade;--> statement-breakpoint
ALTER TABLE "content_keywords" ADD CONSTRAINT "content_keywords_keyword_id_site_id_keywords_id_site_id_fk" FOREIGN KEY ("keyword_id","site_id") REFERENCES "public"."keywords"("id","site_id") ON DELETE cascade;--> statement-breakpoint
ALTER TABLE "content_items" ADD CONSTRAINT "content_items_primary_keyword_id_site_id_keywords_id_site_id_fk" FOREIGN KEY ("primary_keyword_id","site_id") REFERENCES "public"."keywords"("id","site_id") ON DELETE set null;--> statement-breakpoint
ALTER TABLE "keyword_shortlist" ADD CONSTRAINT "keyword_shortlist_keyword_id_site_id_keywords_id_site_id_fk" FOREIGN KEY ("keyword_id","site_id") REFERENCES "public"."keywords"("id","site_id") ON DELETE cascade;
```

Notes: `DROP TABLE "keywords" CASCADE` drops the old table, its indexes, and the 4 old FKs in one shot; the canonical index names are then free to reuse on the renamed table. `keywords_p`'s composite PK constraint is auto-named — that's fine (the snapshot names the PK from the schema; if the empty-diff gate in Step 5 complains about the PK constraint name, `ALTER TABLE "keywords" RENAME CONSTRAINT <auto> TO "keywords_id_site_id_pk"` to match — discover the auto name via `\d keywords`).

- [ ] **Step 4: Apply to dev + test**

Run:
```bash
bun run db:migrate
bun run db:migrate:test
```
Expected: `✓ migrations applied` for both. Then verify the shape on dev:
```bash
psql "$(grep -m1 '^DATABASE_URL=' .env.local | cut -d= -f2- | tr -d '"')" -c "\d+ keywords" -c "select count(*) from pg_inherits where inhparent='keywords'::regclass;"
```
Expected: `keywords` shown as `partitioned table, partition key: HASH (site_id)`; 16 child partitions; the 3 indexes present.

- [ ] **Step 5: Empty-diff gate**

Run: `bun run db:generate`
Expected: **"No schema changes, nothing to migrate"** (or it generates NOTHING new). If it wants to alter `keywords`/FKs/columns, the schema and the hand migration disagree — reconcile (usually a PK/FK constraint name or a missing column attribute) and re-run until the diff is empty. If a NEW migration file was created, delete it after reconciling the mismatch in the schema or the `0042` snapshot.

- [ ] **Step 6: Commit**

```bash
bunx biome check src/lib/db/domain-schema.ts
git add src/lib/db/domain-schema.ts drizzle/0042_*.sql drizzle/meta/
git commit -m "$(cat <<'MSG'
feat(db): hash-partition keywords by site_id + composite FKs (local)

keywords → 16-way HASH(site_id), composite PK (id, site_id). keyword_targets
and content_keywords gain site_id; all 4 keyword FKs become composite
(<col>, site_id) → keywords(id, site_id), preserving cascade/set-null.
Hand-written rebuild+swap migration (drizzle-kit can't emit PARTITION BY);
drizzle-kit generate is an empty diff.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
MSG
)"
```

---

## Task 2: Move keyword `onConflict` targets to `(id, site_id)`

**Files:**
- Modify: `services/analytics/src/upsert.ts`, `src/lib/keywords/actions.ts`, `src/lib/gsc/discovery.ts`, `src/lib/gsc/sync.ts`, `src/lib/keywords/migrate-slugs.ts`

**Interfaces:**
- Consumes: composite PK `(keywords.id, keywords.siteId)` from Task 1.

- [ ] **Step 1: Find every keyword conflict target**

Run: `grep -rnE "onConflictDoUpdate|onConflictDoNothing" src/lib services/analytics --include='*.ts' | grep -v '.test.'`
For each call on the **`keywords`** table, the conflict target must become `[keywords.id, keywords.siteId]`. Leave conflict clauses on OTHER tables (`keywordThemes.id`, `keyword_shortlist`, `domainBacklinks`, `backlinks`, `mcpCreditPurchases`, etc.) unchanged.

- [ ] **Step 2: Update each site**

- `services/analytics/src/upsert.ts` — `upsertCanonical` `.onConflictDoUpdate({ target: keywords.id, … })` → `target: [keywords.id, keywords.siteId]` (keep the `set` block).
- `src/lib/gsc/discovery.ts:56` — `.onConflictDoNothing({ target: keywordsTable.id })` → `target: [keywordsTable.id, keywordsTable.siteId]`.
- `src/lib/keywords/actions.ts` — the keyword upserts at ~108/163 (`onConflictDoUpdate`) and the inserts at ~231/287 (`onConflictDoNothing`): for any whose insert targets the `keywords` table, set `target: [keywords.id, keywords.siteId]`. (Read each; the one at ~388 targets `keywordThemes.id` — leave it.)
- `src/lib/gsc/sync.ts` — `createGscKeywordsForSite`'s keyword insert (the `onConflictDoNothing` the comment at :256 refers to; locate the actual `.onConflictDoNothing(` on `keywords`) → add `target: [keywords.id, keywords.siteId]`.
- `src/lib/keywords/migrate-slugs.ts` — the keyword `onConflictDoNothing()` (the one inserting into `keywords`, ~:145) → `{ target: [keywords.id, keywords.siteId] }`. (The `keyword_shortlist` one at ~:116 is a different table — leave it.)

A bare `onConflictDoNothing()` with no target relies on the primary key; since the PK is now composite it still works, but make the target explicit `[keywords.id, keywords.siteId]` for clarity and to be index-correct.

- [ ] **Step 3: Typecheck + lint**

Run: `bunx tsc --noEmit` (expect clean) and `bunx biome check` on the touched files.

- [ ] **Step 4: Commit**

```bash
git add services/analytics/src/upsert.ts src/lib/keywords/actions.ts src/lib/gsc/discovery.ts src/lib/gsc/sync.ts src/lib/keywords/migrate-slugs.ts
git commit -m "$(cat <<'MSG'
fix(db): keyword onConflict targets → (id, site_id)

The keywords PK is now composite (id, site_id); every keyword upsert/insert
conflict target updated accordingly (upsert, GSC discovery/sync, actions,
migrate-slugs). Other tables' conflict clauses unchanged.

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
MSG
)"
```

---

## Task 3: Tests — referential integrity + composite upsert + ingest

**Files:**
- Create: `src/lib/db/partitioning.test.ts`

**Interfaces:**
- Consumes: the migrated test DB (composite PK + composite FKs) from Task 1; the updated `onConflict` from Task 2.

- [ ] **Step 1: Write the failing tests**

Create `src/lib/db/partitioning.test.ts` (DB-gated, mirrors the repo's other postgres tests — `describe.skipIf(!process.env.DB_TESTS)`):

```ts
import { afterEach, describe, expect, test } from "bun:test";
import { eq } from "drizzle-orm";
import { db } from "@/lib/db/client";
import {
  contentItems, keywords, keywordShortlist, keywordTargets,
} from "@/lib/db/domain-schema";

const RUN = process.env.DB_TESTS === "1";
const SITE = "part-test-site";
const KID = `${SITE}-kw1`;

describe.skipIf(!RUN)("keywords partitioning (postgres)", () => {
  afterEach(async () => {
    await db.delete(keywords).where(eq(keywords.siteId, SITE));
  });

  async function seedKeyword() {
    // NOTE: requires an existing site row `part-test-site` (org/site fixture);
    // the test sets up org+site in beforeEach via the shared test helpers — see
    // src/lib/sites/*.test.ts for the seed pattern and reuse it here.
    await db.insert(keywords).values({
      id: KID, siteId: SITE, term: "t", status: "idea",
      primarySource: "semrush", intent: [], sources: [], stages: [],
      metrics: {}, trend: [], trendBySource: {}, briefs: {}, gscByCountry: {},
      serp: {}, enrichmentExports: {},
    });
  }

  test("composite upsert: same (id, site_id) is idempotent", async () => {
    await seedKeyword();
    await db.insert(keywords).values({
      id: KID, siteId: SITE, term: "t2", status: "idea",
      primarySource: "semrush", intent: [], sources: [], stages: [],
      metrics: {}, trend: [], trendBySource: {}, briefs: {}, gscByCountry: {},
      serp: {}, enrichmentExports: {},
    }).onConflictDoUpdate({
      target: [keywords.id, keywords.siteId], set: { term: "t2" },
    });
    const rows = await db.select().from(keywords).where(eq(keywords.id, KID));
    expect(rows).toHaveLength(1);
    expect(rows[0]?.term).toBe("t2");
  });

  test("deleting a keyword cascades to keyword_targets and shortlist", async () => {
    await seedKeyword();
    await db.insert(keywordTargets).values({ keywordId: KID, siteId: SITE, country: "FR", status: "candidate" });
    await db.insert(keywordShortlist).values({ siteId: SITE, keywordId: KID });
    await db.delete(keywords).where(eq(keywords.id, KID));
    expect(await db.select().from(keywordTargets).where(eq(keywordTargets.keywordId, KID))).toHaveLength(0);
    expect(await db.select().from(keywordShortlist).where(eq(keywordShortlist.keywordId, KID))).toHaveLength(0);
  });
});
```
(Use the exact column values your schema requires; reuse the org+site seeding helper the sibling postgres tests use so the FK chain is satisfied. The `TargetStatus` value `"candidate"` must be a valid member — check `src/lib/keywords/targets.ts` and substitute a real one.)

- [ ] **Step 2: Run — verify they fail or pass meaningfully**

Run: `DB_TESTS=1 DATABASE_URL=postgresql://humperdoo@localhost:5432/seo_insight_test bun --env-file=.env.local --conditions=react-server test src/lib/db/partitioning.test.ts`
Expected: PASS if Tasks 1–2 are correct (the schema + migration already make these behaviors real). If a test fails, it has found a real defect in the migration/FKs — fix the migration, not the test.

- [ ] **Step 3: Full green gate**

Run: `bun run test:all` → expect **0 fail**. Also run the analytics service tests (`cd services/analytics && bun --env-file=../../.env.local --conditions=react-server test`) → the ingest upsert path still green.

- [ ] **Step 4: Commit**

```bash
git add src/lib/db/partitioning.test.ts
git commit -m "$(cat <<'MSG'
test(db): keywords partitioning — composite upsert + FK cascade

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
MSG
)"
```

---

## Self-Review

- **Spec coverage:** schema changes (keywords PK+partition, 4 FK tables + backfill) → Task 1; `onConflict` targets → Task 2; tests (cascade/set-null/composite upsert/ingest) → Task 3; empty-diff gate → Task 1 Step 5; local-only rollout → Task 1 Step 4 (`db:migrate` + `db:migrate:test`, no prod). Re-bench is optional/manual (bench scripts already on branch) — noted, not a coding task. ✅
- **Placeholder scan:** the only run-time-discovered values are the auto-generated PK/FK constraint names (Step 3 note tells how to find + rename) and the real `TargetStatus` enum member (Task 3 tells where to look) — these cannot be known without reading the DB/enum, and the exact discovery command is given. ✅
- **Type consistency:** `keywords.siteId`, `keywordTargets.siteId`, `contentKeywords.siteId`, composite `foreignKey({columns,foreignColumns})`, and `target: [keywords.id, keywords.siteId]` are used identically across Tasks 1–3. ✅