How to store user activity that supports undo? I can track user activity on posts, so that users can see for example "2 updates!". The essence of the strategy I’m using leverages 2 tables:



post_activity:
post_id | user_id | time_created

post_users:
post_id | user_id | time_created | time_last_seen



With these 2 tables, I can query all records from post_activity where the post_user.time_last_seen is less than the post_activity.time_created for a count of all new items.

The problem is that post_activity is essentially an anonymous log, with no associated rows or notion of the type of action that caused it. What I can’t solve is for example this scenario:


User A creates new post (1 new activity stored)
User B comments on new post (1 new activity stored)
User C sees "2 new!" but...
User A or B deletes their contribution.


As far as the system is concerned, there are 2 unseen items but the user may see nothing!

Can anyone provide a simple demonstration of how I might have a stronger relationship between activity and user actions here? Particularly one that supports unseen deleted activity?
Create a TRIGGER on the table in which deletes are recorded and update related tables as required.

Relevant postgresql documentation


Info about triggers: http://www.postgresql.org/docs/9.5/static/triggers.html
How to create a trigger: http://www.postgresql.org/docs/9.5/static/sql-createtrigger.html