Add excludeSourceTypes to duoWorkflowWorkflows

Andrew Fontainerequested to merge
afontaine/630923-exclude-cli-sources-server-side into master

Generated by an AI agent on Andrew's behalf; sources linked inline.

What does this MR do and why?

Part of #630923 (closed)

The Duo panel inbox's "Needs a decision" tab fills with interactive CLI sessions parked in INPUT_REQUIRED after the user left the terminal. The interim fix hid them on the client, but only when the tab fit on one page; with more pages the raw page showed and the badge fell back to 20+. Moving the exclusion to the server needs a way to ask for it.

duoWorkflowWorkflows (user- and project-scoped) gains an optional, experimental excludeSourceTypes: [DuoWorkflowSourceType!] argument, backed by a without_source_types scope on Ai::DuoWorkflows::Workflow. Rows with no recorded source_type are never excluded, so sessions created before the CLI started sending source keep appearing. No schema change; it is one extra predicate on the existing user-scoped query.

The inbox change that uses this argument is in !260068 (merged). The argument must be in production before the inbox MR's flag is enabled anywhere: @gl_introduced cannot guard an argument, and an unknown argument fails both tabs.

References

Screenshots or screen recordings

Not applicable: API-only change.

How to set up and validate locally

  1. Query the API directly in the GraphQL explorer: 
    { duoWorkflowWorkflows(excludeSourceTypes: [DUO_CLI_INTERACTIVE]) { nodes { id sourceType } } }
    Sessions with sourceType: null are still returned.

Specs:

bin/rspec ee/spec/models/ai/duo_workflows/workflow_spec.rb \
  ee/spec/finders/ai/duo_workflows/workflows_finder_spec.rb \
  ee/spec/requests/api/graphql/ai/duo_workflows/workflows_spec.rb

Database review

Generated by an AI agent on Andrew's behalf; sources linked inline.

No migration. The change adds one predicate to the queries Resolvers::Ai::DuoWorkflows::WorkflowsResolver already runs through Ai::DuoWorkflows::WorkflowsFinder. SQL below was captured from the finder and request specs with a sql.active_record subscriber; plans were taken on a Database Lab thin clone of production main (duo_workflows_workflows ≈ 6.8M rows, ≈ 99.8% of them with source_type IS NULL; source_type = 10 does not occur yet). The inbox MR will exclude DUO_CLI_INTERACTIVE and DUO_CLI_ACP (10, 12); plans below use the slightly wider (10, 11, 12). Literals are placeholders. Each plan was run twice; warm numbers are reported.

The new predicate, as Rails renders it (one value renders as !=, several as NOT IN):

("duo_workflows_workflows"."source_type" IS NULL OR "duo_workflows_workflows"."source_type" NOT IN (10, 12))

Q1: user-scoped list with excludeSourceTypes

Default sort, page size the inbox uses (first: 20; the connection maximum is 100).

SELECT "duo_workflows_workflows".*
FROM "duo_workflows_workflows"
WHERE "duo_workflows_workflows"."user_id" = <user_id>
  AND ("duo_workflows_workflows"."source_type" IS NULL OR "duo_workflows_workflows"."source_type" NOT IN (10, 11, 12))
ORDER BY "duo_workflows_workflows"."created_at" DESC
LIMIT 20 OFFSET 0
Case After Before (no predicate)
user with ~340k sessions https://explain.depesz.com/s/OYJ9 — 0.3 ms, 38 buffers https://explain.depesz.com/s/Y5sA — 0.3 ms, 38 buffers
user whose 65 sessions are mostly CLI (52 excluded) https://explain.depesz.com/s/qPqP — 0.6 ms, 71 buffers, 52 rows removed by filter https://explain.depesz.com/s/afHp — 0.2 ms, 26 buffers

Verdict: same plan, Index Scan using index_duo_workflows_workflows_on_user_id_and_created_at with the predicate as a Filter. Extra cost is the excluded rows the scan has to step over before the page fills, bounded by the user's own CLI session count.

Q2: inbox "needs decision" (statuses + excludeSourceTypes)

The caller combines statusGroup: AWAITING_INPUT (status IN (6, 7, 8)) with the exclusion.

SELECT "duo_workflows_workflows".*
FROM "duo_workflows_workflows"
WHERE "duo_workflows_workflows"."user_id" = <user_id>
  AND "duo_workflows_workflows"."status" IN (6, 7, 8)
  AND ("duo_workflows_workflows"."source_type" IS NULL OR "duo_workflows_workflows"."source_type" NOT IN (10, 11, 12))
ORDER BY "duo_workflows_workflows"."created_at" DESC
LIMIT 20 OFFSET 0
Case After Before (no predicate)
user with ~340k sessions, ~118k awaiting input https://explain.depesz.com/s/4P1v — 0.8 ms, 113 buffers https://explain.depesz.com/s/qOXY — 0.9 ms, 113 buffers
user with ~19k sessions, 8 awaiting input https://explain.depesz.com/s/P7oyG — 10.3 ms, 4640 buffers, 18974 rows removed by filter https://explain.depesz.com/s/suyD — 10.0 ms, 4640 buffers, 18974 rows removed by filter

Verdict: identical before and after. The second row is the expensive shape of this query, but it is the status filter that drives it (no index on (user_id, status, created_at)), and that cost exists today without this MR.

Q3: hasNextPage probe for Q2

Emitted by the offset connection when pageInfo is requested (LIMIT first + 1).

SELECT COUNT(*) FROM (
  SELECT 1 AS one
  FROM "duo_workflows_workflows"
  WHERE "duo_workflows_workflows"."user_id" = <user_id>
    AND "duo_workflows_workflows"."status" IN (6, 7, 8)
    AND ("duo_workflows_workflows"."source_type" IS NULL OR "duo_workflows_workflows"."source_type" NOT IN (10, 11, 12))
  LIMIT 21 OFFSET 0
) subquery_for_count
Case After
user with ~340k sessions https://explain.depesz.com/s/PqnF — 1.0 ms, 122 buffers (before: 0.9 ms, 122 buffers)
user with ~19k sessions, 8 awaiting input https://explain.depesz.com/s/xGpv — 12.5 ms, 4640 buffers

Verdict: same index scan as Q2 wrapped in Aggregate; the predicate adds nothing measurable.

Q4: project-scoped list with excludeSourceTypes

Project.duoWorkflowWorkflows goes through for_project(...).from_pipeline.without_other_users_private_sessions(...).

SELECT "duo_workflows_workflows".*
FROM "duo_workflows_workflows"
WHERE "duo_workflows_workflows"."project_id" = <project_id>
  AND "duo_workflows_workflows"."workflow_definition" NOT IN ('chat', 'orbit_agent/v1', 'duo_planner/v1', 'security_analyst_agent/v1', 'analytics_agent/v1', 'ci_expert_agent/v1', 'duo_permissions_assistant/v1', 'support_assistant/v1', 'agentic_chat/v1', 'onboarding_guide/v1', 'flow_creator/v1')
  AND "duo_workflows_workflows"."environment" IN (2, 5)
  AND ((messaging_callback_context ->> 'adapter') IS NULL OR (messaging_callback_context ->> 'adapter') NOT IN ('slack') OR user_id = <user_id>)
  AND ("duo_workflows_workflows"."source_type" IS NULL OR "duo_workflows_workflows"."source_type" NOT IN (10, 11, 12))
ORDER BY "duo_workflows_workflows"."created_at" DESC
LIMIT 20 OFFSET 0
Case After Before (no predicate)
project with ~374k sessions https://explain.depesz.com/s/FDD7e — 3.0 ms, 591 buffers, 564 rows removed by filter https://explain.depesz.com/s/HsyX — 3.2 ms, 591 buffers, 564 rows removed by filter

Verdict: unchanged; Index Scan using index_duo_workflows_workflows_on_project_created_at, predicate joins the existing Filter.

Before/after and index observation

The predicate never changes the plan: every query keeps its (user_id, created_at DESC) or (project_id, created_at DESC) index scan and the exclusion becomes one more Filter condition. Buffers are identical before and after wherever the scan skips no excluded rows, and grow only by the excluded rows skipped (Q1, second case). A negative predicate (IS NULL OR NOT IN) over a column where ≈ 99.8% of rows are NULL is not a candidate for an index, so no index was tested and none is recommended for this MR. The one slow shape found (Q2/Q3 for a user with many sessions but few awaiting input, 4640 buffers) comes from filtering on status after the (user_id, created_at) index and predates this change; if it needs addressing, that is a separate (user_id, status, created_at) index follow-up, not part of this MR.

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.

Edited by Andrew Fontaine

Merge request reports