Implement triggers to keep the `vulnerability_reads table` in sync

Why are we doing this work

As we are constantly getting time-out errors while loading the group&instance-level vulnerability reports pages, we wanted to implement a performant solution to query our complex data model. We are basically creating a new table to denormalize our data to make it easier to query which will improve the performance. This approach is called "Command Query Responsibility Segregation" which in short is CQRS.

The first implementation task of having a separate read model is defining & creating the read model which will be done within the scope of this issue.

Relevant links

• An example timeout issue: #327607 (closed)

Implementation plan

• database Implement triggers for the following scenarios to keep the vulnerability_reads table in synch;
  1. A trigger to insert into this table whenever we have a new record created in the vulnerabilities table
  2. A trigger to update the related record when a duplicated column of a vulnerability gets updated in the vulnerabilities table
  3. A trigger to update the has_issues flag, when we create an issue for a vulnerability

Trigger & function 1

create or replace function insert_vulnerability_reads() returns trigger
    language plpgsql
    as $$
declare
  scanner_id bigint;
  has_issues boolean;
begin
  select 
    vulnerability_scanners.id into scanner_id 
  from 
    vulnerability_occurrences
  inner join vulnerability_scanners on vulnerability_scanners.id = vulnerability_occurrences.scanner_id
  where 
    vulnerability_occurrences.vulnerability_id = new.id;

  select exists(
    select 
      1 
    from 
      vulnerability_issue_links 
    where vulnerability_issue_links.vulnerability_id = new.id
  ) into has_issues;
  
  insert into vulnerability_reads (vulnerability_id, projects_id, report_type, scanner_id, severity, state, has_issues, resolved_on_default_branch)
    values (new.id, new.project_id, new.report_type, scanner_id, new.severity, new.state, has_issues, new.resolved_on_default_branch);
return null;
end
$$;

create trigger insert_vulnerability_reads after insert on vulnerabilities for each row execute procedure insert_vulnerability_reads();

Trigger & function 2

create or replace function update_vulnerability_reads_from_vulnerability() returns trigger
  language plpgsql
  as $$
begin
  update 
    vulnerability_reads 
  set 
    severity = new.severity, 
    state = new.state, 
    resolved_on_default_branch = new.resolved_on_default_branch
  where vulnerability_id = new.id;
return null;
end
$$;

create trigger update_vulnerability_reads_from_vulnerability after update on vulnerabilities for each row execute procedure update_vulnerability_reads_from_vulnerability();

Trigger & function 3

create or replace function set_has_issues_on_vulnerability_reads() returns trigger
  language plpgsql
  as $$
begin
  update 
    vulnerability_reads 
  set 
    has_issues = true 
  where 
    vulnerability_id = new.vulnerability_id;
return null;
end
$$;

create trigger set_has_issues_on_vulnerability_reads after update on vulnerability_issue_links for each row execute procedure set_has_issues_on_vulnerability_reads();