| 1 | # RFC: Persistence SQLite Migration |
| 2 | ... |
| 3 | |
| 4 | ### 1.1 `crates/state` — partial SQLite (rusqlite) |
| 5 | |
| 6 | **Backend**: SQLite via `rusqlite` (not sqlx). |
| 7 | **Path**: `~/.deepseek/state.db` |
| 8 | **Tables**: `threads`, `thread_dynamic_tools`, `messages`, `checkpoints`, `jobs` |
| 9 | **Also**: `session_index.jsonl` — append-only JSONL for thread-name lookups. |
| 10 | **Schema versioning**: none — table shape is versioned implicitly by the binary. |
| 11 | |
| 12 | ### 1.2 `crates/tui/src/session_manager.rs` — JSON sessions |
| 13 | |
| 14 | **Backend**: individual JSON files + atomic writes via `write_atomic`. |
| 15 | **Paths**: |
| 16 | - `~/.codewhale/sessions/{id}.json` (preferred, v0.8.44+) or `~/.deepseek/sessions/{id}.json` (fallback) |
| 17 | - `~/.deepseek/sessions/checkpoints/latest.json` — crash-recovery checkpoint |
| 18 | - `~/.deepseek/sessions/checkpoints/offline_queue.json` — offline/degraded-mode queue |
| 19 | |
| 20 | **Schema constants**: |
| 21 | - `CURRENT_SESSION_SCHEMA_VERSION: u32 = 1` (`SavedSession`) |
| 22 | - `CURRENT_QUEUE_SCHEMA_VERSION: u32 = 1` (`OfflineQueueState`) |
| 23 | |
| 24 | **Policy**: reject-newer — older binary will refuse to load data written by a newer version. |
| 25 | |
| 26 | ### 1.3 `crates/tui/src/runtime_threads.rs` — JSON runtime store |
| 27 | |
| 28 | **Backend**: per-record JSON files + append-only JSONL for events. |
| 29 | **Paths** (under `~/.deepseek/tasks/runtime/` or `DEEPSEEK_RUNTIME_DIR`): |
| 30 | - `threads/{id}.json` |
| 31 | - `turns/{id}.json` |
| 32 | - `items/{id}.json` |
| 33 | - `events/{thread_id}.jsonl` — append-only JSONL event timeline |
| 34 | - `state.json` — global monotonic sequence counter |
| 35 | |
| 36 | **Schema constants**: |
| 37 | - `CURRENT_RUNTIME_SCHEMA_VERSION: u32 = 2` |
| 38 | |
| 39 | **Policy**: reject-newer. |
| 40 | |
| 41 | ### 1.4 `crates/tui/src/task_manager.rs` — JSON task store |
| 42 | |
| 43 | **Backend**: per-record JSON files + atomic writes. |
| 44 | **Paths** (under `~/.deepseek/tasks/` or `DEEPSEEK_TASKS_DIR`): |
| 45 | - `{id}.json` — per-task records |
| 46 | - `queue.json` — queue state |
| 47 | |
| 48 | **Schema constants**: |
| 49 | - `CURRENT_TASK_SCHEMA_VERSION: u32 = 2` |
| 50 | |
| 51 | **Policy**: reject-newer. |
| 52 | |
| 53 | ### 1.5 `crates/tui/src/automation_manager.rs` — JSON automation store |
| 54 | |
| 55 | **Backend**: per-record JSON files. |
| 56 | **Paths** (under `~/.deepseek/automations/` or `DEEPSEEK_AUTOMATIONS_DIR`): |
| 57 | - `{id}.json` |
| 58 | |
| 59 | **Schema constants**: |
| 60 | - `CURRENT_AUTOMATION_SCHEMA_VERSION: u32 = 1` |
| 61 | |
| 62 | ### 1.6 `crates/tui/src/audit.rs` — JSONL audit log |
| 63 | |
| 64 | **Backend**: append-only JSONL with fsync after each event. |
| 65 | **Path**: `~/.deepseek/audit.log` |
| 66 | **Schema**: no version field — each line is a `{"ts", "event", "details"}` blob. |
| 67 | |
| 68 | ### 1.7 Summary of issues |
| 69 | |
| 70 | | Area | Backend | Schema Version | Write Strategy | Queryability | |
| 71 | |------|---------|---------------|----------------|-------------| |
| 72 | | state (threads/messages/jobs) | SQLite | implicit | direct SQL | SQL | |
| 73 | | sessions | JSON files | v1 | atomic rename | file scan | |
| 74 | | runtime threads/turns/items | JSON files | v2 | atomic rename | file scan | |
| 75 | | runtime events | JSONL | v2 | append+fsync | linear scan | |
| 76 | | tasks | JSON files | v2 | atomic rename | file scan | |
| 77 | | automations | JSON files | v1 | atomic rename | file scan | |
| 78 | | audit | JSONL | none | append+fsync | linear scan | |
| 79 | |
| 80 | **Key pain points**: |
| 81 | 1. **Listing** threads/sessions/tasks requires scanning directories and deserializing every file. |
| 82 | 2. **Filtering** (e.g., "all failed tasks in last 7 days") requires full scans. |
| 83 | 3. **No transactional consistency** — a crash between saving a turn and its items can leave orphans. |
| 84 | 4. **Event timeline growth** — JSONL append is O(n) for replay; no indexing. |
| 85 | 5. **Six different schema version constants** across four modules, each with the same reject-newer policy. |
| 86 | |
| 87 |