Draft: Sync schedules when policy changes

What does this MR do and why?

Sync SEP schedules when policy is linked or unlinked

References

1. Persist per-project schedules for scan executio... (!227096 - merged)
2. Add per-project scheduling for scan execution p... (!227101)
3. 👉 Draft: Sync schedules when policy changes (!227102)

Database

The table security_scan_execution_project_schedules is new and doesn't contain data in production. The plans provided are generated locally.

OrchestrationPolicyRuleSchedule scopes

.for_security_policy

SELECT "security_orchestration_policy_rule_schedules".*
FROM "security_orchestration_policy_rule_schedules"
WHERE "security_orchestration_policy_rule_schedules"."security_orchestration_policy_configuration_id" = 309
AND "security_orchestration_policy_rule_schedules"."policy_index" = 0
AND "security_orchestration_policy_rule_schedules"."policy_type" = 0

Seq Scan on security_orchestration_policy_rule_schedules  (cost=0.00..1.05 rows=1 width=84) (actual time=0.004..0.005 rows=1 loops=1)
  Filter: ((security_orchestration_policy_configuration_id = 309) AND (policy_index = 0) AND (policy_type = 0))
  Rows Removed by Filter: 2
Planning Time: 0.706 ms
Execution Time: 0.009 ms

ScanExecutionProjectSchedule scopes

.for_project

SELECT "security_scan_execution_project_schedules".*
FROM "security_scan_execution_project_schedules"
WHERE "security_scan_execution_project_schedules"."project_id" = 53

Index Scan using index_security_scan_execution_project_schedules_on_project_id on security_scan_execution_project_schedules  (cost=0.15..5.24 rows=5 width=60) (actual time=0.034..0.037 rows=1 loops=1)
  Index Cond: (project_id = 53)
Planning Time: 0.604 ms
Execution Time: 0.041 ms

.for_security_policy

SELECT "security_scan_execution_project_schedules".*
FROM "security_scan_execution_project_schedules"
WHERE "security_scan_execution_project_schedules"."security_policy_id" = 826

Index Scan using idx_security_sep_schedules_on_security_policy_id_and_project_id on security_scan_execution_project_schedules  (cost=0.15..5.24 rows=5 width=60) (actual time=0.029..0.033 rows=3 loops=1)
  Index Cond: (security_policy_id = 826)
Planning Time: 0.033 ms
Execution Time: 0.038 ms

.for_rule_schedules + .for_project

SELECT "security_scan_execution_project_schedules".*
FROM "security_scan_execution_project_schedules"
WHERE "security_scan_execution_project_schedules"."policy_rule_schedule_id" IN (258, 274)
AND "security_scan_execution_project_schedules"."project_id" = 53

Index Scan using idx_security_sep_schedules_on_rule_schedule_id_and_project_id on security_scan_execution_project_schedules  (cost=0.15..3.33 rows=1 width=60) (actual time=0.002..0.003 rows=1 loops=1)
  Index Cond: ((policy_rule_schedule_id = ANY ('{258,274}'::bigint[])) AND (project_id = 53))
Planning Time: 0.026 ms
Execution Time: 0.006 ms

How to set up and validate locally

1. Enable the feature flag scan_execution_policy_per_project_scheduling
2. Create a project
3. In the project, create a scheduled scan execution policy
4. In the rails console, verify that a Security::ScanExecutionProjectSchedule record is created
5. Update the policy name
6. In the rails console, verify that the Security::ScanExecutionProjectSchedule record is re-created

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 #592731