At-least-once job queue

Make the claim atomic so two workers never grab the same job. The naive pattern — SELECT a pending row then UPDATE it to in-flight in two statements — is a race: between the SELECT and UPDATE another worker can SELECT the same row and both will UPDATE it, double-grabbing one job. Fix it by collapsing select and lock into one statement: `SELECT ... FOR UPDATE SKIP LOCKED` inside a transaction that also UPDATEs the row to in-flight and RETURNs it. The FOR UPDATE locks the row, SKIP LOCKED makes the second worker skip the locked row instead of blocking (a plain FOR UPDATE would serialize all workers on the hot queue table, adding lock-wait latency proportional to worker count). Prove it: run two concurrent workers polling the same queue with 100 pending jobs and assert they never receive the same job id, and that a skipped row is immediately available to the other poller — the claim throughput scales with worker count, not with lock waits.

Add a visibility timeout so a job whose worker dies before acking is not lost — it becomes claimable again after the timeout instead of stuck in 'claimed' forever. The timeout is the queue's most sensitive tuning knob and the senior mistake is setting it from intuition: too short and a slow-but-alive worker races its own task (the job is re-queued and processed twice concurrently, risking duplicate effects); too long and a real crash stalls the lane for minutes before any other worker can pick it up. The right value is slightly above p99 job duration, not p50 and not a conservative 10x. Implement it: claimed jobs carry `visible_at = now + visibility_timeout`; a sweeper or `WHERE visible_at < now()` claim predicate re-queues expired jobs. Prove it with a crash test: kill a worker mid-job (SIGKILL before ack) and show the job becomes claimable after exactly the visibility timeout, not lost and not immediately.

Let long jobs outlive the visibility timeout without being stolen. A job whose real duration exceeds the timeout would otherwise be re-queued while still running — two workers would then process the same job concurrently, turning at-least-once into at-least-twice with overlapping effects. The fix is a heartbeat that extends the lease: the worker periodically UPDATEs `visible_at = now + visibility_timeout` on a sub-timeout interval (e.g. every timeout/3) while the job is in-flight. The extension must be atomic and must not extend a job that was already re-queued (check `WHERE status='claimed' AND visible_at` still matches). Prove it: run a job that sleeps 3× the visibility timeout with heartbeats and show it is never re-queued; kill the heartbeat mid-job and show it is re-queued after one timeout. Document why extending the timeout itself (instead of heartbeating) is wrong — it would stall real crashes for the full long duration.

Make the consumer idempotent so a re-delivered job — whether from visibility timeout or heartbeat loss — produces exactly one effect. The only honest way is to record the idempotency key in the same transaction as the business effect: `INSERT dedup_key` + `UPDATE business_table` in one commit. If they are in separate commits, a crash between them either double-applies (effect committed, dedup key not — next delivery applies again) or replays forever (dedup key committed, effect not — next delivery is wrongly considered done). The job's natural idempotency key (e.g. `job_id` or `order_id`) is stored in a dedup table with a unique constraint; re-delivery with the same key hits the constraint and becomes a no-op. Prove it: process the same job twice with the same key and assert exactly one business effect; crash between effect and dedup insert (simulated by failing the transaction) and show neither is visible.

Add the production safety net: a poison job that always fails must not loop forever and occupy the queue head, blocking every other job behind it. After N failures (e.g. 5) with exponential backoff and jitter (`delay = base * 2^attempt + rand(0, jitter)`), the job moves to a dead-letter table instead of being re-queued — DLQ depth rising is the earliest signal that something upstream is broken or a payload is malformed, and you should alert on it, not just on latency. Make the queue observable: emit metrics for claim throughput, visibility re-queue rate, DLQ depth, and job duration p50/p99. Then run a chaos test: kill workers at every crash point (before claim, during job, before ack, during dedup insert) under concurrent load and assert zero lost jobs and zero unsafe duplicate effects. The chaos test is the proof that the atomicity boundaries from milestones 1 and 4 actually hold when the process dies at the worst instant.