Requirements for room event storage:
- Lossless storage of the event JSON as the signatures need to remain valid (this is most easily done by simply storing the serialized JSON in a binary column)
- Able to work out the state of the room at any given event.
- Able to traverse the rooms event in topological order (for federation).
- Able to fetch new events for users (including events in rooms that the user is in and membership changes in any room, e.g. invites)
- Able to fetch the most recent N events in each room that the user is in (for initial syncs). In the future initial syncs will most likely be paginated and so the server will need to be able to fetch the most recent recent N events in the most recent M rooms.
- Able to fetch events older than a given point in a room (for back pagination).