Record auto-remediation state as merge requests change
What does this MR do and why?
Builds on the migration that added dismissed_at, now merged.
Fills dependency_management_remediations from the two points that know a remediation changed: creating the merge request, and the close/merge/reopen events that already carry telemetry.
| event | state |
|---|---|
| merge request created | open |
| closed by someone other than the bot | dismissed |
| closed by the bot, by branch deletion, or closer unknown | unchanged |
| reopened | back to open, dismissed rows only |
| merged | merged |
| merge request deleted | row kept, merge_request_id nullified |
A bot close, a branch deletion, or an unknown closer is not a decision about the bump, so those rows are left alone.
Design decisions
| decision | why |
|---|---|
| One row per remediated dependency, at its installed version, not one per dependency in the updater output | The updater also bumps whatever else it must to land the fix, and closing the merge request does not reject those |
The occurrence is named by the workload, via DEPENDENCY_MANAGEMENT_SBOM_OCCURRENCE_ID, resolved through SbomOccurrenceFinder |
A vulnerability can match the same dependency at several installed versions, so deriving it on the merge request side picked the wrong one. Workloads already running when this deploys fall back to the old lowest-id lookup |
dismissed_at is a column, written from the merge request's recorded close time |
Deriving it from updated_at did not work. Every re-proposal against a row that stays dismissed writes updated_at, so the date crept forward and read as a fresh dismissal nobody made. Taking the close time rather than the job's own clock also dates it to when the person actually decided. It is cleared on reopen and when a higher target resurfaces the row |
| A dismissal is only cleared for a strictly higher target, and an uncomparable pair resurfaces rather than going quiet | Nothing enforced this before. The check that would arrives in the follow-up behind a flag that defaults off, so until then a dismissal is invisible whenever the merge request title cannot be parsed or the merge request was deleted, and rows outlive the flag |
Rows key on the dependency location and installed version, not the vulnerability, so declining one bump also suppresses a different vulnerability on that same installed version when its fix is no higher. This widens the check it replaces.
Nothing reads the table yet. The dismissal lookup and the scheduler move over in a follow-up merge request, behind :dependency_management_persisted_remediations.
Database review
Four new queries, all against dependency_management_remediations (gitlab_sec), all single-row and index-backed: the lookup and upsert behind record_proposal, the close/merge update, and the reopen update. The for_merge_request scope has no row of its own because it never runs as a standalone SELECT. It is only the WHERE merge_request_id = ... clause of the two UPDATE queries.
| query | runs | access |
|---|---|---|
record_proposal lookup |
once per merge request created | index scan on the unique index |
record_proposal upsert |
once per merge request created | upsert, arbitrated by the unique index |
transition_for_merge_request |
once per close/merge event | index scan on merge_request_id |
withdraw_dismissal_for_merge_request |
once per reopen event | index scan on merge_request_id, filtered on state |
The table ships empty in !248937 (merged), so the plans below come from a local copy seeded with 200,000 rows across 5,000 projects. Growth is bounded by one row per (project, dependency, installed version) that auto-remediation actually proposes a bump for, and only for projects with an auto-remediation scan profile attached. The lookup and withdraw plans are new, run on a freshly seeded copy with the same numbers, against the table's real schema and index names.
Remediation.record_proposal - lookup, once per merge request created
SELECT dependency_management_remediations.*
FROM dependency_management_remediations
WHERE dependency_management_remediations.project_id = $1
AND dependency_management_remediations.purl_type = $2
AND dependency_management_remediations.package_name = $3
AND dependency_management_remediations.input_file_path = $4
AND dependency_management_remediations.current_version = $5
LIMIT 1;This runs before the upsert below and decides which columns it overwrites.
Row found:
Limit (cost=0.42..2.45 rows=1 width=81) (actual time=0.049..0.049 rows=1 loops=1)
Buffers: shared hit=10
-> Index Scan using idx_dm_remediations_on_project_purl_package_path_and_version on dependency_management_remediations (cost=0.42..2.45 rows=1 width=81) (actual time=0.047..0.048 rows=1 loops=1)
Index Cond: ((project_id = 2) AND (purl_type = 2) AND (package_name = 'pkg-0'::text) AND (input_file_path = 'Cargo.lock'::text) AND (current_version = '1.1.0'::text))
Buffers: shared hit=10
Planning:
Buffers: shared hit=163
Planning Time: 0.587 ms
Execution Time: 0.080 msNo row yet, first proposal for this dependency at this version:
Limit (cost=0.42..2.45 rows=1 width=81) (actual time=0.009..0.010 rows=0 loops=1)
Buffers: shared hit=3
-> Index Scan using idx_dm_remediations_on_project_purl_package_path_and_version on dependency_management_remediations (cost=0.42..2.45 rows=1 width=81) (actual time=0.009..0.009 rows=0 loops=1)
Index Cond: ((project_id = 2) AND (purl_type = 2) AND (package_name = 'pkg-0'::text) AND (input_file_path = 'Cargo.lock'::text) AND (current_version = '9.9.9'::text))
Buffers: shared hit=3
Planning Time: 0.061 ms
Execution Time: 0.017 msRemediation.record_proposal - upsert, once per merge request created
INSERT INTO dependency_management_remediations
(project_id, merge_request_id, purl_type, package_name, input_file_path,
current_version, target_version, state, dismissed_at, created_at, updated_at)
VALUES ($1, $2, $3, $4, $5, $6, $7, $8, $9, $10, $11)
ON CONFLICT (project_id, purl_type, package_name, input_file_path, current_version)
DO UPDATE SET merge_request_id = excluded.merge_request_id,
target_version = excluded.target_version,
state = excluded.state,
dismissed_at = excluded.dismissed_at,
updated_at = excluded.updated_at;When the existing row is dismissed and the new target is not higher, the same statement runs with DO UPDATE SET updated_at = excluded.updated_at only, so the dismissal, its merge request and dismissed_at stay put. The arbiter index and plan shape are the same.
Insert path (no existing row):
Insert on dependency_management_remediations (cost=0.00..0.02 rows=0 width=0) (actual time=0.225..0.225 rows=0 loops=1)
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: idx_dm_remediations_on_project_purl_package_path_and_version
Tuples Inserted: 1
Conflicting Tuples: 0
Buffers: shared hit=58
-> Result (cost=0.00..0.02 rows=1 width=172) (actual time=0.039..0.039 rows=1 loops=1)
Buffers: shared hit=14
Planning Time: 0.161 ms
Execution Time: 0.270 msConflict path (row already present):
Insert on dependency_management_remediations (cost=0.00..0.02 rows=0 width=0) (actual time=0.349..0.349 rows=0 loops=1)
Conflict Resolution: UPDATE
Conflict Arbiter Indexes: idx_dm_remediations_on_project_purl_package_path_and_version
Tuples Inserted: 0
Conflicting Tuples: 1
Buffers: shared hit=62
-> Result (cost=0.00..0.02 rows=1 width=172) (actual time=0.065..0.065 rows=1 loops=1)
Buffers: shared hit=14
Planning Time: 0.273 ms
Execution Time: 0.423 msRemediation.transition_for_merge_request - update, once per close/merge event
UPDATE dependency_management_remediations
SET state = $1, dismissed_at = $2, updated_at = $3
WHERE dependency_management_remediations.merge_request_id = $4; Update on dependency_management_remediations (cost=0.42..2.44 rows=0 width=0) (actual time=0.176..0.177 rows=0 loops=1)
Buffers: shared hit=25
-> Index Scan using index_dependency_management_remediations_on_merge_request_id on dependency_management_remediations (cost=0.42..2.44 rows=1 width=16) (actual time=0.011..0.012 rows=1 loops=1)
Index Cond: (merge_request_id = 13742)
Buffers: shared hit=4
Planning Time: 0.061 ms
Execution Time: 0.236 msThis one is unbounded in principle, since it matches on merge_request_id alone rather than a primary key. In practice a remediation merge request covers a handful of dependencies at most, and the update is deliberately keyed that way so a merge request covering more than one dependency moves all of them.
Remediation.withdraw_dismissal_for_merge_request - update, once per reopen event
UPDATE dependency_management_remediations
SET state = $1, dismissed_at = NULL, updated_at = $2
WHERE dependency_management_remediations.merge_request_id = $3
AND dependency_management_remediations.state = $4;Update on dependency_management_remediations (cost=0.42..2.46 rows=0 width=0) (actual time=0.167..0.167 rows=0 loops=1)
Buffers: shared hit=29
-> Index Scan using index_dependency_management_remediations_on_merge_request_id on dependency_management_remediations (cost=0.42..2.46 rows=1 width=24) (actual time=0.030..0.031 rows=1 loops=1)
Index Cond: (merge_request_id = 10000)
Filter: (state = 1)
Buffers: shared hit=4
Planning:
Buffers: shared hit=3
Planning Time: 0.041 ms
Execution Time: 0.284 msOnly rows in the dismissed state match, so a merged row is never reopened by this update.
References
Related to #603968
MR acceptance checklist
Evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.