Update sharding_key_id for runners tables sharded by project when original project is deleted

We use a new ci_runners.sharding_key_id column to keep track of which cell a runner belongs to. When the project that is associated with a project runner (i.e. assigned to the sharding_key_id field) is deleted, we are in a situation where the sharding_key_id cannot fulfill its role to determine the owning organization (for example, in the event of an organization move to a new cell). We should therefore recompute the sharding_key_id field for each affected runner, runner manager, and runner tagging. The logic so far has been to use the next most-recent associated project (as determined by the ci_runner_projects table), so for simplicity we should do the same. We could also decide to delete runners that are no longer associated with any project, but that is optional.

Note: we also need to update the respective ci_runner_machines and ci_runner_taggings records.

Example

1. Picture the following records:
   • projects with id: 10
   • ci_runners with id: 1, sharding_key_id: 10
   • ci_runner_machines with runner_id: 1, sharding_key_id: 10
   • ci_runner_machines with runner_id: 1, sharding_key_id: 11
   • ci_runner_projects with id: 1, runner_id: 1, project_id: 10
   • ci_runner_projects with id: 1, runner_id: 1, project_id: 11
2. Now projects with id 10 is deleted. We want to have:
   • ci_runners with id: 1, sharding_key_id: 11
   • ci_runner_machines with runner_id: 1, sharding_key_id: 11
   • ci_runner_projects with id: 1, runner_id: 1, project_id: 11

Proposal

• Leverage the ::Projects::ProjectDeletedEvent to trigger a new ::Ci::Runners::UpdateProjectRunnersOwnerWorker to go through the affected runners and update the sharding_key_id fields. We'll need indices on ci_runners.sharding_key_id and ci_runner_machines.sharding_key_id for efficient lookup (currently only exists on the partitioned table).
• If the runner is not associated with any other projects, delete the runner (source).
• Ensure the behaviors are documented in the relevant documentation page (source).

Verification

With this issue solved, we should see no increase over time in the result of the following queries:

SELECT COUNT(*) FROM ci_runners WHERE runner_type = 3 AND sharding_key_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM ci_runner_projects WHERE ci_runners.sharding_key_id = ci_runner_projects.project_id AND ci_runners.id = ci_runner_projects.runner_id)

ℹ️ info Data state at: 2025-01-08 04:45:30 UTC

7952

SELECT COUNT(*) FROM ci_runners_e59bb2812d WHERE runner_type = 3 AND sharding_key_id IS NOT NULL AND NOT EXISTS (SELECT 1 FROM ci_runner_projects WHERE ci_runners_e59bb2812d.sharding_key_id = ci_runner_projects.project_id AND ci_runners_e59bb2812d.id = ci_runner_projects.runner_id)

ℹ️ info Data state at: 2025-01-08 04:45:30 UTC

7977