React Native offline queue with SQLite sync

• I log workout sets offline. Always.
• I queue writes in SQLite, not AsyncStorage.
• I replay the queue with idempotency keys.
• I fixed duplicate syncs and “ghost sets”.

Context

My fitness app has one job.
Log a set in ~5 seconds.

Internet can’t be a dependency.
Basements. Bad gym Wi‑Fi. Airplane mode because people hate notifications.
So the app is offline-first.

I started with the classic mistake.
“Just save locally and sync later.”
Spent 4 hours on it.
Most of it was wrong.

The failures were dumb but real.
Duplicate sets after a retry.
Sets that looked saved, then vanished after a relaunch.
And one brutal bug: I’d sync the same queue item twice when the app resumed.

SQLite fixed it.
Not because it’s trendy.
Because I needed transactions, querying, and a real queue.

1) I store an outbox. Not “pending: true”

I tried pending flags on the main sets table.
It worked until it didn’t.
Deletes were messy.
Edits were messy.
And retries became “which version is the truth?”

So I split it.
Real data tables.
And an outbox table with operations.

I’m on Expo.
I use expo-sqlite.

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

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

export function migrate() {
  // WAL helps concurrency on mobile. Less "database is locked" pain.
  db.execSync(`PRAGMA journal_mode = WAL;`);

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

    CREATE TABLE IF NOT EXISTS outbox (
      id TEXT PRIMARY KEY NOT NULL,
      op TEXT NOT NULL,           -- 'insert_set' | 'delete_set'
      payload TEXT NOT NULL,      -- JSON string
      idempotency_key TEXT NOT NULL,
      created_at INTEGER NOT NULL,
      sent_at INTEGER             -- null until acked
    );

    CREATE INDEX IF NOT EXISTS idx_outbox_sent_at
      ON outbox(sent_at, created_at);

    CREATE UNIQUE INDEX IF NOT EXISTS uq_outbox_idem
      ON outbox(idempotency_key);
  `);
}

Why the unique index?
Because mobile apps retry.
Because I retry.
Because the OS kills the process mid-request.
If the same action gets enqueued twice, I want SQLite to say no.

And yeah.
The first time I added that index, my app crashed on launch.
UNIQUE constraint failed: outbox.idempotency_key.
It was my own duplicate rows from earlier tests.
I deleted the DB.
Moved on.

2) I enqueue + write in one transaction

This is the whole trick.
One transaction.
Either both writes happen, or neither does.

Before this, I’d insert into sets first.
Then enqueue the outbox.
If the app died between those two statements… I had a local set with no sync record.
A ghost set.

So now I do it atomically.

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

type SetRow = {
  id: string;
  workout_id: string;
  exercise_id: string;
  reps: number;
  weight: number;
  created_at: number;
};

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

  // Same user action => same idempotency key.
  // If UI double-taps, SQLite blocks the duplicate outbox row.
  const idempotencyKey = `insert_set:${setId}`;

  db.withTransactionSync(() => {
    db.runSync(
      `INSERT INTO sets (id, workout_id, exercise_id, reps, weight, created_at)
       VALUES (?, ?, ?, ?, ?, ?)` ,
      [setId, input.workout_id, input.exercise_id, input.reps, input.weight, now]
    );

    db.runSync(
      `INSERT INTO outbox (id, op, payload, idempotency_key, created_at, sent_at)
       VALUES (?, ?, ?, ?, ?, NULL)` ,
      [
        randomUUID(),
        'insert_set',
        JSON.stringify({ ...input, id: setId, created_at: now }),
        idempotencyKey,
        now,
      ]
    );
  });

  return setId;
}

No loading spinner.
No network call.
The UI updates from local SQLite immediately.
Sync is background work.

One thing that bit me — withTransactionSync will throw, but my UI didn’t show it.
I had one silent failure from a bad payload (NaN weight).
Now I validate inputs before writing.
Boring. Necessary.

3) I process the outbox with a lease

My second dumb bug.
App resumes.
I start sync.
NetInfo fires twice.
I start sync again.
Now two loops are racing.

Result.
Two requests for the same outbox row.
My API was “mostly idempotent”… until it wasn’t.

Fix.
A lease.
One sync loop at a time.
And I claim rows before sending.

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

let syncing = false;

type OutboxRow = {
  id: string;
  op: string;
  payload: string;
  idempotency_key: string;
  created_at: number;
  sent_at: number | null;
};

export async function syncOutbox(apiBaseUrl: string) {
  if (syncing) return;
  syncing = true;

  try {
    while (true) {
      // Grab the next unsent row.
      const row = db.getFirstSync(
        `SELECT * FROM outbox
         WHERE sent_at IS NULL
         ORDER BY created_at ASC
         LIMIT 1`
      );

      if (!row) break;

      // Claim it before sending. Prevents double-send on re-entry.
      const claimedAt = Date.now();
      db.runSync(`UPDATE outbox SET sent_at = ? WHERE id = ? AND sent_at IS NULL`, [claimedAt, row.id]);

      const stillClaimed = db.getFirstSync<{ c: number }>(
        `SELECT COUNT(1) AS c FROM outbox WHERE id = ? AND sent_at = ?`,
        [row.id, claimedAt]
      );

      if (!stillClaimed || stillClaimed.c !== 1) {
        // Someone else claimed it (or it was already sent). Skip.
        continue;
      }

      const res = await fetch(`${apiBaseUrl}/sync/outbox`, {
        method: 'POST',
        headers: {
          'content-type': 'application/json',
          'idempotency-key': row.idempotency_key,
        },
        body: JSON.stringify({ op: row.op, payload: JSON.parse(row.payload) }),
      });

      if (!res.ok) {
        // Unclaim it so we retry later.
        db.runSync(`UPDATE outbox SET sent_at = NULL WHERE id = ? AND sent_at = ?`, [row.id, claimedAt]);
        throw new Error(`sync failed: ${res.status} ${await res.text()}`);
      }

      // Acked. Delete from outbox.
      db.runSync(`DELETE FROM outbox WHERE id = ?`, [row.id]);
    }
  } finally {
    syncing = false;
  }
}

This isn’t a perfect distributed lock.
It’s good enough.
Single device.
Single DB.
My problem was re-entrancy.
This kills it.

Also.
I delete acked rows.
I don’t keep a “synced history” in outbox.
If I need audit logs, that’s a different table.

4) I make the server idempotent (or don’t bother)

If your server isn’t idempotent, offline sync is cosplay.
Harsh.
But true.

The client can do everything right.
Then a request times out.
You retry.
Now you inserted twice.

I fix it with an idempotency_keys table.
I’m using Postgres on the backend.
This is minimal.
Works.

-- postgres.sql
CREATE TABLE IF NOT EXISTS idempotency_keys (
  key TEXT PRIMARY KEY,
  created_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

CREATE TABLE IF NOT EXISTS sets (
  id UUID PRIMARY KEY,
  workout_id UUID NOT NULL,
  exercise_id UUID NOT NULL,
  reps INT NOT NULL,
  weight DOUBLE PRECISION NOT NULL,
  created_at TIMESTAMPTZ NOT NULL
);

// server.ts (Node 20 + Express)
import express from 'express';
import pg from 'pg';

const { Pool } = pg;
const pool = new Pool({ connectionString: process.env.DATABASE_URL });

const app = express();
app.use(express.json());

app.post('/sync/outbox', async (req, res) => {
  const key = String(req.header('idempotency-key') || '');
  if (!key) return res.status(400).send('missing idempotency-key');

  const { op, payload } = req.body as { op: string; payload: any };

  const client = await pool.connect();
  try {
    await client.query('BEGIN');

    // If the key already exists, we already processed it.
    const inserted = await client.query(
      `INSERT INTO idempotency_keys(key) VALUES($1)
       ON CONFLICT (key) DO NOTHING
       RETURNING key`,
      [key]
    );

    if (inserted.rowCount === 0) {
      await client.query('COMMIT');
      return res.status(200).json({ ok: true, deduped: true });
    }

    if (op === 'insert_set') {
      await client.query(
        `INSERT INTO sets(id, workout_id, exercise_id, reps, weight, created_at)
         VALUES($1, $2, $3, $4, $5, to_timestamp($6 / 1000.0))
         ON CONFLICT (id) DO NOTHING`,
        [payload.id, payload.workout_id, payload.exercise_id, payload.reps, payload.weight, payload.created_at]
      );
    } else {
      throw new Error(`unknown op: ${op}`);
    }

    await client.query('COMMIT');
    res.status(200).json({ ok: true });
  } catch (e: any) {
    await client.query('ROLLBACK');
    res.status(500).send(e.message);
  } finally {
    client.release();
  }
});

app.listen(3000);

Two layers.
Client idempotency.
Server idempotency.
You want both.

And yeah, I got this wrong first.
I only deduped by set.id.
Then I added delete/edit operations later.
Now the idempotency key is per operation.
Same set can be inserted, edited, deleted.
Different keys.

Results

Before the outbox, I could reproduce duplicates in under 2 minutes.
Airplane mode on.
Log 12 sets.
Kill the app.
Reopen.
Turn internet on.
I’d end up with 14–18 sets server-side.
Gross.

After the outbox + lease + idempotency:
I ran that same test 30 times.
Logged 360 sets total.
Server ended with exactly 360 sets.
No duplicates.
No missing rows.

UI speed also improved.
Set logging stays under 100ms on my Pixel 7.
Even with 400+ exercises cached.

Key takeaways

• Don’t mix “real data” and “pending sync” in one table. Use an outbox.
• Wrap “write data” + “enqueue op” in one SQLite transaction, or you’ll ship ghost rows.
• Guard your sync loop. Re-entrancy happens on resume and flaky connectivity.
• Client-side dedupe helps, but server idempotency is the actual safety net.
• Delete acked outbox rows. If you need history, store history explicitly.

Closing

If you’re doing offline-first in React Native, what’s your strategy for edits and deletes?
Do you enqueue full row snapshots, JSON patches, or something else that won’t explode your outbox size?