Problem
Specsmith tracks .specsmith/esdb.sqlite3 while opening it in WAL mode. Its -wal and -shm sidecars are not tracked, so a commit/check-out can omit committed data. SQLite documents that WAL uses these sidecars and requires all processes to be on one host; it is not suitable for a network filesystem: https://www.sqlite.org/wal.html. Git cannot perform a semantic three-way merge of SQLite binaries: https://git-scm.com/docs/gitattributes.
Session persistence also remains legacy session-state.json and conversation-history.jsonl, although the module declares ESDB migration as outstanding. This produces unclear save/load behavior and branch conflicts.
Required approach
Make a deterministic, append-friendly, text-based session/event representation the Git-mergeable source of truth. Treat SQLite as a local derived index/cache, not a file Git must merge. Implement explicit replay, de-duplication, ordering, schema-versioning, and conflict reporting; do not use a custom binary SQLite merge driver. Use SQLite's online backup API for consistent snapshots/recovery rather than copying a live WAL database: https://www.sqlite.org/backup.html.
Acceptance criteria
- Clean checkout can rebuild the ESDB and restore the last valid session from committed canonical artifacts.
- Two branches independently saving session/ESDB events can merge without lost or duplicated events; integration tests simulate the divergence and merge/replay.
- SQLite database and WAL sidecars are local/derived and never require a Git binary conflict resolution.
- Save/load is atomic, validates schema and integrity, and reports malformed or incompatible event data without silently discarding valid state.
- Migration preserves existing projects' records and session history, with a rollback/recovery path and documentation of the repository policy.
- Tests cover Windows, Linux, and macOS file/locking semantics where applicable.
Problem
Specsmith tracks .specsmith/esdb.sqlite3 while opening it in WAL mode. Its -wal and -shm sidecars are not tracked, so a commit/check-out can omit committed data. SQLite documents that WAL uses these sidecars and requires all processes to be on one host; it is not suitable for a network filesystem: https://www.sqlite.org/wal.html. Git cannot perform a semantic three-way merge of SQLite binaries: https://git-scm.com/docs/gitattributes.
Session persistence also remains legacy session-state.json and conversation-history.jsonl, although the module declares ESDB migration as outstanding. This produces unclear save/load behavior and branch conflicts.
Required approach
Make a deterministic, append-friendly, text-based session/event representation the Git-mergeable source of truth. Treat SQLite as a local derived index/cache, not a file Git must merge. Implement explicit replay, de-duplication, ordering, schema-versioning, and conflict reporting; do not use a custom binary SQLite merge driver. Use SQLite's online backup API for consistent snapshots/recovery rather than copying a live WAL database: https://www.sqlite.org/backup.html.
Acceptance criteria