Durable Jobs, Leases, Idempotency, and Recovery

Long AI operations outlive HTTP requests, processes, and deployments. The reliable contract is not “a background task was spawned.” It is:

a durable job was committed with the run;
duplicate client attempts resolve to one logical operation;
one current lease generation may publish effects;
expired work becomes claimable;
retries are bounded and classified;
terminal state and event commit atomically.

The companion’s second SQL migration and mosaic-db implementation make this contract executable.

Learning objectives

distinguish delivery, execution, and effect guarantees;
build a transactional job/outbox boundary;
scope idempotency keys and bind them to canonical request fingerprints;
claim jobs with expiring leases;
fence stale workers with lease generations;
renew, complete, retry, and terminalize under compare-and-set conditions;
design retry budgets and poison/dead-job handling;
recover after process, network, and deployment failure;
understand what SQLite tests prove versus PostgreSQL SKIP LOCKED.

Dependencies and mental model

Depends on SQLx transactions, run/event schemas, HTTP admission, cancellation, and bounded backpressure.

client POST (may retry)
        │ tenant + idempotency key + request fingerprint
        ▼
transaction:
  run + queued event + job + idempotency record
        │ COMMIT
        ▼
workers claim under expiring lease generation
        │
        ├── renew current generation
        ├── retry/requeue with backoff
        ├── complete atomically with terminal event
        └── crash → expiry → another generation recovers

Delivery is generally at least once. Durable state transitions and external effects must therefore be idempotent or reconciled. “Exactly once” is a property of a scoped logical effect, not of a queue message magically being seen once.

The durable acceptance transaction

The companion migration adds:

CREATE TABLE idempotency_keys (
    scope TEXT NOT NULL,
    key TEXT NOT NULL,
    request_fingerprint TEXT NOT NULL,
    run_id TEXT NOT NULL,
    PRIMARY KEY (scope, key),
    FOREIGN KEY (run_id) REFERENCES runs(run_id)
);

CREATE TABLE jobs (
    run_id TEXT PRIMARY KEY NOT NULL,
    state TEXT NOT NULL,
    available_at INTEGER NOT NULL,
    lease_owner TEXT,
    lease_expires_at INTEGER,
    lease_generation INTEGER NOT NULL DEFAULT 0,
    attempts INTEGER NOT NULL DEFAULT 0,
    max_attempts INTEGER NOT NULL,
    last_error_code TEXT,
    FOREIGN KEY (run_id) REFERENCES runs(run_id)
);

create_run_idempotent commits four facts together:

the run projection;
sequence-one run_queued;
the claimable job;
the scoped idempotency record.

If any insert fails, none commit. After commit, the process may crash and a later worker still sees the job.

This is the transactional outbox pattern when a database row is the job/outbox. If a separate broker is required, commit an outbox row with the domain changes; a relay publishes it later and marks delivery. Never perform database commit and broker publish as two uncoordinated success steps.

Idempotency is request identity plus equivalence

An idempotency key alone is insufficient. The server stores:

(scope, key) → request_fingerprint, run_id

The scope normally includes authenticated tenant and operation/endpoint. Same key:

same canonical fingerprint → return/reconstruct the original run response;
different fingerprint → 409 idempotency_conflict;
no record → create the operation transactionally.

The companion test sends the same logical request twice with different proposed RunIds. The second returns the first run; only one queued event exists. Reusing the key with another fingerprint fails.

Canonical fingerprint

Hash a versioned canonical representation of the validated command:

route/operation and schema version;
tenant/principal-relevant scope;
normalized fields after server defaults;
ordered/canonical JSON or typed binary representation;
artifact IDs/hashes, not mutable signed URLs;
policy-relevant options.

Do not hash raw JSON bytes if insignificant key order/whitespace should be equivalent. Do not include random server-generated run ID. Do not log the full key or sensitive fingerprint source.

Retention

Keep idempotency records at least as long as clients may safely retry and side effects remain dangerous to duplicate. Expiry is policy. If a key expires too soon, the same retry can create a new paid operation.

Job states

The companion uses:

queued ──claim──► leased ──complete──► completed
  ▲                  │
  └────retry─────────┘
                     └──attempt budget exhausted──► dead

leased ──expiry──► eligible for a new claim generation

Product run state and operational job state are related but not identical:

run may be queued while a job waits;
job is leased while run is becoming/running;
job can retry without declaring run terminal;
job dead must create/align a terminal run failure;
cancel may terminalize the run and make job unclaimable.

Store both deliberately. Do not infer current ownership solely from a run status string.

A lease is temporary ownership

A claim records:

owner identity;
absolute expiration;
monotonically increasing generation;
attempt count;
run/job ID.

The companion claim:

eligible when:
  available_at <= now
  attempts < max_attempts
  state = queued
    OR state = leased AND lease_expires_at <= now

then atomically changes:

state = leased
lease_owner = worker
lease_expires_at = now + duration
lease_generation += 1
attempts += 1

Expiration provides crash recovery: no process has to release a lease it can no longer execute.

Fencing generations stop stale completion

Expiry alone is not enough:

worker A holds generation 1 and pauses;
lease expires;
worker B claims generation 2 and starts;
worker A wakes and tries to publish.

If completion checks only owner/run, A may overwrite B. Every renew/finalize/effect ledger update requires current owner and generation plus an unexpired lease:

... WHERE run_id = ?
      AND state = 'leased'
      AND lease_owner = ?
      AND lease_generation = ?
      AND lease_expires_at > ?

Zero affected rows means LostLease. The worker must stop publishing and clean up only resources it still owns.

The companion test recovers an expired generation-1 job with worker B at generation 2. A’s terminal commit fails; B’s succeeds and creates the only terminal event.

For external systems, pass a fencing token if the system can reject older tokens. If it cannot, use an idempotent effect key and reconciliation ledger. Database fencing cannot undo an already-issued unfenced provider charge.

Claiming safely on PostgreSQL

The SQLite companion has one connection and uses a short select-plus-conditional-update transaction, which deterministically exercises semantics. A multi-worker PostgreSQL queue normally uses row locking:

WITH candidate AS (
    SELECT run_id
    FROM jobs
    WHERE available_at <= now()
      AND attempts < max_attempts
      AND (
        state = 'queued'
        OR (state = 'leased' AND lease_expires_at <= now())
      )
    ORDER BY available_at, run_id
    FOR UPDATE SKIP LOCKED
    LIMIT 1
)
UPDATE jobs
SET state = 'leased',
    lease_owner = $1,
    lease_expires_at = now() + $2,
    lease_generation = lease_generation + 1,
    attempts = attempts + 1
FROM candidate
WHERE jobs.run_id = candidate.run_id
RETURNING jobs.*;

SKIP LOCKED intentionally gives an inconsistent view suitable for queue-like consumers: workers skip rows another worker locked instead of blocking behind one job. It is not a general read isolation tool.

Test on the production engine. Fairness, query plans, deadlocks, connection loss, clock source, and lock behavior differ from SQLite.

Time and clocks

Use the database clock for cross-worker lease comparison when possible. Worker wall clocks can skew. Store absolute database timestamps, not only process Instant values.

The companion takes deterministic integer now values so tests can cross expiry boundaries without sleep. It validates nonnegative time/duration and checked addition. Production code binds CURRENT_TIMESTAMP/intervals or reads one database time in the transaction.

Lease duration must exceed normal scheduling jitter and renewal latency but remain short enough for recovery. Renewal occurs before expiry with margin:

lease 60 s
renew every 20 s
stop accepting/publishing if renewal fails

Do not renew forever without run deadline/progress policy.

Claim, start, and cancellation

After claim:

load immutable run/program/model/artifact identities;
validate run remains eligible and not cancelled;
append/start attempt under current lease;
execute outside the transaction;
renew while progress is legitimate;
checkpoint only idempotent/resumable boundaries;
on completion, compare lease and commit output/projection/event atomically.

Explicit cancellation should:

atomically mark requested/cancelled state;
prevent new claims;
wake/notify current owner;
current worker observes cancellation and stops safely;
fence final completion against cancelled/version state;
clean external work under deadline.

Cancellation races with completion need a specified winner and audit event.

Retry classification

Do not retry every error.

Usually retryable

provider 429/temporary 5xx;
network reset before known result;
transient database serialization/deadlock;
temporarily unavailable accelerator;
object-store timeout on an idempotent read/write.

Usually terminal

invalid input/schema;
forbidden tool/provider/data residency;
unsupported/corrupt media;
exhausted budget;
deterministic verifier failure after bounded repair;
revoked tenant entitlement.

Ambiguous external effect

Do not blindly retry. Reconcile using provider operation/idempotency key, effect ledger, object hash, or status endpoint.

Backoff:

delay = min(cap, base × 2^attempt) + bounded jitter

Honor safe Retry-After. Cap attempts, total elapsed time, and cost. Persist available_at so a restart preserves delay.

The companion’s retry_job schedules a future attempt while attempts < max_attempts. At the exact budget it atomically:

marks job dead;
marks run failed;
increments event sequence;
appends run_failed with a safe error code.

Attempt history and poison work

A production attempt row records:

attempt number/generation/worker;
claim/start/renew/finish times;
model/program/artifact revisions;
safe error class and retry decision;
usage/cost;
checkpoint/effect IDs;
trace location.

Do not overwrite one last_error as the only evidence. Keep bounded, retention-controlled history.

“Dead letter” is not success. Dead work needs:

terminal user-visible state;
alert/queue by error class;
safe replay tool with new operation identity/policy;
preserved original evidence;
authorization/audit;
prevention of infinite operator replay.

Worker supervision and shutdown

A worker loop:

while accepting:
  claim under deadline
  if none: wait with jitter/notification
  else:
    supervise one attempt
    renew lease
    complete/retry/fail with compare-and-set

shutdown:
  stop claiming
  signal owned attempts
  finish/checkpoint within grace
  release/requeue only if still owner
  exit; otherwise lease expiry recovers

Do not delete job rows on claim. Do not hold the claim transaction during model execution. Do not set a far-future lease to avoid renewals.

Runnable companion implementation

cd rust/rust-ai-engineering
cargo run -q -p mosaic-db --example durable_job
cargo test -p mosaic-db

Expected:

created + replayed; generation=1; status=completed; events=2

Ten repository tests now prove:

migration and atomic first event;
duplicate run rollback;
projection/event atomicity;
unique concurrent event sequences;
no orphan events;
invalid domain rejection;
idempotent replay and mismatch conflict;
expired lease recovery plus stale fencing;
current-generation renewal;
retry schedule and exact attempt-budget terminalization.

Failure investigation: duplicate paid generations

correlate tenant, idempotency key, request fingerprint, run IDs, and provider effect IDs;
check key scope/retention and canonicalization version;
identify unknown commit/HTTP retry window;
inspect whether run/job/idempotency committed in one transaction;
check worker effects used a stable idempotent operation key;
reconcile provider state before retry;
compensate/refund under incident policy and add exact race test.

Failure investigation: two workers completed one run

inspect lease owner, generation, expiry, and terminal update predicates;
check stale worker published external effect before fencing;
verify lease renewal/clock source;
confirm terminal unique/version constraints;
reject all non-current generation commits;
reconcile external effects by idempotency ledger;
run pause-expire-reclaim-resume chaos test.

Failure investigation: jobs remain leased forever

query expiry distribution and database clock;
check eligibility includes expired leased rows;
inspect claim query/index plan;
check lease duration overflow/time zone/unit mismatch;
verify attempts budget did not exclude without terminalization;
inspect long transaction/lock blockers;
repair rows under audited invariant-preserving script.

Alternatives and trade-offs

Database queue

Atomic with run state, simple operations, excellent moderate throughput. Adds polling/write load and requires careful locking/index/retention.

External broker

High throughput/routing and independent scaling. Database-to-broker dual-write requires outbox; delivery remains at least once.

Long workflow engine

Provides timers/retries/history/workflow semantics. Adds platform/serialization/determinism constraints. External effects still require idempotency.

No leases, one consumer

Simple for a local CLI. No horizontal worker recovery; a crash requires manual restart/requeue.

Session advisory locks

Can coordinate, but connection lifetime and pool behavior become ownership. Transaction-level/row state with visible expiry is often easier to inspect/recover.

Security and performance implications

Security:

idempotency scope always includes authenticated tenant/operation;
keys and error payloads are bounded/redacted;
workers receive narrow credentials/capabilities;
claim/complete queries include tenant in production;
stale workers cannot publish privileged state;
replay/dead-job tools require authorization/audit;
poison input cannot create infinite retries/cost.

Performance:

claim index/order and hot-row contention determine throughput;
polling interval trades latency for database load;
batch claims improve throughput but extend lease/fairness complexity;
per-run sequence projection is a serialized hot key by design;
renewals create write load—choose duration/margin from measurements;
retry storms require jitter/global/provider backpressure;
attempt/event history needs partition/retention.

Exercises

Add tenant to every job/idempotency key and all claim/update predicates.
Add an explicit cancelled state and specify cancel-versus-complete race.
Write PostgreSQL SKIP LOCKED integration tests with two independent connections.
Inject a worker pause past expiry and prove the old generation cannot append any effect.
Add an effect ledger keyed by (run, step, external_operation).
Add decorrelated jitter with deterministic RNG injection and total retry deadline.
Design graceful shutdown for 30-second lease and 20-second termination grace.

Solution guidance

Use composite tenant keys/foreign keys. Cancellation and completion both use expected run version, so one compare-and-set wins; the loser observes terminal state. The effect ledger reserves an operation key before the call, stores provider operation/result status, and reconciles unknown before repeating. Chaos tests synchronize with barriers—not sleeps—then expire via injected clock. Shutdown stops claims first and never “releases” a lease after losing its generation.

Check your understanding

What exactly is created in the acceptance transaction?
Why store a request fingerprint with the key?
Does a lease guarantee only one worker is executing?
What does a fencing generation prevent?
Why can an expired job be reclaimed safely?
When is an error ambiguous rather than retryable?
Why is SKIP LOCKED appropriate for a queue but not a general read?
What must happen when attempts are exhausted?