Reindex SBOM occurrence refs when a malware advisory changes

What does this MR do and why?

The Elasticsearch malware field was only written during SBOM ingestion, so it stayed stale until the project's next pipeline: withdrawn advisories left dependencies flagged, and new advisories did not flag already-indexed ones.

Publish an event when an advisory starts or stops flagging its packages, and re-index the affected occurrence refs from it.

DB Review

1. Pre-upsert advisories fetch -

SELECT "pm_malware_advisories"."advisory_xid",
       "pm_malware_advisories"."source_xid",
       "pm_malware_advisories"."withdrawn_date"
FROM "pm_malware_advisories"
WHERE "pm_malware_advisories"."advisory_xid" IN (
  'GLAM-2025-01-00001','GLAM-2025-01-00002','GLAM-2025-01-00003','GLAM-2025-01-00004',
  'GLAM-2025-01-00005','GLAM-2025-01-00006','GLAM-2025-01-00007','GLAM-2025-01-00008',
  'GLAM-2025-01-00009','GLAM-2025-01-00010','GLAM-2025-02-00001','GLAM-2025-02-00002',
  'GLAM-2025-02-00003','GLAM-2025-02-00004','GLAM-2025-02-00005','GLAM-2025-03-00001',
  'GLAM-2025-03-00002','GLAM-2025-03-00003','GLAM-2025-03-00004','GLAM-2025-03-00005');

Query plan - https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/57439/commands/161955

2. Component look up -

SELECT "sbom_components"."id"
FROM "sbom_components"
WHERE "sbom_components"."component_type" = 0
  AND "sbom_components"."name" = 'lodash'
  AND "sbom_components"."purl_type" = 6;

Query Plan - https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/57439/commands/161956

3. Occurrence batching using each_batch - All covered by the index_sbom_occurrences_on_component_id_and_id index and we only fetch ids. So all of these are index only scans.

   3a. First boundary

   SELECT "sbom_occurrences"."id"
   FROM "sbom_occurrences"
   WHERE "sbom_occurrences"."component_id" = 10000
   ORDER BY "sbom_occurrences"."id" ASC
   LIMIT 1;

   Query Plan - https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/57439/commands/161957

   3b. Next boundary

   SELECT "sbom_occurrences"."id"
   FROM "sbom_occurrences"
   WHERE "sbom_occurrences"."component_id" = 10000
     AND "sbom_occurrences"."id" >= 1
   ORDER BY "sbom_occurrences"."id" ASC
   LIMIT 1 OFFSET 100;

   Query Plan - https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/57439/commands/161958

   3c. Batch fetch

   SELECT "sbom_occurrences"."id"
   FROM "sbom_occurrences"
   WHERE "sbom_occurrences"."component_id" = 10000
     AND "sbom_occurrences"."id" >= 1
     AND "sbom_occurrences"."id" < 500
     AND "sbom_occurrences"."component_version_id" IN (10001, 10002, 10003, 10004);

   Query Plan - https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/57775/commands/162434

4. Occurrence Ref fetch

SELECT "sbom_occurrence_refs".*
FROM "sbom_occurrence_refs"
WHERE "sbom_occurrence_refs"."sbom_occurrence_id" IN (1000, 1001, 1002, 1003, 1004, 1005, 1006, 1007, 1008, 1009, 1010, 1011, 1012, 1013, 1014, 1015, 1016, 1017, 1018, 1019, 1020, 1021, 1022, 1023, 1024, 1025, 1026)

Query Plan - https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/57439/commands/161960

References

Resolves #623538

Screenshots or screen recordings

Before	After

How to set up and validate locally

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.

Related to #623538