Eventual Consistency vs Authority Check

[happy15]

Hi there, first applause for the great work.

I made a collaborative docs platform before and now am working on enterprise SaaS. For the former, optimistic read and write ensured local-first functionality. For the latter, strong access control is a necessity.

I am always wondering how to combine the two. In the spirit Stop building databases post and this project, auto sync between frontend and backend databases with eventual consistency made the mental model of building an app much easier and better user experience. But it's kind of conflicting with strict access control in the enterprise SaaS environment.

For example, a client was able to read and write customer records from number 1 to 10, but later the access to 7 to 9 stopped by admin. During the info sync of this access control change from backend to frontend, the client can still operate on customer records 7 to 9, which may violate the access control.

Maybe optimistic read but pessimistic write? The client can still read the info freely locally before it knows the access to 7 to 9 are stopped, but any change to any records including 7 to 9 needs to go through backend first.

Looking forward to some ideas. Thanks.

[carlsverre]

Thank you! I'm very glad you like SQLSync.

The access control discussion is such an important part of the design, which is why I've left it out of the initial prototype. Thanks for taking the initiative and kickstarting the discussion!

Let's talk about write permissions first. In the example you used, I think the SQLSync architecture may work fairly well. Due to SQLSync having a centralized server, it's possible for the reducer to make different decisions on the server than it does on the client. These decisions can leverage fully consistent data on the server side, such as up to date permissions. So in the case that the user looses access to write to rows 7-9, if they try submitting a write (potentially due to eventual consistency or being offline) the server can reject the write and notify the user of that during a later sync. After the client updates, it will see whatever result the server decided which may override an optimistic decision.

Now, there are certainly cases, especially in the enterprise, where you don't want the write to seem like it went through on the client. In this case you want some way to either: show the client that the write is pending, or block the update until it applies on the server (and thus is authoritative). Again, SQLSync can handle both of these cases pretty well. For the first case I'm adding feature #31 which would allow a client to inspect which mutations have been fully acknowledged. Thus rendering some kind of in-progress/pending visual to the user. In the second case, the same sync-state API will be able to push a mutation to the server but not apply it locally. Thus turning the mutation into a regular synchronous API call.

I'm excited to hear your thoughts on the various solutions to the write auth problem.

Read authorization is a much harder problem for SQLSync, since it's designed around full-db replication. Currently, to implement read permissions requires storing data in different databases. Thus the architecture is suited to use cases where the data model can be divided into many small databases where a user has either read-only or read/write to the entire db. Examples of this include: per-user state, per-document state, etc.

I have many thoughts on how to potentially resolve this problem. One is to build partial replication, allowing more granular permissions. Another is to adding powerful backend primitives that can move data between SQLite databases or even in/out of SQLSync (i.e. move info from a centralized db to SQLSync or back). But this is very much an open area of research.

Hope this helps! Please feel free to reach out (hello@orbitinghail.dev) if you're interested in jumping on a call and digging into some specific use cases.

[happy15]

Thanks for the reply.

IMHO your solution to the write auth is enough: an API to check full acknowledgement to let the user know what really happens to avoid misunderstanding. That's the key.

Also agreed on the part of read auth in general, which is smaller and finer granularity of syncing. Partial replication shares the same spirit, and fully mimic the traditional way in the user's perspective. Seems tricky to do: filter changes of accessible records and then send to the frontend.

[carlsverre]

Indeed partial replication is very tricky to do well. It likely requires switching from physical to logical replication which adds quite a lot of complexity to the solution. But it also seems very valuable - so worth researching. :)

[happy15]

https://arxiv.org/abs/2312.00638

An interesting paper on return partial database on sql query.

Related to our discussion somehow and no idea how to use it in our situation.

So just fyi.

[carlsverre]

Thanks for sharing that! When I initially read through I was excited that it might share some insights into achieving better partial read replication. But after some review, it's focused on a slightly different goal. The paper goes into detail on how to transform a arbitrary SQL Query into a minimal set of required tuples to produce that result set. I.e. you can think of it as an algorithm for producing a normalized version of a denormalized result. I think this paper is very useful to anyone returning SQL result sets in an API, and as such - should SQLSync adopt some kind of partial replication solution this paper may be useful.

But thank you for sharing! If you see anything else like this please send it my way.