Add backend filtering for @ autocomplete

What does this MR do and why?

Allows backend filtering for the mention autocomplete so that we don't have to render a very large JSON payload when in large projects or groups or when the user has a lot of authorized groups.

Query plans

The current change adds an extra WHERE clause and an ORDER clause. Plans are very similar to the existing one where we fetch all results. We just add an extra filtering and sorting step.

1. Project members (When in an issue or MR)

   • Before: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24506/commands/78187
   • After: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24506/commands/78189

2. Group members (When in an epic)

   • Before: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24506/commands/78201
   • After: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24506/commands/78202

3. User's authorized groups (When in an issue, epic, or MR. This is used for mentioning groups)

   • Before: https://postgres.ai/console/gitlab/gitlab-production-tunnel-pg12/sessions/24506/commands/78206
   • After: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/24506/commands/78211

How to set up and validate locally

1. Enable mention_autocomplete_backend_filtering feature flag.
2. Open an issue / MR / Epic
3. Type @ in a comment text area and try autocompleting users or groups

You can check the network inspector to confirm that we're making requests with the search param and returning limited results.

MR acceptance checklist

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

• I have evaluated the MR acceptance checklist for this MR.

Related to #209965 (closed)

changed milestone to %Backlog

added Category:Team Planning [deprecated] Accepting merge requests backend backstage [DEPRECATED] bugperformance devopsplan frontend groupproject management missed:12.10 missed:12.9 priority3 sectiondev severity2 workflowscheduling labels

assigned to @engwan

Hey @engwan, backstage [DEPRECATED] is being deprecated in favor of typefeature, featureaddition, typemaintenance, maintenancepipelines, and maintenanceworkflow to improve the identification of these type of changes. Please see https://about.gitlab.com/handbook/engineering/metrics/#data-classification for further guidance. This message was generated automatically. You're welcome to improve it.

removed backstage [DEPRECATED] label

added typebug label

added databasereview pending label

added 1 commit

• e9c02577 - Add backend filtering for @ autocomplete

Compare with previous version

@engwan Some end-to-end (E2E) tests should run based on the stage label.

Please start the trigger-omnibus-and-follow-up-e2e job in the qa stage and ensure tests in the follow-up-e2e:package-and-test-ee pipeline pass before this MR is merged. (E2E tests are computationally intensive and don't run automatically for every push/rebase, so we ask you to run this job manually at least once.)

To run all E2E tests, apply the pipeline:run-all-e2e label and run a new pipeline.

E2E test jobs are allowed to fail due to flakiness. See current failures at the latest pipeline triage issue.

Once done, apply the emoji on this comment.

Team members only: for any questions or help, reach out on the internal #quality Slack channel.

added 1 commit

• cbb95f3b - Add backend filtering for @ autocomplete

Compare with previous version

E2E Test Result Summary

allure-report-publisher generated test report!

e2e-review-qa: test report for 62409e4c

expand test summary

+-----------------------------------------------------------------------+
|                            suites summary                             |
+------------------+--------+--------+---------+-------+-------+--------+
|                  | passed | failed | skipped | flaky | total | result |
+------------------+--------+--------+---------+-------+-------+--------+
| Monitor          | 4      | 0      | 0       | 0     | 4     | ✅     |
| Plan             | 54     | 0      | 1       | 0     | 55    | ✅     |
| Govern           | 3      | 0      | 0       | 0     | 3     | ✅     |
| Create           | 6      | 0      | 4       | 0     | 10    | ✅     |
| Package          | 0      | 0      | 1       | 0     | 1     | ➖     |
| Data Stores      | 2      | 0      | 0       | 0     | 2     | ✅     |
| Framework sanity | 0      | 0      | 1       | 0     | 1     | ➖     |
+------------------+--------+--------+---------+-------+-------+--------+
| Total            | 69     | 0      | 7       | 0     | 76    | ✅     |
+------------------+--------+--------+---------+-------+-------+--------+

e2e-test-on-gdk: test report for 62409e4c

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Plan        | 55     | 0      | 0       | 0     | 55    | ✅     |
| Govern      | 74     | 0      | 0       | 0     | 74    | ✅     |
| Verify      | 31     | 0      | 0       | 0     | 31    | ✅     |
| Create      | 54     | 0      | 7       | 0     | 61    | ✅     |
| Release     | 5      | 0      | 0       | 0     | 5     | ✅     |
| Monitor     | 7      | 0      | 0       | 0     | 7     | ✅     |
| Data Stores | 23     | 0      | 0       | 0     | 23    | ✅     |
| Package     | 4      | 0      | 1       | 0     | 5     | ✅     |
| Analytics   | 2      | 0      | 0       | 0     | 2     | ✅     |
| Manage      | 0      | 0      | 1       | 0     | 1     | ➖     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 255    | 0      | 9       | 0     | 264   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

e2e-package-and-test: test report for 62409e4c

expand test summary

+------------------------------------------------------------------+
|                          suites summary                          |
+-------------+--------+--------+---------+-------+-------+--------+
|             | passed | failed | skipped | flaky | total | result |
+-------------+--------+--------+---------+-------+-------+--------+
| Plan        | 166    | 0      | 6       | 2     | 172   | ✅     |
| Govern      | 6      | 0      | 0       | 0     | 6     | ✅     |
| Create      | 16     | 0      | 4       | 0     | 20    | ✅     |
| Monitor     | 8      | 0      | 0       | 0     | 8     | ✅     |
| Data Stores | 4      | 0      | 0       | 0     | 4     | ✅     |
| Package     | 0      | 0      | 2       | 0     | 2     | ➖     |
+-------------+--------+--------+---------+-------+-------+--------+
| Total       | 200    | 0      | 12      | 2     | 212   | ✅     |
+-------------+--------+--------+---------+-------+-------+--------+

added 2123 commits

• cbb95f3b...024786ea - 2122 commits from branch master
• 16bc8c99 - Add backend filtering for @ autocomplete

Compare with previous version

added 1 commit

• f8f22fff - Add backend filtering for @ autocomplete

Compare with previous version

removed [deprecated] Accepting merge requests label

removed workflowscheduling label

removed missed:12.9 label

removed missed:12.10 label

changed milestone to %16.5

Slow tests detected in this merge request, might be related with changed RSpec files.

Job	File	Name	Duration	Expected duration
#5135371321	spec/models/member_spec.rb#L1041	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group create member when lease cannot be obtained only schedules one job	59.89 s	< 45.4 s
#5135371321	spec/models/member_spec.rb#L1051	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group when member exists update member when access level was changed when lease is obtained schedules a job in the future	60.12 s	< 45.4 s
#5135371321	spec/models/member_spec.rb#L1064	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group when member exists destroy member when lease is obtained schedules a job in the future	60.15 s	< 45.4 s
#5135370464	spec/models/member_spec.rb#L1041	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group create member when lease cannot be obtained only schedules one job	59.84 s	< 45.4 s
#5135370464	spec/models/member_spec.rb#L1051	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group when member exists update member when access level was changed when lease is obtained schedules a job in the future	60.17 s	< 45.4 s
#5135370464	spec/models/member_spec.rb#L1064	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group when member exists destroy member when lease is obtained schedules a job in the future	60.2 s	< 45.4 s
#5290058934	spec/models/member_spec.rb#L1029	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group create member when lease cannot be obtained only schedules one job	59.75 s	< 45.4 s
#5290058934	spec/models/member_spec.rb#L1039	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group when member exists update member when access level was changed when lease is obtained schedules a job in the future	60.19 s	< 45.4 s
#5290058934	spec/models/member_spec.rb#L1052	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group when member exists destroy member when lease is obtained schedules a job in the future	60.25 s	< 45.4 s
#5290061122	spec/models/member_spec.rb#L1029	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group create member when lease cannot be obtained only schedules one job	59.53 s	< 45.4 s
#5290061122	spec/models/member_spec.rb#L1039	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group when member exists update member when access level was changed when lease is obtained schedules a job in the future	60.18 s	< 45.4 s
#5290061122	spec/models/member_spec.rb#L1052	Member when after_commit :update_highest_role member_type: :group_member, source_type: :group when member exists destroy member when lease is obtained schedules a job in the future	60.21 s	< 45.4 s
#5722963240	spec/features/dashboard/group_spec.rb#L16	Dashboard Group creates new group	94.61 s	< 50.13 s
#5730058900	spec/features/dashboard/issues_spec.rb#L24	Dashboard Issues behaves like a "Your work" page with sidebar and breadcrumbs shows the "Your work" sidebar	61.9 s	< 50.13 s

added rspec:slow test detected label