Offline-first architecture with SQLite and sync-on-reconnect

1. The case for offline-first

Most web and desktop apps are built cloud-first: every action makes a network request, and if that request fails, so does the action. This is fine until the user is on a plane, in a building with poor signal, or the API is having an outage. Then everything breaks — often loudly.

Offline-first inverts this. The local database is the source of truth. Actions mutate local state immediately, the UI reflects them instantly, and synchronisation to the server is a background concern. The user experience is faster (no waiting for round trips) and more resilient (works regardless of connectivity).

It's also a privacy win. For apps that handle sensitive data, storing it locally by default and syncing only what's necessary is better data hygiene than routing everything through a cloud database.

2. SQLite as the local source of truth

SQLite is the right choice for most offline-first desktop and Electron apps. It's embedded, zero-configuration, battle-tested, and the file format is stable across decades. For Node.js/Electron apps, better-sqlite3 is the recommended binding — it's synchronous, which simplifies code considerably in the main process.

import Database from 'better-sqlite3';
import path from 'path';
import { app } from 'electron';

const dbPath = path.join(app.getPath('userData'), 'app.db');
const db = new Database(dbPath);

// Enable WAL mode for better concurrent read performance
db.pragma('journal_mode = WAL');
db.pragma('foreign_keys = ON');

WAL (Write-Ahead Logging) mode is worth enabling on day one. It allows reads to proceed concurrently with a write, which matters as soon as you have background sync operations running alongside UI reads.

For schema migrations, use a simple version table and run migrations in sequence on startup:

db.exec(`
  CREATE TABLE IF NOT EXISTS _migrations (
    version INTEGER PRIMARY KEY,
    applied_at TEXT NOT NULL
  )
`);

const currentVersion = db.prepare(
  'SELECT MAX(version) as v FROM _migrations'
).get() as { v: number | null };

if ((currentVersion.v ?? 0) < 1) {
  db.exec(`
    CREATE TABLE invoices (
      id TEXT PRIMARY KEY,
      vendor_name TEXT,
      total REAL,
      synced INTEGER DEFAULT 0,
      updated_at TEXT NOT NULL
    )
  `);
  db.prepare('INSERT INTO _migrations VALUES (1, ?)').run(new Date().toISOString());
}

3. Optimistic UI updates

The principle is simple: when the user takes an action, update the local database immediately and reflect the change in the UI. Queue the sync operation in the background. Don't wait for the server to confirm before showing feedback.

// In the Electron main process (IPC handler):
ipcMain.handle('invoice:save', (_event, invoice: Invoice) => {
  // 1. Write to local SQLite immediately
  db.prepare(`
    INSERT OR REPLACE INTO invoices (id, vendor_name, total, synced, updated_at)
    VALUES (@id, @vendor_name, @total, 0, @updated_at)
  `).run({ ...invoice, updated_at: new Date().toISOString() });

  // 2. Queue for background sync (fire and forget)
  syncQueue.push(invoice.id);

  // 3. Return success immediately — UI can update now
  return { success: true };
});

The synced column (0 = pending, 1 = confirmed) is the heart of the sync system. The UI can read this flag to show a pending indicator on items that haven't been confirmed by the server yet.

4. Conflict resolution strategy

Conflicts happen when the same record is modified both locally and on the server while the client is offline. There are two main strategies:

• Last-write-wins (LWW): the record with the latest updated_at timestamp wins. Simple to implement, handles 95% of real-world cases correctly. The risk is clock skew between client and server — mitigate by always using server time when the client syncs.
• CRDTs (Conflict-free Replicated Data Types): data structures designed to merge automatically without conflicts. Powerful but complex — appropriate for collaborative document editors, not invoice managers.

For most small apps, LWW is the right choice. Here's a simple implementation in the sync handler:

async function syncRecord(invoiceId: string) {
  const local = db.prepare('SELECT * FROM invoices WHERE id = ?').get(invoiceId);
  if (!local) return;

  try {
    const response = await fetch(`${API_BASE}/invoices/${invoiceId}`, {
      method: 'PUT',
      headers: { 'Content-Type': 'application/json' },
      body: JSON.stringify(local),
    });

    if (response.ok) {
      const serverRecord = await response.json();
      // Server returns the winning record — update local to match
      db.prepare(`
        UPDATE invoices SET synced = 1, updated_at = ? WHERE id = ?
      `).run(serverRecord.updated_at, invoiceId);
    }
  } catch {
    // Network error — leave synced = 0, retry on next reconnect
  }
}

5. Sync-on-reconnect

The sync trigger is the online event. In Electron's renderer process (or any browser context):

window.addEventListener('online', async () => {
  console.log('Connection restored — starting sync');
  await syncPendingRecords();
});

window.addEventListener('offline', () => {
  console.log('Connection lost — queuing changes locally');
});

navigator.onLine can also be polled, but event-driven is cleaner. Note that online only means a network interface is available — not that your API is reachable. Wrap your sync calls in try/catch and implement exponential backoff for failed requests:

async function syncPendingRecords() {
  const pending = db.prepare(
    'SELECT id FROM invoices WHERE synced = 0'
  ).all() as { id: string }[];

  for (const row of pending) {
    let delay = 1000;
    for (let attempt = 0; attempt < 3; attempt++) {
      try {
        await syncRecord(row.id);
        break; // success
      } catch {
        await new Promise(r => setTimeout(r, delay));
        delay *= 2; // exponential backoff
      }
    }
  }
}

Make all sync API endpoints idempotent — if the client sends the same record twice (e.g. due to a failed acknowledgement), the server should handle it gracefully. Use the record's UUID as a natural idempotency key. This is the contract that makes the retry logic safe.