Send side effects through an outbox

How to do it

Insert the task into an outbox table inside the transaction. A worker claims due tasks with FOR UPDATE SKIP LOCKED, sends them, marks them sent, and retries failures with backoff. Then a rollback never sends a message, and a crash never loses one. A crash after sending but before marking can send twice, so give each task an ID the receiver uses to drop duplicates.

Example

CREATE TABLE outbox (
  id         bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  kind       text NOT NULL,
  payload    jsonb NOT NULL,
  attempts   int NOT NULL DEFAULT 0,
  send_after timestamptz NOT NULL DEFAULT now(),
  sent_at    timestamptz
);
-- Worker, inside its own short transaction
SELECT id, kind, payload FROM outbox
WHERE sent_at IS NULL AND send_after <= now()
ORDER BY id LIMIT 10
FOR UPDATE SKIP LOCKED;

How to check

Kill the process right after commit. On restart, the pending email is sent. Run two workers at once, and no task is sent by both.