# Backlink — Domain Profile (Audit) Design

- **Date**: 2026-06-08
- **Status**: approved (design)
- **Section**: backlink / profile (new page)
- **Depends on**: sites + the backlink section shell (reservoir) on `dev`. Independent of the acquisition flow (`link_sources`/`backlinks`).

## Goal

A per-site **inbound backlink profile / audit**: ingest a Semrush "Backlink Analytics" export (the links that already point at our domain) into a new `domain_backlinks` table, and surface an audit page — KPIs + a paginated, filterable table. This is the **audit axis** (existing inbound links), distinct from the **acquisition axis** (`backlinks` = sources we prospect).

## The constraint that shapes the design

The real export is **~13.6k rows / 3.9 MB** — well over the **1 MB Server Action payload cap**. So ingestion uses a **route handler** (`POST`), which reads the full request body (no action cap), parses, and bulk-inserts. This also resolves the backlog "chunked import" item for this flow.

## Source columns (verified against `~/Downloads/export-kw/aciebenergie.fr-backlinks.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
```
Boolean-ish columns (`Nofollow, Sponsored, Ugc, Sitewide, New link, Lost link`) and the placement columns (`Text/Frame/Form/Image`) — exact truthy format (e.g. `true`/`false`/empty) and date format of `First/Last seen` are **validated against the real file during implementation** before finalizing the parser (rien en aveugle).

## Data model

New table `domain_backlinks` (`src/lib/db/domain-schema.ts`), per site:
```ts
id: text PK            // hash(siteId + sourceUrl + targetUrl)
siteId: text FK → sites (cascade)
sourceUrl: text
sourceTitle: text | null
sourceDomain: text     // normalized host (no www, lowercased) — ref-domain dedup
targetUrl: text
anchor: text | null
authority: integer | null   // "Page ascore"
nofollow: boolean
sponsored: boolean
ugc: boolean
sitewide: boolean
placement: text        // "text" | "image" | "frame" | "form"
firstSeen: timestamp | null
lastSeen: timestamp | null
isNew: boolean
isLost: boolean
importedAt: timestamp
```
Index on `(siteId)` and `(siteId, authority)`. Re-import **replaces** the site's rows (delete-by-site then insert). Migration via `bun run db:generate` + `db:migrate` + `db:migrate:test` (never `db:push`).

Types in `src/lib/backlink/domain-profile-types.ts`: `DomainBacklink`, `DomainProfileStats`.

## Parser (rust, wasm)

`crates/import-core/src/domain_backlink.rs` → `parse_domain_backlinks_csv(csv) -> Vec<RawDomainBacklink>` (camelCase serde), wasm export `parse_domain_backlinks_csv`. Uses the `csv` crate (handles quoting). Per row:
- map by header name; `authority` = parse int of `Page ascore`;
- booleans = case-insensitive `"true"` / non-empty truthy;
- `placement` = first truthy of Image→`image`, Frame→`frame`, Form→`form`, else `text`;
- `sourceDomain` = normalized host of `Source url` (existing `url` crate in import-core);
- `firstSeen`/`lastSeen` = pass through raw string (TS coerces to Date; empty → null).

## Ingestion flow (route handler — NOT a server action)

`src/app/(app)/backlink/profile/import/route.ts` (`POST`):
1. Auth: resolve session; require the `siteId` (form field) belongs to the user's org (match existing action authorization pattern; if the section trusts `siteId` like content actions, follow that).
2. Read the uploaded file: `const form = await request.formData(); const file = form.get("file") as File; const text = await file.text();`.
3. `const rows = wasm.parse_domain_backlinks_csv(text)`; map to `DomainBacklink[]` with `id = hash(siteId+sourceUrl+targetUrl)`, `importedAt = now`.
4. In a transaction: `delete domain_backlinks where siteId` then bulk-insert in **batches of 500** (avoid param limits). De-dupe by `id` within the batch (keep last).
5. Return JSON `{ ok, imported, refDomains }`.

(Route handlers have no 1 MB body cap; 3.9 MB is a small HTTP body.)

## Queries

`src/lib/backlink/domain-profile-queries.ts` (`server-only`):
- `getDomainProfileStats(siteId) → DomainProfileStats` — SQL aggregate: `backlinks` (count), `refDomains` (distinct sourceDomain), `dofollowPct`, `sitewidePct`, `newCount`, `lostCount`.
- `getDomainBacklinksPage(siteId, { dofollow?, status?: "new"|"lost", page, pageSize, sortDesc })` — paginated rows + `total` (server-side pagination; 13.6k rows can't render client-side). Default sort: `authority desc nulls last`. Returns `{ rows, total }`.

Read actions in `src/lib/backlink/domain-profile-actions.ts` (`"use server"`): `getDomainProfileStatsAction`, `getDomainBacklinksPageAction`.

## UI — `/backlink/profile`

```
[Backlinks][Ref.domains][% dofollow][% sitewide][Nouveaux][Perdus]   ← KPI strip
[ Importer export Semrush ]        (filters: ☑ dofollow  ☑ new  ☑ lost)
┌ table ────────────────────────────────────────────────────────────────┐
│ Domaine src │ Ancre │ Autorité │ Dofollow │ Placement │ 1ère vue │ N/L  │
└────────── ◀ page 1/28 ▶ ──────────────────────────────────────────────┘
```
- `BacklinkProfileContainer` (client): `useActiveSite` → load stats + first page; **Importer** button uploads to the route handler (`fetch(POST, FormData)`), then reloads.
- KPIs reuse `StatCard`. Table reuses `DataTable` (server-paginated via `sorting`/page props + `getDomainBacklinksPageAction`); columns: source domain (link ↗), anchor (truncate), authority (DA-style badge), dofollow (✓/✗), placement, first seen, new/lost badge.
- Empty state: "Aucun profil importé — dépose un export Semrush Backlink Analytics."
- Nav: add **Profil** to the Backlink section (after Reservoir).

## Error / empty states

- File > say 20 MB or wrong columns → route handler returns `{ ok:false, error }`; container shows it inline.
- No active site → empty/skeleton (shared pattern).
- Partial/garbled rows → skipped by the parser, counted; surfaced as "N ignorées".

## Testing

- **Unit (rust)** `domain_backlink.rs`: header mapping, booleans, placement priority, sourceDomain normalization, empty-date → none, quoted fields with commas.
- **Unit (TS)** parser wrapper: maps raw → `DomainBacklink`, stable `id` hash, date coercion.
- **Query test** (DB): seed N rows → `getDomainProfileStats` (counts, distinct domains, pct) + `getDomainBacklinksPage` (filter dofollow/new/lost, pagination, total).
- **Manual (d3k)**: import the real `aciebenergie.fr-backlinks.csv` (13.6k) → KPIs populate, table paginates, filters work, re-import replaces; `d3k errors` clean.

## Non-goals (YAGNI / later slices)

- Toxicity score (absent from the Semrush export — not invented).
- Anchor / source-domain distribution charts (slice 2).
- Time-series new/lost trends (needs dated snapshots).
- CSV export of the profile; the acquisition pages (recherche/planning/overview).