How Would You Make It: Efficient and optimized way of tracking unread message per user

Recently at work we encountered an issue where the inbox Api for an application we were building was slow and we realized it's not scalable.

So when we diagnosed we came up with 3 solutions we can implement
which can take out complex where clause or optimize the table which read info was taken from or both,you must be wondering how complex is this where clause, it went something like this

WHERE receiver ->'$.permission' = ${permissionValue}
      AND (CASE
        WHEN receiver->'$.userType' = ${userType}
        THEN receiver->'$.sendOfficeUser' = true
        WHEN receiver->'$.moduleType' = 'reviewApproval'
        THEN JSON_UNQUOTE(receiver->'$.pLevel') IN ('${permList.review.join("','")}')
        WHEN receiver->'$.moduleType' = 'actions'
        THEN JSON_UNQUOTE(receiver->'$.pLevel') IN ('${permList.action.join("','")}')
        WHEN receiver->'$.moduleType' = ''
        THEN JSON_UNQUOTE(receiver->'$.pLevel') = ''
        ELSE true
        END)
      AND (CASE
        WHEN receiver->'$.grant' IS NOT NULL
        THEN receiver->'$.grant' = '${grant}'
        ELSE true
        END)`

1. Modify the current implementation of how information is stored in NotificationRead - table used to track all read status

2. Ditch the NotificationRead table altogether and hence the Join query as well and introduce two new columns, Owner and read, will contain an array of userId's, this method will reduce not only the join but a complex WHERE clause which relied on JSON column reciever as shown above

3. And Finally a hybrid of the two with solutions earlier, we will have owner column to recognise who can see it but read information will be queried from NotificationRead table

I will talk in detail about the first implementation and remaining two which are straight forward will be given a breif description

1st implementation

Columns

• notification_id

  • Data Type - bigint
  • Foreign key Constraint - Notification.notification_id
  • Composite Key

• user_id

  • Data Type - varchar(50)
  • Foreign key Constraint - Users.username
  • Composite Key

• mark_all

  • Data Type - bit(1)
  • Can be Null

• last_id_marked

  • Data Type - bigint
  • Foreign key Constraint - Notification.notification_id
  • Can be Null

This Table Will be Populated when a user clicks on a inbox notification or clicks on Mark all read

The composite Keys In this tables are straight forward, _notification_id_is the notification read and _user_id_is the user who read it

_mark_all_will be used as a flag of sorts to show that it was marked all from that Id onwards, means that let's say a user with username 'anoop' clicks mark all, and let's say the latest notification's _notification_id_is 800
NotificationRead record will look like this

{
"NotificationRead": [
    {
        "notification_id" : 800,
        "user_id" : "anoop",
        "mark_all" : 1,
        "last_id_marked" : null
    }
]}

What this means is:

• 1. that while querying the Notification table we will assume that all notifications below 800 is read by the user, this is possible because _notification_id _in Notification table is auto increment.
• 2. What this means is that we will only need to store one record in case when a mark all is performed, rather than store all records

last_read_id(probably not the accurate name to it's purpose it serves) will used to store the last_id in a consecutive list of id's marked as read, let me explain
if users mark a set of consecutive mark all akin to something like check box in Gmail which shows 50 at a time, let's say notification_id's 851 to 801 and try to mark them as read our db won't store 50 records instead only one record. and it will look like this

{
"NotificationRead": [
    {
        "notification_id" : 851,
        "user_id" : "anoop",
        "mark_all" : null,
        "last_id_marked" : 801
    }
]}

what this means:

• that while querying the Notification table we will assume that all notifications between 851 and 801 is read by the user
• What this means is that we will only need to store one record instead of 50

The Query

let us look at sample query resulting from this pattern of data, I made a demo table and tested, and it works fine

SELECT n.notification_id , (CASE WHEN nr.notification_id IS NOT NULL THEN true ELSE false END) AS hasRead
FROM Notification n
LEFT JOIN NotificationRead nr ON nr.user_id = 'anoop' AND (nr.mark_all = 1 AND nr.notification_id >= n.notification_id) 
OR (n.notification_id = nr.notification_id) 
OR (nr.last_id_marked IS NOT NULL AND n.notification_id BETWEEN nr.last_id_marked AND nr.notification_id) 
WHERE {condition}

and now we come to last peice in puzzle (actually the first)...

Data Insertion

• Query all notifications id applicable to user with current read status like shown in the sample query in earlier section

• map the result and update the read status of the FE provided id(s) to true, so far straight forward
  

const newlyReadData = currentReadData.map((currentRead) => {
    return {
             ...currentRead,
             isRead: currentRead.isRead ? true: ids.includes(currentRead.id)
           }
})

• Now the crux of data insertion logic starts here. we will reduce the resultant array after mapping and break it into an new array of array of all the id's that has isRead true status, breakdown of the array of array's (we can call it grouping , batching something like that). would be like this:

• let's say a user had 10 notification's 1-10, and the resultant array map of is read is like this = 1,2,3,5,8,9, the reduced array batch will look like this [[1,2,3],[5],[8,9] we will batch all consecutive read notification into one array as you must have deduced from example below. to demonstrate further let's take a look at another example

• same scenario as earlier but read one's are like this 1,2,5,6,7,8,9,10 the batch array will look like [[1,2],[5,6,7,8,9,10]]
  

const batchingNotificationsRead = (notification) => {
    const batchArray = []
    let individualBatch = []
    for (const [index,notified] of notification.entries()) {
        if (notified.isRead){
            individualBatch.push(notified.id)
        }
        else if (!notified.isRead && individualBatch.length > 0) {
            batchArray.push(individualBatch)
            individualBatch = []
        }
        if (index === notification.length -1 && individualBatch.length > 0){
            batchArray.push(individualBatch)
            individualBatch = []
        }
    }
    return batchArray
}

finally let's say we have all 10 as read batch array will look like [[1,2,3,4,5,6,7,8,9,10]] .
Now from the batch array we prepare data to be inserted to data for the batching [[1,2,3],[5],[8,9] we will have three records in DB as follows

[

{   
    "notification_id" : 1,
    "user_id" : "anoop",
    "mark_all" : null,
    "last_id_marked" : 3
},
{  
     "notification_id" : 5,
    "user_id" : "anoop",
    "mark_all" : null,
    "last_id_marked" : null
},
{
    "notification_id" : 8,
    "user_id" : "anoop",
    "mark_all" : null,
    "last_id_marked" : 9
}
]

for the batching [[1,2],[5,6,7,8,9,10] we will have two records in DB as follows

[

{   
    "notification_id" : 1,
    "user_id" : "anoop",
    "mark_all" : null,
    "last_id_marked" : 2
},
{  
     "notification_id" : 5,
    "user_id" : "anoop",
    "mark_all" : 1,
    "last_id_marked" : null
}
]

for the batching [[1,2,3,4,5,6,7,8,9,10] we will only have one records in DB as follows

[
    {  
         "notification_id" : 1,
        "user_id" : "anoop",
        "mark_all" : 1,
        "last_id_marked" : null
    }
]

const prepareNotificationReadForDb = (data ) => {
    let dbData = []
    for (let j = data.length - 1; j>=0; j--) {
        const batch = data[j]
        if (batch[batch.length - 1] === notification[notification.length - 1].id && batch.length > 1) {
            let insertData = {
                "notification_id" : data[j][0],
                "user_id" : "anoop",
                "mark_all" : 1,
                "last_id_marked" : null
            }
            dbData.push(insertData)
        }else if (batch.length > 1) {
            let insertData = {
                "notification_id" : batch[0],
                "user_id" : "anoop",
                "mark_all" : null,
                "last_id_marked" : batch[batch.length - 1]
            }
            dbData.push(insertData)
        }else {
            let insertData = {
                "notification_id" : data[j][0],
                "user_id" : "anoop",
                "mark_all" : null,
                "last_id_marked" : null
            }
            dbData.push(insertData)
        }
    }
    return dbData
}

Last step is deleting all previous records under the username and inserting the newly formed records, since we are doing deletion before updating we have to do unitofworktransaction method as it will be really dangerous to delete and us not being able update.

What this batching means is that we will have maximum n records for 2n-1 notification a user has (if the user read all notifications alternatively). and if they press mark all it reduces to 1 record per user this reduced records in the table speeds up the query.

So Now I would Like to know, how would have you done it, will you guys choose any one of the options mentioned above or you guys have a solution of your own. Please share...