Bound Duo session artifact listings to a 180-day window

What changes for users

Value: 180 days, a constant that is easy to raise later if needed. Shared with Nate in #634979.

• The legacy duoWorkflowSessionArtifacts field lists sessions from the last 180 days by default, the same as Bound AI governance session listings to a 180-d... (!265842 - merged). Today it has no date bound.
• The Audit events tab uses this field only when the ai_governance_sessions_api flag is off (it is on by default), so on GitLab.com this mainly affects API callers.
• On GitLab.com about 1.51M of 7.86M Duo workflows (19%) were created more than 180 days ago. They need a date filter to be seen.
• count is capped: above 1,000 it returns 1,001. No page reads it.

Limits the GraphQL field duoWorkflowSessionArtifacts (groups and projects, an experiment since 19.1) to a 180-day window of sessions and caps its count at 1000. Callers that send a window of 180 days or less, or filter by workflowId, see no change. This is the same change Bound AI governance session listings to a 180-d... (!265842 - merged) made for aiGovernanceSessions, applied to the older field.

Why

With no workflowCreatedAfter, the finder deduplicated a namespace's whole history in the ClickHouse table siphon_duo_workflows_workflows (an argMax GROUP BY over every row before ordering and paging; primary key traversal_path, created_at, id, no partition key) and count counted all of it, so the work grew with the history on a server other features share. The PostgreSQL path had the same shape.

On GitLab.com (Database Lab clone of 2026-10-28) duo_workflows_workflows holds about 7.86M rows. About 1.51M (19%) were created more than 180 days ago, 578k before 2026-01-01 and 19,290 before 2025-07-01, the same split as the sessions table, which was backfilled from these workflows. After this change those need an explicit window to be listed.

The change

• workflowCreatedAfter defaults to 180 days before workflowCreatedBefore or before now, whichever is earlier. An earlier value is moved up to that bound, the same way first is limited by the max page size. This lives in Resolvers::Ai::DuoWorkflows::BaseSessionArtifactsResolver, so the ClickHouse and PostgreSQL finders both get it.
• On PostgreSQL the listing walks each namespace's (namespace_id, workflow_updated_at DESC, workflow_id DESC) index, so the window also bounds workflow_updated_at. A session is never updated before it is created, so no row inside the window is dropped. On a seeded clone, Pratyaksh measured the count and first page dropping from 123,661 to 43,080 buffers.
• A filter by workflowId gets no default window, since it matches one row.
• DuoWorkflowSessionArtifactConnection inherits LimitedCountableConnectionType. count takes a limit of at most 1000 (default 1000) and returns limit + 1 beyond it, on both backends.
• Argument and field descriptions say so. GraphQL reference and introspection are regenerated. No migration.
• The Audit events tab queries this field only when the ai_governance_sessions_api flag is off (on by default); it sends a window only when the user sets one, so it keeps showing every session newer than 180 days. Its two queries select count with no limit and nothing in the frontend reads it, so the cap changes nothing on screen, but every page load still runs that count query; dropping count from the queries is a frontend follow-up.
• The existing error for workflowCreatedAfter not before workflowCreatedBefore is unchanged.

Measured

The table has the same engine and key shape as siphon_ai_governance_sessions, so the measurement from Bound AI governance session listings to a 180-d... (!265842 - merged) applies. Local ClickHouse 26.9.2, 3,000,000 synthetic sessions under one group over 2.8 years, the finder's query shape, first page of 21, peak memory from system.query_log.

Query	Peak memory	Rows read	Time
Page, no window (before)	797 MiB	3,000,000	438 ms
Page, 180-day window (after)	158 MiB	554,906	93 ms
Exact count, no window (before)	257 MiB	3,000,000	67 ms

PostgreSQL query

The listing is an in-operator query: a recursive CTE walks the group's namespaces (traversal_ids @> ('{<group id>}')) and, for each namespace, reads the next artifact in (workflow_updated_at DESC, workflow_id DESC) order through the (namespace_id, workflow_updated_at DESC, workflow_id DESC) index, until the page is full. The window only changes the per-namespace subquery. Before this MR's workflow_updated_at bound:

SELECT workflow_updated_at, workflow_id FROM duo_workflow_session_artifacts
WHERE workflow_created_at >= '<window start>'
  AND namespace_id = array_cte.id
ORDER BY workflow_updated_at DESC, workflow_id DESC LIMIT 1

After:

SELECT workflow_updated_at, workflow_id FROM duo_workflow_session_artifacts
WHERE workflow_created_at >= '<window start>' AND workflow_updated_at >= '<window start>'
  AND namespace_id = array_cte.id
ORDER BY workflow_updated_at DESC, workflow_id DESC LIMIT 1

The same two predicates appear in the keyset step that reads the next row after the cursor. With only workflow_created_at the index scan still walks every row of a namespace in update order and filters each one; with workflow_updated_at in the index condition it stops at the window. A session is never updated before it is created, so no row inside the window is dropped (0 of 7,998,085 workflows on his production clone have updated_at before created_at, Pratyaksh's check).

Pratyaksh measured it on a seeded production clone (200,000 old sessions and 15 recent ones under one group of 10,913 namespaces): count and first page went from 123,661 buffers without the bound to 43,080 with it; the remaining buffers are the namespace walk itself. On GitLab.com the table is empty today: a Database Lab run of the full query for the same group on 2026-10-30 returned 0 rows and spent its 12 seconds walking the namespaces, which this MR does not change, so the index choice on the artifacts table only shows on his seeded clone.

Decisions

• A window, not in-order aggregation: that still reads every row and is five times slower, and without a partition key only a created_at bound prunes.
• Clamp, not error, on a span over 180 days: the Audit events tab's date tokens accept any date.
• 180 days as default and cap, to match Bound AI governance session listings to a 180-d... (!265842 - merged) and the value stream and DORA range limit.
• count limited to 1000, since nothing in the UI reads it.

Ships with

Show the 180-day session window on the AI Gover... (!265843 - merged) shows the 180-day note on the Audit events tab for both session fields, so a user whose filter spans more than 180 days sees which days are listed. The two should merge together with Bound AI governance session listings to a 180-d... (!265842 - merged): if only this MR merges, older sessions drop out of the legacy list with nothing saying so.

Open question

Whether to tighten the default to 30 days later, together with the sessions field.

What users will see

The frontend MR Show the 180-day session window on the AI Gover... (!265843 - merged) explains the window on both pages. After this change:

• The Recent agent sessions card says which window it covers:
• The Audit events tab says which 180 days are listed when the date filter is wider than that:

Merge order

This change ships in three MRs:

Order	MR	What it does	Depends on
1	Show the 180-day session window on the AI Gover... (!265843 - merged) • Dheeraj Joshi • 19.6	Frontend notes that explain the window	none
2	Bound AI governance session listings to a 180-d... (!265842 - merged) • Dheeraj Joshi • 19.6	Sessions list: 180-day default window and a capped count	!265843 (merged) (merge order, not a GitLab block)
2	Bound Duo session artifact listings to a 180-da... (!265854 - merged) • Dheeraj Joshi • 19.6	Session artifacts list: the same window and cap	!265843 (merged) (merge order, not a GitLab block)

Merge the frontend MR first or together with both backend MRs, on the same day. If only the backend merges, sessions older than 180 days drop out of the lists with nothing saying so.

Related to #635324.