Local-First Sync Engines: CRDTs, SQLite and Where the Write Boundary Goes

Every offline-first project starts with the same sentence — "we'll just cache the data locally and sync when we're back online" — and every one of them discovers the same thing about three months later: that sentence contains a distributed systems problem, and it has moved onto a device you do not control, that goes offline on purpose, and whose clock is wrong.

The good news is that 2026 is the first year where the tooling is genuinely production-ready. The bad news is that choosing between the options requires you to answer a question most teams have never articulated.

The question: where do writes become authoritative?

Not "which CRDT library". Not "SQLite or IndexedDB". This:

When a user changes something on their device, at what point is that change the truth?

There are three answers, and they produce three completely different architectures.

1. The server decides (offline queue)

Local writes are optimistic. They render immediately and queue in an outbox. The server processes the queue in order and its answer is final; on conflict, the client's version loses and the UI rolls back.

• Simple. Your existing API still works. One source of truth.
• Rollback is visible to the user — the thing they typed reverts.
• Long offline periods accumulate conflicts that a rollback cannot express sensibly.

Right for transactional data: orders, payments, bookings, anything where "the server said no" is a legitimate and expected outcome.

2. Nobody decides (CRDTs)

Every replica converges on the same state by construction, without a coordinator, because the merge operation is commutative, associative and idempotent. There is no conflict because the data type cannot represent one.

• Genuinely offline-capable. Peer-to-peer is possible. Merges never fail.
• The merge is automatic, which means it is not what you would have chosen in some fraction of cases.
• Metadata grows with edit history, not just with data size.

Right for collaborative documents: text, drawings, boards, anything where two people editing different parts should both keep their work.

3. The server decides, but tells you first (sync engines)

A subset of server state is streamed to a local database (usually SQLite) and kept live. Reads are local and instant; writes go through a defined path with server-side rules.

• Reads are a local query, so the UI has no loading states.
• Your existing Postgres remains the source of truth.
• You must define which subset each client gets, and that partition is a real design problem.

Right for line-of-business apps: dashboards, catalogues, field tools — mostly-read applications with bounded write surfaces.

What a CRDT actually costs

CRDTs are the most interesting and the most oversold of the three, so it is worth being precise about the trade.

A counter is the easy case. Each replica keeps its own count; merging sums them. Increment on two devices, get both increments, no coordination.

Text is the hard case, and it is why the libraries exist. Insert at "position 4" is meaningless once someone else has inserted at position 2. So a sequence CRDT gives every character a stable identity with a total order, and "position" becomes "after this identity". That works — and it means the document carries an identifier for every character ever typed, including deleted ones (tombstones).

That is the cost, and it is the real one. A document with a long editing history carries metadata proportional to the history, not to the visible content. This was the practical blocker for years, and it is the thing that changed: Automerge 3.0 (2025) cut memory use roughly tenfold with a Rust core behind a stable JavaScript API, which moved large documents in a browser from "theoretically fine" to "actually fine".

The state of the ecosystem as it stands:

The conflict CRDTs cannot solve

This is the part that sales pages skip, and the part a senior engineer must say out loud.

Two people edit the same booking offline. One changes the date to Tuesday; the other changes it to Wednesday. A CRDT merges this without error and produces one of them, deterministically, by whatever tie-break the type uses.

No conflict is raised. No user is told. One person's change is silently gone.

The merge is conflict-free, not correct. "Conflict-free" means the algorithm always terminates with a consistent answer on every replica — it makes no claim that the answer preserves intent. For a shared document that is usually fine, because people edit different paragraphs. For a single-value field with business meaning it is a silent data-loss bug wearing a distributed systems hat.

So: CRDTs for the parts of your data where automatic merge is semantically acceptable, and explicit conflict resolution for the parts where it is not. Most real apps need both, in different tables.

The sync engines

PowerSync is the most broadly production-ready option if you already have a database. It watches Postgres, MongoDB or MySQL, streams a defined subset to SQLite on the client, and routes writes back through your own API. You keep your backend, your auth and your business rules; you gain local reads and an offline queue that someone else maintains.

ElectricSQL narrowed its scope in 2024 and is better for it. Rather than a full bidirectional CRDT layer, it streams Postgres tables to clients as live "shapes" — a query whose results stay current — and leaves writes to you. Less magic, far less to go wrong, and a much smaller thing to reason about when it misbehaves.

The distinction that matters between them is the one from the top of this article. They put the write boundary in different places, and that placement is the decision — not the feature list.

The partition problem

Whichever you pick, you must answer: which rows does this client get?

Get it wrong in the permissive direction and a phone tries to sync a table with four million rows. Get it wrong in the restrictive direction and a feature silently has no data offline. And the partition is usually dynamic — "the projects I am a member of" changes when someone adds me to a project, which means the sync set has to change underneath a running client.

Budget real design time for this. It is the part that takes longest and it is not in any quickstart.

A pattern that works without any of it

You do not always need a sync engine. For a mostly-read app with a narrow write surface — which is most apps — an outbox and idempotency keys get you most of the value in an afternoon.

type QueuedMutation = {
  id: string;
  idempotencyKey: string;
  endpoint: string;
  payload: unknown;
  attempts: number;
  queuedAt: number;
};

export async function enqueue(db: Database, endpoint: string, payload: unknown): Promise<void> {
  await db.execute(
    `INSERT INTO outbox (id, idempotency_key, endpoint, payload, attempts, queued_at)
     VALUES (?, ?, ?, ?, 0, ?)`,
    [crypto.randomUUID(), crypto.randomUUID(), endpoint, JSON.stringify(payload), Date.now()],
  );
}

export async function drain(db: Database, send: Sender): Promise<void> {
  // Ordered. A partial drain must leave the queue in a replayable state.
  const pending = await db.getAll<QueuedMutation>(
    `SELECT * FROM outbox ORDER BY queued_at ASC LIMIT 50`,
  );

  for (const mutation of pending) {
    try {
      await send(mutation.endpoint, mutation.payload, {
        // Generated once, at enqueue time, and reused across every retry.
        // This is what makes "the response was lost" safe.
        "Idempotency-Key": mutation.idempotencyKey,
      });
      await db.execute(`DELETE FROM outbox WHERE id = ?`, [mutation.id]);
    } catch (error) {
      if (isPermanent(error)) {
        await moveToDeadLetter(db, mutation, error);
        continue;
      }
      // Transient: stop draining. Order matters more than throughput.
      await db.execute(`UPDATE outbox SET attempts = attempts + 1 WHERE id = ?`, [mutation.id]);
      return;
    }
  }
}

Four properties are doing the work here:

1. The idempotency key is generated at enqueue time, not per attempt. Regenerating it per retry defeats the entire mechanism — which is the single most common implementation bug in this pattern.
2. Draining stops at the first transient failure. Skipping ahead reorders operations, and a delete that overtakes its create is unrecoverable.
3. Permanent failures go to a dead-letter table, not back on the queue. A 400 will never succeed on retry, and a queue that retries it forever never drains.
4. The UI reads local state, never the queue's status. The user should not know or care whether a write has landed.

Choosing, in four questions

1. Can two users edit the same record while offline? No → outbox. Yes → keep going.
2. When they do, is automatic merge acceptable? For prose, yes → CRDT. For a price, a date, a status → explicit resolution, which means version vectors and a UI that asks.
3. How much data does a client need offline? Bounded and small → sync everything. Large or dynamic → a sync engine with real partitioning.
4. How long is the longest realistic offline period? Minutes → almost anything works. Days → you need reconciliation, conflict UI and a migration story for schema changes that happened while the client was away.

That last one is the question nobody asks, and it is the one that determines whether the app survives contact with a field engineer who spent a week without signal.

The honest summary

Local-first is not offline mode. It is a different consistency model, chosen deliberately, with different failure modes — and it is worth it for the apps where instant local reads and genuine offline capability are the product rather than a feature.

Just do not adopt a CRDT because merges never fail. Merges never failing is the property that hides the data loss.

Sources: Automerge, Yjs, ElectricSQL, PowerSync