Skip to content

Backfill null `project_id` rows of `security_scans` from `ci_builds`

Context: #434994 (comment 1779072112)

security_scans table was originally associated with ci_builds without a FK and the project_id column was introduced later. We can try to set the project_id by checking the associated ci_builds records but since there is no FK, it's possible to have missing ci_builds records for some security_scans records.

Implementation plan

  • Create a batched background migration which iterates through all security scans
  • For every batch, query the associated Ci::Builds by build_id
  • Delete any Security::Scan records which did not return a build_id
  • Set scan.project_id to build.project_id if needed
Edited by Brian Williams