perf: replace JSON file persistence with SQLite + configurable history retention

[kovtcharov-amd]

Problem

The current file-based persistence has several performance and reliability issues, especially on Windows:

• tasks.json is read/written in full every 500ms — with 33+ tasks this is a large JSON blob being serialized on every debounce tick
• File locking on Windows (NTFS) — concurrent reads/writes require a tmp+rename atomic dance; still causes contention under load
• No partial updates — changing one field on one task rewrites the entire file
• Config, workspaces, learnings, chat history are all separate JSON files with the same problems
• History files grow unboundedly — rotation is a synchronous operation on the hot save path

Proposed Solution

1. Replace all JSON metadata files with SQLite

Use better-sqlite3 (synchronous, fast, well-tested on Windows with WAL mode).

Tables:

• tasks — id, prompt, workspace_id, display_name, state, session_id, system_prompt, created_at, last_activity, git_state_json, backend_type, interrupted, should_continue, etc.
• archived_tasks — same schema, separate table for performance
• config — key/value store (replaces config.json)
• workspaces — id, path, display_name, system_prompt, active
• learnings — id, title, content, embedding_blob, created_at, utility_score, use_count, success_count
• chat_history — id, role, content, created_at (replaces chat-history.json, auto-trims to 200 rows)

Benefits:

• Single atomic transaction per write, no tmp+rename
• WAL mode: concurrent readers don't block writers (critical on Windows)
• Row-level updates: changing task state touches one row, not the whole file
• No debouncing needed for metadata — writes are fast enough to do immediately
• Schema migrations via versioned PRAGMA user_version

2. Keep PTY history as files — add configurable retention

SQLite BLOBs are not appendable — storing incremental PTY output in SQLite would require chunked rows or full rewrites. Files remain the right storage for this.

Changes to history file handling:

• Add configurable retention period (default: 30 days), exposed in Settings UI
• History files older than the retention window are automatically deleted on startup and via a periodic cleanup (e.g. daily)
• Retention setting stored in SQLite config table
• Archived task entries whose history file has been cleaned up show a "history expired" placeholder in the terminal

History file retention config:

Settings → Storage → History retention: [ 7 days | 30 days | 90 days | Forever ]

3. Migration path

On first startup after upgrade:

1. Read existing tasks.json, config.json, workspace-config.json, learnings.json, chat-history.json
2. Insert all records into SQLite
3. Rename old JSON files to .json.bak (keep for one release as fallback)
4. History .txt files stay untouched — no migration needed

Files affected

• backend/src/task-spawner.ts — remove saveTasks, loadTasks, all readFileSync/writeFileSync for metadata; replace with SQLite reads/writes
• backend/src/config-store.ts — replace JSON file with SQLite config table
• backend/src/learnings-store.ts — replace JSON file with SQLite learnings table
• backend/src/server.ts — chat history persistence
• New file: backend/src/db.ts — SQLite schema, migrations, connection singleton
• backend/src/task-spawner.ts — add history file cleanup job (retention policy)
• Frontend Settings UI — add history retention selector

What stays the same

• PTY history files (task-histories/*.txt, archived-histories/*.txt) — format and location unchanged
• Atomic write for history files (atomicWriteHistorySync) — still needed for history
• History rotation (rotateHistoryFileIfNeeded) — still needed as a hard size cap independent of retention

Performance expectations

• Task switching: already fixed by async I/O (#current PR), SQLite won't change this
• Save path: eliminates the 500ms debounce and full JSON rewrite — row updates are ~microseconds
• Startup load: single SELECT * FROM tasks instead of parsing a large JSON blob
• Windows: WAL mode eliminates the file lock contention that caused stalls

Dependencies

• better-sqlite3 — synchronous SQLite bindings, pre-built for Node.js, Windows-compatible
• Needs native rebuild step in CI for Windows (node-pre-gyp or prebuild)