Designing an Offline Mutation Queue on SQLite

A user opens Storyie on the subway, writes a diary entry, edits it twice, and locks the phone. There is no network the entire time. When they surface an hour later, all three changes have to reach the server — in the right order, exactly once, even if the app was killed in between. The moment you promise "your writing is saved" while offline, you have signed up to keep that promise across a force-quit.

The naive version of this is an in-memory array of pending requests that a timer drains when the network returns. It works in a demo and loses data in the field: the array dies with the process. On mobile the OS reclaims backgrounded apps aggressively, and a force-quit is a normal user gesture, not an edge case. Anything the app "remembers" only in RAM is a change it will eventually drop.

So Storyie's offline mutation queue is a table, not a list. Every pending create, update, and delete is a row in a local SQLite database (op-sqlite + Drizzle) on the device. This post walks through that design: the durable status state machine, the contract adapter that let us move storage without a rewrite, the ownership rule that protects unsynced edits, the retry schedule, and how we keep the queue from ballooning.

TL;DR

• The queue is a SQLite table (local_sync_queue), not an in-memory array, so pending mutations survive a force-quit, a crash, and a cold start.
• Each row is a small state machine: pending → syncing → completed, or → failed → (backoff) → pending again. Status, retryCount, and nextRetryAt all live in the row.
• Retries use exponential backoff with a ceiling — a fixed [5s, 15s, 45s, 135s, 405s] delay table and MAX_RETRIES = 5. A manual retry resets the counter.
• A queued mutation means "I own this entity." hasPendingSyncForEntity() blocks a stale server record from clobbering a local edit or an offline delete — the last-write-wins guard.
• Consolidation keeps the queue small: only the newest pending write per entity is flushed; superseded ones are deleted.
• A rowToItem() adapter kept the whole app oblivious to the KV-store → SQLite migration.

A pending mutation is a row, and its status is a state machine

The schema is deliberately boring. Everything the sync loop needs to make a decision is a column, so nothing important lives only in memory:

// apps/expo/lib/db/schema.ts (excerpt)
export const syncQueueStatuses = ["pending", "syncing", "failed", "completed"] as const;
export const syncQueueOperations = ["create", "update", "delete"] as const;

export const localSyncQueue = sqliteTable(
  "local_sync_queue",
  {
    id: primaryId(),
    entityType: text("entity_type", { enum: syncQueueEntityTypes }).notNull(),
    entityId: text("entity_id").notNull(),
    operation: text("operation", { enum: syncQueueOperations }).notNull(),
    payload: safeJsonText("payload").$type<Record<string, unknown>>(),
    status: text("status", { enum: syncQueueStatuses }).notNull().default("pending"),
    retryCount: integer("retry_count").notNull().default(0),
    nextRetryAt: timestampMs("next_retry_at"),
    errorMessage: text("error_message"),
    createdAt: createdAt(),
    updatedAt: updatedAt(),
  },
  (t) => [
    index("local_sync_queue_status_created_idx").on(t.status, t.createdAt),
    index("local_sync_queue_entity_idx").on(t.entityType, t.entityId),
  ]
);

A row moves through four states. It is born pending. The sync processor marks it syncing while the request is in flight, then completed on success (and later reaps it). On failure it becomes failed — and, if it still has retries left, is immediately reset to pending with a future nextRetryAt. That "failed then back to pending" transition is the whole trick: a stuck row is never a special case the drain has to look for, it just becomes a pending row that isn't eligible yet.

The (status, createdAt) index is not decorative. The drain query asks for exactly one thing — the oldest work that is due right now — and that composite index makes it a range scan instead of a full-table filter:

// apps/expo/lib/db/queries/sync-queue.ts (excerpt)
// status = 'pending' AND (nextRetryAt IS NULL OR nextRetryAt <= now), oldest first
export async function getPendingSyncQueueRows(): Promise<LocalSyncQueueRow[]> {
  const db = await getLocalDb();
  const now = new Date();
  return db
    .select()
    .from(localSyncQueue)
    .where(
      and(
        eq(localSyncQueue.status, "pending"),
        or(isNull(localSyncQueue.nextRetryAt), lte(localSyncQueue.nextRetryAt, now))
      )
    )
    .orderBy(asc(localSyncQueue.createdAt));
}

Ordering by createdAt gives FIFO flushing, which matters when a create and a later update for the same entry are both waiting: the create has to land first or the update has nothing to update.

The contract adapter that hid a storage migration

The queue didn't start on SQLite. An earlier version persisted to the key-value store, and the rest of the app — mutation hooks, the sync service, the little "3 pending" indicator — was written against a SyncQueueItem shape with ISO-string timestamps and a timestamp field. When we moved storage to SQLite (epic #777, the on-device op-sqlite store), the row shape no longer matched: the table stores Date objects and has an updatedAt column instead.

Rather than touch every caller, we put one adapter at the boundary:

// apps/expo/services/syncQueueService.ts (excerpt)
// The on-device table stores timestamps as Date objects and an updatedAt column;
// the legacy SyncQueueItem contract uses ISO strings and a `timestamp` field.
// We bridge the two here so the rest of the app is unaffected by the move
// from KV-store to SQLite.
function rowToItem(row: LocalSyncQueueRow): SyncQueueItem {
  return {
    id: row.id,
    entityType: row.entityType,
    entityId: row.entityId,
    operation: row.operation,
    payload: (row.payload ?? { id: row.entityId }) as SyncQueueItem["payload"],
    timestamp: row.updatedAt.toISOString(),
    retryCount: row.retryCount,
    status: row.status,
    errorMessage: row.errorMessage ?? null,
    createdAt: row.createdAt.toISOString(),
    nextRetryAt: row.nextRetryAt ? row.nextRetryAt.toISOString() : null,
  };
}

It looks trivial, and that is the point. Every public read (getSyncQueue, getPendingSyncItems, getQueueItemsForEntity) runs its rows through rowToItem, so the storage engine underneath became an implementation detail. The migration changed one file's internals and left the consumers untouched — the same "seam at the boundary" discipline we used when migrating authentication without rewriting every caller.

A queued mutation means "I own this entity"

Here is the failure a local-first app has to design against. You edit a diary offline. Seconds later a background refresh for the same screen completes and hands back the server's older copy. If the read path naively writes that server record into the local store, it has just erased your unsynced edit with stale data — a data-loss bug that only reproduces when timing lines up.

The queue is the source of truth for who owns a record. If a mutation for an entity is still pending or in flight, the local copy wins, full stop:

// apps/expo/lib/db/queries/sync-queue.ts (excerpt)
// While a local change is queued, the local row is authoritative and a stale
// server record must not clobber it (#786) — this covers both offline edits
// and offline deletes (a tombstone whose delete op is still queued).
export async function hasPendingSyncForEntity(
  entityType: SyncQueueEntityType,
  entityId: string
): Promise<boolean> {
  const rows = await getSyncQueueRowsByEntity(entityType, entityId);
  return rows.some((r) => r.status === "pending" || r.status === "syncing");
}

The delete case is the subtle one. An offline delete is a tombstone — the row is gone locally, but a delete op sits in the queue waiting to reach the server. Until it does, a refresh that returns the not-yet-deleted server record must not resurrect it. Gating on pending || syncing covers edits and deletes with the same rule, and it is the mechanism behind our broader stance that last-write-wins is a product decision, not an accident of whichever write happened to run last.

Retry is a schedule, not a spin loop

When a flush fails — server down, request rejected, connection dropped mid-way — retrying immediately just burns battery and hammers the API. Storyie schedules the next attempt with exponential backoff, and both the schedule and the give-up point are data, not control flow:

// apps/expo/constants/storageKeys.ts (excerpt)
export const RETRY_DELAYS = [5000, 15000, 45000, 135000, 405000] as const;
export const MAX_RETRIES = 5;

On failure, the service bumps the retry count, computes nextRetryAt from the delay table, and — crucially — flips the row back to pending so the normal drain picks it up once the window passes:

// apps/expo/services/syncQueueService.ts (excerpt)
if (item && item.retryCount < MAX_RETRIES) {
  const newRetryCount = item.retryCount + 1;
  const nextRetryTime = calculateNextRetryTime(newRetryCount);

  updates.retryCount = newRetryCount;
  updates.nextRetryAt = new Date(nextRetryTime);
  updates.status = "pending"; // Reset to pending for retry
} else {
  console.error(`[SyncQueue] Item ${id} exceeded max retries (${MAX_RETRIES})`);
}

calculateNextRetryTime clamps the index into the delay table, so once a row exhausts the five entries it stops escalating and, past MAX_RETRIES, stays failed for a human to deal with instead of retrying forever:

// apps/expo/services/syncQueueService.ts (excerpt)
export function calculateNextRetryTime(retryCount: number): string {
  const clampedRetry = Math.min(Math.max(retryCount - 1, 0), RETRY_DELAYS.length - 1);
  const delayMs = RETRY_DELAYS[clampedRetry];
  const nextTime = new Date(Date.now() + delayMs);
  return nextTime.toISOString();
}

A manual "Retry" from the UI is treated differently from an automatic one. retryFailedItemsNow() resets retryCount to 0, because a user tapping Retry is a fresh intent and should get the full backoff budget again — not inherit the exhausted counter of the automatic attempts that preceded it. Automatic backoff protects the server; manual retry serves the user, and conflating the two would make the button feel broken.

Keeping the queue from ballooning

Autosave fires often. If a user edits one diary entry ten times offline, ten raw writes in the queue would mean ten network round-trips that all end at the same final state — wasteful, and slower to drain. Only the last write actually matters.

Storyie collapses redundant work in two places. For the entity-level draft queue, writes upsert a single logical mutation per entity, keyed on entity_type + entity_id, so re-editing overwrites the pending entry in place rather than appending. And consolidateQueue() sweeps the row queue, keeping only the most recent pending mutation per entity and deleting the superseded ones:

// apps/expo/services/syncQueueService.ts (excerpt)
// Keep only the most recent pending operation for each entity; delete the
// superseded pending rows. Non-pending rows (syncing/failed/completed) are
// always preserved.
for (const [, items] of entityMap) {
  const pending = items.filter((i) => i.status === "pending");
  if (pending.length <= 1) continue;

  // Newest first — keep [0], delete the rest.
  pending.sort((a, b) => new Date(b.timestamp).getTime() - new Date(a.timestamp).getTime());
  for (const stale of pending.slice(1)) {
    idsToDelete.push(stale.id);
  }
}

The important restraint is what it won't touch: only pending rows are candidates. A row that is syncing might already be on the wire, and a failed row carries retry history worth preserving, so consolidation leaves both alone and only prunes the redundant not-yet-started writes. After that, the pending count reflects distinct dirty entities rather than keystroke volume — which is what the "N pending" indicator should have meant all along.

Takeaways

• Durability first. If an offline mutation can be lost by killing the app, it will be. Persist the queue to disk — a SQLite table with the flush order, retry count, and next-retry time as columns — before worrying about anything else.
• Model state as a column, not control flow. Making "failed with retries left" resolve back to pending means the drain has one job (find due pending rows) and no special cases.
• Let the queue arbitrate ownership. "Is a mutation queued for this entity?" is a clean, reusable answer to "may a server record overwrite the local copy?" — for edits and deletes alike.
• Separate machine backoff from human intent. Exponential backoff with a ceiling protects your API; a manual retry resets the budget because it means something different.

The queue is one half of Storyie's local-first story. For the write path that feeds it, see local-first autosave without losing the last keystroke; for how deletes stay reversible while their tombstones drain, see soft deletes in a local-first mobile database.