Expo SQLite offline cache keeps AI-built apps usable when signal drops

AI-built mobile apps usually assume the network is there. Then a user opens the app in a lift, on the metro, or on a rural road, and the whole screen turns into a spinner. The data was available an hour ago, but the app has no memory of it.

A local SQLite cache fixes that gap. Reads come from disk first, so screens render instantly. Writes queue locally when offline and sync when the connection returns. Supabase or any backend stays the source of truth, while the phone stays usable without signal.

This pattern pairs well with an offline-first mutation queue and durable Supabase Storage uploads for files. The cache covers structured rows, the queue covers intent, and storage covers binaries.

Why offline cache beats loading spinners

A spinner says the app knows nothing until the server answers. A cache says the app remembers what it last saw and refreshes when it can. The second experience feels faster even when the network is fine, because local reads resolve in milliseconds.

The tradeoff is complexity. You now own two copies of data and a sync story between them. That cost is worth it for read-heavy screens: home feeds, product catalogs, booking histories, dashboards, and any list users open repeatedly.

Keep the rule simple. Local disk owns what the user sees right now. The server owns what is true across devices. Sync moves local intent up and server truth down, with timestamps to decide conflicts.

Teams that already ship production lists with FlashList v2 get an extra win here. A cached array feeds the list immediately, then a background refresh swaps in fresh rows without a jarring reload.

Design the local schema around sync

Start with the same entities your backend returns, plus three sync columns on every cached table. You need an updated timestamp, a dirty flag for local edits, and a deleted flag for tombstones. Without those three, offline edits become guesswork.

// Cached task row with sync metadata
type CachedTask = {
  id: string;
  title: string;
  done: boolean;
  updatedAt: number;
  dirty: number;
  deleted: number;
};

Create one table per entity you cache. Do not try to store whole API responses as JSON blobs in a single key-value table. Queryable columns let you filter, sort, and paginate locally, which is the whole point of instant screens.

import * as SQLite from 'expo-sqlite';

export async function initDb() {
  const db = await SQLite.openDatabaseAsync('app-cache');
  await db.execAsync(`
    PRAGMA journal_mode = WAL;
    CREATE TABLE IF NOT EXISTS tasks (
      id TEXT PRIMARY KEY NOT NULL,
      title TEXT NOT NULL,
      done INTEGER NOT NULL DEFAULT 0,
      updated_at INTEGER NOT NULL,
      dirty INTEGER NOT NULL DEFAULT 0,
      deleted INTEGER NOT NULL DEFAULT 0
    );
    CREATE INDEX IF NOT EXISTS idx_tasks_updated ON tasks(updated_at);
  `);
  return db;
}

Keep migrations explicit. Add a small schema version table and run one migration function per version. AI-generated code loves to drop and recreate tables, which wipes the offline cache you just promised users. Versioned migrations preserve data across app updates.

Cache only what the user actually revisits. Profile screens, recent orders, saved items, and the first two pages of each feed cover most offline sessions. Archival history can stay server-only with an honest offline message.

Wire reads first, writes second

The read path is cache-first with background refresh. On screen mount, query SQLite and render. Then fire the network request, upsert fresh rows, and re-render. Users see content in one frame instead of waiting for a round trip.

export async function getTasks(db: SQLite.SQLiteDatabase) {
  return db.getAllAsync<CachedTask>(
    'SELECT * FROM tasks WHERE deleted = 0 ORDER BY updated_at DESC LIMIT 100'
  );
}

export async function upsertTasks(
  db: SQLite.SQLiteDatabase,
  rows: { id: string; title: string; done: boolean; updatedAt: number }[]
) {
  for (const row of rows) {
    await db.runAsync(
      `INSERT INTO tasks (id, title, done, updated_at, dirty, deleted)
       VALUES (?, ?, ?, ?, 0, 0)
       ON CONFLICT(id) DO UPDATE SET
         title = excluded.title,
         done = excluded.done,
         updated_at = excluded.updated_at,
         dirty = 0
       WHERE excluded.updated_at >= tasks.updated_at`,
      [row.id, row.title, row.done ? 1 : 0, row.updatedAt]
    );
  }
}

The timestamp guard matters. Without it, a slow response can overwrite a newer local edit with older server data. Last-write-wins by timestamp is crude but predictable, and predictable is what you want when an agent wrote most of the sync code.

Show staleness honestly. A small refreshed-x-minutes-ago label beats a fake live badge. Users forgive old data when you label it. They do not forgive an app that claims to be live while showing yesterday.

Queue mutations when offline

Reads come from disk. Writes go to disk first, then to the server. When the user toggles a task, mark the row dirty and update the UI immediately. A background worker picks up dirty rows and pushes them when connectivity returns.

export async function toggleTaskLocal(
  db: SQLite.SQLiteDatabase,
  id: string,
  done: boolean
) {
  const now = Date.now();
  await db.runAsync(
    'UPDATE tasks SET done = ?, updated_at = ?, dirty = 1 WHERE id = ?',
    [done ? 1 : 0, now, id]
  );
}

export async function getDirtyTasks(db: SQLite.SQLiteDatabase) {
  return db.getAllAsync<CachedTask>(
    'SELECT * FROM tasks WHERE dirty = 1 AND deleted = 0 ORDER BY updated_at ASC LIMIT 50'
  );
}

Keep mutations idempotent. Give every local operation a client-generated id and send it with the request. If a retry fires twice after a timeout, the server can deduplicate instead of creating two orders or two bookings.

Separate connectivity detection from sync logic. Listen for network changes to trigger a flush, but also flush on app foreground and after every local write when online. Relying on a single trigger leaves dirty rows stranded in real usage.

Cap retry behavior. Exponential backoff with a maximum of five attempts per row, then surface the failure in a compact outbox screen. Infinite silent retries drain batteries and hide backend errors that need attention.

Sync without clobbering server truth

Pull before push on every sync cycle. Fetch server changes since the last stored cursor, apply them to SQLite, then push dirty rows. Pull-first reduces conflicts because local edits land on top of fresh server state.

Store a per-table sync cursor in a tiny metadata table. Use the server updated timestamp as the cursor, not the local clock. Device clocks drift, and drift plus last-write-wins equals lost edits.

export async function getSyncCursor(
  db: SQLite.SQLiteDatabase,
  tableName: string
): Promise<number> {
  const row = await db.getFirstAsync<{ cursor_value: number }>(
    'SELECT cursor_value FROM sync_meta WHERE table_name = ?',
    [tableName]
  );
  return row?.cursor_value ?? 0;
}

export async function setSyncCursor(
  db: SQLite.SQLiteDatabase,
  tableName: string,
  cursor: number
) {
  await db.runAsync(
    `INSERT INTO sync_meta (table_name, cursor_value)
     VALUES (?, ?)
     ON CONFLICT(table_name) DO UPDATE SET cursor_value = excluded.cursor_value`,
    [tableName, cursor]
  );
}

Handle deletes with tombstones, not hard deletes. When a user deletes offline, set the deleted flag and keep the row until the server confirms. Hard-deleting locally means the next pull resurrects the row as if it were new.

Decide conflict policy per entity before you code it. Task toggles can use last-write-wins. Cart quantities and booking slots usually need server-wins or a manual merge screen. One global policy creates subtle data loss somewhere.

Test the failure modes before launch

The happy path always works in the simulator. The failure modes are what users remember. Test airplane mode mid-sync, kill the app during a flush, toggle the same task on two devices, and revoke auth tokens while dirty rows are pending.

Seed the cache in development with realistic row counts. A hundred rows hide performance bugs that ten thousand rows expose. Measure query time on a low-end Android device, not just the simulator. Add indexes on sort and filter columns before launch, not after a slowness report.

Verify storage growth. Caches without eviction grow until the OS complains. Cap feed tables to the most recent few hundred rows per user and prune on every successful sync. Keep user-created content until it is confirmed on the server, and prune server mirrors aggressively.

Check auth expiry handling alongside Expo Router auth guards so an expired session does not silently discard queued writes. A 401 during flush should pause the queue and route to sign-in, then resume without losing dirty rows. Losing user intent at the auth boundary is the fastest way to destroy trust in offline mode.

Log sync outcomes as first-party product events. Track flush success rate, conflict count, average dirty age, and cache hit rate on launch. These four numbers tell you whether offline mode is working or just installed.

What to build this week

Pick one read-heavy screen and make it work fully offline. Add the table, the cache-first read, the dirty-flag write path, and the pull-then-push sync. Ship that slice behind your normal release process with preview deployments so reviewers can toggle airplane mode before merge.

Then expand table by table. Offline support compounds in value with each cached entity, but the first slice teaches you the migration, cursor, and conflict habits that keep the rest safe. Start narrow, prove the loop, then widen.

Sources

• Expo SQLite documentation, verified live today: https://docs.expo.dev/versions/latest/sdk/sqlite/
• Related reading on this site: offline-first mutation queue, Supabase Storage uploads, FlashList v2