Add columns `first_pipeline_id` and `latest_pipeline_id` to `vulnerability_occurrences`

In order to drop the vulnerability_occurrence_pipelines table, we need to preserve the first and latest pipelines in which a vulnerability has occurred in order to maintain the current functionality within GitLab.

The most logical place to place this currently is the vulnerability_occurences table as the logic that depends on this information is most commonly used in association with the finding.

These columns should both be nullable, as continuous scanning or alternative scan sources will not have a pipeline_id to associate.

Implementation Plan

• Add the columns | M (#443280 - closed) • Michael Becker • 16.10
• Add the indexes ASYNC | M (#443281 - closed) • Michael Becker • 16.10
• Add the indexes SYNC | M (#443282 - closed) • Michael Becker • 16.10
• Add the model associations | M (#443283 - closed) • Michael Becker • 16.10
• Add the loose FK config | M (#443284 - closed) • Michael Becker • 16.10

added to epic &11241 (closed)

marked this issue as blocking #422383 (closed)

marked this issue as blocking #422384 (closed)

marked this issue as blocking #422385 (closed)

marked this issue as blocking #422386 (closed)

@ghavenga, Please add a group or category label to identify issue ownership.

You can refer to the Features by Group handbook page for guidance.

If you are unsure about the correct group, please do not leave the issue without a group label, and refer to GitLab's shared responsibility functionality guidelines for more information on how to triage this kind of issue.

This message was generated automatically. You're welcome to improve it.

This issue was automatically tagged with the label ~"group::configure" by TanukiStan, a machine learning classification model, with a probability of 0.45.

If this label is incorrect, please tag this issue with the correct group label as well as automation:ml wrong to help TanukiStan learn from its mistakes.

If you are unsure about the correct group, please do not leave the issue without a group label. Please refer to GitLab's shared responsibility functionality guidelines for more information on how to triage this kind of issues.

Authors who do not have permission to update labels can leave the issue to be triaged by group leaders initially assigned by TanukiStan

This message was generated automatically. You're welcome to improve it.

changed the description

added automation:ml label

changed the description

added database groupthreat insights maintenancerefactor workflowrefinement labels

added typemaintenance label

added devopsgovern sectionsec labels

changed title from Track first_pipeline_id and latest_pipeline_id onvulnerability_occurrences to Add columns first_pipeline_id and latest_pipeline_id onvulnerability_occurrences

changed title from Add columns first_pipeline_id and latest_pipeline_id onvulnerability_occurrences to Add columns first_pipeline_id and latest_pipeline_id to vulnerability_occurrences

mentioned in issue gitlab-org/database-team/team-tasks#370 (closed)

added 1 deleted label

removed 1 deleted label

changed milestone to %16.10

assigned to @wandering_person

mentioned in epic &11241 (closed)

created branch 422382-add-columns-first_pipeline_id-and-latest_pipeline_id-to-vulnerability_occurrences to address this issue

mentioned in merge request !144697 (merged)

mentioned in commit f5eaba5c

added workflowin dev label and removed workflowrefinement label

set weight to 2

marked the checklist item database Add first_pipeline_id and latest_pipeline_id to the vulnerability_occurrences table. as completed

added Deliverable label

mentioned in issue #440863 (closed)

Setting health status to on track as the milestone has just begun.

Issue participants are welcome to override this by setting the health status to another value.

changed health status to on track

mentioned in commit 9632fd76

mentioned in issue gitlab-org/quality/triage-reports#16356 (closed)

mentioned in commit 460f1f9a

mentioned in commit ef040b13

mentioned in commit a96f6172

added workflowin review label and removed workflowin dev label

added #443280 (closed) as child task

added #443281 (closed) as child task

added #443282 (closed) as child task

added #443283 (closed) as child task

added #443284 (closed) as child task

mentioned in commit 94914940

mentioned in commit a90e6a35

created branch 422382-sync-index-creation-for-the-new-vulnerability_occurrences-columns to address this issue

mentioned in merge request !145782 (merged)

mentioned in commit f8a3ee32

changed the description

changed weight to 7 from 2

created branch 422382-loose-fk-and-model-associations-for-the-new-vulnerability_occurrences-columns to address this issue

mentioned in merge request !145788 (merged)

mentioned in commit 5051fa68

mentioned in commit 3daa1ee9

mentioned in commit 552707e5

mentioned in commit cd1bfa69

mentioned in commit 151c1d94

mentioned in commit b9455366

marked the checklist item Add the columns | M (#443280 - closed) • Michael Becker • 16.10 as completed

marked the checklist item Add the indexes ASYNC | M (#443281 - closed) • Michael Becker • 16.10 as completed

mentioned in commit fb7b7225

mentioned in commit becdb6c7

marked the checklist item Add the indexes SYNC | M (#443282 - closed) • Michael Becker • 16.10 as completed

mentioned in issue #440864 (closed)

marked the checklist item Add the loose FK config | M (#443284 - closed) • Michael Becker • 16.10 as completed

marked the checklist item Add the model associations | M (#443283 - closed) • Michael Becker • 16.10 as completed

added workflowverification label and removed workflowin review label

unassigned @wandering_person

removed workflowverification label

added workflowverification label

Verification steps

the new model associations and loose foreign keys are only verified by rspec/ci passing. Not sure if there is more to verify at this point in the epic

closed

added completeverified verified-by-author workflowcomplete labels and removed workflowverification label

assigned to @wandering_person

mentioned in commit a7aae5e0

mentioned in task #449145 (closed)

mentioned in merge request !145923 (merged)

mentioned in commit 0e21ec0b

mentioned in task #450224 (closed)

mentioned in commit 9f92724e

mentioned in task #443495 (closed)

mentioned in commit b0a7fd59

removed the relation with #422386 (closed)

mentioned in commit 41c7dd0c

mentioned in commit 7d1cc193

mentioned in commit b6b501c3

mentioned in issue #422383 (closed)

mentioned in epic gitlab-org#11241 (closed)