Очистка данных после поспешного прототипа: удаление дубликатов и восстановление целостности

Что обычно ломается после поспешного прототипа

Поспешный прототип создают, чтобы доказать идею, а не чтобы защитить данные. Правила часто отсутствуют, поля переименовываются в процессе, и кто‑то в итоге "просто правит в базе", чтобы обойти блокер. Если приложение сгенерировано AI‑инструментом, видно быстрые паттерны копипаста: перекрывающиеся таблицы, ID в одном месте как текст, в другом как число, и логика, сохраняющая одну и ту же запись дважды.

Дубликаты проявляются банально и дорого: два аккаунта пользователя с одним и тем же email, но разным регистром, несколько «активных» подписок у одного клиента или тот же заказ, сохранённый при клике и снова при обновлении страницы. Иногда это не точные совпадения: «Acme, Inc» vs «ACME Inc.», либо два контакта с одним телефоном.

Нарушенные связи возникают так же часто. Вы найдёте заказы, указывающие на user_id, которого больше нет, комментарии, привязанные к удалённой записи, или строки, указывающие на 0 или пустую строку, потому что приложение не валидировало ввод. Эти сиротские строки не всегда приводят к падению, но тихо ломаают сводки, дашборды и выгрузки.

Пользователи видят плохие данные как проблемы с входом, неверные итоги (двойной подсчёт заказов, завышенные остатки), пропавшую историю и экраны интерфейса, которые корректно работают только для части записей.

Начните чистку сейчас, если собираетесь брать деньги, вам нужны надёжные отчёты или саппорт тратит время на ручную правку записей. Ожидание усложняет работу, потому что каждая новая функция пишет поверх старого мусора.

Прежде чем трогать данные: снапшот, объем и откат

Очистка идёт не так, когда правят строки, не понимая, что важно и как откатиться. Отнеситесь к этому как к контролируемой операции, а не к быстрой правке.

Начните с определения зон базы, которые затрагивают реальных людей и деньги. Перечислите таблицы и пользовательские сценарии, которые они поддерживают: регистрации, оформление заказа, подписки, публикация контента. Если пропустите это, можно удалить дубликаты в одной таблице и случайно сломать поток, который всё ещё ожидает старую форму данных.

Часто достаточно простой заметки о зоне: users, сессии/авторизация, orders, payments, invoices, subscriptions и ключевые контентные таблицы (posts, comments, files). Запишите источник истины для каждого важного поля (например, доверяете ли вы больше ID платёжного провайдера, чем email).

Далее сделайте безопасный снапшот и опишите откат до первого UPDATE. Снапшот может быть бэкапом базы, точкой восстановления или экспортом затронутых таблиц. Откат — это не «мы будем аккуратны». Это записанный шаг: как восстановить, сколько это займёт и кто может это сделать.

По возможности прогоните очистку сначала на копии. Недавняя клон‑копия продакшна в staging идеальна — там есть реальные краевые случаи без риска для живых данных. Переносите в продакшен только когда скрипты повторяемы и результат предсказуем.

Наконец, задайте временное окно. Если возможно, заморозьте или заблокируйте рискованные записи (новые регистрации, создание заказов, фоновые синхи) на время очистки. Если нельзя, зафиксируйте контрольную метку времени и ограничьте изменения записями, созданными до этого момента.

Решите, что считать дубликатом (и что оставить)

Прежде чем что‑то удалять, конкретизируйте, что вы понимаете под «дубликатом» для вашего приложения. В прототипе две строки могут выглядеть одинаково для человека, но представлять разных реальных пользователей или событий. Ошибка с этим — потеря данных.

Начните с выбора источника истины для каждой сущности. Для пользователей это может быть запись, привязанная к подтверждённой авторизации, самая старая учётная запись или та, на которую ссылаются больше всего заказов. Для заказов — строка, которая действительно была оплачена, а не та, что создана при неудачной оплате.

Опишите простые правила слияния и придерживайтесь их. Решите, как решать конфликты и как обращаться с пустыми полями. Несколько правил покрывают большинство случаев:

• Если у одной записи есть значение, а у другой — null, сохраняйте значение.
• Если у обеих есть значения, отдавайте предпочтение верифицированной или недавно обновлённой записи.
• Не перезаписывайте важные поля аудита (created_at, signup_source и подобные).

Далее выберите канонический ключ для идентификации дубликатов. Часто это email, external_id из провайдера авторизации, номер заказа или составной ключ вроде (workspace_id, normalized_email). Будьте осторожны: в прототипах email часто хранят в разном регистре, с лишними пробелами или с plus‑адресацией.

Крайние случаи — где команды обычно спотыкаются. Общие ящики (team@), тестовые аккаунты, импортированные CSV с заглушечными email, и AI‑сгенерированные seed‑данные могут создавать легитимные повторы. Зафиксируйте исключения заранее и держите короткий список шаблонов, которые вы исключите из dedupe.

Методы обнаружения дубликатов

Начните с самого простого сигнала: две строки с одинаковым ключом. Даже если прототип не ввёл настоящий ключ, обычно есть близкое совпадение (email, external_id, order_number или естественная комбинация вроде user_id плюс день создания).

Быстрый первый проход — посчитать по подозреваемому ключу, отфильтровав те, которые появляются больше одного раза:

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

Далее ищите почти‑дубликаты. Нормализуйте поле в запросе, чтобы увидеть столкновения, которые иначе пропустите:

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

Проверьте дубликаты, созданные повторными попытками. Если приложение дважды вставляет одно и то же событие после таймаута, вы можете увидеть несколько строк с одним event_id, provider_charge_id, idempotency_key или (user_id + exact payload hash). Если таких полей нет, паттерн часто выглядит как идентичные строки, созданные в течение нескольких секунд.

Перед удалением или слиянием соберите небольшой образец для ревью. Возьмите топ‑20 групп дубликатов и проверьте их вручную, чтобы понять, что значит «оставить». Выгрузите полные наборы дубликатов для ревью, сравните временные метки, проверьте, указывают ли дочерние строки (заказы, сессии, счета) на одну из дубликатных записей, и запишите правило, которое будете применять. Сохраните запрос, который дал выборку, чтобы можно было прогнать его повторно после изменений.

Удаляем дубликаты, не потеряв важную историю

Если на запись ссылаются другие таблицы, удаление обычно создаёт новые проблемы. Безопаснее — слить дубликаты и перепривязать всё на одну запись‑хранителя, чтобы история оставалась связанной.

Выберите хранителя для каждой группы дубликатов, перенаправьте ссылки с проигравших ID на хранителя, затем архивируйте проигравшие или пометьте их неактивными. Это избегает поломки заказов, счетов, сообщений и прав доступа, которые всё ещё должны быть привязаны к человеку или аккаунту.

Когда записи расходятся по значениям, заранее решите, как разрешать конфликты. Действуйте предсказуемо: предпочитайте верифицированные записи, статусы paid/active над free/inactive, сохраняйте последние валидные значения, когда обе подходят, и никогда не перезаписывайте непустое поле пустым.

Ведите аудит, чтобы работа была обратимой. Небольшая таблица маппинга вроде dedupe_map(old_id, new_id, merged_at, reason) позволит через месяцы ответить «куда делся этот пользователь?» и значительно упростит дебаг.

Не забывайте про связанные данные: профили, настройки, членства, загруженные файлы и заметки часто живут в отдельных таблицах. Сначала перепривяжите дочерние строки и следите за уникальными ограничениями (например, две строки настроек, которые должны стать одной).

Запускайте dedupe пакетами (например, по 100–1000 групп). Мелкие пакеты уменьшают время блокировок, упрощают локализацию ошибок и дают шанс проверить результаты между прогонками.

Поиск и исправление сиротских строк

Сирота — строка, указывающая на то, чего нет. Чаще всего это дочерняя строка, чей parent_id не соответствует ни одной родительской записи. Такое часто случается после поспешного прототипа, особенно если приложение пропускало транзакции или удаляло записи без очистки связанных таблиц.

Начните с измерения проблемы. Простой LEFT JOIN покажет, сколько дочерних строк не могут найти родителя.

-- 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;

Если вы используете soft deletes, остерегайтесь “скрытых сирот”: родитель существует, но логически удалён (например, users.deleted_at IS NOT NULL). Решите, считать ли такие записи валидными.

Когда поймёте масштабы, выберите путь ремонта в зависимости от смысла данных:

• Воссоздать отсутствующего родителя, если он действительно не сохранился.
• Перепривязать потомка к реальному родителю (часто после dedupe).
• Архивировать или удалить сиротскую запись, если ей нельзя доверять (тестовые данные, частичные записи).
• Создать «Unknown» или «System» родителя только если логика продукта это корректно обрабатывает.

Что бы вы ни выбрали, фиксируйте все изменения, чтобы можно было безопасно прогнать операцию снова. Хороший паттерн: записывать изменения в staging‑таблицу (или помечать строки batch_id), обновлять маленькими батчами и логировать количества до и после.

Обеспечьте целостность ограничениями (и правильными индексами)

После очистки ограничения — это то, что не даст тому же мусору вернуться на следующей неделе. Добавляйте их только после того, как вы убрали дубликаты и починили сломанные связи, иначе вы заблокируете сами себя.

Начните с первичных ключей. Убедитесь, что у каждой таблицы есть PK, он действительно уникален и не будет переиспользован. Если в прототипе использовались значимые ID (например, хеш email), подумайте о переходе на стабильный суррогатный ключ (автоинкремент или UUID), а значимое значение храните как отдельное уникальное поле.

Затем закрепите идентификаторы, на которые опирается ваш бизнес. Для многих приложений это уникальное ограничение на email (часто без учёта регистра) и на любой external_id от платёжного провайдера, CRM или auth‑системы.

Внешние ключи предотвращают сироты. Выберите поведение при удалении, которое соответствует реальности: иногда нужно блокировать удаление, иногда — каскадировать, иногда — сохранять историю, устанавливая FK в null. Например: если в MVP позволяли удалить пользователя, но оставляли за ним заказы, можно запретить удаление при наличии заказов или сохранить заказы и анонимизировать пользователя.

CHECK‑ограничения ловят неправильные значения на раннем этапе. Держите их простыми: totals >= 0, status в разрешённом наборе, обязательные временные метки не null, quantity > 0.

Наконец, добавьте индексы, чтобы правила не замедляли всё. Уникальные ограничения и внешние ключи обычно требуют поддерживающих индексов; стоит сопоставить индексы с частыми запросами (например, orders по user_id).

Пошагово: строим повторяемые скрипты очистки

Исправления грязных данных идут не так, когда делаются вручную, одноразово или без ясного порядка. Отнеситесь к работе как к небольшому релизу: применяйте изменения в последовательности, которую можно повторить в staging и в продакшне.

Простая структура скрипта

Используйте упорядоченные миграции или скрипты, которые каждый раз проходят три фазы: подготовка, backfill, и наложение ограничений. Держите запросы‑детекторы только для чтения отдельно от записывающих шагов, чтобы вы могли просмотреть, что изменится, прежде чем что‑то обновлять.

Практичный поток, подходящий для большинства БД:

• 01-detect.sql (read-only): список дубликатов, сирот и плохих значений
• 02-setup.sql: добавить вспомогательные таблицы/столбцы (например, таблица маппинга old_id -> kept_id)
• 03-backfill.sql: перепривязать FK, слить строки, архивировать то, что удаляете
• 04-enforce.sql: добавить уникальные ограничения, внешние ключи и нужные индексы
• 05-validate.sql (read-only): проверки, доказывающие, что очистка сработала

Делайте записывающие скрипты идемпотентными (безопасными при повторном запуске). Используйте явные проверки вроде WHERE NOT EXISTS (...), проверяйте наличие столбцов/ограничений и предпочитайте upsert в таблицы маппинга. По возможности оборачивайте рискованные обновления в транзакцию и завершайте с ошибкой, если предусловия не соблюдены (например, если в группе дубликатов две «active» записи).

Что фиксировать при каждом прогоне

Логируйте изменения, чтобы можно было объяснить результат и заметить регрессии. Минимум — счётчики по шагам: сколько объединено, сколько перепривязано, сколько заархивировано и сколько осталось нерешённых.

Полезный паттерн — возвращать счётчики из каждого записывающего шага:

-- 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
);

Храните скрипты в системе контроля версий с коротким runbook’ом (входные данные, порядок, ожидаемое время, заметки по откату).

Валидируйте результат быстрыми проверками и повторяемыми тестами

Работа не сделана, пока вы не можете доказать, что база стала безопаснее. Сначала зафиксируйте несколько чисел «до» и «после», которые будете сравнивать при каждом прогоне.

Отслеживайте метрики, показывающие, уменьшилась ли проблема: общее число строк, уникальные бизнес‑ключи, группы дубликатов и количество сирот. Сохраняйте эти значения в небольшом текстовом файле или таблице, чтобы заметить регрессии.

Несколько запросов, которые удобно прогонять после фикса:

-- 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;

Числа полезны, но не заменяют реальное поведение. Прогони пару критичных сценариев в том виде, в каком приложение их использует: может ли пользователь войти, грузится ли заказ, совпадает ли итог счёта с позициями. Затем spot‑проверьте 10–20 реальных записей сквозь весь поток.

Чтобы это не вернулось, добавьте лёгкие автоматические проверки, которые можно запускать после каждого деплоя: скрипт, который прогоняет метрики и падает, если дубликаты или сироты выросли; проверку ограничений (unique, foreign keys) в CI или чек‑лист релиза; ежедневную задачу, сообщающую о новых нарушениях, пока они малы.

Если плохие данные всё ещё могут появляться (старые джобы импорта, слабая валидация API), планируйте постоянную очистку: заблокировать источник, затем держать проверки в рабочем состоянии.

Почему дубликаты и сироты возвращаются

Дубликаты и сироты редко бывают разовой ошибкой. Если приложение продолжает их создавать, очистка лишь выиграет время, пока вы не исправите корень — код и рабочие процессы.

Обычная причина — повторные попытки. Фонная джоба таймаутит, ретраится и вставляет тот же «создать заказ» или «отправить приглашение» снова, потому что нет idempotency key. Джоба сделала свою работу — просто дважды.

Асинхронные потоки тоже создают гонки. Например, при регистрации один запрос создаёт пользователя, другой создаёт профиль. Если профиль выполнится первым или вставка пользователя провалится, появится сиротский профиль. Отсутствие транзакций усугубляет ситуацию: половина операций может закоммититься.

Человеческие правки тоже важны. Быстрые админ‑правки или «просто пофиксить в базе» обходят проверки приложения, особенно если нет ограничений. Импорты даёт ещё один путь: CSV с email в разных регистрах, телефоны с лишними символами или внешние ID без консистентности — и та же людина приходит как несколько записей.

Ограждения, которые останавливают рецидив

Сделайте несколько шагов, которые блокируют плохие записи в источнике:

• Сделайте операции создания идемпотентными (храните ключ запроса/события и игнорируйте повторы).
• Оборачивайте многошаговые записи в транзакцию, чтобы всё либо прошло, либо откатилось.
• Нормализуйте идентификаторы перед записью (lowercase для email, форматирование телефонов, trim для ID).
• Зафиксируйте админ‑пути, чтобы они использовали ту же валидацию, что и приложение, а не прямые обновления.
• Добавьте ограничения + мониторинг, чтобы ошибки были видны и исправлялись быстро.

Если вы унаследовали AI‑сгенерированный MVP, такие проблемы типичны. Часто база — не корень; корень — свободные пути записи.

Пример: очистка пользователей и заказов после AI‑MVP

Типичный кейс — AI‑сгенерированный поток регистрации, который создаёт новую строку пользователя при каждой ретрай. Если человек нажал «Sign up» три раза, вы можете получить три аккаунта с одним email, но разными ID и частичными профилями.

Добавьте вторую проблему: после изменения схемы часть пользователей была удалена (или объединена) без обновления связанных таблиц. Заказы всё ещё указывают на старые user_id, и вы получаете сиротские заказы.

Практичный способ исправить без догадок:

• Выберите канонического пользователя для каждого email (например, с verified = true или с минимальным created_at).
• Создайте таблицу маппинга ID, записывающую old_user_id -> canonical_user_id.
• Обновите заказы, джойня через таблицу маппинга, чтобы каждый заказ указывал на канонический user_id.
• Только затем удаляйте (или архивируйте) лишние строки пользователей.
• Добавьте защиты, чтобы мусор не вернулся.

Ваша таблица маппинга может быть простой: (old_user_id, canonical_user_id), заполненная запросом, группирующим по email и выбирающим победителя. После обновления прогоните быстрые проверки: количество пользователей на email, количество заказов без пользователя и spot‑проверки по VIP‑клиентам.

Наконец закрепите целостность. Добавьте уникальное ограничение на email и внешние ключи orders.user_id → users.id. Если боитесь сломать записи, добавляйте ограничения после очистки и тестируйте на копии продакшна.

Короткий чеклист и следующие шаги

В конце важны две вещи: данные сейчас корректны, и вернуть ту же проблему стало сложно. Ведите заметки, чтобы кто‑то другой мог повторить работу позже.

Чеклист для самой очистки:

• Есть свежий снапшот, и вы знаете, как его восстановить.
• Правила дубликатов записаны (поля совпадения, критерии выбора, что сохраняется).
• Запросы детекции выполнены и сохранены с отметками времени и счётчиками.
• Изменения задокументированы (до/после ID, слияния, удаления и ручные решения).
• Скрипты очистки воспроизводимы на копии продакшна и дают те же результаты.

После того как данные выглядят хорошо, закрепите их, чтобы они оставались такими:

• Добавлены (или запланированы) внешние ключи и уникальные ограничения, обеспеченные нужными индексами.
• Проверки валидности пройдены (счётчики строк, референциальная целостность, уникальность ключей, критичные итоги).
• Откат протестирован целиком на staging‑копии.
• Мониторинг имеет ответственного (уведомления, еженедельный отчёт или условие релиза).

Если вы унаследовали сломанный AI‑сгенерированный прототип и нужен быстрый безопасный путь в продакшен, FixMyMess (fixmymess.ai) фокусируется на диагностике кодовой базы, исправлении логики и целостности данных и укреплении приложения, чтобы те же проблемы не повторялись.