Implement retention period for Security::Finding records

Why are we doing this work

We are using the Security::Finding records to store some metadata of findings to improve the performance of the "pipeline security tab".

Records are being created in the security_scans table after each pipeline finishes, and being removed from this table after the related artifacts expire. However, the records for the locked artifacts stay in the table forever which increases the size of this table therefore we need to implement a different retention period for the "pipeline security tab" and the records stored in the security_findings table.

My suggestion is regardless the related artifact is locked or not, the records from the security_findings table have to be deleted after 1 month.

We've decided to implement 2 different retention policies;

1. security_scans will be marked as purged once the related CI job artifacts are removed.
2. security_scans will be marked as purged for locked artifacts, 90 days after they are created.

Related security_findings records for the purged security_scans will be removed from the database.

This behavior has to be documented and shown in the "pipeline security tab".

Implementation plan

• backend Implement state for Security::Scan records with the following values;
  • created
  • ingesting
  • failed
  • ingested
  • purged
• backend Set the state of the Security::Scan records as ingested in StoreScanService after creating the security findings.
• backend Add the state information to the GraphQL Types::ScanType.
• backend Set the state of the Security::Scan records as purged after deleting the related findings in EE::Ci::JobArtifacts::DestroyBatchService.
• database Implemement partitioned security_findings table
• backend Implement logic to create missing partitions and detach stale partitions for the security_findings table
• backend Set the correct partition number for security_findings while creating them
• backend Implement a new cronjob to set the state of the Security::Scan records as purged after 3 months or change the existing cronjob(Security::Findings::CleanupWorker)(Draft MR: !98102 (merged)).