## Summary `PackageMetadata::MalwareAdvisorySyncService#execute` advances the per-registry checkpoint (`checkpoint.update(sequence:, chunk:)`) once per fetched archive **regardless of whether the ingestion step actually persisted anything**. When `ingest_malware_advisories` is disabled, ingestion is a no-op (it logs `phase: skipped` and returns without raising), yet the sync loop still advances the checkpoint to the fetched snapshot's sequence. This breaks the intended staged rollout — enable `sync_malware_advisories` first to validate fetch/parse, then enable `ingest_malware_advisories` to begin writing. The fetch/parse-only phase consumes the `/all` snapshot and moves every checkpoint past it, so once `ingest_malware_advisories` is enabled `first_sync?` is already `false`, the connector only requests `/delta` (which returns nothing new), and the full dataset is never written. ## Impact Observed on **production on 2026-07-28**: both flags enabled, all worker gates passing (`dependency_scanning` licensed, `sync_malware_advisories` on, `ingest_malware_advisories` on), but `pm_malware_advisories` / `pm_malware_affected_packages` stayed empty. All 5 supported-registry checkpoints (npm, maven, nuget, pypi, cargo) were at `first_sync? = false` with `chunk = 63` (the last `/all` shard), timestamped to the earlier sync-only window. The `/all` payload had been fetched, parsed, skipped, and checkpointed away — so `/delta` returned nothing and the DB never populated. ## Root cause In [`ee/app/services/package_metadata/malware_advisory_sync_service.rb`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/app/services/package_metadata/malware_advisory_sync_service.rb#L57-79) the execute loop: ```ruby connector.data_after(checkpoint).each do |file| ... if pending_sequence && file.sequence != pending_sequence checkpoint.update(sequence: pending_sequence, chunk: pending_chunk) # advances regardless of ingest outcome end ingest_file(file) # -> ingest -> MalwareAdvisoryIngestionService.execute @files_ingested += 1 pending_sequence = file.sequence pending_chunk = file.chunk ... end checkpoint.update(sequence: pending_sequence, chunk: pending_chunk) if !interrupted && pending_sequence ``` `ingest_file` → `ingest` → [`MalwareAdvisoryIngestionService.execute`](https://gitlab.com/gitlab-org/gitlab/-/blob/master/ee/app/services/package_metadata/malware_advisory_ingestion_service.rb#L14-27), which returns early (`log_upsert_skipped`, no write, no raise) when `ingest_malware_advisories` is off. The checkpoint advance is decoupled from whether ingestion persisted data, so a skipped upsert still moves the cursor forward. ## Proposed fix (options) 1. **Only advance the checkpoint for persisted data (preferred).** Have the ingestion service report whether it actually wrote (vs skipped), and skip the `checkpoint.update` when ingestion was a no-op. Keeps the "fetch/parse validation" mode useful without corrupting sync state. 2. **Gate the whole sync on `ingest_malware_advisories`** — don't run/advance the sync at all when ingestion is off. Simpler, but drops the separate fetch/parse-only validation mode the ingestion service comment describes. 3. **Document that both flags must be enabled together (or ingest before sync)** and keep the code as-is. Weakest — relies on operator discipline and the trap remains. ## Recovery / workaround (applied for prod) Reset the malware checkpoints so `first_sync?` becomes true again and the next worker run re-bootstraps `/all` with ingestion on: ```ruby # pm_checkpoints is on the MAIN db; scoped to data_type: malware_advisories, # so it does not touch the public-advisory checkpoints. PackageMetadata::Checkpoint.for_malware_advisories.delete_all ``` ### PG queries (main DB) `pm_checkpoints` is on the **main** database, and its `data_type` / `purl_type` / `version_format` columns are integer-backed enums. Values used below: `data_type = 4` is `malware_advisories`, `version_format = 3` is `v3`, and the supported `purl_type` ints are `5 = maven`, `6 = npm`, `7 = nuget`, `8 = pypi`, `14 = cargo`. **Diagnose (read-only)** — inspect the checkpoints and whether each is stuck off `first_sync`: ```sql SELECT purl_type, -- 5=maven 6=npm 7=nuget 8=pypi 14=cargo sequence, chunk, full_sync_target_sequence, (full_sync_target_sequence IS NOT NULL OR sequence = 0) AS first_sync, updated_at FROM pm_checkpoints WHERE data_type = 4 -- malware_advisories ORDER BY purl_type; ``` A row with `first_sync = false` (i.e. `sequence > 0` and no `full_sync_target_sequence`) is on the `/delta` path — the symptom of this bug after a sync-only window. **Reset (write)** — SQL equivalent of the Rails `delete_all`; run on the MAIN db only: ```sql BEGIN; -- confirm scope first (expect only the malware rows) SELECT id, purl_type, sequence, chunk FROM pm_checkpoints WHERE data_type = 4 ORDER BY purl_type; DELETE FROM pm_checkpoints WHERE data_type = 4; -- malware_advisories only COMMIT; -- ROLLBACK if the pre-check shows anything unexpected ``` Alternatively, zero the rows instead of deleting (also makes `first_sync?` true): ```sql UPDATE pm_checkpoints SET sequence = 0, chunk = 0, full_sync_target_sequence = NULL WHERE data_type = 4; ``` ## Related - Epic: https://gitlab.com/groups/gitlab-org/-/epics/20876 - `sync_malware_advisories` FF rollout: https://gitlab.com/gitlab-org/gitlab/-/work_items/604583 - `ingest_malware_advisories` FF rollout: https://gitlab.com/gitlab-org/gitlab/-/work_items/604584