Fix SQLite restore readback sidecars that block interruption recovery #102

Confirmed restore interruption defect

Parent: #90. Initiative: #12. Required for trustworthy transition/recovery before #23 completion.

At delivered main 32fd0ab2db99441b5c4bb66973aaefb9deff3dec, trace_gc.phase_transition._file_logical() uses SQLite mode=ro. On the verified Windows runtime, reading a restored WAL-mode database creates an empty graph.sqlite3-wal and a 32,768-byte graph.sqlite3-shm. These remain after the read-only connection closes.

_restore_target_locked() permits the two databases, restore receipt and recognized pending/failed backup artifacts, but excludes these sidecars. An interruption after the public restore completes and before the external coordinator durably records its result therefore leaves a target that the same authorized restore request rejects as partial target has unknown content.

The actual restore completed successfully; its archived original files, export backups, budget reservation and logical graph contents are verified. This issue describes its independently reproduced recovery gap, not a failed actual restore. The actual sidecars were neither deleted nor modified.

Minimal mechanics reproduction

From the delivered checkout with core dependencies installed, run the following Python with bytecode writes disabled. It uses an owned temporary database only:

from pathlib import Path
import sqlite3
import tempfile
from trace_gc.phase_transition import _file_logical

with tempfile.TemporaryDirectory() as directory:
    database = Path(directory) / "graph.sqlite3"
    connection = sqlite3.connect(database)
    connection.execute("PRAGMA journal_mode=WAL")
    connection.execute("CREATE TABLE example (id INTEGER PRIMARY KEY)")
    connection.execute("INSERT INTO example VALUES (1)")
    connection.commit()
    connection.close()
    assert not database.with_name(database.name + "-wal").exists()
    _file_logical(database)
    print({p.name: p.stat().st_size for p in Path(directory).iterdir()})

The complete disposable restore reproduction additionally reaches the real partial-target allowlist under a synthetic authority/capsule fixture. It reproduces rejection on exact reentry. This mechanics fixture intentionally does not establish cryptographic or empirical qualification; the delivered signed transition tests must cover the complete authorization contract.

Proposed causal repair

For these quiescent restored files, close all destination writers, reject preexisting WAL/SHM, and use mode=ro&immutable=1 for logical readback. Check again after close that no sidecar appeared. Do not silently ignore a live/nonempty WAL, broaden the inventory allowlist indiscriminately, remove actual target files, or weaken phase identity/ownership gates.

The reviewed candidate changes only _file_logical() and passes fresh restore, same-request resume and foreign-WAL rejection fixtures. Independent assistant scoped review passed. Its production suitability still requires normal repository tests and delivery.

Evidence and identity

• Frozen public helper SHA-256: 5acdec9e5c22eac6817a7846bc670b2f51568eca1f47a3638d410161552f40c9.
• Disposable causal handoff: d5e8b80d3bb3d3eebb1a8f2a12d638600af6e3496dee3fa43281ae841396d33c.
• Proposed patch: 85a8cbbd3619ae916ebefeaba039aafb3d25aab9dd05c8cdd2b1709bf853add8.
• Independent scoped review: 0aeaf0806478d29d1cd1b306de2099fde0530d72623bb66a8f75c252d4dbf082.
• Actual restore and independent fresh verification.

This repair changes the implementation identity. Preserve the frozen live transition checkout, signed releases and receipts. Integrate in an isolated checkout; future use requires a freshly reviewed source identity and phase authorization. Do not hotpatch the existing signed phase or use an old authorization with changed source.

Acceptance criteria

• Add a regression that fails on the delivered code and covers a real WAL-mode restored database.
• Fresh authorized restore leaves the two restored stores and its receipt, with no WAL/SHM created by logical verification.
• Simulate loss of the coordinator result after public restore; the identical signed request reconciles with identical logical contents and receipt.
• Reject nonempty WAL, unexpected SHM, changed phase binding, changed capsule, stale/conflicting authorization and foreign target content.
• Preserve the old event chain, graph/catalog/status contents, pending immutable requests and existing reservation; consume no additional budget or model call.
• Cover the Windows behavior and supported SQLite/Python environments; retain all existing phase and interruption regressions.
• Review final diff; complete core/PDF release and package gates, verified signing, exact-head hosted CI, merge and postmerge verification.
• Document identity migration requirements and any handling needed for targets restored by the previous implementation; keep actual historical artifacts untouched and Support explicitly authorized PaperPilot runtime transitions without losing history #90/Implement and validate a real paper-to-graph-to-cited-answer pilot #23 open until their remaining criteria pass.

Delivered repair and acceptance readback — 2026-10-01

Delivered in PR #103 and the isolated adversarial-test follow-up PR #104. Final verified merge: 7f01f3389e3360ba89ac780f585c9f83c9ec2aae; tree: d98347f701576931a71b0c14a772662fc80bd181. Postmerge CI passed Python 3.11, 3.12 and 3.13.

The eight acceptance criteria above are met for this public code repair. The WAL regression reproduces actual Windows SQLite sidecar behavior. Signed authored restore/reentry tests cover identical-request reconciliation and unchanged budget; negative cases independently isolate capsule drift and freshly signed conflicting authorizations. These restore tests use a synthetic capsule and fence fixture. They establish the API mechanics, not a new real historical-phase restore.

Final signed-head release gate: 1,403 tests run, 1,395 passed, eight documented optional skips, zero failures/errors. PDF coverage passed. Skips comprise two optional NumPy tests, five Windows symlink privilege tests and one external pinned-core test. Package validation, owned installation, CLI version and 66 installed-file comparisons passed. Source ZIP SHA-256 96d2f4f282732d8db4abc6827c2dab74641726bce5f6faee1eeb97394af01645: all 619 members match signed Git and the clean delivered tree.

The delivered immutable reader also verified the four actual closed historical backup databases and preserved their byte/logical identities, 39 checkpoint events, 11 journal events, 19 completed requests, eight source statuses and the existing reservation of one model call / 14,120 request bytes. This was read-only verification: no new restore, import, activation, provider call or graph write. The historical provider outcome remains UNKNOWN_UNATTESTED; its reservation is not evidence of a provider outcome.

Evidence SHA-256: final acceptance audit bed1fec8a47fd465f8ead3087776f3380362b63d41997b9989bdc5f88486173c; final full-validation receipt c1f06a79788fbd13ab2994a1c09fb58bcd4db30ed47f386b3e24fc59ed8bc20a; postmerge/package readback 5671f5b21d53d40555f683d96df573c248b1c459a72cd9746cb2ba66815016bb; actual closed-capsule readback ffc5555cb177b566b415dc67f403091ddc4fc5c78f8579caca080207115731c4; independent actual-capsule review e5bc1aae5be77d9d62bcbeb5b464f14b49a037050b8cc5bfca55d2e6dce29be9.

The frozen historical 32fd transition checkout, its old sidecars, signed releases and receipts remain preserved. The new helper changes implementation identity and requires separately reviewed phase authorization before any future use. Closing this repair leaves #90, #23 and epic #12 open for their remaining real recovery and empirical acceptance gates.