Remove stale merge_request event from Developer flow triggers

What does this MR do and why?

Adds a post-deploy migration to clean up stale merge_request event type (integer 6) from auto-created Developer foundational flow triggers in the ai_flow_triggers table.

Root cause: !249084 (merged) added merge_request to the Developer flow's default triggers. The sync service auto-created trigger rows with that event and an empty action filter, matching all MR sub-events (approved, ready, code_conflict, merged). !250220 (merged) removed it from defaults, but sync is create-only and never prunes, so stale triggers persist.

What the migration does:

• Targets only auto-created triggers for developer/v1, matched by description signature and empty filter. Non auto-created triggers are never touched.
• Deletes rows where merge_request is the sole event type.
• Strips event 6 from rows that also carry assign/mention.

Two description signatures are matched:

• "Foundational flow trigger for Developer" (SyncFoundationalFlowsService)
• "Auto-created triggers for Developer" (ItemConsumers::CreateService)

Batching strategy: Batches are driven from the consumer side — first resolving ai_catalog_items with foundational_flow_reference = 'developer/v1', then iterating over ai_catalog_item_consumers filtered to those items. This avoids scanning all 1.4M rows in ai_flow_triggers and eliminates the correlated EXISTS subquery that caused per-statement times exceeding 100ms. All three hops are indexed.

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.

Migration runtime

The database testing pipeline reports ~12 minutes wall-clock, but only ~32s is actual DB execution — the rest is test-harness overhead. Well within the post-deployment migration budget.

References

• Fixes #628094 (closed)
• Cause: !249084 (merged)
• Fix for new syncs: !250220 (merged)

Screenshots or screen recordings

N/A — data cleanup only, no UI changes.

How to set up and validate locally

Method 1 — Migration spec

bundle exec rspec spec/migrations/db/post_migrate/20260928091307_remove_merge_request_event_from_developer_ai_flow_triggers_spec.rb

Method 2 — End-to-end in GDK

1. Run gdk switch e2e-628094-before-bug and gdk restart
2. Create a project with foundational flows enabled. Verify Developer triggers have only assign + mention events for the project created and project existing already.

Existing project trigger	newly created project trigger

3. Run gdk switch e2e-628094-after-bug and gdk restart
4. Ran the following rails console command to trigger the sync for the above projects

# In rails console
user = User.find_by_username('root')
project = Project.find_by_full_path('your-group/your-project')

# This is exactly what project creation and re-sync call:
Ai::Catalog::Flows::SyncFoundationalFlowsService.new(project, current_user: user).execute

5. Create another project. Verify Developer triggers now has stale merge request event

Existing project trigger	newly created project trigger before bug branch	newly created project trigger after bug branch

6. Run gdk switch duo/fix/628094-stale-developer-mr-trigger and gdk restart
7. At gitlab repo directory, run bundle exec rails db:migrate:down:main VERSION=20260928091307 and bundle exec rails db:migrate:up:main VERSION=20260928091307.
8. Verify stale triggers are cleaned up — the merge_request event is removed from auto-created Developer triggers.

Existing project trigger	newly created project trigger before bug branch	newly created project trigger after bug branch

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.