How to Handle Duplicate Records in an AI-Built App
Detecting near-duplicate records without false positives, and a merge data model (tombstone, redirect, audit log) that keeps order history and foreign keys intact.
How to Handle Duplicate Records in an AI-Built App
How to handle duplicate records in an AI-built app is really two separate problems: finding the duplicates, and deciding what happens to the data attached to them once you merge two rows into one. Most builds only solve the first problem, a unique constraint or an exact-match check on signup, and then hit the second problem for real the first time a customer support agent needs to merge "John Smith" and "J. Smith" without losing either one's order history.
This covers both halves: how duplicates actually get in, a detection approach that catches near-matches without over-triggering on real distinct people, and the merge data model that keeps history intact. It assumes you already have a working app with a customer or user table to attach this to; if not, start with how to build an app with AI.
Where duplicates actually come from
Three sources account for almost all of them, and each needs a different fix rather than one blanket rule.
Re-signup. A user forgets they have an account and signs up again with a slightly different email. A normalized, lowercased, trimmed email uniqueness check catches most of this at creation time.
Bulk import. A spreadsheet a client has maintained by hand for years, which already has its own duplicates baked in before it reaches your app. Deduplicate on import, not after, using the technique below, and see how to add CSV import to an AI-built app for the import pipeline this slots into.
Manual entry. Two different staff members create a record for the same customer over the phone on the same day, spelled slightly differently, with no shared identifier to catch it at write time.
Detecting near-duplicates without a false-positive mess
Exact matching catches the first source and misses the other two, because the whole problem with the other two is that the records are not exact matches. You need fuzzy matching, and the trap with fuzzy matching is setting the threshold once and never revisiting it: too loose and you flag genuinely distinct customers as duplicates, too tight and it misses anything but a typo.
A workable approach that does not require a dedicated data-matching service for most app sizes:
Normalize first: lowercase, trim whitespace, strip punctuation from names and phone numbers, before comparing anything.
Compare on more than one field. Two records with similar names and the same phone number are a strong signal. Similar names alone are weak, since real people share names.
Use a string similarity function (trigram similarity is enough for most databases and needs no extra infrastructure) and treat anything above roughly 0.6 similarity on name plus a matching phone or email as a candidate, not an automatic merge.
Always surface candidates for a human to confirm before merging. Auto-merging on a fuzzy match is how two unrelated customers named Maria Garcia end up sharing an order history.
The merge data model
The part that most implementations skip is what happens to foreign keys pointing at the record you are about to delete. Orders, support tickets, and login history all reference a customer_id, and a naive merge that deletes the loser record breaks every one of those references or silently orphans them.
The pattern that avoids this: never hard-delete the losing record. Instead:
Step | What happens |
|---|---|
1. Pick the survivor | The record that stays canonical, usually the older or more complete one. |
2. Re-point foreign keys | Update every orders, tickets, and activity row referencing the loser to point at the survivor's id. |
3. Mark the loser merged | Set a merged_into_id column on the loser pointing at the survivor. Do not delete the row. |
4. Redirect lookups | Any query or API call that receives the loser's id should resolve it to the survivor via merged_into_id. |
Keeping the loser row as a tombstone rather than deleting it matters for two reasons. First, undo: someone will merge the wrong two records eventually, and reversing a merge is only possible if the losing row and its original data still exist. Second, external references: an old email thread, invoice, or support ticket exported months ago might still cite the old id, and a tombstone with a redirect keeps that link working instead of returning a 404.
If you can't undo a merge, you will eventually need to, and by then the data to undo it with is already gone.
Auditing the merge
Log who merged what, when, and which record survived, in a simple merge_log table: loser_id, survivor_id, merged_by, merged_at. This is a five-minute addition while you're building the merge flow and a genuinely painful one to reconstruct after the fact once a customer disputes losing their order history. The same audit-trail instinct applies broadly any time an action is destructive or hard to reverse, which is the same reasoning behind keeping an audit log on other sensitive actions in the app.
What this looks like from the data model, before you prompt for any of it
Sketch the merged_into_id column and the merge_log table before asking an AI tool to build the merge screen, the same discipline as any other feature: get the shape of the data right first, and the UI on top of it stays simple. The general version of that advice, applied to any feature rather than just merges, is in how to plan your data model before building an app with AI.
If duplicate detection is really a customer-list cleanup problem rather than an ongoing feature of the app itself, for example a one-time cleanup of a spreadsheet a small business has been keeping for years, the narrower job of cleaning that list once is covered directly in how to clean up a customer list with AI, which is the lighter-weight version of the same fuzzy-matching idea without building a merge feature into the app.
Frequently asked questions
Should duplicate detection run automatically on every new record, or only on demand?
Run a check at creation time using the fast, cheap exact-match rule, and run the fuzzy pass as a periodic job or an on-demand "find duplicates" admin action. Running full fuzzy matching against the entire table on every write does not scale and is not necessary, since near-duplicates from bulk import or manual entry surface in batches, not one at a time.
What similarity threshold should I actually use?
Start around 0.6 trigram similarity on name combined with a matching phone or email, review the first batch of candidates by hand, and adjust from there. The right threshold depends entirely on how common similar names are in your specific user base, so treat the starting number as a guess to calibrate, not a fixed rule.
Can I let users merge their own duplicate accounts?
Only with extra verification, since self-service merge is a way to combine two accounts' data, which is exactly the access-control mistake that turns a convenience feature into a way to see someone else's order history. Verify ownership of both accounts (a login to each, not just a claim) before allowing a self-service merge, or keep it staff-only.
Does this apply to companies as well as individual customers?
Yes, with one addition: company records also need to handle the case where two contacts belong to the same merged company, which means deduplicating at two levels, contact and account, rather than one. The merge mechanics (tombstone, redirect, audit log) are identical at both levels.
How did this land?
About the author
Developer Advocate
Steve builds something with Swarmz every week and writes up what worked, what broke, and what he'd do differently. Tutorials and hands-on guides are his lane.
