Security Dashboard - Fix missing detected:true transition when vulnerabilities are re-detected

What does this MR do and why?

During the rollout of Exclude No Longer Detected Security Dashboard feature, we noticed that the drop in the Vulnerabilities over time chart on gitlab-org/gitlab was larger than expected, and the counts no longer aligned with the severity totals.

After debugging, we found a bug affecting re-detected, non-resolved vulnerabilities. When a vulnerability that was previously marked as no longer detected reappears in a scan (while still in a detected, confirmed, or dismissed state), we were not creating a new detected: true transition. As a result:

• PostgreSQL: a stale detected: false transition remains
• Elasticsearch: undetected_since is not reset to null
• Result: vulnerability remains hidden in the Vulnerabilities over time chart

This MR fixes the issue by ensuring that re-detected, non-resolved vulnerabilities receive a detected: true transition during ingestion, so they are correctly reflected in the dashboard.

Closes #585736

Query

• New Query Plan: https://console.postgres.ai/gitlab/gitlab-production-sec/sessions/47331/commands/143325

Raw SQL:

SELECT vo.*
FROM vulnerability_occurrences vo
WHERE vo.vulnerability_id IN (:vulnerability_ids)
  AND EXISTS (
    SELECT 1
    FROM vulnerability_detection_transitions vdt
    WHERE vdt.vulnerability_occurrence_id = vo.id
      AND vdt.id = (
        SELECT id
        FROM vulnerability_detection_transitions latest
        WHERE latest.vulnerability_occurrence_id = vdt.vulnerability_occurrence_id
        ORDER BY id DESC
        LIMIT 1
      )
      AND vdt.detected = FALSE
  );

References

• Main Epic: gitlab-org#19780
• Slack Thread

How to set up and validate locally

Prerequisites

• Elasticsearch should be running locally (guide)
• You should have a project with at least one vulnerability

1. Start Rails console by running rails c
2. Enable the feature flag and mark migration as complete:

Feature.enable(:new_security_dashboard_exclude_no_longer_detected)

migration = Elastic::DataMigrationService.find_by_name!(:add_undetected_since_field_to_vulnerability)
migration.save!(completed: true)
Elastic::DataMigrationService.drop_migration_has_finished_cache!(migration)

3. Find a vulnerability

project = Project.find(<PROJECT_ID>)
vulnerability = project.vulnerabilities.with_states(%i[detected confirmed dismissed]).first
finding = vulnerability.finding
vulnerability_id = vulnerability.id # note down the ID for yourself, you'll need it for the curl command in a later step

4. Now, simulate the bug. create a stale detected: false transition:

Vulnerabilities::DetectionTransition.create!(
  vulnerability_occurrence_id: finding.id,
  project_id: finding.project_id,
  detected: false
)

5. Sync to ES:

::Elastic::ProcessBookkeepingService.track!(
  Search::Elastic::References::Vulnerability.new(
    vulnerability_id, 
    "group_#{vulnerability.project.namespace.root_ancestor.id}"
  )
)
::Elastic::ProcessBookkeepingService.new.execute

6. Verify (in your terminal) if ES shows undetected_since with a timestamp:

curl -s "http://localhost:9200/gitlab-development-vulnerabilities/_search?pretty" \
  -H "Content-Type: application/json" \
  -d '{ "query": { "term": { "vulnerability_id": { "value": <VULNERABILITY_ID> } } }, "_source": ["undetected_since"] }'

7. Check the security dashboard chart — go to your project's Security Dashboard (e.g. http://gdk.test:3000/gitlab-org/security-reports/-/security/dashboard) and note the count for today for the vulnerability's severity. The count should be 1 less than expected because this vulnerability is now excluded.

Example:

8. Verify the query logic finds this vulnerability as needing a fix:

stale_ids = Vulnerabilities::Finding
  .by_vulnerability([vulnerability_id])
  .with_latest_detection_transition
  .filter_map { |f| f.vulnerability_id unless f.latest_detection_transition&.detected? }

stale_ids.include?(vulnerability_id) # this should return true

9. Now, let's run our fix!

findings = Vulnerabilities::Finding.by_vulnerability(stale_ids)
Vulnerabilities::DetectionTransitions::InsertService.new(findings, detected: true).execute
::Elastic::ProcessBookkeepingService.new.execute

10. Verify postgres, the latest transition should be detected: true:

finding.reload.detection_transitions.order(:id).pluck(:id, :detected)
# the last row should be [<id>, true]

11. Now, verify that ES shows undetected_since is now null

curl -s "http://localhost:9200/gitlab-development-vulnerabilities/_search?pretty" \
  -H "Content-Type: application/json" \
  -d '{ "query": { "term": { "vulnerability_id": { "value": <VULNERABILITY_ID> } } }, "_source": ["undetected_since"] }'

We expect: "undetected_since": null

12. Check the security dashboard chart again. Hard refresh the page. The count for today should be back to the original because the vulnerability is now included again.

Example:

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.