BE: Cron job to automatically refresh MR approvals for updated package metadata

The PacakgeMetada::CveEnrichmentSyncWorker runs every hour however the data it consumes is updated on a daily basis, ref: #581332 (comment 2962261724)

From #581332 (comment 2993972194) :

We need to update Security::FindingEnrichment records when the package metadata is refreshed and update MR approvals based on the updated data.

Possible Steps:

  1. Queries security_policies with KEV/EPSS filters
  2. Gets their associated projects via security_policy_project_links
  3. Creates security_finding_enrichments by correlating the updated pm_cve_enrichment CVE with each project's security_findings

This approach minimizes the number of security_findings we need to query, but we parallelize them for each projects which should be fine as we've index for it.

Known limitation: If a policy adds a KEV/EPSS filter after a CVE was introduced in pm_cve_enrichment, matching MRs won't be blocked even though they should be. But I think, We can document this edge case for the initial iteration.

Acceptance Criteria

  • Cron job frequency is optimized based on actual data update schedule
  • Security::FindingEnrichment records are updated when package metadata refreshes
  • MR approvals are recalculated based on updated enrichment data
  • Performance impact is minimized through efficient querying and parallelization

References: #581332 (comment 2993972194)

Edited by Imam Hossain