Investigate using the CRDT data structure

[symbiogenesis]

Conflict-free replicated data types are a sort of cutting edge data structure for distributed synchronization. It is what allows things like collaborative editing on Google Docs.

In theory using this as a back-end would allow excellent performance characteristics with minimal complexity. Offloading most of the heavy lifting to the algorithm.

This approach has already been implemented in Dart.

sql_crdt, an abstract implementation for using relational databases as a data storage backend.

sqlite_crdt, an implementation using Sqlite for storage, useful for mobile or small projects.

postgres_crdt, a sql_crdt that benefits from PostgreSQL's performance and scalability intended for backend applications.

The CRDT algorithm itself was also implemented in .NET over a SingalR websocket with Yjs although it is less relevant to the needs of relational databases.

The notion of using a websocket is kind of nuts, but quite appealing. All changes could be streamed as they happen.