Resolve cross DB issues in `Security::ProjectStatistics` and `Vulnerabilities/Identifier`

Summary

Group level drop down API implementation (!175902 - merged) introduced two new cross-joins, and these need to be resolved.

This is a similar issue to the join in Vulnerabilities::HistoricalStatistic described here: Resolve vulnerability_historical_statistics cro... (#474140 - closed) • Gregory Havenga (is on PTO from 15 December 2025 to 02 January 2026) • 17.9

Further details

See https://gitlab.com/gitlab-org/gitlab/-/blob/b9e8335618948f9e471650f4857380b1c7ff9e64/ee/app/models/security/project_statistics.rb#L19

      def sum_vulnerability_count_for_group(group)
        Security::ProjectStatistics
          .where(project_id: group.all_project_ids)
          .allow_cross_joins_across_databases(url: 'https://gitlab.com/gitlab-org/gitlab/-/issues/510091')
          .sum(:vulnerability_count)
      end

See https://gitlab.com/gitlab-org/gitlab/-/blob/b9e8335618948f9e471650f4857380b1c7ff9e64/ee/app/models/vulnerabilities/identifier.rb#L57

    def self.search_identifier_name_in_group(group, search_pattern)
      # rubocop:disable Database/AvoidUsingPluckWithoutLimit -- there is a limit
      where(project_id: group.all_project_ids)
        .distinct
        .where("name ILIKE ?", ["%", sanitize_sql_like(search_pattern), "%"].join)
        .order(:name)
        .limit(SEARCH_RESULTS_LIMIT)
        .allow_cross_joins_across_databases(url: 'https://gitlab.com/gitlab-org/gitlab/-/issues/510091')
        .pluck(:name)
      # rubocop:enable Database/AvoidUsingPluckWithoutLimit
    end

Related specs:

bundle exec rspec ee/spec/models/vulnerabilities/identifier_spec.rb ee/spec/graphql/resolvers/vulnerabilities/identifier_search_resolver_spec.rb

Proposal

Leverage vulnerability_statistics.traversal_ids and vulnerability_statistics.project_id to collect all the projects of a group and its subgroup w/o joining the projects and namespaces tables.

Example:

project_ids = ::Vulnerabilities::Statistic.by_group(group).select(:project_id)
Security::ProjectStatistics.where(project_id: project_ids)

SQL query

SELECT 
  "project_security_statistics".* 
FROM 
  "project_security_statistics" 
WHERE 
  "project_security_statistics"."project_id" IN (
    SELECT 
      "vulnerability_statistics"."project_id" 
    FROM 
      "vulnerability_statistics" 
    WHERE 
      "vulnerability_statistics"."traversal_ids" >= '{35}' 
      AND "vulnerability_statistics"."traversal_ids" < '{36}'
  )

This needs to be behind a feature flag, to be rolled out after vulnerability_statistics.archived and traversal_ids have been backfilled. See #513030 (closed)

Implementation plan

• Declare new feature flag for EE.
• Update sum_vulnerability_count_for_group to use Statistic.by_group (behind flag).
• Update search_identifier_name_in_group to use Statistic.by_group (behind flag).
• Update specs. Create vulnerability statistics where necessary.
• Create feature flag rollout issue.
• Update allow_cross_joins_across_databases to link to that FF rollout issue.

Verification

To be tested using the group.vulnerabilityIdentifierSearch GraphQL resource, similar to what's being implemented in Enable identifier filter for group level (!179363 - merged).

query groupIdentifiers($fullPath: ID!, $searchTerm: String!) {
  group(fullPath: $fullPath) {
    id
    vulnerabilityIdentifierSearch(name: $searchTerm)
  }
}

{
  "fullPath": "gitlab-org",
  "searchTerm": "CVE-2024-53899"
}

⚠️ vulnerability_filtering_by_identifier_group has to be enabled for the group.

See #510091 (comment 2326720673)