WorkItems: Update Rolledup dates when a milestone dates change

What does this MR do and why?

WorkItems: Update Rolledup dates when a milestone dates change

As part of the epic, we need to ensure that when a Milestone date changes and it's related to a WorkItem in a hierarchy we update all the parent WorkItems in that hierarchy with rolledup dates to use the most broad start/due dates in its hierarchy.

This is being developed behind the feature flag: work_items_rolledup_dates

Related to: #434835 (closed)

Changelog: added EE: true

FAQ

• These update queries are quite complex, have you thought in another way to that?
  • Yes, these are complex updates, but it has the power to update multiple records at the same time. I don't know how to make this simples without losing (a lot probably) in performance. Also, we have precedence for this kind of query, the same is done on the existing legacy Epics (!14366 (merged))
• Why are we creating a new HierarchiesUpdateService if HierarchyUpdateService already exists?
  • The main reason is that the new one (plural) can update multiple work_item trees at the same time, whilst the existing one only handles 1 work_tree at a time.
• Why not replace HierarchyUpdateService by the new HierarchiesUpdateService then?
  • HierarchyUpdateService is also handling the sync between Epic and WorkItems. Since I'm not sure yet how we're going to handle that in this case, I decided not to change the existing code and handle that in a follow-up. (\cc @felipe_artur since you worked on the sync)

Queries

update start date

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/28218/commands/88009

UPDATE "work_item_dates_sources"
SET
  (
    start_date,
    start_date_sourcing_milestone_id,
    start_date_sourcing_work_item_id
  ) = (
    WITH
      "issues" AS MATERIALIZED (
        SELECT
          "issues"."id" AS id,
          "work_item_parent_links"."work_item_parent_id" AS parent_id,
          "issues"."milestone_id" AS milestone_id,
          "issues"."start_date" AS start_date,
          "issues"."due_date" AS due_date
        FROM
          "issues"
          INNER JOIN "work_item_parent_links" ON "work_item_parent_links"."work_item_id" = "issues"."id"
        WHERE
          "work_item_parent_links"."work_item_parent_id" IN (
            SELECT
              "issues"."id"
            FROM
              "issues"
            WHERE
              "issues"."id" IN (533, 535)
          )
      )
    SELECT
      "dates_sources_union"."start_date",
      "dates_sources_union"."start_date_sourcing_milestone_id",
      "dates_sources_union"."start_date_sourcing_work_item_id"
    FROM
      (
        (
          SELECT
            "issues"."parent_id",
            "milestones"."start_date" AS start_date,
            "milestones"."id" AS start_date_sourcing_milestone_id,
            NULL AS start_date_sourcing_work_item_id
          FROM
            "issues"
            INNER JOIN "milestones" ON "milestones"."id" = "issues"."milestone_id"
        )
        UNION
        (
          SELECT
            "issues"."parent_id",
            "dates_source"."start_date" AS start_date,
            NULL AS start_date_sourcing_milestone_id,
            "dates_source"."issue_id" AS start_date_sourcing_work_item_id
          FROM
            "issues"
            INNER JOIN "work_item_dates_sources" "dates_source" ON "dates_source"."issue_id" = "issues"."id"
          WHERE
            "dates_source"."start_date_is_fixed" = TRUE
        )
        UNION
        (
          SELECT
            "issues"."parent_id",
            "issues"."start_date" AS start_date,
            NULL AS start_date_sourcing_milestone_id,
            "issues"."id" AS start_date_sourcing_work_item_id
          FROM
            "issues"
        )
      ) dates_sources_union
    WHERE
      "dates_sources_union"."start_date" IS NOT NULL
      AND (
        dates_sources_union.parent_id = work_item_dates_sources.issue_id
      )
    ORDER BY
      "dates_sources_union"."start_date" ASC
    LIMIT
      1
  )
WHERE
  "work_item_dates_sources"."issue_id" IN (
    SELECT
      "issues"."id"
    FROM
      "issues"
    WHERE
      "issues"."id" IN (533, 535)
      AND "issues"."id" >= 533
  )

update due date

https://postgres.ai/console/gitlab/gitlab-production-main/sessions/28218/commands/88010

UPDATE "work_item_dates_sources"
SET
  (
    due_date,
    due_date_sourcing_milestone_id,
    due_date_sourcing_work_item_id
  ) = (
    WITH
      "issues" AS MATERIALIZED (
        SELECT
          "issues"."id" AS id,
          "work_item_parent_links"."work_item_parent_id" AS parent_id,
          "issues"."milestone_id" AS milestone_id,
          "issues"."start_date" AS start_date,
          "issues"."due_date" AS due_date
        FROM
          "issues"
          INNER JOIN "work_item_parent_links" ON "work_item_parent_links"."work_item_id" = "issues"."id"
        WHERE
          "work_item_parent_links"."work_item_parent_id" IN (
            SELECT
              "issues"."id"
            FROM
              "issues"
            WHERE
              "issues"."id" IN (533, 535)
          )
      )
    SELECT
      "dates_sources_union"."due_date",
      "dates_sources_union"."due_date_sourcing_milestone_id",
      "dates_sources_union"."due_date_sourcing_work_item_id"
    FROM
      (
        (
          SELECT
            "issues"."parent_id",
            "milestones"."due_date" AS due_date,
            "milestones"."id" AS due_date_sourcing_milestone_id,
            NULL AS due_date_sourcing_work_item_id
          FROM
            "issues"
            INNER JOIN "milestones" ON "milestones"."id" = "issues"."milestone_id"
        )
        UNION
        (
          SELECT
            "issues"."parent_id",
            "dates_source"."due_date" AS due_date,
            NULL AS due_date_sourcing_milestone_id,
            "dates_source"."issue_id" AS due_date_sourcing_work_item_id
          FROM
            "issues"
            INNER JOIN "work_item_dates_sources" "dates_source" ON "dates_source"."issue_id" = "issues"."id"
          WHERE
            "dates_source"."due_date_is_fixed" = TRUE
        )
        UNION
        (
          SELECT
            "issues"."parent_id",
            "issues"."due_date" AS due_date,
            NULL AS due_date_sourcing_milestone_id,
            "issues"."id" AS due_date_sourcing_work_item_id
          FROM
            "issues"
        )
      ) dates_sources_union
    WHERE
      "dates_sources_union"."due_date" IS NOT NULL
      AND (
        dates_sources_union.parent_id = work_item_dates_sources.issue_id
      )
    ORDER BY
      "dates_sources_union"."due_date" DESC
    LIMIT
      1
  )
WHERE
  "work_item_dates_sources"."issue_id" IN (
    SELECT
      "issues"."id"
    FROM
      "issues"
    WHERE
      "issues"."id" IN (533, 535)
      AND "issues"."id" >= 533
  )

MR acceptance checklist

Please evaluate this MR against the MR acceptance checklist. It helps you analyze changes to reduce risks in quality, performance, reliability, security, and maintainability.

Screenshots or screen recordings

After updating the milestone start and due dates (tab1), the both epics (tab2, tab3) that have the rolled up dates and children related to that milestone also have their start and due dates updated.

Screen_Recording_2024-05-13_at_19.22.10

How to set up and validate locally

Numbered steps to set up and validate the change are strongly suggested.