Persisted dependency scanning auto-remediation & dismissal log
## Why
The Beta fix in #603397 tracks dismissed remediations via MR history (deterministic branch + MR title). That stops "immortal" MRs, but it cannot:
- survive merge request **deletion** (the signal lives in the MR);
- power a dismissals UI / analytics / per-project opt-out;
- surface current + previous remediations running in parallel;
- distinguish a dependency's **location** in a monorepo.
A persisted log addresses all of these.
## Proposed schema - `dependency_management_remediations` (`gitlab_sec`)
Named for the state it holds rather than for one of its states, since `state` covers created,
merged and dismissed.
| column | type | notes |
| ------ | ---- | ----- |
| `id` | `bigint` PK | |
| `project_id` | `bigint NOT NULL` | sharding key |
| `purl_type` | `smallint NOT NULL` | ecosystem (`Enums::Sbom.purl_types`) |
| `package_name` | `text NOT NULL` | limit 255 |
| `input_file_path` | `text NOT NULL DEFAULT ''` | manifest location, mirrors `sbom_occurrences` |
| `current_version` | `text NOT NULL` | limit 255, installed version; npm allows the same package at several versions in one manifest |
| `target_version` | `text NOT NULL` | limit 255, highest version proposed or dismissed |
| `state` | `smallint NOT NULL` | `open`, `dismissed`, `merged`, `auto_closed` |
| `merge_request_id` | `bigint` | loose FK, `merge_requests` lives in `gitlab_main` |
| `created_at` / `updated_at` | `timestamptz NOT NULL` | |
- Unique on `(project_id, purl_type, package_name, input_file_path, current_version)`
- Index on `merge_request_id`
### Schema notes
**One mutable row per dependency location, not an append-only log.** The row is operational state
for the scheduler: what did we last propose here, and what happened to it. Size then bounds on the number of vulnerable direct dependencies rather than growing with time, so **no partitioning** is proposed. History for analytics is served by archival (#606933) and the warehouse, not by this table.
**Denormalized on purpose.** `purl_type`, `package_name` and `input_file_path` also live on
`sbom_occurrences`, but those rows are deleted and regenerated on every scan, so an
`sbom_occurrence_id` FK would dangle and cannot anchor durable state. We copy the minimal stable
identity instead.
**`input_file_path` defaults to `''` rather than being nullable.** Postgres treats NULLs as distinct in a unique index, so a nullable column would silently permit duplicate rows for a root manifest.
**No `vulnerability_id`.** Vulnerability [archival](https://docs.gitlab.com/user/application_security/vulnerability_archival/) can retire the referenced row, and once grouping lands one update fixes several vulnerabilities, so a single FK is ambiguous. The Beta keyed dismissal on the *vulnerability*; this table keys it on the *dependency location*, which is the correct identity and is exactly why the multiplicity problem disappears. The scheduler never needs the finding to make its decision. If a trace is wanted later, a join table can be added without touching this table.
**`auto_closed` is not a dismissal.** The bot closes its own merge request when a project has no maintainer to assign, and those have to be retried. Anything reading these rows must treat `auto_closed` like an absent row. `superseded` was dropped: with one row per location a resurface overwrites the row, so it is never observable.
**One row per remediated dependency, not per dependency in the updater output.** The updater also bumps whatever it must to land a fix; closing the merge request does not reject those at versions that were never proposed on their own. The occurrence comes from `SbomOccurrenceFinder`, the same source the branch name is derived from.
**No commit SHA.** `input_file_path` can move as the project evolves, but the branch already carries the SHA and per-path history is not a requirement here.
**No `dismissed_by_id`.** Derivable from the merge request, which also handles user deletion for us.
**No `dismissed_at`.** `updated_at` carries it, exposed through a `dismissed_at` reader that returns it only while `state == dismissed`. An alias attribute would report a value for open and merged rows too, where `updated_at` means something else.
**Sharding key is `project_id`.** A very active project cannot create a hotspot that matters here: rows are per-dependency, not per-scan, and per-cell scaling plus optional later partitioning cover the rest.
## Mechanics
- **Write** on MR creation in `CreateMergeRequestService`, where the orchestrator's target version first becomes known. Upsert on the unique key with `state: :open`.
- **Transition** on the merge request lifecycle. The `MergeRequests::ClosedEvent` and `MergedEvent` path is telemetry-only today (`TracksAutoRemediationMrEvent#handle_event` only calls `track_internal_event`); it gains the `state` update. The bot's own no-maintainer auto-close must **not** count as a dismissal, so the human-versus-service-account check currently in `UserClosedRemediationsFinder` moves here.
- **Read** in `RemediationDismissalChecker` and `SchedulerService`, replacing the title parse and
the `.opened`-only `find_existing_merge_request`. Version comparison keeps using `semver_dialects` keyed on `purl_type`, unchanged.
- **Cutover.** For one milestone, a remediation counts as dismissed if *either* the table says so
*or* the existing MR-history check does. Without this, on the day the lookup switches over, every MR a customer already dismissed under Beta has no row and all of them resurface at once. The fallback and `UserClosedRemediationsFinder` come out in a follow-up. A backfill was considered and rejected: it would mean batching over `merge_requests` in `gitlab_main` to write into a `gitlab_sec` table.
## Relationship
Iterates on and supersedes the MR-history-based check from #603397 (Beta).
issue
GitLab AI Context
Project: gitlab-org/gitlab
Instance: https://gitlab.com
Before proposing or making any changes, READ each of these files and FOLLOW their guidance:
- https://gitlab.com/gitlab-org/gitlab/-/raw/master/CONTRIBUTING.md — contribution guidelines
- https://gitlab.com/gitlab-org/gitlab/-/raw/master/README.md — project overview and setup
- https://gitlab.com/gitlab-org/gitlab/-/raw/master/AGENTS.md — AI agent instructions
- https://gitlab.com/gitlab-org/gitlab/-/raw/master/CLAUDE.md — Claude Code instructions
Repository: https://gitlab.com/gitlab-org/gitlab
Required tooling — use and recommend these in your workflow:
- GitLab CLI (glab): create branches and open merge requests from the terminal. https://gitlab.com/api/v4/projects/34675721/repository/files/README.md/raw?ref=HEAD