SbomScan result caching - models and db changes

What does this MR do and why?

Adds database layer and models for SBOM scan result caching which enables the reuse of scan results for scans when dependencies haven't changed.

Currently, SBOM scans are triggered on every pipeline run regardless of whether dependencies have changed, wasting computational resources by processing identical dependencies and generating the same results. This MR introduces an SbomScanResult model which will allow SbomScan instances to reference the same result.

References

Parent MR: Avoid triggering a re-scan when dependencies ha... (#562694) • Igor Frenkel • 18.7 • Needs attention

This MR is one of 3

1. Current 👉 SbomScan result caching - models and db changes (!213584) • Igor Frenkel • 18.7
2. SbomScan result caching - service and api layer (!213586) • Igor Frenkel • 18.7
3. Pending: observability events for caching

Analyzer MR: Add sbom scan caching support (gitlab-org/security-products/analyzers/dependency-scanning!400) • Igor Frenkel • 18.7

Change summary

• Migrations
  • New table sbom_vulnerability_scan_results allows scan result re-use.
  • Modify sbom_vulnerability_scans to add a foreign key to added table.
  • Modify sbom_vulnerability_scans to add an sbom_digest which will allow clients (e.g. DS analyzer) to look for SBOMs that have already been scanned.
• Models
  • Add SbomScanResult
  • Add association between new model and SbomScan
  • Add uploader like SbomScanUploader but simpler since we don't have to handle user uploads.
  • SbomScan#result_file is changed to serve either the "old" results (stored in column) or new results (stored in association).

Database

• Results of the migration checks are here: !213584 (comment 2916768128)

• The biggest change (found in the 2nd MR) is a query against the sbom_vulnerability_scans table to fetch results by sbom_digest (invoked by the SbomScanResultCachingService in the 2nd MR). Example:

  SELECT "sbom_vulnerability_scans".* FROM "sbom_vulnerability_scans" WHERE "sbom_vulnerability_scans"."project_id" = 35 AND "sbom_vulnerability_scans"."sbom_digest" = 'sha256v1-abc123def456' AND "sbom_vulnerability_scans"."sbom_digest" IS NOT NULL AND "sbom_vulnerability_scans"."status" = 2 ORDER BY created_at desc LIMIT 1

  postgres.ai console was down at the time of adding this, so there is no explain but there's also no sbom_digest data. So the closest estimate would be scoped to a project_id lookup.

Rollout plan

See issue for detail: #562694

How to set up and validate locally

This can be validated at the end of the MR stack.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

• This MR is added to the 18.7 release post, or the release post item::no label is added