MySQL: INSERT IGNORE or ON DUPLICATE KEY UPDATE for Sume job_id

Use INSERT ... ON DUPLICATE KEY UPDATE deliveries = deliveries + 1 on a table whose primary key is job_id. MySQL reports affectedRows of 1 when the row is new and 2 when an existing row was updated, so the same statement dedupes the webhook and counts the retries. Start follow-up work only when affectedRows is 1.

INSERT IGNORE also dedupes, but it downgrades other errors to warnings as well, such as a value that does not fit its column. For a table that records paid jobs you want those errors to be loud. Sume's webhook docs make job_id the idempotency key, because delivery is retried up to 10 times and Redeliver can send a terminal event again.

Schema and statement

The primary key is the whole dedupe mechanism. Everything else is bookkeeping, and state lets a sweeper find work that was claimed and never finished.

CREATE TABLE sume_jobs (
  job_id VARCHAR(64) NOT NULL PRIMARY KEY,
  event VARCHAR(32) NOT NULL,
  status VARCHAR(16) NOT NULL,
  payload JSON NOT NULL,
  state VARCHAR(16) NOT NULL DEFAULT 'claimed',
  deliveries INT NOT NULL DEFAULT 1,
  first_seen TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);
INSERT INTO sume_jobs (job_id, event, status, payload)
VALUES ('job_demo', 'job.completed', 'OK', '{"event":"job.completed"}')
ON DUPLICATE KEY UPDATE deliveries = deliveries + 1;
-- first run: 1 row affected; run it again: 2 rows affected
SELECT job_id, state, deliveries FROM sume_jobs WHERE job_id = 'job_demo';
DELETE FROM sume_jobs WHERE job_id = 'job_demo';

INSERT IGNORE against ON DUPLICATE KEY UPDATE

Compare the two statements on the three cases a receiver sees.

Wire it into the receiver

In a handler, verify the signature before the statement and read the raw body for the payload column. A failed write must return a 5xx, so that Sume retries: its docs say it retries network errors and non-2xx responses until the attempts run out.

Failed and canceled jobs use the same envelope with status: "ERROR" and an error object, so they land in the same table. A row with status = 'ERROR' and state = 'claimed' is the one a retry or alert job should look at first.

Caveats

• Keep a deliveries value above 1 as a signal. A retry means your endpoint was slow or failing, and Sume allows 10 seconds per attempt.
• The statement dedupes events, not work. If the follow-up fails after the insert, the retry will not run it again, so keep state and sweep it.
• Keep polling job status for jobs that never produce a row. Delivery is an optimization, not the only recovery path.