User alert system

I’m planing a customizable alert/notification system for an ERP system. I’ve had enough of other features that I got half done before I came up with a better plan so I decided to see if anyone has a better method before I invest too much time.

Because I already have a messaging system in my app most of the ground work is already in place.

  1. I already have the all ground work in place to show an alert icon in the app and the system tray showing the total number of alerts.
  2. Already have a system in place to show toast notifications on windows 8-10.
  3. I already have a central ‘server’ app the handles TCP communication with all connected users.

So my main question is how to trigger the alerts, since I already have the means to send/display the alert once it is triggered.

Here are a few example alerts that may be requested.

  1. Alert me when a sale is <created, modified, deleted> for
  2. Alert me when a sale is <created, modified, deleted> containing
  3. Alert me when status changes to <manufacturing, completed, ready to ship, shipped>

I plan to have an alerts table with fields for user, alert_type, alert_msg, alert_info, record_ID

  1. Alert_type would be the table that the alert is related to (orders, production, shipping…)
  2. I would add a trigger/notification on each of these tables on insert,update,delete
  3. The server would receive the alert and search the alert table for any subscribed alerts that are effected by the change.

Am I on the right track? Will this add an unusually heavy burden to my DB? I’m using PostgreSQL.

I had contemplated writing a separate trigger for each alert but thought that might be overly complicated.

If this is a client side app, (into which a named user logs in), then the app can poll an “alerts table” periodically for relevant alerts.
(Me only, my group, everyone…)
If an alert is found, display it on your dialog or via Windows.
Save a record into ‘Alert shown’ table.
Delete Alerts and Alert Shown after a time period or after all targets have had the alert

Is that too simplistic?

Instead of polling why not use postgresql’s notification system

that should be lighter weight than lots of clients polling

Thanks to Norman.

This is the first time I noticed that PG has this kind of features.

[quote=349658:@Jeff Tullin]If this is a client side app, (into which a named user logs in), then the app can poll an “alerts table” periodically for relevant alerts.
(Me only, my group, everyone…)[/quote]

The server and all users are on the same local network. I was thinking actually having two alerts tables. One Subscribed_alerts would contain information about what triggers the alert. Another table would list triggered alerts. This would include information about the alert and the targeted user. As I already stated I have a means of retrieving and displaying the alerts once they are triggered. When the app is started the triggered_alerts table is read to find a list alerts that have not been dismissed. While the app is running the server needs to take care of figuring out when an alert is triggered. When it detects a condition that would trigger an alert it should write to the triggered_alerts table and send a TCP message to the user to notify them to read the table and get the information about the alert to display.

That is what I’m planning to do but was wondering which of the following methods would be best.

Method 1:

  1. Have the server receive a notification everytime a table is changed (orders, production…)
  2. After the server receives the notification read the Subscribed_alerts table to see if the change should trigger an alert.
  3. If so take steps to alert the user.

Method 2:

  1. When subscribing to an alert create a new PostgreSQL trigger that causes a notification to occur whenever changes are made to the database that meet the alert’s criteria.
  2. The server receives the notification and alerts the appropriate user via TCP.

Basically how can I detect a change to the database that would cause a condition to which the user requested an alert, with the least amount of stress to the database.

Obviously if the DB needs to perform 50 checks every time an update is done it won’t be as efficient.

Perhaps a hybrid would be better.

Using method 1 but instead of reading the subscribed_alerts table all the time that info could be loaded into memory by the server. When subscriptions are added or removed by users they would notify the server via TCP to update it’s in memory subscription list. This might be more efficient especially if there are 50 or 100 subscriptions.

I’m gradually switching my code to remove all polling. Also instead of each user listening for notifications I have the server subscribe and then pass the info along to the users via TCP. This is a magnitude faster than polling, say once a second.

I was thinking maybe 15 minutes… :wink:

Obviously it depends upon how close to real-time you need the messages to appear.
‘Dont forget to take update your Next of Kin’ might be good enough daily.

‘Look out , the building is on fire and I seem to have no other way to inform you’, kinda needs to be immediate.

Whew! I like for things to be instant.

One part of my app called bundling is a process where multiple people from different departments are gathering materials for delivery. All users can be editing the same list simultaneously and all changes are reflected live at all stations via TCP.

Doesnt look like an alert system, so much as live stock updates. But neat.

This is effectively what PostgreSQL’s notifications do for you without all the grunt work on your part

[quote]Basically how can I detect a change to the database that would cause a condition to which the user requested an alert, with the least amount of stress to the database.

Well, doing these kind of checks and react accordingly is what those “After”-Triggers are for (well, among other things). Just see if your criteria is met and notify the listeners. Pretty straight forward.

With this approach I was a little worried about ‘left over triggers’ if something goes haywire. I guess this if probably the correct approach.

It’s not. It’s another part of my program that shows the results of TCP updates. I’m writing info to the DB and then sending a TCP signal to each user to tell them to refresh.

Good point. In my case the users are already all connect to the server app and the ground work is in place so sending
TCP signals is very simple.

Thanks to for everyone for your input.