Design an Offline Write Queue

An offline queue persists user intent before the network request. It must be visible, retryable, and idempotent.

Minimal Table

CREATE TABLE pending_mutations (
  id TEXT PRIMARY KEY,
  operation TEXT NOT NULL,
  entity_type TEXT NOT NULL,
  entity_id TEXT NOT NULL,
  payload_json TEXT NOT NULL,
  idempotency_key TEXT NOT NULL UNIQUE,
  base_version INTEGER,
  attempt_count INTEGER NOT NULL DEFAULT 0,
  next_attempt_at TEXT,
  last_error TEXT,
  created_at TEXT NOT NULL
);

Write Path

async function saveExpense(input: ExpenseInput) {
  const id = crypto.randomUUID();
  const key = `expense:create:${id}`;

  await db.transaction(async (tx) => {
    await tx.expenses.insert({ id, ...input, syncState: "pending" });
    await tx.pendingMutations.insert({
      id: crypto.randomUUID(),
      operation: "create",
      entityType: "expense",
      entityId: id,
      payloadJson: JSON.stringify(input),
      idempotencyKey: key,
      attemptCount: 0,
      createdAt: new Date().toISOString(),
    });
  });
}

Retry Rules

• retry network errors with backoff
• stop and ask user on validation conflicts
• refresh auth before replaying private mutations
• never generate a new idempotency key for the same queued action
• record last_error for debugging and UI copy

UI Rules

• show pending count
• show per-record pending/failed state
• let users retry all failed actions
• prevent duplicate taps from creating duplicate mutations
• keep local edits safe even when sync fails