Workflow dep resolution leaves `scheduled_at` stale, breaking queue delay monitoring

Open
  • Is something wrong?
#1,185 2 comments 0 reactions 0 assignees View on GitHub

Maintainers usually reply within 1 day

Nobody has claimed this yet.

Assessment

Difficulty
4/5
Estimated time
3-5 days
Newbie friendliness
52/100
Issue type
Bug
Clarity
Mostly clear
Activity status
Quiet
Tech stack
go, postgresql
Domain
backend, databases, observability

Research direction

Locate the WorkflowStageJobs and WorkflowStageJobsByIDMany queries and inspect the jobs_to_make_available CTE plus its UPDATE of river_job. Confirm the behavior for jobs becoming available versus remaining scheduled, then choose and implement the agreed timestamp approach; done means dependency-resolved available jobs report an accurate queue-delay timestamp without breaking scheduled jobs.

Written by the indexing model from the issue text.

Description

Description

When WorkflowStageJobs / WorkflowStageJobsByIDMany resolve dependencies and transition a job from pending to available, the scheduled_at column is not updated. It retains its original value from insertion time, which can be hours or months old for long-running workflows.

The UPDATE in both queries only sets state and metadata.workflow_staged_at:

UPDATE river_job
SET
  state = jobs_to_make_available.new_state,
  metadata = jsonb_set(metadata, '{workflow_staged_at}'::text[], $1::jsonb, true)
FROM jobs_to_make_available
WHERE river_job.id = jobs_to_make_available.id

The jobs_to_make_available CTE already reads scheduled_at to decide the target state (available if scheduled_at <= now() + 5s, otherwise scheduled), so by the time the UPDATE executes, the original scheduled_at value has served its purpose.

Impact

Any monitoring that uses NOW() - scheduled_at on available jobs to measure queue delay will report wildly inflated values for dependency-resolved workflow jobs. For workflows where deps take hours or months to resolve, this produces false alarms on queue health metrics.

Current workaround

We discovered that workflow_staged_at is already stamped in metadata during dep resolution, so we use it as a fallback in our metrics query:

MAX(
  CASE
    WHEN metadata ? 'workflow_staged_at'
      THEN NOW() - (metadata->>'workflow_staged_at')::timestamptz
    ELSE NOW() - scheduled_at
  END
) as oldest_delay

This works but requires casting a JSONB string to timestamptz in an aggregate query, which is less ergonomic than using the native scheduled_at column directly.

Proposed solutions

Either of these would address the problem:

  1. Update scheduled_at = now() in WorkflowStageJobs / WorkflowStageJobsByIDMany when transitioning jobs to available. This makes scheduled_at accurately reflect when the job became eligible for pickup, consistent with how non-workflow jobs behave. For jobs transitioning to scheduled (because their scheduled_at is still in the future), no change is needed — scheduled_at is already correct.

  2. Add a first-class available_at column to river_job that records when a job entered the available state, regardless of how it got there (direct insert, scheduled time reached, or workflow dep resolution). This would give monitoring queries a reliable, indexed timestamp without relying on scheduled_at semantics or JSONB metadata. It would also benefit non-workflow use cases like jobs inserted with Pending: true that are later moved to available by application code.

Environment

  • River Pro v0.22.0
  • PostgreSQL
Dominant language
Go
Stars
5.9k
Forks
197
Avg merge
1d 4h
Merged PRs (30d)
120

Getting set up

This project ships no dev container, Dockerfile or contributing guide, so setting up is up to you: start from its README, and see our first-contribution guide for the general steps.

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

More from riverqueue/river

All issues in riverqueue/river

Similar issues

More Go issues

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.