fix(runtime): make local operation state transactional and durable

[doublewhy]

Gap Claim

The local control-plane JSON store performs unlocked whole-file read/modify/write operations. Concurrent writers lose records; replacement is not file-and-directory fsynced; idempotency lookup is linear. API-404 cannot claim durable or idempotent operation state.

Existing Surface Audit

Reviewed local store serialization, audit append, state transition, idempotency lookup, control-plane execution, repository contracts, and issue #8. Adding an in-process lock would not protect multiple workers or crash consistency.

Lineage and Precedent

Implements the durable-state portion of API-404 while preserving the existing store protocol and serialized record shapes.

Literature and Practice

Use SQLite ACID transactions, WAL mode, unique constraints, foreign-key integrity, busy timeout, and explicit checkpoint/backup behavior rather than inventing a file transaction protocol.

Alternatives

1. Thread lock around JSON: rejected because processes still race. 2. File lock plus fsync/versioning: workable but retains O(n) rewrites and complex recovery. 3. SQLite WAL with indexed constraints: chosen.

Chosen Architecture

Persist operations and audit events in normalized SQLite tables. Enforce unique idempotency keys transactionally, use compare-and-set state transitions, indexed reads, WAL and durable sync. Import legacy JSON under an exclusive migration lock, verify counts/digests, retain a timestamped backup, and switch atomically only after validation.

Documentation Defense

Document persistence guarantees, single-host scope, migration/recovery, concurrency model, backup behavior, and what is not a distributed consensus guarantee.

Verification Plan

Add thread/process lost-update tests, same-key exactly-once tests, crash/restart and interrupted migration tests, corruption diagnostics, state-transition races, audit ordering, and scale budgets.

Parent: #8

[doublewhy]

Integration review addendum: recovery and topology gaps

Gap Claim

Review of the completed SQLite remediation found that the generic operation path still performs four independently durable steps: claim RUNNING, invoke the backend, save the snapshot, and save terminal status. A process exit between those boundaries can leave an applied backend effect with stale state, or a durable new snapshot whose operation remains RUNNING. Restart currently loads that record unchanged and an idempotent retry returns it without reconciling or replaying. API-404 therefore cannot yet claim restart-safe idempotency.

The same review found that each RuntimeControlPlane permanently caches snapshot and operation maps while HTTP mutation serialization is application-local. A shared SQLite file does not make multiple app workers coherent; two workers can apply from stale snapshots, and status reads can remain stale. The supported topology must fail closed as one process until a revision-CAS/lease design exists.

Existing Surface Audit

Checked RuntimeControlPlane.__init__, _idempotent_receipt, execute_operation, participant execution, ControlPlaneStore, LocalControlPlaneStore, participant transition CAS methods, API call serialization, operation schemas, API-404, and the issue #1092/#1093 decisions. Participant transition commits already provide the correct atomic snapshot/record pattern, but generic terminal commits and startup reconciliation are absent. No durable backend-effect ledger exists, so blind automatic replay is unsafe.

Lineage and Precedent

This extends the existing API-404 operation lifecycle and SQLite store; it does not add another queue or state family. SQLite transactions remain the single-host durable boundary. The existing FAILED terminal state is used with a stable recovery diagnostic to represent an interrupted, explicitly non-successful operation without changing the published enum/schema.

Literature and Practice

• SQLite transaction semantics: https://www.sqlite.org/lang_transaction.html
• SQLite atomic commit model: https://www.sqlite.org/atomiccommit.html
• Uvicorn process-worker deployment boundary: https://www.uvicorn.org/deployment/

Alternatives

1. Leave orphan RUNNING records and rely on operators: rejected because retries remain stuck and the status falsely implies live work.
2. Automatically replay every orphan: rejected because a backend side effect may already have happened.
3. Introduce a new public indeterminate enum immediately: rejected for this patch because it changes the published schema and every consumer.
4. Chosen: atomically commit snapshot plus terminal status, reconcile startup orphans to FAILED with a stable operation-interrupted diagnostic, preserve the idempotency claim so retries return that explicit non-success result, and enforce an exclusive single-process local-store lease.

Chosen Architecture

Add a store-level atomic terminal commit. On startup, reconcile every persisted RUNNING record before exposing the control plane. Never invoke the backend during reconciliation. Preserve the operation id and idempotency key, change only status to terminal failure, and include stable recovery evidence. Acquire a nonblocking process lease for the local database and reject a second live owner; release it on explicit close/context exit and finalization. This is process-topology enforcement, not cross-process CAS.

Documentation Defense

Amend the #1092/#1093 decisions and runtime architecture to state crash boundaries, orphan semantics, lease lifecycle, single-process scope, and why recovery cannot prove whether an external effect occurred. No public schema migration is required.

Verification Plan

Inject failures after claim, backend return, snapshot serialization/write, and terminal commit; restart at every boundary; verify no terminal snapshot/status split; verify retries never re-invoke the backend; verify recovered status is explicit non-success; verify one local-store process owner and lease release/reacquisition; retain all legacy migration, concurrency, participant transition, API, Ruff, policy, governance, and full verification gates.

[Brad-Edwards]

Deferred pending the runtime control-plane architecture work in #1151. The design proposed here and in PR #1136 will be reconsidered once #1151 establishes the applicable control-plane architectures and determines whether this design fits them.