Persist ReviewMonitor-owned review history and logs locally

[lynnswap]

Parent: #96
Related: #99, #100, #103, #108, #110, #111

Product contract

The Codex app-server is the live execution/event transport. It is not the durable source of truth for ReviewMonitor history, sidebar membership, lifecycle timing, or displayed logs.

ReviewMonitor commits product review state and effective ordered log entries to a local SQLite database. Sidebar, detail, and authorized MCP operations read that committed product history, so app-server omissions, reconnects, refreshes, account/runtime transitions, and process restarts cannot erase it.

Selected implementation

Use direct GRDB 7.11.1 behind the existing CodexReview owner boundary.

An actual Swift 6/macOS 26 probe compared SQLiteData 1.9.0 with direct GRDB:

• Both passed explicit-database, observation, migration rollback, error, cancellation, and close checks.
• SQLiteData required avoiding its property-wrapper/global-default initialization path for fully explicit injection and resolved a substantially larger dependency/macro graph.
• Direct GRDB exposed MainActor ValueObservation, explicit error callbacks, cancellable observation, transactions/migrations, and throwing close directly with a much smaller graph.

No GRDB type appears in a public or cross-target API.

Required owner model

• One stable product review ID owns lifecycle, terminal kind/cause, duration accuracy, final result, current attempt, manual order, and internal source/reviewer thread linkage.
• Attempt identity is composite (reviewID, attemptID); late old-attempt terminal cannot terminate the current attempt.
• Effective log rows own immutable display ordinal and last mutation revision.
• Every mutation increments one global database revision in the same transaction.
• GRDB observations update one stable CodexReviewJob projection registry; the writer never directly publishes UI state.
• UI query failure preserves last-good rows and exposes typed loading/loaded/failed state.
• App-server runtime generations are shorter-lived than the app-lifetime history/database owner.
• Raw deltas without stable upstream IDs are consumed once in connection order and never treated as a replay journal.

Retention and migration

• Memory contains all active reviews plus the newest 100 terminal reviews and pages older rows on demand.
• Completed reviews remain in SQLite until explicit confirmed deletion; Add explicit paging and deletion policy for durable review history #111 owns paging/deletion semantics.
• Existing v0.6.2 capped-text semantics are preserved and characterized; no false claim is made that all metadata/event rows fit a global 256 KiB limit.
• Protect and migrate existing ReviewMonitor local state during recovery cutover #108 imports only canonical outer review evidence into a recovery-owned destination and never mutates the current-main source.
• Authentication secrets are never stored in review/history tables.

Acceptance criteria

• One logical review produces one durable row and one human-facing log; internal reviewer JSON never becomes product content.
• Completed duration/result/log remain identical after app restart without fetching the thread from app-server.
• During app-server omission/failure/reconnect/refresh or auth/runtime transition, last committed history stays visible.
• Writer transaction failure publishes nothing; query failure preserves last-good data and visible error.
• Startup orphan reviews stop advancing and show exact, lower-bound, or unavailable duration truthfully.
• Duplicate stable receipts are idempotent; conflicting duplicates fail at the ingestion owner.
• Current live attempt, old attempt, reviewer child, and source thread cannot satisfy one another's terminal.
• Migration/open/read/write/close, paging, deletion, order, large history, process interruption, and restart are deterministically tested.
• GRDB observations/cancellables and database close leave no callback/read/write after application close.
• Signed-out and stopped/failed runtime states do not hide durable history.

[lynnswap]

Design-gate additions:

• One GRDB DatabasePool (foreign keys + WAL) is the live/temp-integration concrete.
• One ValueObservation generation reads a cross-table revision snapshot in one transaction; paging swaps generations only after the replacement's first complete snapshot, so subscription churn never publishes [].
• Loaded review IDs are monotonic for the process lifetime.
• A persistence write/observation failure is fail-closed: mutation admission closes, upstream workers are stopped and awaited, MCP waiters receive a persistence error, and UI retains last-good rows with active timers frozen as labeled lower bounds.
• Failed-state close joins worker shutdown, performs no semantic write, cancels the query, closes the pool, and preserves primary plus physical-close errors.
• reviewRuns.finalResult is the only final text owner. The effective final log row is a role/reference join; full and sparse delivery collapse to exactly one visible row.
• Final output is bounded at 256 KiB UTF-8; over-limit output fails visibly rather than truncating silently or creating unbounded eager history.

[lynnswap]

Implementation is now sequenced into four reviewed local slices after Wave 3C:

1. 4A — GRDB 7.11.1 schema/writer/global revision/final-result invariant.
2. 4B — one atomic ValueObservation generation, stable job/workspace registry, monotonic loaded window and keyset cursor.
3. 4C — Store/UI/MCP cutover, truthful duration, paging, anchor reorder, and terminal deletion.
4. 4D — startup orphan recovery, fail-closed persistence transition, and insertion into the existing Wave 3 finish/close owner.

The canonical design was clarified in fa32f5d: all runtime producers/workers join before query cancellation and DB close; preview/test data uses an optional ReviewHistoryConfiguration.bootstrap applied by normal writer transactions only to a brand-new empty temporary DB. An independent re-gate reported zero design blockers. 4A remains intentionally gated on a reviewed Wave 3C HEAD.

[lynnswap]

New prerequisite: #113 must land before this issue opens review-history.sqlite. Wave 4 must consume PreparedRecoveryEnvironment and perform last-moment DirectoryCapability revalidation before GRDB opens the path. A raw URL database open is not mergeable. Login/auth cleanup debt remains outside the review-history database.