This was quite an enjoyable read. I fully agree that normalization is the way to go. I’ve been working at my current company to use EventStore by Greg Young, https://geteventstore.com, which solves the problem of needing to be able to have denormalized data like your asking.
It does require events and the use of projections from the source streams to do a transformation. Let’s use the message unread counter you’re using above.
Rooms above are divided by user, so let’s have all messages be written to a global message stream. We’ll use projections (EventStore’s projections feature) to take the global message queue and create a stream for each user’s message client to read their messages from. When a user reads a message, we write an event to another stream or even to the specific user’s message stream which may be beneficial. One way this may help is if we know which messages a specific user has read, we can ensure across all devices that the count would be the same. The count of read/unread messages is the count of unique messages in the queue minus the count of unique messages read. EventStore can do snapshots to limit the recursive fold of events or run a projection that calculates this off for each user if desired to maintain it off the service to do it on the fly.