Serialize the complete SQLite initialization sequence

[ipo]

Outcome

Serialize the complete database open, WAL-repair, and migration sequence across competing Codex processes while preserving current recovery and connection reuse.

Governing evidence

Closed issue #23 and commits 390e0706d0, a7569600dd, and 0e4f62e125; upstream centralizes connections and migration locking but not the complete sequence.

The issue and this evidence define required behavior. Port functional intent onto the current upstream architecture; patch identity is irrelevant.

Non-goals

Do not add a second database, disable WAL, or broaden the lock beyond initialization.

Acceptance criteria and verification

Multi-process integration tests demonstrate concurrent clean startup, WAL recovery, migration failure and retry, lock release on error/cancellation, and no corruption or deadlock. Run the smallest focused just test -p <crate> checks that cover the changed crates under the repository reduced-priority wrapper. Regenerate schemas or snapshots only when this ticket changes them. For a large Rust change run scoped just fix -p <crate>, then just fmt; do not rerun tests after fix/format. Remove disposable build artifacts afterward.

Likely areas

State connection initialization, migration/WAL recovery, and process-concurrency tests.

Dependencies

Fully blocked by #81.

Do not begin ticket-owned mutation until every dependency is integrated; their landed contracts are authoritative.

Blocks #102, #104.

Delivery

The accepted result must integrate as exactly one issue-linked commit with subject fix(state): serialize SQLite initialization. Tests, snapshots, generated files, and fixes belong in that commit; adaptation or test-only follow-up commits are not permitted.

Agent routing

agent_level: 5

Agent signature

gpt-5.6-sol@high:01a07441-98af-75c1-9950-8af46deb76de

[ipo]

Implementation started in ticket/101-sqlite-initialization from a7dc464980.

[ipo]

IMPLEMENTATION_READY

Commit: ee9917ec9a7279836c136dd1057562c5cf400e5d on ticket/101-sqlite-initialization (pushed), exactly one commit from a7dc4649802cd9419197711012e75bcef66608ab.

Implemented:

• Per-database owner-only advisory sidecar lock around the complete centralized writable open, WAL setup/repair, legacy repair, and SQLx migration run. The guard drops before the pool is returned, so ordinary database use remains unlocked; errors and cancellation/process termination release it by RAII.
• Bounded 60-second async lock acquisition with jitter and existing low-cardinality init telemetry.
• Real subprocess coverage for 8-way cold initialization, 6-way WAL repair, migration failure followed by concurrent retry, and termination of a lock holder followed by waiter progress. Each scenario validates successful child exits plus WAL mode, migration checksums, and PRAGMA integrity_check = ok.

Exact checks:

• Reduced-priority just test -p codex-state sqlite::tests:: using disposable CARGO_TARGET_DIR: 4/4 passed in 24.526s after the final WAL-fixture refinement.
• Reduced-priority just test -p codex-state: 192/192 passed, 1 intentionally skipped. This full run preceded only the test-fixture narrowing; the final focused subprocess suite above was rerun afterward.
• Reduced-priority just fix -p codex-state: passed with no lint findings requiring further edits.
• Reduced-priority just fmt: passed; tests were not rerun afterward per repository policy.
• git diff --check: passed before commit.

Artifacts:

• No Cargo dependency/lockfile, schema, snapshot, migration SQL, or generated-file changes.
• Disposable target /mnt/ink_nvme/worktrees/.ticket101-target.v6OZBk (peak 4.7 GiB) removed; four temp homes retained by early failed harness attempts were also removed.

Caveats:

• Validated locally on Linux only; macOS and Windows file-lock behavior remains for CI.
• SQLx 0.9.0 SQLite Migrate::lock/unlock are no-ops, so the sidecar lock remains necessary for the pre-migration WAL/open portion and whole-run serialization.

Agent: gpt-5.6-sol@high:01a07482-180b-7ba2-abe8-bd9c631ad2bf

[ipo]

ACCEPT

Candidate ee9917ec9a7279836c136dd1057562c5cf400e5d satisfies #101.

• Commit/diff: exactly one issue-linked commit above a7dc4649802cd9419197711012e75bcef66608ab; the complete 491-line diff is limited to state/src/runtime.rs, state/src/sqlite.rs, and state/src/sqlite_tests.rs. git diff --check passes and the remote ticket ref matches the candidate.
• Serialization and scope: each runtime database acquires its own owner-only .init.lock before open_read_write_pool, so initial connection establishment (including WAL setup/recovery), legacy migration repair, and the complete SQLx migration run share one cross-process critical section. The guard drops when open_runtime_db returns the same reusable pool; ordinary pool use is outside the lock. Acquisition is jittered and bounded at 60 seconds. SQLx 0.9.0 SQLite migration lock/unlock are confirmed no-ops, making this sidecar the effective serialization boundary.
• Failure/cancellation release: migration errors close the pool while the guard remains held, then RAII drops the lock. Dropping/aborting the initialization future drops the sole File guard; process death closes the descriptor. The isolated termination test passed in 6.112s and demonstrated waiter progress after killing the holder.
• Independent reduced-priority checks, using a disposable target: 8-way cold initialization passed in 29.470s; concurrent rollback-journal-to-WAL repair passed in 2.534s; intentional migration failure followed by four concurrent retries passed in 9.656s; each validates child exits and the relevant integrity/migration invariants.
• Independent abandoned-WAL probe: after an abrupt writer exit left a nonempty WAL containing a committed row, six candidate initializer subprocesses passed 6/6; inspection reported journal=wal, integrity=ok, preserved survives-crash, and one successful expected migration record.
• Deadlock/corruption: all isolated bounded subprocess scenarios completed, and both submitted and independent recovery checks returned PRAGMA integrity_check = ok. Only one database lock is owned per initialization call, so no cross-database lock cycle is introduced.

One aggregate audit invocation timed out while several unrelated worktrees were compiling: Nextest scheduled the four parent tests as separate processes, so their process-local semaphore did not prevent combined child fan-out. Every scenario passed when independently isolated under the same mandated resource wrapper; this was operational host saturation and did not reproduce as a candidate behavior failure. The disposable 3.5 GiB target, WAL-probe home, and Cargo test-induced lockfile rewrite were removed; the ticket worktree is clean.

Agent: gpt-5.6-sol@high:audit-101

[ipo]

Integrated as ee9917ec9a7279836c136dd1057562c5cf400e5d on patch-stack/rust-v0.153.4. Focused four-process contention tests and the 192-test codex-state suite passed; independent audit also passed an abandoned-WAL recovery probe and verified lock release/scope. The integrated commit is the ticket’s sole commit.