# /content/stats 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 `/content/stats` page showing per-page GSC performance over time (7/28/90 days) with period-over-period deltas, sparklines, a drop-detection signal (F4), and a per-page detail sheet — fed by the `gsc_page_snapshots` table the Rust worker fills daily.

**Architecture:** Server-side SQL aggregation (Drizzle over `gsc_page_snapshots`, two FILTERed periods in one pass) + pure TDD'd logic (weighted position, URL normalization, drop detection, series densification) + an RSC page (period via `?p=` searchParam, nuqs `shallow:false` refetch) seeding a client container (no fetch on mount).

**Tech Stack:** Next 16 RSC, Drizzle (sql FILTER aggregates), nuqs, shadcn DataTable/Tabs/Sheet/Badge, the existing `@/components/keywords/sparkline`.

**Context the implementer must know:**
- DB tests: harness pattern in `src/lib/content/content.test.ts` (mock next/cache, assertTestDb, SITE/ORG/USR seed, cleanup). Run via `bun run test <path>`. Never `db:push` (no migration needed here — tables exist, 0017).
- NEVER fetch RSC-provided data on mount in a client container (route-deadlock gotcha). Period switch goes through the URL (`shallow: false`) → RSC refetch → new props.
- `gsc_page_snapshots`: `siteId, date (date), page (text), clicks int, impressions int, ctr real, position real` — PK (siteId,date,page). `content_items` has `url: text | null`, `siteId`, `title`.
- The page pattern to mirror: `src/app/(app)/content/online/page.tsx` (DashboardHeader + Breadcrumb + RSC fetch + container). Sidebar: `src/components/app-sidebar.tsx` CONTENU items at ~line 81.
- Sparkline: `src/components/keywords/sparkline.tsx` — READ it for the component's exact props before use.

---

### Task 1: Logique pure (TDD) — `stats-logic.ts`

**Files:**
- Create: `src/lib/content/stats-logic.ts`
- Test: `src/lib/content/stats-logic.test.ts`

- [ ] **Step 1: Failing tests**

```ts
import { describe, expect, test } from "bun:test";
import {
  densifySeries,
  detectDrop,
  normalizeUrl,
  weightedPosition,
} from "./stats-logic";

describe("weightedPosition", () => {
  test("weights by impressions", () => {
    expect(
      weightedPosition([
        { position: 10, impressions: 90 },
        { position: 100, impressions: 10 },
      ]),
    ).toBe(19);
  });
  test("null when no impressions", () => {
    expect(weightedPosition([{ position: 5, impressions: 0 }])).toBeNull();
    expect(weightedPosition([])).toBeNull();
  });
});

describe("normalizeUrl", () => {
  test("lowercases scheme+host, strips trailing slash, keeps path case", () => {
    expect(normalizeUrl("HTTPS://Example.COM/Path/")).toBe("https://example.com/Path");
    expect(normalizeUrl("https://example.com/")).toBe("https://example.com");
    expect(normalizeUrl("https://example.com/a?b=1")).toBe("https://example.com/a?b=1");
  });
  test("returns input trimmed when not a URL", () => {
    expect(normalizeUrl("  not a url ")).toBe("not a url");
  });
});

describe("detectDrop", () => {
  const ok = { clicks: 100, impressions: 1000, position: 5 };
  test("position degrades by >= 3", () => {
    expect(detectDrop({ ...ok, position: 9 }, { ...ok, position: 5 })).toBe(true);
    expect(detectDrop({ ...ok, position: 7 }, { ...ok, position: 5 })).toBe(false);
  });
  test("clicks fall >= 50% with prev floor of 5", () => {
    expect(detectDrop({ ...ok, clicks: 40 }, { ...ok, clicks: 100 })).toBe(true);
    expect(detectDrop({ ...ok, clicks: 60 }, { ...ok, clicks: 100 })).toBe(false);
    expect(detectDrop({ ...ok, clicks: 1 }, { ...ok, clicks: 4 })).toBe(false); // floor
  });
  test("never fires without impressions on both sides", () => {
    expect(detectDrop({ ...ok, impressions: 0 }, ok)).toBe(false);
    expect(detectDrop(ok, { ...ok, impressions: 0 })).toBe(false);
    expect(detectDrop(ok, { clicks: 0, impressions: 0, position: null })).toBe(false);
  });
});

describe("densifySeries", () => {
  test("fills missing days with 0, chronological", () => {
    expect(
      densifySeries(
        [
          { date: "2026-06-07", clicks: 3 },
          { date: "2026-06-05", clicks: 1 },
        ],
        "2026-06-05",
        "2026-06-08",
      ),
    ).toEqual([1, 0, 3, 0]);
  });
});
```

- [ ] **Step 2: Run → FAIL** (`bun run test src/lib/content/stats-logic.test.ts`, module not found).
- [ ] **Step 3: Implement**

```ts
// src/lib/content/stats-logic.ts — pure helpers for /content/stats (no DB, no IO).

/** Impression-weighted average position; null when there are no impressions. */
export function weightedPosition(
  rows: { position: number | null; impressions: number }[],
): number | null {
  let weight = 0;
  let sum = 0;
  for (const r of rows) {
    if (r.position == null || r.impressions <= 0) continue;
    sum += r.position * r.impressions;
    weight += r.impressions;
  }
  return weight > 0 ? Math.round((sum / weight) * 10) / 10 : null;
}

/** Lowercase scheme+host, strip trailing slash; path/query case preserved. */
export function normalizeUrl(raw: string): string {
  const trimmed = raw.trim();
  try {
    const u = new URL(trimmed);
    const path = u.pathname.replace(/\/+$/, "");
    return `${u.protocol}//${u.host}${path}${u.search}`;
  } catch {
    return trimmed;
  }
}

const POSITION_DROP = 3;
const CLICKS_DROP_RATIO = 0.5;
const CLICKS_FLOOR = 5;

type PeriodMetrics = {
  clicks: number;
  impressions: number;
  position: number | null;
};

/** F4 décrochage: needs impressions on BOTH periods; then position +3 or clicks -50% (floor 5). */
export function detectDrop(cur: PeriodMetrics, prev: PeriodMetrics): boolean {
  if (cur.impressions <= 0 || prev.impressions <= 0) return false;
  if (
    cur.position != null &&
    prev.position != null &&
    cur.position - prev.position >= POSITION_DROP
  ) {
    return true;
  }
  return (
    prev.clicks >= CLICKS_FLOOR &&
    cur.clicks <= prev.clicks * (1 - CLICKS_DROP_RATIO)
  );
}

/** Daily clicks aligned on [start, end] inclusive; missing days = 0. */
export function densifySeries(
  points: { date: string; clicks: number }[],
  start: string,
  end: string,
): number[] {
  const byDate = new Map(points.map((p) => [p.date, p.clicks]));
  const out: number[] = [];
  const d = new Date(`${start}T00:00:00Z`);
  const stop = new Date(`${end}T00:00:00Z`);
  while (d <= stop) {
    out.push(byDate.get(d.toISOString().slice(0, 10)) ?? 0);
    d.setUTCDate(d.getUTCDate() + 1);
  }
  return out;
}
```
NOTE: the clicks-drop test `40 vs 100` → `40 <= 100*0.5` true; `60 vs 100` → false; position test `9-5=4 >= 3` true, `7-5=2` false. Check each test passes EXACTLY; the tests are the spec.

- [ ] **Step 4: Run → PASS** + `npx tsc --noEmit` clean + biome clean.
- [ ] **Step 5: Commit**
```bash
git add src/lib/content/stats-logic.ts src/lib/content/stats-logic.test.ts
git commit -m "feat(content): stats pure logic (weighted position, drop detection, series)"
```

---

### Task 2: Requêtes — `getPageStats` (DB, TDD)

**Files:**
- Create: `src/lib/content/stats.ts`
- Test: `src/lib/content/stats.test.ts`

- [ ] **Step 1: Failing DB test** (harness from `content.test.ts`: mock next/cache, assertTestDb, seed SITE/ORG/USR; cleanup `gscPageSnapshots` + `contentItems` for SITE):

```ts
// inside describe.skipIf(!RUN_DB), with the usual seeds; helper:
async function snap(page: string, date: string, clicks: number, impressions: number, position: number) {
  await db.insert(gscPageSnapshots).values({ siteId: SITE, date, page, clicks, impressions, ctr: impressions ? clicks / impressions : 0, position });
}

test("aggregates current vs previous period with weighted position", async () => {
  // current period (within last 7 days) — use dates relative to today:
  const d = (off: number) => new Date(Date.now() - off * 86400000).toISOString().slice(0, 10);
  await snap("https://x.com/a", d(2), 10, 100, 5);
  await snap("https://x.com/a", d(3), 20, 300, 10);
  // previous period (8-14 days ago)
  await snap("https://x.com/a", d(9), 40, 200, 4);
  const { getPageStats } = await import("./stats");
  const { rows, kpis } = await getPageStats(SITE, 7);
  const a = rows.find((r) => r.page === "https://x.com/a")!;
  expect(a.clicks).toBe(30);
  expect(a.impressions).toBe(400);
  expect(a.position).toBe(8.8); // (5*100+10*300)/400 = 8.75 → 8.8
  expect(a.prevClicks).toBe(40);
  expect(a.prevPosition).toBe(4);
  expect(a.spark.length).toBe(7);
  expect(a.spark.reduce((s, v) => s + v, 0)).toBe(30);
  expect(kpis.clicks).toBe(30);
  expect(kpis.prevClicks).toBe(40);
});

test("prev null when no previous data; content item matched by normalized url", async () => {
  const d = (off: number) => new Date(Date.now() - off * 86400000).toISOString().slice(0, 10);
  await snap("https://x.com/b/", d(2), 5, 50, 7);
  await db.insert(contentItems).values({ id: "ci-b", siteId: SITE, title: "Article B",
    status: "published", lang: "fr", country: "FR", url: "https://X.com/b" });
  const { getPageStats } = await import("./stats");
  const { rows } = await getPageStats(SITE, 7);
  const b = rows.find((r) => r.page === "https://x.com/b/")!;
  expect(b.prevClicks).toBeNull();
  expect(b.contentItemId).toBe("ci-b");
  expect(b.contentTitle).toBe("Article B");
  expect(b.dropping).toBe(false); // prev empty → guarded
});
```

- [ ] **Step 2: Run → FAIL.**
- [ ] **Step 3: Implement** `src/lib/content/stats.ts` (server-only):

```ts
// src/lib/content/stats.ts — per-page GSC performance over a period (+ previous period).
import "server-only";
import { and, eq, gte, isNotNull, sql } from "drizzle-orm";
import { db } from "@/lib/db/client";
import { contentItems, gscPageSnapshots } from "@/lib/db/domain-schema";
import {
  densifySeries,
  detectDrop,
  normalizeUrl,
  weightedPosition,
} from "./stats-logic";

export type StatsPeriod = 7 | 28 | 90;

export interface PageStatRow {
  page: string;
  clicks: number;
  impressions: number;
  ctr: number | null;
  position: number | null;
  prevClicks: number | null;
  prevImpressions: number | null;
  prevPosition: number | null;
  spark: number[];
  contentItemId: string | null;
  contentTitle: string | null;
  dropping: boolean;
}

export interface StatsKpis {
  clicks: number;
  impressions: number;
  ctr: number | null;
  position: number | null;
  prevClicks: number | null;
  prevImpressions: number | null;
  prevCtr: number | null;
  prevPosition: number | null;
}

const iso = (d: Date) => d.toISOString().slice(0, 10);

export async function getPageStats(
  siteId: string,
  periodDays: StatsPeriod,
): Promise<{ rows: PageStatRow[]; kpis: StatsKpis }> {
  const now = new Date();
  const curStart = iso(new Date(now.getTime() - periodDays * 86400000));
  const prevStart = iso(new Date(now.getTime() - 2 * periodDays * 86400000));
  const curEnd = iso(new Date(now.getTime() - 1 * 86400000)); // J-1 (le worker n'écrit pas J)

  // One pass over 2 periods with FILTERed aggregates.
  const agg = await db
    .select({
      page: gscPageSnapshots.page,
      clicks: sql<number>`coalesce(sum(${gscPageSnapshots.clicks}) filter (where ${gscPageSnapshots.date} >= ${curStart}), 0)`,
      impressions: sql<number>`coalesce(sum(${gscPageSnapshots.impressions}) filter (where ${gscPageSnapshots.date} >= ${curStart}), 0)`,
      posWeighted: sql<number | null>`sum(${gscPageSnapshots.position} * ${gscPageSnapshots.impressions}) filter (where ${gscPageSnapshots.date} >= ${curStart})`,
      prevClicks: sql<number | null>`sum(${gscPageSnapshots.clicks}) filter (where ${gscPageSnapshots.date} < ${curStart})`,
      prevImpressions: sql<number | null>`sum(${gscPageSnapshots.impressions}) filter (where ${gscPageSnapshots.date} < ${curStart})`,
      prevPosWeighted: sql<number | null>`sum(${gscPageSnapshots.position} * ${gscPageSnapshots.impressions}) filter (where ${gscPageSnapshots.date} < ${curStart})`,
    })
    .from(gscPageSnapshots)
    .where(and(eq(gscPageSnapshots.siteId, siteId), gte(gscPageSnapshots.date, prevStart)))
    .groupBy(gscPageSnapshots.page);

  // Daily clicks for sparklines (current period only).
  const daily = await db
    .select({
      page: gscPageSnapshots.page,
      date: gscPageSnapshots.date,
      clicks: gscPageSnapshots.clicks,
    })
    .from(gscPageSnapshots)
    .where(and(eq(gscPageSnapshots.siteId, siteId), gte(gscPageSnapshots.date, curStart)));
  const seriesByPage = new Map<string, { date: string; clicks: number }[]>();
  for (const r of daily) {
    (seriesByPage.get(r.page) ?? seriesByPage.set(r.page, []).get(r.page)!).push({
      date: r.date,
      clicks: r.clicks,
    });
  }

  // Content items by normalized URL.
  const items = await db
    .select({ id: contentItems.id, title: contentItems.title, url: contentItems.url })
    .from(contentItems)
    .where(and(eq(contentItems.siteId, siteId), isNotNull(contentItems.url)));
  const itemByUrl = new Map(
    items.filter((i) => i.url).map((i) => [normalizeUrl(i.url as string), i]),
  );

  const num = (v: unknown): number => Number(v ?? 0);
  const numOrNull = (v: unknown): number | null => (v == null ? null : Number(v));

  const rows: PageStatRow[] = agg
    .map((r) => {
      const clicks = num(r.clicks);
      const impressions = num(r.impressions);
      const position =
        impressions > 0 && r.posWeighted != null
          ? Math.round((Number(r.posWeighted) / impressions) * 10) / 10
          : null;
      const prevImpressions = numOrNull(r.prevImpressions);
      const prevClicks = numOrNull(r.prevClicks);
      const prevPosition =
        prevImpressions && r.prevPosWeighted != null
          ? Math.round((Number(r.prevPosWeighted) / prevImpressions) * 10) / 10
          : null;
      const item = itemByUrl.get(normalizeUrl(r.page)) ?? null;
      return {
        page: r.page,
        clicks,
        impressions,
        ctr: impressions > 0 ? Math.round((clicks / impressions) * 1000) / 10 / 100 : null,
        position,
        prevClicks,
        prevImpressions,
        prevPosition,
        spark: densifySeries(seriesByPage.get(r.page) ?? [], curStart, curEnd),
        contentItemId: item?.id ?? null,
        contentTitle: item?.title ?? null,
        dropping: detectDrop(
          { clicks, impressions, position },
          {
            clicks: prevClicks ?? 0,
            impressions: prevImpressions ?? 0,
            position: prevPosition,
          },
        ),
      };
    })
    .filter((r) => r.impressions > 0 || (r.prevImpressions ?? 0) > 0)
    .sort((a, b) => b.clicks - a.clicks);

  const kClicks = rows.reduce((s, r) => s + r.clicks, 0);
  const kImpr = rows.reduce((s, r) => s + r.impressions, 0);
  const hasPrev = rows.some((r) => r.prevImpressions != null);
  const kPrevClicks = hasPrev ? rows.reduce((s, r) => s + (r.prevClicks ?? 0), 0) : null;
  const kPrevImpr = hasPrev ? rows.reduce((s, r) => s + (r.prevImpressions ?? 0), 0) : null;
  const kpis: StatsKpis = {
    clicks: kClicks,
    impressions: kImpr,
    ctr: kImpr > 0 ? Math.round((kClicks / kImpr) * 1000) / 10 / 100 : null,
    position: weightedPosition(rows.map((r) => ({ position: r.position, impressions: r.impressions }))),
    prevClicks: kPrevClicks,
    prevImpressions: kPrevImpr,
    prevCtr: kPrevImpr && kPrevImpr > 0 && kPrevClicks != null
      ? Math.round((kPrevClicks / kPrevImpr) * 1000) / 10 / 100
      : null,
    prevPosition: hasPrev
      ? weightedPosition(rows.map((r) => ({ position: r.prevPosition, impressions: r.prevImpressions ?? 0 })))
      : null,
  };
  return { rows, kpis };
}
```
ADAPT as needed: Drizzle `date` columns compare against strings — if `gte(gscPageSnapshots.date, prevStart)` mistypes, use `sql\`${gscPageSnapshots.date} >= ${prevStart}\``. Postgres SUM returns numeric/bigint as STRING via the driver — that's why every aggregate goes through `num()`/`numOrNull()`. The position test expects 8.8 (8.75 rounds to 8.8 with the *10 rounding). CTR is stored as a 0..1 fraction (e.g. 0.075) — the `Math.round(x*1000)/10/100` keeps 1 decimal as fraction; SIMPLIFY if you prefer: `Math.round((clicks/impressions)*1000)/1000` — the tests don't assert ctr, pick one and be consistent.

- [ ] **Step 4: Run → PASS** + tsc + biome clean. Also `bun run test src/lib/content/` all green.
- [ ] **Step 5: Commit**
```bash
git add src/lib/content/stats.ts src/lib/content/stats.test.ts
git commit -m "feat(content): getPageStats — agrégats par page sur 2 périodes (GSC snapshots)"
```

---

### Task 3: Page + sidebar + UI (KPIs, table, container)

**Files:**
- Create: `src/app/(app)/content/stats/page.tsx`
- Create: `src/components/content/stats/stats-container.tsx`, `stats-kpis.tsx`, `stats-columns.tsx`
- Modify: `src/components/app-sidebar.tsx` (CONTENU: + « Stats » après Online)

- [ ] **Step 1: RSC page** (mirror `online/page.tsx`; Next 16: searchParams is a Promise):

```tsx
// src/app/(app)/content/stats/page.tsx
import { StatsContainer } from "@/components/content/stats/stats-container";
import { DashboardHeader } from "@/components/dashboard-header";
import {
  Breadcrumb, BreadcrumbItem, BreadcrumbList, BreadcrumbPage, BreadcrumbSeparator,
} from "@/components/ui/breadcrumb";
import { getActiveOrgId } from "@/lib/active-org";
import { getPageStats, type StatsPeriod } from "@/lib/content/stats";
import { getActiveSiteId } from "@/lib/sites/active-site";
import { getSitesForOrg } from "@/lib/sites/queries";

export default async function Page({
  searchParams,
}: {
  searchParams: Promise<{ p?: string }>;
}) {
  const { p } = await searchParams;
  const period: StatsPeriod = p === "7" ? 7 : p === "90" ? 90 : 28;
  const sites = await getSitesForOrg(await getActiveOrgId());
  const siteId = await getActiveSiteId(sites.map((s) => s.id));
  const data = siteId
    ? await getPageStats(siteId, period)
    : { rows: [], kpis: null };

  return (
    <>
      <DashboardHeader>
        <Breadcrumb>
          <BreadcrumbList>
            <BreadcrumbItem>Contenu</BreadcrumbItem>
            <BreadcrumbSeparator />
            <BreadcrumbItem><BreadcrumbPage>Stats</BreadcrumbPage></BreadcrumbItem>
          </BreadcrumbList>
        </Breadcrumb>
      </DashboardHeader>
      <StatsContainer
        initialRows={data.rows}
        initialKpis={data.kpis}
        period={period}
      />
    </>
  );
}
```
(Match the real DashboardHeader/Breadcrumb usage from online/page.tsx — copy its exact wrappers.)

- [ ] **Step 2: KPI cards** `stats-kpis.tsx` — 4 cards in a grid (Clics, Impressions, CTR, Position). Each: value + delta vs prev (`Δ% = (cur-prev)/prev`), arrow ↗/↘, green when improving — **position improves when it DECREASES**. Prev null → « — ». Format: clicks/impr `toLocaleString("fr-FR")`, ctr `%` 1 décimale, position 1 décimale.

- [ ] **Step 3: Columns** `stats-columns.tsx` (ColumnDef<PageStatRow>[]):
  - Page: path only (`new URL(page).pathname` fallback page), truncated, `title` attr = full URL.
  - Clics / Impressions: value + small delta% (muted, signed); sortable.
  - CTR: % 1 décimale ou « — ».
  - Position: 1 décimale + delta (SIGN INVERTED coloring); sortable; « — » si null.
  - Tendance: `<Sparkline ... />` from `@/components/keywords/sparkline` (READ its props first — adapt to its actual API; values = `row.original.spark`).
  - Signal: badge « 📉 Décrochage » si `dropping` (variant destructive), sinon rien. Add `filterFn` for the toggle.
  - Article: badge avec `contentTitle` (lien `<Link href="/content/planning">`) si matché.

- [ ] **Step 4: Container** `stats-container.tsx` (client):
  - Props `{ initialRows, initialKpis, period }`. State seeded from props; NO fetch on mount. When props change (RSC refetch after URL change), reflect via `key` on the page or `useEffect`-free derived render — simplest: render directly from props (no local copy needed since all mutations go through the URL).
  - Period switch: `useQueryState("p", parseAsStringEnum(["7","28","90"]).withDefault("28").withOptions({ shallow: false, clearOnDefault: true }))` (nuqs — `shallow:false` forces the RSC refetch). Render as `Tabs` (7 j / 28 j / 90 j).
  - Toolbar: Input recherche (globalFilter sur le path) + Button toggle « 📉 En décrochage » (columnFilters sur la colonne Signal).
  - `<StatsKpis kpis={initialKpis} />` puis `<DataTable columns data getRowId={(r)=>r.page} ... pageSize={50} />` (mirror the DataTable usage from another container, e.g. reservoir).
  - Row click → detail sheet (Task 4) : state `openRow`.

- [ ] **Step 5: Sidebar.** In `src/components/app-sidebar.tsx` CONTENU items, add `{ title: "Stats", url: "/content/stats" }` after Online.

- [ ] **Step 6: Verify** — `npx tsc --noEmit` clean; `bunx biome check` on new files; `bun run test src/lib/content/` still green.
- [ ] **Step 7: Commit**
```bash
git add "src/app/(app)/content/stats" src/components/content/stats src/components/app-sidebar.tsx
git commit -m "feat(content): page /content/stats (KPIs, table par page, périodes, décrochage)"
```

---

### Task 4: Sheet détail par page

**Files:**
- Create: `src/components/content/stats/stats-detail-sheet.tsx`
- Modify: `src/components/content/stats/stats-container.tsx` (wire row click)

- [ ] **Step 1: Component.** Props `{ row: PageStatRow | null, period: number, onOpenChange }`. Sheet latéral (pattern `shortlist-sheet.tsx` / briefing drawer) :
  - Header: path + URL complète (muted) + badge article éventuel.
  - Corps: métriques de la période (clics, impr, CTR, position + deltas) ; **courbe clics** = sparkline agrandie (SVG `sparklinePath` réutilisé avec width/height plus grands, ex. 320×80, stroke primary) avec libellés début/fin de période ; même chose pour la **position** si dispo (note: spark ne contient que les clics — v1 affiche la courbe clics seulement + position en chiffre ; la courbe position viendrait d'une série dédiée, hors v1 si elle exige une requête de plus. DÉCISION: v1 = courbe clics + métriques position texte).
  - Footer: lien « Voir dans Planning » si article matché.
- [ ] **Step 2: Wire** dans le container : `onRowClick={(r) => setOpenRow(r)}` + render du sheet.
- [ ] **Step 3: Verify** tsc + biome.
- [ ] **Step 4: Commit**
```bash
git add src/components/content/stats
git commit -m "feat(content): sheet détail par page (courbe clics, métriques, lien article)"
```

---

### Task 5: Vérification live + roadmap + Linear

- [ ] **Step 1: Gates** — `npx tsc --noEmit` ; `bun run test src/lib/content/ src/lib/keywords/` ; biome sur les fichiers stats.
- [ ] **Step 2: Live (d3k)** — dev server up : `/content/stats` sur Acieb → KPIs remplis (3 jours de données réelles), deltas « — » (période précédente vide), 0 décrochage (garde), sparklines rendues, switch 7/28/90 recharge via l'URL, recherche par path, Sheet détail s'ouvre, entrée sidebar visible. Lecture statique d3k.
- [ ] **Step 3: Roadmap** — `docs/project/roadmap.md` : section Contenu, marquer **stats ✅** (D1 tranché = GSC snapshots ; périodes + deltas + décrochage F4 v1 ; refonte/online integration restent ⬜). Mettre à jour la ligne ⛔ stats du haut de section.
- [ ] **Step 4: Linear** — fermer ELEVA-60 (commentaire livraison) ; l'issue « Persistance vue Kanban/Tableau » etc. inchangées.
- [ ] **Step 5: Commit**
```bash
git add docs/project/roadmap.md
git commit -m "docs(roadmap): /content/stats livré (ELEVA-60)"
```

---

## Notes for the executor
- Order 1 → 2 → 3 → 4 → 5. Tasks 1-2 are the data backbone; 3-4 UI; 5 ops.
- The DB test uses RELATIVE dates (`d(off)`) — never hardcode dates (the worker data shifts daily).
- No new chart lib — the keywords `sparklinePath`/`Sparkline` is the only graph primitive.
- Live data reality: only J-5→J-1 partially filled (3 days) — the page must look correct with sparse data (deltas « — », no drop signals).