15 files changed, 524 insertions, 312 deletions

diff --git a/spec/models/ci/build_metadata_spec.rb b/spec/models/ci/build_metadata_spec.rb
index b2ffb34da1d..5e30f9160cd 100644
--- a/spec/models/ci/build_metadata_spec.rb
+++ b/spec/models/ci/build_metadata_spec.rb
@@ -133,4 +133,11 @@ RSpec.describe Ci::BuildMetadata do
       expect(build.cancel_gracefully?).to be false
     end
   end
+
+  context 'loose foreign key on ci_builds_metadata.project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_build_metadata, project: parent) }
+    end
+  end
 end
diff --git a/spec/models/ci/build_spec.rb b/spec/models/ci/build_spec.rb
index b8c5af5a911..90298f0e973 100644
--- a/spec/models/ci/build_spec.rb
+++ b/spec/models/ci/build_spec.rb
@@ -3618,20 +3618,6 @@ RSpec.describe Ci::Build do
 
       build.scoped_variables
     end
-
-    context 'when variables builder is used' do
-      it 'returns the same variables' do
-        build.user = create(:user)
-
-        allow(build.pipeline).to receive(:use_variables_builder_definitions?).and_return(false)
-        legacy_variables = build.scoped_variables.to_hash
-
-        allow(build.pipeline).to receive(:use_variables_builder_definitions?).and_return(true)
-        new_variables = build.scoped_variables.to_hash
-
-        expect(new_variables).to eq(legacy_variables)
-      end
-    end
   end
 
   describe '#simple_variables_without_dependencies' do
@@ -3642,160 +3628,6 @@ RSpec.describe Ci::Build do
     end
   end
 
-  shared_examples "secret CI variables" do
-    context 'when ref is branch' do
-      let(:pipeline) { create(:ci_pipeline, project: project) }
-      let(:build) { create(:ci_build, ref: 'master', tag: false, pipeline: pipeline, project: project) }
-
-      context 'when ref is protected' do
-        before do
-          create(:protected_branch, :developers_can_merge, name: 'master', project: project)
-        end
-
-        it { is_expected.to include(variable) }
-      end
-
-      context 'when ref is not protected' do
-        it { is_expected.not_to include(variable) }
-      end
-    end
-
-    context 'when ref is tag' do
-      let(:pipeline) { create(:ci_pipeline, project: project) }
-      let(:build) { create(:ci_build, ref: 'v1.1.0', tag: true, pipeline: pipeline, project: project) }
-
-      context 'when ref is protected' do
-        before do
-          create(:protected_tag, project: project, name: 'v*')
-        end
-
-        it { is_expected.to include(variable) }
-      end
-
-      context 'when ref is not protected' do
-        it { is_expected.not_to include(variable) }
-      end
-    end
-
-    context 'when ref is merge request' do
-      let(:merge_request) { create(:merge_request, :with_detached_merge_request_pipeline) }
-      let(:pipeline) { merge_request.pipelines_for_merge_request.first }
-      let(:build) { create(:ci_build, ref: merge_request.source_branch, tag: false, pipeline: pipeline, project: project) }
-
-      context 'when ref is protected' do
-        before do
-          create(:protected_branch, :developers_can_merge, name: merge_request.source_branch, project: project)
-        end
-
-        it 'does not return protected variables as it is not supported for merge request pipelines' do
-          is_expected.not_to include(variable)
-        end
-      end
-
-      context 'when ref is not protected' do
-        it { is_expected.not_to include(variable) }
-      end
-    end
-  end
-
-  describe '#secret_instance_variables' do
-    subject { build.secret_instance_variables }
-
-    let_it_be(:variable) { create(:ci_instance_variable, protected: true) }
-
-    include_examples "secret CI variables"
-  end
-
-  describe '#secret_group_variables' do
-    subject { build.secret_group_variables }
-
-    let_it_be(:variable) { create(:ci_group_variable, protected: true, group: group) }
-
-    include_examples "secret CI variables"
-  end
-
-  describe '#secret_project_variables' do
-    subject { build.secret_project_variables }
-
-    let_it_be(:variable) { create(:ci_variable, protected: true, project: project) }
-
-    include_examples "secret CI variables"
-  end
-
-  describe '#kubernetes_variables' do
-    let(:build) { create(:ci_build) }
-    let(:service) { double(execute: template) }
-    let(:template) { double(to_yaml: 'example-kubeconfig', valid?: template_valid) }
-    let(:template_valid) { true }
-
-    subject { build.kubernetes_variables }
-
-    before do
-      allow(Ci::GenerateKubeconfigService).to receive(:new).with(build).and_return(service)
-    end
-
-    it { is_expected.to include(key: 'KUBECONFIG', value: 'example-kubeconfig', public: false, file: true) }
-
-    context 'generated config is invalid' do
-      let(:template_valid) { false }
-
-      it { is_expected.not_to include(key: 'KUBECONFIG', value: 'example-kubeconfig', public: false, file: true) }
-    end
-  end
-
-  describe '#deployment_variables' do
-    let(:build) { create(:ci_build, environment: environment) }
-    let(:environment) { 'production' }
-    let(:kubernetes_namespace) { 'namespace' }
-    let(:project_variables) { double }
-
-    subject { build.deployment_variables(environment: environment) }
-
-    before do
-      allow(build).to receive(:expanded_kubernetes_namespace)
-        .and_return(kubernetes_namespace)
-
-      allow(build.project).to receive(:deployment_variables)
-        .with(environment: environment, kubernetes_namespace: kubernetes_namespace)
-        .and_return(project_variables)
-    end
-
-    context 'environment is nil' do
-      let(:environment) { nil }
-
-      it { is_expected.to be_empty }
-    end
-  end
-
-  describe '#user_variables' do
-    subject { build.user_variables.to_hash }
-
-    context 'with user' do
-      let(:expected_variables) do
-        {
-          'GITLAB_USER_EMAIL' => user.email,
-          'GITLAB_USER_ID' => user.id.to_s,
-          'GITLAB_USER_LOGIN' => user.username,
-          'GITLAB_USER_NAME' => user.name
-        }
-      end
-
-      before do
-        build.user = user
-      end
-
-      it { is_expected.to eq(expected_variables) }
-    end
-
-    context 'without user' do
-      before do
-        expect(build).to receive(:user).and_return(nil)
-      end
-
-      it { is_expected.to be_empty }
-    end
-  end
-
   describe '#any_unmet_prerequisites?' do
     let(:build) { create(:ci_build, :created) }
 
diff --git a/spec/models/ci/build_trace_chunk_spec.rb b/spec/models/ci/build_trace_chunk_spec.rb
index 31c7c7a44bc..e08fe196d65 100644
--- a/spec/models/ci/build_trace_chunk_spec.rb
+++ b/spec/models/ci/build_trace_chunk_spec.rb
@@ -851,7 +851,7 @@ RSpec.describe Ci::BuildTraceChunk, :clean_gitlab_redis_shared_state, :clean_git
 
     context 'when project is destroyed' do
       let(:subject) do
-        Projects::DestroyService.new(project, project.owner).execute
+        Projects::DestroyService.new(project, project.first_owner).execute
       end
 
       it_behaves_like 'deletes all build_trace_chunk and data in redis'
diff --git a/spec/models/ci/job_artifact_spec.rb b/spec/models/ci/job_artifact_spec.rb
index 2e8c41b410a..bd0397e0396 100644
--- a/spec/models/ci/job_artifact_spec.rb
+++ b/spec/models/ci/job_artifact_spec.rb
@@ -703,4 +703,11 @@ RSpec.describe Ci::JobArtifact do
   it_behaves_like 'it has loose foreign keys' do
     let(:factory_name) { :ci_job_artifact }
   end
+
+  context 'loose foreign key on ci_job_artifacts.project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_job_artifact, project: parent) }
+    end
+  end
 end
diff --git a/spec/models/ci/job_token/project_scope_link_spec.rb b/spec/models/ci/job_token/project_scope_link_spec.rb
index 8d7bb44bd16..c000a3e29f7 100644
--- a/spec/models/ci/job_token/project_scope_link_spec.rb
+++ b/spec/models/ci/job_token/project_scope_link_spec.rb
@@ -88,4 +88,18 @@ RSpec.describe Ci::JobToken::ProjectScopeLink do
       it { is_expected.to be_nil }
     end
   end
+
+  context 'loose foreign key on ci_job_token_project_scope_links.source_project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_job_token_project_scope_link, source_project: parent) }
+    end
+  end
+
+  context 'loose foreign key on ci_job_token_project_scope_links.target_project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_job_token_project_scope_link, target_project: parent) }
+    end
+  end
 end
diff --git a/spec/models/ci/namespace_mirror_spec.rb b/spec/models/ci/namespace_mirror_spec.rb
index a9d916115fc..38471f15849 100644
--- a/spec/models/ci/namespace_mirror_spec.rb
+++ b/spec/models/ci/namespace_mirror_spec.rb
@@ -20,10 +20,10 @@ RSpec.describe Ci::NamespaceMirror do
   end
 
   context 'scopes' do
-    describe '.contains_namespace' do
+    describe '.by_group_and_descendants' do
       let_it_be(:another_group) { create(:group) }
 
-      subject(:result) { described_class.contains_namespace(group2.id) }
+      subject(:result) { described_class.by_group_and_descendants(group2.id) }
 
       it 'returns groups having group2.id in traversal_ids' do
         expect(result.pluck(:namespace_id)).to contain_exactly(group2.id, group3.id, group4.id)
diff --git a/spec/models/ci/pipeline_schedule_spec.rb b/spec/models/ci/pipeline_schedule_spec.rb
index 0f1cb721e95..0f4f148775e 100644
--- a/spec/models/ci/pipeline_schedule_spec.rb
+++ b/spec/models/ci/pipeline_schedule_spec.rb
@@ -227,4 +227,11 @@ RSpec.describe Ci::PipelineSchedule do
       it { is_expected.to eq(144) }
     end
   end
+
+  context 'loose foreign key on ci_pipeline_schedules.project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_pipeline_schedule, project: parent) }
+    end
+  end
 end
diff --git a/spec/models/ci/pipeline_spec.rb b/spec/models/ci/pipeline_spec.rb
index 90f56c1e0a4..c29cc04e0e9 100644
--- a/spec/models/ci/pipeline_spec.rb
+++ b/spec/models/ci/pipeline_spec.rb
@@ -390,20 +390,63 @@ RSpec.describe Ci::Pipeline, :mailer, factory_default: :keep do
   end
 
   describe '#merge_request?' do
-    let(:pipeline) { create(:ci_pipeline, merge_request: merge_request) }
-    let(:merge_request) { create(:merge_request) }
+    let_it_be(:merge_request) { create(:merge_request) }
+    let_it_be_with_reload(:pipeline) do
+      create(:ci_pipeline, project: project, merge_request_id: merge_request.id)
+    end
+
+    it { expect(pipeline).to be_merge_request }
 
-    it 'returns true' do
-      expect(pipeline).to be_merge_request
+    context 'when merge request is already loaded' do
+      it 'does not reload the record and returns true' do
+        expect(pipeline.merge_request).to be_present
+
+        expect { pipeline.merge_request? }.not_to exceed_query_limit(0)
+        expect(pipeline).to be_merge_request
+      end
     end
 
-    context 'when merge request is nil' do
-      let(:merge_request) { nil }
+    context 'when merge request is not loaded' do
+      it 'executes a database query and returns true' do
+        expect(pipeline).to be_present
 
-      it 'returns false' do
+        expect { pipeline.merge_request? }.not_to exceed_query_limit(1)
+        expect(pipeline).to be_merge_request
+      end
+
+      it 'caches the result' do
+        expect(pipeline).to be_merge_request
+        expect { pipeline.merge_request? }.not_to exceed_query_limit(0)
+      end
+    end
+
+    context 'when merge request was removed' do
+      before do
+        pipeline.update!(merge_request_id: non_existing_record_id)
+      end
+
+      it 'executes a database query and returns false' do
+        expect { pipeline.merge_request? }.not_to exceed_query_limit(1)
         expect(pipeline).not_to be_merge_request
       end
     end
+
+    context 'when merge request id is not present' do
+      before do
+        pipeline.update!(merge_request_id: nil)
+      end
+
+      it { expect(pipeline).not_to be_merge_request }
+    end
+
+    context 'when the feature flag is disabled' do
+      before do
+        stub_feature_flags(ci_pipeline_merge_request_presence_check: false)
+        pipeline.update!(merge_request_id: non_existing_record_id)
+      end
+
+      it { expect(pipeline).to be_merge_request }
+    end
   end
 
   describe '#detached_merge_request_pipeline?' do
@@ -3384,7 +3427,7 @@ RSpec.describe Ci::Pipeline, :mailer, factory_default: :keep do
       create(:ci_pipeline,
              project: project,
              sha: project.commit('master').sha,
-             user: project.owner)
+             user: project.first_owner)
     end
 
     before do
@@ -4459,7 +4502,7 @@ RSpec.describe Ci::Pipeline, :mailer, factory_default: :keep do
   describe '#reset_source_bridge!' do
     let(:pipeline) { create(:ci_pipeline, :created, project: project) }
 
-    subject(:reset_bridge) { pipeline.reset_source_bridge!(project.owner) }
+    subject(:reset_bridge) { pipeline.reset_source_bridge!(project.first_owner) }
 
     context 'when the pipeline is a child pipeline and the bridge is depended' do
       let!(:parent_pipeline) { create(:ci_pipeline) }
@@ -4656,9 +4699,11 @@ RSpec.describe Ci::Pipeline, :mailer, factory_default: :keep do
     let(:factory_name) { :ci_pipeline }
   end
 
-  it_behaves_like 'cleanup by a loose foreign key' do
-    let!(:model) { create(:ci_pipeline, user: create(:user)) }
-    let!(:parent) { model.user }
+  context 'loose foreign key on ci_pipelines.user_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:model) { create(:ci_pipeline, user: create(:user)) }
+      let!(:parent) { model.user }
+    end
   end
 
   describe 'tags count' do
@@ -4679,4 +4724,55 @@ RSpec.describe Ci::Pipeline, :mailer, factory_default: :keep do
       it { expect(pipeline.distinct_tags_count).to eq(3) }
     end
   end
+
+  context 'loose foreign key on ci_pipelines.merge_request_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:merge_request) }
+      let!(:model) { create(:ci_pipeline, merge_request: parent) }
+    end
+  end
+
+  context 'loose foreign key on ci_pipelines.project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_pipeline, project: parent) }
+    end
+  end
+
+  describe '#jobs_git_ref' do
+    subject { pipeline.jobs_git_ref }
+
+    context 'when tag is true' do
+      let(:pipeline) { build(:ci_pipeline, tag: true) }
+
+      it 'returns a tag ref' do
+        is_expected.to start_with(Gitlab::Git::TAG_REF_PREFIX)
+      end
+    end
+
+    context 'when tag is false' do
+      let(:pipeline) { build(:ci_pipeline, tag: false) }
+
+      it 'returns a branch ref' do
+        is_expected.to start_with(Gitlab::Git::BRANCH_REF_PREFIX)
+      end
+    end
+
+    context 'when tag is nil' do
+      let(:pipeline) { build(:ci_pipeline, tag: nil) }
+
+      it 'returns a branch ref' do
+        is_expected.to start_with(Gitlab::Git::BRANCH_REF_PREFIX)
+      end
+    end
+
+    context 'when it is triggered by a merge request' do
+      let(:merge_request) { create(:merge_request, :with_detached_merge_request_pipeline) }
+      let(:pipeline) { merge_request.pipelines_for_merge_request.first }
+
+      it 'returns nil' do
+        is_expected.to be_nil
+      end
+    end
+  end
 end
diff --git a/spec/models/ci/ref_spec.rb b/spec/models/ci/ref_spec.rb
index 0a9cd5ef2ec..ffbda4b459f 100644
--- a/spec/models/ci/ref_spec.rb
+++ b/spec/models/ci/ref_spec.rb
@@ -231,4 +231,11 @@ RSpec.describe Ci::Ref do
       it_behaves_like 'no-op'
     end
   end
+
+  context 'loose foreign key on ci_refs.project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_ref, project: parent) }
+    end
+  end
 end
diff --git a/spec/models/ci/runner_project_spec.rb b/spec/models/ci/runner_project_spec.rb
index 13369dba2cf..40ad79c7295 100644
--- a/spec/models/ci/runner_project_spec.rb
+++ b/spec/models/ci/runner_project_spec.rb
@@ -6,4 +6,11 @@ RSpec.describe Ci::RunnerProject do
   it_behaves_like 'includes Limitable concern' do
     subject { build(:ci_runner_project, project: create(:project), runner: create(:ci_runner, :project)) }
   end
+
+  context 'loose foreign key on ci_runner_projects.project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_runner_project, project: parent) }
+    end
+  end
 end
diff --git a/spec/models/ci/runner_spec.rb b/spec/models/ci/runner_spec.rb
index 6830a8daa3b..eb29db697a5 100644
--- a/spec/models/ci/runner_spec.rb
+++ b/spec/models/ci/runner_spec.rb
@@ -3,6 +3,8 @@
 require 'spec_helper'
 
 RSpec.describe Ci::Runner do
+  include StubGitlabCalls
+
   it_behaves_like 'having unique enum values'
 
   it_behaves_like 'it has loose foreign keys' do
@@ -23,6 +25,16 @@ RSpec.describe Ci::Runner do
     end
   end
 
+  describe 'projects association' do
+    let(:runner) { create(:ci_runner, :project) }
+
+    it 'does not create a cross-database query' do
+      with_cross_joins_prevented do
+        expect(runner.projects.count).to eq(1)
+      end
+    end
+  end
+
   describe 'validation' do
     it { is_expected.to validate_presence_of(:access_level) }
     it { is_expected.to validate_presence_of(:runner_type) }
@@ -255,22 +267,89 @@ RSpec.describe Ci::Runner do
     it_behaves_like '.belonging_to_parent_group_of_project'
   end
 
-  describe '.owned_or_instance_wide' do
-    it 'returns a globally shared, a project specific and a group specific runner' do
-      # group specific
-      group = create(:group)
-      project = create(:project, group: group)
-      group_runner = create(:ci_runner, :group, groups: [group])
+  context 'with instance runners sharing enabled' do
+    # group specific
+    let_it_be(:group) { create(:group, shared_runners_enabled: true) }
+    let_it_be(:project) { create(:project, group: group, shared_runners_enabled: true) }
+    let_it_be(:group_runner) { create(:ci_runner, :group, groups: [group]) }
 
-      # project specific
-      project_runner = create(:ci_runner, :project, projects: [project])
+    # project specific
+    let_it_be(:project_runner) { create(:ci_runner, :project, projects: [project]) }
 
-      # globally shared
-      shared_runner = create(:ci_runner, :instance)
+    # globally shared
+    let_it_be(:shared_runner) { create(:ci_runner, :instance) }
 
-      expect(described_class.owned_or_instance_wide(project.id)).to contain_exactly(
-        group_runner, project_runner, shared_runner
-      )
+    describe '.owned_or_instance_wide' do
+      subject { described_class.owned_or_instance_wide(project.id) }
+
+      it 'returns a globally shared, a project specific and a group specific runner' do
+        is_expected.to contain_exactly(group_runner, project_runner, shared_runner)
+      end
+    end
+
+    describe '.group_or_instance_wide' do
+      subject { described_class.group_or_instance_wide(group) }
+
+      before do
+        # Ensure the project runner is instantiated
+        project_runner
+      end
+
+      it 'returns a globally shared and a group specific runner' do
+        is_expected.to contain_exactly(group_runner, shared_runner)
+      end
+    end
+  end
+
+  context 'with instance runners sharing disabled' do
+    # group specific
+    let_it_be(:group) { create(:group, shared_runners_enabled: false) }
+    let_it_be(:group_runner) { create(:ci_runner, :group, groups: [group]) }
+
+    let(:group_runners_enabled) { true }
+    let(:project) { create(:project, group: group, shared_runners_enabled: false) }
+
+    # project specific
+    let(:project_runner) { create(:ci_runner, :project, projects: [project]) }
+
+    # globally shared
+    let_it_be(:shared_runner) { create(:ci_runner, :instance) }
+
+    before do
+      project.update!(group_runners_enabled: group_runners_enabled)
+    end
+
+    describe '.owned_or_instance_wide' do
+      subject { described_class.owned_or_instance_wide(project.id) }
+
+      context 'with group runners disabled' do
+        let(:group_runners_enabled) { false }
+
+        it 'returns only the project specific runner' do
+          is_expected.to contain_exactly(project_runner)
+        end
+      end
+
+      context 'with group runners enabled' do
+        let(:group_runners_enabled) { true }
+
+        it 'returns a project specific and a group specific runner' do
+          is_expected.to contain_exactly(group_runner, project_runner)
+        end
+      end
+    end
+
+    describe '.group_or_instance_wide' do
+      subject { described_class.group_or_instance_wide(group) }
+
+      before do
+        # Ensure the project runner is instantiated
+        project_runner
+      end
+
+      it 'returns a group specific runner' do
+        is_expected.to contain_exactly(group_runner)
+      end
     end
   end
 
@@ -291,6 +370,30 @@ RSpec.describe Ci::Runner do
     end
   end
 
+  describe '#only_for' do
+    let_it_be_with_reload(:runner) { create(:ci_runner, :project) }
+    let_it_be(:project) { runner.projects.first }
+
+    subject { runner.only_for?(project) }
+
+    context 'with matching project' do
+      it { is_expected.to be_truthy }
+    end
+
+    context 'without matching project' do
+      let_it_be(:project) { create(:project) }
+
+      it { is_expected.to be_falsey }
+    end
+
+    context 'with runner having multiple projects' do
+      let_it_be(:other_project) { create(:project) }
+      let_it_be(:runner_project) { create(:ci_runner_project, project: other_project, runner: runner) }
+
+      it { is_expected.to be_falsey }
+    end
+  end
+
   describe '#assign_to' do
     let(:project) { create(:project) }
 
@@ -544,7 +647,7 @@ RSpec.describe Ci::Runner do
     end
   end
 
-  describe '#can_pick?' do
+  describe '#matches_build?' do
     using RSpec::Parameterized::TableSyntax
 
     let_it_be(:pipeline) { create(:ci_pipeline) }
@@ -555,31 +658,15 @@ RSpec.describe Ci::Runner do
     let(:tag_list) { [] }
     let(:run_untagged) { true }
 
-    subject { runner.can_pick?(build) }
-
-    context 'a different runner' do
-      let(:other_project) { create(:project) }
-      let(:other_runner) { create(:ci_runner, :project, projects: [other_project], tag_list: tag_list, run_untagged: run_untagged) }
-
-      before do
-        # `can_pick?` is not used outside the runners available for the project
-        stub_feature_flags(ci_runners_short_circuit_assignable_for: false)
-      end
-
-      it 'cannot handle builds' do
-        expect(other_runner.can_pick?(build)).to be_falsey
-      end
-    end
+    subject { runner.matches_build?(build) }
 
     context 'when runner does not have tags' do
-      it 'can handle builds without tags' do
-        expect(runner.can_pick?(build)).to be_truthy
-      end
+      it { is_expected.to be_truthy }
 
       it 'cannot handle build with tags' do
         build.tag_list = ['aa']
 
-        expect(runner.can_pick?(build)).to be_falsey
+        is_expected.to be_falsey
       end
     end
 
@@ -590,20 +677,18 @@ RSpec.describe Ci::Runner do
         it 'can handle build with matching tags' do
           build.tag_list = ['bb']
 
-          expect(runner.can_pick?(build)).to be_truthy
+          is_expected.to be_truthy
         end
 
         it 'cannot handle build without matching tags' do
           build.tag_list = ['aa']
 
-          expect(runner.can_pick?(build)).to be_falsey
+          is_expected.to be_falsey
         end
       end
 
       context 'when runner can pick untagged jobs' do
-        it 'can handle builds without tags' do
-          expect(runner.can_pick?(build)).to be_truthy
-        end
+        it { is_expected.to be_truthy }
 
         it_behaves_like 'tagged build picker'
       end
@@ -611,9 +696,7 @@ RSpec.describe Ci::Runner do
       context 'when runner cannot pick untagged jobs' do
         let(:run_untagged) { false }
 
-        it 'cannot handle builds without tags' do
-          expect(runner.can_pick?(build)).to be_falsey
-        end
+        it { is_expected.to be_falsey }
 
         it_behaves_like 'tagged build picker'
       end
@@ -622,64 +705,31 @@ RSpec.describe Ci::Runner do
     context 'when runner is shared' do
       let(:runner) { create(:ci_runner, :instance) }
 
-      it 'can handle builds' do
-        expect(runner.can_pick?(build)).to be_truthy
-      end
+      it { is_expected.to be_truthy }
 
       context 'when runner is locked' do
         let(:runner) { create(:ci_runner, :instance, locked: true) }
 
-        it 'can handle builds' do
-          expect(runner.can_pick?(build)).to be_truthy
-        end
+        it { is_expected.to be_truthy }
       end
 
       it 'does not query for owned or instance runners' do
         expect(described_class).not_to receive(:owned_or_instance_wide)
 
-        runner.can_pick?(build)
-      end
-
-      context 'when feature flag ci_runners_short_circuit_assignable_for is disabled' do
-        before do
-          stub_feature_flags(ci_runners_short_circuit_assignable_for: false)
-        end
-
-        it 'does not query for owned or instance runners' do
-          expect(described_class).to receive(:owned_or_instance_wide).and_call_original
-
-          runner.can_pick?(build)
-        end
+        subject
       end
     end
 
     context 'when runner is not shared' do
-      before do
-        # `can_pick?` is not used outside the runners available for the project
-        stub_feature_flags(ci_runners_short_circuit_assignable_for: false)
-      end
-
       context 'when runner is assigned to a project' do
-        it 'can handle builds' do
-          expect(runner.can_pick?(build)).to be_truthy
-        end
-      end
-
-      context 'when runner is assigned to another project' do
-        let(:runner_project) { create(:project) }
-
-        it 'cannot handle builds' do
-          expect(runner.can_pick?(build)).to be_falsey
-        end
+        it { is_expected.to be_truthy }
       end
 
       context 'when runner is assigned to a group' do
         let(:group) { create(:group, projects: [build.project]) }
         let(:runner) { create(:ci_runner, :group, tag_list: tag_list, run_untagged: run_untagged, groups: [group]) }
 
-        it 'can handle builds' do
-          expect(runner.can_pick?(build)).to be_truthy
-        end
+        it { is_expected.to be_truthy }
 
         it 'knows namespace id it is assigned to' do
           expect(runner.namespace_ids).to eq [group.id]
@@ -1220,14 +1270,6 @@ RSpec.describe Ci::Runner do
         runner.pick_build!(build)
       end
     end
-
-    context 'build picking improvement' do
-      it 'does not check if the build is assignable to a runner' do
-        expect(runner).not_to receive(:can_pick?)
-
-        runner.pick_build!(build)
-      end
-    end
   end
 
   describe 'project runner without projects is destroyable' do
@@ -1259,6 +1301,20 @@ RSpec.describe Ci::Runner do
 
       expect(runners).to eq([runner2, runner1])
     end
+
+    it 'supports ordering by the token expiration' do
+      runner1 = create(:ci_runner)
+      runner1.update!(token_expires_at: 1.year.from_now)
+      runner2 = create(:ci_runner)
+      runner3 = create(:ci_runner)
+      runner3.update!(token_expires_at: 1.month.from_now)
+
+      runners = described_class.order_by('token_expires_at_asc')
+      expect(runners).to eq([runner3, runner1, runner2])
+
+      runners = described_class.order_by('token_expires_at_desc')
+      expect(runners).to eq([runner2, runner1, runner3])
+    end
   end
 
   describe '.runner_matchers' do
@@ -1386,47 +1442,6 @@ RSpec.describe Ci::Runner do
     it { is_expected.to eq(contacted_at_stored) }
   end
 
-  describe '.legacy_belonging_to_group' do
-    shared_examples 'returns group runners' do
-      it 'returns the specific group runner' do
-        group = create(:group)
-        runner = create(:ci_runner, :group, groups: [group])
-        unrelated_group = create(:group)
-        create(:ci_runner, :group, groups: [unrelated_group])
-
-        expect(described_class.legacy_belonging_to_group(group.id)).to contain_exactly(runner)
-      end
-
-      context 'runner belonging to parent group' do
-        let_it_be(:parent_group) { create(:group) }
-        let_it_be(:parent_runner) { create(:ci_runner, :group, groups: [parent_group]) }
-        let_it_be(:group) { create(:group, parent: parent_group) }
-
-        context 'when include_parent option is passed' do
-          it 'returns the group runner from the parent group' do
-            expect(described_class.legacy_belonging_to_group(group.id, include_ancestors: true)).to contain_exactly(parent_runner)
-          end
-        end
-
-        context 'when include_parent option is not passed' do
-          it 'does not return the group runner from the parent group' do
-            expect(described_class.legacy_belonging_to_group(group.id)).to be_empty
-          end
-        end
-      end
-    end
-
-    it_behaves_like 'returns group runners'
-
-    context 'when feature flag :linear_runner_ancestor_scopes is disabled' do
-      before do
-        stub_feature_flags(linear_runner_ancestor_scopes: false)
-      end
-
-      it_behaves_like 'returns group runners'
-    end
-  end
-
   describe '.belonging_to_group' do
     it 'returns the specific group runner' do
       group = create(:group)
@@ -1470,4 +1485,182 @@ RSpec.describe Ci::Runner do
       )
     end
   end
+
+  describe '#token_expires_at', :freeze_time do
+    shared_examples 'expiring token' do |interval:|
+      it 'expires' do
+        expect(runner.token_expires_at).to eq(interval.from_now)
+      end
+    end
+
+    shared_examples 'non-expiring token' do
+      it 'does not expire' do
+        expect(runner.token_expires_at).to be_nil
+      end
+    end
+
+    context 'no expiration' do
+      let(:runner) { create(:ci_runner) }
+
+      it_behaves_like 'non-expiring token'
+    end
+
+    context 'system-wide shared expiration' do
+      before do
+        stub_application_setting(runner_token_expiration_interval: 5.days.to_i)
+      end
+
+      let(:runner) { create(:ci_runner) }
+
+      it_behaves_like 'expiring token', interval: 5.days
+    end
+
+    context 'system-wide group expiration' do
+      before do
+        stub_application_setting(group_runner_token_expiration_interval: 5.days.to_i)
+      end
+
+      let(:runner) { create(:ci_runner) }
+
+      it_behaves_like 'non-expiring token'
+    end
+
+    context 'system-wide project expiration' do
+      before do
+        stub_application_setting(project_runner_token_expiration_interval: 5.days.to_i)
+      end
+
+      let(:runner) { create(:ci_runner) }
+
+      it_behaves_like 'non-expiring token'
+    end
+
+    context 'group expiration' do
+      let(:group_settings) { create(:namespace_settings, runner_token_expiration_interval: 6.days.to_i) }
+      let(:group) { create(:group, namespace_settings: group_settings) }
+      let(:runner) { create(:ci_runner, :group, groups: [group]) }
+
+      it_behaves_like 'expiring token', interval: 6.days
+    end
+
+    context 'human-readable group expiration' do
+      let(:group_settings) { create(:namespace_settings, runner_token_expiration_interval_human_readable: '7 days') }
+      let(:group) { create(:group, namespace_settings: group_settings) }
+      let(:runner) { create(:ci_runner, :group, groups: [group]) }
+
+      it_behaves_like 'expiring token', interval: 7.days
+    end
+
+    context 'project expiration' do
+      let(:project) { create(:project, runner_token_expiration_interval: 4.days.to_i).tap(&:save!) }
+      let(:runner) { create(:ci_runner, :project, projects: [project]) }
+
+      it_behaves_like 'expiring token', interval: 4.days
+    end
+
+    context 'human-readable project expiration' do
+      let(:project) { create(:project, runner_token_expiration_interval_human_readable: '5 days').tap(&:save!) }
+      let(:runner) { create(:ci_runner, :project, projects: [project]) }
+
+      it_behaves_like 'expiring token', interval: 5.days
+    end
+
+    context 'multiple projects' do
+      let(:project1) { create(:project, runner_token_expiration_interval: 8.days.to_i).tap(&:save!) }
+      let(:project2) { create(:project, runner_token_expiration_interval: 7.days.to_i).tap(&:save!) }
+      let(:project3) { create(:project, runner_token_expiration_interval: 9.days.to_i).tap(&:save!) }
+      let(:runner) { create(:ci_runner, :project, projects: [project1, project2, project3]) }
+
+      it_behaves_like 'expiring token', interval: 7.days
+    end
+
+    context 'with project runner token expiring' do
+      let_it_be(:project) { create(:project, runner_token_expiration_interval: 4.days.to_i).tap(&:save!) }
+
+      context 'project overrides system' do
+        before do
+          stub_application_setting(project_runner_token_expiration_interval: 5.days.to_i)
+        end
+
+        let(:runner) { create(:ci_runner, :project, projects: [project]) }
+
+        it_behaves_like 'expiring token', interval: 4.days
+      end
+
+      context 'system overrides project' do
+        before do
+          stub_application_setting(project_runner_token_expiration_interval: 3.days.to_i)
+        end
+
+        let(:runner) { create(:ci_runner, :project, projects: [project]) }
+
+        it_behaves_like 'expiring token', interval: 3.days
+      end
+    end
+
+    context 'with group runner token expiring' do
+      let_it_be(:group_settings) { create(:namespace_settings, runner_token_expiration_interval: 4.days.to_i) }
+      let_it_be(:group) { create(:group, namespace_settings: group_settings) }
+
+      context 'group overrides system' do
+        before do
+          stub_application_setting(group_runner_token_expiration_interval: 5.days.to_i)
+        end
+
+        let(:runner) { create(:ci_runner, :group, groups: [group]) }
+
+        it_behaves_like 'expiring token', interval: 4.days
+      end
+
+      context 'system overrides group' do
+        before do
+          stub_application_setting(group_runner_token_expiration_interval: 3.days.to_i)
+        end
+
+        let(:runner) { create(:ci_runner, :group, groups: [group]) }
+
+        it_behaves_like 'expiring token', interval: 3.days
+      end
+    end
+
+    context "with group's project runner token expiring" do
+      let_it_be(:parent_group_settings) { create(:namespace_settings, subgroup_runner_token_expiration_interval: 2.days.to_i) }
+      let_it_be(:parent_group) { create(:group, namespace_settings: parent_group_settings) }
+
+      context 'parent group overrides subgroup' do
+        let(:group_settings) { create(:namespace_settings, runner_token_expiration_interval: 3.days.to_i) }
+        let(:group) { create(:group, parent: parent_group, namespace_settings: group_settings) }
+        let(:runner) { create(:ci_runner, :group, groups: [group]) }
+
+        it_behaves_like 'expiring token', interval: 2.days
+      end
+
+      context 'subgroup overrides parent group' do
+        let(:group_settings) { create(:namespace_settings, runner_token_expiration_interval: 1.day.to_i) }
+        let(:group) { create(:group, parent: parent_group, namespace_settings: group_settings) }
+        let(:runner) { create(:ci_runner, :group, groups: [group]) }
+
+        it_behaves_like 'expiring token', interval: 1.day
+      end
+    end
+
+    context "with group's project runner token expiring" do
+      let_it_be(:group_settings) { create(:namespace_settings, project_runner_token_expiration_interval: 2.days.to_i) }
+      let_it_be(:group) { create(:group, namespace_settings: group_settings) }
+
+      context 'group overrides project' do
+        let(:project) { create(:project, group: group, runner_token_expiration_interval: 3.days.to_i).tap(&:save!) }
+        let(:runner) { create(:ci_runner, :project, projects: [project]) }
+
+        it_behaves_like 'expiring token', interval: 2.days
+      end
+
+      context 'project overrides group' do
+        let(:project) { create(:project, group: group, runner_token_expiration_interval: 1.day.to_i).tap(&:save!) }
+        let(:runner) { create(:ci_runner, :project, projects: [project]) }
+
+        it_behaves_like 'expiring token', interval: 1.day
+      end
+    end
+  end
 end
diff --git a/spec/models/ci/sources/pipeline_spec.rb b/spec/models/ci/sources/pipeline_spec.rb
index ccf3140650b..73f7cfa739f 100644
--- a/spec/models/ci/sources/pipeline_spec.rb
+++ b/spec/models/ci/sources/pipeline_spec.rb
@@ -17,4 +17,18 @@ RSpec.describe Ci::Sources::Pipeline do
   it { is_expected.to validate_presence_of(:source_project) }
   it { is_expected.to validate_presence_of(:source_job) }
   it { is_expected.to validate_presence_of(:source_pipeline) }
+
+  context 'loose foreign key on ci_sources_pipelines.source_project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_sources_pipeline, source_project: parent) }
+    end
+  end
+
+  context 'loose foreign key on ci_sources_pipelines.project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_sources_pipeline, project: parent) }
+    end
+  end
 end
diff --git a/spec/models/ci/stage_spec.rb b/spec/models/ci/stage_spec.rb
index 2b6f22e68f1..b91348eb408 100644
--- a/spec/models/ci/stage_spec.rb
+++ b/spec/models/ci/stage_spec.rb
@@ -362,4 +362,11 @@ RSpec.describe Ci::Stage, :models do
   end
 
   it_behaves_like 'manual playable stage', :ci_stage_entity
+
+  context 'loose foreign key on ci_stages.project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_stage_entity, project: parent) }
+    end
+  end
 end
diff --git a/spec/models/ci/trigger_spec.rb b/spec/models/ci/trigger_spec.rb
index c254279a32f..4ac8720780c 100644
--- a/spec/models/ci/trigger_spec.rb
+++ b/spec/models/ci/trigger_spec.rb
@@ -59,6 +59,20 @@ RSpec.describe Ci::Trigger do
   end
 
   it_behaves_like 'includes Limitable concern' do
-    subject { build(:ci_trigger, owner: project.owner, project: project) }
+    subject { build(:ci_trigger, owner: project.first_owner, project: project) }
+  end
+
+  context 'loose foreign key on ci_triggers.owner_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:user) }
+      let!(:model) { create(:ci_trigger, owner: parent) }
+    end
+  end
+
+  context 'loose foreign key on ci_triggers.project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_trigger, project: parent) }
+    end
   end
 end
diff --git a/spec/models/ci/variable_spec.rb b/spec/models/ci/variable_spec.rb
index 93a24ba9157..29ca088ee04 100644
--- a/spec/models/ci/variable_spec.rb
+++ b/spec/models/ci/variable_spec.rb
@@ -44,4 +44,11 @@ RSpec.describe Ci::Variable do
       end
     end
   end
+
+  context 'loose foreign key on ci_variables.project_id' do
+    it_behaves_like 'cleanup by a loose foreign key' do
+      let!(:parent) { create(:project) }
+      let!(:model) { create(:ci_variable, project: parent) }
+    end
+  end
 end