Idempotent ingestion is a three-part key

Every sync in the service ingested records from someone else’s API, and every one of them had the same job: run twice, insert once. The thing that made that safe was not clever. It was a key with the right number of parts. Get that key wrong in the worst way and the failure is not a duplicate row, it is one customer opening the app to find a stranger’s records sitting in their account.

One id is not enough to be unique#

The obvious idempotency key is the record id the provider gives you. Store it, and before inserting, check whether you have already seen it. If you have, skip.

The trouble is that a provider’s record id is only unique inside that provider, and often only inside one account. Two different providers can hand you the same id for completely unrelated things. And the same user across two connected accounts, or two different users entirely, can carry ids that overlap by pure coincidence.

Key on the id alone and you get two failures that are hard to unpick later. Records from different providers collide and one silently overwrites the other. Worse, records belonging to different users merge, because their ids happened to match, and now one tenant’s data has leaked into another’s history. That second one is not a bug, it is an incident.

So the key is deliberately three parts:

(userId, provider, externalId)

The user id keeps tenants apart, so nothing ever merges across users. The provider keeps namespaces apart, so the same external id from two sources stays two records. The external id is the provider’s own identifier inside that space. All three, or it is not unique.

Enforce it in two places, not one#

Knowing the key is half of it. The other half is enforcing it in both layers, because each layer covers a gap the other leaves open.

At the application layer, before inserting, I look up an existing row by that exact triple and skip if it is present. That handles the common case cleanly and lets me treat a re-run as a no-op instead of an error.

But an app-layer check has a race. Two concurrent syncs can both look, both find nothing, and both insert. So the same triple is a composite unique index in the database:

unique index on (userId, provider, externalId)

The index is the backstop. If two writers slip through the check at the same instant, the second insert fails at the constraint instead of creating a duplicate. The app check keeps the normal path fast and quiet, the index makes correctness a property of the schema rather than a property of good timing.

Idempotency for third-party ingestion is (tenant, provider, provider record id), enforced by both an app-layer check and a matching composite index. One handles the common case, the other handles the race.

The two-part defence matters because you will eventually run the app check and the index against each other, and you want them agreeing on the same three columns. A unique index on the wrong columns is worse than none, because it looks like a guarantee.

One poison record should not stall thousands#

The last piece is what happens when a single record in a batch is bad. Malformed payload, a field the provider swore was always present and this once was not, whatever.

The tempting shape is a transaction that aborts the whole batch on any failure. That feels safe and is the opposite. One poison record stalls thousands of good ones, and a sync that should have imported almost everything imports nothing and retries the same poison forever.

So per-record failures are collected, not thrown. Each record is processed on its own, good ones commit, bad ones get their error recorded and set aside, and the batch keeps moving:

for each record:
  try to ingest
  on failure, append to errors and continue
report processed count and the errors at the end

At the end you have thousands ingested and a short list of what did not make it, which you can inspect and replay. The batch degraded instead of collapsing.

The lesson#

Third-party ingestion has three rules that saved me every time. Make the idempotency key (tenant, provider, provider record id), because an external id alone collides across providers and merges across users. Enforce it in both the app layer and a matching composite index, so the common case is fast and the race is still caught. And accumulate per-item errors instead of failing the batch, so one bad record costs you one record and not the whole run.