Skip to content
Back to Articles

Articles

How to prevent duplicate records in automated workflows

Written by Roberto Postolovski, Founder & Tech LeadUpdated

Why duplicates happen

Duplicates are not a rare bug. They come from the normal behaviour of the systems you connect: webhooks are redelivered after network errors, notifications fire twice, scripts time out and retry, and people forward the same email to a shared mailbox.

Aircall, for example, warns that it can deliver the same webhook event more than once, and Microsoft documents that the Outlook connector's trigger can run twice for a single email. An automation that creates a record for every event will create duplicates sooner or later.

Choose a stable key for every item

The key must stay the same no matter how many times the item arrives:

  • Emails: the message ID, not the subject or arrival time
  • Calls: the call ID from the phone system
  • Invoices: vendor plus invoice number, because the same PDF can arrive by email and by upload
  • Orders: the customer's order number plus the customer
  • Files: a hash of the file content, so renamed copies are recognised

Make every write safe to repeat

  1. 1.Claim the key first

    Before processing, insert the key into a table with a unique constraint. If the insert fails, the item is already being handled or done.

  2. 2.Upsert, don't insert

    Write to the target system as create-or-update keyed on the stable key, so a second write updates instead of duplicating.

  3. 3.Use idempotency keys where the API offers them

    Some APIs accept an idempotency key on create requests. Stripe's API, for example, recognises retries of the same request by the key and returns the saved result instead of running the operation again.

  4. 4.Record the outcome

    Store which record was created for which key. A retry can then link to the existing record instead of guessing.

Common mistakes

  • Checking for an existing record and then inserting, in two separate steps: two parallel retries both see nothing and both insert
  • Using timestamps or file names as keys
  • Matching on fuzzy fields like company names without a review step
  • Cleaning duplicates afterwards instead of preventing them

When a person should decide

Some duplicates are only probable: the same amount from the same vendor a week apart, or two contacts with similar names. Flag these for review instead of merging automatically. Merging the wrong records is harder to undo than leaving a possible duplicate for a person to check.

Sources

Related

Have a process like this?

Tell us how it runs today. We'll tell you whether it can be automated, what it would take, and whether the numbers make sense.