From 57d6bb5ea19d3c6db4faf28a9359c7879bdc6f19 Mon Sep 17 00:00:00 2001
From: mo khan <mo@mokhan.ca>
Date: Thu, 4 Dec 2025 17:55:08 -0700
Subject: [PATCH 1/9] Backfill `user_agent_details.organization_id` column

---
 ...ill_user_agent_details_organization_id.yml |  8 +++
 ...fill_user_agent_details_organization_id.rb | 27 ++++++++
 db/schema_migrations/20251208173859           |  1 +
 ...fill_user_agent_details_organization_id.rb | 18 +++++
 ...user_agent_details_organization_id_spec.rb | 67 +++++++++++++++++++
 ...user_agent_details_organization_id_spec.rb | 26 +++++++
 6 files changed, 147 insertions(+)
 create mode 100644 db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml
 create mode 100644 db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb
 create mode 100644 db/schema_migrations/20251208173859
 create mode 100644 lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
 create mode 100644 spec/lib/gitlab/background_migration/backfill_user_agent_details_organization_id_spec.rb
 create mode 100644 spec/migrations/20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb

diff --git a/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml b/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml
new file mode 100644
index 00000000000000..a52391050d4ef4
--- /dev/null
+++ b/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml
@@ -0,0 +1,8 @@
+---
+migration_job_name: BackfillUserAgentDetailsOrganizationId
+description: Backfill the sharding key on the user_agent_details table
+feature_category: instance_resiliency
+introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/215327
+milestone: '18.7'
+queued_migration_version: 20251208173859
+finalized_by: # version of the migration that finalized this BBM
diff --git a/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb b/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb
new file mode 100644
index 00000000000000..ac0eb221835536
--- /dev/null
+++ b/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb
@@ -0,0 +1,27 @@
+# frozen_string_literal: true
+
+class QueueBackfillUserAgentDetailsOrganizationId < Gitlab::Database::Migration[2.3]
+  milestone '18.7'
+
+  restrict_gitlab_migration gitlab_schema: :gitlab_main
+
+  MIGRATION = "BackfillUserAgentDetailsOrganizationId"
+  DELAY_INTERVAL = 2.minutes
+  BATCH_SIZE = 1_000
+  SUB_BATCH_SIZE = 100
+
+  def up
+    queue_batched_background_migration(
+      MIGRATION,
+      :user_agent_details,
+      :id,
+      job_interval: DELAY_INTERVAL,
+      batch_size: BATCH_SIZE,
+      sub_batch_size: SUB_BATCH_SIZE
+    )
+  end
+
+  def down
+    delete_batched_background_migration(MIGRATION, :user_agent_details, :id, [])
+  end
+end
diff --git a/db/schema_migrations/20251208173859 b/db/schema_migrations/20251208173859
new file mode 100644
index 00000000000000..5124dac923ac23
--- /dev/null
+++ b/db/schema_migrations/20251208173859
@@ -0,0 +1 @@
+547562744d891ce7981c150beab092bbd864ae8bcb44816c36e871ca9f754a35
\ No newline at end of file
diff --git a/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb b/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
new file mode 100644
index 00000000000000..b1c51766fbb692
--- /dev/null
+++ b/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
@@ -0,0 +1,18 @@
+# frozen_string_literal: true
+
+module Gitlab
+  module BackgroundMigration
+    class BackfillUserAgentDetailsOrganizationId < BatchedMigrationJob
+      operation_name :backfill_user_agent_details_organization_id
+      feature_category :instance_resiliency
+
+      DEFAULT_ORGANIZATION_ID = 1
+
+      def perform
+        each_sub_batch do |sub_batch|
+          sub_batch.where(organization_id: nil).update_all(organization_id: DEFAULT_ORGANIZATION_ID)
+        end
+      end
+    end
+  end
+end
diff --git a/spec/lib/gitlab/background_migration/backfill_user_agent_details_organization_id_spec.rb b/spec/lib/gitlab/background_migration/backfill_user_agent_details_organization_id_spec.rb
new file mode 100644
index 00000000000000..1589c4128a71eb
--- /dev/null
+++ b/spec/lib/gitlab/background_migration/backfill_user_agent_details_organization_id_spec.rb
@@ -0,0 +1,67 @@
+# frozen_string_literal: true
+
+require 'spec_helper'
+
+RSpec.describe Gitlab::BackgroundMigration::BackfillUserAgentDetailsOrganizationId, feature_category: :instance_resiliency do
+  let(:connection) { ApplicationRecord.connection }
+
+  let(:issues) { table(:issues) }
+  let(:namespaces) { table(:namespaces) }
+  let(:organizations) { table(:organizations) }
+  let(:projects) { table(:projects) }
+  let(:user_agent_details) { table(:user_agent_details) { |t| t.belongs_to :subject, polymorphic: true } }
+
+  let!(:default_organization) { organizations.create!(id: 1, name: 'default', path: 'default') }
+  let(:organization) { organizations.create!(name: 'organization', path: 'organization') }
+  let(:namespace1) { namespaces.create!(name: 'namespace 1', path: 'namespace1', organization_id: organization.id) }
+  let(:project1) do
+    projects.create!(namespace_id: namespace1.id, project_namespace_id: namespace1.id, organization_id: organization.id)
+  end
+
+  let(:issue1) do
+    issues.create!(namespace_id: namespace1.id, project_id: project1.id, created_at: 5.days.ago, closed_at: 3.days.ago,
+      work_item_type_id: work_item_type_id)
+  end
+
+  let(:issue2) do
+    issues.create!(namespace_id: namespace1.id, project_id: project1.id, created_at: 7.days.ago, closed_at: 2.days.ago,
+      work_item_type_id: work_item_type_id)
+  end
+
+  let(:work_item_type_id) { table(:work_item_types).where(base_type: 1).first.id }
+
+  describe '#perform' do
+    subject(:migration) do
+      described_class.new(
+        start_id: user_agent_details.minimum(:id),
+        end_id: user_agent_details.maximum(:id),
+        batch_table: :user_agent_details,
+        batch_column: :id,
+        sub_batch_size: 100,
+        pause_ms: 0,
+        connection: connection
+      )
+    end
+
+    before do
+      create_user_agent_detail!(organization: organization, target: issue1)
+      create_user_agent_detail!(organization: organization, target: issue2)
+      create_user_agent_detail!(organization: nil, target: issue1)
+      create_user_agent_detail!(organization: nil, target: issue2)
+    end
+
+    it 'updates records without an organization' do
+      expect { migration.perform }.to change { user_agent_details.where(organization_id: nil).count }.from(2).to(0)
+    end
+  end
+
+  def create_user_agent_detail!(target:, organization: nil)
+    user_agent_details.create!(
+      organization_id: organization&.id,
+      ip_address: '127.0.0.1',
+      user_agent: 'Mozilla/5.0 (X11; Linux i686; rv:136.0) Gecko/20100101 Firefox/136.0',
+      subject_id: target&.id,
+      subject_type: target.class.name
+    )
+  end
+end
diff --git a/spec/migrations/20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb b/spec/migrations/20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb
new file mode 100644
index 00000000000000..5f2fd74e359f72
--- /dev/null
+++ b/spec/migrations/20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb
@@ -0,0 +1,26 @@
+# frozen_string_literal: true
+
+require 'spec_helper'
+require_migration!
+
+RSpec.describe QueueBackfillUserAgentDetailsOrganizationId, migration: :gitlab_main_org, feature_category: :instance_resiliency do
+  let!(:batched_migration) { described_class::MIGRATION }
+
+  it 'schedules a new batched 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_main,
+          table_name: :user_agent_details,
+          column_name: :id,
+          batch_size: described_class::BATCH_SIZE,
+          sub_batch_size: described_class::SUB_BATCH_SIZE
+        )
+      }
+    end
+  end
+end
-- 
GitLab


From bfe4b03fae153406bb2f88fd7851b73fcc448b9e Mon Sep 17 00:00:00 2001
From: mo khan <mo@mokhan.ca>
Date: Thu, 11 Dec 2025 09:01:02 -0700
Subject: [PATCH 2/9] Apply 1 suggestion(s) to 1 file(s)

Co-authored-by: Tomasz Skorupa <tskorupa@gitlab.com>
---
 ...8173859_queue_backfill_user_agent_details_organization_id.rb | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb b/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb
index ac0eb221835536..5e7ecf4e0632ca 100644
--- a/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb
+++ b/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb
@@ -3,7 +3,7 @@
 class QueueBackfillUserAgentDetailsOrganizationId < Gitlab::Database::Migration[2.3]
   milestone '18.7'
 
-  restrict_gitlab_migration gitlab_schema: :gitlab_main
+  restrict_gitlab_migration gitlab_schema: :gitlab_main_org
 
   MIGRATION = "BackfillUserAgentDetailsOrganizationId"
   DELAY_INTERVAL = 2.minutes
-- 
GitLab


From deb1c8c49ea1970ced410c81763f0553dee93260 Mon Sep 17 00:00:00 2001
From: mo khan <mo@mokhan.ca>
Date: Thu, 11 Dec 2025 11:51:49 -0700
Subject: [PATCH 3/9] Update spec to user gitlab_main_org instead of
 gitlab_main

---
 ...59_queue_backfill_user_agent_details_organization_id_spec.rb | 2 +-
 1 file changed, 1 insertion(+), 1 deletion(-)

diff --git a/spec/migrations/20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb b/spec/migrations/20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb
index 5f2fd74e359f72..f693440b021e3c 100644
--- a/spec/migrations/20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb
+++ b/spec/migrations/20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb
@@ -14,7 +14,7 @@
 
       migration.after -> {
         expect(batched_migration).to have_scheduled_batched_migration(
-          gitlab_schema: :gitlab_main,
+          gitlab_schema: :gitlab_main_org,
           table_name: :user_agent_details,
           column_name: :id,
           batch_size: described_class::BATCH_SIZE,
-- 
GitLab


From 3897abb9cfa3f4421820f29ccb084f1479fb8896 Mon Sep 17 00:00:00 2001
From: mo khan <mo@mokhan.ca>
Date: Mon, 15 Dec 2025 10:03:25 -0700
Subject: [PATCH 4/9] Apply 2 suggestion(s) to 2 file(s)

Co-authored-by: Andy Schoenen <asoiron@gitlab.com>
---
 .../backfill_user_agent_details_organization_id.yml             | 2 +-
 ...8173859_queue_backfill_user_agent_details_organization_id.rb | 2 +-
 2 files changed, 2 insertions(+), 2 deletions(-)

diff --git a/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml b/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml
index a52391050d4ef4..f6140e22eb7705 100644
--- a/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml
+++ b/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml
@@ -3,6 +3,6 @@ migration_job_name: BackfillUserAgentDetailsOrganizationId
 description: Backfill the sharding key on the user_agent_details table
 feature_category: instance_resiliency
 introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/215327
-milestone: '18.7'
+milestone: '18.8'
 queued_migration_version: 20251208173859
 finalized_by: # version of the migration that finalized this BBM
diff --git a/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb b/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb
index 5e7ecf4e0632ca..2dbae2a3ada510 100644
--- a/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb
+++ b/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb
@@ -1,7 +1,7 @@
 # frozen_string_literal: true
 
 class QueueBackfillUserAgentDetailsOrganizationId < Gitlab::Database::Migration[2.3]
-  milestone '18.7'
+  milestone '18.8'
 
   restrict_gitlab_migration gitlab_schema: :gitlab_main_org
 
-- 
GitLab


From c694aaec339a152f3f8ce195184dac70b5fc2059 Mon Sep 17 00:00:00 2001
From: mo khan <mo@mokhan.ca>
Date: Mon, 15 Dec 2025 10:14:35 -0700
Subject: [PATCH 5/9] Refresh migration timestamps

---
 .../backfill_user_agent_details_organization_id.yml             | 2 +-
 ...170942_queue_backfill_user_agent_details_organization_id.rb} | 0
 db/schema_migrations/20251208173859                             | 1 -
 db/schema_migrations/20251215170942                             | 1 +
 ...2_queue_backfill_user_agent_details_organization_id_spec.rb} | 0
 5 files changed, 2 insertions(+), 2 deletions(-)
 rename db/post_migrate/{20251208173859_queue_backfill_user_agent_details_organization_id.rb => 20251215170942_queue_backfill_user_agent_details_organization_id.rb} (100%)
 delete mode 100644 db/schema_migrations/20251208173859
 create mode 100644 db/schema_migrations/20251215170942
 rename spec/migrations/{20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb => 20251215170942_queue_backfill_user_agent_details_organization_id_spec.rb} (100%)

diff --git a/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml b/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml
index f6140e22eb7705..fa05a4fe07c704 100644
--- a/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml
+++ b/db/docs/batched_background_migrations/backfill_user_agent_details_organization_id.yml
@@ -4,5 +4,5 @@ description: Backfill the sharding key on the user_agent_details table
 feature_category: instance_resiliency
 introduced_by_url: https://gitlab.com/gitlab-org/gitlab/-/merge_requests/215327
 milestone: '18.8'
-queued_migration_version: 20251208173859
+queued_migration_version: 20251215170942
 finalized_by: # version of the migration that finalized this BBM
diff --git a/db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb b/db/post_migrate/20251215170942_queue_backfill_user_agent_details_organization_id.rb
similarity index 100%
rename from db/post_migrate/20251208173859_queue_backfill_user_agent_details_organization_id.rb
rename to db/post_migrate/20251215170942_queue_backfill_user_agent_details_organization_id.rb
diff --git a/db/schema_migrations/20251208173859 b/db/schema_migrations/20251208173859
deleted file mode 100644
index 5124dac923ac23..00000000000000
--- a/db/schema_migrations/20251208173859
+++ /dev/null
@@ -1 +0,0 @@
-547562744d891ce7981c150beab092bbd864ae8bcb44816c36e871ca9f754a35
\ No newline at end of file
diff --git a/db/schema_migrations/20251215170942 b/db/schema_migrations/20251215170942
new file mode 100644
index 00000000000000..d5040f74eee713
--- /dev/null
+++ b/db/schema_migrations/20251215170942
@@ -0,0 +1 @@
+d46b4e76569d49fc319328e2a6fa6e4518e2cd816fecef517813701408825ffd
\ No newline at end of file
diff --git a/spec/migrations/20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb b/spec/migrations/20251215170942_queue_backfill_user_agent_details_organization_id_spec.rb
similarity index 100%
rename from spec/migrations/20251208173859_queue_backfill_user_agent_details_organization_id_spec.rb
rename to spec/migrations/20251215170942_queue_backfill_user_agent_details_organization_id_spec.rb
-- 
GitLab


From ae57961e123d5eabaa2cd4e2cc813c57368cda6f Mon Sep 17 00:00:00 2001
From: mo khan <mo@mokhan.ca>
Date: Tue, 16 Dec 2025 11:35:28 -0700
Subject: [PATCH 6/9] Rename constant

---
 .../backfill_user_agent_details_organization_id.rb            | 4 ++--
 1 file changed, 2 insertions(+), 2 deletions(-)

diff --git a/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb b/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
index b1c51766fbb692..6eb2ac99d942e8 100644
--- a/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
+++ b/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
@@ -6,11 +6,11 @@ class BackfillUserAgentDetailsOrganizationId < BatchedMigrationJob
       operation_name :backfill_user_agent_details_organization_id
       feature_category :instance_resiliency
 
-      DEFAULT_ORGANIZATION_ID = 1
+      ORGANIZATION_ID = 1
 
       def perform
         each_sub_batch do |sub_batch|
-          sub_batch.where(organization_id: nil).update_all(organization_id: DEFAULT_ORGANIZATION_ID)
+          sub_batch.where(organization_id: nil).update_all(organization_id: ORGANIZATION_ID)
         end
       end
     end
-- 
GitLab


From 6686c5af666833e500721060abc18984ae4dc73c Mon Sep 17 00:00:00 2001
From: mo khan <mo@mokhan.ca>
Date: Tue, 16 Dec 2025 11:36:16 -0700
Subject: [PATCH 7/9] Fix linter error

---
 .../backfill_user_agent_details_organization_id.rb            | 4 +---
 1 file changed, 1 insertion(+), 3 deletions(-)

diff --git a/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb b/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
index 6eb2ac99d942e8..a472fb7ec9ebe5 100644
--- a/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
+++ b/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
@@ -6,11 +6,9 @@ class BackfillUserAgentDetailsOrganizationId < BatchedMigrationJob
       operation_name :backfill_user_agent_details_organization_id
       feature_category :instance_resiliency
 
-      ORGANIZATION_ID = 1
-
       def perform
         each_sub_batch do |sub_batch|
-          sub_batch.where(organization_id: nil).update_all(organization_id: ORGANIZATION_ID)
+          sub_batch.where(organization_id: nil).update_all(organization_id: 1)
         end
       end
     end
-- 
GitLab


From 7b4c169f3bbcee9b5edbae00d84407258f1f3400 Mon Sep 17 00:00:00 2001
From: mo khan <mo@mokhan.ca>
Date: Tue, 16 Dec 2025 11:44:34 -0700
Subject: [PATCH 8/9] Use existing constant

---
 .../backfill_user_agent_details_organization_id.rb            | 4 +++-
 1 file changed, 3 insertions(+), 1 deletion(-)

diff --git a/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb b/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
index a472fb7ec9ebe5..7100f92e82a92e 100644
--- a/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
+++ b/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
@@ -8,7 +8,9 @@ class BackfillUserAgentDetailsOrganizationId < BatchedMigrationJob
 
       def perform
         each_sub_batch do |sub_batch|
-          sub_batch.where(organization_id: nil).update_all(organization_id: 1)
+          sub_batch
+            .where(organization_id: nil)
+            .update_all(organization_id: ::Organizations::Organization::DEFAULT_ORGANIZATION_ID)
         end
       end
     end
-- 
GitLab


From ce93637827c1580baa6aa4fb43978b65b4d323db Mon Sep 17 00:00:00 2001
From: mo khan <mo@mokhan.ca>
Date: Tue, 16 Dec 2025 12:04:29 -0700
Subject: [PATCH 9/9] Fix linter error

---
 .../backfill_user_agent_details_organization_id.rb            | 4 +++-
 1 file changed, 3 insertions(+), 1 deletion(-)

diff --git a/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb b/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
index 7100f92e82a92e..c4a5719dede5d3 100644
--- a/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
+++ b/lib/gitlab/background_migration/backfill_user_agent_details_organization_id.rb
@@ -6,11 +6,13 @@ class BackfillUserAgentDetailsOrganizationId < BatchedMigrationJob
       operation_name :backfill_user_agent_details_organization_id
       feature_category :instance_resiliency
 
+      ORGANIZATION_ID = 1
+
       def perform
         each_sub_batch do |sub_batch|
           sub_batch
             .where(organization_id: nil)
-            .update_all(organization_id: ::Organizations::Organization::DEFAULT_ORGANIZATION_ID)
+            .update_all(organization_id: ORGANIZATION_ID)
         end
       end
     end
-- 
GitLab