Adding OKR checkin reminder email notifications

What does this MR do and why?

This MR is part of https://gitlab.com/gitlab-org/incubation-engineering/okr/meta/-/issues/19+, and enables the reminder notifications for which the setting was added in Adding checkin reminder setting quick action (!130371 - merged)

This MR adds a new Sidekiq cron job that runs once per day. The job uses Okrs::CheckinReminderKeyResultFinder to return an array of all opened work items of type key_result with assignees that have been configured with the notification cadence (weekly, twice-monthly, or monthly).

It then iterates over each item and sends individual emails to each assignee.

Screenshots or screen recordings

Email Screenshot

Database Review

The daily cron job introduces the three queries below via the finder class:

Finder Query

SELECT
  "issues".*
FROM
  "issues"
  INNER JOIN "issue_assignees" ON "issue_assignees"."issue_id" = "issues"."id"
  INNER JOIN "work_item_parent_links" ON "work_item_parent_links"."work_item_id" = "issues"."id"
  INNER JOIN "issues" "work_item_parents_issues" ON "work_item_parents_issues"."id" = "work_item_parent_links"."work_item_parent_id"
  LEFT OUTER JOIN "work_item_progresses" ON "work_item_progresses"."issue_id" = "issues"."id"
WHERE
  ("issues"."state_id" IN (1))
  AND (
    "issues"."work_item_type_id" = (
      SELECT
        "work_item_types"."id"
      FROM
        "work_item_types"
      WHERE
        "work_item_types"."base_type" = 6
      LIMIT
        1
    )
  )
  AND "work_item_parent_links"."work_item_parent_id" IN (
    WITH RECURSIVE "base_and_descendants" AS (
      (
        SELECT
          "issues"."id"
        FROM
          "issues"
          INNER JOIN "work_item_progresses" ON "work_item_progresses"."issue_id" = "issues"."id"
        WHERE
          "work_item_progresses"."reminder_frequency" = 1
          AND ("issues"."state_id" IN (1))
          AND (
            "issues"."work_item_type_id" = (
              SELECT
                "work_item_types"."id"
              FROM
                "work_item_types"
              WHERE
                "work_item_types"."base_type" = 5
              LIMIT
                1
            )
          )
          AND (
            NOT EXISTS (
              SELECT
              FROM
                work_item_parent_links
              WHERE
                work_item_id = issues.id
            )
          )
      )
      UNION
      (
        SELECT
          "issues"."id"
        FROM
          "issues",
          "base_and_descendants",
          "work_item_parent_links"
        WHERE
          "work_item_parent_links"."work_item_id" = "issues"."id"
          AND "work_item_parent_links"."work_item_parent_id" = "base_and_descendants"."id"
      )
    )
    SELECT
      "issues"."id"
    FROM
      "base_and_descendants" AS "issues"
  )
  AND (
    work_item_progresses.last_reminder_sent_at IS NULL
    OR work_item_progresses.last_reminder_sent_at <= '2023-09-13'
  )
GROUP BY
  "issues"."id"
ORDER BY
  "issues"."id" ASC
LIMIT
  1000

Query plan:

cold cache: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22655/commands/73037

warm cache: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22655/commands/73038

Update Query

The following update query is introduced in Okrs::CheckinReminderEmailsCronWorker#deliver_reminder.

UPDATE
  "work_item_progresses"
SET
  "updated_at" = '2023-09-13 18:22:46.209470',
  "last_reminder_sent_at" = '2023-09-13 18:22:46.208466'
WHERE
  "work_item_progresses"."issue_id" = 134631298

Query Plan: https://console.postgres.ai/gitlab/gitlab-production-tunnel-pg12/sessions/22174/commands/71674

How to set up and validate locally

1. Enable the okrs_mvc and okr_checkin_reminders feature flag locally. In the rails console, run:

   Feature.enable(:okrs_mvc)
   Feature.enable(:okr_checkin_reminders)

2. Create some OKR seed data. In the rails console run:

   objective = WorkItem.create(
     title: "Sample Objective",
     author: User.first,
     project: Project.first,
     work_item_type_id: ::WorkItems::Type.default_by_type(:objective).id
   )
   
   objective.create_progress(reminder_frequency: 'weekly')
   
   key_result = WorkItem.create(
     title: "Sample Key Result",
     author: User.first,
     project: Project.first,
     work_item_type_id: ::WorkItems::Type.default_by_type(:key_result).id
   )
   
   WorkItems::ParentLink.create(
     work_item: key_result,
     work_item_parent: objective
   )
   
   key_result.assignees<<User.first

3. Run the cron job. In the rails console, run:

   date = Date.today.next_week(:tuesday)
   Okrs::CheckinReminderEmailsCronWorker.new(date: date).perform

4. Load Letter Opener in the browser and you should see the checkin notification email http://127.0.0.1:3000/rails/letter_opener/

MR acceptance checklist

This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.

• I have evaluated the MR acceptance checklist for this MR.