Reminder emails being sent on the same day

[TomGranot]

This is a question, more than an actual bug report.

Consider the following situation: You went to a party, you met 20 new people, and you sit down with Monica in the morning to put in all these contacts so you'll remember to call them in a month or two to catch up.

A month later, you're flooded with 20 more reminder emails to get in touch with those people, on the same day, in the same hour.

I think that by default if you've added more than one contact in a day (or set more than one reminder) you will get a bunch of reminders exactly X amount of time from that day, which makes for a very difficult catching-up day when your contacts are in the hundreds.

Suggestion - every day:

if(new_contacts_added_today > 0 || stay_in_touch_edited_today > 0)  {
    postpone_reminder(1_day);
}

I.E. whenever a reminder is inserted, check whether this is the first contact or whether we've updated the "stay in touch" feature of any contact. If any of the following happened, don't create the next reminder to 14 days from now, but actually to 15 days. It should not matter to me (I'll still get the reminder, just 15 days instead of 14, which in my case doesn't really matter), but my inbox will not be flooded.

I'd need to dig into the code to see exactly how to add that in, but before I actually open the PR I'd like to know what you guys think.

Does that sound reasonable? It might be just me with this problem.

[TomGranot]

To resolve the issue for now, I took a look at all my contacts in the database:

SELECT first_name, 
       last_name, 
       stay_in_touch_frequency, 
       stay_in_touch_trigger_date 
FROM   contacts; 

And then (manually) chose the appropriate reminder frequency for each of them:

UPDATE contacts 
SET    stay_in_touch_frequency=60 
WHERE  last_name IN ('some contact', 'some other contact');

And used this little script to "spread out" the reminders:

#!/bin/bash

# USAGE: mysql_run_query <QUERY>
mysql_run_query() {
  mysql --host=<DATABASE_HOST> --user=<USER_NAME>--password=<PASSSWORD> --reconnect <DATBASE> -Nse "$1"
}

contact_list=$(mysql_run_query "SELECT id FROM contacts WHERE stay_in_touch_frequency != 'NULL';")
counter=0
for contact in $contact_list; do 
    ((counter++))
    relevant_date=$(date '+%Y-%m-%d %H-%M-%S' -d "+$counter Days")
    echo "Setting $contact reminder to be triggered from $relevant_date"
    mysql_run_query "UPDATE contacts SET stay_in_touch_trigger_date = '$relevant_date' WHERE id = '$contact';"
done

That gave the following result when SELECTing the contacts table (contacts I don't want to stay in touch with are marked by NULL values in stay_in_touch_frequency and stay_in_touch_trigger_date):

+-------------------------+----------------------------+
| stay_in_touch_frequency | stay_in_touch_trigger_date |
+-------------------------+----------------------------+
|                      21 | 2019-10-20 22:45:28        |
|                      60 | 2019-10-21 22:45:29        |
|                     180 | 2019-10-22 22:45:31        |
|                      60 | 2019-10-23 22:45:32        |
|                      60 | 2019-10-24 22:45:33        |
|                      60 | 2019-10-25 22:45:35        |
|                      60 | 2019-10-26 22:45:36        |
|                    NULL | NULL                       |
|                      60 | 2019-10-27 21:45:37        |
|                    NULL | NULL                       |
|                      60 | 2019-10-28 21:45:39        |
|                      21 | 2019-10-29 21:45:40        |
|                      21 | 2019-10-30 21:45:42        |
|                      21 | 2019-10-31 21:45:43        |
|                      60 | 2019-11-01 21:45:44        |
|                      60 | 2019-11-02 21:45:46        |
|                      60 | 2019-11-03 21:45:47        |
|                      60 | 2019-11-04 21:45:48        |
|                      60 | 2019-11-05 21:45:49        |
|                     180 | 2019-11-06 21:45:51        |
|                     180 | 2019-11-07 21:45:52        |
|                    NULL | NULL                       |
|                      60 | 2019-11-08 21:45:54        |
|                      60 | 2019-11-09 21:45:55        |
|                      60 | 2019-11-10 21:45:56        |
|                    NULL | NULL                       |
|                    NULL | NULL                       |
|                    NULL | NULL                       |
|                     365 | 2019-11-11 21:45:58        |
|                    NULL | NULL                       |
|                    NULL | NULL                       |
|                      60 | 2019-11-12 21:45:59        |
|                     180 | 2019-11-13 21:46:00        |
|                      60 | 2019-11-14 21:46:02        |
|                    NULL | NULL                       |
|                    NULL | NULL                       |
|                      60 | 2019-11-15 21:46:03        |
|                      60 | 2019-11-16 21:46:04        |
|                      21 | 2019-11-17 21:46:06        |
|                      60 | 2019-11-18 21:46:07        |
|                     180 | 2019-11-19 21:46:08        |
|                      60 | 2019-11-20 21:46:10        |
|                      60 | 2019-11-21 21:46:11        |
|                      60 | 2019-11-22 21:46:12        |
|                      21 | 2019-11-23 21:46:13        |
|                      60 | 2019-11-24 21:46:17        |
+-------------------------+----------------------------+

Now, instead of having to run that script on a continuous basis, it would be super awesome if there would be some checking going on before you schedule a "Stay In Touch" reminder for a contact. Basically scan all the dates of reminders in the database, then pick one that is not already occupied (or one that is the least occupied, for contact-heavy installations).

[asbiin]

This is really interesting! You have a lot of reminders with the same frequency, on the same period. Then you want to balance these reminders into this period so they are allocated equitable.

We should be able to use different solution/algorithm to re-schedule the reminders,

[TomGranot]

Was there a continuance there? ;) And yeah, just need to change up the logic. I'd be happy to write it if it makes sense, just tell me if the whole logic makes sense to you.

[djaiss]

Instead of doing this complex feature, what about sending only one email reminder with all the contacts that are supposed to be sent on the current day?

[TomGranot]

Because that does not actually solve the problem. If there was an option in that e-mail to delay that person by a day, and get the reminder tomorrow (updating the system of course) that would have been useful. But I don't want to contact 20 people 3 months to the day after I've met them. I'd prefer it be spread out over more than one day.

Does that make sense? I might be a bit pedantic, but this is a real-world use case for me.