I'm implementing a chat using Firebase Realtime DB. There's a requirement, that some chatrooms can show new messages by default, but messages in different chatrooms have to be approved first before displaying them.

The DB structure in Firebase is nested like this:

/chat/$roomID/$messageID/

and each message object is structured as follows:

{
    user: string
    content: string
    isApproved: boolean
    clientStamp: number // client-side unix timestamp
    serverStamp: number // server-side unix timestamp, via `firebase.database.ServerValue.TIMESTAMP`
}

Having only the display-by-default chatrooms would be easy - I'd order the data by serverStamp:

let query = DBConn.ref('chat/' + roomID).orderByChild('serverStamp').limitToLast(50);
query.on("value", snapshot => {
     // set the list of messages to display
     this.appendSnapshot(snapshot);
});

The approve-first rule makes this approach difficult however. The obvious solution would be to show only messages with attribute isApproved set to true. But if I'd query for the last N messages ordered by timestamp, and someone would post N unapproved messages, then that would oust any of the approved messages from the query result and the chat would be empty. Realtime DB has the following limitation, which makes it impossible to query by both timestamp + isApproved:

You can only use one order-by method at a time

So right now I'm workarounding it the following way:

• messages are ordered by clientStamp (clientside unix timestamp) instead of serverStamp
• when sending a new message to server, I divide the value of clientStamp by two, which mean these new messages will never oust the already approved older messages from the query result (since their timestamp has higher value)
• when a message gets approved, the value of clientStamp is set to a value of firebase.database.ServerValue.TIMESTAMP (serverside time), which pushes it to the top of the chat query (orderByChild('serverStamp'))

... but this is rather a quick workaround. Is there any way how to make this work nicely for both display-by-default and approve-first chatrooms?