petitspas/database/docs/FK_POLICIES.md

FK_POLICIES.md

Politique des clés étrangères (ON DELETE / ON UPDATE) – Sprint 1

🎯 Objectif

Documenter, de façon unique et partagée, les règles de suppression/mise à jour appliquées aux clés étrangères de la base P’titsPas pour :

• préserver l’intégrité référentielle ;
• conserver l’historique utile (messages, événements…) ;
• respecter les exigences RGPD (suppression en cascade lorsque pertinent).

Par défaut, ON UPDATE = NO ACTION (UUID immuables).
Ce document couvre ON DELETE table par table.

---

🧭 Principes généraux

• CASCADE quand la donnée fille n’a pas de sens sans le parent
  (ex. dossiers d’un parent, avenants d’un contrat).
• SET NULL quand on veut préserver l’historique mais que le référent peut disparaître
  (ex. auteur d’un message supprimé, créateur d’un événement).
• RESTRICT/NO ACTION non utilisé ici pour éviter des blocages au nettoyage.

---

📚 Récapitulatif rapide (matrice)

Table (colonne FK) → Référence	ON DELETE	Raison
assistantes_maternelles(id_utilisateur) → utilisateurs(id)	CASCADE	Profil AM supprimé avec son compte
parents(id_utilisateur) → utilisateurs(id)	CASCADE	Extension parent supprimée avec son compte
parents(id_co_parent) → utilisateurs(id)	SET NULL	Conserver le parent principal si co-parent disparaît
enfants_parents(id_parent) → parents(id_utilisateur)	CASCADE	Nettoyage liaisons N:N
enfants_parents(id_enfant) → enfants(id)	CASCADE	Idem
dossiers(id_parent) → parents(id_utilisateur)	CASCADE	Dossier n’a pas de sens sans parent
dossiers(id_enfant) → enfants(id)	CASCADE	Dossier n’a pas de sens sans enfant
messages(id_dossier) → dossiers(id)	CASCADE	Messages détruits avec le dossier
messages(id_expediteur) → utilisateurs(id)	SET NULL	Garder l’historique des échanges
contrats(id_dossier) → dossiers(id)	CASCADE	1:1, contrat détruit si dossier supprimé
avenants_contrats(id_contrat) → contrats(id)	CASCADE	Avenants détruits avec le contrat
avenants_contrats(initie_par) → utilisateurs(id)	SET NULL	Historiser l’avenant sans bloquer
evenements(id_enfant) → enfants(id)	CASCADE	Événements n’ont plus de sens
evenements(id_am) → utilisateurs(id)	SET NULL	Garder la trace même si AM supprimée
evenements(id_parent) → parents(id_utilisateur)	SET NULL	Garder la trace si parent supprimé
evenements(cree_par) → utilisateurs(id)	SET NULL	Conserver l’historique de création
signalements_bugs(id_utilisateur) → utilisateurs(id)	SET NULL	Conserver le ticket même si compte supprimé
uploads(id_utilisateur) → utilisateurs(id)	SET NULL	Fichier reste référencé sans l’auteur
notifications(id_utilisateur) → utilisateurs(id)	CASCADE	Notifications propres à l’utilisateur
validations(id_utilisateur) → utilisateurs(id)	SET NULL	Garder l’historique de décision

ON UPDATE : NO ACTION partout (les UUID ne changent pas).

---

🔎 Détail par domaine

Utilisateurs & extensions

• assistantes_maternelles.id_utilisateur → CASCADE
• parents.id_utilisateur → CASCADE
• parents.id_co_parent → SET NULL (interdit d’être co-parent de soi-même via CHECK déjà posé)

Enfants & liaisons

• enfants_parents.id_parent → CASCADE
• enfants_parents.id_enfant → CASCADE

Dossiers & échanges

• dossiers.id_parent → CASCADE
• dossiers.id_enfant → CASCADE
• messages.id_dossier → CASCADE
• messages.id_expediteur → SET NULL

Contrats & avenants

• contrats.id_dossier → CASCADE (unique 1:1)
• avenants_contrats.id_contrat → CASCADE
• avenants_contrats.initie_par → SET NULL

Événements

• evenements.id_enfant → CASCADE
• evenements.id_am → SET NULL
• evenements.id_parent → SET NULL
• evenements.cree_par → SET NULL

Divers

• signalements_bugs.id_utilisateur → SET NULL
• uploads.id_utilisateur → SET NULL
• notifications.id_utilisateur → CASCADE
• validations.id_utilisateur → SET NULL

---

🧪 Scénarios de test (exemples)

1. Suppression d’un parent

   • Supprimer utilisateurs(id=parentX)
   • Attendu : parents (CASCADE), ses dossiers (CASCADE), messages liés aux dossiers (CASCADE) sont supprimés.

2. Suppression d’un co-parent

   • Supprimer utilisateurs(id=coParentY)
   • Attendu : parents.id_co_parent passe à NULL, aucun dossier supprimé.

3. Suppression d’un utilisateur auteur de messages

   • Supprimer utilisateurs(id=uZ)
   • Attendu : les lignes messages restent, id_expediteur devient NULL.

4. Suppression d’un enfant

   • Supprimer enfants(id=childA)
   • Attendu : enfants_parents (CASCADE), dossiers du childA (CASCADE), evenements du childA (CASCADE).

5. Suppression d’un utilisateur AM

   • Supprimer utilisateurs(id=amB)
   • Attendu : evenements.id_am devient NULL (historique conservé).

---

🛠 Migrations associées

Les ajustements sont implémentés dans :

• /bdd/migrations/04_fk_policies.sql
  – redéfinition des contraintes FK avec les bonnes politiques (DROP puis ADD CONSTRAINT), de façon idempotente.

---

📄 Notes & futures évolutions

• RGPD (Sprint 2) : si vous activez le soft delete (deleted_at) côté tables métier, ces politiques restent valides (les suppressions logiques se gèrent au niveau applicatif).
• Audit : si vous voulez tracer les suppressions, ajoutez des triggers d’audit (voir ticket Sprint 2 – Audit log).
• Performance : chaque FK doit être indexée côté enfant (cf. 02_indexes.sql).

---

✅ Checklist de conformité

• Toutes les FK listées existent dans la base
• Politique ON DELETE conforme au tableau ci-dessus
• ON UPDATE = NO ACTION partout
• Tests de suppression réalisés sur une base seedée
• 04_fk_policies.sql appliqué sans erreur

---

Mainteneur : Équipe BDD
Dernière mise à jour : Sprint 1 – Politique FK consolidée