Suggestions for queuing up updates to be emailed out in an asp.net-mvc application?

Question

I have an asp.net-mvc application (SQL Server backend) and its a basic CRUD web app that tracks purchase orders. One of the features is the ability to subscribe to a order so if anyone makes any changes, you get an email notification. I am triggering the email notification after the form post after and after the update has been committed to the database.

The issue is that one day there was a ton of updates on a single order and a person got 24 emails updates in one day. The user requested that he just gets 1 email a day with the summary of all changes. This seems like a reasonable request and I have seen this in other cases (get single daily or weekly bulk notifications) but i am trying to figure out the best way to architect this. I know i need to persist a user setting on how they want to receive updates but after that I could:

• Have a temporary update table or
• Run a query over all orders once a day and see if there are any changes on orders for any users who has the "once a day" check.
• other ?

Since i have seen this in other places i thought there might be a recommended pattern or suggestions to support this.

Answer 1

There are three things that need to be solved here:

• storing a bunch of possible messages
• aggregating the messages before sending
• send the messages

Storing the messages is simple, you create a DB table with the individual messages with the recipient's ID.

Then, aggregating the messages totally depend on you, you might want to allow users to specify that they want individual messages, daily or weekly digests. When you've combined the messages to a single e-mail, you can send it, and remove the messages used in the aggregation. (You might just want to soft delete them, or move them to a log table).

As for the technology used for the aggregation and sending, you'd need an asynchronous job to process your message queue and send the e-mails.

If you don't want to use any additional library, you could set up an action method to do the job, and just call the action method regularly from a scheduled Windows job or any site-alive pinger service. You might want to secure this action method, or put some logic in place to process the queue only a couple of times a day.

If third party libraries are not a problem, then Scott Hanselman collected a list of possible options a while back, see here. As others mentioned, Hangfire.io is a popular option.

Answer 2

I would use dates to do the trick:

Any order has a "last updated" datetime. Every user has a "last run" datetime and a frequency. Then you can run a process once every ## minutes, take all users that need to be notified according to user preference and find all orders with last updated date > user last run attribute.

The key is that you will need some background job processing component in your app to schedule the work and to monitor running. I use hangfire.io for the job, having excellent results