Reindex vulnerabilities when their due date changes

What does this MR do and why?

  • Due dates are written with upsert_all/delete_all, which skip callbacks, so a changed due date never reached the new due_date Elasticsearch field from !266731 (merged).
  • BulkSetDueDatesService now syncs the vulnerabilities whose due date actually changed, after each batch, including when a later write fails.
  • A new ES migration, BackfillDueDateInVulnerabilities, reindexes vulnerabilities that already have a due date, so the filter in !266733 (merged) doesn't miss them.

Closes #638048. Design notes are in the issue.

How to test

Requires Elasticsearch in GDK.

  1. Set a due date on a vulnerability with the bulkSetVulnerabilityFindingsDueDates mutation.
  2. Let the ES bookkeeping worker run, then check that the vulnerability's ES document has the new due_date.
  3. Set the same date again and check that nothing is queued for ES.
  4. Remove the due date and check that due_date is gone from the document.
Database queries and plans

All three run on the sec database. Values below are examples.

1. Backfill batch (Vulnerabilities::Read.with_due_date(after_finding_id:), from the ES migration). The migration pages by finding ID (document_primary_key is vulnerability_occurrence_id), and the cursor also applies inside the due dates subquery. Each 30,000-row batch runs this up to 30 times, 1,000 rows at a time (DEFAULT_LIMIT_PER_ITERATION in MigrationDatabaseBackfillHelper).

SELECT "vulnerability_reads".*
FROM "vulnerability_reads"
WHERE (vulnerability_occurrence_id > 0)
  AND "vulnerability_reads"."vulnerability_occurrence_id" IN (
    SELECT "vulnerability_finding_due_dates"."vulnerability_occurrence_id"
    FROM "vulnerability_finding_due_dates"
    WHERE (vulnerability_occurrence_id > 0)
  )
ORDER BY "vulnerability_reads"."vulnerability_occurrence_id" ASC
LIMIT 1000

completed? runs two more queries on every migration run:

-- documents_after_current_id.empty?
SELECT 1 AS one FROM "vulnerability_reads"
WHERE (vulnerability_occurrence_id > 0)
  AND "vulnerability_reads"."vulnerability_occurrence_id" IN (
    SELECT "vulnerability_finding_due_dates"."vulnerability_occurrence_id" FROM "vulnerability_finding_due_dates"
    WHERE (vulnerability_occurrence_id > 0))
LIMIT 1

-- documents_after_current_id.maximum(:vulnerability_occurrence_id)
SELECT MAX("vulnerability_reads"."vulnerability_occurrence_id") FROM "vulnerability_reads"
WHERE (vulnerability_occurrence_id > 0)
  AND "vulnerability_reads"."vulnerability_occurrence_id" IN (
    SELECT "vulnerability_finding_due_dates"."vulnerability_occurrence_id" FROM "vulnerability_finding_due_dates"
    WHERE (vulnerability_occurrence_id > 0))

2. Set or change due dates (BulkSetDueDatesService#upsert_batch, up to 1,000 rows per batch)

RETURNING is new in this MR. Rows whose due date is unchanged are skipped by the WHERE, so only changed findings come back.

INSERT INTO "vulnerability_finding_due_dates"
  ("vulnerability_occurrence_id", "project_id", "due_date", "created_at", "updated_at")
VALUES (1, 1, '2026-11-05', '2026-11-05 10:06:54.378772', '2026-11-05 10:06:54.378772')
ON CONFLICT ("vulnerability_occurrence_id") DO UPDATE
SET due_date = EXCLUDED.due_date, updated_at = EXCLUDED.updated_at
WHERE vulnerability_finding_due_dates.due_date IS DISTINCT FROM EXCLUDED.due_date
RETURNING "vulnerability_occurrence_id"

Plan: https://postgres.ai/console/gitlab/gitlab-production-sec/sessions/81213/commands/189057 (24 ms for a full 1,000-row batch).

  • The plan was run as INSERT ... SELECT from the 1,000 newest due dates, each moved 30 days, so every row takes the ON CONFLICT ... DO UPDATE path. The app sends a VALUES list instead, but conflict handling is the same.

3. Remove due dates (BulkSetDueDatesService#delete_batch, up to 1,000 IDs per batch)

The SELECT is new in this MR. delete_all can't return IDs, so the service plucks the findings that actually have a due date first.

SELECT "vulnerability_finding_due_dates"."vulnerability_occurrence_id"
FROM "vulnerability_finding_due_dates"
WHERE "vulnerability_finding_due_dates"."vulnerability_occurrence_id" IN (1, 2);

DELETE FROM "vulnerability_finding_due_dates"
WHERE "vulnerability_finding_due_dates"."vulnerability_occurrence_id" IN (1, 2);

Plans, for a full 1,000-row batch:

Queries 2 and 3 use the unique index index_vulnerability_finding_due_dates_on_occurrence_id.

GDK verification

Ran BulkSetDueDatesService from this branch on vulnerability 422, then drained the ES queue:

Scenario Synced IDs ES doc due_date
Change to 2026-12-15 [422] "2026-12-15"
Same date again [] unchanged
Remove [422] absent
Remove again [] unchanged
Restore 2026-10-31 [422] "2026-10-31"

Forced delete_all to raise in a batch that also set 422's date: the service raised, and 422's new date still reached ES.

MR acceptance checklist

Evaluate this MR against the MR acceptance checklist.

🤖 Generated with Claude Code

Edited by AJ Biton

Merge request reports

Loading
Loading