Software Engineering

Designing a social media notification system, would a single table with JSON column be the correct approach for data?

I am trying to create a notification system similar to Instagram, for the lack of better comparasion. The Goal: Getting user notifications in a unify way, for easy unparsing and displaying to user. The Problems: Mixed Data - Different body types needing unification - "New Follower" notification requires user details. "FooBatz made a new post" requires different parameters Changing Data - depending on when the user views it, New Follower notification will need to display the current profile picture, not the one that was when he followed. Storage - Many duplicated notifications, "FooBatz" made a new post will trigger notification for all of his followers, he's very popular. Solutions: For mixed data - Using SQL json to store the data, keeping it all in one table. For Changing data - Passing the user only the raw fields and having him send another request for full info, such as profile picture. For storage, separate tables for data and status, so the status row links to the data, while only storing the read status. For the code handling counting on typescript smart typing to recognize: interface NewFollowerNotification { type: 'newFollower' data: {userId: string, profilePicture: string} } interface NewFollowerPostNotification { type: 'NewFollowerPost' data: {userId: string, postId: string} } type Notification = NewFollowerNotification | NewFollowerPostNotification const test: Notification = { type: 'NewFollowerPost' data: { userId: 'foo', profilePicture: 'batz` //

Jun 18, 2025 - 18:40

I am trying to create a notification system similar to Instagram, for the lack of better comparasion.

The Goal:

Getting user notifications in a unify way, for easy unparsing and displaying to user.

The Problems:

Mixed Data - Different body types needing unification - "New Follower" notification requires user details. "FooBatz made a new post" requires different parameters
Changing Data - depending on when the user views it, New Follower notification will need to display the current profile picture, not the one that was when he followed.
Storage - Many duplicated notifications, "FooBatz" made a new post will trigger notification for all of his followers, he's very popular.

Solutions:

For mixed data - Using SQL json to store the data, keeping it all in one table.
For Changing data - Passing the user only the raw fields and having him send another request for full info, such as profile picture.
For storage, separate tables for data and status, so the status row links to the data, while only storing the read status.
For the code handling counting on typescript smart typing to recognize:


interface NewFollowerNotification {
  type: 'newFollower'
  data: {userId: string, profilePicture: string}
}
interface NewFollowerPostNotification {
  type: 'NewFollowerPost'
  data: {userId: string, postId: string}
}

type Notification = NewFollowerNotification | NewFollowerPostNotification

const test: Notification = {
  type: 'NewFollowerPost'
  data: {
    userId: 'foo',
    profilePicture: 'batz` // <- will show error
  }
}

Concerns

Swarming requests as user checks his notifications, as he will fire many requests for extra data - getting user informations, pictures and more that will greatly slow the website loading and hurt the user experience.
With SQL JSON it can get chaotic and will be hard to track and normalize.
With SQL JSON I cannot effectively do joins to return all the relevant data in a single request.
Relying on as for typescript casting as the database module wouldn't actually do checks for typing.

Would that system scale well? Or should I consider a different approach