Speed up assignee and reviewer pickers for Duo service accounts

Jaydip Pansuriyarequested to merge
631005-scope-duo-status-flow-triggers into master

What does this MR do and why?

The assignee and reviewer pickers (autocompleteUsers) get very slow when Duo flow service accounts show up in the results. To show the AI badge, EE::Autocomplete::UsersFinder preloaded ai_flow_triggers and child_item_consumers_flow_triggers for every user in the results. For a foundational flow service account, this loads one item consumer and one flow trigger for every project where the flow is enabled in the whole top-level group. Then DuoStatusType threw away everything except the current project's triggers in Ruby.

On GitLab.com, one duo search in the assignee picker of gitlab-org/gitlab loaded ~117k item consumers and ~36k flow triggers, and took 8-10s (a normal search takes ~0.25s).

This MR removes that preload. flowTriggerEvents is now loaded with one batched query per request, limited to the current project (or the group's direct projects) and the service accounts in the results, using Ai::FlowTrigger.applicable_to_container. The inherited group triggers come from the same query, so the separate inherited trigger preload in the resolver is also removed.

The results are the same as before. The query is now scoped in SQL, so we return ~5 rows instead of ~153k.

References

How to set up and validate locally

Steps to verify the picker badges and query count

  1. Make sure the inherited triggers flag is on:

    Feature.enable(:ai_catalog_group_inherited_consumers)

  2. Pick a project where Duo flow service accounts are members and have flow triggers, then compare the new lookup with the old in-memory logic in rails console:

    project = Project.find_by_full_path('<group>/<project>')
service_accounts = project.authorized_users.select { |u| u.service_account? && u.composite_identity_enforced? }

Types::Users::DuoStatusType.flow_trigger_events_by_user(project, service_accounts.map(&:id))
# => { <service_account_id> => [0, 1], ... } only this project's (ans

service_accounts.to_h do |u|
  [u.id, (u.ai_flow_triggers + u.child_item_consumers_flow_triggers).uniq.select(&:active?)
    .select { |t| t.project_id == project.id }.flat_map(&:event_types).uniq]
end
# => same events for the project triggers (inherited group triggers are added on top by the new lookup)

  3. Check the UI:

    1. Open an issue in that project, and turn on the performance bar (p + b).
    2. Open Assignees and search for one of the Duo service accounts (for example duo).
    3. Service accounts with an ASSIGN trigger in this project show the AI badge and can be selected. Others show No triggers for this event.
    4. Open an MR and do the same in Reviewers. Accounts with an ASSIGN_REVIEWER trigger can be selected.
    5. Open an epic in the group and do the same in Assignees. It uses the triggers of the group's direct projects.
    6. In the performance bar, open the graphql request (add it with the + button if it's not listed) and open pg. There is one SELECT "ai_flow_triggers".* query filtered by project_id, and no ai_catalog_item_consumers ... parent_item_consumer_id IN (...) preload.

  4. Turn off the flag and check that inherited group triggers are no longer returned (the account shows No triggers for this event):

    Feature.disable(:ai_catalog_group_inherited_consumers)

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.

DB Query Plan

Types::Users::DuoStatusType.flow_trigger_events_by_user when parent_entity is ::Project

Types::Users::DuoStatusType.flow_trigger_events_by_user when parent_entity is ::Namespace

Two queries will fire for the namespace branch from the application_to_namespace method. First one finds all the direct projects, and second one finds the AI triggers belonging to the group and those projects.

Related to #631005 (closed)

Edited by Jaydip Pansuriya

Merge request reports