What is the easiest and most flexible to scale method for handling recurring tasks that don't have an end date and will dynamically change?

Question

Problem:
I'm writing a service in Laravel 6.2 that will send out a push notification to different users at specified times down to the minute. Users select how many times a month, week, or day and at which times they want to be notified. I originally tried to store the index day (0-6) of the week and the UTC time stamp (16:00:00) and then run an Artisan command every minute to check which schedules were for that minute.

As far as I could tell there were two main routes I could take:

• Calculated: Store the frequency interval and date/time each reminder should occur.
  • Pro: Keeps database lighter.
  • Con: Heavy calculation every minute and may been an issue when the database grows.
• Long Term Storage: Capture the frequency interval and then store the actual date/times this needed to happen.
  • Pro: Keeps calculations heavy.
  • Con: Fills out the database tables quickly and eventually really slows queries.

Ideas and Explorations:

1. Laravel Tasks - https://laravel.com/docs/6.x/scheduling
   • Con: Not sure if I can make schedules dynamic enough without declaring each schedule in the Kernel.php file.
2. RRule Packages - https://github.com/rlanvin/php-rrule, https://github.com/simshaun/recurr, https://packagist.org/packages/jpmurray/laravel-rrule
   • Store the RRule in the DB and then dynamically schedule those tasks.
   • Con: Not sure if this is efficient or best practice.
3. Store the dynamic frequency of a recurring task in the DB, then figuring out a way to dynamically schedule lots of Laravel task scheduling and then if the server ever restarts to re-load those tasks as dynamic cron tasks.
   • Con: Not sure if this is possible or not.
4. Laravel Snooze Package - https://github.com/thomasjohnkane/snooze
   • Con: Scheduling in the future looks to only occur once. I wouldn't want to chain another call upon the execution of the first. That seems inconsistent.

Answer 1

Step1: User interaction

When the user saves the schedule try to save it in CRON tab format. Something like- '0 0 1 * *'.

You will find a lot of PHP libraries that can encode/decode this string to give you timestamp. This would be the most flexible way of scheduling. In future, if you decide to go with some available libraries, it would be easy to convert this schedule into format those libraries expect.

Save that schedule in database table, say - notification_schedules and you are done in terms of user interaction. No calculations in this step.

Step 2: Script for calculating schedule that matched the current time

Then run a script every minute that would scan all the records from notification_schedules and find records that are eligible for sending notifications at that moment. If a record is eligible, create a record in another table, say - notification_delivery. If you have messaging architecture like rabbitmq, creating a message would be better.

Step 3: Script that generates the delivery pipeline

Add 2nd script that just reads notification_delivery and sends notifications. Once notification is sent, update the record with some flag or delete the record. This notification_delivery will only have active records if they are due for delivery.

Here you are splitting the work into 3 async parts. Making it flexible and loosely coupled.