security_report_ingestion_overview.md

Technical context
-----------------------

`UPSERT` queries **require acquiring locks on unique index
tuples**. This will cause lock contention if multiple processes try to
`UPSERT` records with the same unique attributes. The lock contention
will make each process wait for the other to complete.

Historical context
-----------------------

The `StoreSecurityReportsWorker` job has the lock-contention issue
described above. It was discovered in [this][0] production incident

This Commit
-----------------------

The short-term solution resolves the lock contention by, in effect,
making the jobs run sequentially. However, going through these jobs
sequentially can take a long time ([somewhere][1] on the order of 3.25
hours to 20 hours)

In this change, we implement a medium-term solution that replaces the
problematic job with a similar job that can make use of our existing
[sidekiq deduplication][2] tooling

With this change, even if a single project suddenly has many pipelines
created for it, only one job will be scheduled and the rest will be
de-duplicated

[0]:gitlab-com/gl-infra/production#17754
[1]:gitlab-com/gl-infra/production#17754 (comment 1831442417)
[2]:https://docs.gitlab.com/ee/development/sidekiq/idempotent_jobs.html#deduplication

related to: #452005

EE: true
Changelog: fixed