# SQLite Migration — Design Doc (CONC-009)

> Status: **design-only / deferred**. No implementation work tracked here. Authored as part of the concurrency hardening epic to capture the intended endgame so we don't lose context if/when JSON storage stops being good enough.

---

## 1. Why this exists

The concurrency hardening series (CONC-001 through CONC-008) added per-file mutexes, optimistic concurrency, atomic claims, heap read-safety, transactional dual-writes, idempotency keys, and a concurrent-load test suite — all on top of the existing JSON file store. That is sufficient for the current single-process backend, even under aggressive in-process agent concurrency.

It is **not** sufficient for:

| Future scenario | Why JSON+mutex falls over |
|---|---|
| Multiple Node processes hitting the same data dir | In-process mutexes don't cross process boundaries; `fs.rename` is atomic but the read-modify-write cycle isn't. The 1.5s file-watcher grace period is a heuristic, not a guarantee. |
| Cross-machine swarm (agents on different hosts hitting one backend) | Same as above — the backend itself is single-process today, but the moment we shard it we lose the only thing serializing writes. |
| Bigger datasets (10k+ tasks, 100k+ ML events) | We rewrite the entire JSON file on every save. At 100KB it's free; at 100MB it's a 50–200ms hitch on every mutation. |
| Partial reads / rich queries | Today every query reads the full file into memory. We hand-maintain in-memory indexes (`taskMap`, `taskHeap`). SQLite gets indexes for free and serves partial reads. |
| Point-in-time recovery / audit | JSON has no log; we'd be reverse-engineering crash state from a half-written tmpfile. SQLite WAL gives us a real journal. |

SQLite buys us **real ACID transactions, indexed queries, free per-row reads, and WAL-based crash safety** — without standing up a real DB server. The cost is one native dependency and a one-way migration.

## 2. Non-goals

- **Not** a client/server database. We stay embedded — same deploy story (single process, single file).
- **Not** a different API. The `StorageInterface` (`src/storage/interface.ts`) stays as-is. We introduce a `SqliteStorage` implementation alongside `JsonStorage`; the rest of the codebase shouldn't notice.
- **Not** a feature change. Schema parity with the current `ProgressDatabase` + `GlobalMLDatabase` types. New features (objectives, embedding-ready training data per `DEVELOPMENT_ROADMAP.md`) come *after* the migration lands.
- **Not** a backwards-compat layer. Migration is one-way. We snapshot JSON, convert, swap pointers, and don't look back. Old JSON files become read-only archive.

## 3. Driver choice — `better-sqlite3` vs `sqlite3`

| Dimension | `better-sqlite3` | `sqlite3` (node-sqlite3) |
|---|---|---|
| API | Synchronous (blocks event loop) | Async (callback/promise) |
| Throughput, single thread | ~3–10× faster for typical workloads | Slower; async overhead per call |
| Native build | Prebuilt binaries for common platforms | Same |
| Maturity | Maintained, used by Logseq, AnyType, etc. | Older, broader install base |
| Transactions | First-class `db.transaction(fn)` wrapper | Manual `BEGIN`/`COMMIT` |
| Fits our access pattern? | **Yes** — most ops are sub-millisecond, blocking is fine; aligns with the "JSON-style synchronous mental model" current code already uses around in-memory state | Adds async without buying anything |

**Pick:** `better-sqlite3`. The synchronous API is a feature, not a bug — our current code already does sync work between awaits. It also makes the mutex layer simpler: SQLite's own transactions replace the per-file `async-mutex`, and `db.transaction()` gives us the atomic critical sections we built by hand.

If we ever need to share a single SQLite file across multiple Node *processes* (unlikely — see §6), `better-sqlite3` works fine in WAL mode for that too.

## 4. Schema

Each TypeScript interface in `src/types/schema.ts` maps to one table. JSON-shaped fields (arrays of strings, nested objects without their own queries) stay as JSON columns — SQLite has good `json_*()` operators so we don't lose anything by punting structure where nobody queries it.

```sql
-- ─────────────────────────── core domain ───────────────────────────

CREATE TABLE workspaces (
  id            TEXT PRIMARY KEY,
  name          TEXT NOT NULL,
  description   TEXT,
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL
);

CREATE TABLE workspace_meta (
  key           TEXT PRIMARY KEY,           -- 'current_workspace_id'
  value         TEXT
);

CREATE TABLE projects (
  id            TEXT PRIMARY KEY,
  workspace_id  TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  name          TEXT NOT NULL,
  path          TEXT NOT NULL,
  status        TEXT NOT NULL,              -- ProjectStatus enum
  primary_focus TEXT NOT NULL,
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL
);
CREATE INDEX idx_projects_workspace ON projects(workspace_id);

CREATE TABLE tasks (
  id                  TEXT PRIMARY KEY,
  workspace_id        TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  priority            TEXT NOT NULL,        -- 'P0'..'P3'
  task                TEXT NOT NULL,
  project             TEXT NOT NULL,
  status              TEXT NOT NULL,        -- TaskStatus enum
  blocking            TEXT,
  dependencies_json   TEXT NOT NULL DEFAULT '[]',
  notes               TEXT,
  deadline            TEXT,
  effort              TEXT,                 -- 'low'|'medium'|'high'|null
  priority_score      REAL NOT NULL,
  weights_json        TEXT NOT NULL,        -- TaskWeights serialized
  started_at          TEXT,
  version             INTEGER NOT NULL DEFAULT 1,   -- CONC-003 optimistic concurrency
  claimed_by          TEXT,                 -- CONC-004 atomic claim
  created_at          TEXT NOT NULL,
  updated_at          TEXT NOT NULL
);
CREATE INDEX idx_tasks_workspace_status ON tasks(workspace_id, status);
CREATE INDEX idx_tasks_workspace_priority ON tasks(workspace_id, priority_score);
CREATE INDEX idx_tasks_workspace_project  ON tasks(workspace_id, project);

CREATE TABLE data_gaps (
  id            TEXT PRIMARY KEY,
  workspace_id  TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  element       TEXT NOT NULL,
  coverage      TEXT NOT NULL,
  priority      TEXT NOT NULL,
  impact        TEXT NOT NULL,
  effort        TEXT NOT NULL,
  created_at    TEXT NOT NULL,
  updated_at    TEXT NOT NULL
);

-- Per-workspace heuristic weights (parallels the JSON ProgressDatabase field).
CREATE TABLE heuristic_weights (
  workspace_id  TEXT PRIMARY KEY REFERENCES workspaces(id) ON DELETE CASCADE,
  blocking      REAL NOT NULL,
  cross_project REAL NOT NULL,
  time_sensitive REAL NOT NULL,
  effort_value  REAL NOT NULL,
  dependency    REAL NOT NULL
);

-- ─────────────────────── objectives (V4 prep) ───────────────────────

CREATE TABLE objectives (
  id                    TEXT PRIMARY KEY,
  workspace_id          TEXT NOT NULL REFERENCES workspaces(id) ON DELETE CASCADE,
  name                  TEXT NOT NULL,
  description           TEXT NOT NULL,
  target_date           TEXT,
  status                TEXT NOT NULL,
  key_results_json      TEXT NOT NULL,
  linked_task_ids_json  TEXT NOT NULL DEFAULT '[]',
  linked_project_ids_json TEXT NOT NULL DEFAULT '[]',
  created_at            TEXT NOT NULL,
  updated_at            TEXT NOT NULL
);

CREATE TABLE objective_progress_events (
  id                                TEXT PRIMARY KEY,
  objective_id                      TEXT NOT NULL REFERENCES objectives(id) ON DELETE CASCADE,
  timestamp                         TEXT NOT NULL,
  progress_percent                  REAL NOT NULL,
  key_result_progress_json          TEXT NOT NULL,
  tasks_completed_since_last_json   TEXT NOT NULL DEFAULT '[]',
  queue_snapshot_json               TEXT NOT NULL
);

-- ───────────────────── ML training (global, cross-workspace) ─────────────────────

CREATE TABLE decisions (
  id                  TEXT PRIMARY KEY,
  workspace_id        TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
  date                TEXT NOT NULL,
  decision            TEXT NOT NULL,
  rationale           TEXT NOT NULL,
  decision_type       TEXT,                 -- 'skip'|'defer'|'architectural'|'prioritization'|'other'
  related_task_id     TEXT,
  skipped_task_id     TEXT,
  selected_task_id    TEXT,
  rank_delta          INTEGER,
  selection_event_id  TEXT,
  created_at          TEXT NOT NULL
);
CREATE INDEX idx_decisions_workspace ON decisions(workspace_id);
CREATE INDEX idx_decisions_type ON decisions(decision_type);
CREATE INDEX idx_decisions_selection_event ON decisions(selection_event_id);

CREATE TABLE completion_records (
  id                      TEXT PRIMARY KEY,
  workspace_id            TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
  task_id                 TEXT NOT NULL,
  actual_completion_time  REAL NOT NULL,    -- hours, queue + work
  was_blocking            INTEGER NOT NULL, -- 0/1
  user_override_count     INTEGER NOT NULL,
  context_switch_count    INTEGER NOT NULL,
  outcome                 TEXT NOT NULL,    -- TaskOutcome enum
  initial_priority_score  REAL,
  final_priority_score    REAL,
  started_at              TEXT,
  actual_work_time        REAL,             -- hours, work-only
  completed_at            TEXT NOT NULL
);
CREATE INDEX idx_completion_workspace ON completion_records(workspace_id);
CREATE INDEX idx_completion_task ON completion_records(task_id);
CREATE INDEX idx_completion_completed_at ON completion_records(completed_at);

CREATE TABLE priority_change_events (
  id                      TEXT PRIMARY KEY,
  workspace_id            TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
  task_id                 TEXT NOT NULL,
  old_priority            TEXT NOT NULL,
  new_priority            TEXT NOT NULL,
  old_score               REAL NOT NULL,
  new_score               REAL NOT NULL,
  queue_position_before   INTEGER NOT NULL,
  queue_position_after    INTEGER NOT NULL,
  timestamp               TEXT NOT NULL
);
CREATE INDEX idx_priority_change_workspace ON priority_change_events(workspace_id);
CREATE INDEX idx_priority_change_task ON priority_change_events(task_id);

CREATE TABLE task_selection_events (
  id                          TEXT PRIMARY KEY,
  workspace_id                TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
  selected_task_id            TEXT NOT NULL,
  selected_task_score         REAL NOT NULL,
  selected_task_rank          INTEGER NOT NULL,
  top_task_id                 TEXT NOT NULL,
  top_task_score              REAL NOT NULL,
  queue_size                  INTEGER NOT NULL,
  was_top_selected            INTEGER NOT NULL, -- 0/1
  skipped_task_ids_json       TEXT NOT NULL DEFAULT '[]',
  implicit_preferences_json   TEXT NOT NULL DEFAULT '[]',
  selected_task_features_json TEXT,
  timestamp                   TEXT NOT NULL
);
CREATE INDEX idx_selection_workspace ON task_selection_events(workspace_id);
CREATE INDEX idx_selection_timestamp ON task_selection_events(timestamp);

CREATE TABLE queue_rebalance_events (
  id                          TEXT PRIMARY KEY,
  workspace_id                TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
  trigger                     TEXT NOT NULL,
  trigger_task_id             TEXT,
  queue_size_before           INTEGER NOT NULL,
  queue_size_after            INTEGER NOT NULL,
  significant_changes_json    TEXT NOT NULL DEFAULT '[]',
  top_tasks_before_json       TEXT NOT NULL DEFAULT '[]',
  top_tasks_after_json        TEXT NOT NULL DEFAULT '[]',
  timestamp                   TEXT NOT NULL
);

CREATE TABLE drag_reorder_events (
  id                          TEXT PRIMARY KEY,
  workspace_id                TEXT REFERENCES workspaces(id) ON DELETE SET NULL,
  task_id                     TEXT NOT NULL,
  from_rank                   INTEGER NOT NULL,
  to_rank                     INTEGER NOT NULL,
  direction                   TEXT NOT NULL, -- 'promoted'|'demoted'
  implicit_preferences_json   TEXT NOT NULL,
  dragged_task_features_json  TEXT NOT NULL,
  applied_weight_delta_json   TEXT,
  queue_size                  INTEGER NOT NULL,
  tasks_passed_ids_json       TEXT NOT NULL DEFAULT '[]',
  timestamp                   TEXT NOT NULL
);

-- ───────────────────── singletons ─────────────────────

-- Online learner is a single global object.
CREATE TABLE online_learner_state (
  id                    INTEGER PRIMARY KEY CHECK (id = 1),
  enabled               INTEGER NOT NULL,
  learning_rate         REAL NOT NULL,
  momentum              REAL NOT NULL,
  total_updates         INTEGER NOT NULL,
  last_update_timestamp TEXT,
  momentum_buffer_json  TEXT NOT NULL,
  max_weight_change     REAL NOT NULL,
  min_weight            REAL NOT NULL,
  max_weight            REAL NOT NULL,
  cumulative_loss       REAL NOT NULL,
  correct_predictions   INTEGER NOT NULL,
  total_pairs           INTEGER NOT NULL
);

-- Global heuristic weights baseline (parallels GlobalMLDatabase.heuristicWeights).
CREATE TABLE global_heuristic_weights (
  id            INTEGER PRIMARY KEY CHECK (id = 1),
  blocking      REAL NOT NULL,
  cross_project REAL NOT NULL,
  time_sensitive REAL NOT NULL,
  effort_value  REAL NOT NULL,
  dependency    REAL NOT NULL
);

-- Schema version for forward migrations.
CREATE TABLE schema_meta (
  key   TEXT PRIMARY KEY,
  value TEXT NOT NULL
);
INSERT INTO schema_meta(key, value) VALUES ('version', '1');
```

### JSON-column trade-off

`weights_json`, `dependencies_json`, `implicit_preferences_json`, etc. are stored as `TEXT` containing JSON. We never need to query *inside* them today (the heap rebuild reads `weights` as a whole, then computes `priority_score`; we never `WHERE weights.blocking > N`). If that changes, we promote those fields to columns later — `ALTER TABLE ADD COLUMN` is cheap. Promoting up front would just create dead columns.

### What disappears

- `progress.json`, `ml-training.json`, `workspaces.json`, `workspaces/{id}/progress.json` — gone.
- The atomic-rename pattern in `save()`/`saveGlobalML()` — replaced by SQLite WAL.
- The file watcher with 1.5s grace period (`storage/jsonStorage.ts:127-166`) — irrelevant because there's no file to watch.
- Per-file `async-mutex` (CONC-001) — replaced by SQLite transactions.

### What stays

- `taskMap` and `taskHeap` in-memory caches stay. They're hydrated from `SELECT * FROM tasks WHERE workspace_id = ?` on workspace switch. SQLite is fast, but holding the heap in memory is still faster for `getTopPriority()` than even a single indexed query.
- The interface (`src/storage/interface.ts`) is unchanged. Routes, MCP tools, frontend — all unchanged.

## 5. WAL mode + pragmas

Set on every connection open:

```sql
PRAGMA journal_mode = WAL;          -- writers don't block readers
PRAGMA synchronous = NORMAL;        -- WAL-safe, ~10x faster than FULL on fsync-heavy workloads
PRAGMA foreign_keys = ON;           -- enforce ON DELETE CASCADE
PRAGMA busy_timeout = 5000;         -- wait 5s on contention before throwing SQLITE_BUSY
PRAGMA temp_store = MEMORY;         -- temp tables/indexes in RAM
PRAGMA cache_size = -64000;         -- 64MB page cache (negative = KiB)
PRAGMA mmap_size = 268435456;       -- 256MB memory-mapped IO if supported
```

WAL specifically:
- Readers and writers no longer block each other (the only contention is writer-vs-writer, which our backend serializes anyway).
- `*-wal` and `*-shm` sidecar files appear next to `priority-forge.db`. **Backup script must include all three.**
- Periodic checkpoint via `PRAGMA wal_checkpoint(TRUNCATE);` on a 1-minute timer or after every 1000 commits to keep the WAL from growing unbounded.

`busy_timeout = 5000`: in single-process mode this never fires. It's belt-and-suspenders for the day we accidentally let two processes touch the file (e.g. systemd + dev server simultaneously); they serialize cleanly instead of throwing.

## 6. How transactions replace the mutex layer

Today we have `workspaceWriteMutex` and `globalMLWriteMutex` (CONC-001) plus a `saveBoth()` helper (CONC-006) that holds both in lock order. Under SQLite, that all collapses to:

```ts
// Equivalent of save() + saveGlobalML() atomic dual-write
db.transaction(() => {
  db.prepare(`UPDATE tasks SET ... WHERE id = ? AND version = ?`).run(...);
  db.prepare(`INSERT INTO task_selection_events (...) VALUES (...)`).run(...);
})();
```

`better-sqlite3`'s `db.transaction(fn)` wraps the function in `BEGIN IMMEDIATE` / `COMMIT` (or `ROLLBACK` on throw). Two concurrent writers serialize at the SQLite layer — we keep linearizable semantics without any in-process bookkeeping.

Optimistic concurrency (CONC-003, the `version` field) stays — it's now a `WHERE id = ? AND version = ?` clause, and a row-count of 0 means version conflict.

Atomic claim (CONC-004) becomes:

```sql
UPDATE tasks
SET status = 'in_progress', claimed_by = ?, started_at = ?, version = version + 1
WHERE id = ?
  AND status = 'not_started'
  AND (claimed_by IS NULL OR claimed_by = ?);
```

Single statement, atomic by definition. If `changes()` returns 0 we read back the row to figure out which 409 to throw (already_claimed vs already_complete vs not_found).

**Cross-process safety:** WAL plus `BEGIN IMMEDIATE` makes multi-process writers safe at the SQLite level. We don't need it today, but it's free.

## 7. Migration script

One-way, run-once, idempotent if re-run on the same source files:

`scripts/migrate-json-to-sqlite.ts`

```
1. Refuse to run if `data/priority-forge.db` exists unless `--force`.
2. Open a fresh DB, run schema.sql.
3. Read the JSON snapshot:
     - data/workspaces.json                       → workspaces, workspace_meta
     - data/workspaces/{id}/progress.json (each)  → projects, tasks, data_gaps,
                                                    decisions (workspace-scoped),
                                                    heuristic_weights, objectives,
                                                    objective_progress_events
     - data/ml-training.json                      → completion_records,
                                                    priority_change_events,
                                                    task_selection_events,
                                                    queue_rebalance_events,
                                                    drag_reorder_events,
                                                    online_learner_state,
                                                    global_heuristic_weights,
                                                    decisions (global)
4. All inserts inside ONE big transaction. Either the whole file lands or nothing does.
5. Backfill defaults for fields added since the JSON was first written:
     - tasks.version    → COALESCE(version, 1)
     - tasks.claimed_by → NULL
6. Verify counts: SELECT COUNT(*) FROM each table vs JSON array length.
7. Print a summary report; exit non-zero on any mismatch.
8. Move source JSON files to data/_archive/{ISO timestamp}/ — never delete.
```

The "decisions" table is denormalized in JSON today (it appears in both per-workspace `progress.json` AND in `ml-training.json`). The migration deduplicates by `id` and prefers the global copy.

`--dry-run` flag: do everything in an in-memory DB (`new Database(':memory:')`), report counts, exit. Lets us validate before committing.

## 8. Rollout plan

1. **Prep** — land schema.sql, the new `SqliteStorage` class implementing `StorageInterface`, and the migration script. No wiring yet. Tests for `SqliteStorage` mirror `JsonStorage` tests (same interface = same tests, swap the constructor).
2. **Dual-mode flag** — `STORAGE_BACKEND=json|sqlite` env var; default stays `json`. Devs opt in locally.
3. **Migrate dev** — run `scripts/migrate-json-to-sqlite.ts` on a copy of dev data, run the full vitest suite (CONC-008 concurrency tests included) against `STORAGE_BACKEND=sqlite`. Acceptance: zero test diffs.
4. **Migrate the live backend** — stop the systemd service, run the migration on real `data/`, restart with `STORAGE_BACKEND=sqlite`. Frontend should not notice (same API). Source JSON archived under `data/_archive/`.
5. **Bake** — leave `STORAGE_BACKEND=json` available as a fallback for one release cycle. If anything weird shows up, flip back to JSON (the archive is intact).
6. **Remove** — delete `JsonStorage`, the file watcher, the per-file mutex layer, the dual-mode flag, and the JSON code paths in routes (none should exist — the interface is the seam).

Total expected downtime for step 4: under 30 seconds (data is ~190KB today; `better-sqlite3` inserts ~50k rows/sec; the actual bottleneck is service restart time).

## 9. Open questions to revisit when this becomes real

- **One DB file or one per workspace?** Current sketch: one `priority-forge.db` with `workspace_id` columns everywhere. Pro: cross-workspace ML queries are trivial. Con: workspace deletion is `DELETE FROM ... WHERE workspace_id = ?` instead of `rm -rf`. I'd still pick one DB — the ML data is global anyway, so we'd be juggling two DBs no matter what.
- **Drop the in-memory heap and trust SQLite indexes?** Probably yes eventually. `SELECT ... ORDER BY priority_score LIMIT 1` is microseconds with the index; the heap was load-bearing only because every JSON read was full-file. Defer until after migration lands so we have one variable changing at a time.
- **Embedding columns now or later?** The roadmap (`docs/DEVELOPMENT_ROADMAP.md`) wants `description_embedding BLOB`, `notes_embedding BLOB`, etc. for V5. Easier to add as `ALTER TABLE` later than to ship them empty now.
- **Backup story.** SQLite has `.backup` (online, consistent). Replace the implicit "copy the JSON file" workflow with an `npm run backup` that uses `db.backup('data/backups/...')`. Trivial to add; flag for the implementer.
- **MCP claim-on-checkout for cross-machine swarms.** Once we have multi-process safety, the `claim_task` story extends naturally to multi-machine without further work. That's the actual prize.

## 10. Effort estimate

Rough, when this lands on a real sprint:

| Slice | Effort |
|---|---|
| `SqliteStorage` class implementing `StorageInterface` (all ~30 methods) | 1.5 days |
| Schema + migrations infrastructure | 0.5 day |
| Migration script + dry-run validation | 0.5 day |
| Dual-backend tests (run vitest twice with env flag) | 0.25 day |
| Cutover + monitoring + JSON deprecation | 0.25 day |
| **Total** | **~3 days** |

Plus a release window for the bake period.

---

**Decision record:** see Priority Forge decision log entry on 2026-04-13 (CONC-009 brought forward to close out the concurrency epic with documentation parity, even though scoring puts it at P3).
