Nettoyage des données après un prototype précipité : déduplication et réparation de l'intégrité

Ce qui casse généralement après un prototype précipité

Un prototype bâclé est conçu pour prouver une idée, pas pour protéger vos données. Les règles manquent souvent, des champs changent de nom en cours de développement, et quelqu'un finit par "corriger directement dans la base" pour passer un blocage. Si l'application a été générée par un outil IA, on voit aussi des motifs rapides de copier-coller : tables qui se chevauchent, IDs stockés en texte à un endroit et en nombre à un autre, et une logique qui enregistre deux fois le même enregistrement.

Les doublons apparaissent de manières ennuyeuses et coûteuses : deux comptes utilisateur avec le même email mais une casse différente, plusieurs abonnements "actifs" pour un seul client, ou la même commande enregistrée une fois au clic et encore au rafraîchissement de la page. Parfois ce ne sont pas des correspondances exactes. Vous pouvez avoir des quasi-doublons comme "Acme, Inc" vs "ACME Inc." ou deux contacts partageant un numéro de téléphone.

Les relations cassées sont tout aussi courantes. Vous trouverez des commandes pointant vers un user_id qui n'existe plus, des commentaires liés à un post supprimé, ou des lignes qui référencent 0 ou une chaîne vide parce que l'app n'a jamais validé les entrées. Ces lignes orphelines ne plantent pas toujours l'app, mais elles faussent silencieusement les totaux, tableaux de bord et exports.

Les utilisateurs perçoivent des données dégradées comme des problèmes de connexion, des totaux erronés (commandes comptées en double, inventaire gonflé), un historique manquant, et des écrans UI qui fonctionnent pour certains enregistrements mais pas pour d'autres.

Nettoyez maintenant si vous allez commencer à facturer, si vous avez besoin de reporting fiable, ou si le support passe du temps à corriger des enregistrements à la main. Attendre rend la tâche plus difficile car chaque nouvelle fonctionnalité écrit davantage de données sur le bazar.

Avant de toucher aux données : instantané, périmètre et rollback

Le nettoyage tourne mal quand on commence à modifier des lignes avant de savoir ce qui compte et comment annuler les changements. Traitez cela comme une opération contrôlée, pas comme un correctif rapide.

Commencez par délimiter les parties de la base qui affectent de vraies personnes et de l'argent. Listez les tables et les flux utilisateurs qu'elles soutiennent : inscriptions, checkout, abonnements, publication de contenu. Si vous sautez cette étape, vous pouvez dédupliquer une table et casser par inadvertance un flux en aval qui attend encore l'ancienne forme.

Une simple note de périmètre suffit souvent : users, sessions/tables d'auth, orders, payments, invoices, subscriptions et les tables de contenu principales (posts, comments, files). Écrivez aussi la source de vérité pour chaque champ clé (par exemple, si vous faites davantage confiance à un ID fournisseur de paiement qu'à une adresse email).

Ensuite, prenez un instantané sûr et définissez le rollback avant d'exécuter un seul UPDATE. L'instantané peut être une sauvegarde de base de données, une restauration point-in-time, ou une copie exportée des tables affectées. Le rollback n'est pas "on fera attention". C'est une étape écrite : comment vous restaurez, combien de temps ça prend, et qui peut le faire.

Quand c'est possible, exécutez le nettoyage sur une copie d'abord. Un clone récent de production en staging est idéal parce qu'il contient de vrais cas limites sans risquer les données live. Ne passez en production que lorsque vos scripts sont répétables et que les résultats sont prévisibles.

Enfin, définissez une fenêtre temporelle. Si possible, geler ou filtrer les écritures risquées (nouvelles inscriptions, création de commandes, jobs de synchronisation en arrière-plan) pendant le nettoyage. Si vous ne pouvez pas geler, enregistrez un timestamp de coupure et limite vos changements aux enregistrements créés avant ce moment.

Décidez ce qu'est un doublon (et ce que vous garderez)

Avant de supprimer quoi que ce soit, soyez précis sur ce que "doublon" signifie pour votre application. Dans un prototype, deux lignes peuvent sembler identiques pour un humain mais représenter des utilisateurs ou événements différents. Si vous vous trompez, le nettoyage devient perte de données.

Commencez par choisir une source de vérité pour chaque entité. Pour les utilisateurs, cela peut être l'enregistrement lié à un login vérifié, le compte le plus ancien, ou celui référencé par le plus grand nombre de commandes. Pour les commandes, cela peut être la ligne réellement payée, pas celle créée pendant un checkout échoué.

Rédigez des règles de fusion simples et tenez-vous-y. Décidez comment résoudre les conflits et comment traiter les champs vides. Quelques règles couvrent la plupart des cas :

• Si un enregistrement a une valeur et l'autre est null, conservez la valeur.
• Si les deux ont des valeurs, préférez celui vérifié ou le plus récemment mis à jour.
• N'écrasez pas les champs d'audit importants (created_at, signup_source, etc.).

Ensuite, choisissez la clé canonique utilisée pour identifier les doublons. Options communes : email, un external_id d'un fournisseur d'auth, numéro de téléphone, ou un composé comme (workspace_id, normalized_email). Attention : les prototypes stockent souvent les emails avec des casses différentes, des espaces en trop, ou des variations de plus-addressing.

Les cas limites sont là où les équipes se brûlent. Boîtes partagées (team@), comptes de test, CSV importés avec emails factices, et données générées par IA peuvent tous créer des répétitions valides. Capturez ces exceptions dès le départ et gardez une courte liste de motifs que vous exclurez de la déduplication.

Méthodes pour détecter les doublons

Commencez par le signal le plus simple : deux lignes partageant la même clé. Même si votre prototype n'a jamais défini de vraie clé, vous avez généralement quelque chose de proche (email, external_id, order_number, ou une combinaison naturelle comme user_id plus jour de création).

Un premier balayage rapide : un count par clé suspectée, filtré aux clés qui apparaissent plus d'une fois :

SELECT email, COUNT(*) AS c
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY c DESC;

Ensuite, recherchez les quasi-doublons. Normalisez le champ dans la requête pour voir des collisions que vous manqueriez autrement :

SELECT LOWER(TRIM(email)) AS email_norm, COUNT(*) AS c
FROM users
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

Vérifiez aussi les doublons créés par des retries. Si votre app insère le même événement deux fois après un timeout, vous pouvez voir plusieurs lignes avec le même event_id, provider_charge_id, idempotency_key, ou (user_id + exact payload hash). Si vous n'avez pas ces champs, le motif ressemble souvent à des lignes identiques créées à quelques secondes d'intervalle.

Avant de supprimer ou fusionner quoi que ce soit, constituez un petit échantillon de revue. Choisissez les 20 groupes de doublons les plus importants et inspectez-les manuellement pour savoir ce que "garder" doit signifier. Récupérez les ensembles complets de doublons pour revue, comparez les timestamps, vérifiez si des lignes enfants (orders, sessions, invoices) pointent vers l'un des doublons, et écrivez la règle que vous appliquerez. Sauvegardez la requête qui a produit votre échantillon pour pouvoir la relancer après les changements.

Dédupliquer sans perdre d'historique important

Si d'autres tables pointent vers un enregistrement, le supprimer est généralement la voie la plus rapide pour créer de nouveaux problèmes. Une approche plus sûre est de fusionner les doublons et de re-pointer tout vers une seule ligne gardienne pour que l'historique reste connecté.

Choisissez un gardien pour chaque groupe de doublons, déplacez les références des IDs perdants vers l'ID gardien, puis archivez les perdants ou marquez-les inactifs. Cela évite de casser les commandes, factures, messages et permissions qui doivent rester liés à la même personne ou compte.

Quand les doublons sont en conflit, décidez à l'avance comment résoudre ces conflits. Restez prévisible : préférez les enregistrements vérifiés aux non vérifiés, préférez le statut payant/actif au statut gratuit/inactif, conservez les valeurs valides les plus récentes quand les deux semblent raisonnables, et n'écrasez jamais des champs non vides par des champs vides.

Conservez une piste d'audit pour que le travail soit réversible. Une petite table de mapping comme dedupe_map(old_id, new_id, merged_at, reason) permet de répondre à "que s'est-il passé pour cet utilisateur ?" des mois plus tard, et accélère grandement le debugging.

N'oubliez pas les données autour de la ligne. Profils, paramètres, adhésions, fichiers uploadés et notes vivent souvent dans des tables séparées. Re-pointiez d'abord ces lignes enfants, et attention aux conflits d'unicité (par ex. deux lignes de paramètres qui doivent devenir une seule).

Exécutez la déduplication par lots (par exemple 100 à 1 000 groupes à la fois). Les lots plus petits réduisent le temps de verrouillage, facilitent l'isolation des échecs et vous donnent la possibilité d'inspecter les résultats entre les exécutions.

Trouver et réparer les lignes orphelines

Un orphelin est une ligne qui pointe vers quelque chose qui n'existe pas. Le plus souvent, c'est une ligne enfant dont le parent_id ne correspond à aucun parent existant. Cela arrive beaucoup après un prototype précipité, surtout quand l'app a sauté les transactions ou supprimé des enregistrements sans nettoyer les tables liées.

Commencez par mesurer le problème. Une simple vérification par left join vous indique combien de lignes enfants ne trouvent pas de parent.

-- Example: orders should reference users
SELECT COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

-- Inspect a sample to understand patterns
SELECT o.*
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL
LIMIT 50;

Si vous utilisez des soft deletes, méfiez-vous des "orphelins cachés" : le parent existe, mais il est logiquement supprimé (par exemple users.deleted_at IS NOT NULL). Décidez si ceux-ci doivent encore compter comme valides.

Une fois que vous savez à quoi vous avez affaire, choisissez une voie de réparation selon le sens des données :

• Recréer le parent manquant quand il a réellement échoué à se sauver.
• Réaffecter l'enfant à un parent réel (commun après une déduplication d'utilisateurs).
• Archiver ou supprimer l'enfant orphelin s'il n'est pas fiable (données de test, écritures partielles).
• Créer un parent "Inconnu" ou "Système" seulement si la logique produit peut le gérer proprement.

Quoi que vous choisissiez, enregistrez chaque changement pour pouvoir relancer en toute sécurité. Un bon schéma : écrire les changements dans une table de staging (ou marquer les lignes avec un batch ID), mettre à jour par petits lots, et logger les comptes avant et après.

Imposer l'intégrité avec des contraintes (et les bons index)

Après le nettoyage, les contraintes empêchent le même bazar de revenir la semaine suivante. Ajoutez-les seulement après avoir dédupliqué et réparé les relations cassées, sinon vous bloquerez votre propre travail.

Commencez par les clés primaires. Assurez-vous que chaque table en a une, qu'elle est vraiment unique et qu'elle ne sera jamais réutilisée. Si votre prototype utilisait des IDs significatifs (par ex. un user ID basé sur un hash d'email), envisagez de passer à une clé substitutive stable (auto-increment ou UUID) et traiter la valeur signficative comme un champ unique séparé.

Ensuite, verrouillez les identifiants sur lesquels votre activité se base. Pour beaucoup d'apps, cela signifie une contrainte d'unicité sur l'email (souvent insensible à la casse) et sur tout external_id provenant d'un fournisseur de paiement, CRM ou système d'auth.

Les clés étrangères empêchent les lignes orphelines. Choisissez le comportement de suppression qui correspond à la réalité : parfois vous voulez bloquer les suppressions, parfois vous voulez cascader, parfois conserver l'historique en mettant la clé étrangère à null. Exemple : si votre MVP permettait de supprimer un utilisateur mais laissait des commandes, vous pouvez empêcher la suppression quand des commandes existent, ou garder les commandes et anonymiser l'utilisateur.

Les check constraints attrapent les mauvaises valeurs tôt. Gardez-les simples : totals doit être > = 0, status doit appartenir à un ensemble autorisé, timestamps requis ne doivent pas être null, quantity doit être > 0.

Enfin, ajoutez les bons indexes pour que ces règles ne ralentissent pas tout. Les contraintes d'unicité et les clés étrangères ont généralement besoin d'indexes de support, et il vaut la peine d'ajuster les indexes sur vos recherches les plus fréquentes (par ex. orders par user_id).

Étape par étape : construire des scripts de nettoyage répétables

Les corrections de données bricolées tournent mal quand elles sont faites à la main, en one-off, ou sans ordre clair. Traitez le travail comme une petite release : appliquez les changements dans une séquence que vous pouvez répéter en staging et en production.

Une structure de script simple

Utilisez des migrations ordonnées ou des scripts qui suivent les mêmes trois phases à chaque exécution : setup, backfill, enforce. Séparez les requêtes de détection en lecture seule des étapes d'écriture pour pouvoir revoir ce qui va changer avant toute mise à jour.

Un flux pratique qui marche dans la plupart des bases de données :

• 01-detect.sql (read-only): lister doublons, orphelins et mauvaises valeurs
• 02-setup.sql: ajouter tables/colonnes d'aide (par ex. une table de mapping old_id -> kept_id)
• 03-backfill.sql: réassigner clés étrangères, fusionner les lignes, archiver ce que vous retirez
• 04-enforce.sql: ajouter contraintes d'unicité, clés étrangères et indexes nécessaires
• 05-validate.sql (read-only): vérifications qui prouvent que le nettoyage a fonctionné

Rendez les scripts d'écriture idempotents (sécurisés pour être relancés). Utilisez des gardes clairs comme WHERE NOT EXISTS (...), vérifiez l'existence de colonnes/contraintes, et préférez les upserts dans les tables de mapping. Encapsulez les mises à jour risquées dans une transaction quand c'est possible, et échouez vite si des préconditions attendues ne sont pas remplies (par ex. si un groupe de doublons a deux lignes "actives").

Ce qu'il faut enregistrer à chaque exécution

Logger ce qui a changé pour pouvoir expliquer les résultats et repérer les régressions plus tard. Au minimum, enregistrez des comptes par étape : lignes fusionnées, lignes réassignées, lignes archivées, et lignes restées non résolues.

Un schéma utile : renvoyer des comptes depuis chaque étape d'écriture :

-- Example: reassign child rows to the kept parent
UPDATE orders o
SET user_id = m.kept_user_id
FROM user_merge_map m
WHERE o.user_id = m.duplicate_user_id;

-- Example: archive duplicates (guarded)
INSERT INTO users_archived
SELECT u.*
FROM users u
JOIN user_merge_map m ON u.id = m.duplicate_user_id
WHERE NOT EXISTS (
  SELECT 1 FROM users_archived a WHERE a.id = u.id
);

Stockez les scripts dans le contrôle de version avec un runbook court (entrées, ordre, durée estimée, notes de rollback).

Valider les résultats avec des contrôles rapides et des tests répétables

Le travail n'est pas terminé tant que vous ne pouvez pas prouver que la base est plus sûre qu'avant. Commencez par noter quelques chiffres avant/après que vous pouvez comparer à chaque réexécution du nettoyage.

Suivez des métriques qui montrent si le problème a vraiment diminué : nombre total de lignes, clés métier distinctes, groupes de doublons, et comptes d'orphelins. Sauvegardez ces chiffres dans un petit fichier texte ou une table pour repérer les régressions plus tard.

Voici quelques requêtes que vous pouvez relancer après chaque correction :

-- Row counts (sanity)
SELECT COUNT(*) AS users_total FROM users;

-- Distinct keys (did dedupe work?)
SELECT COUNT(DISTINCT email) AS users_distinct_email FROM users;

-- Duplicate groups (should trend to 0)
SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

-- Orphans (child rows without a parent)
SELECT COUNT(*) AS orphan_orders
FROM orders o
LEFT JOIN users u ON u.id = o.user_id
WHERE u.id IS NULL;

Les chiffres aident, mais ne remplacent pas le comportement réel. Validez quelques flux critiques d'une manière qui correspond à l'usage de l'application : un utilisateur peut-il se connecter, une commande se charge-t-elle, le total d'une facture correspond-il toujours à ses lignes de commande. Ensuite, contrôlez manuellement 10 à 20 enregistrements réels de bout en bout.

Pour éviter que cela ne casse à nouveau, ajoutez des vérifications automatisées légères que vous pouvez exécuter après chaque déploiement : un script qui exécute les métriques et échoue si les doublons ou orphelins augmentent, une vérification des contraintes (unicité, clés étrangères) en CI ou une checklist de release, et un job quotidien qui rapporte les nouvelles violations avant qu'elles ne grossissent.

Si des mauvaises données peuvent encore entrer (anciens jobs d'import, validation API lâche), planifiez un nettoyage continu : bloquez la source, puis gardez les contrôles en place.

Pourquoi les doublons et orphelins reviennent

Les doublons et les lignes orphelines ne sont rarement un accident ponctuel. Si votre application continue à les générer, le nettoyage n'achète que du temps à moins de corriger les causes dans le code et le workflow.

Un déclencheur fréquent : les retries. Un job en arrière-plan timeoute, réessaie, et insère la même création de commande ou d'invitation parce qu'il n'a pas de clé d'idempotence. Le job a fait ce qu'on lui a demandé. Il l'a juste fait deux fois.

Les flux asynchrones peuvent aussi se faire concurrence. Par exemple, un flux d'inscription crée un utilisateur dans une requête et crée un profil dans une autre. Si la requête du profil s'exécute en premier ou si l'insertion de l'utilisateur échoue, vous obtenez un profil orphelin. L'absence de transactions aggrave le problème parce que le travail à moitié fait peut être committé.

Les actions humaines comptent aussi. Des corrections rapides par des admins ou des moments "corrigeons directement dans la DB" contournent souvent les vérifications normales, surtout s'il n'y a pas de contraintes pour empêcher les mauvaises écritures. Les imports ajoutent une autre voie : le CSV contient des emails avec des casses différentes, des numéros de téléphone avec des caractères en trop, ou des IDs externes incohérents, si bien qu'une même personne arrive en plusieurs enregistrements.

Garde-fous qui préviennent la récidive

Visez quelques changements qui bloquent les mauvaises données à la source :

• Rendre les opérations de création idempotentes (stocker une clé de requête ou d'événement et ignorer les répétitions).
• Envelopper les écritures en plusieurs étapes dans une transaction pour qu'elles réussissent ou échouent ensemble.
• Normaliser les identifiants avant l'écriture (lowercase des emails, formatage des téléphones, trimming des IDs).
• Restreindre les chemins d'administration pour qu'ils utilisent la même validation que l'app, pas des mises à jour directes.
• Ajouter des contraintes plus du monitoring pour que les échecs soient visibles et corrigés rapidement.

Si vous héritez d'un MVP construit par IA, ces problèmes sont courants. La base n'est souvent pas la cause racine. Ce sont les chemins d'écriture lâches.

Exemple : nettoyer users et orders après un MVP généré par IA

Un cas courant : un flux d'inscription généré par IA qui crée une nouvelle ligne utilisateur à chaque retry. Si la même personne appuie sur "Sign up" trois fois, vous pouvez vous retrouver avec trois comptes partageant un email mais ayant des IDs différents et des profils partiels.

Ajoutez un second problème : après un changement de schéma, certains utilisateurs ont été supprimés (ou fusionnés) sans mettre à jour les tables liées. Les commandes pointent encore vers l'ancien user_id, vous avez donc des commandes orphelines qui ne correspondent plus à un utilisateur réel.

Une manière pratique de corriger sans deviner :

• Choisissez un utilisateur canonique par email (par ex. celui avec verified = true, ou le created_at le plus ancien).
• Créez une table de mapping d'IDs qui enregistre old_user_id -> canonical_user_id.
• Mettez à jour les orders en joignant la table de mapping pour que chaque commande pointe vers le user_id canonique.
• Ensuite seulement, supprimez (ou archivez) les lignes utilisateur en trop.
• Ajoutez des protections pour que le problème ne revienne pas.

Votre table de mapping peut être aussi simple que (old_user_id, canonical_user_id), remplie par une requête qui groupe par email et choisit le gagnant. Après la mise à jour, exécutez des vérifications rapides : nombre d'utilisateurs par email, nombre de commandes sans utilisateur, et contrôlez manuellement quelques clients à forte valeur.

Enfin, verrouillez l'intégrité. Ajoutez une contrainte d'unicité sur l'email et une clé étrangère de orders.user_id vers users.id. Si vous craignez de casser des écritures, ajoutez-les après le nettoyage et testez d'abord sur une copie de production.

Checklist rapide et étapes suivantes

Deux choses comptent à la fin : les données actuelles sont correctes, et il est difficile que le même bazar revienne. Gardez des notes pendant le processus pour que quelqu'un d'autre puisse répéter le travail plus tard.

Checklist pour le nettoyage lui-même :

• Un instantané récent existe, et vous savez comment le restaurer.
• Les règles de doublons sont écrites (champs de matching, tie-breakers, ce que vous conservez).
• Les requêtes de détection ont été exécutées et sauvegardées avec timestamps et comptes.
• Les changements sont journalisés (IDs avant/après, fusions, suppressions, et décisions manuelles).
• Les scripts de nettoyage peuvent s'exécuter sur une copie de production et produire les mêmes résultats.

Après que les données semblent saines, verrouillez-les pour qu'elles restent saines :

• Les clés étrangères et contraintes d'unicité sont ajoutées (ou planifiées) et soutenues par les bons indexes.
• Les vérifications passent (comptes, intégrité référentielle, unicité des clés, totaux critiques).
• Le rollback est testé de bout en bout sur une copie staging.
• Le monitoring a un propriétaire clair (alerts, rapport hebdomadaire, ou gate de release).

Si vous héritez d'un prototype AI généré et avez besoin d'un chemin rapide et sûr vers la production, FixMyMess (fixmymess.ai) se concentre sur diagnostiquer la base de code, réparer la logique et l'intégrité des données, et durcir l'app pour que ces mêmes problèmes ne réapparaissent pas.