React Native offline queue with SQLite (Expo)

• I ship offline-first writes with a SQLite “outbox” table.
• I retry safely with idempotency keys. No dup sets.
• I keep UI under 100ms by writing locally first.
• I sync in the background with a tiny state machine.

Context

I’m building a fitness app where logging a set should take ~5 seconds. That only works if the app doesn’t care about internet. Gyms have dead zones. Elevators kill LTE. And iOS loves suspending background work at the worst time.

I tried the naive approach first. “Just POST the set. If it fails, show a toast.” Brutal. I lost data. I created duplicates when users rage-tapped “Save” again. I also made the UI feel slow because I waited on the network.

So I switched to an outbox pattern in SQLite. Local write first. Always. Then a sync loop that can crash, resume, and retry without creating duplicate sets on the server.

1) I write locally first. Always.

Fast UI comes from one rule: the “Save set” button never waits on the network.

I keep two tables:

• sets is the source of truth for UI.
• outbox is a durable queue of network work.

The trick: one user action writes both rows in a single transaction.

// db.ts
import * as SQLite from 'expo-sqlite';

export const db = SQLite.openDatabaseSync('gym.db');

export function migrate() {
  // WAL helps concurrency + reduces "database is locked" pain.
  db.execSync(`PRAGMA journal_mode = WAL;`);
  db.execSync(`PRAGMA foreign_keys = ON;`);

  db.execSync(`
    CREATE TABLE IF NOT EXISTS sets (
      id TEXT PRIMARY KEY,
      workout_id TEXT NOT NULL,
      exercise_id TEXT NOT NULL,
      reps INTEGER NOT NULL,
      weight REAL NOT NULL,
      created_at INTEGER NOT NULL,
      updated_at INTEGER NOT NULL
    );
  `);

  db.execSync(`
    CREATE TABLE IF NOT EXISTS outbox (
      id TEXT PRIMARY KEY,
      type TEXT NOT NULL,
      payload_json TEXT NOT NULL,
      idempotency_key TEXT NOT NULL,
      attempt_count INTEGER NOT NULL DEFAULT 0,
      next_attempt_at INTEGER NOT NULL,
      last_error TEXT,
      created_at INTEGER NOT NULL
    );
  `);

  // Prevent accidental duplicates if I enqueue twice.
  db.execSync(`
    CREATE UNIQUE INDEX IF NOT EXISTS outbox_idem_uq
    ON outbox(idempotency_key);
  `);
}

I learned the hard way that AsyncStorage can’t do this cleanly. Once you need “unique index” and “atomic transaction”, you’re already in SQLite land.

2) I enqueue an “outbox job” in the same transaction

This is the whole offline-first move.

I generate:

• setId: stable local ID.
• idempotencyKey: stable request key. Same key on retries.

Then I insert the set and the outbox job in one BEGIN / COMMIT.

// logSet.ts
import { db } from './db';
import { randomUUID } from 'expo-crypto';

type LogSetInput = {
  workoutId: string;
  exerciseId: string;
  reps: number;
  weight: number;
};

export function logSet(input: LogSetInput) {
  const now = Date.now();
  const setId = randomUUID();

  // Stable key = "operation + local id".
  // If the app retries 20 times, server should treat it as one write.
  const idempotencyKey = `set:create:${setId}`;

  const payload = {
    setId,
    workoutId: input.workoutId,
    exerciseId: input.exerciseId,
    reps: input.reps,
    weight: input.weight,
    clientCreatedAt: now,
  };

  db.execSync('BEGIN');
  try {
    db.runSync(
      `INSERT INTO sets (id, workout_id, exercise_id, reps, weight, created_at, updated_at)
       VALUES (?, ?, ?, ?, ?, ?, ?)` ,
      [setId, input.workoutId, input.exerciseId, input.reps, input.weight, now, now]
    );

    db.runSync(
      `INSERT INTO outbox (id, type, payload_json, idempotency_key, next_attempt_at, created_at)
       VALUES (?, ?, ?, ?, ?, ?)` ,
      [randomUUID(), 'SET_CREATE', JSON.stringify(payload), idempotencyKey, now, now]
    );

    db.execSync('COMMIT');
    return { setId };
  } catch (e) {
    db.execSync('ROLLBACK');
    throw e;
  }
}

This made my UI feel instant. Even on airplane mode.

One thing that bit me — I originally generated a new idempotency key per retry. That’s how I got duplicate sets. Don’t do that.

3) I run a tiny sync loop. No magic.

I spent 4 hours trying to be fancy.

Background tasks. AppState listeners. NetInfo gating. A whole “sync manager” class.

Most of it was wrong.

What stuck: a simple loop that:
1) Picks due jobs.
2) Sends them.
3) Deletes on success.
4) Backs off on failure.

It’s a state machine. In SQL.

// syncOutbox.ts
import { db } from './db';

const API_URL = 'https://example.com/api';

type OutboxRow = {
  id: string;
  type: 'SET_CREATE';
  payload_json: string;
  idempotency_key: string;
  attempt_count: number;
  next_attempt_at: number;
};

function backoffMs(attempt: number) {
  // 1s, 2s, 4s, ... up to 60s
  return Math.min(60_000, 1000 * Math.pow(2, attempt));
}

export async function syncOutboxOnce() {
  const now = Date.now();

  const rows = db.getAllSync(
    `SELECT id, type, payload_json, idempotency_key, attempt_count, next_attempt_at
     FROM outbox
     WHERE next_attempt_at <= ?
     ORDER BY created_at ASC
     LIMIT 10`,
    [now]
  );

  for (const job of rows) {
    try {
      if (job.type === 'SET_CREATE') {
        const payload = JSON.parse(job.payload_json);

        const res = await fetch(`${API_URL}/sets`, {
          method: 'POST',
          headers: {
            'Content-Type': 'application/json',
            // Server uses this to dedupe.
            'Idempotency-Key': job.idempotency_key,
          },
          body: JSON.stringify(payload),
        });

        if (!res.ok) {
          const text = await res.text();
          throw new Error(`HTTP ${res.status}: ${text.slice(0, 200)}`);
        }

        // Success. Remove job.
        db.runSync(`DELETE FROM outbox WHERE id = ?`, [job.id]);
      }
    } catch (err: any) {
      const next = Date.now() + backoffMs(job.attempt_count);
      db.runSync(
        `UPDATE outbox
         SET attempt_count = attempt_count + 1,
             next_attempt_at = ?,
             last_error = ?
         WHERE id = ?`,
        [next, String(err?.message ?? err), job.id]
      );

      // Don’t spin on a bad network.
      break;
    }
  }
}

This code is boring. Good.

Also: I don’t delete the local sets row on sync failure. The UI stays correct. The job just retries.

4) I keep the UI fast by separating “local state” from “sync state”

I made a dumb mistake early.

I blocked the “workout screen” on “isSynced”. So any offline moment made the app feel broken.

Now I compute sync state separately. A tiny query.

// selectors.ts
import { db } from './db';

export function getWorkoutSets(workoutId: string) {
  return db.getAllSync<{
    id: string;
    exercise_id: string;
    reps: number;
    weight: number;
    created_at: number;
  }>(
    `SELECT id, exercise_id, reps, weight, created_at
     FROM sets
     WHERE workout_id = ?
     ORDER BY created_at ASC`,
    [workoutId]
  );
}

export function getPendingCount() {
  const row = db.getFirstSync<{ c: number }>(
    `SELECT COUNT(1) as c FROM outbox WHERE next_attempt_at <= ?`,
    [Date.now()]
  );
  return row?.c ?? 0;
}

Then I render a tiny “Syncing…” indicator if getPendingCount() > 0.

Users don’t need a perfect cloud state. They need their workout log to not vanish.

5) I test retries by force-killing the app

Simulators lie. Real phones lie too.

So I test like a user:

• Turn on airplane mode.
• Log 12 sets.
• Swipe-kill the app.
• Re-open.
• Turn internet back on.

I also keep a debug screen that shows the last 20 outbox rows. attempt_count, last_error, all of it. Not pretty. But it saved me.

And yes, I hit SQLITE_BUSY: database is locked a few times before enabling WAL. That error message is burned into my brain.

Results

I measured two things: perceived speed and data loss.

Before the outbox, logging a set took 600–1200ms on LTE because I waited on a POST. Offline was worse: the set just failed and disappeared unless the user retried.

After switching to SQLite-first writes, the button press to UI update is ~20–40ms on my Pixel 7 and ~30–60ms on an iPhone 13 mini (measured with performance.now() around the state update). I also ran the airplane-mode test 15 times with 12 sets each. That’s 180 sets. I got 180 local rows and 180 successful server writes after reconnect. No duplicates.

Key takeaways

• Put your “truth for UI” in SQLite. Not in the network.
• Use an outbox table with a unique idempotency_key. Retries become safe.
• Backoff in SQL (next_attempt_at) instead of spinning timers.
• Don’t block screens on “synced”. Show local data, plus a small pending indicator.
• Test by killing the app mid-sync. If it survives that, it survives reality.

Closing

If you’ve built offline-first on React Native: where do you draw the line on conflict resolution?

Do you keep it simple (append-only + idempotency like I did), or do you implement full “last-write-wins” merges per field in SQLite?