Dedupe Sume webhooks with Postgres ON CONFLICT DO NOTHING

Insert the delivery id into a table with a unique key using INSERT ... ON CONFLICT DO NOTHING RETURNING. If a row comes back, this is the first delivery and you enqueue the work. If no row comes back, it is a duplicate and you return 200. Sume tells receivers to use job_id as the idempotency key for job webhooks, and request_id for Format run webhooks.

Why this form

The PostgreSQL 18 INSERT page says ON CONFLICT DO NOTHING simply avoids inserting a row, and that only rows actually inserted or updated are returned. So RETURNING doubles as the first-time check, in one statement, with no select-then-insert race between two concurrent deliveries.

The id you key on matters. Sume's run webhooks page says the envelope request_id equals the run id and is stable across retries, while the receipt nested at payload.request_id is a different correlation id. Key on the envelope.

The SQL

Create the table once, then run the insert in the same transaction that enqueues your work, so a crash cannot leave an id recorded with no job queued.

create table if not exists webhook_seen (
  delivery_key text primary key,
  event        text not null,
  body         jsonb not null,
  received_at  timestamptz not null default now()
);

-- $1 = job_id or request_id, $2 = event name, $3 = raw JSON body
insert into webhook_seen (delivery_key, event, body)
values ($1, $2, $3::jsonb)
on conflict (delivery_key) do nothing
returning delivery_key;
-- one row  -> first delivery: enqueue the work
-- no rows  -> duplicate: return 200 and stop

Two cases to keep in mind

This does not replace polling. A delivery that never arrives leaves no row, so keep a periodic check of status_url for jobs you expected to finish.

• A redelivery after a terminal failure reuses the same key, so it will be skipped. If you want to reprocess on purpose, delete the row or add a version column.
• Verify the signature before the insert. An unsigned request should never write a row.
• Return 200 for the duplicate. Sume retries on any non-2xx, so an error for a duplicate only creates more traffic.

Alternatives and limits

If you do not use Postgres, the same pattern exists elsewhere: a unique key plus a conditional insert. The point is that the database enforces uniqueness, not your application code, so two deliveries that overlap in time cannot both win. A cache lookup before the insert is a fine optimisation but not a substitute.

Keep the stored body only as long as you need it. The row is useful for debugging a delivery and for replaying your own worker, but the job or run on Sume remains the authoritative record, and you can always read it again from result_url. Add an index on received_at if you plan to prune old rows on a schedule.