Why Local-First Apps Haven’t Become Popular?

Offline-first apps promise instant loading and privacy, but in practice, very few apps get offline support because getting sync right is surprisingly hard.

Offline-first apps sound like the future: instant loading, privacy by default, and no more spinning loaders on flaky connections.

But in practice, very few apps get offline support right. Most simply queue changes locally and push them when the network comes back (spoiler: this doesn’t really work). Eventually, users see a scary banner saying “changes may not be saved.”

The reason is simple: syncing is hard.

When you build a local-first app, you’ve effectively created a distributed system. Multiple devices can mutate data independently, sometimes offline, and later must converge to the exact same state without losing data.

There are two main challenges to solve:

1. Unreliable ordering

2. Conflicts

Let’s go through them.

---

1. Unreliable Ordering

In a distributed environment, events happen on multiple devices at different times. If you just apply them as they arrive, you get inconsistent results.

Example:

• Device A sets x = 3

• Device B sets x = 5

Both happen while offline.

When the devices sync, the final value of x depends on which update is applied first. That’s a problem.

Traditional backend databases solve this with strong consistency, but that requires global coordination—too slow and brittle for local-first systems.

Instead, we want eventual consistency: every device applies changes independently but eventually converges to the same result once all changes are known.

The challenge is figuring out the correct order of events without relying on a centralized clock (because the network might be down).

The Solution: Hybrid Logical Clocks (HLCs)

Surprisingly, there’s a simple solution to this seemingly hard problem: Hybrid Logical Clocks (HLCs).

HLCs generate timestamps that are:

• Comparable (can be sorted lexicographically)

• Causally consistent (encode the order in which events happened)

HLCs combine two pieces of information:

• Physical time (from the machine clock)

• Logical time (a counter that increments when clocks are out of sync or events happen too close together)

In plain terms, HLCs let devices agree on “what happened first” without perfectly synchronized clocks.

A Quick Example

Imagine two machines, A and B:

1. Machine A records an event at 10:00:00.100.
   → Its HLC becomes (10:00:00.100, 0) (time + counter).

2. A sends a message to B with this HLC.

3. Machine B’s clock shows 10:00:00.095 (slightly behind).
   When B receives the message, it advances its HLC to at least match A’s timestamp.

4. B’s HLC becomes (10:00:00.100, 1) — the counter increments to indicate this happened after A’s event.

Result:

• Event on A: (10:00:00.100, 0)

• Event on B: (10:00:00.100, 1)

Even though B’s physical clock was behind, we can now order events consistently across machines.

---

2. Conflicts

Even with proper ordering, conflicts still happen when two devices modify the same data independently.

Example:

• Initial balance = 100

• Device A: +20 → balance = 120

• Device B: -20 → balance = 80

When they sync, which value should “win”?
If you naively apply both updates, one overwrites the other—losing user data.

Most systems ask developers to write manual conflict resolution code, but that’s error-prone and hard to maintain.

The Solution: CRDTs

The right approach is CRDTs (Conflict-Free Replicated Data Types).

CRDTs guarantee two important properties:

• Commutativity: Order of application doesn’t matter

• Idempotence: Applying the same change twice has no effect

This means you can apply messages in any order, even multiple times, and every device will still converge to the same state.

One of the simplest CRDT strategies is Last-Write-Wins (LWW):

• Each update gets a timestamp (physical or logical).

• When two devices write to the same field, the update with the latest timestamp wins.

Example:

• Device A: balance = 120 at 10:00:00

• Device B: balance = 80 at 10:00:02

When syncing, the system keeps 80 because it was written last.

---

Why SQLite Is Perfect for This

When building a local-first app, you need a rock-solid local database. SQLite is the obvious choice: battle-tested, lightweight, and available everywhere.

That’s why we built our local-first framework as a SQLite extension.

Our approach (simplified):

• Every change is stored as a message in a messages table with:

  • timestamp (from HLC)

  • dataset (table name)

  • row (encoded primary keys)

  • column

  • value

Applying a message is as simple as:

1. Look up the current value

2. If the incoming timestamp is newer → overwrite

3. If it’s older → ignore

This guarantees convergence across devices, regardless of the sync order.

---

Why This Matters

This architecture makes syncing simple and reliable:

• Reliable: Survives weeks of offline use without data loss

• Deterministic: Final state always converges

• Minimal: Just a small SQLite extension, no heavy dependencies

• Cross-platform: The extension is available for iOS, Android, macOS, Windows, Linux, and WASM

---

Takeaways for Developers

• Stop faking offline support with request queues

• Embrace eventual consistency

• Use proven distributed-systems techniques like HLCs and CRDTs

• Keep it small and dependency-free

The result? Apps that are instant, offline-capable, and private by default — without the complexity of traditional client–server synchronization.

---

I’m the founder of SQLite AI, our mission is to make local-first sync with SQLite and AI on the Edge straightforward and reliable. If you’d like to experiment with a production-ready sync engine, the same kind of technology discussed in this article, you can explore it at: https://www.sqlite.ai/sqlite-sync