Job scheduler walk-through: leases, retries, idempotency keys and a queue table

Goal

Run background jobs and recurring schedules with at-least-once execution, bounded retries and no duplicated side effects, using a database table before adopting a broker.

Prerequisites

A relational database shared by all workers, handlers that can be made idempotent, and one clock: timestamps come from the database, not from worker hosts.

Steps

1. Constraints: a job runs at least once and may run again after a crash; a schedule fires once per slot even with several scheduler instances; one broken job must not block the rest.
2. Components: a job table as the queue; workers that claim, run and finish jobs; a scheduler that turns schedules into jobs; a reaper for expired leases; a dead-letter list.
3. Data model: job(id, type, payload, idempotency_key unique, status: ready|running|done|failed|dead, run_at, attempts, max_attempts, locked_by, locked_until, last_error, created_at); schedule(id, job_type, cron, next_run_at, last_slot); an index on (status, run_at). For scheduled jobs the idempotency key is schedule_id + slot, so a second scheduler instance enqueuing the same slot does nothing.
4. Claiming: SELECT ... WHERE status = 'ready' AND run_at <= now() ORDER BY run_at LIMIT 1 FOR UPDATE SKIP LOCKED, then set running, locked_by and locked_until = now() + lease, and commit. The PostgreSQL documentation states that with SKIP LOCKED rows that cannot be locked immediately are skipped, and that this can be used to avoid lock contention with multiple consumers accessing a queue-like table. Long handlers extend the lease with a heartbeat.
5. Retries: on failure set run_at = now() + backoff(attempts) with jitter and return the row to ready, until max_attempts moves it to dead; infrastructure errors (database unreachable) do not count as attempts. The handler checks its own effect before acting (a row already written, a message id already recorded), since the scheduler cannot promise a single run.
6. Failure modes: a worker dies holding a lease (the reaper resets the row after locked_until and the handler runs again); bursts at the top of the hour (a per-schedule offset spreads run_at); a poison job (max_attempts, dead-letter, alert); the table growing without bound (archive done rows on a schedule); a clock-skewed worker (database time only).
7. Measure: age of the oldest ready job, running jobs per type, attempts distribution, lease expirations per hour, dead-letter count, schedule slots missed.
8. Not first: a message broker, priorities and per-tenant fairness, workflow graphs, exactly-once claims.

Expected result

A crash anywhere delays work rather than losing or doubling it, and each job's history is one row that explains its state.

Limits and test basis

Proposed design, no measurements. A queue table suits moderate volumes; the switch to a broker is a later decision driven by measured queue age and lock waits.