Build an offline-first sync engine inspired by Linear

Problem

Most web apps fail when the network drops -- forms lose data, actions queue silently, and users see spinners instead of content. Building offline-first applications with optimistic updates and real-time sync requires solving hard problems: conflict resolution, operation ordering, and state reconciliation. Linear's app is the gold standard for this, feeling instant regardless of network conditions.

Solution

Core architecture: Client SQLite + operation log + server reconciliation

Client                          Server
┌─────────────┐                ┌─────────────┐
│ SQLite DB   │◄──sync────────►│ PostgreSQL   │
│ Op Log      │                │ Op Log       │
│ React State │                │ WebSocket    │
└─────────────┘                └─────────────┘

Step 1: Client-side SQLite with optimistic writes

import { SQLocalDrizzle } from "sqlocal/drizzle";
import { drizzle } from "drizzle-orm/sqlite-proxy";

// Local SQLite in the browser via OPFS
const { driver } = new SQLocalDrizzle("app.db");
const db = drizzle(driver);

// Every mutation writes locally first, then syncs
async function updateIssue(id: string, data: Partial<Issue>) {
  const op: Operation = {
    id: crypto.randomUUID(),
    type: "update_issue",
    entityId: id,
    data,
    timestamp: Date.now(),
    clientId: getClientId(),
    synced: false,
  };

  // 1. Apply optimistically to local SQLite
  await db.update(issues).set(data).where(eq(issues.id, id));

  // 2. Append to operation log
  await db.insert(operations).values(op);

  // 3. Attempt to sync (non-blocking)
  syncQueue.push(op);
}

Step 2: Operation log and sync queue

interface Operation {
  id: string;
  type: string;
  entityId: string;
  data: Record<string, unknown>;
  timestamp: number;
  clientId: string;
  synced: boolean;
}

class SyncQueue {
  private ws: WebSocket;
  private pending: Operation[] = [];

  async push(op: Operation) {
    this.pending.push(op);
    if (this.ws.readyState === WebSocket.OPEN) {
      await this.flush();
    }
  }

  async flush() {
    const unsynced = this.pending.filter((op) => !op.synced);
    if (unsynced.length === 0) return;

    this.ws.send(JSON.stringify({ type: "sync", operations: unsynced }));
  }

  handleServerResponse(response: SyncResponse) {
    // Mark operations as synced
    for (const id of response.acknowledged) {
      const op = this.pending.find((o) => o.id === id);
      if (op) op.synced = true;
    }

    // Apply server-side changes we don't have locally
    for (const op of response.newOperations) {
      applyOperation(op);
    }
  }
}

Step 3: Server-side reconciliation

// Server receives operations and resolves conflicts with last-write-wins
function reconcile(clientOps: Operation[], serverOps: Operation[]): Operation[] {
  const merged = [...clientOps, ...serverOps].sort((a, b) => a.timestamp - b.timestamp);

  // Group by entity and apply in order
  const byEntity = new Map<string, Operation[]>();
  for (const op of merged) {
    const existing = byEntity.get(op.entityId) ?? [];
    existing.push(op);
    byEntity.set(op.entityId, existing);
  }

  // Return operations the client hasn't seen
  return merged.filter((op) => op.clientId !== clientOps[0]?.clientId);
}

Why It Works

By writing to local SQLite first and syncing asynchronously, every user action is instant regardless of network conditions. The operation log acts as an append-only event source that can be replayed to reconstruct state. Last-write-wins conflict resolution is simple to implement and correct for most collaborative use cases. The WebSocket connection handles real-time sync when online, while the pending queue ensures no operations are lost when offline.

Context

Linear's sync engine uses a similar operation-log approach, which is why their app feels instant
sqlocal uses OPFS (Origin Private File System) for persistent SQLite in the browser
For more sophisticated conflict resolution, consider CRDTs (Yjs, Automerge) instead of last-write-wins
The operation log doubles as an audit trail and enables undo/redo for free
Consider using wa-sqlite or sql.js as alternatives to sqlocal for browser SQLite
This pattern pairs well with service workers for full offline capability