Re: Building a notification system.

On Mon, Jul 16, 2018 at 8:02 AM, Christopher Browne <cbbrowne(at)gmail(dot)com>
wrote:

> 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.
>

Thanks that helps a lot.