Add background migration to backfill vulnerability_reads dismissal_reason

Gregory Havengarequested to merge
backfill-vulnerability-reads-dismissal-reason into master

What does this MR do and why?

This MR adds a batched background migration to backfill the dismissal_reason field in the vulnerability_reads table for records that are in state: :dismissed but have dismissal_reason: null.

Context

This migration addresses a data integrity issue identified in #547483, where the GraphQL API dismissalReason field was returning null for dismissed vulnerabilities despite proper enum definition in the schema. The root cause was that while vulnerability_state_transitions records were being created with the dismissal reason, the denormalized vulnerability_reads.dismissal_reason field was not being consistently updated.

The issue was particularly problematic because:

  • The dismissal reason enum exists and is properly defined in the GraphQL schema
  • The data exists in vulnerability_state_transitions (visible in system notes)
  • But the dismissalReason field on the VulnerabilityType GraphQL type was returning null
  • This affected the Vulnerability Report UI, which couldn't display dismissal reason badges

How does it work?

The migration:

  1. Identifies vulnerability_reads records with state = dismissed (2) and dismissal_reason = null
  2. Looks up the latest vulnerability_state_transitions record for each vulnerability where to_state = dismissed and dismissal_reason IS NOT NULL
  3. Updates the vulnerability_reads.dismissal_reason to match the state transition's dismissal_reason
  4. Leaves records unchanged if no corresponding state transition exists (historical data)

Implementation Details

  • Background Migration Job: BackfillVulnerabilityReadsDismissalReason
  • Batch Size: 1000 records
  • Sub-batch Size: 100 records
  • Delay Interval: 2 minutes between batches
  • Schema: gitlab_sec

The migration uses DISTINCT ON to efficiently get the latest state transition per vulnerability (ordered by id DESC), ensuring we use the most recent dismissal reason.

Related Issues

Closes #547483

Testing

Comprehensive RSpec tests cover:

  • ✅ Updating records with available state transitions
  • ✅ Leaving records unchanged when no state transition exists
  • ✅ Using the latest state transition when multiple exist
  • ✅ Skipping records that already have a dismissal_reason
  • ✅ Handling state transitions with null dismissal_reason

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.

Checklist

  • Background migration job created
  • Post-migration file created to queue the job
  • Comprehensive specs added
  • Migration follows batched background migration patterns
  • Historical data without state transitions is preserved
Edited by Gregory Havenga

Merge request reports