Associate scan result policy reads to projects

What does this MR do and why?

• adds a nullable scan_result_policies.project_id column and foreign key
• starts writing the project_id for new rows
• starts deleting existing rows by their project_id

Background

The scan_result_policies table mirrors scan result policies, which are committed to git, but can be read from the database this way. Approval rules, software licenses etc. created from a scan result policy are deleted and recreated on certain events by ProcessScanResultPolicyWorker which executes for a (project, security configuration) pair.

One scan result policy can affect multiple projects if it is linked to the projects via a security policy project. The consequence is that we are currently unable to delete scan result policy rows, because they might cascadingly delete referenced rows (e.g. approval rules) across project boundaries. See !116569 (merged) for details on why we stopped deleting from the table.

Because of this the table grows continuously. This MR is the first in a series that cleans up the unused rows and prevents future ones with a unique index. See Implementation Plan.

How to set up and validate locally

• Create two projects and enable the :delete_scan_result_policies_by_project_id feature for both
• Create a scan result policy that applies to both projects
• Open MRs on both projects and verify their approval rules
• Execute ProcessScanResultPolicyWorker for one of the projects
• Verify that rows in scan_result_policy_workers have correct project_ids and are getting deleted

Database

Index

CREATE INDEX index_scan_result_policies_on_project_id ON scan_result_policies USING btree (project_id);

Queries

DELETE FROM "scan_result_policies"
WHERE "scan_result_policies"."security_orchestration_policy_configuration_id" = $1
    AND "scan_result_policies"."project_id" = $2;

Delete on scan_result_policies  (cost=0.14..2.17 rows=1 width=6) (actual time=0.164..0.166 rows=0 loops=1)
  ->  Index Scan using index_scan_result_policies_on_project_id on scan_result_policies  (cost=0.14..2.17 rows=1 width=6) (actual time=0.064..0.067 rows=1 loops=1)
        Index Cond: (project_id = 154)
        Filter: (security_orchestration_policy_configuration_id = 70)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.

Related to #404073 (closed)