Limit getDuoWorkflowEvents to latest checkpoint with cursor pagination

What does this MR do and why?

getDuoWorkflowEvents was loading the full unbounded checkpoints list on every poll. Checkpoints are large records, so even a modest accumulation causes significant memory and CPU pressure during GraphQL serialisation, degrading Webservice GraphQL Apdex on Dedicated tenants.

The resolver now returns an offset-paginated ordered relation instead of the raw association. A default_page_size: 1 on both duo_workflow_events field definitions enforces the tail-only default (latest checkpoint only) when no pagination arguments are supplied, keeping per-poll cost O(1). Clients that need to page through history can pass first: N, after: <cursor>.

Offset pagination is used rather than keyset because Checkpoint has a composite primary key [id, created_at] (required by daily partitioning). The keyset SimpleOrderBuilder loses the workflow_id association scope when reconstructing WHERE conditions from the cursor, causing subsequent pages to return results from the wrong workflow.

A named scope order_by_created_at_desc is added to Ai::DuoWorkflows::Checkpoint to keep the ordering out of the resolver (per CodeReuse/ActiveRecord).

References

• https://gitlab.com/gitlab-org/gitlab/-/issues/603509

Screenshots or screen recordings

After

By default query returns 1 record per page

Order of returned records

[3] pry(main)> Ai::DuoWorkflows::Workflow.find(877).checkpoints.order(created_at: :desc).map(&:thread_ts)

=> ["1f16fbdb-40b3-650e-800a-c2cbce58ee90",
 "1f16fb8a-57b5-696a-8009-667d13d0cca3",
 "1f16fb89-ab0d-6d3e-8008-e5d7ea7f288a",
 "1f16efd1-9a8b-6dee-8007-32660a0329a0",
 "1f16efd1-9a85-6bec-8006-181bf2535141",
 "1f16efd1-9a7c-69ac-8005-c5dfc5d194a6",
 "1f16efd1-6ea3-65f6-8004-5e525c9671a9",
 "1f16efd1-3477-66b6-8003-2fecb3f4dbb7",
 "1f16efd1-13b4-6e4c-8002-79e2507fe3e3",
 "1f16efd1-13ae-69d4-8001-c4a344063fa6",
 "1f16efd0-ecf1-6986-8000-e0dbf6562daa",
 "1f16efd0-ecef-6d7a-bfff-7f887f74ae9d"]

How to set up and validate locally

1. Create a Duo Workflow and let it accumulate several checkpoints.
2. Query getDuoWorkflowEvents(workflowId: "...") — confirm only 1 node is returned and hasNextPage: true.
3. Query with first: 3 — confirm up to 3 nodes are returned, ordered newest-first.
4. Use the endCursor from step 2 as after: in a first: N query — confirm older checkpoints are returned.

GQL query

query GetOlderWorkflowEvents {
    duoWorkflowEvents(
      workflowId: "gid://gitlab/Ai::DuoWorkflows::Workflow/877"
      first: 3
    ) {
      pageInfo {
        hasNextPage
        endCursor
      }
      edges {
        node {
          threadTs
	  parentTs
        }
      }
    }
  }

Database query

The new scope is used through the workflow.checkpoints association in the resolver, producing:

SELECT "p_duo_workflows_checkpoints".*
FROM "p_duo_workflows_checkpoints"
WHERE "p_duo_workflows_checkpoints"."workflow_id" = $1
ORDER BY "p_duo_workflows_checkpoints"."created_at" DESC,
         "p_duo_workflows_checkpoints"."id" DESC
LIMIT $2 OFFSET $3

p_duo_workflows_checkpoints is partitioned by created_at (daily). The ORDER BY created_at DESC, id DESC with a small LIMIT is a cheap index scan on the partition covering the most recent day; older partitions are pruned by the query planner.

database review requested per Danger guidance.

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.