# P4 — Provisioning des partitions mensuelles GSC

> Compagnon de `docs/project/ops/db-audit-2026-07-07.md` (finding **P4**,
> chantier §4.2). Statut : outillage + tests prêts sur `dev`
> (`feature/gsc-partition-provisioning`) ; **câblage du timer systemd en
> prod reste une opération manuelle, sudo-gated** — pas faite par cette doc.

## Problème

`drizzle/0050_gsc_snapshots_partition.sql` partitionne `gsc_query_snapshots`
et `gsc_page_snapshots` en RANGE(date) mensuel, avec **72 partitions
pré-créées en dur** (`2026_01` → `2028_12`) + une partition `DEFAULT` par
table. Aucun job n'en crée de nouvelles. Dès `2029-01` :

- les upserts du worker Rust (`crates/gsc-worker/src/db.rs`) routent
  **silencieusement** vers `*_default` (pas d'erreur, pas d'alerte) ;
- le pruning par date est perdu pour ces lignes ;
- tout `CREATE TABLE ... PARTITION OF` correctif doit scanner la `DEFAULT`
  entière sous verrou `ACCESS EXCLUSIVE` pour vérifier qu'aucune ligne ne
  viole la nouvelle contrainte de plage — coûteux si la fuite a duré
  longtemps.

`pg_partman` n'est pas installé sur le serveur prod (`serveur-prod.md`) et
`pg_cron` exige `shared_preload_libraries` (non garanti côté config
serveur) : la solution ne dépend d'aucun des deux. À la place : une
fonction SQL idempotente + un script bun rejouable, câblés à un timer
systemd mensuel — même schéma que `seo-insight-gsc-worker.{service,timer}`,
déjà en place pour la sync GSC quotidienne.

## Ce qui est livré

| Pièce | Où |
|---|---|
| `ensure_gsc_partitions(months_ahead int default 6)` | `drizzle/0059_gsc_partitions_maintenance.sql` |
| `gsc_default_partition_rowcounts()` (canari) | idem |
| Script bun rejouable | `scripts/gsc-ensure-partitions.ts` (`bun run gsc:partitions`) |
| Tests d'intégration | `src/lib/db/gsc-partition-provisioning.test.ts` (`DB_TESTS=1`) |
| Unités systemd (à installer manuellement en prod) | `deploy/systemd/seo-insight-gsc-partitions.{service,timer}` |

### `ensure_gsc_partitions`

Pour **chacune** des deux tables et **chaque mois** de `date_trunc('month',
now())` à `+months_ahead` mois, crée `<table>_YYYY_MM` (convention de
nommage identique à 0050, donc les 72 partitions pré-créées sont
reconnues et jamais recréées).

Idempotence : `to_regclass()` vérifie d'abord par nom exact si la
partition du mois existe déjà (chemin emprunté par tous les rejeux et par
toutes les partitions pré-créées par 0050). Deux exceptions sont attrapées
**par mois** (jamais pour tout le run) :

- `duplicate_table` — créée entre notre vérification et le `CREATE`
  (concurrence, deux exécutions qui se chevauchent) → traité comme `exists`.
- `invalid_object_definition` (`42P17`) — la plage de ce mois chevauche une
  partition déjà enregistrée sous un **autre nom** que notre convention
  (partition ad-hoc créée manuellement) → `RAISE WARNING` + `overlap_skipped`,
  le run continue sur les mois suivants au lieu d'échouer entièrement.

Retourne une ligne `(partition_name, action)` par (table × mois), `action`
∈ `{created, exists, overlap_skipped}`.

**Ne crée jamais de partition dans le passé** : la boucle part de
`date_trunc('month', now())`, jamais avant. Important pour la remédiation
(§ ci-dessous).

### `gsc_default_partition_rowcounts()`

`SELECT partition_name, row_count` pour les deux `*_default`. `row_count >
0` = des lignes ont fui faute de partition pour leur mois.

### `scripts/gsc-ensure-partitions.ts`

```bash
bun run gsc:partitions                              # 6 mois d'avance (défaut)
GSC_PARTITION_MONTHS_AHEAD=12 bun run gsc:partitions # horizon custom
```

Appelle `ensure_gsc_partitions(N)`, logue les partitions créées et les
éventuels `overlap_skipped`, puis appelle le canari. **Exit code 1** si une
`*_default` contient au moins une ligne — c'est ce code de sortie que le
timer systemd utilise pour faire remonter l'alerte (`systemctl status`
marque le dernier run `failed`, visible via `journalctl` / tout monitoring
branché sur l'état systemd).

Rejouable sans risque en prod : ne crée que des partitions manquantes, ne
touche jamais aux données existantes.

## Câblage prod (à faire manuellement)

Mêmes commandes que le fix F1 du timer GSC existant
(`docs/project/ops/serveur-prod.md`) :

```bash
sudo cp /data/seo-insight/deploy/systemd/seo-insight-gsc-partitions.service \
        /data/seo-insight/deploy/systemd/seo-insight-gsc-partitions.timer \
        /etc/systemd/system/
sudo systemctl daemon-reload
sudo systemctl enable --now seo-insight-gsc-partitions.timer
sudo systemctl start seo-insight-gsc-partitions.service   # premier run immédiat, pour vérifier avant d'attendre le mois prochain
journalctl -u seo-insight-gsc-partitions -f
```

Le `.timer` déclenche le `.service` (oneshot) le 1er de chaque mois à
03:10 + jitter 10 min (`OnCalendar=*-*-01 03:10:00`, voir le fichier pour
le détail). `Persistent=true` rattrape un run manqué si la box était
éteinte au moment prévu.

`deploy/deploy.sh` ne référence pas encore cette unité (comme
`seo-insight-gsc-worker` avant le fix F1, c'est un `Type=oneshot` piloté
par un timer, pas un service à redémarrer à chaque deploy — inutile de
l'y ajouter tant que le timer est actif : le prochain run planifié prend
le code à jour automatiquement).

### Vérifier après install

```bash
systemctl list-timers seo-insight-gsc-partitions.timer
journalctl -u seo-insight-gsc-partitions -n 50
psql "$DATABASE_URL" -c "select * from gsc_default_partition_rowcounts();"
```

## Si le canari alerte (`row_count > 0` sur une `DEFAULT`)

1. **Diagnostiquer l'ampleur et les mois touchés** :

   ```sql
   select date_trunc('month', date)::date as month, count(*)
   from gsc_query_snapshots_default group by 1 order by 1;

   select date_trunc('month', date)::date as month, count(*)
   from gsc_page_snapshots_default group by 1 order by 1;
   ```

2. **Arrêter la fuite pour le mois courant et les suivants** — rejouer le
   provisioning tout de suite plutôt que d'attendre le prochain run planifié :

   ```bash
   bun run gsc:partitions
   ```

   Si le mois qui a fui est **le mois courant** (cas le plus probable —
   canari qui alerte peu de temps après la fuite), cette seule commande crée
   la partition manquante et les nouvelles lignes routeront correctement.
   Mais les lignes **déjà** tombées dans `DEFAULT` n'y sont PAS déplacées
   automatiquement (créer la partition ne fait que router les *futures*
   insertions) — étape 3 obligatoire dans tous les cas.

3. **Sortir les lignes déjà fuitées de `DEFAULT`, PAR MOIS TOUCHÉ**, pour
   éviter le scan `ACCESS EXCLUSIVE` coûteux qu'un `CREATE TABLE ...
   PARTITION OF` ferait sur une `DEFAULT` qui contient encore des lignes
   dans la plage ciblée (il refuserait la création tant que ces lignes n'ont
   pas été évacuées — c'est le risque documenté par l'audit P4). Procédure
   par table × mois concerné (exemple `gsc_query_snapshots`, mois
   `2029-01`) :

   ```sql
   BEGIN;

   -- 1. Copier les lignes du mois hors de DEFAULT.
   CREATE TEMP TABLE gsc_query_leak_202901 AS
     SELECT * FROM gsc_query_snapshots_default
     WHERE date >= '2029-01-01' AND date < '2029-02-01';

   -- 2. Les retirer de DEFAULT (DELETE ciblé = verrou ROW EXCLUSIVE, pas
   --    ACCESS EXCLUSIVE ; coût proportionnel aux lignes qui ont fui, pas à
   --    la taille de DEFAULT).
   DELETE FROM gsc_query_snapshots_default
     WHERE date >= '2029-01-01' AND date < '2029-02-01';

   -- 3. Créer la partition du mois. Si c'est le mois COURANT au moment de
   --    la remédiation, `select * from ensure_gsc_partitions(0)` suffit ; si
   --    le mois est déjà PASSÉ (canari repéré tardivement), la fonction ne
   --    remonte jamais en arrière — créer explicitement :
   CREATE TABLE gsc_query_snapshots_2029_01 PARTITION OF gsc_query_snapshots
     FOR VALUES FROM ('2029-01-01') TO ('2029-02-01');
   -- DEFAULT est maintenant vide pour cette plage : la vérification que
   -- Postgres fait avant d'accepter la nouvelle partition reste bon marché
   -- (peu ou pas de lignes à scanner) — c'est tout l'intérêt d'agir tôt,
   -- pendant que la fuite est encore petite, plutôt que des années plus
   -- tard sur une DEFAULT qui a eu le temps de grossir.

   -- 4. Réinsérer via la table mère : Postgres route automatiquement vers
   --    la partition qui vient d'être créée.
   INSERT INTO gsc_query_snapshots SELECT * FROM gsc_query_leak_202901;

   DROP TABLE gsc_query_leak_202901;

   COMMIT;
   ```

   Répéter par mois touché et par table (`gsc_page_snapshots` est
   symétrique — mêmes colonnes de dimension `page` au lieu de `query`).

4. **Vérifier que le canari est retombé à zéro** :

   ```bash
   bun run gsc:partitions   # doit sortir en exit 0, "aucune fuite vers DEFAULT"
   ```

5. **Post-mortem** — comprendre pourquoi le timer a manqué un cycle avant
   d'en arriver là (box éteinte au-delà de la fenêtre `Persistent`, timer
   désactivé/désinstallé après un redeploy, `DATABASE_URL` invalide côté
   `.env.production`…) :

   ```bash
   systemctl status seo-insight-gsc-partitions.timer
   journalctl -u seo-insight-gsc-partitions --since "-60 days"
   ```

## Lien avec le timer GSC existant

`seo-insight-gsc-partitions.timer` est indépendant de
`seo-insight-gsc-worker.timer` (sync quotidienne des données GSC — voir
`serveur-prod.md` § Findings F1) : le worker écrit dans les tables, le
timer de partitions garantit qu'il existe toujours une partition pour
accueillir l'écriture. Les deux doivent être actifs en prod ; l'un sans
l'autre laisse la fenêtre P4 ouverte (worker actif + pas de provisioning
= fuite silencieuse vers `DEFAULT` dès l'horizon des 72 partitions
pré-créées atteint).

## Tests

```bash
DB_TESTS=1 DATABASE_URL=postgresql://<user>@localhost:5432/seo_insight_test \
  bun --env-file=.env.local --conditions=react-server test src/lib/db/gsc-partition-provisioning.test.ts
```

Couvre : création des partitions des N prochains mois pour les deux
tables (vérifié via `pg_inherits`) ; rejeu idempotent sans erreur ; une
ligne datée hors de tout horizon connu tombe dans `DEFAULT` et le canari
la détecte (le canari ne corrige rien tout seul — cf. § remédiation
ci-dessus).