Table Design

We divide notifications into two types:

IndividualUserNotification

• This one contains all user-specific data, it is only viewable by the user who requests it.

• Without that, we would have to sub-query all the data using an userId, making an API non-cachable.

• IndividualUserNotificationType indicates the notification purpose of that notification.

  • For example, type: NEW_ISSUE means that we want to notify userEmail there is a new issue.

    Which issue is it? According to business logic, which is the issue corr. to sessionId.

  • Similarly, type: NEW_CHANNEL_TO_JOIN means that we want to notify userEmail there is a new channel available.

    Which channel is it? Which is the channel corr. to channelId.

1enum IndividualUserNotificationType {
2    NEW_ISSUE
3    NEW_LLM_REPLY
4    NEW_DRAFT
5    NEW_CHANNEL_TO_JOIN
6    SESSION_NEW_LIVE
7    SESSION_NEW_LIVE_REPLY
8    ASSIGNED_AS_FOLLOWER
9    WATCH
10}
11
12model IndividualUserNotification {
13    id              Int                            @id @default(autoincrement())
14    type            IndividualUserNotificationType
15    sessionId       String?                        @db.Uuid
16    channelId       String?                        @db.Uuid
17    userEmail       String
18    createdAt       Float                          @default(dbgenerated("gen_created_at()"))
19    createdAtHK     String                         @default(dbgenerated("gen_created_at_hk_timestr()"))
20    
21    @@index([userEmail, type])
22}

GlobalNotification

• This one contains data that is viewable whenever a user has certain right, it is like a real-time status.

• For example, when a Live is held currently but not ended inside a channel, we notify all people there is a ONGOING_LIVE status.

• We don't treat this as IndividualUserNotification because it needs a userEmail, but the status ONGOING_LIVE can be viewed even if a user join the channel much later than the Live is held.

• This notification should not have target user as the notification target is uncertain at any time .

1enum GlobalNotificationType {
2    ONGOING_LIVE
3}
4
5model GlobalNotification {
6    id          Int                    @id @default(autoincrement())
7    type        GlobalNotificationType
8    channelId   String?                @db.Uuid
9    Channel     Channel?               @relation(fields: [channelId], references: [id], onDelete: Cascade)
10    createdAt   Float                  @default(dbgenerated("gen_created_at()"))
11    createdAtHK String                 @default(dbgenerated("gen_created_at_hk_timestr()"))
12}

Backend Notification Design:

What to Receive in Frontend?

• First of all we need to know from business logic:

• Therefore each channel-notification must have a parent projectId.

• Simiarly, each session-notification must have a channelId and projectId.

• We prototype the frontend notification data as follows:

  1export type IndividualType = {
  2    channel: "NEW_CHANNEL_TO_JOIN"
  3    session: "NEW_ISSUE"
  4    | "NEW_LLM_REPLY"
  5    | "NEW_DRAFT"
  6    | "SESSION_NEW_LIVE"
  7    | "SESSION_NEW_LIVE_REPLY"
  8    | "ASSIGNED_AS_FOLLOWER"
  9    | "WATCH"
  10}
  11
  12export type GlobalNotificationType = "ONGOING_LIVE"
  13
  14export type InappNotification = {
  15    global?: {
  16        channels: {
  17            [projectId in string]?: {
  18                [channelId in string]?: {
  19                    [type in GlobalNotificationType]?: number
  20                }
  21            }
  22        }
  23    },
  24    individual?: {
  25        channels: {
  26            [projectId in string]?: {
  27                [channelId in string]?: {
  28                    [type in IndividualType["channel"]]?: number
  29                }
  30            }
  31        },
  32        sessions: {
  33            [projectId in string]?: {
  34                [channelId in string]?: {
  35                    [sessionId in string]?: {
  36                        [type in IndividualType["session"]]?: number
  37                    }
  38                }
  39            }
  40        }
  41    }
  42}

How Frontend Consume the Data?

We store the data inside state.inappNotification of some slice.

1...
2const notification = useAppSelector(s => s.chat
3  .inappNotification
4  ?.individual
5  ?.sessions
6  ?.[selectedProjectId]
7  ?.[selectedChannelId]
8  ?.[roomId]
9);
10
11const numOfNotification = (() => {
12    if (type === "LIVE") {
13        if (isLiveEnded) {
14            return notification?.NEW_LLM_REPLY;
15        } else {
16            if (notification?.SESSION_NEW_LIVE) {
17                // this specialCount is -1, indicating NEW
18                return NotificationSpecialCount.NEW; 
19            }
20            else {
21                return notification?.SESSION_NEW_LIVE_REPLY
22            }
23        }
24    } else {
25        if (notification?.NEW_ISSUE) {
26            return NotificationSpecialCount.NEW;
27        } else {
28            return notification?.NEW_LLM_REPLY
29        }
30    }
31})();
32...

Backend Handler that Responses Desired Prototype

General Idea

The general idea is to form an object that contains all the notification.

• Notification Due to Session Level Information.

  1{
  2  [projectId] : {
  3    [channelId]: {
  4      [sessionId]: {
  5        NEW_ISSUE: 1
  6        NEW_DRAFT: 3
  7        ...
  8      }
  9    }
  10  }
  11}

• Notification Due to Channel Level Information.

  1{
  2  [projectId] : {
  3    [channelId]: {
  4      NEW_CHANNEL: 1
  5    }
  6  }
  7}

Code Implementation

This is a little bit long processing.

Since we want to get all data in one single query. If it is hard to read, it is suggested to separate individual notifications into two separate queries for channels and for sessions respectively.

1const getInappNotifications = async (req: Request, res: Response) => {
2    const userEmail = req.user?.email || "";
3
4    // this is a fixed array of notification types that is suppose to be "session notification"
5    const individualSession: IndividualUserNotification["type"][] = [
6        "ASSIGNED_AS_FOLLOWER",
7        "NEW_DRAFT",
8        "NEW_ISSUE",
9        "NEW_LLM_REPLY",
10        "SESSION_NEW_LIVE",
11        "SESSION_NEW_LIVE_REPLY",
12        "WATCH"
13    ];
14    // that to be "channel notification"
15    const individualChannel: IndividualUserNotification["type"][] = [
16        "NEW_CHANNEL_TO_JOIN"
17    ];
18
19    const [globalChannelsResult, notificationBySessionsResult] = await Promise.all([
20    db.selectFrom("GlobalNotification")
21        .leftJoin("UserToChannel", "UserToChannel.channelId", "GlobalNotification.channelId")
22        .leftJoin("Channel", "Channel.id", "GlobalNotification.channelId")
23        .leftJoin("Project", "Project.id", "Channel.projectId")
24        .select([
25            "GlobalNotification.type",
26            "GlobalNotification.channelId",
27            "Project.id as projectId"
28        ])
29        .where("UserToChannel.userEmail", "=", userEmail)
30        .execute(),

In the follwoing query we make the following aliases:

• NotificationChannel = channel being notified
• NotificationSessionChannel = the channel of the session being notified
• NotificationProject = project being notified
• NotificationSessionProject = project of session being notified (forget to add Notification at the prefix)

This will introduce sparsities (nulls) to each selected row.

Just recall that project contains many channel, channel contains many messagesSession, then the nullity check will make sense

In frontend each session, channel and project will calculate what notification to show based on these informations

31    db.selectFrom("IndividualUserNotification")
32        .leftJoin("MessagesSession", "MessagesSession.id", "IndividualUserNotification.sessionId")
33        .leftJoin("Channel as NotificationChannel", "NotificationChannel.id", "IndividualUserNotification.channelId")
34        .leftJoin("Channel as NotificationSessionChannel", "NotificationSessionChannel.id", "MessagesSession.channelId")
35        .leftJoin("Project as NotificationChannelProject", "NotificationChannelProject.id", "NotificationChannel.projectId")
36        .leftJoin("Project as NotificationSessionProject", "NotificationSessionProject.id", "NotificationSessionChannel.projectId")
37        .select([
38            "IndividualUserNotification.sessionId as sessionId",
39            "IndividualUserNotification.type as notificationType",
40            "NotificationSessionChannel.id as notificationSessionChannelId",
41            "NotificationChannel.id as notificationChannelId",
42            "NotificationChannelProject.id as notificationChannelProjectId",
43            "NotificationSessionProject.id as notificationSessionProjectId"
44        ])
45        .where("IndividualUserNotification.userEmail", "=", userEmail)
46        .execute()
47    ])
48    const globalChannels: {
49        [projectId in string]?: {
50            [channelId in string]?: {
51                [type in GlobalNotification["type"]]?: number
52            }
53        }
54    } = {};
55
56    const notificationByChannels: {
57        [projectId in string]?: {
58            [channelId in string]?: {
59                [type in IndividualUserNotification["type"]]?: number
60            }
61        }
62    } = {};
63
64    const notificationBySessions: {
65        [sectionId in string]?: {
66            [projectId in string]?: {
67                [channelId in string]?: {
68                    [sessionId in string]?: { [type in IndividualUserNotification["type"]]?: number }
69                }
70            }
71        }
72    } = {};
73
74    // set global channels notifications
75    for (const result of globalChannelsResult) {
76        const { channelId = "", type = "", projectId = "" } = result;
77        if (projectId && channelId && type) {
78            const key = `[${projectId}][${channelId}][${type}]`;
79            const count = lodash.get(globalChannels, key, 0) as number;
80            lodash.set(globalChannels, key, count + 1);
81        }
82    }
83
84    // set individual sessions and channels notifications
85    for (const result of notificationBySessionsResult) {
86        const {
87            notificationChannelId = "",
88            notificationSessionChannelId = "",
89            notificationType,
90            notificationChannelProjectId = "",
91            notificationSessionProjectId = "",
92            sessionId = ""
93        } = result;
94
95        if (!notificationType) {
96            continue;
97        }
98        if (individualChannel.includes(notificationType)) {
99            if (notificationChannelProjectId && notificationChannelId && notificationType) {
100                const key = `[${notificationChannelProjectId}][${notificationChannelId}][${notificationType}]`;
101                const count = lodash.get(notificationByChannels, key, 0) as number;
102                lodash.set(notificationByChannels, key, count + 1);
103            }
104        }
105        if (individualSession.includes(notificationType)) {
106            if (notificationSessionProjectId && notificationSessionChannelId && sessionId && notificationType) {
107                const key = `[${notificationSessionProjectId}][${notificationSessionChannelId}][${sessionId}][${notificationType}]`;
108                const count = lodash.get(notificationBySessions, key, 0) as number;
109                lodash.set(notificationBySessions, key, count + 1);
110            }
111        }
112    }
113
114    res.json({
115        success: true,
116        result: {
117            global: {
118                channels: globalChannels
119            },
120            individual: {
121                channels: notificationByChannels,
122                sessions: notificationBySessions
123            }
124        }
125    })
126}

Sample from Real Data

1{
2  global: {
3    channels: {
4      "018e3606-3293-fce9-2f55-49f61657b978": {
5        "018e3624-aead-6e5f-86c4-87b4bceaf83e": {
6          ONGOING_LIVE: 4,
7        },
8      },
9      "018def71-630f-4767-2e78-0f0b9f7360a6": {
10        "018e3975-20be-40d6-2318-6db03ca7a23d": {
11          ONGOING_LIVE: 2,
12        },
13      },
14      "018e39e2-2a2e-2b20-2762-65943cddad3b": {
15        "018e39fd-2cd2-bb83-6676-be150087df97": {
16          ONGOING_LIVE: 1,
17        },
18      },
19    },
20  },
21  individual: {
22    channels: {
23    },
24    sessions: {
25      "018def71-630f-4767-2e78-0f0b9f7360a6": {
26        "018e3975-20be-40d6-2318-6db03ca7a23d": {
27          "018e3975-4fa0-0f04-3ba0-f57b7925f8c3": {
28            WATCH: 1,
29          },
30        },
31        "018e1877-2588-c90d-8688-623edd8282cd": {
32          "018e2e0e-241e-97c1-5dfe-59a09157e350": {
33            NEW_DRAFT: 1,
34          },
35        },
36        "018e2e0d-bc74-dbf2-1ea8-3786b974267d": {
37          "018e2e31-4b1a-a468-cd91-d6c1b95539fe": {
38            NEW_DRAFT: 1,
39          },
40          "018e2e59-8f93-3a4b-3270-dd4338468d2a": {
41            NEW_DRAFT: 1,
42          },
43          "018e2e0f-2c06-a0d4-289f-2a0752af6dde": {
44            NEW_DRAFT: 1,
45          },
46        },
47      },
48      "018e39e2-2a2e-2b20-2762-65943cddad3b": {
49        "018e39fd-2cd2-bb83-6676-be150087df97": {
50          "018e3fed-bda5-ceab-1334-01c89e14b315": {
51            SESSION_NEW_LIVE: 1,
52          },
53          "018e3fec-4d53-d9bd-b991-820552aba584": {
54            NEW_ISSUE: 1,
55            ASSIGNED_AS_FOLLOWER: 1,
56          },
57          "018e3f48-177f-2a78-ec95-09be41ca76a1": {
58            NEW_ISSUE: 2,
59          },
60        },
61      },
62      "018e39c6-4a23-dcaa-cebf-2a22e9842dd6": {
63        "018e39c6-dca4-e263-6dfd-b82a002c7ace": {
64          "018e39c6-ed70-868e-44c2-26e0500241e2": {
65            WATCH: 1,
66          },
67          "018e3c22-450a-06d8-00a0-f2bb56324c82": {
68            WATCH: 1,
69          },
70        },
71      },
72      "018e3606-3293-fce9-2f55-49f61657b978": {
73        "018e3624-aead-6e5f-86c4-87b4bceaf83e": {
74          "018e3fed-5323-24cb-6f60-eff184f31f2b": {
75            SESSION_NEW_LIVE: 1,
76          },
77          "018e4095-6879-eec7-3187-a4e4165283e4": {
78            NEW_ISSUE: 1,
79          },
80          "018e3f4c-4c18-dcac-69b4-10d8c307abb0": {
81            NEW_ISSUE: 1,
82          },
83        },
84      },
85    },
86  },
87}