web applications – How to create a news feed (or news email) from a database with questions, answers and comments?

I have a web app where users can ask questions and get answers, ideas and comments from other users. Backend is Django Rest Framework with a Postgres Database, Frontend is React.

Now I want to add some ‘news-feed’ functionality, meaning e.g.:

  • users can ‘subscribe’ to questions
  • users get notifications/emails (daily or weekly) about updates in subscribed questions (E.g. “three new answers to your question xyz”!)
  • users see a ‘new’ badge attached to content that has been created since they last visited that question

My naive approach would probably be something like:

  • add an ‘activity’ log to all ‘question’ objects in my database, with timestamps for each relevant action like: added answer with id, added comment with id, etc.
  • add a ‘subscriptions’ table linking users and questions, which also includes timestamps ‘lastViewed’ for each question
  • And create the news feed by comparing timestamps

But I’m sure there are lots of caveats here, regarding both logic and performance. But I’m sure this has been done before and there must be some ‘best practices’ to do it.

My best research result was this article: Designing a social media newsfeed on AWS which is focussing on a twitter/facebook style news-feed that needs to be delivered very quickly when users are visiting the site. And so it focusses on pre-generating and caching the news feed.

I’m looking for more insight in how to best approach the timestamp comparisons and database design, and on how to store/model the ‘news-feed’ itself. There’s probably articles out there about it, but I could not find them yet.

Thank you!!

Disclaimer: this is my second question on “Software Engineering”, please don’t be too harsh downvoting it.