However for our use case we want total control over the server database. And wanted to store it in normalized tables.
The solution we went for us is streaming the mutation stream (basically the WAL) from/to client and server. And use table stream duality to store them in a table.
Permissions are handled on a table level.
When a client writes it sends a mutation to the servers. Or queues it locally if offline. Writes never conflict: we employ a CRDT “last write wins” policy.
Queries are represented by objects and need to be implemented both in Postgres as wel as SQLLite (if you want offline querying, often we don’t). A query we implement for small tables is: “SELECT *”.
Note that the result set being queried is updated realtime for any mutation coming in.
It’s by default not enforcing relational constraints on the clientside so no rollbacks needed.
However you can set a table in different modes:
- online synchronous writes only: allows us to have relational constraints. And to validate the creation against other server only business rules.
The tech stack is Kotlin on client (KMM) and server, websocket for streaming. Kafka for all mutations messaging. And vanilla Postgres for storing.
The nice thing is that we now have a Kafka topic that contains all mutations that we can listen to. For example to send emails or handle other use cases.
For every table you:
- create a serializable Kotlin data class
- create a Postgres table on the server
- implement reading and writing that data, and custom queries
Done: the apps have offline support for reading a single entity and upserts.
Querying require to be online if not implemented on the client.
However for our use case we want total control over the server database. And wanted to store it in normalized tables.
The solution we went for us is streaming the mutation stream (basically the WAL) from/to client and server. And use table stream duality to store them in a table.
Permissions are handled on a table level.
When a client writes it sends a mutation to the servers. Or queues it locally if offline. Writes never conflict: we employ a CRDT “last write wins” policy.
Queries are represented by objects and need to be implemented both in Postgres as wel as SQLLite (if you want offline querying, often we don’t). A query we implement for small tables is: “SELECT *”.
Note that the result set being queried is updated realtime for any mutation coming in.
It’s by default not enforcing relational constraints on the clientside so no rollbacks needed.
However you can set a table in different modes: - online synchronous writes only: allows us to have relational constraints. And to validate the creation against other server only business rules.
The tech stack is Kotlin on client (KMM) and server, websocket for streaming. Kafka for all mutations messaging. And vanilla Postgres for storing.
The nice thing is that we now have a Kafka topic that contains all mutations that we can listen to. For example to send emails or handle other use cases.
For every table you: - create a serializable Kotlin data class - create a Postgres table on the server - implement reading and writing that data, and custom queries
Done: the apps have offline support for reading a single entity and upserts. Querying require to be online if not implemented on the client.