# Backlink Domain Profile (Audit) 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:** Ingest a large (~13.6k-row) Semrush inbound-backlink export per site via a route handler, store it in a new `domain_backlinks` table, and surface an audit page (KPIs + server-paginated, filterable table).

**Architecture:** Route handler `POST /backlink/profile/import` (no 1 MB Server Action cap) reads the uploaded file, a rust `import-core` parser maps Semrush columns → rows, the handler replaces the site's rows (delete-then-batched-insert in a transaction). Read queries aggregate KPIs and page the rows server-side; a client container renders KPIs (StatCard) + DataTable page-slice + Prev/Next.

**Tech Stack:** Drizzle/Postgres, Next 16 route handler + server actions, rust `csv`+`url` → wasm, TanStack DataTable, bun:test.

---

### Task 1: `domain_backlinks` schema + types + migration

**Files:**
- Modify: `src/lib/db/domain-schema.ts`
- Create: `src/lib/backlink/domain-profile-types.ts`

- [ ] **Step 1: Add the table**

In `src/lib/db/domain-schema.ts`, after the `backlinks` table, add (it already imports `boolean`? if not, add `boolean` to the `drizzle-orm/pg-core` import):

```ts
/** Per-site INBOUND backlink profile (audit) — the links pointing AT our domain.
 *  Distinct from `backlinks` (acquisition targeting). Re-import replaces a site's rows. */
export const domainBacklinks = pgTable(
  "domain_backlinks",
  {
    id: text("id").primaryKey(), // `${siteId}:${sha1(sourceUrl|targetUrl)}`
    siteId: text("site_id")
      .notNull()
      .references(() => sites.id, { onDelete: "cascade" }),
    sourceUrl: text("source_url").notNull(),
    sourceTitle: text("source_title"),
    sourceDomain: text("source_domain").notNull(),
    targetUrl: text("target_url").notNull(),
    anchor: text("anchor"),
    authority: integer("authority"),
    nofollow: boolean("nofollow").notNull().default(false),
    sponsored: boolean("sponsored").notNull().default(false),
    ugc: boolean("ugc").notNull().default(false),
    sitewide: boolean("sitewide").notNull().default(false),
    placement: text("placement").notNull().default("text"),
    firstSeen: timestamp("first_seen", { withTimezone: true }),
    lastSeen: timestamp("last_seen", { withTimezone: true }),
    isNew: boolean("is_new").notNull().default(false),
    isLost: boolean("is_lost").notNull().default(false),
    importedAt: timestamp("imported_at", { withTimezone: true })
      .defaultNow()
      .notNull(),
  },
  (t) => [
    index("domain_backlinks_site_idx").on(t.siteId),
    index("domain_backlinks_site_authority_idx").on(t.siteId, t.authority),
  ],
);
```

Ensure `boolean` is in the pg-core import line at the top (`import { boolean, index, integer, jsonb, pgTable, text, timestamp } from "drizzle-orm/pg-core";`).

- [ ] **Step 2: Add the read/UI types**

Create `src/lib/backlink/domain-profile-types.ts`:

```ts
// src/lib/backlink/domain-profile-types.ts — read/UI shapes for the inbound backlink profile.
export interface DomainBacklink {
  id: string;
  siteId: string;
  sourceUrl: string;
  sourceTitle: string | null;
  sourceDomain: string;
  targetUrl: string;
  anchor: string | null;
  authority: number | null;
  nofollow: boolean;
  sponsored: boolean;
  ugc: boolean;
  sitewide: boolean;
  placement: string; // "text" | "image" | "frame" | "form"
  firstSeen: string | null; // ISO
  lastSeen: string | null; // ISO
  isNew: boolean;
  isLost: boolean;
}

export interface DomainProfileStats {
  backlinks: number;
  refDomains: number;
  dofollowPct: number; // 0..100
  sitewidePct: number; // 0..100
  newCount: number;
  lostCount: number;
}

export interface DomainBacklinksPage {
  rows: DomainBacklink[];
  total: number;
}
```

- [ ] **Step 3: Generate + apply migration**

Run:
```bash
bun --env-file=.env.local run db:generate
bun run db:migrate
NODE_ENV=test BETTER_AUTH_SECRET=dummymigrationsecret_not_used_at_all_32+ bun run src/lib/db/migrate.ts
```
Expected: a new `drizzle/00NN_*.sql` creating `domain_backlinks`; both dev + test DBs apply it. Verify:
```bash
psql seo_insight_dev -tAc "select count(*) from domain_backlinks;"
psql seo_insight_test -tAc "select count(*) from domain_backlinks;"
```
Expected: `0` on both (table exists).

- [ ] **Step 4: Typecheck + commit**

Run: `bunx tsc --noEmit` → PASS.
```bash
git add src/lib/db/domain-schema.ts src/lib/backlink/domain-profile-types.ts drizzle
git commit -m "feat(backlink): domain_backlinks table (inbound profile) + types"
```

---

### Task 2: Rust Semrush parser + wasm export

**Files:**
- Create: `crates/import-core/src/domain_backlink.rs`
- Modify: `crates/import-core/src/lib.rs`

- [ ] **Step 1: Create the parser with tests**

Create `crates/import-core/src/domain_backlink.rs`:

```rust
// crates/import-core/src/domain_backlink.rs — parse a Semrush "Backlink Analytics" export.
use crate::normalize::domain_norm;
use serde::Serialize;

#[derive(Debug, Clone, PartialEq, Serialize)]
#[serde(rename_all = "camelCase")]
pub struct RawDomainBacklink {
    pub source_url: String,
    pub source_title: Option<String>,
    pub source_domain: String,
    pub target_url: String,
    pub anchor: Option<String>,
    pub authority: Option<i64>,
    pub nofollow: bool,
    pub sponsored: bool,
    pub ugc: bool,
    pub sitewide: bool,
    pub placement: String,
    pub first_seen: String,
    pub last_seen: String,
    pub is_new: bool,
    pub is_lost: bool,
}

/// Semrush boolean cells: "true"/"false"/empty (and "0"). Empty/false/0 -> false.
fn truthy(s: &str) -> bool {
    let t = s.trim();
    !t.is_empty() && !t.eq_ignore_ascii_case("false") && t != "0"
}

fn opt(s: String) -> Option<String> {
    if s.trim().is_empty() {
        None
    } else {
        Some(s)
    }
}

pub fn parse_domain_backlinks(csv: &str) -> Vec<RawDomainBacklink> {
    let mut rdr = csv::ReaderBuilder::new()
        .has_headers(true)
        .flexible(true)
        .trim(csv::Trim::All)
        .from_reader(csv.as_bytes());
    let headers = match rdr.headers() {
        Ok(h) => h.clone(),
        Err(_) => return vec![],
    };
    let idx = |name: &str| headers.iter().position(|h| h == name);
    let i_ascore = idx("Page ascore");
    let i_stitle = idx("Source title");
    let i_surl = idx("Source url");
    let i_turl = idx("Target url");
    let i_anchor = idx("Anchor");
    let i_nofollow = idx("Nofollow");
    let i_sponsored = idx("Sponsored");
    let i_ugc = idx("Ugc");
    let (i_text, i_frame, i_form, i_image) =
        (idx("Text"), idx("Frame"), idx("Form"), idx("Image"));
    let _ = i_text;
    let i_sitewide = idx("Sitewide");
    let (i_first, i_last) = (idx("First seen"), idx("Last seen"));
    let (i_new, i_lost) = (idx("New link"), idx("Lost link"));

    let mut out = Vec::new();
    for rec in rdr.records() {
        let Ok(r) = rec else { continue };
        let get = |i: Option<usize>| i.and_then(|i| r.get(i)).unwrap_or("").to_string();
        let source_url = get(i_surl);
        if source_url.trim().is_empty() {
            continue;
        }
        let placement = if truthy(&get(i_image)) {
            "image"
        } else if truthy(&get(i_frame)) {
            "frame"
        } else if truthy(&get(i_form)) {
            "form"
        } else {
            "text"
        }
        .to_string();
        out.push(RawDomainBacklink {
            source_domain: domain_norm(&source_url),
            authority: get(i_ascore).trim().parse().ok(),
            anchor: opt(get(i_anchor)),
            source_title: opt(get(i_stitle)),
            target_url: get(i_turl),
            nofollow: truthy(&get(i_nofollow)),
            sponsored: truthy(&get(i_sponsored)),
            ugc: truthy(&get(i_ugc)),
            sitewide: truthy(&get(i_sitewide)),
            placement,
            first_seen: get(i_first),
            last_seen: get(i_last),
            is_new: truthy(&get(i_new)),
            is_lost: truthy(&get(i_lost)),
            source_url,
        });
    }
    out
}

#[cfg(test)]
mod tests {
    use super::*;

    const CSV: &str = "Page ascore,Source title,Source url,Target url,Anchor,External links,Internal links,Nofollow,Sponsored,Ugc,Text,Frame,Form,Image,Sitewide,First seen,Last seen,New link,Lost link\n53,Mon article,https://www.trm24.fr/x/,https://aciebenergie.fr/,Joël Lassalle,1,1,false,false,false,true,false,false,false,false,2025-01-15,2025-06-01,true,false\n40,Pic,https://blog.example.com/p,https://aciebenergie.fr/,,1,1,true,false,false,false,false,false,true,true,2024-12-01,2025-05-01,false,true\n";

    #[test]
    fn maps_columns_and_placement() {
        let r = parse_domain_backlinks(CSV);
        assert_eq!(r.len(), 2);
        assert_eq!(r[0].authority, Some(53));
        assert_eq!(r[0].source_domain, "trm24.fr");
        assert_eq!(r[0].anchor.as_deref(), Some("Joël Lassalle"));
        assert!(!r[0].nofollow);
        assert_eq!(r[0].placement, "text");
        assert!(r[0].is_new && !r[0].is_lost);
        // row 2: image placement, nofollow, sitewide, lost
        assert_eq!(r[1].placement, "image");
        assert!(r[1].nofollow && r[1].sitewide && r[1].is_lost);
        assert_eq!(r[1].anchor, None);
        assert_eq!(r[1].source_domain, "blog.example.com");
    }

    #[test]
    fn skips_rows_without_source_and_bad_header() {
        assert!(parse_domain_backlinks("nonsense\n,,,").is_empty());
    }
}
```

- [ ] **Step 2: Register module + wasm export in `crates/import-core/src/lib.rs`**

Add `mod domain_backlink;` with the other `mod` lines, and after the previous exports:

```rust
#[wasm_bindgen]
pub fn parse_domain_backlinks_csv(csv: &str) -> Result<JsValue, JsValue> {
    let rows = domain_backlink::parse_domain_backlinks(csv);
    serde_wasm_bindgen::to_value(&rows).map_err(|e| JsValue::from_str(&e.to_string()))
}
```

- [ ] **Step 3: Run rust tests**

Run: `cargo test --manifest-path crates/import-core/Cargo.toml domain_backlink`
Expected: 2 tests pass.

- [ ] **Step 4: Validate against the REAL export, then rebuild wasm**

```bash
bun run build:wasm
bun install
grep parse_domain_backlinks_csv src/lib/backlink/import-core/import_core.d.ts
```
Then sanity-check the parser on the real 13.6k file via a one-off:
```bash
bun -e 'const w=await import("import-core"); const fs=require("fs"); const rows=w.parse_domain_backlinks_csv(fs.readFileSync("/Users/humperdoo/Downloads/export-kw/aciebenergie.fr-backlinks.csv","utf8")); console.log("rows",rows.length,"sample",JSON.stringify(rows[0]))'
```
Expected: ~13.6k rows; sample has sourceDomain/authority/anchor populated. **If booleans or dates look wrong** (e.g. Nofollow uses a different token), adjust `truthy`/columns and re-run — this is the rien-en-aveugle check.

- [ ] **Step 5: Restart d3k** (controller — reload wasm in the dev server)

```bash
pkill -f dev3000; sleep 2
cd /Users/humperdoo/Github/seo-insight && d3k --startup-timeout 90 --browser-navigation-timeout 60 --browser-tool agent-browser --date-time local --agent-name claude-yolo --no-tui > /tmp/d3k-launch.log 2>&1 &
sleep 13; grep -m1 "ready\|Your App" /tmp/d3k-launch.log
```

- [ ] **Step 6: Commit**

```bash
git add crates/import-core/src/domain_backlink.rs crates/import-core/src/lib.rs
git commit -m "feat(backlink): rust Semrush inbound-backlink CSV parser + wasm export"
```

---

### Task 3: TS import mapper (`parseDomainBacklinks`)

**Files:**
- Create: `src/lib/backlink/domain-profile-import.ts`
- Test: `src/lib/backlink/domain-profile-import.test.ts`

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

```ts
// src/lib/backlink/domain-profile-import.test.ts
import { describe, expect, it } from "bun:test";
import { parseDomainBacklinks } from "./domain-profile-import";

const CSV = `Page ascore,Source title,Source url,Target url,Anchor,External links,Internal links,Nofollow,Sponsored,Ugc,Text,Frame,Form,Image,Sitewide,First seen,Last seen,New link,Lost link
53,Art,https://www.trm24.fr/x/,https://aciebenergie.fr/,Joël,1,1,false,false,false,true,false,false,false,false,2025-01-15,2025-06-01,true,false
53,Art,https://www.trm24.fr/x/,https://aciebenergie.fr/,Joël,1,1,false,false,false,true,false,false,false,false,2025-01-15,2025-06-01,true,false`;

describe("parseDomainBacklinks", () => {
  it("maps rows, builds a stable id, dedupes, coerces dates", () => {
    const at = new Date("2026-06-08T00:00:00.000Z");
    const rows = parseDomainBacklinks(CSV, "site-x", at);
    expect(rows).toHaveLength(1); // duplicate (same source+target) collapsed
    const r = rows[0];
    expect(r.id.startsWith("site-x:")).toBe(true);
    expect(r.siteId).toBe("site-x");
    expect(r.sourceDomain).toBe("trm24.fr");
    expect(r.authority).toBe(53);
    expect(r.nofollow).toBe(false);
    expect(r.firstSeen instanceof Date).toBe(true);
    expect(r.importedAt).toBe(at);
  });
});
```

- [ ] **Step 2: Run it to verify it fails**

Run: `bun --env-file=.env.local test src/lib/backlink/domain-profile-import.test.ts`
Expected: FAIL ("Cannot find module './domain-profile-import'").

- [ ] **Step 3: Implement**

```ts
// src/lib/backlink/domain-profile-import.ts — Semrush export → domain_backlinks insert rows.
import { createHash } from "node:crypto";
import * as wasm from "import-core";
import type { domainBacklinks } from "@/lib/db/domain-schema";

type InsertRow = typeof domainBacklinks.$inferInsert;

interface RawDomainBacklink {
  sourceUrl: string;
  sourceTitle: string | null;
  sourceDomain: string;
  targetUrl: string;
  anchor: string | null;
  authority: number | null;
  nofollow: boolean;
  sponsored: boolean;
  ugc: boolean;
  sitewide: boolean;
  placement: string;
  firstSeen: string;
  lastSeen: string;
  isNew: boolean;
  isLost: boolean;
}

function coerceDate(s: string): Date | null {
  const t = s.trim();
  if (!t) return null;
  const d = new Date(t);
  return Number.isNaN(d.getTime()) ? null : d;
}

export function parseDomainBacklinks(
  csvText: string,
  siteId: string,
  importedAt: Date,
): InsertRow[] {
  const raw = wasm.parse_domain_backlinks_csv(csvText) as RawDomainBacklink[];
  const seen = new Set<string>();
  const out: InsertRow[] = [];
  for (const r of raw) {
    const hash = createHash("sha1")
      .update(`${r.sourceUrl}|${r.targetUrl}`)
      .digest("hex")
      .slice(0, 24);
    const id = `${siteId}:${hash}`;
    if (seen.has(id)) continue;
    seen.add(id);
    out.push({
      id,
      siteId,
      sourceUrl: r.sourceUrl,
      sourceTitle: r.sourceTitle,
      sourceDomain: r.sourceDomain,
      targetUrl: r.targetUrl,
      anchor: r.anchor,
      authority: r.authority,
      nofollow: r.nofollow,
      sponsored: r.sponsored,
      ugc: r.ugc,
      sitewide: r.sitewide,
      placement: r.placement,
      firstSeen: coerceDate(r.firstSeen),
      lastSeen: coerceDate(r.lastSeen),
      isNew: r.isNew,
      isLost: r.isLost,
      importedAt,
    });
  }
  return out;
}
```

- [ ] **Step 4: Run the test (PASS)**

Run: `bun --env-file=.env.local test src/lib/backlink/domain-profile-import.test.ts`
Expected: PASS (requires Task 2 wasm rebuild).

- [ ] **Step 5: Commit**

```bash
git add src/lib/backlink/domain-profile-import.ts src/lib/backlink/domain-profile-import.test.ts
git commit -m "feat(backlink): Semrush export → insert-row mapper (stable id, dedupe, dates)"
```

---

### Task 4: Import route handler

**Files:**
- Create: `src/app/(app)/backlink/profile/import/route.ts`

- [ ] **Step 1: Write the route handler**

```ts
// src/app/(app)/backlink/profile/import/route.ts — POST a Semrush export (bypasses the 1MB Server Action cap).
import { eq } from "drizzle-orm";
import { headers } from "next/headers";
import { parseDomainBacklinks } from "@/lib/backlink/domain-profile-import";
import { auth } from "@/lib/auth";
import { db } from "@/lib/db/client";
import { domainBacklinks } from "@/lib/db/domain-schema";

export async function POST(request: Request): Promise<Response> {
  const session = await auth.api.getSession({ headers: await headers() });
  if (!session) {
    return Response.json({ ok: false, error: "Non authentifié" }, { status: 401 });
  }
  const form = await request.formData();
  const siteId = String(form.get("siteId") ?? "");
  const file = form.get("file");
  if (!siteId || !(file instanceof File)) {
    return Response.json(
      { ok: false, error: "siteId + file requis" },
      { status: 400 },
    );
  }

  let rows: ReturnType<typeof parseDomainBacklinks>;
  try {
    rows = parseDomainBacklinks(await file.text(), siteId, new Date());
  } catch {
    return Response.json({ ok: false, error: "Parse échoué" }, { status: 400 });
  }
  if (rows.length === 0) {
    return Response.json(
      { ok: false, error: "Aucun backlink reconnu (mauvais format ?)" },
      { status: 400 },
    );
  }

  await db.transaction(async (tx) => {
    await tx.delete(domainBacklinks).where(eq(domainBacklinks.siteId, siteId));
    for (let i = 0; i < rows.length; i += 500) {
      await tx.insert(domainBacklinks).values(rows.slice(i, i + 500));
    }
  });

  const refDomains = new Set(rows.map((r) => r.sourceDomain)).size;
  return Response.json({ ok: true, imported: rows.length, refDomains });
}
```

- [ ] **Step 2: Typecheck**

Run: `bunx tsc --noEmit`
Expected: PASS.

- [ ] **Step 3: Commit**

```bash
git add "src/app/(app)/backlink/profile/import/route.ts"
git commit -m "feat(backlink): profile import route handler (replace + batched insert)"
```

---

### Task 5: Queries + read actions (stats + paginated page)

**Files:**
- Create: `src/lib/backlink/domain-profile-queries.ts`
- Create: `src/lib/backlink/domain-profile-actions.ts`
- Test: `src/lib/backlink/domain-profile-queries.test.ts`

- [ ] **Step 1: Write the failing DB test**

```ts
// src/lib/backlink/domain-profile-queries.test.ts — integration, gated on DB_TESTS=1.
import { beforeEach, describe, expect, mock, test } from "bun:test";

mock.module("server-only", () => ({}));
const RUN_DB = process.env.DB_TESTS === "1";

describe.skipIf(!RUN_DB)("domain profile queries (postgres)", () => {
  let db: typeof import("@/lib/db/client").db;
  let schema: typeof import("@/lib/db/domain-schema");
  let q: typeof import("./domain-profile-queries");

  beforeEach(async () => {
    ({ db } = await import("@/lib/db/client"));
    schema = await import("@/lib/db/domain-schema");
    q = await import("./domain-profile-queries");
    const { assertTestDb } = await import("@/lib/db/assert-test-db");
    await assertTestDb();
    await db.delete(schema.domainBacklinks);
    await db.delete(schema.sites);
    await db.insert(schema.sites).values({
      id: "site-a",
      orgId: "demo-org",
      ownerUserId: "seed-owner",
      name: "A",
      primaryLanguage: "fr",
      languages: [],
      targetCountries: ["FR"],
    });
    const mk = (i: number, over: Partial<typeof schema.domainBacklinks.$inferInsert>) => ({
      id: `site-a:${i}`,
      siteId: "site-a",
      sourceUrl: `https://d${i}.com/p`,
      sourceDomain: `d${i}.com`,
      targetUrl: "https://a.fr/",
      placement: "text",
      ...over,
    });
    await db.insert(schema.domainBacklinks).values([
      mk(1, { authority: 90, nofollow: false, sitewide: true, isNew: true }),
      mk(2, { authority: 50, nofollow: true, isLost: true }),
      mk(3, { authority: 10, nofollow: false, sourceDomain: "d1.com" }), // dup domain
    ]);
  });

  test("getDomainProfileStats aggregates counts + percentages + distinct domains", async () => {
    const s = await q.getDomainProfileStats("site-a");
    expect(s.backlinks).toBe(3);
    expect(s.refDomains).toBe(2); // d1.com (x2) + d2.com
    expect(s.dofollowPct).toBe(67); // 2/3
    expect(s.newCount).toBe(1);
    expect(s.lostCount).toBe(1);
  });

  test("getDomainBacklinksPage filters + sorts by authority + totals", async () => {
    const all = await q.getDomainBacklinksPage("site-a", { page: 0, pageSize: 50 });
    expect(all.total).toBe(3);
    expect(all.rows[0].authority).toBe(90); // desc
    const dofollow = await q.getDomainBacklinksPage("site-a", { dofollow: true, page: 0, pageSize: 50 });
    expect(dofollow.total).toBe(2);
    const lost = await q.getDomainBacklinksPage("site-a", { status: "lost", page: 0, pageSize: 50 });
    expect(lost.total).toBe(1);
  });
});
```

- [ ] **Step 2: Run it to verify it fails**

Run: `bun run test src/lib/backlink/domain-profile-queries.test.ts`
Expected: FAIL ("Cannot find module './domain-profile-queries'").

- [ ] **Step 3: Implement the queries**

```ts
// src/lib/backlink/domain-profile-queries.ts
import "server-only";
import { and, count, countDistinct, desc, eq, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import { domainBacklinks } from "@/lib/db/domain-schema";
import type {
  DomainBacklink,
  DomainBacklinksPage,
  DomainProfileStats,
} from "./domain-profile-types";

export async function getDomainProfileStats(
  siteId: string,
): Promise<DomainProfileStats> {
  const [row] = await db
    .select({
      backlinks: count(),
      refDomains: countDistinct(domainBacklinks.sourceDomain),
      dofollow: sql<number>`count(*) filter (where ${domainBacklinks.nofollow} = false)`,
      sitewide: sql<number>`count(*) filter (where ${domainBacklinks.sitewide} = true)`,
      newCount: sql<number>`count(*) filter (where ${domainBacklinks.isNew} = true)`,
      lostCount: sql<number>`count(*) filter (where ${domainBacklinks.isLost} = true)`,
    })
    .from(domainBacklinks)
    .where(eq(domainBacklinks.siteId, siteId));
  const total = Number(row?.backlinks ?? 0);
  const pct = (n: number) => (total ? Math.round((n / total) * 100) : 0);
  return {
    backlinks: total,
    refDomains: Number(row?.refDomains ?? 0),
    dofollowPct: pct(Number(row?.dofollow ?? 0)),
    sitewidePct: pct(Number(row?.sitewide ?? 0)),
    newCount: Number(row?.newCount ?? 0),
    lostCount: Number(row?.lostCount ?? 0),
  };
}

export interface PageOpts {
  dofollow?: boolean;
  status?: "new" | "lost";
  page?: number;
  pageSize?: number;
}

function toDomainBacklink(
  r: typeof domainBacklinks.$inferSelect,
): DomainBacklink {
  return {
    id: r.id,
    siteId: r.siteId,
    sourceUrl: r.sourceUrl,
    sourceTitle: r.sourceTitle,
    sourceDomain: r.sourceDomain,
    targetUrl: r.targetUrl,
    anchor: r.anchor,
    authority: r.authority,
    nofollow: r.nofollow,
    sponsored: r.sponsored,
    ugc: r.ugc,
    sitewide: r.sitewide,
    placement: r.placement,
    firstSeen: r.firstSeen ? r.firstSeen.toISOString() : null,
    lastSeen: r.lastSeen ? r.lastSeen.toISOString() : null,
    isNew: r.isNew,
    isLost: r.isLost,
  };
}

export async function getDomainBacklinksPage(
  siteId: string,
  opts: PageOpts,
): Promise<DomainBacklinksPage> {
  const { dofollow, status, page = 0, pageSize = 50 } = opts;
  const conds = [eq(domainBacklinks.siteId, siteId)];
  if (dofollow === true) conds.push(eq(domainBacklinks.nofollow, false));
  if (dofollow === false) conds.push(eq(domainBacklinks.nofollow, true));
  if (status === "new") conds.push(eq(domainBacklinks.isNew, true));
  if (status === "lost") conds.push(eq(domainBacklinks.isLost, true));
  const where = and(...conds);
  const [{ total }] = await db
    .select({ total: count() })
    .from(domainBacklinks)
    .where(where);
  const rows = await db
    .select()
    .from(domainBacklinks)
    .where(where)
    .orderBy(desc(domainBacklinks.authority))
    .limit(pageSize)
    .offset(page * pageSize);
  return { rows: rows.map(toDomainBacklink), total: Number(total) };
}
```

- [ ] **Step 4: Run the test (PASS)**

Run: `bun run test src/lib/backlink/domain-profile-queries.test.ts`
Expected: PASS (2 tests).

- [ ] **Step 5: Add the read actions**

Create `src/lib/backlink/domain-profile-actions.ts`:

```ts
// src/lib/backlink/domain-profile-actions.ts
"use server";
import {
  getDomainBacklinksPage,
  getDomainProfileStats,
  type PageOpts,
} from "./domain-profile-queries";
import type {
  DomainBacklinksPage,
  DomainProfileStats,
} from "./domain-profile-types";

export async function getDomainProfileStatsAction(
  siteId: string,
): Promise<DomainProfileStats> {
  return getDomainProfileStats(siteId);
}

export async function getDomainBacklinksPageAction(
  siteId: string,
  opts: PageOpts,
): Promise<DomainBacklinksPage> {
  return getDomainBacklinksPage(siteId, opts);
}
```

- [ ] **Step 6: Commit**

```bash
git add src/lib/backlink/domain-profile-queries.ts src/lib/backlink/domain-profile-queries.test.ts src/lib/backlink/domain-profile-actions.ts
git commit -m "feat(backlink): domain profile stats + paginated queries/actions"
```

---

### Task 6: UI — page, container, columns, KPIs, nav

**Files:**
- Create: `src/components/backlink/domain-profile-columns.tsx`
- Create: `src/components/backlink/domain-profile-container.tsx`
- Create: `src/app/(app)/backlink/profile/page.tsx`
- Modify: `src/components/app-sidebar.tsx`

- [ ] **Step 1: Columns**

Create `src/components/backlink/domain-profile-columns.tsx`:

```tsx
// src/components/backlink/domain-profile-columns.tsx
"use client";

import type { ColumnDef } from "@tanstack/react-table";
import { Badge } from "@/components/ui/badge";
import type { DomainBacklink } from "@/lib/backlink/domain-profile-types";

const dash = <span className="text-muted-foreground">—</span>;

export const domainProfileColumns: ColumnDef<DomainBacklink>[] = [
  {
    id: "sourceDomain",
    header: "Domaine source",
    cell: ({ row }) => (
      <a
        href={row.original.sourceUrl}
        target="_blank"
        rel="noopener noreferrer"
        className="text-primary hover:underline"
      >
        {row.original.sourceDomain}
      </a>
    ),
  },
  {
    accessorKey: "anchor",
    header: "Ancre",
    cell: ({ row }) =>
      row.original.anchor ? (
        <span className="block max-w-[220px] truncate">
          {row.original.anchor}
        </span>
      ) : (
        dash
      ),
  },
  {
    accessorKey: "authority",
    header: "Autorité",
    cell: ({ row }) =>
      row.original.authority == null ? dash : row.original.authority,
  },
  {
    id: "dofollow",
    header: "Dofollow",
    cell: ({ row }) =>
      row.original.nofollow ? (
        <span className="text-muted-foreground">✗</span>
      ) : (
        <span className="text-green-600">✓</span>
      ),
  },
  {
    accessorKey: "placement",
    header: "Placement",
    cell: ({ row }) => row.original.placement,
  },
  {
    accessorKey: "firstSeen",
    header: "1ère vue",
    cell: ({ row }) =>
      row.original.firstSeen
        ? new Date(row.original.firstSeen).toLocaleDateString("fr-FR")
        : dash,
  },
  {
    id: "status",
    header: "État",
    cell: ({ row }) =>
      row.original.isLost ? (
        <Badge variant="destructive">Perdu</Badge>
      ) : row.original.isNew ? (
        <Badge variant="secondary">Nouveau</Badge>
      ) : (
        dash
      ),
  },
];
```

- [ ] **Step 2: Container**

Create `src/components/backlink/domain-profile-container.tsx`:

```tsx
// src/components/backlink/domain-profile-container.tsx
"use client";

import { useCallback, useEffect, useRef, useState } from "react";
import { DataTable } from "@/components/ui/data-table";
import { StatCard } from "@/components/keywords/overview/stat-card";
import { Button } from "@/components/ui/button";
import {
  getDomainBacklinksPageAction,
  getDomainProfileStatsAction,
} from "@/lib/backlink/domain-profile-actions";
import type {
  DomainBacklink,
  DomainProfileStats,
} from "@/lib/backlink/domain-profile-types";
import { useActiveSite } from "@/lib/keywords/site-context";
import { domainProfileColumns } from "./domain-profile-columns";

const PAGE_SIZE = 50;
type Filter = "all" | "dofollow" | "new" | "lost";

export function BacklinkProfileContainer() {
  const { activeSiteId } = useActiveSite();
  const inputRef = useRef<HTMLInputElement>(null);
  const [stats, setStats] = useState<DomainProfileStats | null>(null);
  const [rows, setRows] = useState<DomainBacklink[]>([]);
  const [total, setTotal] = useState(0);
  const [page, setPage] = useState(0);
  const [filter, setFilter] = useState<Filter>("all");
  const [busy, setBusy] = useState(false);
  const [note, setNote] = useState<string | null>(null);

  const opts = useCallback(() => {
    const o: { dofollow?: boolean; status?: "new" | "lost"; page: number; pageSize: number } = {
      page,
      pageSize: PAGE_SIZE,
    };
    if (filter === "dofollow") o.dofollow = true;
    if (filter === "new") o.status = "new";
    if (filter === "lost") o.status = "lost";
    return o;
  }, [page, filter]);

  const load = useCallback(async () => {
    if (!activeSiteId) return;
    const [s, p] = await Promise.all([
      getDomainProfileStatsAction(activeSiteId),
      getDomainBacklinksPageAction(activeSiteId, opts()),
    ]);
    setStats(s);
    setRows(p.rows);
    setTotal(p.total);
  }, [activeSiteId, opts]);

  useEffect(() => {
    void load().catch(() => {});
  }, [load]);

  async function onFile(e: React.ChangeEvent<HTMLInputElement>) {
    const file = e.target.files?.[0];
    e.target.value = "";
    if (!file || !activeSiteId) return;
    setBusy(true);
    setNote(null);
    try {
      const fd = new FormData();
      fd.set("siteId", activeSiteId);
      fd.set("file", file);
      const res = await fetch("/backlink/profile/import", {
        method: "POST",
        body: fd,
      });
      const json = (await res.json()) as {
        ok: boolean;
        imported?: number;
        refDomains?: number;
        error?: string;
      };
      if (!json.ok) setNote(json.error ?? "Import échoué");
      else {
        setNote(`Importé : ${json.imported} liens · ${json.refDomains} domaines`);
        setPage(0);
        await load();
      }
    } catch {
      setNote("Import impossible.");
    } finally {
      setBusy(false);
    }
  }

  const pages = Math.max(1, Math.ceil(total / PAGE_SIZE));

  return (
    <div className="space-y-4 p-4">
      <div className="flex items-center justify-between">
        <h1 className="font-semibold text-lg">Profil backlink</h1>
        <Button size="sm" disabled={busy} onClick={() => inputRef.current?.click()}>
          {busy ? "Import…" : "Importer export Semrush"}
        </Button>
        <input
          ref={inputRef}
          type="file"
          accept=".csv,text/csv"
          className="hidden"
          onChange={onFile}
        />
      </div>
      {note ? <p className="text-muted-foreground text-sm">{note}</p> : null}

      {stats ? (
        <div className="grid grid-cols-2 gap-3 md:grid-cols-6">
          <StatCard label="Backlinks" value={stats.backlinks.toLocaleString("fr-FR")} />
          <StatCard label="Ref. domains" value={stats.refDomains.toLocaleString("fr-FR")} />
          <StatCard label="% dofollow" value={`${stats.dofollowPct}%`} />
          <StatCard label="% sitewide" value={`${stats.sitewidePct}%`} />
          <StatCard label="Nouveaux" value={stats.newCount.toLocaleString("fr-FR")} />
          <StatCard label="Perdus" value={stats.lostCount.toLocaleString("fr-FR")} />
        </div>
      ) : null}

      <div className="flex gap-2">
        {(["all", "dofollow", "new", "lost"] as Filter[]).map((f) => (
          <Button
            key={f}
            size="sm"
            variant={filter === f ? "secondary" : "ghost"}
            onClick={() => {
              setPage(0);
              setFilter(f);
            }}
          >
            {f === "all" ? "Tous" : f === "dofollow" ? "Dofollow" : f === "new" ? "Nouveaux" : "Perdus"}
          </Button>
        ))}
      </div>

      <DataTable columns={domainProfileColumns} data={rows} getRowId={(r) => r.id} />

      <div className="flex items-center justify-end gap-3 text-sm">
        <span className="text-muted-foreground">
          Page {page + 1} / {pages} · {total.toLocaleString("fr-FR")} liens
        </span>
        <Button size="sm" variant="outline" disabled={page <= 0} onClick={() => setPage((p) => p - 1)}>
          ◀
        </Button>
        <Button
          size="sm"
          variant="outline"
          disabled={page + 1 >= pages}
          onClick={() => setPage((p) => p + 1)}
        >
          ▶
        </Button>
      </div>
    </div>
  );
}
```

- [ ] **Step 3: Page + nav**

Create `src/app/(app)/backlink/profile/page.tsx`:

```tsx
// src/app/(app)/backlink/profile/page.tsx
import { BacklinkProfileContainer } from "@/components/backlink/domain-profile-container";
import { DashboardHeader } from "@/components/dashboard-header";
import {
  Breadcrumb,
  BreadcrumbItem,
  BreadcrumbList,
  BreadcrumbPage,
  BreadcrumbSeparator,
} from "@/components/ui/breadcrumb";

export default function Page() {
  return (
    <>
      <DashboardHeader>
        <Breadcrumb>
          <BreadcrumbList>
            <BreadcrumbItem>Backlink</BreadcrumbItem>
            <BreadcrumbSeparator />
            <BreadcrumbItem>
              <BreadcrumbPage>Profil</BreadcrumbPage>
            </BreadcrumbItem>
          </BreadcrumbList>
        </Breadcrumb>
      </DashboardHeader>
      <BacklinkProfileContainer />
    </>
  );
}
```

In `src/components/app-sidebar.tsx`, the Backlink section's `items` is `[{ title: "Reservoir", url: "/backlink/reservoir" }]` — add `{ title: "Profil", url: "/backlink/profile" }` after it.

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

Run: `bunx tsc --noEmit && bunx biome check --write src/components/backlink/domain-profile-columns.tsx src/components/backlink/domain-profile-container.tsx "src/app/(app)/backlink/profile/page.tsx" src/components/app-sidebar.tsx`
Expected: tsc PASS; biome formats only.

- [ ] **Step 5: Commit**

```bash
git add src/components/backlink/domain-profile-columns.tsx src/components/backlink/domain-profile-container.tsx "src/app/(app)/backlink/profile/page.tsx" src/components/app-sidebar.tsx
git commit -m "feat(backlink): domain profile page (KPIs + paginated audit table + nav)"
```

---

### Task 7: Live verification (d3k) + sweep

**Files:** none.

- [ ] **Step 1: Full sweep**

```bash
bunx tsc --noEmit
cargo test --manifest-path crates/import-core/Cargo.toml domain_backlink
psql seo_insight_test -tAc "truncate table domain_backlinks, content_items, keywords, backlinks, link_sources, keyword_themes, sites cascade;" >/dev/null
bun --env-file=.env.local test src/lib/backlink/domain-profile-import.test.ts
bun run test src/lib/backlink/domain-profile-queries.test.ts
```
Expected: all green.

- [ ] **Step 2: Live import the real 13.6k export via d3k**

Navigate to `http://localhost:3000/backlink/profile` (active site = Acieb). Click **Importer export Semrush** and choose `/Users/humperdoo/Downloads/export-kw/aciebenergie.fr-backlinks.csv` (use `d3k agent-browser upload "input[type=file]" <path>`).

Verify (eval + screenshot): note shows "Importé : ~13.6k liens · N domaines"; KPIs populate (Backlinks ~13.6k, Ref.domains, % dofollow…); table shows top-authority rows; filters (Dofollow/Nouveaux/Perdus) change the total; pagination ◀▶ works; re-import keeps the count stable (replace). `d3k errors` clean (no 413, no 500).

- [ ] **Step 3: Clean up** the imported rows if not wanted (`psql … delete from domain_backlinks where site_id='<acieb>'`), or leave for inspection.

---

## Done criteria

- `domain_backlinks` migrated (dev + test); rust parser + wasm shipped (cargo green); validated on the real 13.6k export.
- Route handler imports large files (no 1 MB cap), replace semantics, batched insert in a transaction; session-guarded.
- Stats + paginated/filterable queries + actions, tested; page renders KPIs + audit table + Prev/Next; nav has Profil.
- tsc clean, all touched tests pass, d3k live import verified, `d3k errors` clean.
- Branch `feature/backlink-domain-profile` merged into `dev` (ff-only), deleted, pushed.
```