VSA: Persist "last_approved_at" in the database

Background

Code reviews are a crucial part of the development process, where team members can provide feedback on each other’s code to ensure code quality, maintainability, and adherence to coding standards.

When creating a Value Stream in our Value Stream Analytics, we're given limited number of Start and End events.

While we have access to these Start Events related to Merge Requests:

• created
• first assigned
• first commit time
• first deployed to production
• label was added
• label was removed
• last build finish time
• merged
• reviewer first assigned

This gives us little insight into the natural flow of MRs with multiple reviews and reviewers, and currently, we don't have a way to track when a merge request was last approved

Objective

Implement the logic to persist the "last_approved_at" timestamp for merge requests in the database.

Implementation Details

1. Database Changes:

   • Create a new table named merge_request_approval_metrics with the following columns:
     • merge_request_id (Primary Key)
     • last_approved_at (timestamp)

2. Background Worker:

   • Create a new background worker named MergeRequest::ApprovalMetricsWorker
   • This worker will subscribe to the MergeRequests::ApprovedEvent
   • When an approval event is received, the worker will:
     • Check if an entry exists for the merge request in the merge_request_approval_metrics table
     • If it exists, update the last_approved_at timestamp
     • If it doesn't exist, create a new entry with the current timestamp

3. Changes to MergeRequestMetricsCalculator.rb:

   • Update MergeRequestMetricsCalculator to include last_approved_at

4. Testing:

   • Write unit tests for the new background worker
   • Write integration tests to ensure the timestamp is correctly updated when a merge request is approved

5. Documentation:

   • Update relevant documentation to reflect the new feature
   • Add information about the new table and its purpose to the database schema documentation

Acceptance Criteria

• New merge_request_approval_timestamps table is created in the database
• MergeRequest::ApprovalMetricsWorker is implemented and correctly subscribes to MergeRequests::ApprovedEvent
• Worker correctly updates or creates entries in the merge_request_approval_timestamps table
• All tests pass
• Documentation is updated

Next iteration

VSA: Add last_approved_at event (#503754 - closed)