Atomic Task Checkout

The single UPDATE...RETURNING statement that atomically claims a task for one agent, preventing two agents from grabbing the same work under concurrent heartbeat runs. How the lock works.

Atomic Task Checkout: Preventing Double Work

What you'll learn

How Paperclip uses a single UPDATE...RETURNING statement to claim tasks without race conditions, why this works under concurrent heartbeat runs, and how to verify it in your own queries.

The Problem: Two Heartbeats, One Task

Without coordination, two concurrent heartbeat runs could both query for open issues, both find the same task, and both invoke the same agent — resulting in duplicate work and double billing.

The Solution: Single-Statement Claim

Paperclip uses PostgreSQL's atomic update:

UPDATE issues
SET
  status     = 'claimed',
  claimed_at = now()
WHERE id = (
  SELECT id
  FROM issues
  WHERE company_id   = $1
    AND assigned_agent_id = $2
    AND status       = 'open'
  ORDER BY
    CASE priority
      WHEN 'critical' THEN 1 WHEN 'high' THEN 2
      WHEN 'medium'   THEN 3 WHEN 'low'  THEN 4
    END, created_at ASC
  LIMIT 1
  FOR UPDATE SKIP LOCKED   -- ← the key clause
)
RETURNING *;

Why This Is Safe

FOR UPDATE SKIP LOCKED acquires a row-level lock on the selected row. Any concurrent transaction attempting FOR UPDATE on the same row will skip it rather than waiting. This means:

• Transaction A claims row 5 (locks it)
• Transaction B runs the same query, skips row 5, claims row 6 instead
• No deadlock, no wait, no duplicate work

The claim is atomic: the SELECT and UPDATE happen in a single statement — there is no window between "I found it" and "I locked it."

Verifying No Duplicates

After running multiple concurrent heartbeats in development:

-- Check for any issue that was claimed more than once in the same run
SELECT issue_id, COUNT(*) AS claim_count
FROM heartbeat_run_items   -- a view or junction table if your schema has one
GROUP BY issue_id
HAVING COUNT(*) > 1;

-- Or check for issues resolved multiple times
SELECT id, title, attempt_count
FROM issues
WHERE attempt_count > 1
  AND company_id = 'your-company-id'
ORDER BY attempt_count DESC;

The attempt_count column is incremented each time an issue enters in_progress. A value above 1 means the issue was retried (due to failure), not duplicated.

The attempt_count Guard

-- Heartbeat also checks max_attempts before claiming
UPDATE issues
SET status = 'claimed', claimed_at = now(), attempt_count = attempt_count + 1
WHERE id = (
  SELECT id FROM issues
  WHERE ...
    AND attempt_count < max_attempts   -- ← guard
  FOR UPDATE SKIP LOCKED
)
RETURNING *;

When attempt_count >= max_attempts, the issue is moved to escalated rather than retried:

UPDATE issues
SET status = 'escalated'
WHERE status = 'open'
  AND attempt_count >= max_attempts
  AND company_id = $1;

Testing Concurrency Locally

# Run 3 heartbeats simultaneously
for i in 1 2 3; do
  curl -s -X POST http://localhost:3100/api/heartbeat/run \
    -H "Content-Type: application/json" \
    -d '{"company_id": "'${COMPANY_ID}'"}' &
done
wait

# Verify: each issue resolved exactly once
psql $DATABASE_URL -c "
  SELECT attempt_count, COUNT(*) FROM issues
  WHERE company_id = '${COMPANY_ID}'
  GROUP BY attempt_count;"

Checkpoint ✓

• [ ] You can explain why FOR UPDATE SKIP LOCKED prevents double-work
• [ ] You understand attempt_count vs max_attempts and the escalation path
• [ ] You can run concurrent heartbeats and verify no duplicates