1) Does the CRDT require synchronized clocks? I always felt that was the weakness of stuff like Google's Spanner (and its main competitor, can't remember the name). There just has to be some other way to derive ordering, maybe using pseudorandom sequences generated from the same seed or using time deltas from the last update or something? Although those might be spoofable/cheatable.
2) Is there any workaround for the lack of foreign key constraints? Those are critical to what makes query builders like Laravel's Eloquent relations easy to reason about (especially for deletions and other cascade operations). For example, maybe a dirty flag on the row could get cleared once eventual consistency was reached and all FK constraints were met? I think the other limitation with uniqueness on only 1 primary key could be addressed through composite keys or a hash of other keys somehow, so is not a dealbreaker.
No. CRDT are Commutative. You can apply a set of updates in any order and achieve the same result. This is a core feature.
"Provably, replicas of any CRDT converge to a common state that is equivalent to some correct sequential execution. As a CRDT requires no synchronisation, an update executes immediately, unaffected by network latency, faults, or disconnection"
The secret sauce is the use of a Lamport timestamp (instead of a clock) as a logical time counter, which is a sequence number or local count of the number of writes. The sequence number gets sent by each peer for each write. Most of the time, the CRDT can derive which event happened first by comparing sequence numbers from each peer.
In pathological cases, like two users making a slightly different edit to the same item with the same last-known state on two different devices while they're in a tunnel disconnected from wireless internet, a tie-breaking rule determines which edit wins. That could be as simple as favoring the peer id which comes first alphanumerically.
In practice, peers write to the CRDT and then receive a series of update events that may oscillate or contradict the peer's notion of what happened, until eventual consistency is reached. So in a video game, both players might shoot and think they killed one another until the CRDT determines that one player shot first. As long as the game is stateless and can reflect the last known state received from the CRDT, then it will eventually come to correctly show that one player won. But it might have some undesired animation in the meantime showing the wrong player winning.
Also I suspect that a real game would have a more advanced tie-breaking heuristic that does include some notion of local time. For example, if the max ping is capped at 5 seconds or something, then the CRDT could use dead reckoning to place constraints on when each write could have happened. But it would only be used in rare cases when ordering can't be derived deterministically. It could also use a majority vote from each peer like with Raft/Paxos (I think), but that might stray into something too complex to implement or too vulnerable to cheating.
1) Does the CRDT require synchronized clocks? I always felt that was the weakness of stuff like Google's Spanner (and its main competitor, can't remember the name). There just has to be some other way to derive ordering, maybe using pseudorandom sequences generated from the same seed or using time deltas from the last update or something? Although those might be spoofable/cheatable.
2) Is there any workaround for the lack of foreign key constraints? Those are critical to what makes query builders like Laravel's Eloquent relations easy to reason about (especially for deletions and other cascade operations). For example, maybe a dirty flag on the row could get cleared once eventual consistency was reached and all FK constraints were met? I think the other limitation with uniqueness on only 1 primary key could be addressed through composite keys or a hash of other keys somehow, so is not a dealbreaker.