DB-001 — Alignement migrations Laravel / schéma déployé / base de test

Scope

• Tables concernées (périmètre strict) : actor_subscriptions, ai_user_quotas.
• Objectif : rendre les migrations Laravel utilisées pour reconstruire une base de test fraîche fidèles au schéma réellement déployé, sans arbitrer de sémantique métier produit.
• Hors périmètre : billing, Stripe, changement de plan, refonte Monetization, DOC-001, arbitrage « quota par acteur vs par (user_id, acteur_id) », toute autre dette Security.
• Origine : audits SEC-P0-001B6/B6A, qui ont mis en évidence des diagnostics faussés par un schéma de test local incorrect.

1. Sources de schéma inspectées

Source	Date/origine	Type	Fiabilité	Tables concernées
Migrations Laravel (api/database/migrations)	historique du dépôt, non daté par source unique	Code exécutable localement	Faible pour actor_subscriptions/ai_user_quotas — prouvée incorrecte	Les deux
Base de test reconstruite (dmv_test, PostgreSQL)	reconstruite à la demande depuis les migrations Laravel	Base réelle, mais miroir direct des migrations	Reflète fidèlement les migrations — donc leurs défauts	Les deux
dmv-backoffice/supabase/sql/2026-04-10_subscriptions.sql	2026-04-10, script SQL ad-hoc appliqué directement à Supabase (hors dossier migrations/ formel)	Script de conception d'origine	Haute — nomme explicitement CONSTRAINT one_subscription_per_actor UNIQUE (acteur_id)	actor_subscriptions
dmv-backoffice/supabase/migrations/20260410153620_remote_schema.sql	2026-04-10, supabase db pull	Snapshot réel tiré de Supabase	Haute — confirme la contrainte et les colonnes de base à cette date	actor_subscriptions
dmv-backoffice/supabase/migrations/20260615090000_actor_subscription_overrides.sql	2026-06-15, migration Supabase formelle et versionnée	Migration réellement appliquée	Haute	actor_subscriptions (feature_overrides, feature_override_until)
Backups locaux /Users/sylvain/dev/dmv/backups/2026-08-* (schema.sql / full.backup)	2026-08-02 à 2026-08-13	Dumps automatiques quotidiens locaux	Haute pour le contenu, mais ce sont des instantanés locaux, pas une preuve de production live	Les deux
Production live (Supabase)	—	—	Non interrogée	—

ai_user_quotas — origine non retrouvée dans l'historique versionné. Recherche exhaustive (git log --all -S"ai_user_quotas") sur dmv-backoffice, dmv_backoffice, dmv-public, dmv-workspace : aucun résultat. Aucune migration Supabase formelle ni script sql/ ad-hoc ne crée cette table dans les dépôts inspectés. Elle existe uniquement dans les dumps locaux. Fait constaté et documenté tel quel, sans hypothèse inventée sur son origine.

2. Dump de référence retenu

Dernier instantané local disponible du schéma déployé : backup du 2026-08-13 (/Users/sylvain/dev/dmv/backups/2026-08-13_09-00-03/full.backup), extrait localement via pg_restore --schema-only, sans connexion réseau. Explicitement qualifié comme tel — pas « la production actuelle ».

3. Comparaison des deux derniers dumps

Comparaison intégrale (colonnes, types, contraintes, index) entre le backup du 2026-08-09 et celui du 2026-08-13 pour les deux tables : identiques, octet pour octet, sur les blocs CREATE TABLE et toutes les lignes de contraintes/index. Le schéma déployé de ces deux tables est donc stable sur la période observée.

4. Divergences complètes — actor_subscriptions

Colonne/contrainte	Migration Laravel (avant DB-001)	Base de test (avant DB-001)	Dump de référence (2026-08-13)
id	uuid primary, sans défaut	uuid, sans défaut	uuid DEFAULT gen_random_uuid() — divergence résiduelle, non corrigée (voir §11)
acteur_id	uuid NOT NULL	idem	idem
plan_id	varchar(50) NOT NULL	idem	uuid NOT NULL + FK → subscription_plans.id — divergence de TYPE, non corrigée (voir §11)
status	varchar(50) NOT NULL	idem	idem
stripe_subscription_id	text nullable	idem	idem
created_at	timestamp(0) nullable, défaut CURRENT_TIMESTAMP	idem	idem
periode	absente (migration 000065 mal ordonnée, jamais exécutée)	absente	text nullable
started_at	absente	absente	timestamp with time zone nullable
current_period_start	absente	absente	timestamp(0) without time zone nullable
current_period_end	absente	absente	timestamp with time zone nullable
promo_applied	absente	absente	boolean NOT NULL DEFAULT false
promo_end_at	absente	absente	timestamp with time zone nullable
admin_note	absente	absente	text nullable
granted_by	absente	absente	uuid nullable
updated_at	absente	absente	timestamp with time zone NOT NULL DEFAULT now()
feature_overrides	absente	absente	jsonb nullable
feature_override_until	absente	absente	timestamp with time zone nullable
Contrainte UNIQUE(acteur_id)	absente	absente	actor_subscriptions_acteur_id_key, nommée one_subscription_per_actor à l'origine (2026-04-10)
FK plan_id → subscription_plans(id)	absente	absente	présente — non ajoutée ici (dépend de la correction de type non traitée)
FK acteur_id → acteurs(id) ON DELETE CASCADE	absente	absente	présente — non ajoutée ici, non prouvée nécessaire par les audits B6/B6A, laissée en dette

5. Divergences complètes — ai_user_quotas

Élément	Migrations Laravel / base de test (avant DB-001)	Dump de référence (2026-08-13)
Colonnes	id, user_id, monthly_limit, monthly_used, bonus_credits, reset_at, onboarding_at, created_at, updated_at, acteur_id	Identiques — aucune divergence de colonnes
Contrainte	ai_user_quotas_user_id_unique = UNIQUE(user_id) global	ai_user_quotas_user_actor_uniq = UNIQUE(user_id, acteur_id) WHERE acteur_id IS NOT NULL + ai_user_quotas_user_null_actor_uniq = UNIQUE(user_id) WHERE acteur_id IS NULL
Preuve empirique (avant correction)	Un même user_id avec 2 acteur_id différents levait systématiquement SQLSTATE[23505]	Un même user_id peut légitimement posséder plusieurs lignes liées à des acteurs différents (observé dans les données réelles du dump : 1 utilisateur avec 6 lignes acteur + 1 ligne personnelle)

Seule la contrainte diverge — pas les colonnes.

6. Autres migrations mal ordonnées détectées

Recherche systématique (analyse du contenu — Schema::create vs Schema::table, pas seulement des noms de fichiers) sur les 107 migrations du dépôt. Cause racine générale : toute migration nommée 000NNN_... trie lexicalement avant toute migration nommée 0000_00_00_NNNNNN_..., car un chiffre (0-9, ASCII 48-57) est toujours inférieur à _ (ASCII 95) — indépendamment de la valeur numérique voulue. Deux conventions de nommage incompatibles coexistent dans le même dossier.

10 migrations correctives concernées, toutes guardées (hasTable/hasColumn) donc actuellement no-op silencieux, touchant 8 tables :

Migration corrective	S'exécute avant	Table
000057_add_ai_credits_to_subscription_plans.php	0000_00_00_000028_create_subscription_plans_table.php	subscription_plans
000059_add_bonus_to_push_usage.php	0000_00_00_000030_create_push_usage_table.php	push_usage
000064_add_missing_columns_to_boosts_table.php	0000_00_00_000032_create_boosts_table.php	boosts
000065_add_missing_columns_to_actor_subscriptions_table.php	0000_00_00_000014_create_actor_subscriptions_table.php	actor_subscriptions
000070_add_featured_until_to_acteurs.php	0000_00_00_000009_create_acteurs_table.php	acteurs
000071_add_featured_until_to_publications.php	0000_00_00_000018_create_publications_table.php	publications
000072_add_date_fin_to_publications.php	0000_00_00_000018_create_publications_table.php	publications
000073_add_mairie_actor_id_to_communes.php	0000_00_00_000005_create_communes_table.php	communes
000074_add_disabled_at_to_commune_elus.php	0000_00_00_000006_create_commune_elus_table.php	commune_elus
000075_add_seo_text_to_acteurs.php	0000_00_00_000009_create_acteurs_table.php	acteurs

Seules 000065 (actor_subscriptions) et son équivalent implicite pour ai_user_quotas (divergence de contrainte, pas d'ordonnancement) sont traitées dans DB-001, conformément au périmètre strict (« les tables concernées »). Les 9 autres cas sont documentés mais non corrigés — dette DB-001 restant ouverte (§11).

Constat annexe (non défectueux) : 4 tables (actor_activity_events, reward_rules, reward_grants, boost_usages) ont deux migrations Schema::create — la seconde (convention 0000_00_00_) est correctement guardée par hasTable() et no-op sans erreur. Pas un défaut, simple duplication historique inoffensive.

7. Cause racine

Deux causes distinctes, prouvées séparément :

1. actor_subscriptions : défaut d'ordonnancement de migration (§6) — la colonne cible existait déjà, seule l'exécution était cassée.
2. ai_user_quotas : la contrainte UNIQUE(user_id) a été déclarée directement dans la migration de création (000055), avant l'ajout ultérieur d'acteur_id (000058) — jamais mise à jour pour refléter le modèle réel. Pas un problème d'ordonnancement : un problème de contenu resté obsolète.

8. Stratégie retenue par table

actor_subscriptions : nouvelle migration corrective (Option 2 — append-only), datée 2026_08_17, garantie de s'exécuter après toutes les migrations existantes. 000065 n'est ni modifiée ni supprimée (fidélité de l'historique Git) ; elle reste un no-op documenté et expliqué. Ajout des colonnes manquantes (types exacts vérifiés colonne par colonne contre le dump) et de la contrainte UNIQUE(acteur_id), entièrement gardés et idempotents.

ai_user_quotas : nouvelle migration corrective du même type. Remplace ai_user_quotas_user_id_unique par les deux index UNIQUE partiels prouvés. Décision arbitrée explicitement avec l'utilisateur avant implémentation (question posée : fidélité de schéma pure vs statu quo documenté) — choix retenu : aligner, sans toucher AIQuotaService::getOrCreate() ni trancher la question métier ouverte.

9. Fichiers créés

• api/database/migrations/2026_08_17_000001_fix_actor_subscriptions_missing_columns_and_constraint.php
• api/database/migrations/2026_08_17_000002_fix_ai_user_quotas_business_key_constraint.php
• api/tests/Feature/Database/SchemaAlignmentTest.php (7 tests structurels)
• Ce rapport.

10. Fichiers modifiés

• api/tests/Feature/Monetization/MonetizationTest.php — 1 test marqué skipped avec justification complète (§ dette découverte, ci-dessous). Aucune autre ligne touchée.
• api/tests/Feature/Admin/AdminTest.php — 1 test corrigé (fixture créant 2 abonnements pour le même acteur → 2 acteurs distincts). Correction de fixture de test, aucune logique applicative touchée.

11. Migrations historiques modifiées ou non

Aucune migration historique n'a été modifiée. 000065_add_missing_columns_to_actor_subscriptions_table.php reste intacte (no-op documenté). Les 9 autres migrations mal ordonnées identifiées (§6) ne sont pas non plus modifiées.

12. Nouvelles migrations créées

2 (voir §9). Aucune n'est destinée à s'exécuter contre Supabase — les migrations Laravel ne sont, par convention du projet, jamais exécutées contre la production (php artisan migrate interdit sur supabase.co, cf. garde applicative existante).

13. Protections pour bases déjà correctes

Chaque opération est gardée individuellement : Schema::hasColumn() avant tout ADD COLUMN, requête pg_constraint/pg_indexes avant toute création/suppression de contrainte ou d'index. Une exécution contre une base déjà alignée sur le schéma cible ne produit aucune erreur et aucune modification (vérifié en relançant les migrations sur la base déjà migrée : aucune opération supplémentaire). Aucun DROP COLUMN, aucune suppression de données.

14. Résultat de reconstruction d'une base fraîche

Base PostgreSQL jetable créée de zéro, migrations complètes exécutées (107 historiques + 2 nouvelles) : succès intégral, aucune erreur.

15. Résultat de comparaison avec le schéma de référence

Comparaison automatisée (colonnes, types, nullabilité, défauts, index, contraintes) entre la base fraîche et une base restaurée directement depuis le dump de référence :

• ai_user_quotas : identique en tout point (colonnes et contraintes).
• actor_subscriptions : identique à l'exception de 2 divergences résiduelles, connues et non corrigées par choix explicite (§4, lignes id et plan_id) :
  • id sans défaut gen_random_uuid() — sans impact fonctionnel (l'application fournit toujours un UUID explicitement à l'insertion) ;
  • plan_id de type varchar(50) au lieu d'uuid + FK — divergence significative, documentée en dette (§16), non corrigée car sa correction toucherait la logique applicative (ActorSubscription::plan(), AdminSubscriptionWriteService), hors périmètre strict de DB-001.

16. Tests structurels ajoutés

7 tests dans SchemaAlignmentTest : colonnes attendues (actor_subscriptions, ai_user_quotas), contrainte UNIQUE(acteur_id) exacte, updated_at NOT NULL/DEFAULT now(), absence de UNIQUE(user_id) globale sur ai_user_quotas, présence des deux index partiels exacts (définition complète vérifiée, pas seulement l'existence du nom), et un test d'observation (non correctif) démontrant que deux utilisateurs peuvent désormais coexister pour le même acteur sans lever d'exception — sans se prononcer sur la légitimité métier de ce comportement.

17. Validations exécutées

• git diff --check : OK
• php -l sur les 5 fichiers touchés : OK
• Reconstruction complète d'une base jetable + php artisan migrate : succès
• Comparaison automatisée base fraîche / référence : conforme (§15)
• Tests structurels DB-001 : 7/7
• AITest : 57/57
• AICacheQuotaHardeningTest : 7/7
• MonetizationTest : 16/16 + 1 skip documenté
• AdminTest : 25/25
• RewardEngineTest / RewardReadTest : 13/13 + 9/9
• Suite complète (PHPRC=/private/tmp/dmv-php-test.ini php artisan test) : 603/604, 1 skip documenté, 0 échec
• vendor/bin/pint --test : passed

18. Non-régression B1–B6A

Aucune modification de AIController, IdentifyApp, ActorAccessService, AICacheService, AIGatewayService ou AIQuotaService::getOrCreate(). AITest (B1-B5) et AICacheQuotaHardeningTest (B6A) passent intégralement, inchangés. Le déblocage du chemin acteur réel obtenu en B6A (correction de resolvePlanLimit()) reste fonctionnel — confirmé par les mêmes tests de non-régression B4/B5 déjà en place.

19. Dettes restant ouvertes

1. 9 autres migrations mal ordonnées (§6), touchant subscription_plans, push_usage, boosts, acteurs (×2), publications (×2), communes, commune_elus — même cause racine, non corrigées (hors « tables concernées »). Recommandation : traiter dans un DB-001 de suivi dédié, table par table, avec le même niveau de preuve.

2. actor_subscriptions.plan_id — divergence confirmée, non corrigée dans DB-001. Code Laravel (ActorSubscription::plan(), tous les writers MonetizationWriteService/AdminSubscriptionWriteService) : varchar(50) contenant un slug ('free', 'starter', …). Schéma déployé observé (script SQL d'origine 2026-04-10, pull Supabase du même jour, dumps locaux 2026-08-09 et 2026-08-13, identiques) : uuid NOT NULL REFERENCES subscription_plans(id). Incohérence métier/applicative réelle — pas une divergence de test. Traitement explicitement reporté au chantier Monetization séparé (MON-SEC-001) ; non traité ici.

3. Dette Monetization/Stripe découverte — préexistante, non créée par DB-001. Deux méthodes de MonetizationWriteService sont incompatibles avec le schéma déployé observé :

   • onSubscriptionDeleted() → autoSubscribeFree() : après annulation d'un abonnement payant, autoSubscribeFree() tente d'INSERT une nouvelle ligne actor_subscriptions pour un acteur qui en possède déjà une (celle tout juste marquée canceled), ce qui viole actor_subscriptions_acteur_id_key = UNIQUE(acteur_id). Confirmé par le test désormais marqué skipped (tests/Feature/Monetization/MonetizationTest.php) et par exécution réelle du pipeline (handleWebhook()) contre une base de test isolée : SQLSTATE[23505], avec pour état persisté observé une ligne canceled orpheline et aucune ligne active restante pour l'acteur.
   • subscribe() : même défaut architectural — insère une nouvelle ligne sans mettre à jour/supprimer la ligne existante (l'acteur a toujours au moins une ligne free via ensureFreeSubscription()), ce qui violerait la même contrainte dès le tout premier abonnement payant réel.

   Précisions factuelles, pour éviter toute sur-interprétation :

   • Préexistant : UNIQUE(acteur_id) figure dans le schéma déployé depuis son origine (2026-04-10), stable sur les deux dumps locaux disponibles. DB-001 ne modifie jamais Supabase/production (les migrations Laravel ne s'y exécutent pas) — DB-001 ne crée pas ce défaut.
   • Rendu visible par DB-001 : avant DB-001, la contrainte était absente de la base de test locale, donc ces chemins n'étaient jamais réellement exercés par les tests. DB-001 aligne le schéma de test sur le schéma déployé observé, ce qui expose ce défaut pour la première fois en local.
   • Usage Stripe réel : dans les données observées (dumps locaux 2026-08-09/2026-08-13, 1339 lignes actor_subscriptions), stripe_subscription_id est NULL sur 100 % des lignes et subscription_plans.stripe_product_id est NULL sur les deux plans existants — rien n'indique que Stripe ait été réellement câblé ou utilisé à ce jour.
   • Production live non interrogée — conclusions basées exclusivement sur les dumps locaux disponibles (2026-08-09, 2026-08-13) et le script SQL/pull Supabase du 2026-04-10.
   • Ces deux défauts sont couplés à la dette n°2 (plan_id) : en production réelle, la divergence de type serait rencontrée en premier (échec invalid input syntax for type uuid avant même d'atteindre la contrainte UNIQUE). Un traitement futur devra couvrir les deux ensemble.

   Hors périmètre DB-001 — traitement prévu dans un chantier dédié séparé (MON-SEC-001), immédiatement après ce merge.

4. FK manquantes sur actor_subscriptions (acteur_id → acteurs, plan_id → subscription_plans, cette dernière bloquée par la dette n°2) — non ajoutées, non prouvées nécessaires par les audits B6/B6A.

5. Origine de ai_user_quotas non retrouvée dans l'historique versionné (§1) — à documenter si l'information est retrouvée par un autre moyen (accès Supabase direct, mémoire d'équipe).

20. Confirmation qu'aucun arbitrage métier quota n'a été pris

AIQuotaService::getOrCreate() n'a subi aucune modification — vérifié explicitement (diff nul sur ce fichier). La migration ai_user_quotas aligne uniquement la contrainte DB sur l'état déjà déployé ; elle ne décide pas si le modèle « quota par acteur » ou « quota par (user_id, acteur_id) » est le bon. Le test d'observation ajouté (§16) documente le nouvel état sans le juger. Décision d'implémenter cet alignement de contrainte validée explicitement par l'utilisateur avant tout code.

21. Confirmation qu'aucun frontend n'a été modifié

Confirmé — seuls des fichiers api/database/migrations/, api/tests/ et dmv-docs/docs/19-engineering/database/ ont été créés ou modifiés.

22. Confirmation qu'aucun commit n'a été créé

Confirmé — branche feature/db-001-schema-test-alignment, working tree avec modifications non indexées/non commitées, git log main..HEAD vide.