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

> V12 Instance-Dependent SQLite schema and Nuke and Pave migration strategy.

# Local Database Schema

**Source:** `lib/local-db.ts` (14.8KB)

The local database defines the V12 Instance-Dependent schema -- two core tables with zero foreign key constraints and a Nuke and Pave migration strategy.

***

## Schema Version: 12

```typescript theme={null}
const DATABASE_VERSION = 12;
```

***

## Table: local\_tasks

The master task entity table. Mirrors the Convex tasks collection with JSON columns for complex nested structures:

| Column                 | Type             | Purpose                                  |
| :--------------------- | :--------------- | :--------------------------------------- |
| id                     | TEXT PRIMARY KEY | Convex \_id (Unified Identity)           |
| convex\_id             | TEXT             | Duplicate for legacy query compatibility |
| assigner\_id           | TEXT             | User who created the commitment          |
| assignee\_id           | TEXT             | User who must fulfill it                 |
| title                  | TEXT             | Human-readable commitment name           |
| description            | TEXT             | Optional description                     |
| visibility             | TEXT             | private, public, or partner\_only        |
| recurrence\_json       | TEXT             | JSON: days, time windows, repeat config  |
| conditions\_json       | TEXT             | JSON: location, digital, rule conditions |
| config\_json           | TEXT             | JSON: verification style, grace period   |
| penalty\_json          | TEXT             | JSON: penalty type and configuration     |
| penalty\_waiver\_json  | TEXT             | JSON: waiver type and deadline           |
| strict\_until          | INTEGER          | Epoch timestamp when strict lock expires |
| strict\_duration\_days | INTEGER          | Original lock duration for display       |
| created\_at            | INTEGER          | Creation epoch timestamp                 |
| updated\_at            | INTEGER          | Last modification epoch timestamp        |
| synced\_at             | INTEGER          | Last sync with Convex epoch timestamp    |

***

## Table: task\_instances

Individual commitment occurrences. Orphan-safe with zero FK constraints:

| Column                | Type             | Purpose                                                                   |
| :-------------------- | :--------------- | :------------------------------------------------------------------------ |
| id                    | TEXT PRIMARY KEY | Convex instance \_id (Unified Identity)                                   |
| task\_id              | TEXT             | Parent task ID (NO FK constraint)                                         |
| convex\_id            | TEXT             | Duplicate for query compatibility                                         |
| scheduled\_timestamp  | INTEGER          | When this instance was projected                                          |
| start\_time           | INTEGER          | Window start epoch                                                        |
| end\_time             | INTEGER          | Window end epoch                                                          |
| status                | TEXT             | pending, proceeding, proceeded, failed, penalized, waived, waiver\_active |
| title                 | TEXT             | Inherited from parent task                                                |
| config\_json          | TEXT             | JSON: verification style, alarm timing                                    |
| checkpoints           | TEXT             | JSON: checkpoint verification statuses                                    |
| conditions\_json      | TEXT             | JSON: per-instance condition statuses                                     |
| penalty\_json         | TEXT             | JSON: frozen penalty snapshot                                             |
| penalty\_waiver\_json | TEXT             | JSON: frozen waiver snapshot                                              |
| strict\_until         | INTEGER          | Inherited strict lock timestamp                                           |
| is\_manual\_edit      | INTEGER          | 0 or 1: protects from bulk purge                                          |
| created\_at           | INTEGER          | Local insertion timestamp                                                 |

***

## Nuke and Pave Migration

There are no incremental migrations. The migration function checks `PRAGMA user_version` against DATABASE\_VERSION:

```
if (currentVersion !== DATABASE_VERSION) {
    DROP TABLE IF EXISTS local_tasks;
    DROP TABLE IF EXISTS task_instances;
    CREATE TABLE local_tasks (...);
    CREATE TABLE task_instances (...);
    PRAGMA user_version = 12;
    VACUUM;  // Physically reconstruct the file
}
```

VACUUM eliminates ghost WAL boundaries from the old schema, preventing the Kotlin Accessibility Service from reading stale page headers.

***

## Three Recovery Tiers

| Tier              | Method                      | Scope         | Preserves File Descriptors |
| :---------------- | :-------------------------- | :------------ | :------------------------- |
| 1. Surgical Purge | DELETE FROM                 | Data only     | Yes                        |
| 2. Nuke and Pave  | DROP + CREATE + VACUUM      | Schema + data | No                         |
| 3. OS-Level Clear | Android Settings clear data | Everything    | No                         |