# Phase 0 — Scalabilité analytique (instrumentation + partitionnement GSC + cache)

> **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:** Exécuter la Phase 0 du premortem DuckDB (`docs/superpowers/specs/2026-06-22-duckdb-read-path-premortem.md` §6) : chiffrer la contention réelle (instrumentation), borner la croissance des snapshots GSC (partitionnement mensuel + BRIN), et cesser de re-scanner l'historique à chaque visite (cache). La mesure accumulée décidera de la Phase 1 (DuckDB read-path).

**Architecture:** Les tables `gsc_query_snapshots` / `gsc_page_snapshots` (PK `(site_id,date,dim)`, écrites uniquement par le worker Rust en upsert `ON CONFLICT`) passent en `PARTITION BY RANGE (date)` mensuel avec index BRIN sur le parent — migration MANUSCRITE (drizzle-kit ne sait pas émettre `PARTITION BY`), pattern copy+swap du précédent keywords 0045, snapshot meta gardé en phase (gate = `db:generate` → diff vide). Dev/test sont vides mais **la prod ne l'est pas** (cron Dokploy quotidien) → la migration copie les données. Le lecteur unique `getPageStats` (3 requêtes, group-by FILTER) gagne un timing structuré + un `unstable_cache` keyé `(siteId, period)`.

**Décisions de cadrage (actées par le contrôleur) :**
- **Rollup/matview : DIFFÉRÉ.** Zéro donnée mesurée aujourd'hui ; construire un rollup maintenant contredirait le « measure-first ». Le déclencheur sera les timings accumulés.
- **Partitions pré-créées 2026-01 → 2028-12 + partition DEFAULT** en filet de sécurité (le worker Rust n'a pas besoin de changer : ses upserts tombent toujours dans une partition existante). L'extension annuelle des partitions est documentée comme tâche de maintenance ; PAS de modification du crate Rust dans ce plan.
- **Invalidation du cache : time-based uniquement** (revalidate 1 h). Le writer est le worker Rust en SQL direct — il ne peut pas `revalidateTag`. Données rafraîchies 1×/jour → 1 h de staleness est indolore. Le tag `gsc-stats-<siteId>` est posé quand même pour une invalidation future côté app.

**Tech Stack:** Next.js 16.2.7, Drizzle + postgres.js, bun, migration SQL manuscrite, `unstable_cache` (next/cache).

## Global Constraints

- Branche : `feature/phase0-analytics` (créée depuis dev à jour). JAMAIS de commit sur main.
- Conventional Commits. Gate par tâche : `bunx tsc --noEmit` 0 erreur, `bun run lint` 0 erreur, tests de la tâche verts.
- **`db:push` INTERDIT** pour le partitionnement — `bun run db:generate` + `bun run db:migrate` / `db:migrate:test` uniquement.
- Gate spécifique migration : après la migration, un second `bun run db:generate` doit répondre **« No schema changes »** (le snapshot meta décrit le schéma logique final).
- Validation migration sur DB jetable AVANT dev : `createdb seo_insight_migcheck` → migrate → `\d+` → `dropdb` (pattern réconciliation 0045/0046). Jamais de test destructif sur dev.
- Tests DB : `DB_TESTS=1`, base `*_test` uniquement, `assertTestDb()` avant tout delete, pattern `src/lib/db/partitioning.test.ts`.
- `domain-schema.ts` continue de déclarer les deux tables comme des `pgTable` ordinaires — l'ORM ignore le partitionnement (précédent keywords).
- Le worker Rust (`crates/gsc-worker`) n'est PAS modifié.

---

### Task 1: Instrumentation — helper de timing + câblage stats.ts

**Files:**
- Create: `src/lib/observability/timed.ts`
- Create: `src/lib/observability/timed.test.ts`
- Modify: `src/lib/content/stats.ts` (3 requêtes de `getPageStats`)

**Interfaces:**
- Consumes: rien.
- Produces: `timed<T>(label: string, meta: Record<string, string | number>, fn: () => Promise<T>): Promise<T>` — exécute `fn`, logge une ligne structurée `[query-timing] {"label":…,"ms":…,"rows":…,…meta}` (rows = `Array.isArray(result) ? result.length : -1`), retourne le résultat inchangé. Ne throw JAMAIS à cause du logging.

- [ ] **Step 1: Test (failing)**

```ts
// src/lib/observability/timed.test.ts
import { describe, expect, it, spyOn } from "bun:test";
import { timed } from "./timed";

describe("timed", () => {
  it("retourne le résultat de fn inchangé et logge label/ms/rows", async () => {
    const spy = spyOn(console, "log").mockImplementation(() => {});
    const rows = await timed("stats.agg", { siteId: "s1", period: 28 }, async () => [
      1, 2, 3,
    ]);
    expect(rows).toEqual([1, 2, 3]);
    const line = spy.mock.calls.find((c) => c[0] === "[query-timing]");
    expect(line).toBeDefined();
    const payload = JSON.parse(String(line?.[1]));
    expect(payload.label).toBe("stats.agg");
    expect(payload.rows).toBe(3);
    expect(payload.siteId).toBe("s1");
    expect(typeof payload.ms).toBe("number");
    spy.mockRestore();
  });

  it("résultat non-tableau → rows = -1", async () => {
    const spy = spyOn(console, "log").mockImplementation(() => {});
    const out = await timed("x", {}, async () => ({ ok: true }));
    expect(out).toEqual({ ok: true });
    const line = spy.mock.calls.find((c) => c[0] === "[query-timing]");
    expect(JSON.parse(String(line?.[1])).rows).toBe(-1);
    spy.mockRestore();
  });

  it("propage l'erreur de fn (et ne la masque pas)", async () => {
    const spy = spyOn(console, "log").mockImplementation(() => {});
    await expect(timed("boom", {}, async () => Promise.reject(new Error("x")))).rejects.toThrow("x");
    spy.mockRestore();
  });
});
```

- [ ] **Step 2: Run** `bun run test src/lib/observability/` → FAIL (module introuvable).

- [ ] **Step 3: Implémentation**

```ts
// src/lib/observability/timed.ts — timing structuré des requêtes analytiques.
// Phase 0 du premortem DuckDB : on chiffre la contention réelle AVANT
// d'investir dans la Phase 1. Une ligne par requête, parsable (JSON), visible
// dans les logs d3k en dev et Dokploy en prod.

export async function timed<T>(
  label: string,
  meta: Record<string, string | number>,
  fn: () => Promise<T>,
): Promise<T> {
  const t0 = performance.now();
  const result = await fn();
  try {
    const ms = Math.round((performance.now() - t0) * 10) / 10;
    const rows = Array.isArray(result) ? result.length : -1;
    console.log("[query-timing]", JSON.stringify({ label, ms, rows, ...meta }));
  } catch {
    // le logging ne doit jamais casser la requête
  }
  return result;
}
```

- [ ] **Step 4: Run** `bun run test src/lib/observability/` → PASS (3 tests).

- [ ] **Step 5: Câbler `getPageStats`** — dans `src/lib/content/stats.ts`, envelopper CHACUNE des 3 requêtes (l'agrégat group-by, la requête daily/sparklines, la lecture contentItems) :

```ts
const agg = await timed("gsc-stats.agg", { siteId, period: periodDays }, () =>
  db.select({ /* … requête existante inchangée … */ }) /* … */,
);
```

(Adapter aux noms de variables réels du fichier ; ZÉRO changement de logique de requête.) Vérifier que `src/lib/content/stats.test.ts` passe toujours : `bun run test src/lib/content/`.

- [ ] **Step 6: Gate + commit**

```bash
bunx tsc --noEmit && bun run lint && bun run test src/lib/observability/ src/lib/content/
git add src/lib/observability/ src/lib/content/stats.ts
git commit -m "feat(analytics): instrument GSC stats queries (Phase 0 measure-first)"
```

---

### Task 2: Migration — partitionnement mensuel RANGE(date) des deux tables GSC

**Files:**
- Create: `drizzle/0050_gsc_snapshots_partition.sql` (manuscrite — voir procédure)
- Modify: `drizzle/meta/*` (snapshot généré puis conservé)
- Create: `src/lib/db/gsc-partitioning.test.ts`

**Interfaces:**
- Consumes: tables `gsc_query_snapshots` / `gsc_page_snapshots` existantes (créées en 0017), FK `site_id → sites(id) ON DELETE cascade`, PK `(site_id, date, query|page)`.
- Produces: mêmes tables, mêmes noms, même schéma logique — mais `PARTITION BY RANGE (date)`, partitions mensuelles `2026-01 → 2028-12` + `DEFAULT`, index BRIN `(date)` sur chaque parent. Les upserts `ON CONFLICT (site_id, date, dim)` du worker restent valides tels quels (le PK inclut la clé de partition).

- [ ] **Step 1: Générer le squelette de migration**

`domain-schema.ts` ne change PAS (le schéma logique est identique). Créer la migration vide à numéroter proprement :

```bash
bun run db:generate -- --custom --name gsc_snapshots_partition
```

(drizzle-kit crée `drizzle/0050_gsc_snapshots_partition.sql` vide + entrée journal, snapshot inchangé — c'est exactement ce qu'on veut : le schéma logique ne bouge pas, donc le diff doit rester vide.) Si la commande `--custom` n'existe pas dans cette version de drizzle-kit : créer le fichier SQL à la main ET ajouter l'entrée dans `drizzle/meta/_journal.json` (idx 50, tag `0050_gsc_snapshots_partition`, `breakpoints: true`), en copiant la forme des entrées existantes.

- [ ] **Step 2: Écrire le SQL de la migration**

```sql
-- 0050 — Partitionnement mensuel RANGE(date) des snapshots GSC (Phase 0 ADR).
-- Manuscrite : drizzle-kit ne sait pas émettre PARTITION BY. Le schéma logique
-- (colonnes, PK, FK) est inchangé — domain-schema.ts et le snapshot meta
-- restent la vérité déclarative. Pattern copy+swap du précédent 0045 (keywords).
-- Dev/test sont vides mais la PROD ne l'est pas : la copie est obligatoire.

-- ============ gsc_query_snapshots ============
CREATE TABLE "gsc_query_snapshots_p" (LIKE "gsc_query_snapshots" INCLUDING DEFAULTS)
  PARTITION BY RANGE ("date");
--> statement-breakpoint
ALTER TABLE "gsc_query_snapshots_p" ADD PRIMARY KEY ("site_id", "date", "query");
--> statement-breakpoint
DO $$
DECLARE m date;
BEGIN
  FOR m IN SELECT generate_series('2026-01-01'::date, '2028-12-01'::date, interval '1 month')
  LOOP
    EXECUTE format(
      'CREATE TABLE %I PARTITION OF "gsc_query_snapshots_p" FOR VALUES FROM (%L) TO (%L)',
      'gsc_query_snapshots_' || to_char(m, 'YYYY_MM'), m, m + interval '1 month'
    );
  END LOOP;
END $$;
--> statement-breakpoint
CREATE TABLE "gsc_query_snapshots_default" PARTITION OF "gsc_query_snapshots_p" DEFAULT;
--> statement-breakpoint
INSERT INTO "gsc_query_snapshots_p" SELECT * FROM "gsc_query_snapshots";
--> statement-breakpoint
DROP TABLE "gsc_query_snapshots" CASCADE;
--> statement-breakpoint
ALTER TABLE "gsc_query_snapshots_p" RENAME TO "gsc_query_snapshots";
--> statement-breakpoint
ALTER TABLE "gsc_query_snapshots"
  ADD CONSTRAINT "gsc_query_snapshots_site_id_sites_id_fk"
  FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade;
--> statement-breakpoint
CREATE INDEX "gsc_query_snap_date_brin" ON "gsc_query_snapshots" USING brin ("date");
--> statement-breakpoint

-- ============ gsc_page_snapshots ============ (symétrique, dim = page)
CREATE TABLE "gsc_page_snapshots_p" (LIKE "gsc_page_snapshots" INCLUDING DEFAULTS)
  PARTITION BY RANGE ("date");
--> statement-breakpoint
ALTER TABLE "gsc_page_snapshots_p" ADD PRIMARY KEY ("site_id", "date", "page");
--> statement-breakpoint
DO $$
DECLARE m date;
BEGIN
  FOR m IN SELECT generate_series('2026-01-01'::date, '2028-12-01'::date, interval '1 month')
  LOOP
    EXECUTE format(
      'CREATE TABLE %I PARTITION OF "gsc_page_snapshots_p" FOR VALUES FROM (%L) TO (%L)',
      'gsc_page_snapshots_' || to_char(m, 'YYYY_MM'), m, m + interval '1 month'
    );
  END LOOP;
END $$;
--> statement-breakpoint
CREATE TABLE "gsc_page_snapshots_default" PARTITION OF "gsc_page_snapshots_p" DEFAULT;
--> statement-breakpoint
INSERT INTO "gsc_page_snapshots_p" SELECT * FROM "gsc_page_snapshots";
--> statement-breakpoint
DROP TABLE "gsc_page_snapshots" CASCADE;
--> statement-breakpoint
ALTER TABLE "gsc_page_snapshots_p" RENAME TO "gsc_page_snapshots";
--> statement-breakpoint
ALTER TABLE "gsc_page_snapshots"
  ADD CONSTRAINT "gsc_page_snapshots_site_id_sites_id_fk"
  FOREIGN KEY ("site_id") REFERENCES "public"."sites"("id") ON DELETE cascade;
--> statement-breakpoint
CREATE INDEX "gsc_page_snap_date_brin" ON "gsc_page_snapshots" USING brin ("date");
```

NOTE implémenteur : vérifier dans `drizzle/0017_reflective_firelord.sql` les noms EXACTS des contraintes FK d'origine et les reprendre à l'identique (le snapshot meta les référence par nom — un nom divergent casse le gate diff-vide).

- [ ] **Step 3: Valider sur DB jetable**

```bash
createdb seo_insight_migcheck
DATABASE_URL=postgresql://humperdoo@localhost:5432/seo_insight_migcheck bun --env-file=.env.local run src/lib/db/migrate.ts
psql seo_insight_migcheck -c '\d+ gsc_page_snapshots' | head -40   # PARTITION BY RANGE, 37 partitions, BRIN, FK
psql seo_insight_migcheck -c "insert into \"user\" ..."             # facultatif
dropdb seo_insight_migcheck
```

Attendu : `Partition key: RANGE (date)`, 36 partitions mensuelles + default, index BRIN, FK cascade présente.

- [ ] **Step 4: Gate diff-vide** — `bun run db:generate` → doit répondre « No schema changes ». Si drizzle propose un diff, le snapshot est désynchronisé : STOP, corriger (jamais commiter un diff parasite).

- [ ] **Step 5: Appliquer sur test puis dev**

```bash
bun run db:migrate:test && bun run db:migrate
```

(Le dev server peut logguer un burst transitoire « relation does not exist » — connu, re-tester avant de diagnostiquer.)

- [ ] **Step 6: Test d'intégration (failing d'abord si écrit avant le migrate test — sinon écrire puis vérifier PASS)**

```ts
// src/lib/db/gsc-partitioning.test.ts
// Integration — DB_TESTS=1, *_test uniquement. Valide le partitionnement
// mensuel des snapshots GSC : upsert ON CONFLICT idempotent à travers les
// partitions, routage par mois, cascade delete depuis sites, BRIN présent.
import { afterAll, beforeEach, describe, expect, mock, test } from "bun:test";

mock.module("server-only", () => ({}));

const RUN = process.env.DB_TESTS === "1";
const NOW = new Date();
const ORG = "gscp-org";
const OWNER = "gscp-owner";
const SITE = "gscp-site";

describe.skipIf(!RUN)("gsc snapshots partitioning (postgres)", () => {
  // biome-ignore lint/suspicious/noExplicitAny: imports dynamiques de test
  let db: any;
  // biome-ignore lint/suspicious/noExplicitAny: imports dynamiques de test
  let authSchema: any;
  // biome-ignore lint/suspicious/noExplicitAny: imports dynamiques de test
  let domain: any;
  // biome-ignore lint/suspicious/noExplicitAny: imports dynamiques de test
  let sqlTag: any;

  async function cleanup() {
    const { inArray } = await import("drizzle-orm");
    await db.delete(authSchema.organization).where(inArray(authSchema.organization.id, [ORG]));
    await db.delete(authSchema.user).where(inArray(authSchema.user.id, [OWNER]));
  }

  beforeEach(async () => {
    ({ db } = await import("@/lib/db/client"));
    authSchema = await import("@/lib/db/auth-schema");
    domain = await import("@/lib/db/domain-schema");
    ({ sql: sqlTag } = await import("drizzle-orm"));
    const { assertTestDb } = await import("@/lib/db/assert-test-db");
    await assertTestDb();
    await cleanup();
    await db.insert(authSchema.user).values({
      id: OWNER, name: OWNER, email: `${OWNER}@test.dev`,
      emailVerified: true, createdAt: NOW, updatedAt: NOW,
    });
    await db.insert(authSchema.organization).values({ id: ORG, name: ORG, slug: ORG, createdAt: NOW });
    await db.insert(domain.sites).values({
      id: SITE, orgId: ORG, ownerUserId: OWNER, name: "GSC part test",
      primaryLanguage: "fr", createdAt: NOW,
    });
  });

  afterAll(async () => {
    if (!RUN) return;
    await cleanup();
  });

  test("la table est bien partitionnée RANGE(date) avec BRIN", async () => {
    const [part] = await db.execute(
      sqlTag`select partstrat from pg_partitioned_table pt
             join pg_class c on c.oid = pt.partrelid
             where c.relname = 'gsc_page_snapshots'`,
    );
    expect(part?.partstrat).toBe("r");
    const idx = await db.execute(
      sqlTag`select indexname from pg_indexes
             where tablename = 'gsc_page_snapshots' and indexdef ilike '%brin%'`,
    );
    expect(idx.length).toBeGreaterThan(0);
  });

  test("upsert ON CONFLICT idempotent, lignes routées vers la partition du mois", async () => {
    const row = {
      siteId: SITE, date: "2026-07-15", page: "/a",
      clicks: 1, impressions: 10, ctr: 0.1, position: 3.2,
    };
    await db.insert(domain.gscPageSnapshots).values(row);
    await db
      .insert(domain.gscPageSnapshots)
      .values({ ...row, clicks: 5 })
      .onConflictDoUpdate({
        target: [domain.gscPageSnapshots.siteId, domain.gscPageSnapshots.date, domain.gscPageSnapshots.page],
        set: { clicks: 5 },
      });
    const [routed] = await db.execute(
      sqlTag`select count(*)::int as n from gsc_page_snapshots_2026_07 where site_id = ${SITE}`,
    );
    expect(routed?.n).toBe(1);
    const rows = await db.execute(
      sqlTag`select clicks from gsc_page_snapshots where site_id = ${SITE}`,
    );
    expect(rows).toHaveLength(1);
    expect(rows[0].clicks).toBe(5);
  });

  test("date hors horizon → partition DEFAULT (filet de sécurité)", async () => {
    await db.insert(domain.gscPageSnapshots).values({
      siteId: SITE, date: "2031-01-01", page: "/future",
      clicks: 0, impressions: 0, ctr: 0, position: 0,
    });
    const [d] = await db.execute(
      sqlTag`select count(*)::int as n from gsc_page_snapshots_default where site_id = ${SITE}`,
    );
    expect(d?.n).toBe(1);
  });

  test("delete du site → cascade à travers les partitions", async () => {
    await db.insert(domain.gscPageSnapshots).values({
      siteId: SITE, date: "2026-07-15", page: "/a",
      clicks: 1, impressions: 10, ctr: 0.1, position: 3.2,
    });
    const { eq } = await import("drizzle-orm");
    await db.delete(domain.sites).where(eq(domain.sites.id, SITE));
    const rows = await db.execute(
      sqlTag`select 1 from gsc_page_snapshots where site_id = ${SITE}`,
    );
    expect(rows).toHaveLength(0);
  });
});
```

Run : `bun run test src/lib/db/gsc-partitioning.test.ts` → PASS (4 tests). NOTE : adapter les accès `db.execute` au retour réel du driver (postgres.js renvoie un tableau de lignes ; vérifier la forme sur `partitioning.test.ts` existant).

- [ ] **Step 7: Gate + commit**

```bash
bunx tsc --noEmit && bun run lint && bun run db:generate   # « No schema changes »
git add drizzle/ src/lib/db/gsc-partitioning.test.ts
git commit -m "feat(db): monthly RANGE partitioning + BRIN for GSC snapshots (0050, Phase 0)"
```

---

### Task 3: Cache — unstable_cache sur getPageStats

**Files:**
- Modify: `src/lib/content/stats.ts`
- Modify (si besoin): `src/lib/content/stats.test.ts`

**Interfaces:**
- Consumes: `getPageStats(siteId, periodDays)` existant (Task 1 y a ajouté le timing).
- Produces: même signature exacte — le cache est un détail interne.

- [ ] **Step 1: Envelopper la lecture dans `unstable_cache`**

```ts
import { unstable_cache } from "next/cache";

// Le worker GSC écrit 1×/jour (cron Dokploy, fenêtre J-5→J-1) : 1 h de
// staleness est indolore et évite de re-scanner l'historique à chaque visite.
// Invalidation time-based uniquement — le writer est du SQL direct côté Rust,
// il ne peut pas revalidateTag. Le tag existe pour une invalidation app future.
export async function getPageStats(siteId: string, periodDays: StatsPeriod) {
  return unstable_cache(
    () => computePageStats(siteId, periodDays),
    ["gsc-page-stats", siteId, String(periodDays)],
    { revalidate: 3600, tags: [`gsc-stats-${siteId}`] },
  )();
}
```

Renommer l'implémentation actuelle en `computePageStats` (non exportée), `getPageStats` devient le wrapper ci-dessus. ATTENTION : les dates `curStart`/`prevStart` doivent être calculées DANS `computePageStats` (dérivées de `new Date()` — si elles étaient capturées hors du cache, la clé servirait des fenêtres périmées ; ici la clé n'inclut pas la date, mais revalidate 3600 borne la dérive de fenêtre à 1 h, acceptable pour des données J-1).

- [ ] **Step 2: Tests** — `src/lib/content/stats.test.ts` mocke déjà `next/cache` (`unstable_cache` stub pass-through) : vérifier que le stub couvre la nouvelle utilisation et que la suite passe : `bun run test src/lib/content/`. Si le stub actuel ne fait pas pass-through de la fonction, l'ajuster : `unstable_cache: (fn: (...args: unknown[]) => unknown) => fn`.

- [ ] **Step 3: Gate + commit**

```bash
bunx tsc --noEmit && bun run lint && bun run test src/lib/content/
git add src/lib/content/
git commit -m "perf(analytics): cache getPageStats 1h (unstable_cache, Phase 0)"
```

---

### Task 4: Gate final (contrôleur)

- [ ] `bun run test:all` → 0 fail ; `bunx tsc --noEmit` ; `bun run lint` ; `bun run db:generate` → « No schema changes ».
- [ ] Vérification live (dev server) : visiter `/content/stats` → la ligne `[query-timing]` apparaît dans les logs d3k au premier hit, absente au second (cache), réapparaît après 1 h ou redémarrage.
- [ ] ff-merge → dev, push, suppression de branche.
- [ ] Mémoire : mettre à jour `todo-phase0-analytics-scale.md` (étapes 1-3 faites, rollup différé, critère de déclenchement Phase 1 = timings accumulés).
- [ ] **Déploiement prod : NE PAS déclencher** — la migration 0050 partira avec le prochain deploy manuel décidé par l'utilisateur (elle copie les données prod, prévoir le passage du cron worker après migration).