Reduce SQL queries in SeedFoundationalFlowsService

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

Problem

CascadeSyncFoundationalFlowsWorker exceeds Gitlab::QueryLimiting's 100-query threshold during a fresh seed. Each foundational flow runs ~10 queries through SeedFoundationalFlowsService (SELECT + 4× EXISTS validations + INSERT + autosave). With 8 flows registered, the cascade hits 103-107 queries depending on the test scenario.

CI failure (introduced when adding recommend_reviewers/v1 flow in !230863 (merged)):

Gitlab::QueryLimiting::Transaction::ThresholdExceededError:
  Too many SQL queries were executed in Ai::Catalog::Flows::CascadeSyncFoundationalFlowsWorker:
  a maximum of 100 is allowed but 107 SQL queries were executed

Suggested fixes

  • Preload all existing Ai::Catalog::Item records by foundational_flow_reference in a single query at the start of SeedFoundationalFlowsService#execute (saves N SELECTs)
  • Reduce or batch the validation queries triggered by association autosave (4× redundant EXISTS per flow before INSERT)
  • Skip seeding when an item already matches the registered flow definition (avoid writes on subsequent runs)
Edited by 🤖 GitLab Bot 🤖