Local Database

The disposable SQLite cache with Nuke and Pave migrations and Instance-Dependent Architecture.

Local Database

The local database (lib/local-db.ts) is a SQLite cache powered by expo-sqlite. It mirrors a subset of the Convex cloud data for offline access and instant UI rendering. The database file is named commit.db and uses WAL journaling mode for concurrent read performance.

---

Schema Version 12 (Instance-Dependent Architecture)

The current schema defines two core tables with zero foreign key constraints:

PRAGMA journal_mode = 'wal';

-- Master commitment definitions
CREATE TABLE local_tasks (
  id TEXT PRIMARY KEY,
  convex_id TEXT UNIQUE NOT NULL,
  assigner_id TEXT NOT NULL,
  assignee_id TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  visibility TEXT NOT NULL,
  recurrence_json TEXT NOT NULL,
  conditions_json TEXT NOT NULL,
  config_json TEXT NOT NULL DEFAULT '{}',
  penalty_json TEXT DEFAULT NULL,
  penalty_waiver_json TEXT DEFAULT NULL,
  strict_until INTEGER DEFAULT NULL,
  strict_duration_days INTEGER DEFAULT NULL,
  created_at INTEGER NOT NULL,
  updated_at INTEGER NOT NULL,
  synced_at INTEGER
);

-- Individual commitment occurrences (orphan-safe)
CREATE TABLE task_instances (
  id TEXT PRIMARY KEY,
  task_id TEXT NOT NULL,          -- NO FOREIGN KEY constraint
  convex_id TEXT NOT NULL,
  title TEXT DEFAULT '',
  scheduled_timestamp INTEGER NOT NULL,
  start_time INTEGER NOT NULL,
  end_time INTEGER NOT NULL,
  status TEXT DEFAULT 'pending',
  config_json TEXT NOT NULL DEFAULT '{}',
  checkpoints TEXT NOT NULL DEFAULT '[]',
  penalty_json TEXT DEFAULT NULL,
  conditions_json TEXT DEFAULT NULL,
  penalty_waiver_json TEXT DEFAULT NULL,
  is_manual_edit INTEGER DEFAULT 0,
  strict_until INTEGER DEFAULT NULL,
  created_at INTEGER NOT NULL
);

There are no foreign key constraints in this schema. This is intentional. The task_id column in task_instances references local_tasks.id logically but not physically. Orphaned instances (whose parent task was deleted) are first-class citizens that continue to enforce penalties.

---

Why No Foreign Keys?

The removal of FK constraints was a direct response to a production database corruption bug. The prior schema required toggling PRAGMA foreign_keys on and off around every write:

Problem	Impact
PRAGMA foreign_keys cannot be changed inside a transaction	SQLite silently ignores it, leaving inconsistent enforcement
Concurrent writers share the same connection	Race conditions when one writer enables FKs while another disables them
Root cause of database disk image is malformed errors	WAL corruption from competing PRAGMA state changes

By removing FKs entirely, all PRAGMA toggles were eliminated, and write paths became race-free.

---

Nuke and Pave Migration Strategy

There are no incremental migrations. If the on-device PRAGMA user_version does not match the current DATABASE_VERSION constant, the entire database is destroyed and rebuilt:

const DATABASE_VERSION = 12;

export async function migrateDbIfNeeded(db: SQLiteDatabase): Promise<void> {
  let currentVersion: number;

  try {
    const result = await db.getFirstAsync<{ user_version: number }>(
      "PRAGMA user_version"
    );
    currentVersion = result?.user_version ?? 0;
  } catch (e) {
    // PRAGMA read failed — database is corrupted beyond recovery
    currentVersion = -1;
  }

  // Hot path: schema is current, nothing to do (~0ms)
  if (currentVersion === DATABASE_VERSION) return;

  // Cold path: wipe everything and deploy the unified schema
  await db.execAsync(UNIFIED_SCHEMA_V12);
  await db.execAsync(`
    PRAGMA wal_checkpoint(TRUNCATE);
    VACUUM;
  `);
}

The VACUUM command physically reconstructs the entire database file from scratch. This eliminates ghost WAL boundaries that caused Android’s native CursorWindow to misread corrupted lengths (triggering 124MB OOM allocations on the JVM).

---

Three Recovery Tiers

1. Surgical Purge

   Clears all data rows without dropping tables. Preserves file descriptors so the Kotlin Accessibility Service keeps its database handle alive. Used by Manual Resync.

2. Nuke and Pave

   Drops all tables, deploys the unified schema, and runs VACUUM. Used on schema version mismatch or terminal corruption. Requires a full re-sync from Convex (Amnesia Mode).

3. OS-Level Clear

   If both tiers above fail, the user must clear the app data through Android Settings. This destroys the SQLite file entirely and forces a fresh install state.