Guard project-scoped Duo Workflow session search against full-table scans

What does this MR do and why?

Fixes the expensive query identified in https://gitlab.com/gitlab-org/gitlab/-/work_items/635083.

When a Developer searches a project's session list via project.duoWorkflowWorkflows(search:), the query issues one ILIKE '%word%' per word against goal and workflow_definition with no index support, scanning every session in the project. At 300,000 sessions in GDK this took 1.5 s of database time per request.

This MR adds four controls behind the duo_workflow_project_search_guard feature flag (gitlab_com_derisk, off by default):

  1. 3-char minimum per word — drops use_minimum_char_limit: false, so 1–2 char words no longer get a %word% wildcard ILIKE. A term made only of short words still falls back to a single exact-match ILIKE (no wildcards).
  2. Word cap at 10 — bounds the number of ILIKE clauses regardless of input length. A quoted phrase counts as one word.
  3. 30-day recency window — restricts the scan to sessions created in the last 30 days, for both project-scoped and user-level searches, so the index scan is bounded by created_at instead of reading every session in the project or of the user. A search made only of session IDs still finds older sessions.
  4. Rate limiter — adds a duo_workflow_project_search ApplicationRateLimiter (30 req/min per user per project) on the GraphQL resolver.

The legacy path (flag off) is unchanged so existing callers are unaffected during rollout. The finder now raises ArgumentError when given both project_path and project_id; the resolver already rejects that pair.

Also adds search_duo_workflow_sessions internal event tracking and a structured application.log entry on every search, recording scope (project vs user) and word count — without logging the raw search term, which may contain sensitive goal text.

The search limits are enforced only here, in the backend. The companion frontend MR (!264025 (merged)) tells users that search only finds sessions created in the last 30 days.

Why cap the number of words

fuzzy_search adds two ILIKE predicates per word (one each for goal and workflow_definition), and the term arrives as a GraphQL variable, so nothing else limits its length. Cost grows roughly linearly with word count (warm, 30-day window: 1 word ~145 ms, 10 words ~666 ms, 50 words ~1.4 s). The cap bounds the work per row; it does not reduce the rows read, which is what the 30-day window is for.

Database

Modified read query: the session search in Ai::DuoWorkflows::WorkflowsFinder#resolve_search, run once per duoWorkflowWorkflows(search:) request. "Before" is duo_workflow_project_search_guard off, "after" is on. Plans ran on gitlab-production-main with project_id = 278964 (gitlab-org/gitlab), using SQL captured from the GraphQL resolver in GDK. The column list is shortened to *; the full statement is in each plan link.

1. Project session search, word with no matches

With no matches, nothing stops the scan early, so this is the expensive case.

SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows"
WHERE "duo_workflows_workflows"."project_id" = <project_id>
AND "duo_workflows_workflows"."workflow_definition" NOT IN (<foundational agent definitions>)
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 = <current_user_id>)
AND ("duo_workflows_workflows"."workflow_definition" ILIKE '%<word>%' OR "duo_workflows_workflows"."goal" ILIKE '%<word>%')
AND (created_at > '<30_days_ago>') -- after only
ORDER BY "duo_workflows_workflows"."created_at" DESC, "duo_workflows_workflows"."id" DESC LIMIT 100 OFFSET 0

Both use index_duo_workflows_workflows_project_environment_created_at; after the change, created_at is part of the index condition.

2. Project session search, 10 words, last word has no matches

Query 1 (after) with 10 ILIKE predicates per column: the worst case under the word cap.

3. Session search with projectPath

The current user's sessions in one project. Plans use the user with the most sessions in the project (87,595).

SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows"
WHERE "duo_workflows_workflows"."user_id" = <user_id> AND "duo_workflows_workflows"."project_id" = <project_id>
AND ("duo_workflows_workflows"."workflow_definition" ILIKE '%<word>%' OR "duo_workflows_workflows"."goal" ILIKE '%<word>%')
AND (created_at > '<30_days_ago>') -- after only
ORDER BY "duo_workflows_workflows"."created_at" DESC, "duo_workflows_workflows"."id" DESC LIMIT 100 OFFSET 0

4. User-level session search (no project)

The current user's sessions across all projects, as searched from the user sessions page and the Duo side panel. Uses index_duo_workflows_workflows_on_user_id_and_created_at (user_id, created_at DESC).

SELECT "duo_workflows_workflows".* FROM "duo_workflows_workflows"
WHERE "duo_workflows_workflows"."user_id" = <user_id>
AND ("duo_workflows_workflows"."workflow_definition" ILIKE '%<word>%' OR "duo_workflows_workflows"."goal" ILIKE '%<word>%')
AND (created_at > '<30_days_ago>') -- after only
ORDER BY "duo_workflows_workflows"."created_at" DESC, "duo_workflows_workflows"."id" DESC LIMIT 100 OFFSET 0

Not shown

  • A common word finds 100 matches within the last 30 days in both versions, so the plan does not change.
  • Searches with only short words, or sorted by UPDATED_DESC, use the same 30-day scan as query 1 (about 140–170 ms warm).

References

Screenshots or screen recordings

N/A — backend-only change, no UI.

How to set up and validate locally

  1. Enable the flag:
    Feature.enable(:duo_workflow_project_search_guard)
  2. Create a project with many Duo Workflow sessions (or use GDK seed data).
  3. Run a GraphQL query:
    query {
      project(fullPath: "your/project") {
        duoWorkflowWorkflows(search: "soft devel", first: 20) {
          nodes { id goal }
        }
      }
    }
  4. Observe in development.log that the query includes a created_at >= predicate and uses the (project_id, created_at DESC) index.
  5. Search soft devel v1 and verify v1 is dropped: the query has %soft% and %devel% patterns but no %v1%. Then search only v1 and verify the query has one exact-match ILIKE 'v1' per column (no % wildcards), not zero ILIKE clauses.
  6. Verify an 11-word search term only generates 10 ILIKE clauses per column.
  7. Check development.log for a duo_workflow_session_search structured log entry with scope, word_count, and char_count.
  8. Search from your user sessions page without a project filter and verify the query also has the created_at > predicate. Then search only a session ID older than 30 days and verify it's still returned.

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 Lindsey Shelton

Merge request reports

Loading
Loading