How to store user activity that supports undo?

Question

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?

Answer 1

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