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.
- 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)
- It's invisible when reading or grepping application code.
- It's difficult to test, since it's decoupled from the application.
- It's not re-useable.
- 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.
- Reviewers aren't accustomed to seeing business logic written in plpgsql, which means that there is a lack of domain expertise among reviewers.
- 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:
- 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.
- 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