Fix statement timeout in Duo Workflows credits cron worker

Serhii Yarynovskyirequested to merge
630263-fix-broken-cron-job into master

What does this MR do and why?

Ai::DuoWorkflows::FetchSessionCreditsCronWorker was failing on every 15-minute tick in production (gprd) with ActiveRecord::QueryCanceled / PG::QueryCanceled (statement timeout). This MR rewrites the worker's scan query so it can no longer time out, regardless of how far behind the sync cursor has fallen.

https://log.gprd.gitlab.net/app/r/s/v7eCE

Root cause

scan ran:

status IN (3,5,6,7,8) AND updated_at > cursor AND updated_at <= 2.hours.ago
ORDER BY updated_at, id
LIMIT 1000

The supporting index, idx_workflows_status_updated_at_id, is (status, updated_at, id) — it leads on status. With an IN list on the leading column, PostgreSQL cannot emit rows in (updated_at, id) order directly, so it adds a Sort node and must materialize every matching row in the window before the LIMIT can apply. The LIMIT was effectively doing nothing.

This was self-reinforcing: the :duo_workflow_session_credits sync cursor only advances after a successful dispatch, so every timeout left the cursor in place and widened the next run's window, making the query even slower next time. It could not recover on its own, and the worker includes CronjobQueue, which disables retries, so nothing else recovered it either.

Also worth noting: the duo_workflow_session_credits_ingestion feature flag does not gate the scan — perform runs scan before any flag check, so turning the flag off would not have stopped the timeouts. Only Gitlab::ClickHouse.globally_enabled_for_analytics? and the suspend_click_house_data_ingestion pause control sit in front of it.

Kibana showed db_main_replica_duration_s of 15.048 out of a total job duration_s of 15.344 — one query burned the entire ~15s statement timeout. The backtrace pointed at ee/app/workers/ai/duo_workflows/fetch_session_credits_cron_worker.rb, in the scan method.

The fix: scan now issues one query branch per billable status (an equality condition on the leading index column) and combines them with from_union(remove_duplicates: false, remove_order: false), then applies the outer ORDER BY and LIMIT. Each branch also carries its own LIMIT. This lets PostgreSQL plan a Merge Append over five bounded Index Scans with no Sort, so the LIMIT actually stops the scan early and runtime no longer scales with how far behind the cursor is.

Supporting change: Ai::DuoWorkflows::Workflow.billable_status_values was extracted so the worker can iterate the individual status values. The with_billable_status scope became unused as a result and was removed; its specs were retargeted at .billable_status_values.

Query plan shape (verified locally on PostgreSQL 17.8; project MINIMUM_POSTGRES_VERSION is 17) Before: 
Limit -> Sort (Sort Key: updated_at, id) -> Index Scan using idx_workflows_status_updated_at_id
  Index Cond: status = ANY ('{3,5,6,7,8}') AND updated_at range

After:

Limit -> Merge Append (Sort Key: updated_at, id) -> five child [Limit -> Index Scan] nodes, one per status
  each Index Cond: status = N AND updated_at range

Database review

This changes a query against duo_workflows_workflows, a large table, so this MR needs database review.

Before
Before 
SELECT "duo_workflows_workflows".*
FROM "duo_workflows_workflows"
WHERE "duo_workflows_workflows"."status" IN (3,
                                             5,
                                             6,
                                             7,
                                             8)
  AND "duo_workflows_workflows"."updated_at" > '2026-09-05 08:22:25'
  AND "duo_workflows_workflows"."updated_at" <= '2026-09-22 06:24:33.285841'
ORDER BY "duo_workflows_workflows"."updated_at" ASC,
         "duo_workflows_workflows"."id" ASC LIMIT 1000;
 
Time: 54.893 s
  - planning: 2.291 ms
  - execution: 54.891 s
    - I/O read: 2.181 min
    - I/O write: 0.477 ms

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/58273/commands/162941
After
After 
SELECT "duo_workflows_workflows".*
FROM (
        (SELECT "duo_workflows_workflows"."id",
                "duo_workflows_workflows"."namespace_id",
                "duo_workflows_workflows"."project_id",
                "duo_workflows_workflows"."updated_at"
         FROM "duo_workflows_workflows"
         WHERE "duo_workflows_workflows"."status" = 3
           AND "duo_workflows_workflows"."updated_at" > '2026-09-05 09:24:31'
           AND "duo_workflows_workflows"."updated_at" <= '2026-09-22 07:37:39.926783'
         ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000)
      UNION ALL
        (SELECT "duo_workflows_workflows"."id",
                "duo_workflows_workflows"."namespace_id",
                "duo_workflows_workflows"."project_id",
                "duo_workflows_workflows"."updated_at"
         FROM "duo_workflows_workflows"
         WHERE "duo_workflows_workflows"."status" = 5
           AND "duo_workflows_workflows"."updated_at" > '2026-09-05 09:24:31'
           AND "duo_workflows_workflows"."updated_at" <= '2026-09-22 07:37:39.926783'
         ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000)
      UNION ALL
        (SELECT "duo_workflows_workflows"."id",
                "duo_workflows_workflows"."namespace_id",
                "duo_workflows_workflows"."project_id",
                "duo_workflows_workflows"."updated_at"
         FROM "duo_workflows_workflows"
         WHERE "duo_workflows_workflows"."status" = 6
           AND "duo_workflows_workflows"."updated_at" > '2026-09-05 09:24:31'
           AND "duo_workflows_workflows"."updated_at" <= '2026-09-22 07:37:39.926783'
         ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000)
      UNION ALL
        (SELECT "duo_workflows_workflows"."id",
                "duo_workflows_workflows"."namespace_id",
                "duo_workflows_workflows"."project_id",
                "duo_workflows_workflows"."updated_at"
         FROM "duo_workflows_workflows"
         WHERE "duo_workflows_workflows"."status" = 7
           AND "duo_workflows_workflows"."updated_at" > '2026-09-05 09:24:31'
           AND "duo_workflows_workflows"."updated_at" <= '2026-09-22 07:37:39.926783'
         ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000)
      UNION ALL
        (SELECT "duo_workflows_workflows"."id",
                "duo_workflows_workflows"."namespace_id",
                "duo_workflows_workflows"."project_id",
                "duo_workflows_workflows"."updated_at"
         FROM "duo_workflows_workflows"
         WHERE "duo_workflows_workflows"."status" = 8
           AND "duo_workflows_workflows"."updated_at" > '2026-09-05 09:24:31'
           AND "duo_workflows_workflows"."updated_at" <= '2026-09-22 07:37:39.926783'
         ORDER BY "duo_workflows_workflows"."updated_at" ASC, "duo_workflows_workflows"."id" ASC LIMIT 1000)) duo_workflows_workflows
ORDER BY "duo_workflows_workflows"."updated_at" ASC,
         "duo_workflows_workflows"."id" ASC LIMIT 1000
 
Time: 132.217 ms
  - planning: 25.193 ms
  - execution: 107.024 ms
    - I/O read: 103.056 ms
    - I/O write: 0.000 ms

https://console.postgres.ai/gitlab/projects/gitlab-production-main/sessions/58273/commands/162944

Deployment note

After this deploys, the stuck sync cursor does not jump forward instantly. It advances by one batch (up to MAX_ROWS_PER_RUN = 1000 rows) per 15-minute tick, starting from wherever it froze. Recovery is gradual catch-up, not immediate.

References

Screenshots or screen recordings

Not applicable — this MR has no UI change.

How to set up and validate locally

  1. To see the plan change directly, run EXPLAIN on the SQL generated by scan and confirm the plan uses Merge Append rather than Sort.
  2. Run the relevant specs (the worker spec is tagged :click_house, so it needs the tag to run at all):
    • bundle exec rspec ee/spec/workers/ai/duo_workflows/fetch_session_credits_cron_worker_spec.rb --tag click_house
    • bundle exec rspec ee/spec/models/ai/duo_workflows/workflow_spec.rb

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

🤖 Generated with Claude Code

Edited by Serhii Yarynovskyi

Merge request reports