Consistency check: Backfill vulnerability_occurrence_id on sbom_occurrences_vulnerabilities

Everyone can contribute. Help move this issue forward while earning points, leveling up and collecting rewards.

Summary

Implement consistency check and remediation to backfill vulnerability_occurrence_id on sbom_occurrences_vulnerabilities table.

Parent Epic

&20929 - 0. Secure Data Consistency Worker

Why This Matters

The vulnerability_occurrence_id column was added to this table as part of VAC, but no backfill migration exists. This was discovered during analysis of VAC-related tables.

Check Logic

SELECT sov.id, sov.vulnerability_id
FROM sbom_occurrences_vulnerabilities sov
WHERE sov.vulnerability_occurrence_id IS NULL
  AND sov.vulnerability_id IS NOT NULL;

Also verify existing non-NULL values are valid:

SELECT sov.id, sov.vulnerability_occurrence_id
FROM sbom_occurrences_vulnerabilities sov
WHERE sov.vulnerability_occurrence_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1 FROM vulnerability_occurrences vo 
    WHERE vo.id = sov.vulnerability_occurrence_id
  );

Remediation Logic

Backfill from vulnerabilities.finding_id:

SbomOccurrencesVulnerability
  .where(vulnerability_occurrence_id: nil)
  .joins(:vulnerability)
  .where.not(vulnerabilities: { finding_id: nil })
  .each_batch do |batch|
    batch.update_all(
      "vulnerability_occurrence_id = (
        SELECT finding_id FROM vulnerabilities 
        WHERE vulnerabilities.id = sbom_occurrences_vulnerabilities.vulnerability_id
      )"
    )
  end

Feature Flags

Flag Purpose
secure_consistency_check_occurrence_id_fill Enable check
secure_consistency_fix_occurrence_id_fill Enable remediation

Acceptance Criteria

  • Check detects NULL vulnerability_occurrence_id where backfill is possible
  • Check detects invalid (orphaned) vulnerability_occurrence_id values
  • Remediation backfills from vulnerabilities.finding_id
  • Batched processing
  • Logging includes record counts and project context

Note

vulnerability_user_mentions was also identified as missing a backfill, but @bwill has identified that table is redundant and will likely be removed.

Related

Edited by 🤖 GitLab Bot 🤖