Use SQL constraints to prevent duplicate work

The decision

Checking whether a record exists and then inserting it is not a concurrency guarantee. Two requests can both see an empty result and both insert. If the business rule says there may be only one record for a key, encode that rule in a unique constraint and let the database arbitrate concurrent writers.

A worked example

A purchase can produce a redirect, a webhook, and a retry of that webhook. Each handler may ask to create a delivery job. Use the checkout session as a unique order key and a deterministic job key such as purchase:session_id. Both callers may run, but only one row represents the order and one row represents the intended delivery.

How to put it into practice

1. Identify the business key before choosing a generated primary key. A random UUID alone does not prevent duplicate purchases.
2. Use a transaction for related writes that must commit together. An order without its delivery job is an incomplete operation.
3. Choose conflict behavior deliberately. DO NOTHING preserves the first row; an update must not overwrite stronger state with an older event.
4. Test simultaneous requests, not only repeated requests that happen one after another.

CREATE TABLE delivery_jobs (
  id text PRIMARY KEY,
  state text NOT NULL DEFAULT 'pending'
);
INSERT INTO delivery_jobs(id)
VALUES ('purchase:example-session')
ON CONFLICT (id) DO NOTHING;

A failure to plan for

Try it on your project

Open two database clients and insert the same business key concurrently. Confirm that one durable record exists and that the losing operation produces a controlled outcome rather than an unhandled application error.

Primary reference

Read the official documentation →