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.

Amias

What's with the anonymous hate? If you have a problem with the question, suggest some edits or better yet make some. Be meaningful. – Amias

It would be helpful, if you at least specified which language you are using. Your question is very broad, "But maybe there are more elegant patterns than this? Something already established? Or some tool in azure etc.?" Those are three open ended questions are very subjective in nature. – Somehow

Because I don't care what language it's in. I'm looking for an architectural pattern. – Amias

This is why SO is becoming useless. So many people get tired of the draconian police force so thwy rarely come here anymore (myself included, and I used to be fanatical). Then, with a bounty of 200, the pool of good answerers is so small no one even cares to answer. – Amias

I have given you an architectural pattern, superior to your own, and yet the bounty was never paid. With more information, a more customized solution could have been developed. If my answer did not fit your needs please comment as to why and give me the opportunity to respond. – Somehow

Any solution for this @richard, so did you stick with your current solution, or did you find any better solutions? – Cereus

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.

Somehow

"schedule the jobs to run at the appropriate time" kinda seems to gloss over a pretty important detail. What's the appropriate time? – Execution