model Action {
  id           String   @id @default(cuid())
  type         String   // like, reply, follow
  author_id    String
  source_id     String  // entry_id, chain_id
  source_type   String  // entry, chain
  author       User     @relation(fields: [author_id], references: [id])
  is_deleted   Boolean  @default(false)
  created_at   DateTime @default(now())
  updated_at   DateTime @updatedAt
  @@index([author_id])
}

model Activity {
  id           String   @id @default(cuid())
  author_id    String
  source_id    String
  source_type  String  // entry, chain, action, follow

  author       User     @relation(fields: [author_id], references: [id])

  created_at   DateTime @default(now())
  updated_at   DateTime @updatedAt
  @@index([author_id])
  @@unique([author_id, source_id, source_type])
}

model Notification {
  id             String   @id @default(cuid())
  key            String
  author_id      String
  recipient_id   String

  sound_id       String  // immediate access to sound
  source_id      String  // action_id, chain_id
  source_type    String  // action, chain, follow

  recipient    User     @relation(name: "notifee", fields: [recipient_id], references: [id])
  author       User     @relation(name: "notifier", fields: [author_id], references: [id])

  is_read      Boolean  @default(false)
  is_deleted   Boolean  @default(false)
  created_at   DateTime @default(now())
  updated_at   DateTime @updatedAt
  @@index([author_id])
  @@index([recipient_id])
}

this is my current set up. an “action” can be sort of marking interaction with an entity (like, flag, important, etc).

when an action is submitted, we create the subsequent activity/notification in the database.

we also push the notificationId to a Redis sorted set called user:unread_notifications.

when the user requests notifications, we check the sorted set for the ids of each notification, we then hit the database for each notifications data. using the key, we batch all similar notifications together, then using the sourceid/type, we ping redis for the most up to date context for each notification and merge the two and finally return the notification data to the user and then cache the aggregated notification data into user:read_notifications

i can’t find a single source of reference for learning this so I’m resorting to asking here.

i’m creating a social media application. i’m expecting a very large user base from the beginning so i’m trying to toe the line between keeping things simple/scalable.

ive tried the above but im not sure if its the correct/most efficient approach.