Backport of 'Remove stale merge_request event from Developer flow triggers' to 19.4

What does this MR do and why?

Backports !254232 (merged) to 19-4-stable-ee.

That MR adds a post-deploy migration that cleans up the stale merge_request event (type 6) left on auto-created Developer foundational flow triggers in ai_flow_triggers. These stale triggers start Developer sessions on any merge request event without a goal, and those sessions then stall. Self-managed instances on 19.4 that synced Developer triggers while merge_request was a default event still have these rows.

The commit was cherry-picked cleanly (git cherry-pick -x fbfb2805eb5c) with no changes. The migration keeps its master timestamp (20260928091307), so instances that later upgrade to 19.5 will not run it twice.

Before cherry-picking, I checked that 19-4-stable-ee already has what the migration relies on: the Developer flow default triggers are only assign/mention, ai_flow_triggers has the description/filter columns, the indexes on ai_catalog_item_consumer_id and foundational_flow_reference exist, the tables are gitlab_main_org, and both auto-created description signatures match.

• Fixes #628094 (closed)

Data deletion / recovery: Only auto-created rows are deleted (matched by the sync-generated description and empty filter). The deleted rows are the bug — they cause the Developer flow to start without a goal/prompt on bare MR events, then stall. Recovery: re-syncing the Developer flow (SyncFoundationalFlowsService) recreates any missing default triggers (assign/mention). Only auto-created triggers are affected. The database testing pipeline deleted exactly 1 row on GitLab.com.

Data update: when auto-created triggers has redundant merge_request event along with the two assign and mention events, this migration will update the trigger to have just assign and mention.

Database queries

The migration drives batches from ai_catalog_item_consumers (filtered to developer/v1 items), then operates on ai_flow_triggers using an IN clause on consumer IDs instead of a correlated EXISTS subquery.

Query 1 — Delete triggers where merge_request is the only event:

DELETE FROM ai_flow_triggers
   WHERE ai_catalog_item_consumer_id IN (
     SELECT c.id FROM ai_catalog_item_consumers c
     JOIN ai_catalog_items i ON i.id = c.ai_catalog_item_id
     WHERE i.foundational_flow_reference = 'developer/v1'
     LIMIT 1000
   )
     AND event_types @> '{6}'
     AND description IN (
       'Foundational flow trigger for Developer',
       'Auto-created triggers for Developer'
     )
     AND filter = '{}'::jsonb
     AND cardinality(event_types) = 1;

Query plan

Query 2 — Strip event 6 from triggers that also carry other events:

UPDATE ai_flow_triggers
   SET event_types = array_remove(event_types, 6::smallint)
   WHERE ai_catalog_item_consumer_id IN (
     SELECT c.id FROM ai_catalog_item_consumers c
     JOIN ai_catalog_items i ON i.id = c.ai_catalog_item_id
     WHERE i.foundational_flow_reference = 'developer/v1'
     LIMIT 1000
   )
     AND event_types @> '{6}'
     AND description IN (
       'Foundational flow trigger for Developer',
       'Auto-created triggers for Developer'
     )
     AND filter = '{}'::jsonb
     AND cardinality(event_types) > 1;

query plan

Both queries hit index_ai_flow_triggers_on_ai_catalog_item_consumer_id. Consumer batches are capped at 1,000, keeping each statement well within the 100ms guideline.

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• This MR is backporting a bug fix, documentation update, or spec fix, previously merged in the default branch.
• The MR that fixed the bug on the default branch has been deployed to GitLab.com (not applicable for documentation or spec changes).
• The MR title is descriptive (e.g. "Backport of 'title of default branch MR'"). This is important, since the title will be copied to the patch blog post.
• Required labels have been applied to this merge request
  • severity label and bug subtype labels (if applicable)
  • If this MR fixes a bug that affects customers, the customer label has been applied.
• This MR has been approved by a maintainer (only one approval is required).
• Ensure the E2E Omnibus GitLab EE pipeline (triggered by thee2e:test-on-omnibus-ee job) has succeeded. If it has failed (or shows warnings), investigate the failures. If you determine the failures are unrelated, you may proceed. If you need assistance investigating, request help in the #s_developer_experience Slack channel to confirm the failures are unrelated to the merge request.

Note to the merge request author and maintainer

If you have questions about the patch release process, please:

• Refer to the patch release runbook for engineers and maintainers for guidance.
• Ask questions on the #releases Slack channel (internal only).
• Once the backport has been merged, the commit changes will be automatically deployed to a release environment that can be used for manual validation. See after merging runbook for details.