- I log sets offline first. Always.
- I queue writes in SQLite, not AsyncStorage.
- I replay the queue with idempotency keys.
- I fixed my duplicate-sync bug with one UNIQUE index.
Context
I’m building a fitness app in React Native. Expo. SQLite. It has to log a set in under 5 seconds. No spinners. No “try again later”.
Offline-first sounds simple until you add sync. Brutal.
My first attempt used AsyncStorage as a queue. It worked. Then it didn’t. I got duplicate workout sets after flaky network toggles. Twice. Sometimes three times. The UI looked fine, but the server ended up with 2-3 copies of the same set.
So I moved the queue into SQLite. Transactions. Indexes. A real outbox.
This post is the pattern I ended up with. Copy-pasteable. And yes, I wasted 4 hours before I added the UNIQUE constraint.
1) I treat every write like an event. Not a “save”
I don’t “update the workout” first.
I append events. set_logged, set_deleted, workout_renamed. Whatever.
Two reasons.
One: it’s naturally offline.
Two: sync becomes replay.
I keep two tables locally:
workout_setsfor UI reads (fast)
outboxfor sync writes (durable)
Here’s the schema I use in Expo SQLite.
// db/schema.ts
import * as SQLite from "expo-sqlite";
export async function initDb(db: SQLite.SQLiteDatabase) {
// UI table: what the app renders
await db.execAsync(`
PRAGMA journal_mode = WAL;
CREATE TABLE IF NOT EXISTS workout_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
);
-- Outbox: what gets synced
CREATE TABLE IF NOT EXISTS outbox (
id TEXT PRIMARY KEY,
type TEXT NOT NULL,
payload TEXT NOT NULL,
idempotency_key TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'pending',
attempt_count INTEGER NOT NULL DEFAULT 0,
next_attempt_at INTEGER NOT NULL DEFAULT 0,
created_at INTEGER NOT NULL
);
-- This saved me. Without it I got duplicate server writes.
CREATE UNIQUE INDEX IF NOT EXISTS outbox_idem_idx
ON outbox(idempotency_key);
CREATE INDEX IF NOT EXISTS outbox_status_next_idx
ON outbox(status, next_attempt_at);
`);
}
That outbox_idem_idx is the whole story.
Without it, a double-tap or a retry loop creates duplicate rows. Then you sync duplicates. Then you cry.
2) I enqueue inside the same transaction as the UI write
Atomic or bust.
If I insert into workout_sets but fail to insert into outbox, the UI lies. The set looks saved, but it’ll never sync.
So I do both in one SQLite transaction.
Also: I generate the idempotency key on the client. It’s stable. It goes to the server. The server must dedupe on it too.
// db/logSet.ts
import * as SQLite from "expo-sqlite";
type LogSetInput = {
setId: string; // uuid
workoutId: string;
exerciseId: string;
reps: number;
weight: number;
now: number; // Date.now()
};
export async function logSet(db: SQLite.SQLiteDatabase, input: LogSetInput) {
const payload = {
id: input.setId,
workoutId: input.workoutId,
exerciseId: input.exerciseId,
reps: input.reps,
weight: input.weight,
createdAt: input.now,
};
// Deterministic key. Same event => same key.
const idemKey = `set_logged:${input.setId}`;
await db.withTransactionAsync(async () => {
await db.runAsync(
`INSERT INTO workout_sets (id, workout_id, exercise_id, reps, weight, created_at)
VALUES (?, ?, ?, ?, ?, ?);`,
[
input.setId,
input.workoutId,
input.exerciseId,
input.reps,
input.weight,
input.now,
]
);
// If this INSERT hits the UNIQUE index, it means we've already queued it.
// That's fine. UI stays correct. Outbox stays deduped.
await db.runAsync(
`INSERT OR IGNORE INTO outbox
(id, type, payload, idempotency_key, status, attempt_count, next_attempt_at, created_at)
VALUES (?, ?, ?, ?, 'pending', 0, 0, ?);`,
[
crypto.randomUUID(),
"set_logged",
JSON.stringify(payload),
idemKey,
input.now,
]
);
});
}
One thing that bit me — I originally used INSERT without OR IGNORE.
Then a retry would throw UNIQUE constraint failed: outbox.idempotency_key.
That’s correct behavior. But it spammed Sentry and scared me into removing the index. Bad move.
Keep the index. Use INSERT OR IGNORE.
3) I sync in small batches. And I back off hard
I don’t try to “sync everything”.
I take 10 rows. Send them. Mark them done.
If the network is trash, I back off. Otherwise you burn battery and hammer your own API.
I keep attempt_count and next_attempt_at in the outbox table. SQLite is my scheduler.
// sync/syncOutbox.ts
import * as SQLite from "expo-sqlite";
type SyncResult = { ok: true } | { ok: false; retryAfterMs: number };
async function postEvents(
events: Array<{ id: string; type: string; payload: string; idempotency_key: string }>
): Promise {
const res = await fetch("https://api.example.com/sync/events", {
method: "POST",
headers: {
"Content-Type": "application/json",
},
body: JSON.stringify({ events }),
});
if (res.status >= 200 && res.status < 300) return { ok: true };
// 429 / 503 / random failures => retry
const retryAfter = Number(res.headers.get("Retry-After") ?? "0");
const retryAfterMs = retryAfter > 0 ? retryAfter * 1000 : 30_000;
return { ok: false, retryAfterMs };
}
export async function syncOutbox(db: SQLite.SQLiteDatabase) {
const now = Date.now();
const rows = await db.getAllAsync<{
id: string;
type: string;
payload: string;
idempotency_key: string;
attempt_count: number;
}>(
`SELECT id, type, payload, idempotency_key, attempt_count
FROM outbox
WHERE status = 'pending' AND next_attempt_at <= ?
ORDER BY created_at ASC
LIMIT 10;`,
[now]
);
if (rows.length === 0) return;
const result = await postEvents(rows);
await db.withTransactionAsync(async () => {
if (result.ok) {
const ids = rows.map(r => r.id);
const placeholders = ids.map(() => "?").join(",");
await db.runAsync(
`UPDATE outbox SET status = 'done' WHERE id IN (${placeholders});`,
ids
);
return;
}
// Exponential-ish backoff, capped.
for (const r of rows) {
const nextAttempt = now + Math.min(15 * 60_000, result.retryAfterMs * (r.attempt_count + 1));
await db.runAsync(
`UPDATE outbox
SET attempt_count = attempt_count + 1,
next_attempt_at = ?
WHERE id = ?;`,
[nextAttempt, r.id]
);
}
});
}
This runs on an interval when the app is open.
I don’t do background sync yet. iOS will humble you.
Also, I don’t delete rows immediately. I mark them done.
That saved me during debugging. I could inspect what actually got sent.
4) I only update UI from local tables. The server is secondary
This is the part most people skip.
If your UI reads from “server state”, offline-first becomes “offline maybe”.
My screens read from workout_sets. Always.
Sync is just a conveyor belt in the background.
Here’s a tiny hook that loads sets for a workout and keeps it fast.
// hooks/useWorkoutSets.ts
import { useEffect, useState } from "react";
import * as SQLite from "expo-sqlite";
type WorkoutSet = {
id: string;
workout_id: string;
exercise_id: string;
reps: number;
weight: number;
created_at: number;
};
export function useWorkoutSets(db: SQLite.SQLiteDatabase, workoutId: string) {
const [sets, setSets] = useState([]);
useEffect(() => {
let cancelled = false;
(async () => {
const rows = await db.getAllAsync(
`SELECT * FROM workout_sets
WHERE workout_id = ?
ORDER BY created_at ASC;`,
[workoutId]
);
if (!cancelled) setSets(rows);
})();
return () => {
cancelled = true;
};
}, [db, workoutId]);
return sets;
}
Yeah, it’s not reactive.
I tried to get fancy with listeners. Spent hours. Most of it was wrong.
Now I just re-fetch after logging a set. It’s cheap. SQLite is local. It’s fine.
If you want it snappier, update state optimistically and let the DB catch up. Same pattern.
Results
Before this, I could reproduce duplicates in 2 minutes. Airplane mode on/off. Tap “log set” twice. Then open the app later on Wi‑Fi. I’d see 2-3 copies on the server.
After moving to a SQLite outbox with a UNIQUE idempotency key, I haven’t created a duplicate in the last 9 days of testing.
I also stopped losing writes. My old AsyncStorage queue dropped 7 events across 3 test sessions (I was clearing keys during dev and forgetting).
Now the UI write and the enqueue happen in one transaction. If it’s on screen, it’s in the outbox.
Key takeaways
- Put the outbox in SQLite. Not AsyncStorage.
- Enqueue in the same transaction as the UI write.
- Add a UNIQUE index on
idempotency_keyon day one. - Mark outbox rows as
done. Don’t delete during development. - Batch sync (10-ish). Back off hard when the API says no.
Closing
If you’re doing offline sync in React Native: where do you enforce idempotency — client only with a UNIQUE index, server only, or both with the same idempotency key?
SOCIAL SHARE CARD GENERATOR