# /backlink/recherche 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 `/backlink/recherche` page: the org's global source pool scored for the active site (language × DR), with footprint alerts, multi-site prospect creation (N sources × M sites in one action), and bulk push to planning (`prospect → planned`).

**Architecture:** Pure scoring (`relevance.ts`, TDD) + a `uniqueIndex` migration on `backlinks(link_source_id, site_id)` + one grouped query (`getResearchPool`) + two gated server actions (assign N×M with onConflictDoNothing, push planned) + an RSC page seeding a client container (selection bar with site pills, DataTable, detail sheet).

**Tech Stack:** Next 16 RSC, Drizzle, bun:test, shadcn (DataTable/Sheet/Badge/Select popper), existing DrBadge/KindBadge/pagination.

**Context the implementer must know:**
- DB tests: harness in `src/lib/backlink/dr-chunk.test.ts` / `queries.test.ts` (mock server-only, RUN_DB gate, assertTestDb, dynamic imports, ORG "demo-org" seeded by db:seed). Run via `bun run test <path>`. Never `db:push`.
- `linkSources`: org-scoped, has `domainNorm/platform/url/kind/da/effort/linkType/language/themes/tags/flags/note`; DR resolves via `domain_ratings` by domainNorm (see `getReservoir` in `src/lib/backlink/queries.ts` — REUSE its DR-map approach). `LinkSource` type already has `dr: number | null`.
- `backlinks`: `id` (uuid), `linkSourceId`, `siteId`, `status` (`prospect|planned|live|lost`), `anchor`, `firstSeen`, `createdAt`. Existing simple index `backlinks_source_site_idx` on (link_source_id, site_id) — this plan converts it to UNIQUE (0 duplicates verified on dev).
- UI gotchas: `SelectContent position="popper"`; no fetch-on-mount in containers (props from RSC; post-action refresh = `router.refresh()`); real pointer events needed to test radix tabs (testing note only).
- The réservoir page (`src/app/(app)/backlink/reservoir/page.tsx` + `reservoir-container.tsx`) is the structural model. Sidebar items: `src/components/app-sidebar.tsx` (BACKLINK section ~line 83: Reservoir, Profil).

---

### Task 1: Score pur (TDD) + migration unique

**Files:**
- Create: `src/lib/backlink/relevance.ts` + `relevance.test.ts`
- Modify: `src/lib/db/domain-schema.ts` (backlinks index → uniqueIndex)
- Generate: `drizzle/0018_*.sql`

- [ ] **Step 1: Failing tests** (`relevance.test.ts`):

```ts
import { describe, expect, test } from "bun:test";
import { footprintAlert, relevanceScore } from "./relevance";

describe("relevanceScore", () => {
  test("language match keeps full strength; mismatch ×0.3", () => {
    expect(relevanceScore({ language: "fr", dr: 80, da: null }, "fr")).toBe(80);
    expect(relevanceScore({ language: "en", dr: 80, da: null }, "fr")).toBe(24);
  });
  test("dr wins over da; falls back to da; 0 when neither", () => {
    expect(relevanceScore({ language: "fr", dr: 60, da: 90 }, "fr")).toBe(60);
    expect(relevanceScore({ language: "fr", dr: null, da: 45 }, "fr")).toBe(45);
    expect(relevanceScore({ language: "fr", dr: null, da: null }, "fr")).toBe(0);
  });
  test("rounded to 1 decimal", () => {
    expect(relevanceScore({ language: "en", dr: 91, da: null }, "fr")).toBe(27.3);
  });
});

describe("footprintAlert", () => {
  test("threshold at 3", () => {
    expect(footprintAlert(2)).toBe(false);
    expect(footprintAlert(3)).toBe(true);
    expect(footprintAlert(7)).toBe(true);
  });
});
```

- [ ] **Step 2: FAIL** then implement `relevance.ts`:

```ts
// src/lib/backlink/relevance.ts — pertinence d'une source pour un site (pur).
// v1 : langue × force (DR sinon DA). Le terme thématique s'ajoutera quand
// link_sources.themes et un thème de site existeront (CdC F3).

const LANG_MISMATCH_FACTOR = 0.3;
export const FOOTPRINT_THRESHOLD = 3;

export function relevanceScore(
  source: { language: string; dr: number | null; da: number | null },
  siteLanguage: string,
): number {
  const langMatch = source.language === siteLanguage ? 1 : LANG_MISMATCH_FACTOR;
  const strength = source.dr ?? source.da ?? 0;
  return Math.round(langMatch * strength * 10) / 10;
}

/** F5 — alerte quand la source référence déjà N sites de l'org. */
export function footprintAlert(siteCount: number): boolean {
  return siteCount >= FOOTPRINT_THRESHOLD;
}
```

- [ ] **Step 3: PASS** (5 tests).
- [ ] **Step 4: Unique migration.** In `domain-schema.ts`, backlinks table config: replace `index("backlinks_source_site_idx").on(t.linkSourceId, t.siteId)` with `uniqueIndex("backlinks_source_site_uq").on(t.linkSourceId, t.siteId)` (import `uniqueIndex` from drizzle-orm/pg-core if missing). `bun run db:generate` → inspect (drop old index + create unique). `bun run db:migrate && bun run db:migrate:test`.
- [ ] **Step 5: tsc + biome + commit:**
```bash
git add src/lib/backlink/relevance.ts src/lib/backlink/relevance.test.ts src/lib/db/domain-schema.ts drizzle/
git commit -m "feat(backlink): score de pertinence (langue×DR) + unicité backlinks(source,site)"
```

---

### Task 2: Pool scoré + actions (DB, TDD)

**Files:**
- Create: `src/lib/backlink/research.ts`, `src/lib/backlink/research-actions.ts`
- Test: `src/lib/backlink/research.test.ts`

- [ ] **Step 1: Failing tests** (harness from `queries.test.ts`; ORG "demo-org"; seed two SITES — check how sites are seeded there or in keywords tests: sites need id/orgId/name/ownerUserId/primaryLanguage):

```ts
test("pool scoré : footprint, targetedByCurrent, currentStatus, score", async () => {
  // seed: source S1 (language en, da 40) + domain_ratings dr 90 pour son domainNorm
  // backlinks: S1→SITE_A (prospect), S1→SITE_B (live)
  const { getResearchPool } = await import("./research");
  const rows = await getResearchPool(ORG, SITE_A);
  const s1 = rows.find((r) => r.id === "S1")!;
  expect(s1.footprint).toBe(2);
  expect(s1.alert).toBe(false);           // 2 < 3
  expect(s1.targetedByCurrent).toBe(true);
  expect(s1.currentStatus).toBe("prospect");
  expect(s1.score).toBe(27);              // en vs fr → 0.3 × 90 = 27
});

test("assignProspectsAction N×M avec dédup", async () => {
  // seed: S1, S2 (sans backlinks)
  const { assignProspectsAction } = await import("./research-actions");
  const r1 = await assignProspectsAction(["S1", "S2"], [SITE_A, SITE_B]);
  expect(r1).toEqual({ created: 4, skipped: 0 });
  const r2 = await assignProspectsAction(["S1", "S2"], [SITE_A, SITE_B]);
  expect(r2).toEqual({ created: 0, skipped: 4 });
});

test("pushProspectsToPlanningAction ne touche que les prospects", async () => {
  // seed: S1→SITE_A prospect, S2→SITE_A live
  const { pushProspectsToPlanningAction } = await import("./research-actions");
  const r = await pushProspectsToPlanningAction(SITE_A, ["S1", "S2"]);
  expect(r).toEqual({ updated: 1 });
  // S2 reste live, S1 devient planned (vérifier en DB)
});
```

- [ ] **Step 2: FAIL** then implement.

`research.ts` (server-only) :
```ts
// src/lib/backlink/research.ts — pool global scoré pour un site (CdC backlink/recherche).
import "server-only";
import { eq, inArray, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import { backlinks, domainRatings, linkSources, sites } from "@/lib/db/domain-schema";
import type { BacklinkStatus } from "@/lib/db/domain-schema";
import { footprintAlert, relevanceScore } from "./relevance";
import type { LinkSource } from "./types";

export type ResearchRow = LinkSource & {
  score: number;
  footprint: number;
  alert: boolean;
  targetedByCurrent: boolean;
  currentStatus: BacklinkStatus | null;
};

export async function getResearchPool(
  orgId: string,
  siteId: string,
): Promise<ResearchRow[]> {
  const site = (await db.select().from(sites).where(eq(sites.id, siteId)))[0];
  const siteLanguage = site?.primaryLanguage ?? "fr";

  const rows = await db
    .select()
    .from(linkSources)
    .where(eq(linkSources.orgId, orgId))
    .orderBy(linkSources.platform);
  if (rows.length === 0) return [];

  // DR partagé par domaine (même résolution que le réservoir).
  const domains = [...new Set(rows.map((r) => r.domainNorm))];
  const drRows = await db
    .select({ domainNorm: domainRatings.domainNorm, dr: domainRatings.dr })
    .from(domainRatings)
    .where(inArray(domainRatings.domainNorm, domains));
  const drByDomain = new Map(drRows.map((d) => [d.domainNorm, d.dr]));

  // Footprint + statut courant en une requête groupée.
  const ids = rows.map((r) => r.id);
  const rels = await db
    .select({
      linkSourceId: backlinks.linkSourceId,
      footprint: sql<string>`count(distinct ${backlinks.siteId})`,
      currentStatus: sql<string | null>`max(${backlinks.status}) filter (where ${backlinks.siteId} = ${siteId})`,
    })
    .from(backlinks)
    .where(inArray(backlinks.linkSourceId, ids))
    .groupBy(backlinks.linkSourceId);
  const relBySource = new Map(rels.map((r) => [r.linkSourceId, r]));

  return rows
    .map((r) => {
      const dr = drByDomain.get(r.domainNorm) ?? null;
      const rel = relBySource.get(r.id);
      const footprint = rel ? Number(rel.footprint) : 0;
      const currentStatus = (rel?.currentStatus ?? null) as BacklinkStatus | null;
      return {
        id: r.id, platform: r.platform, url: r.url, domainNorm: r.domainNorm,
        kind: r.kind, da: r.da, dr, effort: r.effort, linkType: r.linkType,
        language: r.language, themes: r.themes, tags: r.tags, flags: r.flags,
        note: r.note ?? undefined,
        score: relevanceScore({ language: r.language, dr, da: r.da }, siteLanguage),
        footprint,
        alert: footprintAlert(footprint),
        targetedByCurrent: currentStatus != null,
        currentStatus,
      };
    })
    .sort((a, b) => b.score - a.score);
}
```

`research-actions.ts` :
```ts
// src/lib/backlink/research-actions.ts — actions de prospection (multi-sites + planning).
"use server";
import { randomUUID } from "node:crypto";
import { and, eq, inArray } from "drizzle-orm";
import { revalidatePath } from "next/cache";
import { db } from "@/lib/db/client";
import { backlinks } from "@/lib/db/domain-schema";

/** F4 — crée des prospects N sources × M sites (dédup par l'unique (source,site)). */
export async function assignProspectsAction(
  sourceIds: string[],
  siteIds: string[],
): Promise<{ created: number; skipped: number }> {
  const pairs = sourceIds.flatMap((s) => siteIds.map((t) => ({ s, t })));
  if (pairs.length === 0) return { created: 0, skipped: 0 };
  const inserted = await db
    .insert(backlinks)
    .values(pairs.map(({ s, t }) => ({
      id: randomUUID(), linkSourceId: s, siteId: t, status: "prospect" as const,
    })))
    .onConflictDoNothing()
    .returning({ id: backlinks.id });
  revalidatePath("/backlink/recherche");
  revalidatePath("/backlink/reservoir");
  return { created: inserted.length, skipped: pairs.length - inserted.length };
}

/** F7 — push planning : prospects sélectionnés du site → planned (live/lost intacts). */
export async function pushProspectsToPlanningAction(
  siteId: string,
  sourceIds: string[],
): Promise<{ updated: number }> {
  if (sourceIds.length === 0) return { updated: 0 };
  const updated = await db
    .update(backlinks)
    .set({ status: "planned" })
    .where(and(
      eq(backlinks.siteId, siteId),
      inArray(backlinks.linkSourceId, sourceIds),
      eq(backlinks.status, "prospect"),
    ))
    .returning({ id: backlinks.id });
  revalidatePath("/backlink/recherche");
  return { updated: updated.length };
}
```
PITFALL: `max(status) filter` works because status is text (max = alphabetic) — when a source has ONE row for the site it returns that status exactly. Tests assert single-row cases only; fine.

- [ ] **Step 3: PASS** (3 tests) + tsc + biome + whole `src/lib/backlink/` dir green.
- [ ] **Step 4: Commit:**
```bash
git add src/lib/backlink/research.ts src/lib/backlink/research-actions.ts src/lib/backlink/research.test.ts
git commit -m "feat(backlink): getResearchPool scoré + assign multi-sites + push planning"
```

---

### Task 3: Page RSC + sidebar + table

**Files:**
- Create: `src/app/(app)/backlink/recherche/page.tsx`
- Create: `src/components/backlink/research/research-container.tsx`, `research-columns.tsx`
- Modify: `src/components/app-sidebar.tsx` (BACKLINK: + « Recherche » entre Reservoir et Profil)

READ FIRST: `src/app/(app)/backlink/reservoir/page.tsx` + `src/components/backlink/reservoir-container.tsx` (the structural model: RSC props, DataTable usage, filter bar style), `src/components/backlink/columns.tsx` (DrBadge/KindBadge usage, `resolvedDr` helper), `src/components/keywords/research/targeting-bar.tsx` (selection-bar pattern for Task 4).

- [ ] **Step 1: RSC page** — org → `getSitesForOrg` (pills besoin {id,name}) → site actif → `getResearchPool(orgId, siteId)` → `<BacklinkResearchContainer initialRows={rows} sites={sites.map(s=>({id:s.id,name:s.name}))} activeSiteId={siteId} />`. Breadcrumb Backlink → Recherche. Sites vide → empty state.
- [ ] **Step 2: Columns** (`research-columns.tsx`, ColumnDef<ResearchRow>[]):
  - Domaine: platform (font-medium) + domainNorm (muted), globalFilter accessorFn = `${platform} ${domainNorm}`.
  - DR: `<DrBadge value={r.dr ?? r.da} />`.
  - Type: KindBadge. Langue: uppercase badge outline. Effort: existing pattern from backlink columns (read it).
  - **Score**: bold tabular-nums, sortable, default sort desc (set initialSorting in container).
  - **Footprint**: number + `<Badge variant="destructive">⚠️ {n} sites</Badge>` when `alert`.
  - **Ciblé**: when `targetedByCurrent`, a small badge with `currentStatus` (e.g. secondary "prospect" / default "planned" / outline "live"); else "—" muted. Add a `filterFn` (id "targeted") for the hide-targeted toggle: `(row,_id,v) => !v || !row.original.targetedByCurrent`.
- [ ] **Step 3: Container** — client, props-only (no fetch on mount). Filter bar: Input recherche + Select kind (options = SOURCE_KINDS, "tous" default, position="popper") + Select langue (options = distinct languages of rows, défaut "toutes") + Button toggle « Masquer les déjà ciblés ». DataTable: `columns, data, getRowId=(r)=>r.id, globalFilter, columnFilters (kind/langue/targeted), sorting initial [{id:"score", desc:true}], rowSelection + onRowSelectionChange, onRowClick (no-op v1 → Task 4 sheet), pageSize 50`. Selection state for Task 4.
- [ ] **Step 4: Sidebar** — BACKLINK items: `{ title: "Recherche", url: "/backlink/recherche" }` after Reservoir.
- [ ] **Step 5:** tsc + biome + commit:
```bash
git add "src/app/(app)/backlink/recherche" src/components/backlink/research src/components/app-sidebar.tsx
git commit -m "feat(backlink): page /backlink/recherche (pool scoré, filtres, footprint)"
```

---

### Task 4: Barre multi-sites + push planning + sheet détail

**Files:**
- Create: `src/components/backlink/research/prospect-bar.tsx`, `source-detail-sheet.tsx`
- Modify: `src/components/backlink/research/research-container.tsx`

- [ ] **Step 1: ProspectBar** (pattern TargetingBar) — props `{ count, sites: {id,name}[], selectedSiteIds, onToggleSite, onAssign, onPushPlanning, onClear, busy }`. Hidden at count 0. Pills togglables par site (Button size sm, variant default si sélectionné sinon outline), puis:
```tsx
<Button size="sm" onClick={onAssign} disabled={busy || selectedSiteIds.length === 0}>
  Créer prospects ({count} × {selectedSiteIds.length})
</Button>
<Button size="sm" variant="outline" onClick={onPushPlanning} disabled={busy}>→ Planning</Button>
<Button size="sm" variant="ghost" onClick={onClear} className="ml-auto">Annuler</Button>
```
- [ ] **Step 2: Wire container** — state `selectedSiteIds` (init `[activeSiteId]`), handlers:
```tsx
async function assignSelected() {
  const sourceIds = Object.entries(rowSelection).filter(([, v]) => v).map(([id]) => id);
  if (!sourceIds.length || !selectedSiteIds.length) return;
  setBusy(true);
  try {
    const r = await assignProspectsAction(sourceIds, selectedSiteIds);
    toast.success(`${r.created} prospect(s) créé(s)${r.skipped ? ` — ${r.skipped} existant(s)` : ""}`);
    setRowSelection({});
    router.refresh();
  } catch { toast.error("Échec de la création des prospects"); }
  finally { setBusy(false); }
}
async function pushSelectedToPlanning() {
  const sourceIds = Object.entries(rowSelection).filter(([, v]) => v).map(([id]) => id);
  if (!sourceIds.length) return;
  setBusy(true);
  try {
    const r = await pushProspectsToPlanningAction(activeSiteId, sourceIds);
    toast.success(`${r.updated} prospect(s) → Planning`);
    setRowSelection({});
    router.refresh();
  } catch { toast.error("Échec du push en planning"); }
  finally { setBusy(false); }
}
```
- [ ] **Step 3: Detail sheet** (`source-detail-sheet.tsx`) — pattern stats-detail-sheet : SheetTitle = platform, SheetDescription = url (break-all). Corps : DR badge + score décomposé (`langue {source.language} {match ? "=" : "≠"} {siteLanguage} → ×{1|0.3} · force {dr ?? da}`), kind/effort/linkType, footprint (« Référence {n} site(s) de l'org » + alerte), statut courant. SITE LANGUAGE: pass `siteLanguage` prop down from the RSC (add to container props — the page knows the site; pass `siteLanguage` from `getSitesForOrg` row's primaryLanguage). Wire `onRowClick={(r) => setOpenRow(r)}` + SheetDescription requise (pas de warning a11y).
- [ ] **Step 4:** tsc + biome + `bun run test src/lib/backlink/` green + commit:
```bash
git add src/components/backlink/research
git commit -m "feat(backlink): barre prospects multi-sites + push planning + sheet détail"
```

---

### Task 5: Live + roadmap + Linear

- [ ] **Step 1: Gates** — tsc ; `bun run test src/lib/backlink/ src/lib/content/` ; biome.
- [ ] **Step 2: Live (d3k, vrais événements pointeur)** — `/backlink/recherche` sur Acieb : tri par score (DR réels ×0.3 — tout le seed est en vs site fr), sélection 2 sources → pills sites → « Créer prospects (2×M) » → vérif DB (`backlinks` rows prospect) → re-run = skipped ; « → Planning » → statut planned en DB ; badge footprint si ≥3 (seeder 1 source vers 3 sites en SQL pour le voir, puis nettoyer) ; sheet détail ; toggle masquer ciblés ; sidebar.
- [ ] **Step 3: Roadmap** — Backlink : `recherche` ✅ (résumé + différés F6/F8/DnD), la ligne « ⬜ recherche · planning · overview · import-export » se scinde.
- [ ] **Step 4: Linear** — fermer ELEVA-62 (commentaire livraison).
- [ ] **Step 5: Commit + finish branch** (ff-merge → dev, push, delete).

---

## Notes for the executor
- Order 1 → 2 → 3 → 4 → 5.
- The migration in Task 1 converts an existing index to unique — verify the generated SQL drops `backlinks_source_site_idx` and creates the unique one; 0 duplicates confirmed on dev (safe).
- All-en seed × fr site: every Acieb score = 0.3 × (dr ?? da) — expected, assert accordingly in live verify.
- `getRowId = r.id` (source id) — selection maps to sourceIds directly.