Skip to content

Migrate `Vulnerability::Read` create / update logic from postgresql to Rails

Why are we doing this work

Vulnerability::Read records are currently created / updated via PostgresQL triggers. This has many downsides.

  1. It's difficult to change. Every update requires creating a database migration with raw SQL. There is not even a file that we can edit. e.g. !112233 (merged)
  2. It's invisible when reading or grepping application code.
  3. It's difficult to test, since it's decoupled from the application.
  4. It's not re-useable.
  5. It has less flexibility compared to Ruby and the triggers have grown more complex over time, to the point where they would benefit from being written in a more flexible language.
  6. Reviewers aren't accustomed to seeing business logic written in plpgsql, which means that there is a lack of domain expertise among reviewers.
  7. We cannot make use of other application-level logic (ex: feature flags).

We should migrate this logic from Postgres over to Rails.

Relevant links

Non-functional requirements

  • Documentation:
  • Feature flag:
  • Performance:
  • Testing:

Implementation plan

Considerations:

  1. Security report ingestion uses bulk inserts which prevents us from using traditional Rails models. For cases where we need to check values before and after update (ex: Create a vulnerability_read when a vulnerability becomes present on the default branch), we'd need to have the insert return that value and manually check it.
  2. Vulnerabilities / Findings can be created from multiple places and we need to ensure that all of them create / update Reads in a consistent manner.

Verification steps

Edited by Brian Williams