SPEP Test Run: Create Security::PolicyScheduleTestRun model and database table

Summary

Create a new model Security::PolicyScheduleTestRun and corresponding database table to store test-run results for Scheduled Pipeline Execution Policies.

Background

This table will store the test-run records including the policy, project, user, pipeline reference, state, and any error messages.

Parent MR: !218585 (closed)

Implementation details

Database Migration

# db/migrate/20260113000000_create_security_policy_schedule_test_runs.rb
class CreateSecurityPolicyScheduleTestRuns < Gitlab::Database::Migration[2.3]
  milestone '18.9'
  disable_ddl_transaction!

  def up
    create_table :security_policy_schedule_test_runs do |t|
      t.bigint :security_policy_id, null: false
      t.bigint :project_id, null: false
      t.bigint :user_id, null: false
      t.bigint :pipeline_id
      t.text :error_message, limit: 1000
      t.timestamps_with_timezone null: false
    end

    add_index :security_policy_schedule_test_runs, [:security_policy_id, :project_id, :created_at],
      name: 'idx_policy_schedule_test_runs_on_policy_project_created'

    add_concurrent_foreign_key :security_policy_schedule_test_runs, :security_policies,
      column: :security_policy_id, on_delete: :cascade
    add_concurrent_foreign_key :security_policy_schedule_test_runs, :projects,
      column: :project_id, on_delete: :cascade
    add_concurrent_foreign_key :security_policy_schedule_test_runs, :users,
      column: :user_id, on_delete: :cascade
  end

  def down
    drop_table :security_policy_schedule_test_runs
  end
end

# db/migrate/20260113000001_add_indexes_to_security_policy_schedule_test_runs.rb
class AddIndexesToSecurityPolicyScheduleTestRuns < Gitlab::Database::Migration[2.3]
  milestone '18.9'
  disable_ddl_transaction!

  def up
    add_concurrent_index :security_policy_schedule_test_runs, :user_id,
      name: 'idx_security_policy_schedule_test_runs_on_user_id', if_not_exists: true
    add_concurrent_index :security_policy_schedule_test_runs, :project_id,
      name: 'idx_security_policy_schedule_test_runs_on_project_id', if_not_exists: true
    add_concurrent_index :security_policy_schedule_test_runs, :pipeline_id,
      name: 'idx_security_policy_schedule_test_runs_on_pipeline_id', if_not_exists: true
  end

  def down
    remove_concurrent_index_by_name :security_policy_schedule_test_runs,
      'idx_security_policy_schedule_test_runs_on_user_id', if_exists: true
    remove_concurrent_index_by_name :security_policy_schedule_test_runs,
      'idx_security_policy_schedule_test_runs_on_project_id', if_exists: true
    remove_concurrent_index_by_name :security_policy_schedule_test_runs,
      'idx_security_policy_schedule_test_runs_on_pipeline_id', if_exists: true
  end
end

# db/migrate/20260115000000_add_state_to_security_policy_schedule_test_runs.rb
class AddStateToSecurityPolicyScheduleTestRuns < Gitlab::Database::Migration[2.3]
  milestone '18.9'

  def change
    add_column :security_policy_schedule_test_runs, :state, :integer, default: 0, null: false
  end
end

Model

# ee/app/models/security/policy_schedule_test_run.rb
module Security
  class PolicyScheduleTestRun < ApplicationRecord
    self.table_name = 'security_policy_schedule_test_runs'

    belongs_to :security_policy, class_name: 'Security::Policy'
    belongs_to :project
    belongs_to :user
    belongs_to :pipeline, class_name: 'Ci::Pipeline', optional: true

    validates :security_policy, :project, :user, presence: true
    validate :security_policy_is_pipeline_execution_schedule_policy
    validate :user_is_project_owner

    enum :state, { running: 0, complete: 1, failed: 2 }, default: :running

    scope :for_policy, ->(policy) { where(security_policy: policy) }
    scope :for_project, ->(project) { where(project: project) }
    scope :recent, -> { where(created_at: 1.hour.ago..) }

    delegate :started_at, :finished_at, :duration, to: :pipeline, allow_nil: true

    def pending?
      pipeline.nil? || pipeline.created?
    end

    def running?
      pipeline&.running? || pipeline&.pending?
    end

    def success?
      pipeline&.success?
    end

    def failed?
      pipeline&.failed? || error_message.present?
    end

    def completed?
      pipeline&.complete?
    end

    def duration_seconds
      duration&.to_i
    end

    private

    def security_policy_is_pipeline_execution_schedule_policy
      return unless security_policy
      return if security_policy.type_pipeline_execution_schedule_policy?

      errors.add(:security_policy, _('must be a pipeline_execution_schedule_policy'))
    end

    def user_is_project_owner
      return unless user && project
      return if project.owner == user || user.can?(:owner_access, project)

      errors.add(:user, _('must be a project owner'))
    end
  end
end

Additional files

# db/docs/security_policy_schedule_test_runs.yml
---
table_name: security_policy_schedule_test_runs
classes:
- Security::PolicyScheduleTestRun
feature_categories:
- security_policy_management
description: Stores test-run results for Scheduled Pipeline Execution Policies
introduced_by_url:
milestone: '18.9'
gitlab_schema: gitlab_main_org
sharding_key:
  project_id: projects
table_size: small

# config/gitlab_loose_foreign_keys.yml (add to existing file)
security_policy_schedule_test_runs:
  - table: p_ci_pipelines
    column: pipeline_id
    on_delete: async_nullify
    worker_class: LooseForeignKeys::CiPipelinesBuildsCleanupCronWorker

• db/docs/security_policy_schedule_test_runs.yml - Database dictionary entry
• config/gitlab_loose_foreign_keys.yml - Add loose FK for pipeline_id