Add unique index to ScanResultPolicyRead
Description
While investigating gitlab-com/gl-infra/production#8638 (closed) we found that there are duplicate records in scan_result_policies
for a same policy ([security_orchestration_policy_configuration_id, orchestration_policy_idx]
). Those duplicate records will not functionally affect the feature, but they stay as stale records. This could be avoided by adding unique index on [security_orchestration_policy_configuration_id, orchestration_policy_idx]
Implementation Plan
-
!119763 (merged)
- adds nullable
scan_result_policies.project_id
column and foreign key - writes
project_id
for new rows - deletes existing rows by
project_id
- adds nullable
-
!120322 (merged)
- adds nullable
scan_result_policies.rule_index
column - writes
rule_index
for new rows - adds a unique index over
security_orchestration_policy_configuration_id, orchestration_policy_idx, rule_index
(works becauseNULL
isn't comparable in SQL)
- adds nullable
- wait for the background migration of #416261 (closed) to complete
- Add a background migration that deletes all
scan_result_policies
without aproject_id
- Add
NOT NULL
constraint toscan_result_policies.rule_index
column
Edited by Dominic Bauer