world-postgres: step jobs are permanently retired by lost workers, not by failures #3992

Packages: @workflow/world-postgres@4.3.0, @workflow/world@4.2.1, workflow@4.6.0
Queue: graphile-worker@0.16.6
Runtime: AWS ECS Fargate, rolling deploys

What happens

world-postgres enqueues every step job with maxAttempts: 3. The underlying queue counts deliveries, not failures — get_job increments attempts as part of the claim. That is normal for an at-least-once queue, but it composes badly with long-lived workflow steps: a worker replaced mid-step consumes an attempt and records nothing.

Three such losses retire the job permanently. Availability is a stored generated column, so once attempts are exhausted the row can never be claimed again regardless of its lock (graphile-worker/sql/000011.sql:67):

is_available boolean generated always as
  (((locked_at is null) and (attempts < max_attempts))) stored not null

Note the 4-hour stale-lock expiry in get_job(worker_id, task_identifiers, job_expiry interval = interval '4 hours') cannot rescue these rows, because the attempts condition is ANDed in.

Why this matters for workflow specifically

A step can run for minutes — in our case a model call — so "the worker disappears mid-step" is not an edge case on a platform with rolling deploys. ECS caps its stop timeout well below a step's duration, so every deploy landing mid-step costs one attempt on an otherwise healthy job.

The loss is also silent: last_error stays null, so a lost worker is indistinguishable from a genuine failure, in the queue or anywhere else.

Evidence

From one incident, a single workflow run's model-call step:

id    attempts  locked_at            locked_by                   last_error
8765  3/3       2026-09-07 09:53:39  worker-5e36c7e39c794d6abe   (null)
8767  3/3       2026-09-07 09:54:14  worker-0bb74f306ada48ace3   (null)

One attempt went to a genuine provider error; two went to container replacements during a deploy. Neither of the latter recorded anything.

The end state is permanent. A row in the same table, untouched for 28 days:

id    attempts  locked_at  created_at            run_at
2116  3/3       (null)     2026-08-10 09:33:20   2026-08-10 13:36:19

run_at is exactly 4h03m after created_at — the lock did expire and release. The row was still never reclaimed, because the attempts were spent.

Reproduction

1. Start a workflow whose step takes longer than the platform's shutdown grace period.
2. Replace the process mid-step, three times.
3. The job sits at attempts = max_attempts with last_error null and is never picked up again. The run stays non-terminal indefinitely.

What we would like

Either increment attempts on failure rather than on claim, or distinguish a lost claim from a failure so it does not spend the budget. A visibility timeout or lease renewal would achieve the same thing: a worker that stops renewing releases the job without consuming an attempt.

This compounds with two related reports we are filing alongside: the retry cap is not configurable, and nothing recovers the run afterwards except a process boot.

Related: #3993 (recovery only runs at startup) and #3994 (the retry cap is not configurable). The three compound: a lost worker spends an attempt, the cap cannot be raised, and nothing recovers the run afterwards except a process boot.