Adding OKR checkin reminder email notifications
Compare changes
Files
5- Darby Frey authored
+ 1
− 1
@@ -25258,7 +25258,7 @@ CREATE TABLE work_item_progresses (
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.
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
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)
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
Run the cron job. In the rails console, run:
date = Date.today.next_week(:tuesday)
Okrs::CheckinReminderEmailsCronWorker.new(date: date).perform
Load Letter Opener in the browser and you should see the checkin notification email http://127.0.0.1:3000/rails/letter_opener/
This checklist encourages us to confirm any changes have been analyzed to reduce risks in quality, performance, reliability, security, and maintainability.