[STORY]: Grant Activity digest email trigger

[greg-adams]

Subtask of [STORY]: Create new email notifications for 'Follow + Note' #2960

Blocked by

• [STORY]: Grant Activity digest notification preference #3570

Blocks

• [STORY]: Grant Activity digest email #3572

User Story

As an organization, we would like to foster more collaboration between our users to allow for the sharing of knowledge and resources to empower them to more efficiently and effectively apply for the grants discovered on our platform.

As a grantseeker, I want to be able to easily share grants I’ve discovered with my team.

As a grantseeker, I want to be able to easily find collaborators who may be able to help me when preparing and applying for grants.

As a grantseeker, I want to be able to quickly see what grants my team members are interested in.

As an admin, I want to be able to easily surface and resolve issues and blockers my team is running into.

Why is this issue important?

In order to help build the collaborative spirit we're going for, we need to have a prompt to pull people back to the platform when people they may be interested in supporting or receiving support from mark interest in the same grant. This will help bring users back to the platform in a timely manner to collaborate on previously marked grants.

Definition of Done

1. A daily digest email will be sent each morning to a user who has their Grant Activity notification set to "on" AND at the time of the check, if a new user in the organization follows a grant and/or a new note is added to a grant, and/or an existing note is edited for any grants that the user follows (i.e. since the previous day's check for activity).
   • if the user does not follow any grants or has no activity in the past 24 hours (since the last check) for any grant they follow, they will not receive an email
   • the user receiving the email must be following the grant at the time of the check (i.e. if I followed a grant at 10am today, but unfollowed it at 2pm today, then tomorrow morning I would not receive a grant activity notification email)
   • The person who followed the grant should not receive the email if the only activity is their own follow action and/or their own note creation/edit

Implementation Details

• Create a new instance of the existing terraform/modules/scheduled_ecs_task module. This new instance should be implemented within the terraform/modules/gost_api module to register the cron task to run daily at 8am, America/New_York timezone (e.g. 0 8 * * *).
• Create a new script handler called sendGrantActivityDigestEmail in src/scripts (see existing sendGrantDigestEmail.js) to handle this event
• Handler script should add a new query to the lib/grantCollaboration package interface to perform query(s) to collect grant activity data for each recipient
  • The query time boundaries (:digestPeriodStart and :digestPeriodEnd in the examples below) should be aligned with the cron schedule (rather than something like now()), so that even if the task execution is delayed a bit, we're still reliably querying for a period from >= 8am yesterday until < 8am today.
• Handler should use asyncBatch library to send emails in batches of 2.
• Implementation should use luxon library where needed with dates.

The email content/design will be covered by #3572, however a placeholder for email body can be used in the interim.

Querying for grant activity

The following query returns both new follows and note revision content for grants since a given :digestPeriodStart timestamp and up to a given :digestPeriodEnd timestamp. In this example, the rows are ordered by grant ID (to keep rows pertaining to the same grant together), then new follower activity (oldest first), then new note revision content (oldest first).

SELECT
    g.grant_id AS grant_id,
    g.title AS grant_title,
    u.id AS user_id,
    u.name AS user_name,
    u.email AS user_email,
    a.name AS agency_name,
    activity.activity_at,
    activity.activity_type,
    activity.text_content AS note_text
FROM (
    SELECT
        gf.id,
        gf.grant_id,
        gf.user_id,
        gf.created_at AS activity_at,
        'follow' AS activity_type,
        null AS text_content
    FROM grant_followers gf
    UNION ALL
    SELECT
        rev.id,
        gn.grant_id,
        gn.user_id,
        rev.created_at AS activity_at,
        'note' AS activity_type,
        rev.text AS text_content
    FROM grant_notes gn
    LEFT JOIN LATERAL (
        SELECT
            r.id,
            r.grant_note_id,
            r.created_at,
            r.text
        FROM grant_notes_revisions r
        WHERE r.grant_note_id = gn.id
        ORDER BY r.created_at DESC
        LIMIT 1
    ) AS rev ON rev.grant_note_id = gn.id
) activity
JOIN users u ON u.id = activity.user_id
JOIN agencies a ON a.id = u.agency_id
JOIN grants g on g.grant_id = activity.grant_id
WHERE 
    activity.activity_at > :digestPeriodStart
    AND activity.activity_at <= :digestPeriodEnd
ORDER BY
    g.grant_id DESC,
    -- Followers first, then notes (remove for grant activity in chronological order, regardless of type):
    array_position(array['follow', 'note'], activity.activity_type) ASC,
    activity.activity_at ASC;

Example result set:

grant_id	grant_title	user_id	user_name	user_email	agency_name	activity_at	activity_type	note_text
987654	This grant has a lot of activity	5	Jennifer Coleman	[email protected]	Climate Office	2024-09-19 09:24:07.531172+00	follow	null
987654	This grant has a lot of activity	2	Ray Welch	[email protected]	Dept of Education	2024-09-19 15:20:37.545297+00	follow	null
987654	This grant has a lot of activity	5	Jennifer Coleman	[email protected]	Climate Office	2024-09-19 09:25:53.746174+00	note	First note of the day.
987654	This grant has a lot of activity	4	Ken Ingram	[email protected]	Public Works	2024-09-19 10:01:26.897312+00	note	This is the second note.
987654	This grant has a lot of activity	3	Traci Tran	[email protected]	Climate Office	2024-09-19 14:21:53.306255+00	note	Last note for today!
246813	A grant with a single new follower	4	Ken Ingram	[email protected]	Public Works	2024-09-19 10:45:29.726974+00	follow	null
123456	Two new followers and one new note	3	Traci Tran	[email protected]	Climate Office	2024-09-19 15:50:42.655014+00	follow	null
123456	Two new followers and one new note	5	Jennifer Coleman	[email protected]	Climate Office	2024-09-19 16:22:08.934724+00	follow	null
123456	Two new followers and one new note	5	Jennifer Coleman	[email protected]	Climate Office	2024-09-19 16:22:09.468254+00	note	This is the second grant I've followed today.

This query retrieves all new grant collaboration activity (new follows and new note revisions), across all organizations (tenants). However, it only represents user data about users who performed some activity (i.e. became a new follower and/or created or revised a note on a grant); it does not provide all users who are following a grant. In other words, the query in the previous section provides results that can be used to build the content of a digest email, but not to determine all users who should receive that digest. Although this query may be useful for analytical purposes, it must be adjusted in order to be made suitable for our email digest use-case.

Querying for digest recipients

In order to determine every recipient for a 24-hour digest, we need to locate every grant with at least one new follower and/or at least one new or revised note. Although the query in the previous section satisfies that need, we also need to determine which users are following those grants – all followers of a grant (not just new followers) will receive a digest email as long as some of the follower and/or activity pertains to users within the same organization.

This can be achieved by modifying the SELECT statement and adding JOIN expressions that establishes and then filters on a new relationship, grant_followers AS recipient_followers:

SELECT
    DISTINCT recipient_followers.user_id AS recipient_user_id
FROM (
    SELECT
        gf.id,
        gf.grant_id,
        gf.user_id,
        gf.created_at AS activity_at,
        'follow' AS activity_type,
        null AS text_content
    FROM grant_followers gf
    UNION ALL
    SELECT
        rev.id,
        gn.grant_id,
        gn.user_id,
        rev.created_at AS activity_at,
        'note' AS activity_type,
        rev.text AS text_content
    FROM grant_notes gn
    LEFT JOIN LATERAL (
        SELECT
            r.id,
            r.grant_note_id,
            r.created_at,
            r.text
        FROM grant_notes_revisions r
        WHERE r.grant_note_id = gn.id
        ORDER BY r.created_at DESC
        LIMIT 1
    ) AS rev ON rev.grant_note_id = gn.id
) activity
-- Limit activity to grants with (current) followers:
JOIN grant_followers recipient_followers ON recipient_followers.grant_id = activity.grant_id
-- Incorporate `users` table data for users responsible for the activity
JOIN users activity_users ON activity_users.id = activity.user_id
-- Incorporate `users` table data for recipient followers
JOIN users recipient_users ON recipient_users.id = recipient_followers.user_id
-- (No need to JOIN on `agencies` or `grants` tables because their data is only used for email bodies)
WHERE 
    activity.activity_at > :digestPeriodStart
    AND activity.activity_at <= :digestPeriodEnd
    -- Only consider actions taken by users in the same organization as the recipient:
    AND recipient_users.tenant_id = activity_users.tenant_id
    -- Exclude rows where the recipient user is the one taking the action, 
    --   to ensure that users only receive a digest if OTHER users took action:
    AND recipient_followers.user_id != activity.user_id;

The results of this query are a single column of unique recipient user IDs, which can be iterated over in a fan-out scenario so that individual digest emails can be constructed and sent in parallel.

Querying for a single recipient's email data

In this scenario, a single recipient user ID is a known value, having been determined as described in "Querying for digest recipients". Now we need to build the contents of the digest email that will be sent to this single recipient.

Once again, our query is primarily concerned with grant activity that occurred within a start/end timestamp threshold. This time however, we need to select all the values that will be used in the body of the email, as well as filter results in the following ways:

• Only include information about an activity where the grant associated with the activity is followed by the recipient user.
• Only include information about an activity where the user associated with the activity belongs to the same organization (i.e. same users.tenant_id value) as the recipient user.

SELECT
    g.grant_id AS grant_id,
    g.title AS grant_title,
    activity_users.id AS user_id,
    activity_users.name AS user_name,
    activity_users.email AS user_email,
    activity_users_agencies.name AS agency_name,
    activity.activity_at,
    activity.activity_type,
    activity.text_content AS note_text
FROM (
    SELECT
        gf.id,
        gf.grant_id,
        gf.user_id,
        gf.created_at AS activity_at,
        'follow' AS activity_type,
        null AS text_content
    FROM grant_followers gf
    UNION ALL
    SELECT
        rev.id,
        gn.grant_id,
        gn.user_id,
        rev.created_at AS activity_at,
        'note' AS activity_type,
        rev.text AS text_content
    FROM grant_notes gn
    LEFT JOIN LATERAL (
        SELECT
            r.id,
            r.grant_note_id,
            r.created_at,
            r.text
        FROM grant_notes_revisions r
        WHERE r.grant_note_id = gn.id
        ORDER BY r.created_at DESC
        LIMIT 1
    ) AS rev ON rev.grant_note_id = gn.id
) activity
-- Limit activity to grants for which the recipient user is a follower (note the additional condition):
JOIN grant_followers recipient_followers ON recipient_followers.grant_id = activity.grant_id
    -- (This could alternatively go in the WHERE clause:)
    AND recipient_followers.user_id = :recipientUserId
-- Incorporate `users` table data for users responsible for the activity:
JOIN users activity_users ON activity_users.id = activity.user_id
-- Incorporate `users` table data for the recipient follower:
JOIN users recipient_users ON recipient_users.id = recipient_followers.user_id
-- Additional JOINs for data selected for use in the email's content:
JOIN grants g on g.grant_id = activity.grant_id
JOIN agencies activity_users_agencies ON activity_users_agencies.id = activity_users.agency_id
WHERE 
    activity.activity_at > :digestPeriodStart
    AND activity.activity_at <= :digestPeriodEnd
    -- Limit to activity where the user performing the activity belongs to the same organization:
    AND activity_users.tenant_id = recipient_users.tenant_id
ORDER BY
    -- Somewhat arbitrary, but ensures rows with the same `grant_id` occur consecutively:
    g.grant_id DESC,
    -- Followers first, then notes (remove for grant activity in chronological order, regardless of type):
    array_position(array['follow', 'note'], activity.activity_type) ASC,
    -- Activity of the same type is ordered oldest to most-recent:
    activity.activity_at ASC;

The ORDER BY criteria allows for the result set to be iterated over in a manner that can be used to progressively construct the contents of a digest email. Since rows with the same grant ID appear consecutively (and within those, rows representing the same activity type appear consecutively, ordered by oldest-first), the body of an iterating loop can watch for changing grant_id and activity_type values to determine when to construct a new digest section for a grant and a new sub-section pertaining to activity type within that grant.

[TylerHendrickson]

@greg-adams re

• Handler script should add a new query to the db module (src/db) to perform query(s) to collect grant activity data for each recipient

I'd prefer this functionality to be part of the lib/grantsCollaboration package interface – I'd prefer to avoid adding more clutter to src/db as-is, given both its size and black-hole-ish-ness. Let me know if you think otherwise.

• Create a new Terraform module (see existing grant_digest.tf) to register the cron task to run daily in the morning (E.g. cron(0 0 * * *))

A bit of pedantia for the sake of clarity / avoiding unnecessary work: this doesn't require creating an entire new Terraform module (e.g. a new directory in terraform/modules/) – we just need another instance (module some-name {} block) of the existing terraform/modules/scheduled_ecs_task module. This new instance should be implemented within the terraform/modules/gost_api module. I figured that's what you meant, but wanted to call it out just in case.

Also, I'd suggest configuring the scheduled task cron for 8am, America/New_York timezone, although it's probably worth getting @ClaireValdivia's input on when we should begin sending out emails.

Finally, we should make sure that the query time boundaries (:digestPeriodStart and :digestPeriodEnd in the examples above) are aligned with the cron schedule (rather than something like now()), so that even if the task execution is delayed a bit, we're still reliably querying for a period from >= 8am yesterday until < 8am today.

[greg-adams]

agreed - I've updated language to include @TylerHendrickson 's comments