lookit-bot email delay scoping

[ianchandlercampbell]

Summary

Writing this as a placeholder scoping issue for the lookit api-- this could be a SendGrid issue or multiple things.

We've noticed hours-long delays on lookit-bot email notifications on Lab member requests as well as experiment runner deploying over a span of weeks. The severity and frequency of this problem is currently unknown, but I have been able to confirm that on both the lab member request and experiment runner building side, these appear to be working on the site itself as normal.

I have a sneaking suspicion this could lead to us going through the lookit-bot notification system and making some changes in the long run. Melissa requested that we look into cataloging the various email bots (e.g. lookit-bot) and then figuring out where they send emails in general for the CHS admin side (ideally, we want everything routed to [email protected] at least primarily).

For the week of 06-03, let's do some scoping on just the email notification delays and see scope out how this system works and where we might be seeing some issues.

Issue

It seems like the data on production has gotten large enough that the announcement emails are taking a very long time to complete. This has been replicated locally with the production data set. This command resolve quickly on the staging data set.

To resolve this issue, I feel that the SQL query used to generate the announcement emails should be refactored and optimized.

Implementation

Before we can get into implementation details, we'll probably need a good list of rules for the announcement emails job.

1. Review and understand current SQL query, and underlying rules/conditions.
2. Review the current understood functionality of this query/job.
3. For code readability, use combination of Django's ORM and comments.
4. Best attempt at a single call to DB where only the needed data is returned.

When we have time, it would be worth doing the following:

• Detailed understanding of what we need out of the announcement system. We may be able to simplify/remove some of the existing constraints.
• Consider approaching this problem from other directions, such as selecting children/studies in a different order, etc.
• Investigate caching options (e.g. pre-processing eligibility, scheduling future emails)
• Consider sending an email digest (one email, but could add in all eligible studies, or some number)