Add BBM to delete orphaned dependency scanning Security::Scan records

What does this MR do and why?

Add batched background migration to delete orphaned Dependency Scanning Security::Scan records.

When the Dependency Scanning using SBOM beta feature processes SBOM reports, it creates Security::Scan records in a created state. However, the cleanup logic only runs when the security report has data. Since SBOM reports may have blank security data (when they're incompatible with the DS analyzer), these records are skipped during cleanup and remain in the created state indefinitely.

This MR adds a batched background migration to remove these orphaned records that have been in created state for more than 7 days.

Note: This complements !212842 (merged), which fixes the code to prevent new orphaned records from being created. This migration cleans up the existing backlog.

References

DS using SBOM feature creates dangling Security... (#580826)

Remove dangling scans even if there are no scan... (!212842 - merged)

Query Plan

EXPLAIN SELECT * FROM security_scans
WHERE scan_type = 2 
  AND status = 0 
  AND created_at < NOW() - INTERVAL '7 days'
LIMIT 10000;

How to set up and validate locally

Click to expand test setup and verification steps

Prerequisites

Ensure you have a running GDK environment with access to the Rails console.

1. Create test data

Run the following script in the Rails console to create test records:

# Get the current user and organization
current_user = User.first
organization = Organizations::Organization.first

# Create a namespace with owner and organization
namespace = Namespace.create!(
  name: 'test-migration',
  path: 'test-migration',
  owner: current_user,
  organization: organization
)

# Create a project
project = Project.create!(
  name: 'test-migration-project',
  path: 'test-migration-project',
  namespace: namespace,
  creator: current_user,
  organization: organization,
  visibility_level: Gitlab::VisibilityLevel::PRIVATE
)

# Create a pipeline
pipeline = Ci::Pipeline.create!(
  project: project,
  status: 'success',
  source: 'push',
  sha: 'abc123def456',
  ref: 'main'
)

# Create a stage for the pipeline
stage = Ci::Stage.create!(
  pipeline: pipeline,
  project: project,
  name: 'test',
  position: 1,
  status: 'success'
)

# Helper to create builds
def create_build(project, pipeline, stage, index)
  Ci::Build.create!(
    project: project,
    pipeline: pipeline,
    stage_id: stage.id,
    name: "test-job-#{index}",
    status: 'success',
    ref: 'main',
    tag: false,
    scheduling_type: :stage
  )
end

# Create test scans
test_cases = [
  { count: 1000, prefix: 'orphaned', scan_type: :dependency_scanning, status: :created, stale: true, description: 'orphaned dependency scanning scans (stale - SHOULD be deleted)' },
  { count: 200, prefix: 'recent', scan_type: :dependency_scanning, status: :created, stale: false, description: 'recent created scans (should NOT be deleted)' },
  { count: 300, prefix: 'processed', scan_type: :dependency_scanning, status: :succeeded, stale: true, description: 'processed scans (should NOT be deleted)' },
  { count: 150, prefix: 'sast', scan_type: :sast, status: :created, stale: true, description: 'SAST scans in created state (should NOT be deleted)' }
]

test_cases.each do |tc|
  puts "Creating #{tc[:count]} #{tc[:description]}..."
  tc[:count].times do |i|
    build = create_build(project, pipeline, stage, "#{tc[:prefix]}-#{i}")
    days_ago = tc[:stale] ? (10 + rand(30)).days.ago : (1 + rand(6)).days.ago

    Security::Scan.create!(
      project: project,
      pipeline: pipeline,
      build: build,
      scan_type: tc[:scan_type],
      status: tc[:status],
      created_at: days_ago,
      updated_at: days_ago
    )
    puts "  Created #{i + 1}/#{tc[:count]}" if (i + 1) % 100 == 0
  end
end

2. Verify test data before migration

puts "=== Test Data Summary ==="
puts "Total Security::Scan records: #{Security::Scan.count}"
puts "Orphaned scans (SHOULD be deleted): #{Security::Scan.where(scan_type: :dependency_scanning, status: :created).where('created_at < ?', 7.days.ago).count}"
puts "Recent created scans (should NOT be deleted): #{Security::Scan.where(scan_type: :dependency_scanning, status: :created).where('created_at >= ?', 7.days.ago).count}"
puts "Processed scans (should NOT be deleted): #{Security::Scan.where(scan_type: :dependency_scanning, status: :succeeded).count}"
puts "SAST scans (should NOT be deleted): #{Security::Scan.where(scan_type: :sast).count}"

Expected output (approx):

Category	Count
Total records	1650
Orphaned scans (to delete)	1000
Recent created scans	200
Processed scans	300
SAST scans	150

3. Run the migration

migration = Gitlab::BackgroundMigration::DeleteOrphanedDependencyScans.new(
  start_id: Security::Scan.minimum(:id),
  end_id: Security::Scan.maximum(:id),
  batch_table: :security_scans,
  batch_column: :id,
  sub_batch_size: 100,
  pause_ms: 0,
  connection: ApplicationRecord.connection
)
migration.perform

4. Verify results after migration

Run the same verification script from step 2.

Expected output (approx):

Category	Count
Total records	650
Orphaned scans	0
Recent created scans	200
Processed scans	300
SAST scans	150

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.