# GSC Snapshots Worker 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 standalone Rust worker (`crates/gsc-worker`) that snapshots GSC search-analytics daily (dimensions date×query and date×page, window J-5→J-1) into Postgres, authenticating via an app-side token broker (Better Auth encrypts OAuth tokens — the app refreshes/decrypts, the worker fetches & upserts).

**Architecture:** Cron-invoked Rust binary (tokio+reqwest+sqlx runtime queries+tracing) → reads `sites.gsc_property` from Postgres → per site asks `POST {APP_URL}/api/worker/gsc-token` (Bearer `WORKER_API_KEY`) for a fresh access token → queries GSC `searchAnalytics/query` with pagination → batch-upserts `gsc_query_snapshots`/`gsc_page_snapshots` + one `gsc_snapshot_runs` row.

**Tech Stack:** Rust (tokio, reqwest rustls, sqlx postgres runtime, serde, tracing, chrono, uuid), Next.js route handler, Drizzle migrations, bun:test.

**Context the implementer must know:**
- Migrations: `bun run db:generate` + `db:migrate` + `db:migrate:test`, NEVER `db:push`. DB tests via `bun run test <path>` (test DB + .env.local).
- The agent cannot write `.env*` — `WORKER_API_KEY` must be added to `.env.local` by the user (live-verify step).
- `auth.api.getAccessToken({ body: { providerId: "google", userId } })` works server-side without a session (signature verified: `userId` optional in body) and handles refresh + decryption (`encryptOAuthTokens: true`).
- Rust deps installed via `sfw cargo add -p gsc-worker <crate>` (Socket Firewall). Workspace root `Cargo.toml` members list must gain `"crates/gsc-worker"`.
- sqlx: use RUNTIME queries (`sqlx::query(...)`) — no compile-time macros (no DB needed at build).

---

### Task 1: Schéma — 3 tables snapshots + migration

**Files:**
- Modify: `src/lib/db/domain-schema.ts`
- Generate: `drizzle/0017_*.sql`

- [ ] **Step 1: Add the tables** (end of domain-schema.ts, before the Relations section; check imports — `date` and `real` may need adding to the drizzle-orm/pg-core import):

```ts
/** Daily GSC search-analytics snapshot, dimension query (worker-written). */
export const gscQuerySnapshots = pgTable(
  "gsc_query_snapshots",
  {
    siteId: text("site_id").notNull().references(() => sites.id, { onDelete: "cascade" }),
    date: date("date").notNull(),
    query: text("query").notNull(),
    clicks: integer("clicks").notNull(),
    impressions: integer("impressions").notNull(),
    ctr: real("ctr").notNull(),
    position: real("position").notNull(),
  },
  (t) => [primaryKey({ columns: [t.siteId, t.date, t.query] })],
);

/** Daily GSC search-analytics snapshot, dimension page (worker-written). */
export const gscPageSnapshots = pgTable(
  "gsc_page_snapshots",
  {
    siteId: text("site_id").notNull().references(() => sites.id, { onDelete: "cascade" }),
    date: date("date").notNull(),
    page: text("page").notNull(),
    clicks: integer("clicks").notNull(),
    impressions: integer("impressions").notNull(),
    ctr: real("ctr").notNull(),
    position: real("position").notNull(),
  },
  (t) => [primaryKey({ columns: [t.siteId, t.date, t.page] })],
);

/** One row per worker run (observability: last snapshot, failures). */
export const gscSnapshotRuns = pgTable("gsc_snapshot_runs", {
  id: text("id").primaryKey(),
  startedAt: timestamp("started_at", { withTimezone: true }).notNull(),
  finishedAt: timestamp("finished_at", { withTimezone: true }),
  sitesOk: integer("sites_ok").notNull().default(0),
  sitesFailed: integer("sites_failed").notNull().default(0),
  rowsUpserted: integer("rows_upserted").notNull().default(0),
  error: text("error"),
});
```

- [ ] **Step 2:** `bun run db:generate` — inspect: ONLY the 3 tables (2 composite PKs, cascade FKs on site_id, no extra).
- [ ] **Step 3:** `bun run db:migrate && bun run db:migrate:test` — both "✓ migrations applied".
- [ ] **Step 4:** Verify: `psql $DATABASE_URL -c "\d gsc_query_snapshots"` (PK (site_id,date,query), real ctr/position).
- [ ] **Step 5: Commit**

```bash
git add src/lib/db/domain-schema.ts drizzle/
git commit -m "feat(db): gsc snapshot tables (query/page daily + runs)"
```

---

### Task 2: Token broker — logique `worker-token.ts` (TDD)

**Files:**
- Modify: `src/env.ts` (add `WORKER_API_KEY: z.string().optional(),` next to the other optional secrets)
- Create: `src/lib/gsc/worker-token.ts`
- Test: `src/lib/gsc/worker-token.test.ts`

- [ ] **Step 1: Failing test.** READ a sibling DB test FIRST (`src/lib/keywords/actions.test.ts` harness: mock server-only, RUN_DB gate, assertTestDb, org/user/site seed). Create `worker-token.test.ts`:

```ts
import { beforeEach, describe, expect, mock, test } from "bun:test";
mock.module("server-only", () => ({}));
mock.module("next/headers", () => ({ headers: async () => new Headers() }));
// Mock the auth module BEFORE importing worker-token (it imports @/lib/auth):
const getAccessToken = mock(async (_args: unknown) => ({ accessToken: "fresh-token" }));
mock.module("@/lib/auth", () => ({ auth: { api: { getAccessToken } } }));

const RUN_DB = process.env.DB_TESTS === "1";

describe.skipIf(!RUN_DB)("resolveWorkerGscToken", () => {
  // seed in beforeEach: org WT-ORG, user WT-USR, member(role owner), site WT-SITE
  // with gscProperty "sc-domain:example.com", account(providerId google, userId WT-USR)
  // — mirror the harness of actions.test.ts; clean those tables first (assertTestDb()).

  test("resolves site → owner → google account → access token", async () => {
    const { resolveWorkerGscToken } = await import("./worker-token");
    const res = await resolveWorkerGscToken("WT-SITE");
    expect(res).toEqual({ accessToken: "fresh-token", property: "sc-domain:example.com" });
    expect(getAccessToken).toHaveBeenCalled();
  });

  test("404-style nulls: no gscProperty / no google account / unknown site", async () => {
    const { resolveWorkerGscToken } = await import("./worker-token");
    expect(await resolveWorkerGscToken("ghost")).toBeNull();
    // + a seeded site without gscProperty → null
    // + a seeded site whose owner has no google account row → null
  });
});
```
Flesh out the seeds exactly per the harness (org/user/member/site/account inserts; `account` needs id, accountId, providerId "google", userId, createdAt/updatedAt per auth-schema).

- [ ] **Step 2:** Run `bun run test src/lib/gsc/worker-token.test.ts` — FAILS (module not found).
- [ ] **Step 3: Implement** `src/lib/gsc/worker-token.ts`:

```ts
// src/lib/gsc/worker-token.ts — resolve a fresh GSC access token for a site (worker broker).
import "server-only";
import { and, eq } from "drizzle-orm";
import { auth } from "@/lib/auth";
import { db } from "@/lib/db/client";
import { account, member } from "@/lib/db/auth-schema";
import { sites } from "@/lib/db/domain-schema";

export type WorkerGscToken = { accessToken: string; property: string };

/**
 * Site → org owner → google account → fresh access token (Better Auth refreshes
 * + decrypts; encryptOAuthTokens is on). Returns null when any link is missing.
 */
export async function resolveWorkerGscToken(
  siteId: string,
): Promise<WorkerGscToken | null> {
  const site = (
    await db.select().from(sites).where(eq(sites.id, siteId))
  )[0];
  if (!site?.gscProperty) return null;
  const owner = (
    await db
      .select()
      .from(member)
      .where(and(eq(member.organizationId, site.orgId), eq(member.role, "owner")))
  )[0];
  if (!owner) return null;
  const googleAccount = (
    await db
      .select()
      .from(account)
      .where(and(eq(account.userId, owner.userId), eq(account.providerId, "google")))
  )[0];
  if (!googleAccount) return null;
  try {
    const res = await auth.api.getAccessToken({
      body: { providerId: "google", userId: owner.userId },
    });
    if (!res?.accessToken) return null;
    return { accessToken: res.accessToken, property: site.gscProperty };
  } catch {
    return null;
  }
}
```
(If `sites.orgId` has a different JS name, READ domain-schema.ts and adjust. If `auth.api.getAccessToken` requires a `headers` field even server-side, pass `headers: new Headers()`.)

- [ ] **Step 4:** Tests pass + `npx tsc --noEmit` clean.
- [ ] **Step 5: Commit**

```bash
git add src/env.ts src/lib/gsc/worker-token.ts src/lib/gsc/worker-token.test.ts
git commit -m "feat(gsc): worker token broker logic (site→owner→google access token)"
```

---

### Task 3: Route handler `/api/worker/gsc-token`

**Files:**
- Create: `src/app/api/worker/gsc-token/route.ts`
- Test: `src/lib/gsc/worker-route.test.ts`

- [ ] **Step 1: Failing test** (unit — the handler is a plain async function):

```ts
import { describe, expect, mock, test } from "bun:test";
mock.module("server-only", () => ({}));
const resolveWorkerGscToken = mock(async (siteId: string) =>
  siteId === "ok-site" ? { accessToken: "tok", property: "sc-domain:x.com" } : null,
);
mock.module("@/lib/gsc/worker-token", () => ({ resolveWorkerGscToken }));
mock.module("@/env", () => ({ env: { WORKER_API_KEY: "secret-key" } }));

const { POST } = await import("@/app/api/worker/gsc-token/route");

const req = (auth: string | null, body: unknown) =>
  new Request("http://test/api/worker/gsc-token", {
    method: "POST",
    headers: { "Content-Type": "application/json", ...(auth ? { Authorization: auth } : {}) },
    body: JSON.stringify(body),
  });

describe("POST /api/worker/gsc-token", () => {
  test("401 without/with wrong bearer", async () => {
    expect((await POST(req(null, { siteId: "ok-site" }))).status).toBe(401);
    expect((await POST(req("Bearer nope", { siteId: "ok-site" }))).status).toBe(401);
  });
  test("200 with token for a resolvable site", async () => {
    const res = await POST(req("Bearer secret-key", { siteId: "ok-site" }));
    expect(res.status).toBe(200);
    expect(await res.json()).toEqual({ accessToken: "tok", property: "sc-domain:x.com" });
  });
  test("404 when unresolvable; 400 on bad body", async () => {
    expect((await POST(req("Bearer secret-key", { siteId: "ghost" }))).status).toBe(404);
    expect((await POST(req("Bearer secret-key", {}))).status).toBe(400);
  });
});
```
(Plus a 503 case: re-mock `@/env` with `WORKER_API_KEY: undefined` in a separate describe/file if bun's mock.module allows; if module-mock leakage makes it flaky, assert 503 logic by direct inspection and note it.)

- [ ] **Step 2:** FAILS (route module missing).
- [ ] **Step 3: Implement** `src/app/api/worker/gsc-token/route.ts`:

```ts
// POST /api/worker/gsc-token — token broker for the Rust GSC snapshots worker.
import { timingSafeEqual } from "node:crypto";
import { env } from "@/env";
import { resolveWorkerGscToken } from "@/lib/gsc/worker-token";

function keyMatches(header: string | null, key: string): boolean {
  if (!header?.startsWith("Bearer ")) return false;
  const got = Buffer.from(header.slice(7));
  const want = Buffer.from(key);
  return got.length === want.length && timingSafeEqual(got, want);
}

export async function POST(req: Request): Promise<Response> {
  const key = env.WORKER_API_KEY;
  if (!key) return Response.json({ error: "worker disabled" }, { status: 503 });
  if (!keyMatches(req.headers.get("authorization"), key)) {
    return Response.json({ error: "unauthorized" }, { status: 401 });
  }
  let siteId: unknown;
  try {
    siteId = ((await req.json()) as { siteId?: unknown }).siteId;
  } catch {
    return Response.json({ error: "invalid body" }, { status: 400 });
  }
  if (typeof siteId !== "string" || !siteId) {
    return Response.json({ error: "siteId required" }, { status: 400 });
  }
  const token = await resolveWorkerGscToken(siteId);
  if (!token) return Response.json({ error: "not resolvable" }, { status: 404 });
  return Response.json(token);
}
```

- [ ] **Step 4:** Tests pass + tsc clean + `bunx biome check` on both files.
- [ ] **Step 5: Commit**

```bash
git add src/app/api/worker/gsc-token/ src/lib/gsc/worker-route.test.ts
git commit -m "feat(api): /api/worker/gsc-token broker (Bearer WORKER_API_KEY)"
```

---

### Task 4: Crate `gsc-worker` — squelette + logique pure (TDD Rust)

**Files:**
- Modify: `Cargo.toml` (root: members += "crates/gsc-worker")
- Create: `crates/gsc-worker/Cargo.toml`, `crates/gsc-worker/src/main.rs`, `crates/gsc-worker/src/gsc.rs`, `crates/gsc-worker/src/window.rs`

- [ ] **Step 1: Scaffold.** `cargo new crates/gsc-worker --bin --vcs none`; add `"crates/gsc-worker"` to root workspace members. Deps (via `sfw cargo add -p gsc-worker ...`): `tokio --features rt-multi-thread,macros`, `reqwest --no-default-features --features json,rustls-tls`, `sqlx --no-default-features --features runtime-tokio,tls-rustls,postgres,chrono,uuid`, `serde --features derive`, `serde_json`, `chrono`, `uuid --features v4`, `tracing`, `tracing-subscriber --features env-filter`.

- [ ] **Step 2: TDD the pure logic.** `src/window.rs`:

```rust
use chrono::{Duration, NaiveDate};

/// GSC snapshot window: [today-days, today-1] inclusive (GSC data lags ~2-3 days;
/// re-fetching the window + upserting self-corrects recent days).
pub fn snapshot_window(today: NaiveDate, days: i64) -> (NaiveDate, NaiveDate) {
    (today - Duration::days(days), today - Duration::days(1))
}

#[cfg(test)]
mod tests {
    use super::*;
    #[test]
    fn window_is_j_minus_days_to_j_minus_1() {
        let today = NaiveDate::from_ymd_opt(2026, 6, 10).unwrap();
        let (start, end) = snapshot_window(today, 5);
        assert_eq!(start, NaiveDate::from_ymd_opt(2026, 6, 5).unwrap());
        assert_eq!(end, NaiveDate::from_ymd_opt(2026, 6, 9).unwrap());
    }
}
```

`src/gsc.rs` — response types + parse + request body building + pagination decision:

```rust
use serde::Deserialize;

#[derive(Debug, Deserialize)]
pub struct GscResponse {
    #[serde(default)]
    pub rows: Vec<GscRow>,
}

#[derive(Debug, Deserialize)]
pub struct GscRow {
    pub keys: Vec<String>, // [date, query|page]
    #[serde(default)]
    pub clicks: f64,
    #[serde(default)]
    pub impressions: f64,
    #[serde(default)]
    pub ctr: f64,
    #[serde(default)]
    pub position: f64,
}

/// A parsed snapshot row (dimension-agnostic: `key` is the query or the page).
#[derive(Debug, PartialEq)]
pub struct SnapshotRow {
    pub date: String, // YYYY-MM-DD (validated by Postgres date type on insert)
    pub key: String,
    pub clicks: i32,
    pub impressions: i32,
    pub ctr: f32,
    pub position: f32,
}

pub fn parse_rows(resp: GscResponse) -> Vec<SnapshotRow> {
    resp.rows
        .into_iter()
        .filter_map(|r| {
            let mut keys = r.keys.into_iter();
            let date = keys.next()?;
            let key = keys.next()?;
            Some(SnapshotRow {
                date,
                key,
                clicks: r.clicks as i32,
                impressions: r.impressions as i32,
                ctr: r.ctr as f32,
                position: r.position as f32,
            })
        })
        .collect()
}

/// GSC returns at most `row_limit` rows; a full page means there may be more.
pub fn needs_next_page(returned: usize, row_limit: usize) -> bool {
    returned == row_limit
}

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

    #[test]
    fn parses_rows_and_skips_malformed() {
        let json = r#"{"rows":[
            {"keys":["2026-06-08","achat groupe"],"clicks":3,"impressions":120,"ctr":0.025,"position":12.4},
            {"keys":["2026-06-08"],"clicks":1,"impressions":5,"ctr":0.2,"position":3.0}
        ]}"#;
        let parsed = parse_rows(serde_json::from_str(json).unwrap());
        assert_eq!(parsed.len(), 1);
        assert_eq!(parsed[0].key, "achat groupe");
        assert_eq!(parsed[0].clicks, 3);
    }

    #[test]
    fn empty_response_yields_no_rows() {
        let parsed = parse_rows(serde_json::from_str("{}").unwrap());
        assert!(parsed.is_empty());
    }

    #[test]
    fn pagination_continues_only_on_full_page() {
        assert!(needs_next_page(25000, 25000));
        assert!(!needs_next_page(24999, 25000));
    }
}
```

`src/main.rs` minimal for now (wired fully in Task 5):

```rust
mod gsc;
mod window;

fn main() {
    println!("gsc-worker scaffold");
}
```

- [ ] **Step 3:** `cargo test -p gsc-worker` — all pass. `cargo build -p gsc-worker` — clean.
- [ ] **Step 4: Commit**

```bash
git add Cargo.toml Cargo.lock crates/gsc-worker
git commit -m "feat(gsc-worker): crate scaffold + pure parse/window/pagination (TDD)"
```

---

### Task 5: Worker — boucle complète (broker + GSC + upserts + run row)

**Files:**
- Modify: `crates/gsc-worker/src/main.rs`
- Create: `crates/gsc-worker/src/db.rs`, `crates/gsc-worker/src/broker.rs`

- [ ] **Step 1: broker.rs** — client of the app:

```rust
use serde::Deserialize;

#[derive(Debug, Deserialize)]
#[serde(rename_all = "camelCase")]
pub struct WorkerToken {
    pub access_token: String,
    pub property: String,
}

pub async fn fetch_token(
    http: &reqwest::Client,
    app_url: &str,
    api_key: &str,
    site_id: &str,
) -> anyhow::Result<WorkerToken> {
    let res = http
        .post(format!("{app_url}/api/worker/gsc-token"))
        .bearer_auth(api_key)
        .json(&serde_json::json!({ "siteId": site_id }))
        .send()
        .await?;
    if !res.status().is_success() {
        anyhow::bail!("broker HTTP {} for site {site_id}", res.status());
    }
    Ok(res.json().await?)
}
```
(Add `anyhow` via `sfw cargo add -p gsc-worker anyhow`.)

- [ ] **Step 2: db.rs** — sqlx pool + upserts + runs:

```rust
use sqlx::{postgres::PgPoolOptions, PgPool};
use crate::gsc::SnapshotRow;

pub async fn connect(database_url: &str) -> anyhow::Result<PgPool> {
    Ok(PgPoolOptions::new().max_connections(3).connect(database_url).await?)
}

pub async fn sites_with_property(pool: &PgPool) -> anyhow::Result<Vec<(String, String)>> {
    let rows: Vec<(String, String)> =
        sqlx::query_as("select id, gsc_property from sites where gsc_property is not null")
            .fetch_all(pool)
            .await?;
    Ok(rows)
}

/// Upsert one batch into gsc_query_snapshots or gsc_page_snapshots.
/// `dim_col` is "query" or "page" (table + column derive from it).
pub async fn upsert_snapshots(
    pool: &PgPool,
    site_id: &str,
    dim_col: &str,
    rows: &[SnapshotRow],
) -> anyhow::Result<u64> {
    let table = match dim_col {
        "query" => "gsc_query_snapshots",
        "page" => "gsc_page_snapshots",
        _ => anyhow::bail!("unknown dimension {dim_col}"),
    };
    let mut count = 0u64;
    for chunk in rows.chunks(500) {
        let mut tx = pool.begin().await?;
        for r in chunk {
            sqlx::query(&format!(
                "insert into {table} (site_id, date, {dim_col}, clicks, impressions, ctr, position)
                 values ($1, $2::date, $3, $4, $5, $6, $7)
                 on conflict (site_id, date, {dim_col})
                 do update set clicks = excluded.clicks, impressions = excluded.impressions,
                               ctr = excluded.ctr, position = excluded.position"
            ))
            .bind(site_id)
            .bind(&r.date)
            .bind(&r.key)
            .bind(r.clicks)
            .bind(r.impressions)
            .bind(r.ctr)
            .bind(r.position)
            .execute(&mut *tx)
            .await?;
            count += 1;
        }
        tx.commit().await?;
    }
    Ok(count)
}

pub async fn insert_run(pool: &PgPool, id: &str) -> anyhow::Result<()> {
    sqlx::query("insert into gsc_snapshot_runs (id, started_at) values ($1, now())")
        .bind(id).execute(pool).await?;
    Ok(())
}

pub async fn finish_run(
    pool: &PgPool, id: &str, ok: i32, failed: i32, rows: i64, error: Option<&str>,
) -> anyhow::Result<()> {
    sqlx::query(
        "update gsc_snapshot_runs set finished_at = now(), sites_ok = $2,
         sites_failed = $3, rows_upserted = $4, error = $5 where id = $1",
    )
    .bind(id).bind(ok).bind(failed).bind(rows).bind(error)
    .execute(pool).await?;
    Ok(())
}
```
NOTE: the table/column names are interpolated from a CLOSED two-value match (never user input) — safe; bind everything else.

- [ ] **Step 3: main.rs** — the run loop:

```rust
mod broker;
mod db;
mod gsc;
mod window;

use chrono::Utc;
use tracing::{error, info};

const ROW_LIMIT: usize = 25_000;

async fn fetch_dimension(
    http: &reqwest::Client,
    token: &str,
    property: &str,
    dim: &str,
    start: &str,
    end: &str,
) -> anyhow::Result<Vec<gsc::SnapshotRow>> {
    let url = format!(
        "https://www.googleapis.com/webmasters/v3/sites/{}/searchAnalytics/query",
        urlencoding::encode(property)
    );
    let mut all = Vec::new();
    let mut start_row = 0usize;
    loop {
        let res = http
            .post(&url)
            .bearer_auth(token)
            .json(&serde_json::json!({
                "startDate": start, "endDate": end,
                "dimensions": ["date", dim],
                "rowLimit": ROW_LIMIT, "startRow": start_row,
            }))
            .send()
            .await?;
        if !res.status().is_success() {
            anyhow::bail!("GSC HTTP {} ({dim})", res.status());
        }
        let parsed = gsc::parse_rows(res.json().await?);
        let n = parsed.len();
        all.extend(parsed);
        if !gsc::needs_next_page(n, ROW_LIMIT) {
            break;
        }
        start_row += ROW_LIMIT;
    }
    Ok(all)
}

#[tokio::main]
async fn main() -> anyhow::Result<()> {
    tracing_subscriber::fmt().with_env_filter(
        tracing_subscriber::EnvFilter::try_from_default_env()
            .unwrap_or_else(|_| "info".into()),
    ).init();

    let database_url = std::env::var("DATABASE_URL")?;
    let app_url = std::env::var("APP_URL").unwrap_or_else(|_| "http://localhost:3000".into());
    let api_key = std::env::var("WORKER_API_KEY")?;
    let days: i64 = std::env::var("GSC_WINDOW_DAYS").ok()
        .and_then(|v| v.parse().ok()).unwrap_or(5);

    let pool = db::connect(&database_url).await?;
    let http = reqwest::Client::new();
    let run_id = uuid::Uuid::new_v4().to_string();
    db::insert_run(&pool, &run_id).await?;

    let (start, end) = window::snapshot_window(Utc::now().date_naive(), days);
    let (start, end) = (start.to_string(), end.to_string());
    info!(%start, %end, "snapshot window");

    let sites = db::sites_with_property(&pool).await?;
    info!(count = sites.len(), "sites with GSC property");

    let (mut ok, mut failed, mut total_rows) = (0i32, 0i32, 0i64);
    let mut first_error: Option<String> = None;

    for (site_id, _property) in &sites {
        let result: anyhow::Result<u64> = async {
            let tok = broker::fetch_token(&http, &app_url, &api_key, site_id).await?;
            let mut rows = 0u64;
            for dim in ["query", "page"] {
                let parsed =
                    fetch_dimension(&http, &tok.access_token, &tok.property, dim, &start, &end)
                        .await?;
                rows += db::upsert_snapshots(&pool, site_id, dim, &parsed).await?;
            }
            Ok(rows)
        }
        .await;
        match result {
            Ok(rows) => { ok += 1; total_rows += rows as i64; info!(site_id, rows, "site ok"); }
            Err(e) => {
                failed += 1;
                error!(site_id, %e, "site failed");
                first_error.get_or_insert_with(|| format!("{site_id}: {e}"));
            }
        }
    }

    db::finish_run(&pool, &run_id, ok, failed, total_rows, first_error.as_deref()).await?;
    info!(ok, failed, total_rows, "run finished");
    if ok == 0 && failed > 0 { std::process::exit(1); }
    Ok(())
}
```
(Add `urlencoding` via `sfw cargo add -p gsc-worker urlencoding`.)

- [ ] **Step 4:** `cargo test -p gsc-worker && cargo build -p gsc-worker` clean (units from Task 4 still pass; no network/DB in tests). `cargo clippy -p gsc-worker` — no warnings (or fix).
- [ ] **Step 5: Commit**

```bash
git add crates/gsc-worker Cargo.lock
git commit -m "feat(gsc-worker): full run loop (broker token, GSC fetch, upserts, run row)"
```

---

### Task 6: Script + README + vérification live + roadmap/Linear

**Files:**
- Modify: `package.json` (script), `docs/project/roadmap.md`
- Create: `crates/gsc-worker/README.md`

- [ ] **Step 1: Script.** package.json: `"worker:gsc": "sh -c 'set -a; . ./.env.local; set +a; APP_URL=${APP_URL:-http://localhost:3000} cargo run -p gsc-worker'"`
- [ ] **Step 2: README** (`crates/gsc-worker/README.md`): purpose, env (DATABASE_URL/APP_URL/WORKER_API_KEY/GSC_WINDOW_DAYS), dev run (`bun run worker:gsc`), prod cron (Dokploy quotidien, ex. 06:00 UTC), the broker dependency (app must be up), idempotence (window re-fetch + upsert).
- [ ] **Step 3: USER ACTION** — ask the user to add `WORKER_API_KEY=<random>` to `.env.local` (e.g. `openssl rand -hex 32`) and restart the dev server (env re-parse).
- [ ] **Step 4: Live run** — dev server up, `bun run worker:gsc`; verify: `gsc_snapshot_runs` has a finished row (sites_ok ≥ 1); `select date, count(*) from gsc_query_snapshots group by 1 order by 1` shows J-5→J-1; same for pages. Re-run → idempotent (same counts ±latest GSC corrections).
- [ ] **Step 5: Roadmap + Linear** — roadmap.md: marquer le worker ✅ (Socle) avec le résumé ; ajouter la ligne cron au runbook ELEVA-41 ; fermer ELEVA-40 (commentaire de livraison).
- [ ] **Step 6: Commit**

```bash
git add package.json crates/gsc-worker/README.md docs/project/roadmap.md
git commit -m "docs(gsc-worker): script dev + README ops + roadmap (ELEVA-40)"
```

---

## Notes for the executor
- Order: 1 → 2 → 3 → 4 → 5 → 6 (each builds on the previous).
- Task 6 step 3 BLOCKS on the user (`.env.local`) — surface it clearly when reached.
- Rust deps via `sfw cargo add -p gsc-worker <crate>` ; if `sfw` chokes on cargo, plain `cargo add` and note it.
- sqlx runtime queries only (no `query!` macros — no DB at compile time).