Performance optimizations for the license approval rules workflow(behind FF)

Summary

Performance optimizations for the license approval rules workflow to reduce database load and improve job scheduling. #589686

Changes

Worker: ee/app/workers/ci/sync_reports_to_report_approval_rules_worker.rb

• Changed urgency from :high to :low to prevent blocking the high-urgency CPU queue
• Added defer_on_database_health_signal to back off when the main database is under pressure
• The worker primarily performs database reads and writes (loading pipelines, coverage aggregation, approval rule updates). The previous :cpu annotation was incorrect and inconsistent with the urgency :low designation. Changing to :unknown accurately reflects the I/O-bound nature of this work.

Pipeline model: ee/app/models/ee/ci/pipeline.rb

• Rewrote opened_merge_requests_with_head_sha from a Ruby select (loading all MRs into memory) to a SQL EXISTS subquery filtering on merge_request_diffs.head_commit_sha, leveraging the existing index
• This change is disabled by default, behind the feature flag optimize_opened_merge_requests_with_head_sha. #598611
• Now checks both sha and source_sha for matching merge requests

SBOM scanner: ee/lib/gitlab/license_scanning/sbom_scanner.rb

• Added strong_memoize_attr :report to cache the report method, which fetches SBOM artifacts from object storage and runs DB queries
• Report is now computed once per scanner instance instead of once per approval rule

License approvals service: ee/app/services/security/scan_result_policies/update_license_approvals_service.rb

• Memoized target_branch_report by pipeline ID so when multiple approval rules share the same target pipeline, the SBOM report is fetched from object storage only once

Tests

• Added worker urgency assertion
• Added source_sha coverage for the pipeline SQL rewrite
• Added memoization tests for both the scanner and the service