Set archived, traversal_ids upon vulnerability statistics ingestion

What does this MR do and why?

Set archived and traversal_ids columns of vulnerability_statistics table when ingesting Vulnerabilities::Statistic models.

This only occurs when the severity counts change. See #513228 (comment 2299778448)

This is a follow up to Add traversal_ids column to vulnerability_stati... (#512592 - closed). It's one of the steps involved in resolving cross-database joins b/w vulnerability_statistics of gitlab_sec and tables of the main gitlab schema. See proposal and planning breakdown.

References

Please include cross links to any resources that are relevant to this MR. This will give reviewers and future readers helpful context to give an efficient review of the changes introduced.

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.

How to set up and validate locally

SQL query

The complexity of the query is the same as before. We only pass the extra fields as VALUES.

When running the spec, collecting project.namespace.traversal_ids doesn't cause an extra SELECT query.

INSERT INTO vulnerability_statistics AS target (
  project_id, archived, traversal_ids, 
  letter_grade, created_at, updated_at, 
  "info", "unknown", "low", "medium", 
  "high", "critical"
) 
VALUES 
  (
    421, FALSE, '{841}', 4, now(), now(), 
    1, 0, 0, 1, 0, 1
  ), 
  (
    437, TRUE, '{873}', 4, now(), now(), 
    0, 0, 0, 1, 0, 1
  ) ON CONFLICT (project_id) DO 
UPDATE 
SET 
  "info" = TARGET."info" + EXCLUDED."info", 
  "unknown" = TARGET."unknown" + EXCLUDED."unknown", 
  "low" = TARGET."low" + EXCLUDED."low", 
  "medium" = TARGET."medium" + EXCLUDED."medium", 
  "high" = TARGET."high" + EXCLUDED."high", 
  "critical" = TARGET."critical" + EXCLUDED."critical", 
  letter_grade = (
    SELECT 
      (
        CASE WHEN TARGET.critical + EXCLUDED.critical > 0 THEN 4 WHEN TARGET.high + TARGET.unknown + EXCLUDED.high + EXCLUDED.unknown > 0 THEN 3 WHEN TARGET.medium + EXCLUDED.medium > 0 THEN 2 WHEN TARGET.low + EXCLUDED.low > 0 THEN 1 ELSE 0 END
      ) as letter_grade 
    FROM 
      (
        values 
          (
            TARGET.critical, TARGET.unknown, 
            TARGET.high, TARGET.medium, TARGET.low
          )
      ) as TARGET (
        critical, unknown, high, medium, low
      ), 
      (
        values 
          (
            EXCLUDED.critical, EXCLUDED.unknown, 
            EXCLUDED.high, EXCLUDED.medium, 
            EXCLUDED.low
          )
      ) as EXCLUDED (
        critical, unknown, high, medium, low
      )
  ), 
  updated_at = now()

same query before the change

INSERT INTO vulnerability_statistics AS target (
  project_id, letter_grade, created_at, 
  updated_at, "info", "unknown", "low", 
  "medium", "high", "critical"
) 
VALUES 
  (1, 4, now(), now(), 0, 0, 0, 1, 0, 1), 
  (17, 4, now(), now(), 0, 0, 0, 1, 0, 1) ON CONFLICT (project_id) DO 
UPDATE 
SET 
  "info" = TARGET."info" + EXCLUDED."info", 
  "unknown" = TARGET."unknown" + EXCLUDED."unknown", 
  "low" = TARGET."low" + EXCLUDED."low", 
  "medium" = TARGET."medium" + EXCLUDED."medium", 
  "high" = TARGET."high" + EXCLUDED."high", 
  "critical" = TARGET."critical" + EXCLUDED."critical", 
  letter_grade = (
    SELECT 
      (
        CASE WHEN TARGET.critical + EXCLUDED.critical > 0 THEN 4 WHEN TARGET.high + TARGET.unknown + EXCLUDED.high + EXCLUDED.unknown > 0 THEN 3 WHEN TARGET.medium + EXCLUDED.medium > 0 THEN 2 WHEN TARGET.low + EXCLUDED.low > 0 THEN 1 ELSE 0 END
      ) as letter_grade 
    FROM 
      (
        values 
          (
            TARGET.critical, TARGET.unknown, 
            TARGET.high, TARGET.medium, TARGET.low
          )
      ) as TARGET (
        critical, unknown, high, medium, low
      ), 
      (
        values 
          (
            EXCLUDED.critical, EXCLUDED.unknown, 
            EXCLUDED.high, EXCLUDED.medium, 
            EXCLUDED.low
          )
      ) as EXCLUDED (
        critical, unknown, high, medium, low
      )
  ), 
  updated_at = now()

Related to #513228 (closed)