Claiming work exactly once, with one UPDATE

Once you decide that a Postgres table is the delivery guarantee, you inherit a problem the message broker used to handle: two workers can reach for the same piece of work at the same moment, and exactly one of them must get it.

For DevPilot this is not hypothetical. A developer with a laptop and a desktop runs an orchestrator on both. Both poll. Both see the same unclaimed row. If both win, the same Linear issue gets dispatched twice, two agents start editing the same repository, and the resulting pull request is somebody's afternoon.

The whole resolution is one statement.

The claim

const claimed = await db
  .update(dispatchQueue)
  .set({ claimedAt: new Date(), attempts: sql`${dispatchQueue.attempts} + 1` })
  .where(
    and(
      eq(dispatchQueue.id, queueId),
      eq(dispatchQueue.orchestratorId, orchestratorId),
      isNull(dispatchQueue.claimedAt),
    ),
  )
  .returning({ payload: dispatchQueue.payload })

return claimed.length > 0 ? claimed[0].payload : null

isNull(dispatchQueue.claimedAt) is the load-bearing line. In SQL:

UPDATE dispatch_queue
   SET claimed_at = now(), attempts = attempts + 1
 WHERE id = $1
   AND orchestrator_id = $2
   AND claimed_at IS NULL
RETURNING payload;

Two machines issue this concurrently. Postgres takes a row lock for the first one; the second blocks. When the first commits, the second re-evaluates its WHERE clause against the now-updated row — and claimed_at IS NULL is no longer true. It matches zero rows and its RETURNING comes back empty.

The loser gets null. Not an error, not an exception to handle, not a retry loop. An empty result set that means someone else has this, and the natural code for handling it is to move on to the next row.

No advisory lock. No SELECT ... FOR UPDATE followed by a separate write. No Redis. No application-level coordination of any kind, which matters more than it sounds, because coordination between machines we do not control is the thing we are trying not to build.

Why not SELECT ... FOR UPDATE

The instinct is to read the row, decide, then write:

BEGIN;
SELECT * FROM dispatch_queue WHERE id = $1 FOR UPDATE;
-- application decides whether to take it
UPDATE dispatch_queue SET claimed_at = now() WHERE id = $1;
COMMIT;

This is correct. It is also worse in three specific ways.

It holds a transaction open across a network round trip to the application, which under serverless fan-out is how you exhaust a connection pool. It is two statements where one will do. And it splits the decision from the write, so the invariant now lives in the application's control flow rather than in the statement — which means it can be got wrong later by someone reading only half of it.

The conditional UPDATE collapses read, decide and write into a single atomic operation. The rule is in the statement. There is no window to reason about because there is no gap.

The pattern generalises well beyond queues. Any "take this if nobody has" operation — claiming a lease, marking an invoice paid, transitioning a state machine — is a conditional UPDATE with the precondition in the WHERE clause and a zero-row result meaning lost the race.

The failure this creates

Claiming is the easy half. The hard half is that a claim is a promise to finish, and machines break promises: laptop lid closes, VPN drops, process is killed. The row now has a claimed_at, no other machine will touch it, and the work sits there forever.

So a claim expires. sweepStale finds rows claimed longer ago than the timeout and releases them:

const cutoff = new Date(now.getTime() - DISPATCH_CLAIM_TIMEOUT_MS)

The default timeout is thirty minutes, and it is the only genuinely arbitrary number in this design. Too short and you release work from a machine that is mid-run, and now you really have dispatched twice. Too long and a dead machine parks an issue for half an hour. Thirty minutes is a bet about how long a coding agent's slowest reasonable run takes; it will be wrong for someone, and it is configurable for that reason.

The sweep runs off the polling path

Something we did not plan and would recommend.

The obvious home for the sweeper is a cron job. Ours is on Vercel's Hobby plan, where cron is capped at once per day — a stale claim would sit for up to twenty-four hours, which is not a recovery story.

So the sweep also runs opportunistically, throttled, on the poll route:

// Recover stale claims off the polling path rather than relying on cron,
// which is capped at daily on Hobby plans. Throttled internally.
void maybeSweepStale()

The void is deliberate — the sweep is not awaited, and its failure is caught and logged rather than propagated, because a poll must never fail on account of housekeeping.

What makes this work is not the workaround, it is the property underneath it. Every machine that polls is a machine that can recover other machines' abandoned claims. Recovery frequency scales with fleet activity, which is exactly when recovery matters: a busy fleet sweeps constantly, and an idle fleet has nothing to sweep. The daily cron stayed as a backstop for the case where the fleet is entirely quiet, which is the one case where a stale claim harms nobody.

We arrived here because of a billing limit. It is a better design than the one we would have written without it.

What we actually verified

Two things, and it is worth separating them from what we merely believe.

The race is proven at five concurrent claims against a single row: exactly one claim returns a payload and four return null. That is a real test against a real database, not a stub.

It is not proven at fleet scale. There is no load test. The reasoning above says contention resolves correctly regardless of how many machines race, because Postgres's row lock does the serialising and the count never enters the argument — but reasoning is not measurement, and we would rather say which is which.