Add `has_vulnerability_resolution` column to `vulnerability_reads` table

What does this MR do and why?

This migration adds a has_vulnerability_resolution column to vulnerability_reads table with a default value of false

We need to be able to filter vulnerabilities on whether they have the "Resolve with Duo" button enabled.

This button is enabled if a finding's CWE value is included in this hard-coded list of CWE values.

These values ultimately come from the name column of the vulnerability_identifiers model

Alternatives Considered

We could use the existing identifier_names column to build a scope like this:

  scope :with_duo_resolution, -> do
    where("vulnerability_reads.identifier_names && ARRAY[?]::text[]", Vulnerabilities::Finding::HIGH_CONFIDENCE_AI_RESOLUTION_CWES)
  end

or store a regex-optimized string column and add a scope like:

  AI_RESOLUTION_REGEX = Vulnerabilities::Finding::HIGH_CONFIDENCE_AI_RESOLUTION_CWES.join('|')

  scope :with_duo_resolution, -> do
    where("vulnerability_reads.identifier_names_string ~ ?", AI_RESOLUTION_REGEX)
  end

Why a boolean column was chosen

While the above are quicker to implement and have a much lower maintenance cost (they don't require migrations whenever the list of CWEs changes), ultimately the expected performance of a boolean column outweighed all other considerations.

You can see more detailed discussions in threads on the epic

Follow-up work

Future MRs will address:

• Asynchronously adding an index ¹
• Synchronously adding the index
• adding the application code to populate the column
• adding a batched background migration to backfill existing rows

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

---

epic: &15036 (closed)
resolves: #485583 (closed)
Changelog: added
EE: true

---

1. I would like to do the async index in this MR: !165548 (comment 2097036644) ↩