1 files changed, 36 insertions, 0 deletions
diff --git a/lib/gitlab/background_migration/backfill_namespace_id_of_vulnerability_reads.rb b/lib/gitlab/background_migration/backfill_namespace_id_of_vulnerability_reads.rb
new file mode 100644
index 00000000000..cd349bf3ae1
--- /dev/null
+++ b/lib/gitlab/background_migration/backfill_namespace_id_of_vulnerability_reads.rb
@@ -0,0 +1,36 @@
+# frozen_string_literal: true
+
+module Gitlab
+  module BackgroundMigration
+    # Sets the `namespace_id` of the existing `vulnerability_reads` records
+    class BackfillNamespaceIdOfVulnerabilityReads < BatchedMigrationJob
+      UPDATE_SQL = <<~SQL
+        UPDATE
+          vulnerability_reads
+        SET
+          namespace_id = sub_query.namespace_id
+        FROM
+          (%<subquery>s) as sub_query
+        WHERE
+          vulnerability_reads.vulnerability_id = sub_query.vulnerability_id
+      SQL
+
+      def perform
+        each_sub_batch(operation_name: :set_namespace_id) do |sub_batch|
+          update_query = update_query_for(sub_batch)
+
+          connection.execute(update_query)
+        end
+      end
+
+      private
+
+      def update_query_for(sub_batch)
+        subquery = sub_batch.select("vulnerability_reads.vulnerability_id, projects.namespace_id")
+                            .joins("INNER JOIN projects ON projects.id = vulnerability_reads.project_id")
+
+        format(UPDATE_SQL, subquery: subquery.to_sql)
+      end
+    end
+  end
+end