> ## Documentation Index
> Fetch the complete documentation index at: https://committ.mintlify.site/llms.txt
> Use this file to discover all available pages before exploring further.

# 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**:

```sql theme={null}
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
);
```

<Warning>
  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.
</Warning>

***

## 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:

```typescript theme={null}
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

<Steps>
  <Step title="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.
  </Step>

  <Step title="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).
  </Step>

  <Step title="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.
  </Step>
</Steps>