Add Sbom::Occurrence.for_tracked_context scope for reachability

What does this MR do

Part of #602156 (closed) (epic &21396 Complete at Scale Vulnerability Management).

Reachability is stored on sbom_occurrences, which is project global. To support reachability per tracked branch, the SBOM query layer needs to scope occurrences to a tracked context. This MR adds a for_tracked_context scope on Sbom::Occurrence that filters occurrences to those linked, via sbom_occurrence_refs, to a given Security::ProjectTrackedContext.

Behaviour:

• With a context, returns only occurrences that have an OccurrenceRef for that context (correlated EXISTS subquery).
• With nil, returns all occurrences, preserving existing default branch behaviour so current callers are unaffected.

This does not change Vulnerability#reachability or the GraphQL consumer. Those are handled in the companion Sec-Infra MR for #602157.

Why

Threading tracked context through the existing reachability query path is the foundation for showing reachability on non default branches in the vulnerability report, vulnerability details, and pipeline security tab.

Testing

Added .for_tracked_context model specs (linked context match, different context excluded, nil returns all). Ran locally against PostgreSQL 17:

ee/spec/models/sbom/occurrence_spec.rb -e for_tracked_context
3 examples, 0 failures

Database review

This change adds the for_tracked_context scope, a correlated EXISTS against sbom_occurrence_refs, used by Vulnerability#reachability and the GraphQL reachability resolver. Plans were captured on gitlab-production-sec via postgres.ai (Joe).

The EXISTS predicate is served by idx_sbom_occurrence_refs_on_sec_prj_trck_cnxt_id and correlates to sbom_occurrences by primary key, so there is no sequential scan on the filtered data. In the plans below the InitPlan that does ORDER BY id DESC LIMIT 1 is only a scaffold to supply a real security_project_tracked_context_id; the application passes a bound parameter, so that node is not part of the production query. The id chosen happens to be the largest tracked context (1987 refs), so these numbers are a realistic upper bound for a single context.

Model query, as used by Vulnerability#reachability (postgres.ai #154701):

SELECT sbom_occurrences.* FROM sbom_occurrences
WHERE EXISTS (
  SELECT 1 FROM sbom_occurrence_refs
  WHERE sbom_occurrence_refs.security_project_tracked_context_id = $1
    AND sbom_occurrence_refs.sbom_occurrence_id = sbom_occurrences.id
) AND sbom_occurrences.reachability IN (1, 2);

Plan and buffers (#154701)

 Nested Loop  (cost=1.75..4811.58 rows=11 width=398) (actual time=12.046..12.049 rows=0 loops=1)
   Buffers: shared hit=10201
   InitPlan 1
     ->  Limit  (cost=0.57..0.61 rows=1 width=16) (actual time=0.039..0.040 rows=1 loops=1)
           Buffers: shared hit=5
           ->  Index Scan Backward using sbom_occurrence_refs_pkey on public.sbom_occurrence_refs sbom_occurrence_refs_1  (cost=0.57..4357501.13 rows=100522936 width=16) (actual time=0.036..0.037 rows=1 loops=1)
                 Buffers: shared hit=5
   ->  Index Scan using idx_sbom_occurrence_refs_on_sec_prj_trck_cnxt_id on public.sbom_occurrence_refs  (cost=0.57..1433.05 rows=942 width=8) (actual time=0.077..1.944 rows=1987 loops=1)
         Index Cond: (sbom_occurrence_refs.security_project_tracked_context_id = (InitPlan 1).col1)
         Buffers: shared hit=266
   ->  Index Scan using sbom_occurrences_pkey on public.sbom_occurrences  (cost=0.57..3.59 rows=1 width=398) (actual time=0.005..0.005 rows=0 loops=1987)
         Index Cond: (sbom_occurrences.id = sbom_occurrence_refs.sbom_occurrence_id)
         Filter: (sbom_occurrences.reachability = ANY ('{1,2}'::integer[]))
         Rows Removed by Filter: 1
         Buffers: shared hit=9935
 Settings: seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', random_page_cost = '1.5', work_mem = '100MB'

 Time: 18.173 ms (planning 5.992 ms, execution 12.181 ms)
 Shared buffers: hits 10201 (~79.70 MiB), reads 0, dirtied 0, writes 0

GraphQL resolver query, batched reachability (postgres.ai #154697):

SELECT sbom_occurrences.reachability, occurrences_vulnerabilities.vulnerability_id
FROM sbom_occurrences
INNER JOIN sbom_occurrences_vulnerabilities occurrences_vulnerabilities
  ON occurrences_vulnerabilities.sbom_occurrence_id = sbom_occurrences.id
WHERE occurrences_vulnerabilities.vulnerability_id IN ($1, $2, ...)
  AND EXISTS (
    SELECT 1 FROM sbom_occurrence_refs
    WHERE sbom_occurrence_refs.security_project_tracked_context_id = $n
      AND sbom_occurrence_refs.sbom_occurrence_id = sbom_occurrences.id
  ) AND sbom_occurrences.reachability IN (1, 2);

Plan and buffers (#154697)

 Nested Loop Semi Join  (cost=2.88..4943.37 rows=1 width=10) (actual time=610.110..610.116 rows=0 loops=1)
   Buffers: shared hit=8577 read=1624 dirtied=53
   InitPlan 1
     ->  Limit  (cost=0.57..0.61 rows=1 width=16) (actual time=6.456..6.458 rows=1 loops=1)
           ->  Index Scan Backward using sbom_occurrence_refs_pkey on public.sbom_occurrence_refs sbom_occurrence_refs_1  (rows=1 loops=1)
   ->  Nested Loop  (cost=1.70..4933.57 rows=10 width=10) (actual time=610.110..610.112 rows=0 loops=1)
         ->  Nested Loop  (cost=1.14..4810.97 rows=11 width=18) (actual time=610.109..610.110 rows=0 loops=1)
               ->  Index Scan using idx_sbom_occurrence_refs_on_sec_prj_trck_cnxt_id on public.sbom_occurrence_refs  (cost=0.57..1433.05 rows=942 width=8) (actual time=7.104..30.301 rows=1987 loops=1)
                     Index Cond: (sbom_occurrence_refs.security_project_tracked_context_id = (InitPlan 1).col1)
                     Buffers: shared hit=63 read=203 dirtied=7
               ->  Index Scan using sbom_occurrences_pkey on public.sbom_occurrences  (cost=0.57..3.59 rows=1 width=10) (actual time=0.291..0.291 rows=0 loops=1987)
                     Index Cond: (sbom_occurrences.id = sbom_occurrence_refs.sbom_occurrence_id)
                     Filter: (sbom_occurrences.reachability = ANY ('{1,2}'::integer[]))
                     Rows Removed by Filter: 1
                     Buffers: shared hit=8514 read=1421 dirtied=46
         ->  Index Only Scan using i_sbom_occ_vulns_on_occ_id_vuln_id_and_project_id on public.sbom_occurrences_vulnerabilities occurrences_vulnerabilities  (cost=0.57..7.54 rows=361 width=16) (actual time=0.000..0.000 rows=0 loops=0)
               Index Cond: (occurrences_vulnerabilities.sbom_occurrence_id = sbom_occurrences.id)
               Heap Fetches: 0
 Settings: seq_page_cost = '4', effective_cache_size = '338688MB', jit = 'off', random_page_cost = '1.5', work_mem = '100MB'

 Time: 616.762 ms (planning 6.522 ms, execution 610.240 ms)
 Shared buffers: hits 8577 (~67 MiB), reads 1624 (~12.7 MiB), dirtied 53

The 610 ms reflects this one outsized context being probed 1987 times against the buffer pool (no disk reads in the cached run); for typical contexts and the bound parameter the cost is far lower. All access is by index, with the occurrences_vulnerabilities lookup served index only (zero heap fetches).

Reviewer notes

@zmartins you were the DRI for the reachability work, so I would value your eyes here. The companion MRs for #602157 each carry a local copy of this same scope (they branch off master before this merges); we will reconcile that at merge time.