Add sortable title sub-field and title_asc/title_desc for work items

🤖 AI-authored change.

What does this MR do and why?

Adds order_by=title (title_asc / title_desc) to work item search on both the basic (PostgreSQL) and advanced (Elasticsearch) paths.

title is a text field and Elasticsearch cannot sort on text, so the key was excluded with a TODO. This adds a sortable title.keyword sub-field, the mapping and backfill migrations that populate it, and a gate keeping the new keys unavailable until the backfill finishes — a sort on an unpopulated field returns HTTP 200 in tie-breaker order, i.e. silently wrong.

• ~database: the basic path adds ORDER BY lower(title), id DESC and there is no index on lower(title). Cost at large-namespace row counts is unmeasured.
• Both paths tie-break on id DESC, so paging over duplicate titles is stable.

Backfill cost on GitLab.com. The backfill reindexes the whole work_items index. At 38,120,381 primary documents with batch_size 10,000 and throttle_delay 1 minute, the styleguide formula gives 3,812 minutes — expect 2–3 days, during which order_by=title falls back to created_at_desc.

References

• Specification: specs/advanced-search-filters-and-sorting/SPEC.md in gitlab-org/search-team/artifacts, merged as 6af57ce01254
• #565410
• Database review: the generated basic-search SQL is in $6054339; GDK-scale EXPLAIN (ANALYZE, BUFFERS) plans are in !254391 (comment 3812030537) and a production-clone plan for the issues path is in !254391 (comment 3822626022). The MR's only change to that query is the ORDER BY clause.

---

🤖 Automated change. Mention @johnmason for feedback, or reply #human to escalate to John.

Mechanism and citations (for agents / deep readers)

• Mapping — ee/lib/search/elastic/types/work_item.rb: title.fields.keyword, normalizer: lower_case_normalizer, ignore_above: 1024. Issuable::TITLE_LENGTH_MAX caps titles at 255, so nothing reachable is dropped; an over-limit title is still indexed and sorts into the missing tail. Elasticsearch's normalizer and PostgreSQL's lower(title) disagree on non-ASCII titles; measured examples are in the code comment above the mapping in that file.
• Migrations — AddTitleKeywordToWorkItems overrides completed? because MigrationUpdateMappingsHelper#completed? compares only top-level keys and title already exists. BackfillTitleKeywordInWorkItems carries retry_on_failure, so a persistently failing run reaches migration.fail instead of parking every later migration behind it. Its batch_size and throttle_delay match four sibling work_items reindex migrations.
• Document count — 38,120,381 is elasticsearch_indices_docs_primary for gitlab-production-work_items-20251016-1225-reindex-2000233-0 in the gprd Mimir datasource, latest scrape 2026-06-14. Primary shards only, so it counts each document once; elasticsearch_indices_docs_total reads 76,240,762 across primaries and one replica.
• Runtime figure is a ceiling — process_batch_with_scroll! loops until total_processed reaches QUEUE_THRESHOLD (50,000), so one run can enqueue more than batch_size.
• Gate — ee/lib/search/elastic/sorts/base.rb: sort_available? lets the advanced finders route to PostgreSQL, which is correct in every migration state; build_sort substitutes FALLBACK_SORT_KEY = :created_at_desc and logs for callers that cannot re-route. The fallback must be a key every subclass defines, because Pagination reads query_hash[:sort] unconditionally. The pre-backfill behaviour is documented in doc/api/search.md.
• Basic search — lib/gitlab/search_results.rb calls order_title_asc.with_order_id_desc / order_title_desc.with_order_id_desc, mirroring Issuable.sort_by_attribute. The sort is a bounded top-N above the already-filtered join, capped by SearchService::MAX_PER_PAGE.

What I did not verify

• No postgres.ai access: the query plans are GDK-scale only. The document count is a production metric, but its latest scrape is 2026-06-14, so the index has grown since.
• CI's pinned Elasticsearch versions (7.17 / 8.19 / 9.5) were not exercised locally; local runs used 9.4.4.
• The GraphQL resolver path was not exercised end to end.