From 1169e8150e0b08432a4ce46db58c785f4b54a34a Mon Sep 17 00:00:00 2001 From: Albina Yusupova Date: Thu, 11 Dec 2025 23:18:44 +0100 Subject: [PATCH] Add BBM to delete orphaned dependency scanning Security::Scan records Deletes orphaned Security::Scan records in 'created' state from the DS using SBOM feature. Changelog: changed --- .../delete_orphaned_dependency_scans.yml | 8 + ..._queue_delete_orphaned_dependency_scans.rb | 27 +++ db/schema_migrations/20251204205418 | 1 + .../delete_orphaned_dependency_scans.rb | 29 +++ .../delete_orphaned_dependency_scans_spec.rb | 201 ++++++++++++++++++ ...e_delete_orphaned_dependency_scans_spec.rb | 28 +++ 6 files changed, 294 insertions(+) create mode 100644 db/docs/batched_background_migrations/delete_orphaned_dependency_scans.yml create mode 100644 db/post_migrate/20251204205418_queue_delete_orphaned_dependency_scans.rb create mode 100644 db/schema_migrations/20251204205418 create mode 100644 lib/gitlab/background_migration/delete_orphaned_dependency_scans.rb create mode 100644 spec/lib/gitlab/background_migration/delete_orphaned_dependency_scans_spec.rb create mode 100644 spec/migrations/20251204205418_queue_delete_orphaned_dependency_scans_spec.rb diff --git a/db/docs/batched_background_migrations/delete_orphaned_dependency_scans.yml b/db/docs/batched_background_migrations/delete_orphaned_dependency_scans.yml new file mode 100644 index 00000000000000..32906c5f9ee65c --- /dev/null +++ b/db/docs/batched_background_migrations/delete_orphaned_dependency_scans.yml @@ -0,0 +1,8 @@ +--- +migration_job_name: DeleteOrphanedDependencyScans +description: Deletes orphaned Security::Scan records in created state from the DS using SBOM feature +feature_category: software_composition_analysis +introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/216223 +milestone: "18.7" +queued_migration_version: 20251204205418 +finalized_by: # version of the migration that finalized this BBM diff --git a/db/post_migrate/20251204205418_queue_delete_orphaned_dependency_scans.rb b/db/post_migrate/20251204205418_queue_delete_orphaned_dependency_scans.rb new file mode 100644 index 00000000000000..a13e48ad2a1940 --- /dev/null +++ b/db/post_migrate/20251204205418_queue_delete_orphaned_dependency_scans.rb @@ -0,0 +1,27 @@ +# frozen_string_literal: true + +class QueueDeleteOrphanedDependencyScans < Gitlab::Database::Migration[2.3] + milestone '18.7' + + restrict_gitlab_migration gitlab_schema: :gitlab_sec + + MIGRATION = "DeleteOrphanedDependencyScans" + DELAY_INTERVAL = 2.minutes + BATCH_SIZE = 10_000 + SUB_BATCH_SIZE = 100 + + def up + queue_batched_background_migration( + MIGRATION, + :security_scans, + :id, + job_interval: DELAY_INTERVAL, + batch_size: BATCH_SIZE, + sub_batch_size: SUB_BATCH_SIZE + ) + end + + def down + delete_batched_background_migration(MIGRATION, :security_scans, :id, []) + end +end diff --git a/db/schema_migrations/20251204205418 b/db/schema_migrations/20251204205418 new file mode 100644 index 00000000000000..6edc45d6f810cf --- /dev/null +++ b/db/schema_migrations/20251204205418 @@ -0,0 +1 @@ +7b014aa79c5e2c683456d16ce24ed4472c856c166c187b9a4ae14e1173d040eb \ No newline at end of file diff --git a/lib/gitlab/background_migration/delete_orphaned_dependency_scans.rb b/lib/gitlab/background_migration/delete_orphaned_dependency_scans.rb new file mode 100644 index 00000000000000..dde22acf1d5ab1 --- /dev/null +++ b/lib/gitlab/background_migration/delete_orphaned_dependency_scans.rb @@ -0,0 +1,29 @@ +# frozen_string_literal: true + +# See https://docs.gitlab.com/ee/development/database/batched_background_migrations.html +# for more information on how to use batched background migrations + +# Update below commented lines with appropriate values. + +module Gitlab + module BackgroundMigration + class DeleteOrphanedDependencyScans < BatchedMigrationJob + operation_name :delete_orphaned_dependency_scans + feature_category :software_composition_analysis + + # Only delete scans that have been in created state for more than 7 days + STALE_THRESHOLD = 7.days + DEPENDENCY_SCANNING = 2 + CREATED = 0 + + def perform + each_sub_batch do |sub_batch| + sub_batch + .where(scan_type: DEPENDENCY_SCANNING, status: CREATED) + .where(created_at: ...STALE_THRESHOLD.ago) + .delete_all + end + end + end + end +end diff --git a/spec/lib/gitlab/background_migration/delete_orphaned_dependency_scans_spec.rb b/spec/lib/gitlab/background_migration/delete_orphaned_dependency_scans_spec.rb new file mode 100644 index 00000000000000..08d547c46af8e0 --- /dev/null +++ b/spec/lib/gitlab/background_migration/delete_orphaned_dependency_scans_spec.rb @@ -0,0 +1,201 @@ +# frozen_string_literal: true + +require 'spec_helper' + +RSpec.describe Gitlab::BackgroundMigration::DeleteOrphanedDependencyScans, feature_category: :software_composition_analysis do + let(:security_scans) { table(:security_scans, database: :sec) } + let(:ci_builds) { table(:p_ci_builds, primary_key: :id, database: :ci) } + let(:projects) { table(:projects) } + let(:namespaces) { table(:namespaces) } + let(:organizations) { table(:organizations) } + let(:users) { table(:users) } + + # Enum constants for readability + let(:scan_type_sast) { 1 } + let(:scan_type_dependency_scanning) { 2 } + let(:status_created) { 0 } + let(:status_succeeded) { 1 } + + # Threshold for "stale" scans (matches implementation) + let(:stale_threshold) { 7.days } + + let!(:organization) { organizations.create!(name: 'test-org', path: 'test-org') } + let!(:shared_project) { create_project('shared') } + + describe '#perform' do + subject(:perform_migration) { described_class.new(**migration_args).perform } + + context 'when deleting orphaned dependency scans' do + let!(:orphaned_scan_old) do + create_scan(age: 10.days, scan_type: scan_type_dependency_scanning, status: status_created) + end + + let!(:orphaned_scan_stale) do + create_scan(age: 8.days, scan_type: scan_type_dependency_scanning, status: status_created) + end + + let!(:recent_created_scan) do + create_scan(age: 2.days, scan_type: scan_type_dependency_scanning, status: status_created) + end + + let!(:processed_scan) do + create_scan(age: 10.days, scan_type: scan_type_dependency_scanning, status: status_succeeded) + end + + let!(:sast_scan_created) do + create_scan(age: 10.days, scan_type: scan_type_sast, status: status_created) + end + + it 'deletes only stale dependency scanning scans in created status' do + expect { perform_migration } + .to change { security_scans.count }.from(5).to(3) + .and change { security_scans.exists?(orphaned_scan_old.id) }.from(true).to(false) + .and change { security_scans.exists?(orphaned_scan_stale.id) }.from(true).to(false) + end + + it 'preserves recent scans in created status' do + perform_migration + + expect(security_scans.exists?(recent_created_scan.id)).to be true + end + + it 'preserves scans that have been processed (non-created status)' do + perform_migration + + expect(security_scans.exists?(processed_scan.id)).to be true + end + + it 'preserves non-dependency-scanning scan types' do + perform_migration + + expect(security_scans.exists?(sast_scan_created.id)).to be true + end + + it 'is idempotent' do + 2.times { perform_migration } + + expect(security_scans.count).to eq(3) + end + end + + context 'with boundary conditions around the stale threshold' do + let!(:scan_at_threshold) do + create_scan(age: stale_threshold, scan_type: scan_type_dependency_scanning, status: status_created) + end + + let!(:scan_just_before_threshold) do + create_scan(age: stale_threshold - 1.hour, scan_type: scan_type_dependency_scanning, status: status_created) + end + + let!(:scan_just_after_threshold) do + create_scan(age: stale_threshold + 1.hour, scan_type: scan_type_dependency_scanning, status: status_created) + end + + it 'deletes scans older than the threshold' do + perform_migration + + expect(security_scans.exists?(scan_just_after_threshold.id)).to be false + end + + it 'preserves scans newer than the threshold' do + perform_migration + + expect(security_scans.exists?(scan_just_before_threshold.id)).to be true + end + + it 'deletes scans exactly at the threshold (uses >= comparison)' do + perform_migration + + expect(security_scans.exists?(scan_at_threshold.id)).to be false + end + end + + context 'with an empty batch' do + it 'handles gracefully when no scans match criteria' do + # Only create scans that should NOT be deleted + create_scan(age: 2.days, scan_type: scan_type_dependency_scanning, status: status_created) + + expect { perform_migration }.not_to change { security_scans.count } + end + end + + context 'with various scan types' do + # Ensure all scan types other than dependency_scanning are preserved + let(:other_scan_types) { [1, 3, 4, 5, 6, 7, 8] } # All types except 2 (dependency_scanning) + + before do + other_scan_types.each do |scan_type| + create_scan(age: 10.days, scan_type: scan_type, status: status_created) + end + end + + it 'only targets dependency_scanning type' do + expect { perform_migration }.not_to change { security_scans.count } + end + end + end + + private + + def migration_args + min, max = security_scans.pick('MIN(id)', 'MAX(id)') + + { + start_id: min || 0, + end_id: max || 0, + batch_table: 'security_scans', + batch_column: 'id', + sub_batch_size: 100, + pause_ms: 0, + connection: SecApplicationRecord.connection + } + end + + def create_scan(age:, scan_type:, status:, project: shared_project) + build = ci_builds.create!(project_id: project.id, partition_id: 100) + timestamp = age.ago + + security_scans.create!( + build_id: build.id, + project_id: project.id, + scan_type: scan_type, + status: status, + created_at: timestamp, + updated_at: timestamp + ) + end + + def create_project(name) + user = users.create!( + username: "user-#{name}", + email: "user-#{name}@example.com", + encrypted_password: 'password', + projects_limit: 10, + organization_id: organization.id + ) + + namespace = namespaces.create!( + name: "group-#{name}", + path: "group-#{name}", + owner_id: user.id, + type: 'User', + organization_id: organization.id + ) + + project_namespace = namespaces.create!( + name: "project-#{name}", + path: "project-#{name}", + owner_id: user.id, + type: 'User', + organization_id: organization.id + ) + + projects.create!( + namespace_id: namespace.id, + project_namespace_id: project_namespace.id, + name: "project-#{name}", + path: "project-#{name}", + organization_id: organization.id + ) + end +end diff --git a/spec/migrations/20251204205418_queue_delete_orphaned_dependency_scans_spec.rb b/spec/migrations/20251204205418_queue_delete_orphaned_dependency_scans_spec.rb new file mode 100644 index 00000000000000..d18a11eb3f3887 --- /dev/null +++ b/spec/migrations/20251204205418_queue_delete_orphaned_dependency_scans_spec.rb @@ -0,0 +1,28 @@ +# frozen_string_literal: true + +require 'spec_helper' +require_migration! + +RSpec.describe QueueDeleteOrphanedDependencyScans, migration: :gitlab_sec, + feature_category: :software_composition_analysis do + let!(:batched_migration) { described_class::MIGRATION } + + it 'schedules a new batched background migration' do + reversible_migration do |migration| + migration.before -> { + expect(batched_migration).not_to have_scheduled_batched_migration + } + + migration.after -> { + expect(batched_migration).to have_scheduled_batched_migration( + gitlab_schema: :gitlab_sec, + table_name: :security_scans, + column_name: :id, + interval: described_class::DELAY_INTERVAL, + batch_size: described_class::BATCH_SIZE, + sub_batch_size: described_class::SUB_BATCH_SIZE + ) + } + end + end +end -- GitLab