I'd like to ask what, in your view, would be one preferable and robust software design for implementing continuous processing of records in a time-aware manner. A problem overview of the problem to solve follows:
There are records added to the database. Each record is an event with signed up participants. Each event is scheduled to take place on a particular day at a particular hour. Each participant should receive a notification 1 hour before the event's scheduled start time (which is also saved in the database). Events are added to the database all the time. Each participant can take part in many events.
I'm particularly interested in solving this requirement: "Each participant should receive a notification 1 hour before the event's scheduled start time (which is also saved in the database)."
What would be a good software design approach to accomplish this business requirement.
I personally considered the following:
- The database is being polled for the latest event start times at regular intervals (every 5 minutes) - something like (simplified SQL):
SELECT participant FROM events WHERE event.start_time > NOW - 10 minutes AND participant.was_notified = 0
- Each participant is then passed to the queuing system like, e.g. RabbitMQ
- A queue worker process sends notifications to participants selected in step 1 above
- A queue worker process marks the participants as "having received notification" (
participant.was_notified = 1
) - The cycle goes again
The approach I described above would probably work to some extent, but it has lots of drawbacks and does not seem very accurate. Some problems that I can think of:
- The notification are not very time-accurate. 10 minutes interval mean that a participant can receive a notification at 11:55 instead of at 12:00,
- There has to be a guarantee that a notification was delivered and that a record published to the queue has been processed,
- There might be no notifications to deliver within another several hours, but the database will still be polled every 5 minutes.
I'm sure there are more problems with this approach, but I just wanted to list some I could think about off the top of my head.
So coming back to the main question - what would be a preferred, production-suitable software design pattern to solve the presented problem?