Skip to content

Add `migrated_to_new_structure` column to `vulnerability_occurrences`

Mehmet Emin INAC requested to merge 239183_add_migrated_to_new_structure_column_to_vulnerability_occurrences into master

What does this MR do and why?

This MR adds a new column to the vulnerability_occurrences table called migrated_to_new_structure which will indicate if the record is using the new schema or not.

As part of &4239, we're removing the raw_metadata field and replacing it with new models and schemas. At the same time, #343332 (closed) will replace the existing ingestion service with a new one; the new service will ingest data to both the old and new schemas, while the old StoreReportService will continue to use only raw_metadata. This flag will help us during the removal effort by clearly indicating which records already have data in the new schema.

After we finish all the ingestion services by !75287 (merged), we will start setting this attribute as true and we will implement a background migration to migrate existing records to the new data schema. The background migration will work only for the findings where migrated_to_new_structure IS FALSE this is why I added the index.

Related to #239183.

Database review

rake db:migrate:up

VERSION=20211129151155 
== 20211129151155 AddMigratedToNewStructureColumnToVulnerabilityOccurrences: migrating
-- add_column(:vulnerability_occurrences, :migrated_to_new_structure, :boolean, {:default=>false, :null=>false})
   -> 0.0085s
== 20211129151155 AddMigratedToNewStructureColumnToVulnerabilityOccurrences: migrated (0.0086s)
VERSION=20211129151832 
== 20211129151832 AddIndexOnVulnerabilityOccurrencesMigratedToNewStructureColumn: migrating
-- transaction_open?()
   -> 0.0000s
-- index_exists?(:vulnerability_occurrences, [:migrated_to_new_structure, :id], {:name=>"index_vulnerability_occurrences_on_migrated_to_new_structure", :algorithm=>:concurrently})
   -> 0.0072s
-- execute("SET statement_timeout TO 0")
   -> 0.0007s
-- add_index(:vulnerability_occurrences, [:migrated_to_new_structure, :id], {:name=>"index_vulnerability_occurrences_on_migrated_to_new_structure", :algorithm=>:concurrently})
   -> 0.2540s
-- execute("RESET statement_timeout")
   -> 0.0007s
== 20211129151832 AddIndexOnVulnerabilityOccurrencesMigratedToNewStructureColumn: migrated (0.2649s)

rake db:migrate:down

VERSION=20211129151155 
== 20211129151155 AddMigratedToNewStructureColumnToVulnerabilityOccurrences: reverting
-- remove_column(:vulnerability_occurrences, :migrated_to_new_structure, :boolean, {:default=>false, :null=>false})
   -> 0.0086s
== 20211129151155 AddMigratedToNewStructureColumnToVulnerabilityOccurrences: reverted (0.0124s)
VERSION=20211129151832 
== 20211129151832 AddIndexOnVulnerabilityOccurrencesMigratedToNewStructureColumn: reverting
-- transaction_open?()
   -> 0.0000s
-- indexes(:vulnerability_occurrences)
   -> 0.0136s
-- execute("SET statement_timeout TO 0")
   -> 0.0010s
-- remove_index(:vulnerability_occurrences, {:algorithm=>:concurrently, :name=>"index_vulnerability_occurrences_on_migrated_to_new_structure"})
   -> 0.0100s
-- execute("RESET statement_timeout")
   -> 0.0008s
== 20211129151832 AddIndexOnVulnerabilityOccurrencesMigratedToNewStructureColumn: reverted (0.0315s)

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

Edited by Mehmet Emin INAC

Merge request reports