# Analytics maison V1 — stockage MySQL dédié

Référence fonctionnelle : note V3 du 22 septembre 2026. Le stockage SQLite éditorial est sur NFS chez OVH ; les Analytics utilisent désormais exclusivement une autre base, MySQL/InnoDB. La migration générale du site vers MySQL reste hors périmètre.

## État et isolation

La collecte reste désactivée par défaut. Aucune base réelle n'a été installée, modifiée ou activée pour cette adaptation.

`AnalyticsConnection` crée paresseusement une connexion DBAL avec `ANALYTICS_DATABASE_URL`. Elle ne reçoit ni la connexion ORM ni `DATABASE_URL`. Collecte, lectures du dashboard, observations et purge utilisent toutes cette connexion. Une URL absente, non MySQL ou indisponible entraîne une erreur Analytics contrôlée ; aucun repli vers SQLite. Les pages publiques continuent à s'afficher.

Les trois anciennes classes ORM Analytics ont été retirées du mapping éditorial. Le filtre de schéma de la connexion principale ignore `analytics_*`, afin que les futurs diff Doctrine n'en proposent pas la suppression. La migration historique `migrations/Version20260922120000.php` est délibérément neutralisée dans les deux sens : elle ne crée plus de tables sur de nouvelles installations et ne supprime pas celles déjà présentes. Son numéro est conservé pour les installations qui l'ont déjà enregistrée. Le reliquat SQLite de préproduction n'est ni lu, ni alimenté, ni transféré, ni nettoyé par ce dispositif.

`ANALYTICS_SIGNING_SECRET` est vide par défaut. Chaque environnement utilisant la collecte doit définir un secret dédié propre, aléatoire et suffisamment long (par exemple 32 octets aléatoires encodés en hexadécimal), dans sa configuration non versionnée, identique pour HTTP et CLI. Sans ce secret, même avec `ANALYTICS_ENABLED=1`, la collecte reste non opérationnelle : aucun descripteur n'est émis et le Cockpit indique la collecte désactivée. Le secret sert exclusivement à signer et vérifier les descripteurs `analytics-page:v1` ; il n'est jamais affiché, journalisé ou transmis au navigateur. Il est indépendant de `APP_SECRET`, des identifiants SQL et de la Basic Auth. Ne pas modifier `APP_SECRET` pour activer le beacon.

## Base et paramètres OVH à préparer

Prévoir une base Analytics de préproduction et une autre de production, avec comptes et configuration distincts. Il faut obtenir, pour chaque environnement :

- nom exact de la base dédiée ;
- nom d'hôte SQL et port, accessibilité depuis les processus PHP web et CLI ;
- version précise du serveur : **MySQL 8.0 minimum retenu pour cette implémentation**, InnoDB disponible ; MariaDB n'est pas validé et est refusé ;
- utilisateur SQL, mot de passe et droits sur cette base uniquement ;
- modalités TLS/certificats imposées par l'offre OVH, sauvegardes et restauration.

La connexion reste extérieure à Doctrine ORM et à ses migrations. Dans les variables d'environnement de chaque installation, ou son fichier local non versionné :

```dotenv
ANALYTICS_DATABASE_URL="mysql://USER:PASSWORD@HOST:3306/DEDICATED_DATABASE?charset=utf8mb4"
ANALYTICS_ENABLED=0
ANALYTICS_SIGNING_SECRET=""
ANALYTICS_HOST="preproduction.example.org"
ANALYTICS_EXCLUDED_IPS=""
```

Ces valeurs sont des placeholders, pas des identifiants à utiliser. Encoder les caractères réservés des identifiants selon le format URL. Ne pas déposer l'URL dans un fichier versionné, une commande avec identifiants en argument, un rapport ou une sortie de diagnostic. `DATABASE_URL` reste la configuration SQLite éditoriale existante. Les configurations HTTP et CLI doivent être identiques pour une même installation.

`ANALYTICS_HOST` est un hôte exact, sans protocole ni chemin. Le contrôle exige également un `ANALYTICS_SIGNING_SECRET` non vide, `APP_ENV=prod` et un chemin de base vide ou `/index.php`. Une préproduction Symfony peut donc utiliser `APP_ENV=prod`, mais uniquement avec son hôte et sa base Analytics propres. Les préproductions en sous-répertoire restent exclues : vérifier l'implantation réelle avant un essai contrôlé. La production conserve `ANALYTICS_ENABLED=0` jusqu'à validation distincte.

Le timeout de connexion PDO est fixé à 2 secondes ; le timeout de lecture à 2 secondes si le pilote le propose. Les attentes InnoDB sont bornées à 1 seconde et les verrous de métadonnées à 2 secondes sur la seule session Analytics. Ce n'est pas une garantie absolue de durée totale, notamment face aux délais DNS/réseau : mesurer l'impact d'une panne réelle avant activation.

## Schéma et écritures

Le schéma se trouve dans `analytics-migrations/`, hors du répertoire des migrations éditoriales :

- `analytics_page` : clé stable `famille:id` ou clé de liste/accueil, famille et dernier titre public observé ;
- `analytics_daily_view` : `DATE`, clé de page, provenance fermée et compteur `BIGINT UNSIGNED` ; clé primaire `(day, page_key, source)` et index `(page_key, day)` ;
- `analytics_collection_day` : date et indicateurs d'observation active/désactivée ;
- `analytics_migration_versions` : registre technique Doctrine, indépendant du registre des migrations éditoriales.

Les trois tables de données sont InnoDB. Les clés techniques utilisent ASCII avec collation binaire ; les libellés utilisent utf8mb4. Une clé étrangère interne relie les vues au référentiel Analytics, jamais au site éditorial. Elle protège le référentiel lors d'une collecte simultanée à une purge.

Les écritures utilisent `INSERT ... ON DUPLICATE KEY UPDATE`, avec paramètres répétés pour les nouvelles valeurs : pas de fonction `VALUES()` dépréciée ni de dépendance aux alias introduits dans des versions plus récentes. Le compteur augmente directement dans MySQL. Les observations utilisent `GREATEST` pour conserver les deux états si un arrêt et une activation sont observés le même jour. Référentiel, incrément et observation sont validés dans une transaction courte.

En cas de verrou, deadlock ou indisponibilité, la transaction est annulée et la consultation peut être perdue ; aucune relance après un résultat de commit incertain, afin d'éviter un double comptage. La page publique reste affichée. Le logger reçoit seulement `analytics.collection_failed`, sans exception brute ni contexte personnel. Les erreurs de connexion ne chaînent pas l'exception du pilote, pour éviter d'exposer ses identifiants.

Références SQL : [incréments MySQL](https://dev.mysql.com/doc/refman/8.0/en/insert-on-duplicate.html), [verrous InnoDB](https://dev.mysql.com/doc/refman/8.0/en/innodb-locks-set.html). La compatibilité déclarée n'est pas une validation d'intégration sur le serveur OVH.

## Installation indépendante — après validation explicite

1. Créer la base dédiée chez OVH et attribuer ses droits ; cette commande ne crée pas la base elle-même.
2. Configurer l'URL de cette base, en maintenant `ANALYTICS_ENABLED=0`.
3. Vérifier le nom cible et les sauvegardes. Depuis l'installation concernée :

```sh
APP_ENV=prod php bin/console app:analytics:migrate
```

Sans `--execute`, la commande n'ouvre pas de connexion Analytics et n'écrit rien ; elle rappelle la procédure. Ce n'est pas un test de connexion.

4. Après validation de la cible :

```sh
APP_ENV=prod php bin/console app:analytics:migrate --execute --database=DEDICATED_DATABASE
```

Le nom confirmé doit correspondre exactement à la connexion. La commande utilise sa propre DependencyFactory Doctrine, uniquement `analytics-migrations/`, et son propre registre `analytics_migration_versions`. Ne pas utiliser `doctrine:migrations:migrate` ou `doctrine:schema:update` pour installer Analytics : ces commandes restent celles du site éditorial.

L'installation ne doit être lancée que par un opérateur à la fois. MySQL valide implicitement les DDL : une panne peut laisser un schéma partiel. La commande ne détruit rien pour se réparer. Inspecter les tables et le registre et décider de la reprise sur la base dédiée ; ne pas enregistrer une migration comme exécutée sans vérifier tout son schéma. Une installation réussie peut être relancée sans réexécuter la migration. Aucun rollback automatique de suppression n'est prévu.

**Droits SQL :** en exploitation, `SELECT`, `INSERT`, `UPDATE`, `DELETE` sur la base Analytics suffisent aux compteurs, lectures et purge, ainsi que la possibilité de régler les variables de session utilisées. Pour installer, ajouter `CREATE`, `ALTER`, `INDEX`, `REFERENCES` et les droits de lecture/écriture du registre Doctrine. Aucun droit global, `CREATE DATABASE`, `FILE` ou `SUPER` n'est requis par le code. Le compte ne doit pas accéder à une autre base métier. Retirer les droits DDL après installation si OVH permet cette séparation.

## Fonctionnement V1 conservé

Le GET seul ne compte aucune consultation. `AnalyticsSubscriber` valide les requêtes principales GET donnant du HTML 200 sur les routes autorisées : accueil, listes, fiches publiques et affichage du contact. Pour ces réponses, il ajoute le script first-party `public/js/analytics-view.js` et un descripteur de page signé. La politique de cache HTML reste inchangée.

Le descripteur contient uniquement le contexte `analytics-page:v1`, le nom de route autorisé, l'hôte Analytics configuré, l'ID entier pour une fiche et une signature HMAC-SHA256. La sérialisation signée a un ordre fixe ; la signature est vérifiée avec `hash_equals`, à partir du secret dédié `ANALYTICS_SIGNING_SECRET`. Il ne contient ni clé Analytics, famille, titre, provenance, timestamp ni identifiant de consultation. Il est réutilisable et non expirant : il peut être rejoué tant que la page reste publiquement admissible. Aucun état individuel anti-rejeu n'est conservé. Une rotation du secret invalide les descripteurs présents dans d'anciens HTML stockés.

Après `load`, le script tente une seule fois `POST /analytics/view`, via `fetch` en mode `same-origin`, avec `credentials: 'omit'`, sans retry ni dépendance supplémentaire. Sans JavaScript, en cas de blocage du script ou d'échec réseau, aucune vue n'est enregistrée. L'affichage ne dépend jamais de cette collecte. Ce mécanisme vise principalement les robots HTTP qui n'exécutent pas JavaScript ; un navigateur automatisé exécutant JavaScript peut encore compter des vues ou rejouer le descripteur.

Le POST exige l'hôte configuré, un `Origin` exact égal à l'origine de la requête, un corps JSON de 2 048 octets maximum et exactement deux champs : `page` et `referrer_origin` (512 octets maximum). La route, la structure du descripteur, son association avec l'ID et sa signature sont vérifiées. Une whitelist explicite associe chaque route à son type de contenu : offre publiée et cliquable, projet en ligne, ressource publiée avec détail accessible, pôle en ligne ou page éditoriale en ligne. Le contenu est relu au POST ; suppression ou dépublication empêchent le comptage. Clé, famille et titre courant sont reconstruits exclusivement côté serveur. Les listes filtrées gardent leur clé unique ; les changements de titre ne fractionnent pas l'historique. Les pages supprimées restent dans le référentiel tant qu'un compteur les référence.

Administration, connexion, confirmations, autres POST, HEAD, erreurs, redirections, 304, sous-requêtes, assets, XHR, robots déclarés et préchargements identifiables restent exclus de l'émission. Le POST applique aussi le filtre User-Agent, incluant `Barkrowler` et `meta-externalagent`, sans exclusion générique de Chrome, Avast ou CCleaner. Les headers `Sec-Fetch-*`, lorsqu'ils existent, doivent correspondre au fetch same-origin (`Dest: empty`, `Site: same-origin`, `Mode: same-origin`) ; leur absence n'empêche pas la collecte.

Le JavaScript transmet seulement l'origine HTTP(S) de `document.referrer`, sans chemin, paramètres, fragment ni identifiants ; une valeur vide, invalide ou non HTTP(S) devient vide. Le serveur classe immédiatement cette origine en `search`, `linkedin`, `other`, `direct_unknown` ou `internal`, suivant les domaines existants. Une origine reçue contenant un chemin ou une autre structure invalide devient `direct_unknown`. L'origine brute n'est ni persistée ni journalisée par notre code Analytics. Cette provenance déclarative peut être falsifiée. Le canal interne contribue aux totaux mais reste séparé des quatre autres canaux et de leur dénominateur. Aucun canal n'est propagé aux pages suivantes. Direct/inconnu ne distingue pas le direct véritable des référents absents.

Aucun cookie Analytics, stockage navigateur, session Analytics, IP ou hash d'IP stocké, User-Agent stocké, identifiant visiteur, événement individuel ou prestataire Analytics n'est ajouté. Seuls les agrégats journaliers et les métadonnées publiques existants sont alimentés. Les liens vers des ressources externes ne mesurent pas leur consultation. Le POST répond sans contenu, avec `private, no-store`, y compris après refus ou panne ; les erreurs techniques ne journalisent qu'un message générique sans contexte HTTP. Cela ne modifie pas les éventuels journaux d'accès de l'hébergement.

### Exclusion IP et diagnostic OVH

`ANALYTICS_EXCLUDED_IPS` est vide par défaut. Exemple documentaire : `192.0.2.10,198.51.100.0/24,2001:db8::1,2001:db8:abcd::/48`. IPv4, IPv6 et CIDR sont comparés uniquement à la volée avec `Request::getClientIp()` et `IpUtils::checkIp()`, au GET et au POST. Sans configuration, le comportement est normal ; les visites de l'agence ne sont alors pas exclues. Aucune lecture directe de `X-Forwarded-For` ni modification des trusted proxies n'est introduite.

Aucun trusted proxy explicite n'est présent dans la configuration versionnée examinée. La chaîne OVH reste à confirmer : si Symfony voit l'adresse d'un proxy, renseigner cette adresse pourrait exclure tous ses visiteurs. Avant de renseigner l'IP de l'agence, prévoir un diagnostic ponctuel dans un accès administrateur protégé, avec réponse privée `no-store` : afficher uniquement à l'administrateur `REMOTE_ADDR`, `getClientIp()` et des booléens de présence des headers forwarded, sans lire leur contenu, sans journal, export ni écriture Analytics. Faire un essai depuis l'agence et depuis une autre connexion, puis retirer le diagnostic. Aucun endpoint de diagnostic n'est installé par ce lot ; une commande CLI ne permettrait pas de vérifier la chaîne HTTP réelle.

### Contact

Le formulaire conserve honeypot, CSRF, validation et filtre de liens. Le délai minimal est de trois secondes après le premier affichage et après chaque tentative soumise, qu'elle aboutisse à un rejet, une validation invalide, une erreur d'envoi ou un succès. Un nouvel affichage GET ne remet pas ce délai à zéro. Le délai est commun aux onglets d'une même session Contact. Seul l'email destiné à Sensipode est envoyé, avec le `Reply-To` de l'utilisateur ; aucune confirmation automatique n'est envoyée à l'adresse saisie. Le transport Symfony configuré est utilisé, en conservant l'envoi synchrone existant ; le mailer global routé vers Messenger n'est pas utilisé pour ce formulaire.

Le Cockpit reste protégé par `ROLE_ADMIN`, dans le contrôleur et le menu, avec filtres GET signés EasyAdmin. Les bilans 30/90 jours et 12 mois se terminent hier en Europe/Paris ; la journée courante est exclue. Les comparaisons exigent une observation active chaque jour des deux périodes, sans arrêt connu, et un dénominateur précédent non nul. Le filtre de page ne change que sa série journalière. Les réponses sont privées et `no-store` ; aucun changement de bibliothèque graphique.

## Observation et purge

Après installation, prévoir sur chaque environnement concerné :

```sh
APP_ENV=prod php bin/console app:analytics:observe --host=preproduction.example.org
APP_ENV=prod php bin/console app:analytics:purge
```

L'observation enregistre l'état de configuration sans ajouter de vue ni activer la collecte. La planifier périodiquement, par exemple chaque heure si OVH le permet, et lors des changements d'état. La configuration CLI doit être celle du site web. Un jour actif sans vue affiche zéro ; sans observation, ou seulement avec un état désactivé, il affiche « — ». Un jour ayant les deux états est partiel. Une observation ponctuelle ne certifie pas une journée de fonctionnement continu : les pannes entre observations peuvent échapper au bilan.

La purge s'exécute séparément, idéalement chaque jour, même après arrêt de la collecte. Elle supprime les jours strictement antérieurs à aujourd'hui moins 24 mois, conserve le jour limite, traite les suppressions journalières par lots MySQL de 500 et retire les références devenues orphelines. Elle est relançable après interruption, ne s'exécute jamais dans une consultation et ne touche pas SQLite. Les sauvegardes ont une conservation distincte ; reprendre la purge après restauration d'une sauvegarde ancienne.

## Tests locaux et intégration MySQL

Les tests indépendants du moteur utilisent des doubles de repository pour la collecte, les périodes, le Cockpit et ses permissions. Le kernel HTTP utilise une base SQLite éditoriale temporaire uniquement pour les contenus et comptes du site : cela ne valide pas le SQL Analytics. Un test vérifie qu'un reliquat SQLite reste intact lorsque la connexion Analytics manque. Le cache prod et Twig sont validés dans ce kernel isolé.

```sh
SYMFONY_DEPRECATIONS_HELPER=weak php bin/phpunit --bootstrap vendor/autoload.php --do-not-cache-result tests/Analytics
```

Le mode `weak` conserve les dépréciations anciennes sans les confondre avec des assertions échouées. Sans `ANALYTICS_TEST_DATABASE_URL`, les tests d'intégration MySQL sont explicitement ignorés, jamais remplacés par SQLite.

Sur OVH mutualisé, le nom de base est imposé. La suite autorise désormais uniquement le nom exact `sensipodtest`, avec `ANALYTICS_TEST_ALLOW_RESET=1` obligatoire. Fournir son URL dans `ANALYTICS_TEST_DATABASE_URL`, hors fichiers versionnés. `sensipodpreprod` est réservée à la collecte de préproduction et est refusée par les tests destructifs, comme toute autre base, y compris les anciens noms `analytics_test_*`. **Cette base doit être jetable : les tables Analytics et leur registre y sont réinitialisés entre les tests. Ne pas utiliser la base de collecte de préproduction ou de production.** La garde contrôle la cible avant toute connexion, puis vérifie `SELECT DATABASE()` avant toute réinitialisation. La query de l’URL peut seulement contenir `charset=utf8mb4` : les surcharges `dbname`, pilote ou options de connexion sont refusées. Les workers concurrents héritent de la variable secrète validée, sans URL dans leurs arguments.

La garde seule peut être vérifiée sans connexion réseau : `php bin/phpunit --bootstrap vendor/autoload.php --do-not-cache-result tests/Analytics/AnalyticsMySqlSafetyTest.php`.

```sh
SYMFONY_DEPRECATIONS_HELPER=weak php bin/phpunit --bootstrap vendor/autoload.php --do-not-cache-result tests/Analytics/AnalyticsMySqlIntegrationTest.php
```

La suite préparée couvre migration MySQL, agrégation et renommage, 160 incréments issus de quatre processus, échec/rollback, attente sur verrou, lectures des bilans, observations sans vue et purge à sa frontière de conservation. Il reste à l'exécuter réellement ; son existence n'atteste pas son succès.

## Essai contrôlé de préproduction et passage en production

Après validation et hors de cette mission :

1. Confirmer MySQL 8.0+, InnoDB, pilote PDO, accès réseau/TLS, droits et timeout de panne. Exécuter la suite sur une base jetable distincte.
2. Installer le schéma de la base Analytics de préproduction, collecte toujours désactivée ; vérifier le registre et la relance de l'installateur.
3. Tester le Cockpit et ses droits, puis l'observation désactivée. Vérifier l'absence de nouvelles écritures Analytics dans SQLite.
4. Après autorisation d'essai, activer uniquement sur l'hôte de préproduction avec sa base dédiée. Contrôler quelques consultations connues, exclusions, provenances et rechargements ; confirmer les incréments et la cohérence des bilans.
5. Vérifier les connexions concurrentes réelles, les timeouts, les pannes réseau et l'intégrité après rollback. Contrôler que les autres hôtes ne comptent pas et que la production reste désactivée.
6. Vérifier dans le navigateur le dashboard et les en-têtes HTML Apache/OVH (`ExpiresDefault`, 304, éventuel CDN) ; mesurer les consultations qui atteignent Symfony. Ce point reste ouvert malgré le changement de base.
7. Tester les commandes planifiées, sauvegardes, restauration et purge ; rétablir l'état désactivé à la fin de l'essai et enregistrer cette observation.
8. Avant production : valider les résultats, la configuration et la base de production distinctes, les conditions juridiques du mécanisme réel, l'information de confidentialité et les durées des logs techniques OVH/PHP/Symfony. Installer et activer uniquement après décision séparée.

La politique de confidentialité et les contenus éditoriaux ne sont pas modifiés. Transitions, clics, géographie, visiteurs uniques, parcours et heatmaps restent hors périmètre.