React Native offline-first: SQLite schema for speed

-- 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);

// 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;
}

// 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]);
}

// 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 };
}