React Native offline-first: SQLite schema for speed

• I model workouts as “events”, not nested JSON.
• I use SQLite indexes that match my screens.
• I store local-only IDs + sync state cleanly.
• I hit sub-100ms reads on a mid phone.

Context

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

Offline-first forced my hand.
I can’t rely on network.
I can’t rely on a giant in-memory object either.
I tried the “store the whole workout as JSON” approach first.
Brutal.
It made edits painful, queries slow, and sync a mess.

So I went boring.
SQLite.
Not as a cache.
As the source of truth.

This post is just one angle.
Schema.
Tables, indexes, and the queries that map to screens.

1) I store sets as append-only events

I stopped thinking in “workout objects”.
I started thinking in “things that happened”.

A set logged is an event.
Editing a set is another event.
Deleting is another event.
Later, I can compact.
But day-to-day? Append-only wins.

It fixed two annoying problems.
Undo becomes easy.
Sync conflicts get smaller.

Here’s the schema I’m using in Expo SQLite.
It’s not fancy.
But it’s stable.

-- 001_init.sql
PRAGMA foreign_keys = ON;

CREATE TABLE IF NOT EXISTS workouts (
  id TEXT PRIMARY KEY,              -- UUID (client-generated)
  started_at INTEGER NOT NULL,       -- epoch ms
  ended_at INTEGER,                 -- epoch ms
  title TEXT,
  note TEXT,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS set_events (
  id TEXT PRIMARY KEY,              -- UUID
  workout_id TEXT NOT NULL,
  exercise_id TEXT NOT NULL,
  set_index INTEGER NOT NULL,        -- position within exercise in that workout
  kind TEXT NOT NULL,               -- 'LOG' | 'EDIT' | 'DELETE'
  reps INTEGER,
  weight_kg REAL,
  rir INTEGER,                       -- reps in reserve
  happened_at INTEGER NOT NULL,      -- epoch ms
  device_seq INTEGER NOT NULL,       -- monotonic per device
  dirty INTEGER NOT NULL DEFAULT 1,  -- 1 = needs sync
  server_rev INTEGER NOT NULL DEFAULT 0,
  FOREIGN KEY (workout_id) REFERENCES workouts(id) ON DELETE CASCADE
);

-- The screen query: "latest state per set" needs this.
CREATE INDEX IF NOT EXISTS idx_set_events_workout_exercise
  ON set_events(workout_id, exercise_id, set_index, happened_at DESC);

-- Sync query: "give me dirty events in order" needs this.
CREATE INDEX IF NOT EXISTS idx_set_events_dirty_seq
  ON set_events(dirty, device_seq);

I learned the hard way that device_seq matters.
I originally sorted by happened_at.
Two events in the same millisecond.
Same phone.
Same JS tick.
Order flipped once.
I stared at a “ghost delete” for 4 hours.
Most of it was wrong.

Now I do device_seq.
Just an integer I increment locally.

2) I index for the UI I actually have

SQLite is fast.
But “fast” disappears when you make it guess.

My app has three hot paths:
1) Today’s workout screen (list exercises + latest sets)
2) Exercise history screen (last 20 sets)
3) Sync (dirty events)

So I index for those.
Not for “normal forms”.
For screens.

This is the query behind “show me the latest sets for this exercise in this workout”.
It uses a window function.
Expo’s SQLite ships new enough SQLite for this.
If you’re on something ancient, you’ll need a different approach.

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

export type LatestSetRow = {
  set_index: number;
  reps: number | null;
  weight_kg: number | null;
  rir: number | null;
  kind: "LOG" | "EDIT" | "DELETE";
  happened_at: number;
};

export async function getLatestSetsForExercise(
  db: SQLite.SQLiteDatabase,
  workoutId: string,
  exerciseId: string
): Promise {
  const sql = `
    WITH ranked AS (
      SELECT
        set_index, reps, weight_kg, rir, kind, happened_at,
        ROW_NUMBER() OVER (
          PARTITION BY set_index
          ORDER BY happened_at DESC
        ) AS rn
      FROM set_events
      WHERE workout_id = ? AND exercise_id = ?
    )
    SELECT set_index, reps, weight_kg, rir, kind, happened_at
    FROM ranked
    WHERE rn = 1 AND kind != 'DELETE'
    ORDER BY set_index ASC;
  `;

  const rows = await db.getAllAsync(sql, [workoutId, exerciseId]);
  return rows;
}

That idx_set_events_workout_exercise index is doing real work here.
Without it, my mid-tier Android (Pixel 6a) was spiking.
Not always.
Just enough to feel gross.

One thing that bit me — using TEXT IDs everywhere means indexes get bigger.
Still fine.
But don’t add random extra indexes “just in case”.
Each one costs writes.
And I’m writing a lot.

3) I keep sync state out of my domain fields

I don’t want “server stuff” leaking everywhere.
But offline-first means I need it.
So I isolate it.

My rule:

• Domain fields: reps, weight, exercise_id
• Sync fields: dirty, server_rev, device_seq

Then sync is just: “give me dirty events, ordered”.
No scanning.
No weird filters.

Here’s the exact query + a tiny “mark as synced” function.
This is intentionally boring.

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

type DirtyEvent = {
  id: string;
  workout_id: string;
  exercise_id: string;
  set_index: number;
  kind: string;
  reps: number | null;
  weight_kg: number | null;
  rir: number | null;
  happened_at: number;
  device_seq: number;
  server_rev: number;
};

export async function getDirtyEvents(
  db: SQLite.SQLiteDatabase,
  limit = 200
): Promise {
  const sql = `
    SELECT id, workout_id, exercise_id, set_index, kind,
           reps, weight_kg, rir, happened_at, device_seq, server_rev
    FROM set_events
    WHERE dirty = 1
    ORDER BY device_seq ASC
    LIMIT ?;
  `;
  return db.getAllAsync(sql, [limit]);
}

export async function markEventsSynced(
  db: SQLite.SQLiteDatabase,
  eventIds: string[],
  newServerRev: number
) {
  if (eventIds.length === 0) return;

  const placeholders = eventIds.map(() => "?").join(",");
  const sql = `
    UPDATE set_events
    SET dirty = 0,
        server_rev = ?
    WHERE id IN (${placeholders});
  `;

  await db.runAsync(sql, [newServerRev, ...eventIds]);
}

I used to store “lastSyncedAt” on the workout.
Bad move.
One event fails.
Now the whole workout looks synced.
Congrats, you just lost data.

Event-level sync state is annoying.
But it’s correct.

4) I generate IDs and sequences locally. Every time.

Offline means no server IDs at write time.
So I generate UUIDs on device.
And I keep a monotonic sequence.

I’m on Expo.
So I use expo-crypto for UUID.
And I store the sequence in SQLite too.
No AsyncStorage.
One storage system.
Less weirdness.

// db/writeSet.ts
import * as Crypto from "expo-crypto";
import * as SQLite from "expo-sqlite";

async function nextDeviceSeq(db: SQLite.SQLiteDatabase): Promise {
  await db.execAsync(`
    CREATE TABLE IF NOT EXISTS kv (
      key TEXT PRIMARY KEY,
      value TEXT NOT NULL
    );
  `);

  await db.runAsync(
    `INSERT OR IGNORE INTO kv(key, value) VALUES('device_seq', '0');`
  );

  // Atomic increment in SQLite.
  await db.runAsync(
    `UPDATE kv SET value = CAST(value AS INTEGER) + 1 WHERE key = 'device_seq';`
  );

  const row = await db.getFirstAsync<{ value: string }>(
    `SELECT value FROM kv WHERE key = 'device_seq';`
  );

  return Number(row?.value ?? 0);
}

export async function logSetEvent(
  db: SQLite.SQLiteDatabase,
  input: {
    workoutId: string;
    exerciseId: string;
    setIndex: number;
    reps: number;
    weightKg: number;
    rir?: number | null;
  }
) {
  const id = Crypto.randomUUID();
  const deviceSeq = await nextDeviceSeq(db);
  const now = Date.now();

  await db.runAsync(
    `INSERT INTO set_events(
      id, workout_id, exercise_id, set_index,
      kind, reps, weight_kg, rir,
      happened_at, device_seq, dirty
    ) VALUES(?, ?, ?, ?, 'LOG', ?, ?, ?, ?, ?, 1);`,
    [
      id,
      input.workoutId,
      input.exerciseId,
      input.setIndex,
      input.reps,
      input.weightKg,
      input.rir ?? null,
      now,
      deviceSeq,
    ]
  );

  return { id, deviceSeq };
}

This fixed a nasty bug.
I had device_seq in memory.
Killed the app.
Restart.
Sequence reset.
Sync order broke.

Storing it in SQLite is dull.
Also bulletproof.

Results

After moving from “nested JSON per workout” to the event schema above, my heaviest screen stopped stuttering.
On my Pixel 6a, the “open workout” query went from ~280–450ms down to ~45–85ms for a workout with 9 exercises and 31 logged sets.
Set logging writes are ~6–12ms per event locally.
Sync became simpler too: I send 1–200 small rows instead of merging a big JSON blob.
The best part is debugging.
I can read the event table and know what happened.
No guessing.

Key takeaways

• Model offline writes as events. Editing becomes another event.
• Add indexes that match your screens. Not your ER diagram.
• Keep sync fields (dirty, server_rev, device_seq) separate from domain fields.
• Don’t sort by timestamps alone. A monotonic sequence saves you.
• Store your sequence in SQLite, not memory. App restarts are real.

Closing

I’m still tuning this.
Next up is compaction: turning 40 events into 1 “current set” row without losing history.

If you’ve shipped offline-first on React Native: do you compact event logs on-device, or only on the server?