# Audit complet Postgres + schéma Drizzle — 2026-07-07

**Méthode :** trois audits parallèles read-only (schéma & cohérence, performances, sécurité & tenancy), chacun sur le code complet (`src/lib/db/*`, 52 migrations, toutes les Server Actions/routes) + introspection de la base dev + référentiel `supabase-postgres-best-practices`. Aucune modification effectuée. Les IDs (S*, P*, B/C/M/A/D*) renvoient aux rapports d'origine.

**Contexte volumétrie :** base dev quasi vide (structure seule) ; échelle cible = milliers de sites, 100k+ keywords/org, ~1000 lignes GSC/site/jour. ⚠️ **La prod est encore sur `0c68b8b` : les migrations 0043→0051 (dont TOUT le partitionnement keywords + GSC et l'upsert audit) n'y ont jamais tourné** — plusieurs décisions ci-dessous sont donc encore réversibles à coût faible.

---

## 1. Verdict d'ensemble

Le socle est **plus sain que ce que tu crains** : tenancy applicative systématique et testée (guards fail-closed, aucune injection, tokens OAuth chiffrés au repos, clés API hashées, god-mode gaté serveur-side), FK presque toutes indexées, montants en cents, index unique partiel « une subscription active par org » exemplaire, sélections de colonnes explicites sur les chemins chauds. Les audits ELEVA-72/73/74 ont payé.

Les vrais problèmes sont concentrés sur **quatre axes** : (1) l'infrastructure de connexion (rôle superuser), (2) des invariants jamais poussés dans la base (0 CHECK, uniques manquants, un onDelete oublié qui casse la suppression de compte), (3) deux chemins chauds non bornés (réservoir keywords sans pagination serveur, chokepoint MCP à 8 requêtes/appel), (4) un partitionnement décidé avant le signal de volume, dont la complexité s'est propagée.

---

## 2. P0 — à corriger immédiatement (petits, graves)

| ID | Quoi | Où | Impact |
|---|---|---|---|
| **SEC-P1** | L'app se connecte en **superuser + BYPASSRLS**, sans TLS ni mot de passe (confirmé dev, **à vérifier en prod**) | `src/lib/db/client.ts`, `.env.local` | Toute faille applicative = accès total (lecture cross-tenant, `COPY TO PROGRAM` = exécution OS). Neutraliserait tout RLS futur. Remède : rôle applicatif dédié NOBYPASSRLS/DML-only, superuser réservé aux migrations, `sslmode=require` en distant. |
| **S1** | `sites.ownerUserId` **sans `onDelete`** alors que la suppression de compte self-service est activée | `domain-schema.ts:52-54`, `auth.ts:347` | Un propriétaire de site qui supprime son compte → violation FK → **500**. Idem `support_tickets.user_id/assigned_to`. Remède : `onDelete:"restrict"` explicite + transfert de propriété dans le flow. |
| **B1** | **IDOR cross-org** : `assignProspectsAction` insère des `linkSourceId` bruts sans vérifier l'org | `backlink/research-actions.ts:11-33` | L'org A peut s'attacher (et lire) les sources privées de l'org B. Remède : `assertLinkSourcesInActiveOrg` (filtre org/global), corrige aussi B2. |
| **P5** | `setKeywordTargetsBulkAction` : bulk insert **non chunké** (plafond 65 535 params) | `keywords/actions.ts:164-172` | Échec dur en prod sur sélection large × plusieurs pays. Remède : `chunk(ops, 500)` en transaction (3 lignes). |
| **SEC-P4** | `seed.ts` fait `db.delete(sites)` **sans `assertTestDb()`** | `db/seed.ts:87` | Lancé avec la mauvaise `DATABASE_URL` → wipe dev/prod. Remède : garde en tête + regex durcie `^seo_insight_test$`. |
| **D3** | Le logger drizzle dev logue **les tokens de session/vérification en clair** dans `~/.d3k/...` | `db/client.ts:13-17` | Détournement de session par simple lecture des logs. Remède : masquer les params des tables `session`/`verification`/`account`. |

## 3. Quick wins (≤ 1 jour chacun)

- **P3 — N+1 « dernier audit par site » ×2 surfaces** (`spec/audit/actions.ts:74-108` + `mcp/tools.ts:62-91`) : un SELECT par site avec le jsonb `results` complet juste pour compter pass/fail. Remède : `selectDistinctOn(siteId)` + compteurs `pass/fail/warn` dénormalisés écrits par `persistSiteAudit`.
- **P2 + M1 — Chokepoint MCP : 8 requêtes DB/appel + TOCTOU quota** (`mcp/auth.ts`, `mcp/quota.ts`, `billing/*`) : cache taggé 30-60 s sur limits/billing/addons (invalidé par webhooks Stripe) + compteur mensuel dénormalisé sur `organization` avec `UPDATE ... RETURNING` atomique → 8 → ~2 requêtes et le bypass concurrent disparaît.
- **Migration « invariants »** (une seule migration) : CHECK 0-100 sur `dr`/`score`/`authority`/`da` (**0 CHECK dans toute la base aujourd'hui**, S2) ; UNIQUE partiel sur `sites.gsc_property` (S3, double-mapping silencieux sinon) ; UNIQUE sur `mcp_api_keys.key_hash` (S7, colonne d'**authentification**) ; index `keyword_targets.site_id` + `content_keywords.site_id` (P11, seules FK non couvertes).
- **C17 — Token-broker GSC** (`api/worker/gsc-token/`) : secret global unique exposé sur Internet, sans scoping org ni log de succès. Remède : nginx location loopback-only (le worker est sur la même machine), journaliser les résolutions réussies, rotation de `WORKER_API_KEY`.
- **A2/P8 — Quotas transactionnels** : sièges à l'acceptation d'invitation et quotas d'import = check-then-write sans verrou → dépassement par concurrence. Remède : transaction + `FOR UPDATE` sur la ligne org (ou acter le best-effort en le documentant).
- **P14 — lz4** : étendre la compression TOAST à `site_audits.results` (le jsonb le plus réécrit — 156 items à chaque audit) et `keywords.gsc`.
- **Hygiène** : `chmod 600 .env*` (P3-sec) ; retirer `api/sentry-example-api` (C18) ; `assertMutableTarget` sur `setUserRoleAction` (B3) ; plafond de taille sur les 2 routes d'upload qui n'en ont pas (C12/C15) ; normalisation email dans le hook `user.create` pour couvrir OAuth (A1) ; déclarer `sameSite:"lax"` explicitement.

## 4. Chantiers (à planifier)

1. **Réservoir keywords — le chantier n°1 en perf (P1-perf)** : `getKeywordsForSite`/`getResearchKeywords` chargent **toute la table du site** (jsonb inclus) à chaque render ; tri/filtres/pagination 100 % client. À 100k keywords : latence, mémoire Node et navigateur linéaires sans plafond. Refonte coordonnée : keyset pagination sur `(site_id, term, id)` + filtres/facettes en SQL + **dénormalisation `volume_primary`/`kd_primary`** en colonnes indexées (aujourd'hui le tri vit dans un jsonb clé par `primarySource` variable — non indexable). UI et modèle doivent bouger ensemble.
2. **Partitions GSC — avant le déploiement de 0050 en prod (P4-perf)** : 72 partitions pré-créées jusqu'à 2028-12 puis plus rien — **aucun job ne crée les suivantes** ; en 2029-01 tout part silencieusement dans la partition DEFAULT (pruning perdu, rattrapage sous verrou exclusif). Remède : job mensuel de provisioning (pg_partman ou cron applicatif) + canari `count(*)>0` sur les DEFAULT. À câbler dans le runbook de déploiement de 0050.
3. **Rôle DB puis RLS (SEC-P2)** : une fois le rôle déprivilégié (P0), ajouter RLS `USING (org_id = current_setting('app.current_org_id'))` en **defense-in-depth** (pas en mécanisme primaire) — pattern `(select current_setting(...))` pour le coût par requête, et dénormaliser `org_id` sur les tables qui ne l'ont que par join (`backlinks` — précisément là où B1 s'est produit).
4. **Overview keywords en SQL (P7)** : `byIntent`/`byTheme`/`kdDistribution` calculés en JS après SELECT complet → `GROUP BY` / `unnest` / `width_bucket`.
5. **Worker Rust GSC (P10)** : 500 statements single-row par transaction → multi-VALUES (`QueryBuilder::push_values`), aligné sur le pattern TS de `sync.ts`.
6. **Budget connexions (P6)** : 3 pools indépendants (Next 10, analytics 10, worker 3) non coordonnés — fixer les `max` explicites documentés, pgbouncer + `prepare:false` avant toute réplication horizontale.

## 5. Décision stratégique — partitionnement de `keywords` (S6/P16)

C'est le point « mauvais choix précoce » le plus net, ET il est **encore réversible** : la prod n'a jamais exécuté 0045.

- **Coût déjà payé** : PK composite `(id, site_id)` imposée par Postgres, propagée dans 4 tables satellites (colonnes `site_id` dupliquées, migrations 0046/0047 de rattrapage), 17 contraintes physiques par FK composite, `id` seul plus unique-enforced globalement.
- **Gain actuel** : nul (dev : 4 keywords) ; gain futur réel seulement à plusieurs millions de lignes, et le hash × site_id colle bien à l'accès dominant (`WHERE site_id = ?`).
- **Options** : (a) **garder** — le coût de complexité est déjà payé et mergé, le design est techniquement correct (validation P16) ; (b) **dé-partitionner avant le premier déploiement prod** — migration inverse pendant que les volumes sont nuls, retour à une table simple + index `(site_id, …)`, et re-partitionner plus tard *si* un signal de volume réel apparaît (seuil à écrire dans un ADR, ex. > 10 M lignes ou > 10 Go).
- **Recommandation** : (b) si tu veux « propre dès le début » — la dénormalisation `volume_primary`/`kd_primary` du chantier réservoir est le bon moment pour tout faire d'un coup ; (a) si tu préfères ne pas toucher à ce qui est testé. À trancher **avant** de déployer 0043→0051 en prod.

## 6. Si on repartait de zéro (consolidé des trois audits)

1. **Rôle applicatif non-superuser dès le premier jour** + URL de migration distincte ; RLS defense-in-depth posée d'entrée (policies simples quand chaque table a son `org_id`).
2. **`uuid` natif + UUIDv7** au lieu de `text` + `randomUUID()` : index ~2× plus petits, localité d'insertion (S13). Applicable dès maintenant aux **nouvelles** tables.
3. **CHECK et UNIQUE métier dès la première migration** — un invariant documenté en commentaire est un invariant non appliqué (S2/S3/S7).
4. **Partitionner sur signal, pas par anticipation** — avec un ADR à seuil chiffré, et jamais sans le job de maintenance des partitions futures (S6/P4).
5. **FK réelles plutôt que conventions de strings** : `keywords.theme` sans FK (S4), ids `link_sources` namespacés `${orgId}__` parsés par convention (S9) — chaque convention de nommage est un invariant que la base ne défend pas.
6. **Une convention de schéma unique** (timestamps withTimezone partout, text vs varchar, relations() complètes ou absentes) — les 4 fichiers de schéma ont dérivé chacun dans leur coin (S5/S8/S10/S11).

## 7. Nettoyages cosmétiques notés

`Site.plan` (type TS) porte l'URL du site — collision de nom frontale avec le plan Stripe (S14) ; `health_check` en serial jamais nettoyée (S11) ; `varchar(256)` isolé sur `support_tickets.title` (S10) ; BRIN redondants sur les snapshots GSC (P13) ; `relations()` drizzle à compléter ou supprimer (S5).