Latest ingested SBOM pipeline query is timing out

Summary

Frontend update for Continuous Vulnerability Sc... (#418326 - closed) added a link to the SBOM pipeline to the vulnerability report. On occasion, the SQL query that fetches the SBOM pipeline takes too long, and the vulnerability page returns an HTTP 500 error.

https://sentry.gitlab.net/gitlab/gitlabcom/issues/4184915/?referrer=gitlab_plugin

Further details

Query Timing out: https://sentry.gitlab.net/gitlab/gitlabcom/issues/4184915/events/d4081f0cee594542a1a4f90fe8a217b0/

frontend call to backend

    def security_dashboard_pipeline_data(project)
      pipeline = project.latest_ingested_security_pipeline
      sbom_pipeline = project.latest_ingested_sbom_pipeline
 
      ...

      pipelines
    end

...

    def latest_ingested_sbom_pipeline
      latest_default_branch_pipeline_with_reports(::Ci::JobArtifact.of_report_type(:sbom))
    end

    def latest_default_branch_pipeline_with_reports(reports)
      latest_pipeline_with_reports_for_ref(default_branch, reports)
    end

    def latest_pipeline_with_reports_for_ref(ref, reports)
      all_pipelines.success.newest_first(ref: ref).with_reports(reports).take
    end

    def all_pipelines_for_merge_request
      pipelines_for_merge_request = triggered_by_merge_request
      pipelines_for_branch = triggered_for_branch.for_sha(recent_diff_head_shas(COMMITS_LIMIT))

      Ci::Pipeline.from_union([pipelines_for_merge_request, pipelines_for_branch])
    end

Applied Scopes:

    scope :with_reports, -> (reports_scope) do
      where('EXISTS (?)',
        ::Ci::Build
          .latest
          .with_artifacts(reports_scope)
          .where("#{quoted_table_name}.id = #{Ci::Build.quoted_table_name}.commit_id")
          .select(1)
      )
    end

Steps to reproduce

Example Project

What is the current bug behavior?

Vulnerability report page fails (HTTP 500).

What is the expected correct behavior?

Vulnerability report page renders successfully.

Relevant logs and/or screenshots

Mitigation

Introduce a feature flag, or reuse existing one. This is being discussed in #422920 (comment 1525919524).

Possible fixes

This needs to be addressed in the backend.

• Improve the query.
  • Con: time consuming
• Introduce a new SBOM statics model similar to the vulnerability statistics model. #422920 (comment 1525915661)
  • Con: We might need SBOM statistics in the future, but at this point we don't.
• Write the pipeline ID on Redis. #422920 (comment 1526591670)
  • Pro: Simple