Why your automation creates duplicate records

Duplicates are rarely random. They almost always come from one of four causes, and each has a different fix. Guessing between them is why the problem keeps coming back.

Notes on the boring parts · 8 minute read

You built something sensible. A form fires a webhook, the workflow creates a record, everyone is happy for a fortnight. Then someone notices the same customer three times, or a report that counts 412 orders when the shop sold 400.

The instinct is to bolt a deduplication step on the end and move on. That usually works for a week and then fails differently, because where the duplicate came from determines which fix actually holds. Below are the four causes, in the order they show up in real systems, with the symptom that tells them apart.

1. The webhook was retried

This is the most common cause and the least suspected. Most systems that send webhooks will retry if they do not get a 2xx back quickly enough. Your workflow may have run perfectly and created the record; if the acknowledgement was slow or got lost, the sender assumes failure and sends the identical payload again.

From inside your workflow this is invisible. Both runs look like legitimate, successful, separate submissions.

How to tell

Two executions, seconds or minutes apart, with byte-identical payloads. If your platform shows execution history, they will sit next to each other looking like a user who double-clicked.

The fix

Deduplicate on an identifier that the sending system owns and that survives a retry — an order id, a submission id, a message id. Before creating, look it up. If it exists, stop.

2. The dedupe key is not stable

Sometimes there is a deduplication check and duplicates still appear. Usually the key changes between runs.

Anything the receiving system generates is a bad key: a row number, an auto-increment id, a hash that includes a timestamp, a "full name plus date" composite where the date is when you processed it rather than when the event happened.

How to tell

Duplicates that are not simultaneous. Hours or days apart, same underlying person or order. The check is running, it is just comparing something that was never going to match.

The fix

Ask one question of any candidate key: if this exact event arrived again tomorrow, would this value be identical? If the answer is no, it is not a key.

3. The values look different but are the same

Then there is the quiet one. Your key is stable and your check is correct, and [email protected] still gets created twice because one arrived as  [email protected]  with a trailing space from a copy-paste.

String comparison is exact. Humans are not.

The fix

Normalize before you compare, every time, in this order:

• Trim leading and trailing whitespace
• Lowercase anything case-insensitive in the real world — emails, usernames, country codes
• Strip formatting from numbers that people type freely: phone numbers, postcodes, tax ids
• Decide once whether +1 (954) 555-0100 and 9545550100 are the same thing, and apply that decision in one place

Normalizing costs one step. Not normalizing costs a support ticket every few days, forever.

4. Two runs raced each other

The rarest, and the one that survives every fix above. Two executions start close enough together that both check for an existing record, both correctly find nothing, and both create one. Neither did anything wrong. They were simply looking at the same moment in time.

How to tell

Duplicates that appear only under load, or only during a bulk import, and never when you test by hand. If it disappears the moment you slow things down, this is it.

The fix

Push uniqueness down to the layer that can actually enforce it. A unique constraint or unique index in the database. An upsert instead of a create. An API endpoint that accepts an idempotency key. A check-then-write in your workflow can always be raced; a constraint cannot.

If none of that is available, reduce concurrency to one for that path. Slower, but correct.

Telling them apart quickly

What to do about the ones already there

Fixing the cause does not clean up history, and cleaning up history without fixing the cause just delays the next occurrence. Do both, in that order — cause first, so your cleanup is not immediately undone.

When you do clean up, decide deliberately which copy survives. Usually it is the oldest record with the newest field values, because later submissions often contain corrections. Merging blindly to the newest row can discard a note somebody added to the original.