[#17a] Session storage v2 sidecar: SQLite-backed session index with WAL, lock, stale reindex

[quangdang46]

[#17a] Session storage v2 sidecar: SQLite-backed session index with WAL, lock, stale reindex

Sub-issue of #17. The fast-resume index. Standalone deliverable: a small SQLite index that mirrors current JSONL metadata so --resume and the session picker don't have to walk every file.

Goal

• Today's session picker (src/tui/session_picker/loading.rs) does deferred file-scan loading. Loading is still slow when the directory has thousands of files. A sidecar SQLite index turns "list / search / sort" into O(log N) instead of O(N) full scans.

Schema

• File: ~/.jcode/agent/sessions.idx.db (SQLite, WAL mode enabled).
• Sibling lock file: ~/.jcode/agent/sessions.idx.lock (advisory, prevents concurrent reindex).
• Tables:

  CREATE TABLE sessions (
    id TEXT PRIMARY KEY,
    path TEXT NOT NULL,
    cwd TEXT NOT NULL,
    last_modified INTEGER NOT NULL,
    started_at INTEGER NOT NULL,
    name TEXT,
    tag TEXT,
    label TEXT,
    model TEXT,
    entry_count INTEGER,
    file_size INTEGER,
    checksum TEXT
  );
  CREATE INDEX idx_sessions_cwd_last_modified ON sessions(cwd, last_modified DESC);
  CREATE INDEX idx_sessions_tag ON sessions(tag);

Stale detection + reindex

• On read: if any JSONL file's mtime is newer than its row's last_modified, the row is stale; reindex affected rows lazily.
• On startup: if the lock file's pid is no-longer-running, reclaim and run a full directory scan in the background.

Verified jcode code paths

• Session loader: src/session.rs.
• Session picker: src/tui/session_picker.rs, src/tui/session_picker/loading.rs.

Out of scope

• Crash-resilient save → #17b
• Durability flag → #17c
• Rollback ledger → #17d
• Migration subcommand → #17e

Cross-references

• Parent: jcode session migrate: session storage migration & v2 sidecar fast-path for large sessions #17
• Siblings: #17b, #17c, #17d, #17e
• Related: Session tree branching: /tree, /fork, /clone in-place navigation #2 (tree branching), Add general-purpose jcode doctor diagnostic command #8 (doctor --only sessions)

Reference

• pi_agent_rust → "Session Indexing", "Session Store V2 Sidecar".