0%

In-App Notification

March 14, 2024

Sql

Contents

1. Table Design

We divide notifications into two types:

1.1.

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.

enum IndividualUserNotificationType {
    NEW_ISSUE
    NEW_LLM_REPLY
    NEW_DRAFT
    NEW_CHANNEL_TO_JOIN
    SESSION_NEW_LIVE
    SESSION_NEW_LIVE_REPLY
    ASSIGNED_AS_FOLLOWER
    WATCH
}

model IndividualUserNotification {
    id              Int                            @id @default(autoincrement())
    type            IndividualUserNotificationType
    sessionId       String?                        @db.Uuid
    channelId       String?                        @db.Uuid
    userEmail       String
    createdAt       Float                          @default(dbgenerated("gen_created_at()"))
    createdAtHK     String                         @default(dbgenerated("gen_created_at_hk_timestr()"))
    
    @@index([userEmail, type])
}

1.2.

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 an 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 .

enum GlobalNotificationType {
    ONGOING_LIVE
}

model GlobalNotification {
    id          Int                    @id @default(autoincrement())
    type        GlobalNotificationType
    channelId   String?                @db.Uuid
    Channel     Channel?               @relation(fields: [channelId], references: [id], onDelete: Cascade)
    createdAt   Float                  @default(dbgenerated("gen_created_at()"))
    createdAtHK String                 @default(dbgenerated("gen_created_at_hk_timestr()"))
}

2. Backend Notification Design:

2.1.

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:

    export type IndividualType = {
    channel: "NEW_CHANNEL_TO_JOIN"
    session: "NEW_ISSUE"
    | "NEW_LLM_REPLY"
    | "NEW_DRAFT"
    | "SESSION_NEW_LIVE"
    | "SESSION_NEW_LIVE_REPLY"
    | "ASSIGNED_AS_FOLLOWER"
    | "WATCH"
}

export type GlobalNotificationType = "ONGOING_LIVE"

export type InappNotification = {
    global?: {
        channels: {
            [projectId in string]?: {
                [channelId in string]?: {
                    [type in GlobalNotificationType]?: number
                }
            }
        }
    },
    individual?: {
        channels: {
            [projectId in string]?: {
                [channelId in string]?: {
                    [type in IndividualType["channel"]]?: number
                }
            }
        },
        sessions: {
            [projectId in string]?: {
                [channelId in string]?: {
                    [sessionId in string]?: {
                        [type in IndividualType["session"]]?: number
                    }
                }
            }
        }
    }
}

2.2.

How Frontend Consume the Data?

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

...
const notification = useAppSelector(s => s.chat
  .inappNotification
  ?.individual
  ?.sessions
  ?.[selectedProjectId]
  ?.[selectedChannelId]
  ?.[roomId]
);

const numOfNotification = (() => {
    if (type === "LIVE") {
        if (isLiveEnded) {
            return notification?.NEW_LLM_REPLY;
        } else {
            if (notification?.SESSION_NEW_LIVE) {
                // this specialCount is -1, indicating NEW
                return NotificationSpecialCount.NEW; 
            }
            else {
                return notification?.SESSION_NEW_LIVE_REPLY
            }
        }
    } else {
        if (notification?.NEW_ISSUE) {
            return NotificationSpecialCount.NEW;
        } else {
            return notification?.NEW_LLM_REPLY
        }
    }
})();
...

2.3.

Backend Handler that Responses Desired Prototype

2.3.1.

General Idea

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

  • Notification Due to Session Level Information.

    {
  [projectId] : {
    [channelId]: {
      [sessionId]: {
        NEW_ISSUE: 1
        NEW_DRAFT: 3
        ...
      }
    }
  }
}

  • Notification Due to Channel Level Information.

    {
  [projectId] : {
    [channelId]: {
      NEW_CHANNEL: 1
    }
  }
}
2.3.2.

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}

3. Sample from Real Data

{
  global: {
    channels: {
      "018e3606-3293-fce9-2f55-49f61657b978": {
        "018e3624-aead-6e5f-86c4-87b4bceaf83e": {
          ONGOING_LIVE: 4,
        },
      },
      "018def71-630f-4767-2e78-0f0b9f7360a6": {
        "018e3975-20be-40d6-2318-6db03ca7a23d": {
          ONGOING_LIVE: 2,
        },
      },
      "018e39e2-2a2e-2b20-2762-65943cddad3b": {
        "018e39fd-2cd2-bb83-6676-be150087df97": {
          ONGOING_LIVE: 1,
        },
      },
    },
  },
  individual: {
    channels: {
    },
    sessions: {
      "018def71-630f-4767-2e78-0f0b9f7360a6": {
        "018e3975-20be-40d6-2318-6db03ca7a23d": {
          "018e3975-4fa0-0f04-3ba0-f57b7925f8c3": {
            WATCH: 1,
          },
        },
        "018e1877-2588-c90d-8688-623edd8282cd": {
          "018e2e0e-241e-97c1-5dfe-59a09157e350": {
            NEW_DRAFT: 1,
          },
        },
        "018e2e0d-bc74-dbf2-1ea8-3786b974267d": {
          "018e2e31-4b1a-a468-cd91-d6c1b95539fe": {
            NEW_DRAFT: 1,
          },
          "018e2e59-8f93-3a4b-3270-dd4338468d2a": {
            NEW_DRAFT: 1,
          },
          "018e2e0f-2c06-a0d4-289f-2a0752af6dde": {
            NEW_DRAFT: 1,
          },
        },
      },
      "018e39e2-2a2e-2b20-2762-65943cddad3b": {
        "018e39fd-2cd2-bb83-6676-be150087df97": {
          "018e3fed-bda5-ceab-1334-01c89e14b315": {
            SESSION_NEW_LIVE: 1,
          },
          "018e3fec-4d53-d9bd-b991-820552aba584": {
            NEW_ISSUE: 1,
            ASSIGNED_AS_FOLLOWER: 1,
          },
          "018e3f48-177f-2a78-ec95-09be41ca76a1": {
            NEW_ISSUE: 2,
          },
        },
      },
      "018e39c6-4a23-dcaa-cebf-2a22e9842dd6": {
        "018e39c6-dca4-e263-6dfd-b82a002c7ace": {
          "018e39c6-ed70-868e-44c2-26e0500241e2": {
            WATCH: 1,
          },
          "018e3c22-450a-06d8-00a0-f2bb56324c82": {
            WATCH: 1,
          },
        },
      },
      "018e3606-3293-fce9-2f55-49f61657b978": {
        "018e3624-aead-6e5f-86c4-87b4bceaf83e": {
          "018e3fed-5323-24cb-6f60-eff184f31f2b": {
            SESSION_NEW_LIVE: 1,
          },
          "018e4095-6879-eec7-3187-a4e4165283e4": {
            NEW_ISSUE: 1,
          },
          "018e3f4c-4c18-dcac-69b4-10d8c307abb0": {
            NEW_ISSUE: 1,
          },
        },
      },
    },
  },
}