# GSC → Keywords Positioning Overlay — Design (Slice 2)

- **Date**: 2026-06-05
- **Status**: approved (design, revised), pending spec review
- **Section**: Google Search Console → keywords / recherche
- **Depends on**: slice 1 (GSC connection + `sites.gscProperty` mapping) — on `dev`.

## Goal

A manual owner-only "Synchroniser GSC" button on keywords → recherche that pulls the active site's GSC query data and attaches it, as a **positioning overlay**, to **existing** keywords — telling us whether a keyword is positioned and at what average position, to drive improvements. Shown as an inline "GSC" badge next to the keyword.

## Core principle (revised)

- **vol/kd/cpc are the primary reference metrics** (Mangools/Semrush) and are **untouched** — `SourceMetrics` stays strict (those fields stay required). GSC does NOT dilute them.
- **GSC is a separate positioning overlay**, not a `SourceMetrics`. It indicates *"this keyword is positioned"* + its average position (and clicks/impressions/CTR) for improvement focus.
- **Enrich existing keywords only**: a GSC query is matched to an existing keyword by `lower(term)`. Unknown queries (not already in the reservoir) are **ignored** — no keyword creation. The reservoir stays anchored on vol/kd/cpc keywords.

## Decisions

- **Storage**: a dedicated nullable jsonb column `keywords.gsc` (separate from `metrics`), typed `GscTracking | null`. Requires a small migration. `metrics`/`SourceMetrics` are NOT changed.
- **Matching**: by `(siteId, lower(term))` against existing keyword rows; matched → set `gsc` overlay + add `"gsc"` to `sources`. No new rows.
- **Scope**: fetch the property's queries (28d, `dimensions:["query"]`, large `rowLimit` e.g. 25000 in one request) and match all against existing keywords. No top-N cap (that was only relevant for creation, which we no longer do).
- **Indicator**: inline **"GSC" badge** right of the term, rendered when `keyword.gsc != null`; tooltip shows position (1 dp), clics, impressions. **No new column.** vol/kd/cpc columns unchanged.
- **Trigger**: manual button on recherche, active site, **owner-only** (owner's token; cross-user deferred). Members see the badges, no button.

## Non-goals

- Creating keywords from unknown GSC queries (discovery) — deliberately out.
- Scheduled/background sync (Rust worker later); per-page/country dimensions; date-range picker.
- Cross-user token resolution.
- Historical position trend / performance dashboard.

## Prerequisite

Active site has a mapped `gscProperty` (Settings → Intégrations) + owner GSC-connected. Otherwise the action returns a clear error.

## Data model

`src/lib/keywords/types.ts`:
```ts
/** GSC positioning overlay on a keyword (does it rank, and where). Separate from
 *  SourceMetrics — the SEO reference metrics (vol/kd/cpc) are untouched. */
export interface GscTracking {
  clicks: number;
  impressions: number;
  ctr: number;       // 0..1
  position: number;  // average position
  updatedAt: string; // ISO date
}
```
- `Keyword` gains `gsc: GscTracking | null`.
- `keywords` table gains `gsc: jsonb("gsc").$type<GscTracking | null>()` (nullable). `toKeyword` maps it (default null). Migration `drizzle/NNNN_*` → applied to dev + test.

## Components / files

```
src/lib/keywords/types.ts            — add GscTracking; Keyword.gsc
src/lib/db/domain-schema.ts          — keywords.gsc jsonb column (+ migration)
src/lib/keywords/queries.ts          — toKeyword maps gsc
src/lib/gsc/types.ts                 — GscQueryRow (DONE)
src/lib/gsc/parse.ts                 — parseSearchAnalytics (DONE; topByImpressions kept, unused here)
src/lib/gsc/search-analytics.ts      — querySearchAnalytics (DONE)
src/lib/gsc/sync.ts                  — applyGscOverlayForSite(siteId, rows) → { matched, queries }
src/lib/gsc/sync.test.ts             — DB-gated: matches existing by lower(term), sets gsc + sources, ignores unknown
src/lib/gsc/actions.ts               — syncGscKeywordsAction(siteId) (owner-gated)
src/components/keywords/research/research-container.tsx — sync button (owner)
src/components/keywords/research/research-columns.tsx   — inline GSC badge + tooltip
src/app/(app)/keywords/recherche/page.tsx               — resolve canSync (owner), pass down
```

## Sync flow (`syncGscKeywordsAction(siteId)`)

1. Owner-gate; verify the site is in the active org; read `gscProperty` (error if null) + token (error if not connected).
2. `querySearchAnalytics(token, property, { days:28, rowLimit:25000 })` → `GscQueryRow[]`.
3. `applyGscOverlayForSite(siteId, rows)`:
   - load existing keywords for the site; map by `lower(term)`.
   - for each GSC row whose `lower(query)` matches an existing keyword: set `gsc = { clicks, impressions, ctr, position, updatedAt: now }`, add `"gsc"` to `sources` (dedup). Unknown → skip.
   - return `{ matched, queries: rows.length }`.
4. `revalidatePath("/keywords/recherche")`; return `{ ok:true, matched, queries }`.

## UI

- **Button** "Synchroniser GSC" in `research-container` toolbar, owner-only (`canSync` from the page). Transition + result notice "X mots-clés positionnés (sur Y requêtes GSC)" / error; re-runs the existing keyword loader after success.
- **Inline "GSC" badge** in the term cell of `research-columns`, rendered when `keyword.gsc != null`. Tooltip: `Position {gsc.position.toFixed(1)} · {gsc.clicks} clics · {gsc.impressions} impr.`. No new column; vol/kd/cpc columns unchanged.

## Error handling

- Unmapped/not connected → actionable French error.
- GSC API failure → `{ ok:false, error:"Échec de la synchronisation GSC." }`; never crash recherche.
- 0 matches → `{ ok:true, matched:0, queries }` with an info notice.

## Testing

- `search-analytics.test.ts` (pure parse + top-N) — DONE.
- `sync.test.ts` (DB-gated, `_test`, `--conditions=react-server`): existing keyword matched (gsc set, "gsc" in sources, primarySource/status untouched); unknown query ignored (no new row); re-sync updates the overlay.
- Manual (d3k): owner on recherche with a mapped property → sync → existing keywords that rank show the inline "GSC" badge + tooltip (position…); vol/kd/cpc unchanged; unknown queries don't appear.

## Open items for the plan

- Confirm `toKeyword` shape in `queries.ts` and map the new `gsc` column (default null).
- Confirm the migration only adds `keywords.gsc`.