Add organization_id column to runner tables

MR	Description
1. !193171 (merged) 👈	Add organization_id column to runner tables
2. !193202 (merged)	Populate organization_id in runner tables
3. !194119 (merged)	Remove the populate_organization_id_in_runner_tables FF
4. !193195 (merged)	Backfill organization_id column in runner tables

What does this MR do and why?

This MR adds an organization_id column to runner tables, with the respective indices. This column will eventually replace the sharding_key_id column.

It is a break-up of !189651 (closed), which has already received a few reviews, but was getting too big.

Changelog: other

References

• Add organization_id column to runner tables (#523694 - closed)
• !189651 (closed)

Testing locally

I've tested locally 2 scenarios:

ci_runners_archived table is dropped first (as it happened on .com in %18.0, where PDMs already ran)

git switch pedropombeiro/523694/add-organization_id-to-runner-tables

bundle exec scripts/regenerate-schema

organization_id is added first (as a regular migration in %18.0 on self-managed)

To simulate this scenario, I started from master branch:

git switch master

# Ensuring the test db is clean
bundle exec scripts/regenerate-schema

git switch pedropombeiro/523694/1-add-organization_id-column-to-runner-tables

# Confirm that we have only ci_runners table
gdk psql -d gitlabhq_test_ci -c "SELECT tablename, tableowner FROM pg_tables WHERE tablename in ('ci_runners_e59bb2812d', 'ci_runners', 'ci_runners_archived')"

# Rollback migrations that dropped the archived tables
bundle exec rails db:migrate:down:ci RAILS_ENV=test VERSION=20250422144362 && bundle exec rails db:migrate:down:main RAILS_ENV=test VERSION=20250422144362
bundle exec rails db:migrate:down:ci RAILS_ENV=test VERSION=20250422144359 && bundle exec rails db:migrate:down:main RAILS_ENV=test VERSION=20250422144359

# Ensure that we have ci_runners and ci_runners_archived tables
gdk psql -d gitlabhq_test_ci -c "SELECT tablename, tableowner FROM pg_tables WHERE tablename in ('ci_runners_e59bb2812d', 'ci_runners', 'ci_runners_archived')"

# Bring up the regular migrations that add the organization_id column. This should add the column to both ci_runners and ci_runners_archived tables, and adapt the sync trigger
bundle exec rails db:migrate:up:ci RAILS_ENV=test VERSION=20250602152141 && bundle exec rails db:migrate:up:main RAILS_ENV=test VERSION=20250602152141
bundle exec rails db:migrate:up:ci RAILS_ENV=test VERSION=20250602152146 && bundle exec rails db:migrate:up:main RAILS_ENV=test VERSION=20250602152146
bundle exec rails db:migrate:up:ci RAILS_ENV=test VERSION=20250602152151 && bundle exec rails db:migrate:up:main RAILS_ENV=test VERSION=20250602152151

# Check that the sync trigger also syncs `organization_id` column
gdk psql -d gitlabhq_test_ci -c "INSERT INTO ci_runners (runner_type, sharding_key_id, organization_id, token) VALUES (2, 23, 1, 'some_token2')"
gdk psql -d gitlabhq_test_ci -c "SELECT * FROM ci_runners_archived ORDER BY created_at DESC LIMIT 1"

# Run the remaining post-deployment migrations
bundle exec rails db:migrate RAILS_ENV=test

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.