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

I don't think we are discussing actual logic here, instead, we are discussing the approaches. I see majorly two options:

1. Calculate and create digest-type emails every time you trigger email notification on normalized data (use #last-updated)

2. De-normalize the data and prepare summarized content before hand and send as per the scheduled time.

If a person has opted all email notifications then you can send an email immediately, else you can set one time in a day and use either Quazrtz or Hangfire as suggested by @tede24 to send these notifications asynchronously. I personally prefer hangfire and using it.

Answer 2

I think here are two solutions that i did in one of my project.

1. Use a trigger and send email via SQL Mail
2. Use a windows service and run it once a day and you can send your all your summary information in just one email.

Answer 3

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 4

Code bellow written in notepad I will check it tomorrow.

Update: I have checked the code at the bottom and it works fine in SQL Server.

Quickest and easiest way to do what required is to add field last_update_date to orders table and then:

This piece of code return you all updated records in some period of time and users subscribed to the order

select o.*, u.* from orders o 
inner join user_subscribtions us on us.order_id = o.order_id
inner join users_intervals ui on ui.user_id = us.user_id and ui.interval = @interval
inner join users u on u.user_id = us.user_id
where o.last_update_date >= DATEADD(HOUR, @interval, GETDATE());

If you have server and can create service

Run 2 jobs that will execute same procedure with different time interval parameter. First job will execute procedure every 24 hours with parameter -24 (hours) second job will execute mentioned procedure once a week with parameter -168 (hours = week) the procedure will get all orders updated in that period of time and insert their data into table of emailing

    create procedure getAllUpdatedOrders @interval datetime as

    begin

    insert into emails_to_send
    select o.*, u.* from orders o 
    inner join user_subscribtions us on us.order_id = o.order_id
    inner join users_intervals ui on ui.user_id = us.user_id and ui.interval = @interval
    inner join users u on u.user_id = us.user_id
    where o.last_update_date >= DATEADD(HOUR, @interval, GETDATE());

    end

    GO

Then you need service that will periodically check table emails_to_send and send all emails from that table.

exec getAllUpdatedOrders -24; - all orders updated in last 24 hours + users subscribed to the orders and daily mail

exec getAllUpdatedOrders -168; - all orders updated within last week + users subscribed to the orders and weekly mail

If you need to show all updates of some order for last 24 hours or a week you will need to store such updates in some separate table for at least one week and to join to above query

Example of getting subscribed users and order updated in some period:

--drop TABLE orders;

--drop TABLE user_subscribtions;

--drop TABLE users_intervals;

CREATE TABLE orders(order_id int, last_update_date datetime);
CREATE TABLE user_subscribtions(id int, order_id int, user_id int);
CREATE TABLE users_intervals(id int, interval int, user_id int);

insert into orders values(1, '2015-02-15');
insert into orders values(2, '2015-02-02');

insert into user_subscribtions values(1, 1, 10);
insert into user_subscribtions values(1, 1, 11);

insert into users_intervals values(1, -24, 10);
insert into users_intervals values(1, -168, 11);

select o.*, us.* from orders o 
inner join user_subscribtions us on us.order_id = o.order_id
inner join users_intervals ui on ui.user_id = us.user_id and ui.interval = -24
where o.last_update_date >= '2015-02-15';

Answer 5

You'll definitely need some form of background processing, tons of solutions out there, however without knowing where and how this site is hosted I'll wait to offer suggestions on that front.

As for the notifications themselves. It sounds like a super simple request, however what kindof time-frame does the user expect from their notifications? Do they use them to go check on orders or is it just data to them? The nice thing about instant notifications is you know whats happening in real time, which the user may enjoy. However in the high volume situation they would have preferred one email with all the mall lumped together, to save spam. However when you lump notifications like that (especially on a daily level) you're disconnecting yourself (up to 24 hours) from the thing you're being notified about. It's very difficult to have both, since the app would somehow need to know "I'm about to get a bunch of orders, so I should wait to send these" or "This is just one order and I wont have another for some time so I can send it now", which I bet is what the user would prefer, but is very difficult to architect (precognition is hard).

I think you might want to ask the user exactly what it is they expect from their notifications. If they want these notifications to alert them of this information, and to potentially trigger something on their end, a daily summary might not be ideal for them (outside of that high volume situation).

I'm going to continue with the pretense that the user would prefer a Daily Summary (since that is the situation where you actually have to change something). I would architect a system where you have a core "notification" table where you store a record per event that will trigger a notification, then a second many to many table with UserIds and NotificationIds, and then a flag stating that the notification was sent or not. Then you simply need an app off to the side somewhere that at set intervals (daily, hourly, etc) checks the many to many table for emails it needs to send out, groups them, and sends them out.

Notifications

• NotificationId
• ... (other columns relevant to the notification)

Users

• UserId
• ...

UserNotifications

• UserNotificationId (some form of primary key)
• UserId (FK)
• NotificationId (FK)
• Sent (bit)

You could also, with the help of some DateTime columns on those tables, and user preferences, setup a system where the application waits say, 15 minutes, to send notifications, and if another notification happens in that 15 minute time span it resets the cool-down, until 15 minutes pass without a new event, and it then groups all the notifications and sends them. This is definitely more complicated, but may be a "best of both worlds" for the user. Where high volume situations will be lumped conveniently, and other notifications will only be delayed by 15 minutes (or whatever the "cool-down" may be)

Notifications are one of those things that are simple on the surface, but get blown out once you dig into what the user wants out of them, hence why there isn't a one size fits all shoe.

Answer 6

I would consider storing all email messages to a seperate table in the case of a digest-type update sysem, and create a trigger -- either a windows service that runs once a day or a scheduled task that runs a console to trigger a function that compiles the messages to one person in the table into one email, and deals with the sending.

You could even go further and split the types of updates into priority; some types result in an immediate message, others get queued for the daily digest. Additionally, you could let users decide wether they want a digest-type update or single messages if you keep track of preferences. Choice is always a good thing to have.

Keeping track of when the last message was send should be easy enough, but you should keep in mind that some things the customer will want to know regardless of when the last message was send. Let them decide with options.

Answer 7

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