Add join table to track scheduled policy pipelines (Part 1/2)

Andy Schoenenrequested to merge
558362-spep-pipeline-dashboard-part1-database into master

What does this MR do and why?

Introduce security_policy_schedule_pipelines table to track CI pipelines created by scheduled security policy executions (both scan execution and pipeline execution schedule policies).

This is part 1 of a 2-part implementation:

  • Part 1 (this MR): Database schema and write operations
  • Part 2 (follow-up): GraphQL API to query the data

This enables tracking which pipelines were triggered by policy schedules, which is a key building block for the policy pipeline dashboard feature.

Design decision: Separate table from test runs

I considered reusing the existing security_scheduled_pipeline_execution_policy_test_runs table (introduced in !219751 (merged) and extended in !222053 (merged)) for the dashboard pipeline tracking. Both tables share the same core foreign keys (security_policy_id, pipeline_id, project_id), but I chose to keep them as separate tables for the following reasons:

  • Different lifecycle patterns: The test run table follows a state machine pattern — records are created before the pipeline exists (pipeline_id is nullable), then updated on pipeline creation and again on completion. The dashboard table (security_policy_schedule_pipelines) is append-only — records are created only after a successful pipeline, and never updated.
  • Different data requirements: Test runs need state (running/complete/failed) and error_message columns to track the test run lifecycle independently of the pipeline. Dashboard records don't need these — pipeline status is available through the Ci::Pipeline association.
  • Different query patterns: The dashboard queries pipelines per policy ordered by created_at desc. Test runs are queried by pipeline_id (for completion events) or by id (for individual status checks). Different access patterns benefit from separate indexes.
  • Simpler invariants: Dashboard records guarantee pipeline_id NOT NULL with a unique constraint. Test runs allow nullable pipeline_id and no uniqueness constraint since a pipeline may fail to be created.

If I later want to show test run pipelines on the dashboard alongside scheduled pipelines, the simplest approach would be to add a source enum column to security_policy_schedule_pipelines and have the TestRunService also write a dashboard record on successful pipeline creation.

Performance considerations

This approach avoids the performance issues from #505031 (closed) by:

  • Recording at write-time (simple INSERT) vs. querying at read-time (complex JOINs)
  • No complex joins to p_ci_builds or p_ci_pipelines tables with filters
  • No time-window or status filtering that requires table scans
  • Indexed lookups only when querying the data

The problematic query in #505031 (closed) joined large partitioned tables with filters on 100+ project IDs, time windows, and build statuses. Our approach uses a small join table with indexed lookups, fundamentally avoiding those performance issues.

How to set up and validate locally

Step 1: Create a group and projects

  1. Create a new group, e.g. my-group
  2. Inside that group, create a target project, e.g. my-group/my-target-project — this is the project whose scheduled pipelines will be created
  3. Inside the same group, create a security policy management project, e.g. my-group/security-policy-project — this will hold both the policy YAML and the CI config file

Step 2: Set up the security policy management project

  1. In my-group/security-policy-project, add a CI config file policy-ci.yml at the root: 
    test-job:
  script:
    - echo "Hello from scheduled pipeline execution policy"
  2. In the same project, add the policy file at .gitlab/security-policies/policy.yml. Note the experiments section at the top which enables the scheduled pipeline execution policy feature: 
    experiments:
  pipeline_execution_schedule_policy:
    enabled: true
pipeline_execution_schedule_policy:
  - name: "My Scheduled PEP"
    description: "Daily scheduled pipeline execution policy for testing"
    enabled: true
    policy_scope: {}
    content:
      include:
        - project: "my-group/security-policy-project"
          file: "policy-ci.yml"
    schedules:
      - type: daily
        start_time: "08:00"
        time_window:
          value: 600
          distribution: random
        timezone: "UTC"

    ⚠️ Replace my-group/security-policy-project with the actual full path of your policy project.

  3. In the security policy project, go to Settings > General > Visibility, project features, permissions and enable Grant security policy project access to CI/CD configuration
  4. Commit both files

Step 3: Link the policy project to the group

  1. Go to my-group → Security & Compliance → Policies
  2. Click Link policy project and select my-group/security-policy-project

This links the policy at the group level, so all projects in my-group (including my-target-project) will inherit the scheduled pipeline execution policy.

Step 4: Verify the policy synced to the database

In Rails console:

group = Group.find_by_full_path('my-group')
config = group.security_orchestration_policy_configuration

# Verify the policy was synced to the database
policy = Security::Policy.type_pipeline_execution_schedule_policy
  .for_policy_configuration(config)
  .first
puts policy.name          # => "My Scheduled PEP"
puts policy.content       # => content hash with include + schedules

# Verify the schedule was created for the target project
target_project = Project.find_by_full_path('my-group/my-target-project')
schedule = Security::PipelineExecutionProjectSchedule.for_policy(policy).for_project(target_project).first
puts schedule.cron        # => "0 8 * * *"
puts schedule.next_run_at # => next scheduled run time

If policy is nil, the policy sync may not have run yet. You can trigger it manually by re-pushing to the policy management project to trigger the sync webhook.

Step 5: Trigger a scheduled pipeline and verify join table record

Rather than waiting for the cron worker, manually trigger the pipeline in Rails console:

target_project = Project.find_by_full_path('my-group/my-target-project')
group = Group.find_by_full_path('my-group')
config = group.security_orchestration_policy_configuration
policy = Security::Policy.type_pipeline_execution_schedule_policy
  .for_policy_configuration(config).first
schedule = Security::PipelineExecutionProjectSchedule.for_policy(policy)
  .for_project(target_project).first

# Trigger the worker manually
Security::PipelineExecutionPolicies::RunScheduleWorker.new.perform(schedule.id)

# Verify the pipeline was created
pipeline = target_project.all_pipelines.last
puts pipeline.source  # => "pipeline_execution_policy_schedule"

# Verify the join table record was created (NEW in this MR)
record = Security::PolicySchedulePipeline.last
puts record.security_policy.name  # => "My Scheduled PEP"
puts record.pipeline.id           # => same as pipeline.id above
puts record.project.id            # => target_project.id

Expected result: A Security::PolicySchedulePipeline record is created linking the policy, pipeline, and project.

Database review

EXPLAIN
SELECT id
FROM security_policies
WHERE security_orchestration_policy_configuration_id = 3178149
  AND policy_index = 0
  AND type = 4
LIMIT 1;

https://console.postgres.ai/gitlab/gitlab-production-main/sessions/50721/commands/150288

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.

Related to #558362

Edited by Andy Schoenen

Merge request reports