Conflict Resolution and CRDTs

Core Concepts

Why conflicts exist in local-first systems

In a local-first architecture, every client holds a writable replica of the data. While a client is offline, it writes to its local copy with no coordination against other replicas. When connectivity resumes, those independent writes must be reconciled. There is no way to prevent this situation — it is the price of offline capability.

The two primary strategies for handling this reconciliation are Last-Write-Wins (LWW) and CRDT-based automatic merge.

---

Last-Write-Wins (LWW)

LWW resolves a conflict by keeping the change with the latest timestamp and silently discarding all others. It is the simplest possible strategy and, according to Hasura's offline-first design guide, is suitable for roughly 95% of apps where users work with shared — but not collaboratively edited — state.

LWW is pragmatic because:

• No extra metadata is stored per operation.
• The server determines the canonical timestamp at commit time, keeping client logic simple.
• It is the default in most sync engines that do not implement deeper merge semantics.

Its fatal flaw is equally simple: it is destructive. When two users edit the same field while offline, one user's work disappears without any notification. This is acceptable for fields with low contention (a user updating their own profile), but it silently violates intent for any field that two users might legitimately and independently change.

---

The LWW semantics gap

The deeper problem with LWW is not clock drift — it is intent. Matthew Weidner's CRDT survey describes this as the semantic gap: LWW picks a value based on timestamp, but applications often need the right value based on what users intended.

Consider a counter tracking items added to a shared list by two users simultaneously. User A adds 3 items (counter goes from 10 to 13 on their replica); User B adds 2 items (counter goes from 10 to 12 on their replica). Under LWW, the merged result is either 13 or 12 — not 15. The correct answer requires aggregation, which LWW cannot express.

Designing semantics that correctly capture user intent across single-user edits, multi-user concurrent edits, and extended offline scenarios is technically difficult to implement correctly in all cases. This is why CRDTs were developed.

---

CRDTs: automatic merge without data loss

A Conflict-Free Replicated Data Type is a mathematical data structure with one defining property: it is commutative. Changes can be merged in any order across any number of replicas, and the final state is always identical. No user's work is silently discarded — every concurrent change is incorporated into the merged result.

As described by Supabase's pg_crdt write-up and the EDB Postgres Distributed documentation, there are two merge strategies in practice:

• Standard CRDTs: merge the full new version and the full local version together. Requires comparing complete state.
• Delta CRDTs: compare the old and new versions, compute only the delta (the difference), and apply that delta to the local version. This is more efficient when only a portion of a large data structure has changed.

Both maintain the commutativity guarantee. Delta CRDTs reduce the computational and bandwidth overhead of the merge process, which matters for large documents.

ElectricSQL implements CRDT-based synchronization for active-active bi-directional sync between Postgres and SQLite, built by the same researchers who invented CRDTs. Their model means application developers do not write any conflict resolution code — the merge is automatic.

---

Consistency models: eventual vs. causal

Two consistency models are relevant to local-first conflict handling:

Eventual consistency: The guarantee that all replicas will converge to the same state — eventually. Users may observe intermediate inconsistent states during the synchronization window. This is the baseline guarantee provided by local-first systems using optimistic mutations and server reconciliation. It enables immediate local feedback and offline capability at the cost of temporary divergence.

Causal consistency: A stronger model requiring that causally related operations appear in the same order on all processes, while independent operations may be observed in any order. Crucially, causal consistency remains available under network partitions — clients can continue reading and writing. Implementing it requires each operation to carry causal context metadata (a tag) so that receiving nodes can hold back an update until its causal predecessors have arrived.

CRDTs typically operate in an eventually consistent model. Causal consistency is an additional layer that prevents anomalies such as a reply appearing before the message it replies to.

---

Conflict detection (when you cannot prevent conflicts)

Before merging, systems must detect that a conflict exists. Write-write conflicts occur when the same field is updated in two different replicas during the same period — for example, a client editing offline while another user edits the server copy.

Obtaining exclusive access to prevent conflicts is not practical in offline-first architectures: the client may have no connectivity. Instead, practical strategies include tracking change tokens, version numbers, or timestamps and comparing the client's version against the server's version at sync time.

---

Unique constraints and distributed writes

Auto-incrementing primary keys and UNIQUE constraints are architecturally incompatible with local-first replication. When two clients independently generate sequential IDs offline, both frequently yield the same value. At merge time, those duplicate IDs cause constant conflicts and data loss during INSERT statements.

Research on replicated relational systems is unambiguous: this is not a tuning problem — it is a fundamental mismatch. The replication protocol requires conflict-free merging; unique constraint enforcement requires centralized coordination. You cannot have both simultaneously.

ElectricSQL explicitly does not support unique constraints and requires their removal before "electrifying" a table.

The standard mitigation is UUIDs for all primary keys. This trades human readability and minor database performance for replication compatibility. Beyond primary keys, unique constraints on any column where clients can independently assign values should be removed from the schema and replaced with application-level validation or eventual consistency patterns.

---

Compare & Contrast

Dimension	Last-Write-Wins	CRDT Automatic Merge
Implementation cost	Low — no extra metadata	High — requires CRDT-aware library or engine
Data loss risk	High — concurrent writes are silently discarded	Low — all writes are incorporated
Semantic correctness	Only correct for single-owner fields	Correct for collaborative fields when the CRDT type matches the intent
Counter/aggregation support	Cannot aggregate — one value replaces the other	Supported via G-Counter, PN-Counter types
Performance	O(1) merge	O(n) to O(n log n) depending on type and history depth
Undo/redo	Simple — reverse the last write	Complex — see Boundary Conditions
Unique constraints	Equally incompatible	Equally incompatible
Best fit	Per-user owned fields, configuration, settings	Collaborative text, shared lists, counters with meaningful aggregation

ElectricSQL vs. PowerSync with Yjs

ElectricSQL's CRDT approach is built directly into the sync layer. The developer does not configure merge behavior per field — the engine handles it. This is ergonomic for row-level data but provides less control over document-level collaborative editing semantics.

PowerSync with Yjs integrates the Yjs CRDT library for collaborative text editing. Yjs is a separate document-level CRDT that the application manages; PowerSync handles the sync transport. This gives more control and access to Yjs's rich text semantics, but requires the developer to manage the boundary between Yjs state and the relational schema stored in Postgres.

ElectricSQL abstracts conflict resolution away from the developer. PowerSync + Yjs gives the developer control over document semantics at the cost of integration complexity.

---

Worked Example

Scenario: a shared task board

Two team members — Alice and Bob — both use the app offline. They each update the same task record.

The data:

-- tasks table (UUID primary key, no UNIQUE constraints on editable fields)
CREATE TABLE tasks (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  title TEXT,
  status TEXT,
  assigned_to TEXT,
  updated_at TIMESTAMPTZ
);

Alice (offline for 2 hours) changes status from 'todo' to 'in_progress' and sets assigned_to to 'alice'.

Bob (offline for 30 minutes) changes assigned_to to 'bob' and leaves status as 'todo'.

---

Under LWW: Bob synced first (shorter offline window, earlier timestamp). Alice's sync arrives later with a newer timestamp. Result: status = 'in_progress', assigned_to = 'alice'. Bob's assignment is silently lost with no notification.

If the LWW timestamp were at the field level (not the row level), the result could instead be: status = 'in_progress' (Alice's timestamp wins), assigned_to = 'alice' (Alice's timestamp wins). Still loses Bob's intent.

Under CRDT row-level merge (ElectricSQL): Both changes are automatically incorporated. Depending on the CRDT type used per field (typically LWW-register at field level with causal ordering rather than wall-clock time), each field retains the causally later write. Neither write is discarded, though if both users changed the same field, the causally later one wins — but this is now a deterministic, reproducible result rather than a wall-clock race.

Lesson: For collaborative applications, field-level causal ordering beats row-level wall-clock LWW.

---

The counter case — where CRDTs also have limits:

-- inventory table
CREATE TABLE inventory (
  product_id UUID PRIMARY KEY,
  stock_count INTEGER
);

Alice processes a sale: stock_count goes from 100 to 97 (sold 3). Bob processes a sale: stock_count goes from 100 to 95 (sold 5).

Under CRDT merge with LWW-register semantics: the result is either 97 or 95. The correct answer is 92. A PN-Counter CRDT can represent this correctly, but PN-Counters cannot enforce non-negative constraints. If stock was actually 4 and both sales go through, the merged result is -4. There is no mechanism to reject the merge.

For inventory and financial balances, CRDTs are the wrong tool. These domains require serialized writes through a coordination point.

---

Common Misconceptions

"CRDTs eliminate data loss." CRDTs prevent the silent discard of Last-Write-Wins, but production CRDT deployments introduce their own data loss vectors: misconfigured sequence rules, failed merge operations, node divergence, and incorrect compaction strategies. CRDTs reduce one category of data loss while introducing new operational risks.

"LWW is always wrong for collaborative data." LWW is wrong for concurrently edited data. For fields that only one user typically owns — their own display name, their own preferences, their own draft — LWW is often perfectly correct and far simpler to operate.

"Using UUIDs fixes all unique constraint problems." UUIDs solve the primary key collision problem. They do not solve unique constraints on other columns — for example, a UNIQUE(email) constraint on a users table, or UNIQUE(slug) on a posts table. Two clients can independently create records with the same email or slug while offline. UUID primary keys do not prevent this. These constraints must be removed from the schema and enforced at the application layer or through post-sync reconciliation.

"CRDT merge is free." Merge operations have real computational cost. CRDT space complexity is linear with the total number of inserts and insertion points. Documents with heavy edit histories degrade in performance over time. State-based CRDTs send their entire state on every sync, which becomes expensive for large documents where only small changes occurred.

"ElectricSQL and PowerSync handle CRDTs the same way." They do not. ElectricSQL embeds CRDT semantics in the sync layer automatically. PowerSync delegates document-level CRDT management to Yjs, which the developer integrates and manages separately. The operational model, control surface, and failure modes are different.

---

Boundary Conditions

When CRDTs break down: bounded numeric values

CRDT counters cannot enforce non-negative constraints or upper bounds. Concurrent counter updates merge in ways that can produce invalid states. Retail inventory, account balances, seat reservations, and ticket limits all require serialized writes through a coordination point (the server). Attempting to use CRDTs here will produce silent constraint violations in production.

When LWW breaks down: intent-bearing concurrent writes

Any field where two users might independently and legitimately make different changes to the same value — shared document titles, collaborative notes, shared assignment fields — will silently discard one user's work under LWW. The semantic gap grows wider as the frequency of concurrent offline edits increases.

Undo/redo under distributed state

Implementing undo and redo on top of CRDTs is significantly more complex than in centralized systems. Counter-based undo approaches fail when operations are concurrently undone across multiple replicas. Global undo/redo (affecting all users) violates user expectations in most collaborative scenarios. Local undo/redo (affecting only the current user's operations) is the pragmatic choice, but introduces semantic complexity: CRDT-stable cursor positions can lock onto deleted elements rather than tracking newly created elements after a redo. The non-linear operation history created by concurrent edits makes correct undo/redo semantics and implementation exceptionally challenging.

Vector clock scaling

Vector clocks — the common mechanism for tracking causality in operation-based CRDTs — scale poorly as the number of replicas grows. In an n-node system, vector clock size grows linearly with node count. An attempted optimization (delta vector clocks) introduced O(n²) memory overhead. For applications with thousands of concurrent clients, this metadata bloat becomes a practical production constraint on storage and bandwidth.

Compaction and late-joining clients

Production CRDT systems must garbage-collect history. Aggressive compaction — creating snapshots and discarding history older than a retention window — can reduce file sizes by up to 90% when a document accumulates millions of tombstones. The trade-off: a client that reconnects after the retention window cannot observe the full causal history and must perform a full resync. Snapshot frequency and retention period become operational decisions that affect both storage costs and user experience for intermittently connected clients.

Library performance variance

Choosing a CRDT library is not purely a features decision — performance differences between implementations can be enormous. Automerge has been measured taking nearly 5 minutes to process certain editing traces where an optimized implementation handles the same trace in 56 milliseconds — a 5000x gap. Yjs dominates sequential character insertion benchmarks; Automerge performs better for concurrent operations and map structures. The right library depends on your specific workload pattern, not on general reputation.

Fig 1