Reuse open merge requests Duo flows create by idempotency key

Mike Wronskirequested to merge
dedupe-duo-flow-mrs-idempotency-key into master

What does this MR do and why?

Fix Pipeline fires once per failed pipeline. When a pipeline stays broken across several runs (for example broken master), each run opens its own fix merge request for the same root cause. During one incident this produced duplicate MRs (238193, 238209), because nothing told a later run that an earlier run already opened a fix.

This MR adds a nullable idempotency_key column to duo_workflows_workflow_merge_requests, the join table that links a Duo workflow run to the merge request it created. The key derives server-side from the flow definition and the source pipeline ref, for example fix_pipeline/v1:pipeline-ref-sha:<sha256 of the ref>. Before creating a merge request, EE::MergeRequests::CreateService looks up an open MR in the project with the same key and returns it instead of creating a duplicate. If no match exists, it creates the MR and stores the key through the existing LinkArtifactService/ensure_link path. A merged or closed MR stops matching, so the key frees up on its own; there is no worker or extra state to clean up.

Flows opt in per flow through a new reuse_open_merge_request boolean on the foundational flow registry, default false. Only fix_pipeline/v1 sets it, so other flows and workflows without a source pipeline get a nil key and unchanged behavior. The lookup is gated on the existing workflow-context header, the existing update_duo_workflow permission, and read_merge_request on the returned MR, so humans and non-workflow API calls are not affected. Two known limits are accepted for now: the pre-check is not a DB constraint, so two runs racing in the same second can still both create an MR; and keying by ref means a second, unrelated failure on the same branch reuses the open fix MR while it stays open (tracked in issue 607135).

References

Screenshots or screen recordings

Backend-only change; no UI changes.

How to set up and validate locally

  1. In a rails console, create a Fix Pipeline run with a source pipeline and derive its key. Adjust required attributes to your GDK seed data as needed.
project = Project.find_by_full_path('flightjs/Flight')
user = project.owners.first
workflow = Ai::DuoWorkflows::Workflow.create!(project: project, user: user, workflow_definition: 'fix_pipeline/v1', goal: 'test')
pipeline = project.ci_pipelines.last
Ai::DuoWorkflows::WorkflowPipeline.ensure_link(workflow: workflow, artifact: pipeline, link_type: :source)
Ai::DuoWorkflows::WorkflowMergeRequest.idempotency_key_for(workflow)
# => "fix_pipeline/v1:pipeline-ref:<ref>"
  1. Create a merge request twice inside the workflow context. The second call returns the first MR.
params = { title: 'Fix pipeline', source_branch: 'fix-a', target_branch: 'master' }
mr1 = mr2 = nil
Gitlab::ApplicationContext.with_context(duo_workflow_id: workflow.id.to_s) do
  mr1 = MergeRequests::CreateService.new(project: project, current_user: user, params: params.dup).execute
  mr2 = MergeRequests::CreateService.new(project: project, current_user: user, params: params.merge(source_branch: 'fix-b')).execute
end
mr1.id == mr2.id # => true
  1. Close mr1 and repeat step 2's second call. A new merge request is created.

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.

Database review

Table duo_workflows_workflow_merge_requests is new as of 19.2 and classified table_size: small (one row per workflow-linked merge request). The new column starts NULL on all existing rows and is populated only for fix_pipeline/v1 runs. The partial index covers only rows with a key.

Review findings

  • Table size: table_size: small (db/docs/duo_workflows_workflow_merge_requests.yml), one row per workflow-linked merge request, shipped in 19.2. The new column is nullable text written only for fix_pipeline/v1 runs, so growth is a few dozen bytes on a subset of new rows. Far below the 100 GB limit; no exemption needed.
  • Index redundancy: reviewed against the four existing indexes (merge_request_id, namespace_id, project_id, unique (workflow_id, merge_request_id, link_type)). The new partial index covers only keyed rows, so it cannot replace the full project_id index, and no existing index can serve the key lookup. Five indexes total, no redundancy in either direction.
  • Reversibility: down removes the index, the text limit, and the column explicitly (mirrors up in reverse). Verified with a local round-trip; output below.
  • db:gitlabcom-database-testing re-run for the updated migration: https://gitlab.com/gitlab-org/gitlab/-/jobs/15905942459

db:gitlabcom-database-testing has been triggered: https://gitlab.com/gitlab-org/gitlab/-/jobs/15859025218

Migration output

Up:

== 20260812100000 AddIdempotencyKeyToDuoWorkflowsWorkflowMergeRequests: migrating
-- add_column(:duo_workflows_workflow_merge_requests, :idempotency_key, :text, {:if_not_exists=>true})
   -> 0.0456s
-- execute("ALTER TABLE duo_workflows_workflow_merge_requests\nADD CONSTRAINT check_duo_wf_wf_mrs_idempotency_key_limit\nCHECK ( char_length(idempotency_key) <= 255 )\nNOT VALID;\n")
   -> 0.0013s
-- add_index(:duo_workflows_workflow_merge_requests, [:project_id, :idempotency_key], {:where=>"idempotency_key IS NOT NULL", :name=>"index_duo_wf_wf_mrs_on_project_id_and_idempotency_key", :algorithm=>:concurrently})
   -> 0.0030s
== 20260812100000 AddIdempotencyKeyToDuoWorkflowsWorkflowMergeRequests: migrated (0.0921s)

The constraint is added NOT VALID and validated in a post-deployment migration, after the migration pipeline flagged the inline VALIDATE at 158 ms on production data:

== 20260824100000 ValidateIdempotencyKeyLimitOnDuoWorkflowsWorkflowMergeRequests: migrating
-- execute("ALTER TABLE duo_workflows_workflow_merge_requests VALIDATE CONSTRAINT check_duo_wf_wf_mrs_idempotency_key_limit;")
== 20260824100000 ValidateIdempotencyKeyLimitOnDuoWorkflowsWorkflowMergeRequests: migrated (0.0589s)

Down:

== 20260812100000 AddIdempotencyKeyToDuoWorkflowsWorkflowMergeRequests: reverting
-- remove_index(:duo_workflows_workflow_merge_requests, {:algorithm=>:concurrently, :name=>"index_duo_wf_wf_mrs_on_project_id_and_idempotency_key"})
-- execute("ALTER TABLE duo_workflows_workflow_merge_requests DROP CONSTRAINT IF EXISTS check_duo_wf_wf_mrs_idempotency_key_limit")
-- remove_column(:duo_workflows_workflow_merge_requests, :idempotency_key, {:if_exists=>true})
== 20260812100000 AddIdempotencyKeyToDuoWorkflowsWorkflowMergeRequests: reverted (0.0657s)

New query

Emitted by WorkflowMergeRequest.open_merge_request_for before a Duo workflow creates a merge request (only when the flow opts in and the run has a source pipeline):

SELECT "duo_workflows_workflow_merge_requests".*
FROM "duo_workflows_workflow_merge_requests"
INNER JOIN "merge_requests" ON "merge_requests"."id" = "duo_workflows_workflow_merge_requests"."merge_request_id"
WHERE "duo_workflows_workflow_merge_requests"."project_id" = 278964
  AND "duo_workflows_workflow_merge_requests"."idempotency_key" = 'fix_pipeline/v1:pipeline-ref-sha:5f7f4a...'
  AND "merge_requests"."state_id" = 1
ORDER BY "duo_workflows_workflow_merge_requests"."id" ASC
LIMIT 1

Served by the new partial index index_duo_wf_wf_mrs_on_project_id_and_idempotency_key, then a primary-key join to merge_requests. The column has no data yet on production, so a Database Lab plan returns 0 rows by definition; the index is empty until fix_pipeline/v1 runs populate it.

Plan from Database Lab (production clone; column and index applied to the clone with exec first):

Limit  (cost=388.68..388.68 rows=1 width=90) (actual time=0.068..0.069 rows=0 loops=1)
  Buffers: shared hit=8
  ->  Sort  (cost=388.68..388.69 rows=2 width=90) (actual time=0.066..0.067 rows=0 loops=1)
        Sort Key: duo_workflows_workflow_merge_requests.id
        Sort Method: quicksort  Memory: 25kB
        ->  Nested Loop  (cost=0.69..388.67 rows=2 width=90) (actual time=0.034..0.035 rows=0 loops=1)
              ->  Index Scan using index_duo_wf_wf_mrs_on_project_id_and_idempotency_key on public.duo_workflows_workflow_merge_requests  (cost=0.12..116.21 rows=76 width=90) (actual time=0.033..0.033 rows=0 loops=1)
                    Index Cond: ((project_id = 278964) AND (idempotency_key = 'fix_pipeline/v1:pipeline-ref-sha:abc'::text))
              ->  Index Scan using idx_merge_requests_on_unmerged_state_id on public.merge_requests  (cost=0.56..3.58 rows=1 width=8) (never executed)
                    Index Cond: (merge_requests.id = duo_workflows_workflow_merge_requests.merge_request_id)
                    Filter: (merge_requests.state_id = 1)

Time: 7.726 ms (planning: 7.584 ms, execution: 0.142 ms)
Shared buffers: hits 8 (~64 KiB), reads 0

The write path reuses the existing ensure_link upsert (ON CONFLICT (workflow_id, merge_request_id, link_type)); the new column only joins the insert payload.

Edited by Mike Wronski

Merge request reports