Set vulnerability_occurrence_id in SBOM ingestion (!228448) · Merge requests · GitLab.org / GitLab

What does this MR do and why?

The vulnerability_occurrence_id column was added to sbom_occurrences_vulnerabilities as part of the Vulnerabilities Across Contexts initiative, but the ingestion code was never updated to populate it. This MR fixes that gap so new sbom_occurrences_vulnerabilities rows carry the finding (vulnerability_occurrences.id) that links a SBOM occurrence to a vulnerability. Existing rows are backfilled separately in #602158.

Determinism

A vulnerability can have more than one finding (e.g. the same package/version detected via both dependency and container scanning). Sbom::Ingestion::VulnerabilityData must pick a single finding id per vulnerability_id. To make that choice stable across runs, the finding rows are aggregated ORDER BY vulnerability_occurrences.id, so the first-write-wins Ruby dedup deterministically selects the smallest finding id (MIN) per vulnerability.

Nil handling

sbom_occurrences_vulnerabilities.vulnerability_occurrence_id is only transitionally nullable. The column is slated to become NOT NULL once the existing rows are backfilled in #602158, so this ingestion path must not write new NULLs. By construction it never does: vulnerability_finding_ids_map and vulnerability_ids are set together from the same VulnerabilityData row, so every vulnerability_id in a link has a resolved finding id.

The nil branch is a defensive fallback that should never fire. If it ever does, the (sbom_occurrence_id, vulnerability_id) link is still created (rather than dropping a valid vulnerability link), and a warning is logged so we can confirm zero new NULLs are written before the NOT NULL constraint is added.

Note this MR only sets the column for newly ingested links. Existing rows are excluded from ingestion (new_links = ingested_links - existing_links) and are backfilled separately by #602158, not by re-ingestion.

Database

The read query lives in ee/app/services/sbom/ingestion/vulnerability_data.rb. It builds a VALUES list CTE from the occurrence maps and joins it against vulnerability_occurrences (findings):

WITH occurrence_maps (name, version, path) AS (VALUES ($1, $2, $3), ...)
SELECT
  occurrence_maps.name,
  occurrence_maps.version,
  occurrence_maps.path,
  json_agg(
    json_build_object(
      'vulnerability_id', vulnerability_occurrences.vulnerability_id,
      'finding_id', vulnerability_occurrences.id
    )
    ORDER BY vulnerability_occurrences.id
  ) AS vulnerability_data,
  MAX(vulnerability_occurrences.severity) AS highest_severity,
  COUNT(vulnerability_occurrences.id) AS vulnerability_count
FROM vulnerability_occurrences
JOIN occurrence_maps
  ON occurrence_maps.name = (vulnerability_occurrences.location -> 'dependency' -> 'package' ->> 'name')::text
  AND occurrence_maps.version = (vulnerability_occurrences.location -> 'dependency' ->> 'version')::text
  AND occurrence_maps.path = COALESCE(
    vulnerability_occurrences.location ->> 'file',
    vulnerability_occurrences.location ->> 'image'
  )::text
WHERE vulnerability_occurrences.report_type IN (1, 2) /* dependency_scanning, container_scanning */
  AND vulnerability_occurrences.project_id = $n
  /* ... active + unresolved scopes ... */
GROUP BY occurrence_maps.name, occurrence_maps.version, occurrence_maps.path;

The only behavioural change to this query is the addition of ORDER BY vulnerability_occurrences.id inside json_agg. The join/filter shape is unchanged, so the plan is equivalent to the pre-existing query.

Query Plans

Captured on postgres.ai (Database Lab, gitlab-production-sec), which is where the vulnerability_occurrences and sbom_occurrences_vulnerabilities tables live (sec-decomposed). Timings are on a thin clone so they run slower than production, but the plan shape and buffers match prod.

Representative project: project_id = 278964 (gitlab-org/gitlab), which has 4,489 dependency_scanning + container_scanning findings. The occurrence_maps CTE is seeded with 30 real (name, version, path) tuples from that project to mirror a real ingestion batch.

The only behavioural change is the aggregate: OLD array_to_json(array_agg(vulnerability_id)), NEW json_agg(json_build_object(...) ORDER BY vulnerability_occurrences.id). The concern was whether the new ORDER BY inside json_agg forces an extra per-group Sort node that array_agg did not need.

Conclusion: it does not add a Sort node. Both plans already contain one Sort (quicksort, 36kB, 149 rows) because the GROUP BY occurrence_maps.name, version, path feeds a GroupAggregate that needs sorted input. The ORDER BY vulnerability_occurrences.id only appends vulnerability_occurrences.id as a trailing key on that same existing Sort. Same node, same method, same memory. Warm-cache execution is 30.7 ms (NEW) vs 26.0 ms (OLD) with identical buffers (shared hit ~10826, read=0), so the extra sort key costs a low single-digit millisecond in-memory sort of 149 rows. Negligible.

(The NEW cold-run below shows 264 ms because it was the first query to touch vulnerabilities_pkey pages on the fresh clone; I/O read=234 ms is clone cache warming, not the sort. The warm re-run confirms the real delta.)

NEW: json_agg(... ORDER BY vulnerability_occurrences.id) - EXPLAIN (ANALYZE, BUFFERS)

Warm re-run: execution 30.7 ms, planning 5.6 ms, buffers shared hit=10826 read=0, Sort quicksort 36kB (149 rows). Cold first-run below:

GroupAggregate  (cost=3298.09..3298.12 rows=1 width=138) (actual time=264.206..264.395 rows=22 loops=1)
  Group Key: (name), (version), (COALESCE(file, image))
  Buffers: shared hit=10530 read=296 dirtied=47
  WAL: records=48 fpi=47 bytes=316905
  I/O Timings: read=234.882 write=0.000
  ->  Sort  (cost=3298.09..3298.09 rows=1 width=114) (actual time=264.182..264.190 rows=149 loops=1)
        Sort Key: (name), (version), (COALESCE(file, image)), vulnerability_occurrences.id
        Sort Method: quicksort  Memory: 36kB
        Buffers: shared hit=10530 read=296 dirtied=47
        ->  Nested Loop  (cost=2520.03..3298.08 rows=1 width=114) (actual time=27.329..263.777 rows=149 loops=1)
              Buffers: shared hit=10524 read=296 dirtied=47
              ->  Nested Loop  (cost=2519.46..3294.49 rows=1 width=114) (actual time=24.234..27.225 rows=178 loops=1)
                    Buffers: shared hit=9944 read=6
                    ->  Limit  (cost=2518.76..2519.59 rows=30 width=96) (actual time=23.312..23.378 rows=30 loops=1)
                          ->  HashAggregate  (cost=2518.76..2565.35 rows=1694 width=96) (actual time=23.310..23.360 rows=30 loops=1)
                                Group Key: name, version, COALESCE(file, image)
                                Batches: 1  Memory Usage: 121kB
                                ->  Index Scan using index_vulnerability_occurrences_for_override_uuids_logic on vulnerability_occurrences vo  (cost=0.57..2506.05 rows=1695 width=96) (actual time=0.040..22.010 rows=4489 loops=1)
                                      Index Cond: ((project_id = 278964) AND (report_type = ANY ('{1,2}')))
                                      Filter: (name IS NOT NULL)
                    ->  Index Scan using i_vuln_occurrences_on_proj_report_loc_dep_pkg_ver_file_img on vulnerability_occurrences  (cost=0.70..25.82 rows=1 width=230) (actual time=0.072..0.124 rows=5.93 loops=30)
                          Index Cond: ((project_id = 278964) AND (name = vo.name) AND (version = vo.version) AND (COALESCE(file, image) = vo.COALESCE(file, image)))
                          Index Searches: 90
                          Buffers: shared hit=749 read=6
              ->  Index Scan using vulnerabilities_pkey on vulnerabilities  (cost=0.57..3.59 rows=1 width=8) (actual time=1.327..1.327 rows=0.84 loops=178)
                    Index Cond: (id = vulnerability_occurrences.vulnerability_id)
                    Filter: ((NOT resolved_on_default_branch) AND (state = ANY ('{1,4}')))
                    Buffers: shared hit=580 read=290 dirtied=47
                    I/O Timings: read=233.032
Settings: jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB'

Time: 270.230 ms (planning 5.682 ms, execution 264.548 ms, I/O read 234.882 ms)
Shared buffers: hits 10530, reads 296, dirtied 47, writes 0

OLD: array_to_json(array_agg(vulnerability_id)) - EXPLAIN (ANALYZE, BUFFERS)

GroupAggregate  (cost=3298.09..3298.12 rows=1 width=138) (actual time=25.777..25.846 rows=22 loops=1)
  Group Key: (name), (version), (COALESCE(file, image))
  Buffers: shared hit=10823
  ->  Sort  (cost=3298.09..3298.09 rows=1 width=114) (actual time=25.763..25.771 rows=149 loops=1)
        Sort Key: (name), (version), (COALESCE(file, image))
        Sort Method: quicksort  Memory: 36kB
        Buffers: shared hit=10823
        ->  Nested Loop  (cost=2520.03..3298.08 rows=1 width=114) (actual time=24.033..25.661 rows=149 loops=1)
              Buffers: shared hit=10820
              ->  Nested Loop  (cost=2519.46..3294.49 rows=1 width=114) (actual time=24.018..24.723 rows=178 loops=1)
                    Buffers: shared hit=9950
                    ->  Limit  (cost=2518.76..2519.59 rows=30 width=96) (actual time=23.944..23.955 rows=30 loops=1)
                          ->  HashAggregate  (cost=2518.76..2565.35 rows=1694 width=96) (actual time=23.942..23.951 rows=30 loops=1)
                                Group Key: name, version, COALESCE(file, image)
                                Batches: 1  Memory Usage: 121kB
                                ->  Index Scan using index_vulnerability_occurrences_for_override_uuids_logic on vulnerability_occurrences vo  (cost=0.57..2506.05 rows=1695 width=96) (actual time=0.043..22.570 rows=4489 loops=1)
                                      Index Cond: ((project_id = 278964) AND (report_type = ANY ('{1,2}')))
                                      Filter: (name IS NOT NULL)
                    ->  Index Scan using i_vuln_occurrences_on_proj_report_loc_dep_pkg_ver_file_img on vulnerability_occurrences  (cost=0.70..25.82 rows=1 width=230) (actual time=0.011..0.024 rows=5.93 loops=30)
                          Index Cond: ((project_id = 278964) AND (name = vo.name) AND (version = vo.version) AND (COALESCE(file, image) = vo.COALESCE(file, image)))
                          Index Searches: 90
                          Buffers: shared hit=755
              ->  Index Scan using vulnerabilities_pkey on vulnerabilities  (cost=0.57..3.59 rows=1 width=8) (actual time=0.005..0.005 rows=0.84 loops=178)
                    Index Cond: (id = vulnerability_occurrences.vulnerability_id)
                    Filter: ((NOT resolved_on_default_branch) AND (state = ANY ('{1,4}')))
                    Buffers: shared hit=870
Settings: jit = 'off', work_mem = '100MB', random_page_cost = '1.5', seq_page_cost = '4', effective_cache_size = '338688MB'

Time: 31.728 ms (planning 5.734 ms, execution 25.994 ms, I/O read 0.000 ms)
Shared buffers: hits 10823, reads 0, dirtied 0, writes 0

The write is a bulk_insert! into sbom_occurrences_vulnerabilities (see ee/app/services/sbom/ingestion/tasks/ingest_occurrences_vulnerabilities.rb); it now sets the nullable vulnerability_occurrence_id in addition to the existing columns.

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.

Edited Sep 03, 2026 by Gregory Havenga

Set vulnerability_occurrence_id in SBOM ingestion