Merge duplicate customer accounts without losing anything

Reported by: Noor Haddad (Customer Data, Marlow Supply) · Priority: Medium · Component: Customer database

Marketing's last campaign went out three times to some people, and support keeps finding the same customer with two or three accounts: one made at the shop, another when they signed up for the newsletter, another from the wholesale form. We found out the sign-up forms do not lower-case emails and some imports kept a space at the end, so "Bob@example.com" and "bob@example.com " became different people. I attached the list of duplicates we know about. Last year someone merged a few by hand and we lost order history, loyalty points and a delivery address. This time we must keep everything: all orders on the oldest account, points added up, one address per label. And it must not be possible to create the same customer twice again. The deploy tool retries a migration if it fails halfway.

Support note (Mateo): The rules for merging are written up in the repo README. Please write it as a migration; the database is at migration 0006.

Expected

Each customer appears once, with all their orders, their points added up and their addresses, and the database refuses a new account with the same email.

Actual

Nine accounts for five customers, three of them with several accounts.