01Why duplicates are expensive, not just untidy
In a healthcare lead-generation CRM I built for a US client, duplicate records were not a data-hygiene annoyance. They were a revenue and compliance problem. Two agents working the same customer meant paying twice for one lead, calling a person who had already enrolled, and occasionally selling a second policy that had to be unwound.
The team had a manual process for this: before creating a lead, check whether the customer exists. With two hundred agents under call-time pressure, that check happened when it was convenient. The fix was not training. It was making the system do the check.
02Matching strategy, in priority order
Duplicate detection is a precision-versus-recall problem, and the two failure modes have very different costs. A missed duplicate costs money. A false positive costs agent trust — and once agents learn to dismiss the warning reflexively, the feature is dead.
So I match in tiers, strongest signal first, and I tell the agent which tier fired.
- Normalised phone number. Strip formatting, country codes and leading zeros to a canonical form before comparing. This is the single highest-precision signal in practice.
- Normalised email. Lowercase, trim, and for common providers strip dot and plus-suffix variants.
- Government or policy identifier, where the domain has one. Exact match only.
- Fuzzy name plus date of birth. Names arrive misspelled constantly, so this needs trigram similarity rather than equality — and on its own it is a warning, never a certainty.
- Address similarity as a tie-breaker, useful for households but never sufficient alone.
Normalise before you compare. The overwhelming majority of "duplicates" a naive system misses are the same phone number written four different ways.
03Warn, do not block
My first instinct was to prevent creating a duplicate. That was wrong, and the reason is instructive: legitimate duplicates exist. Two family members share a phone number. A person genuinely re-enrols after a lapse. A previous record was created in error and the agent is correcting it.
A hard block turns those cases into support tickets and teaches agents to work around the system with fake data — which is far worse for data quality than the duplicates you were trying to prevent.
So the interface warns. It shows the matching record, which agent owns it, its current status, and when it was last touched. Then it offers three actions: open the existing record, create anyway with a reason, or cancel. The reason field matters — it turns overrides into an auditable trail instead of a silent bypass.
04Where to run the check
On the server, always. Enforcing it in the interface only means the API is unprotected, and in a CRM the API is exactly what bulk imports and integrations use — precisely the paths that generate duplicates fastest.
For responsiveness I run it twice: a debounced lookup as the agent types the phone number, so the warning appears before they have filled the rest of the form, and a definitive check on submit. The first is a convenience, the second is the guarantee.
On the database side this needs real indexes. Normalised phone and email get functional indexes on the normalised expression, not the raw column, or the index is never used. Fuzzy name matching needs a trigram index. Without both, duplicate checking becomes the slowest query in the system precisely as the table grows.
- Store the normalised form as a generated column and index that, rather than normalising at query time.
- Use a trigram index for fuzzy name comparison.
- Scope the check to the tenant. Cross-tenant duplicate warnings leak data between customers.
- Rate-limit the as-you-type endpoint. It is a customer-lookup API and should be treated as one.
05What it was worth
On that CRM the duplicate alert became the feature managers cited most in reviews, despite never appearing in the original brief. It removed a manual cross-check from every single lead creation, cut double-selling, and gave managers a record of when overrides happened and why.
If you are scoping a CRM, put this in the first release. It is cheap to build early and disproportionately expensive to retrofit once you have a large table of records nobody trusts.
Found this useful?
I write these from live project work. Follow along or get in touch if you want this kind of thinking applied to your product.
