Offline-First Web Apps: Synchronizing IndexedDB with PostgreSQL

Why Real-World Apps Need Local-First Architecture

Most web apps treat the cloud database as the immediate gatekeeper for every single user click. When a user clicks a button, the app shows a spinner, waits 200 to 800 milliseconds for a roundtrip network request, and blocks interaction until the server responds. If the network drops or the connection is flaky, the app crashes or shows an error dialog.

This request-response pattern fails in operational environments like warehouses, retail counters, and busy dining rooms. When building [Restaurant OS / Corner Cafe](/projects/4), waitstaff and kitchen staff could not afford to lose order tickets when restaurant Wi-Fi dipped during rush hours. The solution was an offline-first architecture: write immediately to browser IndexedDB, update the UI with zero delay, and sync mutations to cloud PostgreSQL through a reliable background queue.

The Client Mutation Queue Pattern

To keep the interface responsive without losing data, we separate local state writes from network requests. Every write operation generates two records inside IndexedDB: the entity itself in a local table, and a sync task inside an outbox queue table.

import Dexie, { type Table } from 'dexie';

interface OrderItem {
  id: string;
  name: string;
  quantity: number;
  price: number;
}

interface LocalOrder {
  id: string;
  tableNumber: number;
  items: OrderItem[];
  status: 'pending' | 'synced' | 'failed';
  clientUpdatedAt: number;
}

interface SyncQueueItem {
  id: string;
  entity: 'orders';
  action: 'INSERT' | 'UPDATE' | 'DELETE';
  payload: Record<string, unknown>;
  createdAt: number;
  attempts: number;
}

class AppDatabase extends Dexie {
  orders!: Table<LocalOrder, string>;
  syncQueue!: Table<SyncQueueItem, string>;

  constructor() {
    super('RestaurantDB');
    this.version(1).stores({
      orders: 'id, tableNumber, status, clientUpdatedAt',
      syncQueue: 'id, entity, createdAt, attempts'
    });
  }
}

export const db = new AppDatabase();

export async function createOrderOptimistic(orderData: Omit<LocalOrder, 'status' | 'clientUpdatedAt'>) {
  const now = Date.now();
  const newOrder: LocalOrder = {
    ...orderData,
    status: 'pending',
    clientUpdatedAt: now
  };

  const queueTask: SyncQueueItem = {
    id: crypto.randomUUID(),
    entity: 'orders',
    action: 'INSERT',
    payload: newOrder as unknown as Record<string, unknown>,
    createdAt: now,
    attempts: 0
  };

  // Atomic transaction ensures outbox item exists if local record exists
  await db.transaction('rw', [db.orders, db.syncQueue], async () => {
    await db.orders.put(newOrder);
    await db.syncQueue.put(queueTask);
  });

  return newOrder;
}

Idempotent Server Ingestion with PostgreSQL

When the client reconnects, the background worker replays queued tasks. Because mobile networks drop packets intermittently, the server might receive the same mutation multiple times. The backend ingestion endpoint must be completely idempotent.

We handle this in PostgreSQL by using UUIDs generated on the client, checking transaction version timestamps, and applying ON CONFLICT clauses to prevent duplicate inserts or out-of-order overwrites.

-- Schema definition
CREATE TABLE IF NOT EXISTS orders (
  id UUID PRIMARY KEY,
  table_number INT NOT NULL,
  items JSONB NOT NULL DEFAULT '[]'::jsonb,
  status VARCHAR(32) NOT NULL DEFAULT 'received',
  client_updated_at BIGINT NOT NULL,
  server_updated_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

-- Batch ingestion statement with timestamp safety
INSERT INTO orders (id, table_number, items, status, client_updated_at, server_updated_at)
VALUES ($1, $2, $3, $4, $5, NOW())
ON CONFLICT (id) DO UPDATE
SET
  table_number = EXCLUDED.table_number,
  items = EXCLUDED.items,
  status = EXCLUDED.status,
  client_updated_at = EXCLUDED.client_updated_at,
  server_updated_at = NOW()
WHERE orders.client_updated_at < EXCLUDED.client_updated_at;
-- Stale writes arriving out of sequence are ignored without throwing an error

Sync Worker Lifecycle and Network Reconnection

React components should not manage network listeners directly. Instead, a dedicated sync engine handles synchronization using three triggers: immediate execution on mutation creation, the browser window online event, and an exponential backoff timer for failed requests.

• Client writes directly to IndexedDB in less than 5 milliseconds, immediately reflecting changes on screen.
• The background runner reads the outbox table sorted by createdAt ascending to preserve causal ordering.
• Payloads are posted in batches of 10 to 50 items to minimize HTTP connection overhead.
• On HTTP 200, the runner marks the local order status as synced and deletes the queue item.
• On HTTP 5xx or network drop, the attempts counter increments and retries use an exponential backoff.

Need Help Implementing This in Your Product?

Building dependable offline-first web applications requires deep care around state management, edge cases, and database constraints. Whether you are building an operational dashboard, inventory tool, or POS system, I can help design and build a clean solution.

Take a look at my [Full-Stack Web Development Services](/services) or [get in touch directly](/contact) to discuss your product requirements.