Fetch resource link events for timebox reporting
Background
We keep various "resource event" records in these tables:
resource_milestone_events
resource_iteration_events
resource_state_events
resource_weight_events
They are not only useful for generating event notes but also for reconstructing a historical state of an issue/task. For example, when plotting a burndown chart for a milestone (sample: https://gitlab.com/groups/gitlab-org/-/milestones/98#tab-issues), we need to know what state an issue was in. If an issue was closed on a particular date X, the corresponding data point for the closed issues count should reflect this.
What does this MR do and why?
Related to #381879
TimeboxReportService
is the current service responsible for fetching the resource events needed to calculate the data points used to plot a burnup/down chart:
# ee/app/services/timebox_report_service.rb
def resource_events_query
union = Gitlab::SQL::Union.new([resource_timebox_events, state_events, weight_events]) # rubocop: disable Gitlab/Union
Arel::SelectManager.new
.with(materialized_ctes)
.project(Arel.star)
.from("((#{union.to_sql}) ORDER BY created_at, id LIMIT #{EVENT_COUNT_LIMIT + 1}) resource_events_union").to_sql
end
As a part of the refactor to implement the feature requirements in https://gitlab.com/gitlab-org/gitlab/-/issues/381879, I've factored out the fetch logic into a separate service Timebox::EventAggregationService
(ee/app/services/timebox/event_aggregation_service.rb) which is not currently used in production sitting behind the feature flag :rollup_timebox_chart
.
This MR updates Timebox::EventAggregationService
to fetch resource_link_events
in addition to the existing resource events:
union = Gitlab::SQL::Union.new([
resource_timebox_events,
state_events,
weight_events,
+ resource_link_events
])
The change is needed so that we can figure out which tasks belonged to which issues on a past date. In a later MR !148023, the change to Timebox::EventAggregationService
will be used.
Database
-
A sample iteration report from .com https://gitlab.com/gitlab-org/gitlab/-/cadences/28324/iterations/1292151
-
Existing query: https://postgres.ai/console/gitlab/gitlab-production-main/sessions/27320/commands/84918
-
Updated query: https://console.postgres.ai/gitlab/gitlab-production-main/sessions/27320/commands/84919 (uses few more buffers ~790 vs ~721.)
If you want to check out the query locally, find a milestone or iteration with start_date & due_date then run:
[9] pry(main)> Timebox::EventAggregationService.new(Milestone.where("start_date is not null and due_date is not null").first).execute
Milestone Load (0.7ms) SELECT "milestones".* FROM "milestones" WHERE (start_date is not null and due_date is not null) ORDER BY "milestones"."id" ASC LIMIT 1 /*application:console,db_config_name:main,console_hostname:eukos-MacBook-Pro.local,console_username:euko,line:(pry):9:in `__pry__'*/
(1.7ms) WITH "scoped_issue_ids" AS MATERIALIZED (SELECT "resource_milestone_events"."issue_id" FROM "resource_milestone_events" WHERE "resource_milestone_events"."milestone_id" = 65 AND "resource_milestone_events"."action" = 1 AND (created_at <= '2024-01-26 23:59:59.999999')) SELECT * FROM (((SELECT 'timebox' AS event_type, "resource_milestone_events"."id", "resource_milestone_events"."created_at", milestone_id AS value, "resource_milestone_events"."action", "resource_milestone_events"."issue_id" FROM "resource_milestone_events" WHERE (created_at <= '2024-01-26 23:59:59.999999') AND "resource_milestone_events"."issue_id" IN (SELECT * FROM "scoped_issue_ids") LIMIT 20000)
UNION
(SELECT 'state' AS event_type, "resource_state_events"."id", "resource_state_events"."created_at", state AS value, NULL AS action, "resource_state_events"."issue_id" FROM "resource_state_events" WHERE (created_at <= '2024-01-26 23:59:59.999999') AND "resource_state_events"."issue_id" IN (SELECT * FROM "scoped_issue_ids") LIMIT 20000)
UNION
(SELECT 'weight' AS event_type, "resource_weight_events"."id", "resource_weight_events"."created_at", weight AS value, NULL AS action, "resource_weight_events"."issue_id" FROM "resource_weight_events" WHERE (created_at <= '2024-01-26 23:59:59.999999') AND "resource_weight_events"."issue_id" IN (SELECT * FROM "scoped_issue_ids") LIMIT 20000)
UNION
(SELECT 'link' AS event_type, "resource_link_events"."id", "resource_link_events"."created_at", child_work_item_id as value, "resource_link_events"."action", "resource_link_events"."issue_id" FROM "resource_link_events" WHERE (created_at <= '2024-01-26 23:59:59.999999') AND "resource_link_events"."issue_id" IN (SELECT * FROM "scoped_issue_ids") LIMIT 20000)) ORDER BY created_at, id LIMIT 50001) resource_events_union /*application:console,db_config_name:main,console_hostname:eukos-MacBook-Pro.local,console_username:euko,line:/ee/app/services/timebox/event_aggregation_service.rb:42:in `resource_events'*/