Skip to content

Fetch resource link events for timebox reporting

euko requested to merge 381879-fetch-resource-link-events into master

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

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'*/
Edited by euko

Merge request reports