How to design a calendar reminder/alert system

Question

I have a calendar system in my web app. We need to implement reminders.

Are there any reminder/alert patterns or system designs, or best practices? If so or if not, what might be some ways of achieving this?

Design Considerations

1. Need to be able to cancel/prevent reminders if the calendar event is deleted or changed, or the user turns off the reminder for that event. So we can't just fire and forget them in a queue or something.
2. The reminders can be X amount of time before the event, X being set in the calendar event settings
3. Reminders don't need to be super accurate (to the second or even minute). +- 5 minutes is fine.
4. Don't want to pre-calculate reminders because then the maintenance becomes a nightmare as calendar events change, especially where recurring events are concerned.

So far my design is something like this:

1. Have a scheduled job run every 10 minutes.
2. The job grabs all possible relevant events and calculates potential occurrences for the next 10 minute interval (filtering out events that don't have a reminder set).
3. Job calls an API endpoint in my server side that kicks of a front end notification and an email reminder for all relevant parties.

But maybe there are more elegant patterns than this? Something already established? Or some tool in azure etc.?

Our stack is .net and azure.

Answer 1

Create a table for the next day. Add a trigger to add the reminder upon insertion into the main table and a trigger(if it is the same day, to verify the job is scheduled for that 10 minute increment and add it to the daily table, same for the removal of the reminder.)

Batch a job each day at off peak times to place reminders for the next day and schedule the jobs to run at the appropriate times. If they are removed before the batch runs they are never added; if after, the trigger will remove them.

Use your existing script, as scheduled by the batch scripts and triggers, to send notifications based on the much smaller daily table.

This process will minimize the execution time overall, while using minimally more database space.

Edit: While the suggested process is an improvement on the original algorithm, whether or not this will translate into financial savings on the Azure platform cannot be determined without usage data.

With light usage: This will improve performance by eliminating full table queries and eliminating the jobs every 10 minutes when not required, at the expense of a few MB of additional storage.

At large to hyper-scale solutions: The batching and triggers will improve efficiency by eliminating full-table scans during the 10 minute queries.

In the middle, it is 50/50 to your proposed solution.

Where those exact boundaries lie, I cannot say without more data.