Re: Building a notification system.

On Sun, Jul 15, 2018, 5:30 AM Anto Aravinth, <anto(dot)aravinth(dot)cse(at)gmail(dot)com>
wrote:

> Hello Everyone,
>
>
> I'm playing around with postgresql with SO datasets. In the process, I
> have dumped 60M questions data onto the postgresql. I'm trying to build a
> notification system on top of this, so that, when a user edits a question,
> I need to show a notification to the user when he/she logs in next time. So
> literally, trying to create the clone of SO (with very minimal feature)
>
> I'm not sure, how to get started with this. Read about NOTIFY:
> https://www.postgresql.org/docs/current/static/sql-notify.html
>
>
> Not sure that fits my use case, thanks for your help in this.
>

I do not think that the NOTIFY command implemented in postgreSQL is
terribly likely to be useful for your application.

That command is useful for distribution of notifications to applications
that are continuously connected to the database, which is not likely true
for web app connections, particularly in view of your comment about
notifying users "when they log in next time."

Instead, you need a table that captures a log of undelivered notifications
of changes to questions. It should capture useful attributes such as..
- Who made the change
- Who is to be notified
- The time of the change
- Perhaps the nature of the change, which could be pretty open ended
- A reference to the question, e.g. its ID
- Some lifecycle attribute such as "viewed-on" or "acknowledged-on"

When a user logs in, it should be easy to query that table, providing the
list of unexamined updates.

Perhaps entries may be removed as soon as they are viewed, or you may need
a more sophisticated lifecycle so they are only removed after some express
indication that the change has been fully acknowledged.