1 files changed, 33 insertions, 0 deletions
diff --git a/lib/gitlab/background_migration/delete_orphaned_operational_vulnerabilities.rb b/lib/gitlab/background_migration/delete_orphaned_operational_vulnerabilities.rb
new file mode 100644
index 00000000000..c3e1019b72f
--- /dev/null
+++ b/lib/gitlab/background_migration/delete_orphaned_operational_vulnerabilities.rb
@@ -0,0 +1,33 @@
+# frozen_string_literal: true
+
+module Gitlab
+  module BackgroundMigration
+    # Background migration for deleting orphaned operational vulnerabilities (without findings)
+    class DeleteOrphanedOperationalVulnerabilities < ::Gitlab::BackgroundMigration::BatchedMigrationJob
+      REPORT_TYPES = {
+        cluster_image_scanning: 7,
+        custom: 99
+      }.freeze
+
+      NOT_EXISTS_SQL = <<-SQL
+        NOT EXISTS (
+          SELECT FROM vulnerability_occurrences
+          WHERE "vulnerability_occurrences"."vulnerability_id" = "vulnerabilities"."id"
+        )
+      SQL
+
+      scope_to ->(relation) do
+        relation
+          .where(report_type: [REPORT_TYPES[:cluster_image_scanning], REPORT_TYPES[:custom]])
+      end
+
+      def perform
+        each_sub_batch(operation_name: :delete_orphaned_operational_vulnerabilities) do |sub_batch|
+          sub_batch
+            .where(NOT_EXISTS_SQL)
+            .delete_all
+        end
+      end
+    end
+  end
+end