Why your integration creates duplicates, and the patterns that stop it

The integration works in staging. It works for the first month in production. Then finance runs a reconciliation and finds the same purchase order in the ERP twice, four minutes apart, under two different internal ids. Nobody deployed anything. A webhook was retried after a timeout on a request that had already committed, and the second delivery created a second record. This is the most common way integrations fail once they are live, and it is never dramatic. No alert fires and no stack trace is written. There is only a quiet drift between two systems that somebody notices weeks later while closing the books. The fix is not more careful code. It is designing every write so that running it twice does the same thing as running it once.

At-least-once is the guarantee you actually have

Every transport you are likely to use delivers at least once. Webhook providers retry on any non-2xx response and on timeouts. Message brokers redeliver when a consumer dies before acknowledging. HTTP clients retry on connection resets, frequently by default, and so do the proxies in front of them. A timeout is not a failure, it is an absence of information: the request may have been rejected before it did anything, or it may have committed with the response lost on the way back. The sender cannot tell those apart, and losing a record is worse than writing one twice, so it retries. Vendors who advertise exactly-once mean deduplication inside their own system within a bounded window, and that guarantee stops at the edge of their infrastructure. The moment you write into an ERP or a CRM, exactly-once is something you implement.

Derive the external id from the source record

The highest-return change available to you is to stop inserting. Every write into a system of record should be an upsert keyed on an identifier you derive from the source, so repeating it converges instead of accumulating. A good derived id is stable across retries, unique per logical record, and computable from the payload alone without reading any state. Usually the source hands you one and all you do is namespace it, because order 1042 from two different systems is two different orders. When there is no id, a row in a delivered CSV or a line parsed out of a document, hash the fields that genuinely define identity. Be deliberate about that hash. If it includes a delivery timestamp or a message id, anything that changes per attempt, every retry produces a fresh id and you have built a duplicate generator with extra steps.

create table invoices (
  id           bigserial primary key,
  source       text        not null,
  external_id  text        not null,
  payload_hash text        not null,
  amount_cents bigint      not null,
  status       text        not null,
  updated_at   timestamptz not null default now(),
  constraint invoices_source_external_key unique (source, external_id)
);

-- The only write path the sync worker is allowed to use.
insert into invoices (source, external_id, payload_hash, amount_cents, status)
values ('erp', 'INV-88213', 'sha256:a91f7c', 412500, 'open')
on conflict (source, external_id) do update
   set amount_cents = excluded.amount_cents,
       status       = excluded.status,
       payload_hash = excluded.payload_hash,
       updated_at   = now()
 where invoices.payload_hash is distinct from excluded.payload_hash
returning id, (xmax = 0) as was_insert;

The constraint is doing the real work there. Application code that selects for an existing row and then inserts has a race between the check and the write, and under a retry storm that race stops being theoretical. A unique index makes the database the arbiter, so two workers processing the same redelivered event end up with one row and no need to coordinate. The returning clause tells you cheaply whether the statement inserted or updated, which is worth a counter: a rise in suppressed duplicates means something upstream is retrying harder than you assumed. The where clause matters too. Without it every run rewrites every row, updated_at stops meaning anything, and downstream change detection degrades into a full scan. When the target is a third-party API rather than a table you own, the same idea applies through a mapping table you control, so choose that store deliberately.

Idempotency keys, and how long to keep them

Against an API you do not control you cannot add a unique index, so the key has to travel with the request. Some APIs accept an idempotency key header and replay the original response instead of acting twice. Use them, but generate the key from the same deterministic id you derived earlier, never from a fresh random value. The bug we hit most often is a key generated inside the retry loop, so every attempt carries a different key and the server reads each one as a new intent. Generate it once, when the unit of work is created, and persist it with the job so a restart cannot lose it. Where the API offers no idempotency support, keep your own mapping table from source id to target id, written in the same transaction that marks the job complete.

Retention is a design decision, not an afterthought. The key store has to outlive the longest window in which the same work could plausibly be attempted again, and that window is almost never the retry policy. It is the backfill somebody runs three weeks later after fixing a mapping bug, or the replay of last quarter during an audit. A dedupe cache that expires after an hour is worse than none, because it gives everyone confidence right up until the replay that quietly recreates six thousand records. Our default is to keep the source-to-target mapping permanently: it is small, and reconciliation needs it anyway. Request-level keys can expire, but give them at least thirty days.

Timestamp watermarks lose records at the boundary

The standard incremental sync reads everything where updated_at is greater than the last run time, then stores the new maximum. It looks correct and it leaks records in at least three ways. Ties are the obvious one: a bulk import stamps four hundred rows with the same second, you page through part of them, and a strict comparison on the next run skips the rest permanently. Clock skew is the second, since the source database and your worker rarely agree to the millisecond. The third is the one that hurts, because it is invisible. A transaction that opens at 10:00:00 and commits at 10:00:09 writes rows stamped 10:00:00, but nobody can see them until the commit lands. A sync running at 10:00:05 finds nothing and moves its watermark past them. Those rows are now in the past forever.

Prefer a monotonic change sequence wherever the source offers one: a log sequence number, a rowversion column, an ordered change table, or a native change-data-capture feed. Ordering there comes from commit order rather than a wall clock, which removes the whole class of problem. When all you get is a timestamp, do two things. Page with a composite cursor of timestamp plus primary key so ties cannot strand a partial page, and rewind the persisted cursor by an overlap window on every run, wide enough to cover your longest source transaction. You will re-read the boundary constantly, and that is fine, because the earlier work made re-reads free. Once repeating a write costs nothing, you can afford to read too much and stop worrying about reading too little.

-- Page with (timestamp, id), never a bare timestamp.
select id, updated_at, payload
from   source_orders
where  (updated_at, id) > (:cursor_ts, :cursor_id)
order  by updated_at, id
limit  500;

-- Advance the cursor only after the whole batch has committed,
-- and rewind it by an overlap window so the next run re-reads
-- the boundary instead of trusting it:
--
--   next_cursor_ts = max(updated_at) - interval '15 minutes'
--   next_cursor_id = 0
--
-- Re-reading is safe only because every write is an upsert.

Partial failure inside a multi-step write

One logical operation often touches three systems: create the customer in the ERP, attach the signed contract in the document store, publish an event for downstream consumers. Step two fails. A try/catch around the whole thing cannot save you, because there is no rollback for an HTTP POST that already succeeded, and the compensating call you write to undo it is itself a network write that can fail. The pattern that holds is to make each step individually idempotent, then make the orchestration durable, so a resumed run skips the steps that already completed instead of repeating them blind. A workflow engine that persists step state gives you this directly. A transactional outbox gets you most of the way with one table. The local transaction is the only atomic thing you have here, so put the decision to act inside it and the acting outside it.

1. 1 Write the record and the intent together
   Insert the business row and an outbox row holding the target, payload, and derived key in one local transaction. If it rolls back, the intent goes with it, so a record never exists without its instruction to publish.
2. 2 Dispatch outside the transaction
   A separate worker claims pending rows with select for update skip locked, makes the call, then marks the row done. Skip locked lets several dispatchers run with no coordinator and no chance of two claiming the same row.
3. 3 Retry with backoff, and cap it
   Keep an attempt count and a next attempt time on the row, back off exponentially with jitter, and park it in a failed state after a fixed number of tries. Retrying is safe because every attempt carries the key from step one.
4. 4 Record the target id on the way back
   Write the id the target returned onto the outbox row and into the mapping table. Reconciliation can then say which record over there matches which one over here, without guessing from names, amounts, or dates.
5. 5 Prove the dispatcher is restartable
   Kill the process mid-batch in staging and confirm claimed rows return to the queue and get picked up cleanly, with nothing duplicated on the far side. Test it by actually killing it. Reasoning about it is how bugs ship.

Last write wins is a decision, not a default

Once a sync runs in both directions, both systems can edit the same field, and most integrations resolve it by whichever job ran last. That is not a policy, it is an accident of your cron schedule. Decide ownership per field rather than per record. The CRM owns the account name and the owner, the ERP owns the credit limit and payment terms, activity notes are append-only. Field-level ownership dissolves most conflicts, because only one side ever writes a given field. For genuinely shared fields, compare the source's own version or etag and reject stale writes, or route the collision to a review queue. Watch for the echo loop too: your write fires a webhook, which fires your write, indefinitely. Stamp outbound writes with an origin marker and ignore inbound changes whose payload hash matches what you just sent. Writing into an ERP sharpens all of this.

A dead-letter queue an operations person can clear

Some records will never succeed however often you retry: a required field is empty, a referenced account is missing, the target rejects the payload. They need somewhere to go, and the person who clears them should not have to be an engineer. A usable dead-letter queue shows the record in business terms, the invoice number and customer name, not a UUID and a stack trace. It gives the reason in one sentence translated out of the raw API error, plus when it first failed, how many attempts it has had, and who owns it. It offers one button to retry, safe to press repeatedly precisely because of everything above. Then put the count of items older than a day on a dashboard, because a dead-letter queue does not fail by filling up, it fails because nobody opens it. That trail also feeds the audit story you will be asked for later.