# Chantier Réservoir Keywords — Implementation Plan (audit 2026-07-07, P1-perf + §5 + P7)

> **For agentic workers:** REQUIRED SUB-SKILL: superpowers:subagent-driven-development (un sous-agent par tâche) ou superpowers:executing-plans. Steps en checkboxes (`- [ ]`). Branche : `feature/reservoir-refactor` (déjà créée depuis dev e282ec5). NO push sans demande explicite.

**Goal:** Le réservoir et la Recherche tiennent 100k+ keywords/site : dé-partitionnement de `keywords` (retour PK simple, FK simples), colonnes de tri dénormalisées **générées** (`volume_primary`/`kd_primary`/`cpc_primary`/`growth_primary`), pagination keyset + filtres/tri/facettes 100 % SQL, UI branchée serveur (nuqs conservé), overview en agrégats SQL (P7).

**Architecture:**
- **T1** migration 0057 : rebuild `keywords` en table plate (pattern inverse de 0045), PK(`id`) seule (les ids `${siteId}-${slug}` sont globalement uniques par construction), FK satellites simplifiées vers `keywords(id)`. Les colonnes `site_id` des satellites sont **conservées** (utilisées fonctionnellement par `keyword_targets` ; utiles pour le RLS futur §4.3 de l'audit) mais reçoivent une vraie FK vers `sites` + index (P11).
- **T2** migration 0058 : colonnes `GENERATED ALWAYS AS ((metrics -> primary_source ->> '<clé>')::real) STORED` — **zéro write-path à maintenir** (imports CSV app, service analytics, GSC sync, actions : tous couverts automatiquement), backfill automatique à l'ADD COLUMN.
- **T3** nouvelle couche `src/lib/keywords/list-query.ts` : `KeywordListFilter` sérialisable partagé entre page/list, facettes, exports et mutations bulk (mode « tout le filtre » sans matérialiser les ids côté client) ; curseur keyset opaque base64url.
- **T4a/T4b** réservoir puis recherche : RSC lit les searchParams (nuqs server cache) → première page + facettes en props ; le client refetch par Server Action dans une transition ; sélection bulk = ids de la page OU « les N résultats filtrés » (filtre serveur).
- **T5** `overview-query.ts` : agrégats SQL (GROUP BY / unnest / CASE-buckets) gardant la forme `KeywordOverview` inchangée → zéro changement UI.
- **T6** seed synthétique 50k + QA perf réelle + revue finale.

**Tech Stack:** Next.js 16 App Router (RSC + Server Actions, `unstable_cache`/`updateTag`), Drizzle 0.45 / Postgres, bun test (DB tests `DB_TESTS=1` sur `seo_insight_test`), nuqs 2, TanStack Table 8 (mode manuel), biome.

## Global Constraints

- Conventional Commits en français + trailers du repo :
  `Co-Authored-By: Claude Fable 5 <noreply@anthropic.com>` et `Claude-Session: https://claude.ai/code/session_01FdJ7KB3nYb1UShR7Eob8qi`.
- Gate verte = `bun run test:all` (JAMAIS `bun test` global — mock.module fuit entre fichiers) + `bunx biome check --write <fichiers>` avant chaque commit.
- Migrations : `bun run db:generate` pour le snapshot, SQL réécrit à la main quand drizzle-kit ne sait pas exprimer le rebuild (précédent : 0045). Appliquer sur **dev ET test** : `bun run db:migrate` puis `bun run db:migrate:test`. Après réécriture manuelle : re-lancer `bun run db:generate` → doit dire « No schema changes » (zéro drift).
- On ne réécrit AUCUNE migration ≤ 0056 (0043→0056 jamais déployées en prod mais versionnées : la prod les rejouera séquentiellement — 0045 partitionne, 0057 dé-partitionne, c'est voulu et linéaire).
- Migration ≠ UI : jamais dans le même commit/tâche.
- Textes UI en français.
- Le service analytics (`services/analytics`) partage le schéma via `paths: {"@/*": ["../../src/*"]}` — toute modif de `domain-schema.ts` le touche ; son check : `cd services/analytics && bun run check`.

## Décisions actées (ne pas rediscuter)

1. Dé-partitionner d'abord (audit §5 option b), PK(`id`) seule, FK simples.
2. `site_id` satellites : conservés partout + FK `sites` + index ; seuls les FK **composites** vers keywords disparaissent.
3. Colonnes dénormalisées = colonnes **générées STORED** (pas de maintenance write-path, pas de backfill).
4. Facettes sans counts par option (l'UI actuelle n'en affiche pas — on préserve l'UX au plus simple) ; les facettes serveur fournissent : langues présentes, sources présentes, min/max CPC, total filtré.
5. Sélection bulk cross-pages = « Sélectionner les N résultats filtrés » (mode filtre côté serveur), à la Gmail. « Envoyer vers Contenu » (création d'items) reste ids-only.
6. Colonnes triables serveur : `term` (défaut), `volume`, `kd`, `cpc`, `growth`, + `gscPosition`/`gscOpportunity` (recherche). Consensus/saisonnier/thème/statut/intent deviennent non-triables (JS pur ou coût/valeur nul) — changement UX assumé, documenté.
7. Filtre « Croissance % » et tri growth = `growth_primary` (growthPct **natif** de la source primaire) ; le fallback JS `trendDeltaPct` reste pour l'AFFICHAGE seulement (delta documenté).

---

### Task 1: Dé-partitionnement `keywords` (migration 0057) + PK/FK simples

**Files:**
- Modify: `src/lib/db/domain-schema.ts` (keywords, keywordTargets, contentKeywords, contentItems, keywordShortlist)
- Create: `drizzle/0057_departition_keywords.sql` (SQL manuel) + `drizzle/meta/0057_snapshot.json` (généré) + entrée `_journal.json`
- Modify: `services/analytics/src/upsert.ts` (conflict target `[keywords.id]`, restaurer le trick `xmax = 0`)
- Modify: `src/lib/gsc/discovery.ts` (conflict target `[keywordsTable.id]`, commentaire)
- Rename/Modify: `src/lib/db/partitioning.test.ts` → `src/lib/db/keywords-integrity.test.ts`
- Modify: `src/lib/keywords/queries.ts` (`getTargetsForSite` : filtre direct `keywordTargets.siteId` sans join)

**Interfaces produites:** table `keywords` plate, `PRIMARY KEY (id)`, index `keywords_site_term_idx (site_id, term)` + `keywords_stages_gin` ; FK simples : `keyword_targets.keyword_id → keywords(id) CASCADE`, `content_keywords.keyword_id → keywords(id) CASCADE`, `content_items.primary_keyword_id → keywords(id) SET NULL`, `keyword_shortlist.keyword_id → keywords(id) CASCADE` ; nouvelles FK `site_id → sites(id) CASCADE` + index `keyword_targets_site_idx`, `content_keywords_site_idx`.

- [ ] **Step 1 — Schéma.** Dans `domain-schema.ts` :
  - `keywords`: `id: text("id").primaryKey()` ; supprimer le bloc `primaryKey({ columns: [t.id, t.siteId] })` ET l'index redondant `keywords_site_idx` (préfixe de `keywords_site_term_idx`). Garder `siteId.references(sites.id, cascade)`, `keywords_site_term_idx`, `keywords_stages_gin`.
  - `keywordTargets`: supprimer le `foreignKey` composite ; `keywordId: text("keyword_id").notNull().references(() => keywords.id, { onDelete: "cascade" })`, `siteId: text("site_id").notNull().references(() => sites.id, { onDelete: "cascade" })`, ajouter `index("keyword_targets_site_idx").on(t.siteId)`. PK `(keywordId, country)` inchangée.
  - `contentKeywords`: idem (FK simple keywords cascade, FK sites cascade sur siteId, `index("content_keywords_site_idx").on(t.siteId)`).
  - `contentItems`: supprimer le `foreignKey` composite ; `primaryKeywordId: text("primary_keyword_id").references(() => keywords.id, { onDelete: "set null" })` (le footgun « column-list SET NULL » de 0046 disparaît).
  - `keywordShortlist`: supprimer le `foreignKey` composite ; `keywordId.references(() => keywords.id, { onDelete: "cascade" })`.
- [ ] **Step 2 — Snapshot.** `bun run db:generate` → garder `0057_snapshot.json` + entrée journal ; **jeter le .sql généré** (drizzle-kit ne sait pas dé-partitionner). Relever les noms de contraintes/FK exacts du snapshot pour les réutiliser dans le SQL manuel.
- [ ] **Step 3 — SQL manuel** `drizzle/0057_departition_keywords.sql` (pattern rebuild+swap inverse de 0045 ; noms de contraintes = ceux du snapshot) :

```sql
-- Dé-partitionnement de keywords (audit db-audit-2026-07-07 §5, option b).
-- La prod n'a jamais exécuté 0045 : elle rejouera 0045 (partitionne) puis 0057
-- (dé-partitionne) — linéaire et idempotent. PK(id) seule : id = `${siteId}-${slug}`,
-- globalement unique par construction. FK satellites simplifiées vers keywords(id) ;
-- les colonnes site_id satellites restent (tenancy/RLS futur) avec FK sites + index (P11).
CREATE TABLE "keywords_flat" (LIKE "keywords" INCLUDING DEFAULTS);--> statement-breakpoint
ALTER TABLE "keywords_flat" ADD PRIMARY KEY ("id");--> statement-breakpoint
INSERT INTO "keywords_flat" SELECT * FROM "keywords";--> statement-breakpoint
DROP TABLE "keywords" CASCADE;--> statement-breakpoint
ALTER TABLE "keywords_flat" RENAME TO "keywords";--> 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 "keywords" ADD CONSTRAINT "keywords_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade;--> statement-breakpoint
ALTER TABLE "keyword_targets" ADD CONSTRAINT "keyword_targets_keyword_id_keywords_id_fk" FOREIGN KEY ("keyword_id") REFERENCES "public"."keywords"("id") ON DELETE cascade;--> statement-breakpoint
ALTER TABLE "keyword_targets" ADD CONSTRAINT "keyword_targets_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade;--> statement-breakpoint
CREATE INDEX "keyword_targets_site_idx" ON "keyword_targets" USING btree ("site_id");--> statement-breakpoint
ALTER TABLE "content_keywords" ADD CONSTRAINT "content_keywords_keyword_id_keywords_id_fk" FOREIGN KEY ("keyword_id") REFERENCES "public"."keywords"("id") ON DELETE cascade;--> statement-breakpoint
ALTER TABLE "content_keywords" ADD CONSTRAINT "content_keywords_site_id_sites_id_fk" FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade;--> statement-breakpoint
CREATE INDEX "content_keywords_site_idx" ON "content_keywords" USING btree ("site_id");--> statement-breakpoint
ALTER TABLE "content_items" ADD CONSTRAINT "content_items_primary_keyword_id_keywords_id_fk" FOREIGN KEY ("primary_keyword_id") REFERENCES "public"."keywords"("id") ON DELETE set null;--> statement-breakpoint
ALTER TABLE "keyword_shortlist" ADD CONSTRAINT "keyword_shortlist_keyword_id_keywords_id_fk" FOREIGN KEY ("keyword_id") REFERENCES "public"."keywords"("id") ON DELETE cascade;
```

  Vérifier ensuite `bun run db:generate` → « No schema changes ».
- [ ] **Step 4 — Code.** `services/analytics/src/upsert.ts` : `target: [keywords.id]` ; remplacer le pré-comptage `matched` par le trick `xmax = 0` (RETURNING `(xmax = 0) as inserted` — de nouveau supporté sur table plate ; supprimer la boucle SELECT). `src/lib/gsc/discovery.ts` : `onConflictDoNothing({ target: [keywordsTable.id] })` + màj commentaire. `queries.ts getTargetsForSite` : `WHERE keywordTargets.siteId = siteId` direct (plus de join keywords).
- [ ] **Step 5 — Tests.** Renommer `partitioning.test.ts` → `keywords-integrity.test.ts` : mêmes assertions (upsert idempotent sur conflit `id`, cascade delete → keyword_targets + shortlist), + un test « id unique globalement » (insert même id, autre site → conflit). Adapter `services/analytics` tests si le compte created/matched est asserté.
- [ ] **Step 6 — Gates.** `bun run db:migrate && bun run db:migrate:test` ; replay fresh-DB (`createdb seo_insight_migcheck` + migrate + `psql -c '\d keywords'` : table plate, PK id ; `dropdb`) ; `bun run test:all` ; `cd services/analytics && bun run check` ; biome ; commit `feat(db): dé-partitionne keywords — PK simple + FK satellites simplifiées (audit §5)`.

### Task 2: Colonnes générées volume/kd/cpc/growth (migration 0058)

**Files:**
- Modify: `src/lib/db/domain-schema.ts` (table keywords)
- Create: `drizzle/0058_keywords_generated_metrics.sql` + snapshot/journal
- Create: `src/lib/db/keywords-generated.test.ts`

**Interfaces produites:** colonnes `volume_primary real`, `kd_primary real`, `cpc_primary real`, `growth_primary real` — `GENERATED ALWAYS AS ((metrics -> primary_source ->> '<clé>')::real) STORED`, NULL quand la source primaire n'a pas de métrique (GSC-only/manual) ; index `keywords_site_volume_idx (site_id, volume_primary DESC NULLS LAST)`, `keywords_site_kd_idx (site_id, kd_primary)`.

- [ ] **Step 1 — Schéma drizzle** (drizzle 0.45 supporte `.generatedAlwaysAs`, colonnes exclues de `$inferInsert` → aucun writer à toucher, service analytics inclus) :

```ts
// dans pgTable "keywords", après enrichmentExports :
volumePrimary: real("volume_primary").generatedAlwaysAs(
  (): SQL => sql`((metrics -> primary_source ->> 'volume')::real)`,
),
kdPrimary: real("kd_primary").generatedAlwaysAs(
  (): SQL => sql`((metrics -> primary_source ->> 'kd')::real)`,
),
cpcPrimary: real("cpc_primary").generatedAlwaysAs(
  (): SQL => sql`((metrics -> primary_source ->> 'cpc')::real)`,
),
growthPrimary: real("growth_primary").generatedAlwaysAs(
  (): SQL => sql`((metrics -> primary_source ->> 'growthPct')::real)`,
),
// indexes (t) => [...]
index("keywords_site_volume_idx").on(t.siteId, t.volumePrimary.desc().nullsLast()),
index("keywords_site_kd_idx").on(t.siteId, t.kdPrimary),
```

- [ ] **Step 2 — Migration.** `bun run db:generate` ; si le SQL généré est correct (ADD COLUMN ... GENERATED ... STORED + CREATE INDEX), le garder ; sinon le réécrire à la main (mêmes expressions), snapshot conservé, re-generate → zéro drift. L'ADD COLUMN STORED backfille automatiquement les lignes existantes.
- [ ] **Step 3 — Test DB** `keywords-generated.test.ts` : insert keyword `primarySource: "semrush"`, `metrics: { semrush: { volume: 1300, kd: 42, cpc: 1.5, growthPct: 10, updatedAt } }` → SELECT des 4 colonnes = valeurs ; UPDATE `metrics`/`primarySource` → recalcul ; keyword `metrics: {}` → 4× NULL.
- [ ] **Step 4 — Gates.** migrate dev+test ; `test:all` ; `cd services/analytics && bun run check` (vérifie que l'upsert compile sans mention des colonnes générées) ; biome ; commit `feat(db): colonnes générées volume/kd/cpc/growth_primary sur keywords (P1)`.

### Task 3: Couche requête serveur — keyset, filtres, facettes, sélection

**Files:**
- Create: `src/lib/keywords/list-query.ts` (+ `list-query.test.ts` DB-gated)
- Modify: `src/lib/keywords/actions.ts` (nouvelles actions + bulk actions acceptant une sélection)
- Modify: `src/lib/keywords/shortlist.ts` (retourner `{id, term, country}` via join)

**Interfaces produites (contrat unique server↔client — NE PAS dévier):**

```ts
// list-query.ts — types 100 % sérialisables (passent en props RSC et en args d'action)
export type KeywordSortField =
  | "term" | "volume" | "kd" | "cpc" | "growth" | "gscPosition" | "gscOpportunity";
export interface KeywordSort { field: KeywordSortField; desc: boolean }
export interface NumRange { min?: number; max?: number }
export interface KeywordListFilter {
  q?: string;                    // term ILIKE %q% (échapper %/_)
  status?: KeywordStatus[];      // status = ANY
  theme?: string[];              // theme = ANY (slugs)
  intent?: SearchIntent[];       // intent && ARRAY[...]  (overlap, multi-valué)
  lang?: string;                 // lang =
  source?: KeywordSource;        // sources @> ARRAY[source]
  stage?: KeywordStage;          // stages @> ARRAY[stage] (GIN) — 'research' pour Recherche
  volume?: NumRange; kd?: NumRange; cpc?: NumRange; growth?: NumRange; // colonnes *_primary
  gscBand?: "top10" | "quickwin" | "beyond" | "none";  // sur (gsc->>'position')::real
}
export interface KeywordPageParams {
  filter: KeywordListFilter; sort: KeywordSort;
  limit: number;               // clamp serveur 1..500
  cursor?: string | null;      // opaque, base64url(JSON {v: string|number|null, id: string})
}
export interface KeywordPage { rows: Keyword[]; total: number; nextCursor: string | null }
export interface KeywordFacets {
  languages: string[]; sources: KeywordSource[];
  cpcRange: [number, number] | null;   // min/max cpc_primary du site (échelle couleur CPC)
  total: number;                        // keywords du site (resp. du stage pour recherche)
}
export type KeywordSelection =
  | { mode: "ids"; ids: string[] }
  | { mode: "filter"; filter: KeywordListFilter; except?: string[] };

export async function searchKeywords(siteId: string, params: KeywordPageParams): Promise<KeywordPage>;
export async function getKeywordFacets(siteId: string, stage?: KeywordStage): Promise<KeywordFacets>;
/** WHERE drizzle réutilisable par les mutations bulk (mode filter → pas de matérialisation d'ids). */
export function selectionWhere(siteId: string, sel: KeywordSelection): SQL;
```

**Sémantique keyset (à implémenter exactement):** projection = `keywordSelect` existant (jamais `serp`). Tri : `ORDER BY <expr> <ASC|DESC> NULLS LAST, id ASC` ; expr : `term` → `keywords.term` ; volume/kd/cpc/growth → colonnes `*_primary` ; gscPosition → `(gsc->>'position')::real` ; gscOpportunity → `CASE WHEN (gsc->>'position')::real > 10 AND (gsc->>'position')::real <= 20 THEN (gsc->>'impressions')::real * (21 - (gsc->>'position')::real) ELSE 0 END`. Prédicat curseur (ASC) : `v` non-null → `(expr > v OR (expr = v AND id > id0) OR expr IS NULL)` ; `v` null → `(expr IS NULL AND id > id0)` ; DESC : remplacer `>` par `<` sur expr (id reste `>`). `total` = `count(*)` avec le même WHERE sans curseur ; `nextCursor` = null si `rows.length < limit`. Facettes = 4 requêtes en `Promise.all` (`DISTINCT lang`, `DISTINCT unnest(sources)`, `min/max(cpc_primary)`, `count(*)`).

- [ ] **Step 1 —** Implémenter `list-query.ts` (builder WHERE partagé `filterWhere(siteId, filter)`, encode/decode curseur avec validation zod du JSON décodé, clamp limit).
- [ ] **Step 2 — Actions.** Dans `actions.ts` : `fetchKeywordsPageAction(siteId, params)` (assert tenancy puis `searchKeywords`), `fetchKeywordFacetsAction(siteId, stage?)`, `fetchKeywordsByFilterAction(siteId, filter, opts?: { limit?: number })` (exports/matrice, cap serveur 20 000, tri term).
- [ ] **Step 3 — Bulk par sélection.** Étendre `setKeywordsStatusAction`, `setKeywordsThemeAction`, `deleteKeywordsAction`, `addKeywordsToStageAction`, `excludeKeywordsAction`, `addToShortlistAction`, `markKeywordsExportedAction` : le param `ids: string[]` devient `selection: string[] | KeywordSelection` (`Array.isArray` → mode ids, rétro-compatible avec tous les appels existants). Mode filter → UPDATE/DELETE direct `WHERE selectionWhere(...)` (pas d'inArray géant) ; pour `excludeKeywordsAction`/`addToShortlistAction` (besoin des lignes) → SELECT id/term par filtre, chunk(500).
- [ ] **Step 4 —** `shortlist.ts` : `getShortlistForSite` → `{ id, term, country }[]` (join keywords) ; adapter `fetchShortlistAction` + le typage du container recherche (compile en T4b, ici seulement le type exporté).
- [ ] **Step 5 — Tests DB `list-query.test.ts`** : seed ~40 keywords 2 sites (statuts/thèmes/intents/langs/sources/metrics variés, quelques gsc) ; couvrir : filtre combiné, ILIKE échappé, keyset stable sur tri volume DESC avec NULLs (pages successives sans doublon ni trou = union exacte), curseur invalide → reset page 1, tenancy (site B invisible), facettes, `selectionWhere` mode filter + except.
- [ ] **Step 6 — Gates.** `test:all`, biome, commit `feat(keywords): couche requête serveur keyset + filtres + facettes + sélection par filtre (P1)`.

### Task 4a: Réservoir branché serveur

**Files:**
- Modify: `src/app/(app)/keywords/reservoir/page.tsx`
- Modify: `src/components/keywords/reservoir.tsx`
- Modify: `src/components/ui/data-table.tsx` (mode manuel opt-in)
- Modify: `src/components/keywords/columns.tsx` (désactiver tri des colonnes non-serveur)
- Modify: `src/components/keywords/bulk-actions-bar.tsx` (léger : count + libellé « N résultats filtrés »)
- Create: `src/lib/keywords/search-params.ts` (parsers nuqs partagés server/client)

**Contrat RSC → client :** `page.tsx` parse l'URL via `createSearchParamsCache` (nuqs server) avec les parsers de `search-params.ts` (`q`, `status`, `theme`, `intent` CSV — existants ; + `lang`, `sort` `"champ.asc|desc"`), construit `filter` initial (lang par défaut = `primaryLanguage` si présent dans `facets.languages`, comme `deriveLanguageFilter` aujourd'hui), puis `Promise.all([fetchKeywordsPageAction(siteId, {filter, sort, limit: 50}), fetchKeywordFacetsAction(siteId)])` → props `initialPage: KeywordPage`, `initialFacets: KeywordFacets`, `initialFilter`, `initialSort`, `initialSiteId`.

**État client :** nuqs pour q (debounce 300 ms) / status / theme / intent / lang / sort ; ranges volume/kd/cpc/growth + `sourceView` restent en state local mais entrent dans `filter` ; `cursorStack: string[]` en state (pageIndex = length, prev = pop, next = push(nextCursor)) — le curseur ne va PAS dans l'URL ; tout changement de filtre/tri vide la stack et refetch page 1 dans `useTransition` via `fetchKeywordsPageAction`. `pageCount = ceil(total/pageSize)`. `cpcRange`/`languages`/`availableSources` viennent des facettes (plus des rows).

- [ ] **Step 1 —** `search-params.ts` : parsers nuqs + `filterFromSearchParams()` pur (partagé T4b).
- [ ] **Step 2 —** `data-table.tsx` : prop `manual?: boolean` → `manualSorting/manualFiltering/manualPagination: true`, pas de `getFilteredRowModel/getSortedRowModel/getPaginationRowModel` ; `sorting` contrôlé existant réutilisé. Chemin non-manuel byte-identique.
- [ ] **Step 3 —** `reservoir.tsx` : refetch serveur (supprimer `fetchKeywordsForSiteAction` du composant, le filtrage `sourceView` client, `deriveLanguageFilter`, `onFilteredRowsChange`) ; brancher toolbar/chips sur le `filter` (mêmes composants `MultiFacetDropdown` etc., alimentés par un adaptateur filter↔ColumnFiltersState conservant leur contrat) ; sorting → `sort` nuqs.
- [ ] **Step 4 — Sélection bulk.** Case « tout sélectionner » = page courante ; si `total > rows.length`, bandeau « Sélectionner les {total} résultats filtrés » → `selection = { mode: "filter", filter, except }` (décocher une ligne alimente `except`) ; les handlers passent `selection` aux actions étendues (T3). « Envoyer vers Contenu » désactivé en mode filter (tooltip « Sélection par pages uniquement »).
- [ ] **Step 5 —** `columns.tsx` : `enableSorting: false` sur theme/status/intent/consensus×3/seasonal/growth-fallback ; header tri actif seulement sur volume/kd/cpc/growthPct (le tri part au serveur via `onSortingChange`).
- [ ] **Step 6 — Gates.** `test:all` ; QA manuelle dev (4 keywords seed) : deep-link URL filtres, recherche, tri volume, pagination, bulk statut, suppression, sheet OK ; biome ; commit `feat(reservoir): pagination/filtres/tri serveur + sélection par filtre (P1)`.

### Task 4b: Recherche branchée serveur

**Files:**
- Modify: `src/app/(app)/keywords/recherche/page.tsx`
- Modify: `src/components/keywords/research/research-container.tsx`, `research-toolbar.tsx`, `research-columns.tsx`, `shortlist-sheet.tsx` (props `{id,term,country}[]`), `coverage-matrix.tsx` (prop cap)
- Modify: `src/components/keywords/export-enrichment-dialog.tsx` (source des lignes)

Mêmes patterns que T4a avec : `filter.stage = "research"` fixe ; `gscBand` nuqs existant → `filter.gscBand` ; tris additionnels `gscPosition` / `gscOpportunity` (le toggle « Trier par opportunité » pose `sort = {field:"gscOpportunity", desc:true}`) ; `getTargetsForSiteAction` inchangé (state targets complet du site — table dédiée, pas la table keywords).

- [ ] **Step 1 —** page.tsx : parse searchParams → initialPage (stage research) + facettes(stage) + targets + exclusions + shortlist (`Promise.all`).
- [ ] **Step 2 —** container : refetch serveur, cursor stack, sélection bulk par filtre (targeting bulk : mode filter → `setKeywordTargetsBulkAction` reçoit la sélection, résout les ids côté serveur via `selectionWhere` + chunk).
- [ ] **Step 3 — Exports & matrice.** `exportFullCsv`/export enrichissement : `fetchKeywordsByFilterAction(siteId, filter)` ; mode ids → passer les rows déjà chargées si la sélection est ids-page ; mode filter → action par filtre ; toast « Export limité aux 20 000 premiers » si `total > cap`. Vue matrice : `fetchKeywordsByFilterAction(..., { limit: 2000 })` + bandeau « affinez les filtres » si tronqué.
- [ ] **Step 4 —** shortlist : sheet consomme `{id,term,country}[]` du serveur (plus de résolution via rows).
- [ ] **Step 5 — Gates.** `test:all`, QA manuelle (bande GSC, opportunité, ciblage bulk, exclusions, shortlist, exports), biome, commit `feat(recherche): liste serveur keyset + tris GSC + exports par filtre (P1)`.

### Task 5: Overview keywords en SQL (P7)

**Files:**
- Create: `src/lib/keywords/overview-query.ts` (+ `overview-query.test.ts` DB-gated)
- Modify: `src/lib/keywords/queries.ts` (`getCachedKeywordOverview` appelle `queryKeywordOverview`)

**Interface produite:** `queryKeywordOverview(siteId: string): Promise<KeywordOverview>` — forme STRICTEMENT identique à `buildKeywordOverview` (types de `overview.ts` inchangés, UI intouchée). `buildKeywordOverview` reste (référence de parité + tests unitaires existants).

Requêtes (Promise.all, chacune `WHERE site_id = $1`) :
1. Scalaires : `count(*)`, `count(*) FILTER (WHERE gsc IS NOT NULL)`, sommes clicks/impressions `(gsc->>'clicks')::int`, `avg((gsc->>'position')::real)`, `sum(volume_primary)`, `avg(kd_primary)`, `count(*) FILTER (WHERE stages @> ARRAY['research'])`, idem planning.
2. `positionDistribution` : `GROUP BY` sur `CASE` reproduisant `positionBucket` (top3/top10/quickwin/deep/far/none, gsc NULL → none).
3. `kdDistribution` : `CASE` sur `kd_primary` (0-20/21-40/41-60/61-80/81-100, NULL exclu).
4. `byIntent` : `SELECT i, count(*) FROM keywords, unnest(intent) i GROUP BY i`.
5. `byTheme` : `GROUP BY coalesce(theme,'—') ORDER BY count DESC LIMIT 6`.
6. `topByVolume` : `ORDER BY volume_primary DESC NULLS LAST LIMIT 10`.
7. `opportunities` : filtre quickwin + score `(impressions)*(21-position)` `ORDER BY score DESC LIMIT 8`.
8. backlinks byStatus : `GROUP BY status` (remplace le SELECT de toutes les lignes).

- [ ] **Step 1 —** Implémenter `overview-query.ts` ; brancher `getCachedKeywordOverview` (tag/TTL inchangés) ; `getKeywordsForSite` n'est plus appelé par l'overview.
- [ ] **Step 2 — Test de parité DB** : seed ~25 keywords couvrant tous les buckets/edge-cases (gsc null, metrics vides, thèmes null, multi-intents) + backlinks → `expect(await queryKeywordOverview(site)).toEqual(buildKeywordOverview(kws, bl))` (trier les arrays à égalité de count si besoin — documenter tout écart d'ordre accepté).
- [ ] **Step 3 — Gates.** `test:all`, biome, commit `perf(overview): agrégats keywords en SQL (P7) — parité testée`.

### Task 6: Seed synthétique 50k, QA perf, revue finale

**Files:**
- Create: `scripts/seed-keywords-bench.ts`

- [ ] **Step 1 — Script** (garde `assertSeedableDb()` de `src/lib/db/assert-test-db.ts`, refuse hors `seo_insight_dev|test*`) : `bun --env-file=.env.local run scripts/seed-keywords-bench.ts --site <slug|id> --count 50000 [--wipe]`. Génère des keywords réalistes : termes `bench-<i>-<mots aléatoires>`, distribution lang fr/en 80/20, statuts/intents/thèmes répartis, `primarySource` ∈ {semrush, seranking, mangools, gsc, manual}, `metrics[primarySource]` (volume log-normal 0–90k, kd 1–95, cpc 0–12, growthPct -50..120, 15 % sans métriques), `trend` 12 mois pour 30 %, `gsc` pour 25 % (positions 1–80), 20 % `stages:['research']`. Insert par `chunk(1000)` (pattern upsert.ts). id = `${siteId}-bench-<i>` ; `--wipe` supprime `WHERE term LIKE 'bench-%'` du site.
- [ ] **Step 2 — QA perf live (dev, 50k)** : réservoir < ~500 ms serveur par page ; `EXPLAIN ANALYZE` de `searchKeywords` (tri volume → index `keywords_site_volume_idx` utilisé) et de l'overview ; payload RSC/action ≈ 50 lignes (vérifier network) ; pagination avant/arrière sans doublon ; « Sélectionner les 50 000 résultats » → set statut bulk < 5 s ; recherche texte ; overview < 300 ms ; mémoire navigateur stable. Consigner les chiffres dans le commit final.
- [ ] **Step 3 —** `--wipe`, `bun run test:all` final, `bunx biome check`, revue finale de branche, puis merge --ff-only vers dev (pas de main).

## Risques identifiés

- **Service analytics** : écrit `keywords` via Drizzle avec le schéma partagé (`services/analytics/src/upsert.ts`) — impacté par T1 (conflict target + xmax) et couvert automatiquement par T2 (colonnes générées hors `$inferInsert`). DuckDB ne fait que lire les CSV ; aucun SQL brut vers `keywords`. Redémarrer le service après T1/T2 en dev (`bun run dev:analytics`).
- **gsc-worker (Rust)** : n'écrit QUE `gsc_*` (`crates/gsc-worker/src/db.rs`) — non concerné, vérifié.
- **0057 en prod** : rejouera 0045→0057 séquentiellement (partitionne puis dé-partitionne une table quasi vide) — coût nul, mais le runbook de déploiement doit exécuter les migrations AVANT de démarrer la nouvelle app (l'ancien code composite échoue sur PK simple et vice-versa pour les conflict targets).
- **Générées + immutabilité** : `(jsonb -> text ->> text)::real` est immutable ; si Postgres refusait l'expression (version exotique), fallback = colonnes normales + trigger — à ne faire QUE si l'ADD COLUMN échoue.
- **Changements UX assumés** : tri retiré sur consensus/saisonnier/thème/statut/intent ; filtre growth = growthPct natif (plus de fallback trend dans le FILTRE) ; page courante non deep-linkée (curseur hors URL) ; matrice/exports plafonnés (2 000 / 20 000) avec bandeau.
- **`fetchKeywordsForSiteAction` reste utilisé** par `new-content-dialog.tsx` / `content-keywords-panel.tsx` — ne pas le supprimer (hors périmètre), mais il ne doit plus être appelé par réservoir/recherche.