# Keywords — GSC Exclusion + Cross-source Median — 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:** (A) Let users exclude unwanted GSC-imported keywords from Recherche with a persistent per-site exclusion list so a re-sync never recreates them; (B) add toggle-able "consensus" columns showing the median of Volume/KD/CPC across SEO sources. **Architecture:** New `keyword_exclusions(site_id, slug)` table; `excludeKeywordsAction` deletes the keywords and records their slugs; `syncGscForSite` skips excluded slugs. A pure `consensusMetrics` util (median) feeds 3 new hidden-by-default columns in the shared `columns.tsx`. **Tech Stack:** Drizzle + Postgres, Next 16 Server Actions/client components, TanStack Table, shadcn `AlertDialog`, `import-core` (wasm `slugify`), bun + biome, bun:test. Spec: `docs/superpowers/specs/2026-06-09-keywords-gsc-exclusion-cross-source-design.md`. **Branch:** create from `dev` before Task 1: ```bash git checkout dev && git checkout -b feature/keywords-exclusion-consensus ``` --- ## File Structure - **Modify** `src/lib/db/domain-schema.ts` — add `keywordExclusions` table. - **Generate** a Drizzle migration under `drizzle/` + apply it. - **Create** `src/lib/keywords/consensus.ts` (+ `.test.ts`) — pure median util. - **Modify** `src/lib/keywords/queries.ts` — add `getExcludedSlugs(siteId)`. - **Modify** `src/lib/keywords/actions.ts` — add `excludeKeywordsAction(siteId, ids)`. - **Modify** `src/lib/gsc/sync.ts` (+ `sync.test.ts`) — skip excluded slugs. - **Modify** `src/components/keywords/columns.tsx` — 3 consensus columns. - **Modify** `src/components/keywords/research/targeting-bar.tsx` — "Exclure" button. - **Modify** `src/components/keywords/research/research-container.tsx` — exclude handler + AlertDialog. Key facts: `Keyword.metrics: Partial>`; `SourceMetrics = { volume: number; kd: number; cpc: number; … }`. SEO sources = `semrush | mangools | ahrefs` (gsc has no vol/kd/cpc). Keyword actions scope by `siteId` only (existing repo pattern). `wasm.slugify` (from `import-core`) is how GSC ids are slugged (`createGscKeywordsForSite`: `${siteId}-${wasm.slugify(query)}`). --- ## Task 1: `keyword_exclusions` table + migration **Files:** Modify `src/lib/db/domain-schema.ts`; generate `drizzle/*`. - [ ] **Step 1: Add the table** In `src/lib/db/domain-schema.ts`, after the `keywordTargets` table definition (the composite-PK example, ~line 115), add: ```ts /** Per-site slugs to keep OUT of a site's keywords — a GSC re-sync skips them. * Populated when the user excludes an unwanted GSC-imported keyword. */ export const keywordExclusions = pgTable( "keyword_exclusions", { siteId: text("site_id") .notNull() .references(() => sites.id, { onDelete: "cascade" }), slug: text("slug").notNull(), createdAt: timestamp("created_at", { withTimezone: true }) .defaultNow() .notNull(), }, (t) => [primaryKey({ columns: [t.siteId, t.slug] })], ); ``` (`pgTable`, `text`, `timestamp`, `primaryKey`, and `sites` are already imported/defined in this file.) - [ ] **Step 2: Generate the migration** Run: `bun run db:generate` Expected: a new `drizzle/NNNN_*.sql` creating `keyword_exclusions`. (Do NOT run `db:push`.) - [ ] **Step 3: Apply it to the dev DB** Run: `bun run db:migrate` Expected: migration applied, no error. - [ ] **Step 4: Apply it to the test DB** (so DB tests in later tasks see the table) Run: `bun run db:migrate:test` Expected: applied to the test DB. - [ ] **Step 5: Typecheck + commit** Run: `npx tsc --noEmit` → `No errors found`. ```bash git add src/lib/db/domain-schema.ts drizzle/ git commit -m "feat(db): keyword_exclusions table (per-site GSC exclusion list)" ``` --- ## Task 2: `consensusMetrics` util (pure, TDD) **Files:** Create `src/lib/keywords/consensus.ts`, Test `src/lib/keywords/consensus.test.ts`. - [ ] **Step 1: Write the failing test** ```ts // src/lib/keywords/consensus.test.ts import { expect, test } from "bun:test"; import type { KeywordSource, SourceMetrics } from "./types"; import { consensusMetrics } from "./consensus"; const m = ( partial: Partial>>, ): Partial> => partial as Partial>; test("no SEO source → all null", () => { expect(consensusMetrics(m({}))).toEqual({ volume: null, kd: null, cpc: null }); // gsc is not an SEO metric source expect(consensusMetrics(m({ gsc: { volume: 999 } }))).toEqual({ volume: null, kd: null, cpc: null, }); }); test("one source → its value", () => { expect( consensusMetrics(m({ semrush: { volume: 1000, kd: 40, cpc: 2 } })), ).toEqual({ volume: 1000, kd: 40, cpc: 2 }); }); test("two sources → mean of the two (even count median)", () => { expect( consensusMetrics( m({ semrush: { volume: 900, kd: 30, cpc: 1 }, mangools: { volume: 1100, kd: 50, cpc: 3 } }), ), ).toEqual({ volume: 1000, kd: 40, cpc: 2 }); }); test("three sources → middle value (odd count median)", () => { expect( consensusMetrics( m({ semrush: { volume: 900, kd: 30, cpc: 1 }, mangools: { volume: 1400, kd: 50, cpc: 5 }, ahrefs: { volume: 1000, kd: 45, cpc: 2 }, }), ), ).toEqual({ volume: 1000, kd: 45, cpc: 2 }); }); test("per-field independence: a field missing on one source is ignored for that field", () => { // mangools has volume only; consensus volume uses both, kd/cpc use semrush only const r = consensusMetrics( m({ semrush: { volume: 1000, kd: 40, cpc: 2 }, mangools: { volume: 2000 } as Partial }), ); expect(r.volume).toBe(1500); expect(r.kd).toBe(40); expect(r.cpc).toBe(2); }); ``` - [ ] **Step 2: Run, expect FAIL:** `bun test src/lib/keywords/consensus.test.ts` → module/export missing. - [ ] **Step 3: Implement (`src/lib/keywords/consensus.ts`):** ```ts // src/lib/keywords/consensus.ts — median of SEO metrics across the sources that // actually carry them (semrush/mangools/ahrefs; gsc has no vol/kd/cpc). import type { KeywordSource, SourceMetrics } from "./types"; const SEO_SOURCES: KeywordSource[] = ["semrush", "mangools", "ahrefs"]; function median(values: number[]): number | null { if (values.length === 0) return null; const s = [...values].sort((a, b) => a - b); const mid = Math.floor(s.length / 2); return s.length % 2 ? s[mid] : (s[mid - 1] + s[mid]) / 2; } export type ConsensusMetrics = { volume: number | null; kd: number | null; cpc: number | null; }; /** Median of each metric across the SEO sources present on the keyword. * 0 sources → null; 1 → that value; ≥2 → median (mean of the two centre values * when even). Each field is computed independently. */ export function consensusMetrics( metrics: Partial>, ): ConsensusMetrics { const pick = (field: "volume" | "kd" | "cpc") => median( SEO_SOURCES.map((s) => metrics[s]?.[field]).filter( (v): v is number => v != null, ), ); return { volume: pick("volume"), kd: pick("kd"), cpc: pick("cpc") }; } ``` - [ ] **Step 4: Run, expect PASS:** `bun test src/lib/keywords/consensus.test.ts` → 5 pass. - [ ] **Step 5: tsc + biome + commit** `npx tsc --noEmit` → clean. `bunx biome check --write src/lib/keywords/consensus.ts src/lib/keywords/consensus.test.ts` → clean. ```bash git add src/lib/keywords/consensus.ts src/lib/keywords/consensus.test.ts git commit -m "feat(keywords): cross-source median (consensusMetrics) util" ``` --- ## Task 3: exclusion query + action **Files:** Modify `src/lib/keywords/queries.ts`, `src/lib/keywords/actions.ts`, Test `src/lib/keywords/actions.test.ts`. - [ ] **Step 1: Add `getExcludedSlugs` to `queries.ts`** Add the import for `keywordExclusions` to the `@/lib/db/domain-schema` import in `src/lib/keywords/queries.ts`, then add: ```ts /** The set of slugs excluded for a site (a GSC re-sync skips them). */ export async function getExcludedSlugs(siteId: string): Promise> { const rows = await db .select({ slug: keywordExclusions.slug }) .from(keywordExclusions) .where(eq(keywordExclusions.siteId, siteId)); return new Set(rows.map((r) => r.slug)); } ``` (`db`, `eq` are already imported in `queries.ts`; confirm and add `eq` from `drizzle-orm` if missing.) - [ ] **Step 2: Add `excludeKeywordsAction` to `actions.ts`** In `src/lib/keywords/actions.ts` (`"use server"`), add `keywordExclusions` to the `@/lib/db/domain-schema` import, add `import * as wasm from "import-core";`, then add (model it on the existing `deleteKeywordsAction`): ```ts /** Delete the given keywords AND record their slugs in keyword_exclusions so a * GSC re-sync won't recreate them. Scoped to the site. */ export async function excludeKeywordsAction( siteId: string, ids: string[], ): Promise<{ excluded: number }> { if (ids.length === 0) return { excluded: 0 }; const rows = await db .select({ id: keywords.id, term: keywords.term }) .from(keywords) .where(and(eq(keywords.siteId, siteId), inArray(keywords.id, ids))); if (rows.length === 0) return { excluded: 0 }; await db.transaction(async (tx) => { await tx .insert(keywordExclusions) .values(rows.map((r) => ({ siteId, slug: wasm.slugify(r.term) }))) .onConflictDoNothing(); await tx.delete(keywords).where( and( eq(keywords.siteId, siteId), inArray( keywords.id, rows.map((r) => r.id), ), ), ); }); return { excluded: rows.length }; } ``` - [ ] **Step 3: Add a DB test** in `src/lib/keywords/actions.test.ts` (follow the file's existing setup/teardown + `assertTestDb()` pattern — read the file first). Test: seed a site + 2 keywords; `excludeKeywordsAction(site, [id1])` → returns `{ excluded: 1 }`, the keyword row is gone, and `getExcludedSlugs(site)` contains `slugify(term1)`. ```ts // add near the other tests; reuse the file's existing SITE/ORG/USR setup helpers test("excludeKeywordsAction deletes the keyword and records its slug", async () => { // (seed one keyword id=`${SITE}-foo-bar`, term="Foo Bar" via the file's helper) const res = await excludeKeywordsAction(SITE, [`${SITE}-foo-bar`]); expect(res).toEqual({ excluded: 1 }); const remaining = await db.select().from(keywords).where(eq(keywords.id, `${SITE}-foo-bar`)); expect(remaining).toHaveLength(0); const slugs = await getExcludedSlugs(SITE); expect(slugs.has("foo-bar")).toBe(true); }); ``` Import `excludeKeywordsAction` from `./actions` and `getExcludedSlugs` from `./queries`. Match the actual seed helper names in the file (read it). - [ ] **Step 4: Run the DB test** Run: `bun run test src/lib/keywords/actions.test.ts` (the `test` script points DATABASE_URL at the test DB). Expected: the new test passes (plus the file's existing tests). - [ ] **Step 5: tsc + biome + commit** `npx tsc --noEmit` → clean. `bunx biome check --write src/lib/keywords/queries.ts src/lib/keywords/actions.ts src/lib/keywords/actions.test.ts` → clean. ```bash git add src/lib/keywords/queries.ts src/lib/keywords/actions.ts src/lib/keywords/actions.test.ts git commit -m "feat(keywords): excludeKeywordsAction + getExcludedSlugs" ``` --- ## Task 4: sync skips excluded slugs **Files:** Modify `src/lib/gsc/sync.ts`, Test `src/lib/gsc/sync.test.ts`. - [ ] **Step 1: Apply the exclusion set in `syncGscForSite`** In `src/lib/gsc/sync.ts`: add `import * as wasm from "import-core";` and `import { getExcludedSlugs } from "@/lib/keywords/queries";`. Inside `syncGscForSite`, after loading the site and BEFORE the row loop, add: ```ts const excluded = await getExcludedSlugs(siteId); ``` Then at the very top of the `for (const row of rows)` loop body, add: ```ts if (excluded.has(wasm.slugify(row.query))) continue; ``` (This skips an excluded query for BOTH the overlay and the create paths.) - [ ] **Step 2: Extend `sync.test.ts`** Add a test (the file already seeds SITE/ORG/USR + an existing keyword; import `keywordExclusions` from `@/lib/db/domain-schema` and clean it in `beforeEach` alongside the others): ```ts test("skips GSC queries whose slug is excluded for the site", async () => { await db.insert(keywordExclusions).values({ siteId: SITE, slug: "unwanted-term" }); const res = await syncGscForSite(SITE, [ { query: "unwanted term", clicks: 5, impressions: 80, ctr: 0.06, position: 3 }, // excluded → skipped { query: "wanted term", clicks: 4, impressions: 70, ctr: 0.05, position: 7 }, // created ]); expect(res).toEqual({ matched: 0, created: 1, queries: 2 }); const all = await db.select().from(kw).where(eq(kw.siteId, SITE)); expect(all.some((k) => k.term === "unwanted term")).toBe(false); expect(all.some((k) => k.term === "wanted term")).toBe(true); }); ``` Add `await db.delete(keywordExclusions).where(eq(keywordExclusions.siteId, SITE));` to the `beforeEach` cleanup block. - [ ] **Step 3: Run the gsc suite** Run: `bun run test src/lib/gsc/sync.test.ts` Expected: all pass (existing + new). - [ ] **Step 4: tsc + biome + commit** `npx tsc --noEmit` → clean. `bunx biome check --write src/lib/gsc/sync.ts src/lib/gsc/sync.test.ts` → clean. ```bash git add src/lib/gsc/sync.ts src/lib/gsc/sync.test.ts git commit -m "feat(gsc): sync skips excluded slugs (no recreate after exclude)" ``` --- ## Task 5: consensus columns **Files:** Modify `src/components/keywords/columns.tsx`. - [ ] **Step 1: Import the util** Add to `src/components/keywords/columns.tsx`: ```tsx import { consensusMetrics } from "@/lib/keywords/consensus"; ``` - [ ] **Step 2: Add the three columns** Right AFTER the existing `cpc` column object (the one ending the CPC `CpcBadge` cell), add these three column defs to the `columns` array: ```tsx { id: "volumeConsensus", accessorFn: (k) => consensusMetrics(k.metrics).volume ?? 0, header: "Vol. ✦", enableGlobalFilter: false, filterFn: numberRange, cell: ({ row }) => naCell(consensusMetrics(row.original.metrics).volume, (n) => n.toLocaleString("fr-FR"), ), }, { id: "kdConsensus", accessorFn: (k) => consensusMetrics(k.metrics).kd ?? 0, header: "KD ✦", enableGlobalFilter: false, filterFn: numberRange, cell: ({ row }) => { const kd = consensusMetrics(row.original.metrics).kd; return kd == null ? naCell(null) : ; }, }, { id: "cpcConsensus", accessorFn: (k) => consensusMetrics(k.metrics).cpc ?? 0, header: "CPC ✦", enableGlobalFilter: false, filterFn: numberRange, cell: ({ row, table }) => { const cpc = consensusMetrics(row.original.metrics).cpc; if (cpc == null) return naCell(null); const [min, max] = table.options.meta?.cpcRange ?? [0, 1]; return ; }, }, ``` - [ ] **Step 3: Register them in the column menu + hide by default** In `TOGGLEABLE_COLUMNS`, after the `{ id: "cpc", label: "CPC" }` entry, add: ```tsx { id: "volumeConsensus", label: "Vol. ✦" }, { id: "kdConsensus", label: "KD ✦" }, { id: "cpcConsensus", label: "CPC ✦" }, ``` In `DEFAULT_COLUMN_VISIBILITY`, add (so they're opt-in): ```tsx volumeConsensus: false, kdConsensus: false, cpcConsensus: false, ``` - [ ] **Step 4: tsc + biome + commit** `npx tsc --noEmit` → clean. `bunx biome check --write src/components/keywords/columns.tsx` → clean. ```bash git add src/components/keywords/columns.tsx git commit -m "feat(keywords): cross-source consensus columns (Vol/KD/CPC ✦, opt-in)" ``` --- ## Task 6: "Exclure" UI **Files:** Modify `src/components/keywords/research/targeting-bar.tsx`, `src/components/keywords/research/research-container.tsx`. - [ ] **Step 1: Add an `onExclude` button to `TargetingBar`** In `src/components/keywords/research/targeting-bar.tsx`, add `onExclude: () => void;` to the props type, destructure it, and add this button right before the final "Annuler" ` ``` - [ ] **Step 2: Wire the exclude handler + confirmation in the container** In `src/components/keywords/research/research-container.tsx`: Add imports: ```tsx import { AlertDialog, AlertDialogAction, AlertDialogCancel, AlertDialogContent, AlertDialogDescription, AlertDialogFooter, AlertDialogHeader, AlertDialogTitle, } from "@/components/ui/alert-dialog"; import { excludeKeywordsAction } from "@/lib/keywords/actions"; ``` (Read `src/components/ui/alert-dialog.tsx` first and use its ACTUAL exported names; the shadcn set above is expected.) Add state with the other hooks: ```tsx const [excludeOpen, setExcludeOpen] = useState(false); ``` Add the handler near `targetSelected` (it reads `rowSelection` the same way): ```tsx async function excludeSelected() { const ids = Object.entries(rowSelection) .filter(([, v]) => v) .map(([id]) => id); setExcludeOpen(false); try { const { excluded } = await excludeKeywordsAction(activeSiteId, ids); toast.success(`${excluded} mot(s)-clé(s) exclu(s)`); setRowSelection({}); await load(); } catch { toast.error("Échec de l'exclusion"); } } ``` Pass `onExclude={() => setExcludeOpen(true)}` to the `` usage. Render the confirmation near ``: ```tsx Exclure {selectedCount} mot(s)-clé(s) ? Ils sont supprimés et ne reviendront pas au prochain sync GSC. Annuler Exclure ``` - [ ] **Step 3: tsc + biome + commit** `npx tsc --noEmit` → clean. `bunx biome check --write src/components/keywords/research/targeting-bar.tsx src/components/keywords/research/research-container.tsx` → clean. ```bash git add src/components/keywords/research/targeting-bar.tsx src/components/keywords/research/research-container.tsx git commit -m "feat(keywords): exclude selected GSC keywords (with confirmation)" ``` --- ## Task 7: Live verification + finish - [ ] **Step 1: Full sweep** — `npx tsc --noEmit` clean; `bun test src/lib/keywords/consensus.test.ts` 5 pass; `bun run test src/lib/keywords/actions.test.ts src/lib/gsc/sync.test.ts` green; `bunx biome check` clean on all touched files. - [ ] **Step 2: Live (d3k, dev server :3000, logged in on Acieb)** - `/keywords/recherche` → Colonnes menu → enable **Vol. ✦ / KD ✦ / CPC ✦**; confirm they show N/A for GSC-only rows (and a median where a keyword has SEO metrics). - Select a couple of unwanted GSC rows → **Exclure** → confirm dialog → toast "N exclu(s)", rows gone. - Click **Synchroniser GSC** → confirm the excluded terms are NOT recreated (the toast's "importés" count excludes them). - [ ] **Step 3: Finish** — Use superpowers:finishing-a-development-branch (ff-merge `feature/keywords-exclusion-consensus` → `dev`, push, delete branch). --- ## Notes / risks - **Migrations:** `db:generate` + `db:migrate` (and `db:migrate:test`). NEVER `db:push`. - **Slug consistency:** exclusion stores `wasm.slugify(term)`; the sync skips on `wasm.slugify(query)` — same function, so a re-synced query matches its exclusion. - **Scoping:** keyword actions scope by `siteId` (existing repo pattern; `activeSiteId` is already org-correct). - **Consensus columns hidden by default** to avoid an N/A-heavy table; shared `columns.tsx` so Réservoir gets them too (also hidden). - **Out of scope:** un-exclude UI; export filter/select; direct import into Recherche (all in roadmap backlog). ```