[Loops] Reconciliation cycle blocks SQLite writes for ~20 s; tool results that land inside it are lost #689

Version: compozy 0.3.0-beta.27, macOS, one daemon. ~/.compozy/compozy.db is 337 MB, plus a 17 MB WAL.

Symptom

A loop action tool runs to the end, and its side effects are on disk, but the daemon fails to persist the result:

{"level":"ERROR","msg":"daemon: execute loop action run",
 "task_id":"loop.looprun-18818d74440c30de.g1.node.watch.0",
 "error":"call action tool \"ext__work__deliver_watch\": tool \"ext__work__deliver_watch\" observability emit failed\nstore: fail task run lease transaction: store: begin immediate sqlite write: context deadline exceeded"}

Afterwards the node never settles correctly. We saw three outcomes:

1. The output goes to enqueued and stays there. The node is never dispatched again, even with retry.max_attempts: 3 and attempt 1 (loop.looprun-42b94147209f7373.g1.node.prepare.0).
2. The output stays running until the node timeout. In our case the timeout was 1440m30s, so the run, its parent run-loop, and the grandparent all hang (loop.looprun-18818d74440c30de.g1.node.watch.0).
3. The node fails with failure_class: transport and is retried. The retry runs a tool that has already run once.

Correlation with the reconciliation cycle

The log has one line per reconciliation cycle:

{"level":"INFO","msg":"daemon: Loop reconciliation cycle complete","runs_examined":0,"records_settled":0,"orphans_repaired":0,"provenance_backfilled":0,"duration_ms":20426}

All 7 lost results on 2026-10-01 fell inside a cycle window (from complete − duration_ms to complete), each 10–13 s after the cycle started:

The same windows also show session supervision failed: store: sweep heartbeat wake events: database is locked (5) (SQLITE_BUSY). CLI calls made during them fail with SQLITE_BUSY (exit 69) or context deadline exceeded.

The cycle keeps getting longer

The cycle runs every ~72 s (median), and every cycle reports runs_examined: 0.

The losses began on 09-29 between 18:00 and 20:00 local time. That is when the median duration passed ~10 s, which suggests the result write waits a bounded time (~15 s in the reproduction below) that the longer cycles outlast. On 10-01 the cycles filled ~23% of wall-clock time. On that day, from 15:00Z on, about 2% of node attempts lost their result (9 of 475).

Row counts at the time: loop_runs 689, loop_generations 1466, loop_node_attempts 3728, task_runs 9421, task_events 55882, event_summaries 120266, sessions 787. We found no CLI command that prunes history. We have not changed reconcile_interval.

Reproduction (no fault injection)

Use a test tool that returns at a chosen offset from the start of the next reconciliation cycle. The start is predicted from the complete − duration_ms lines.

There is also no way to recover such a node from the CLI:

• loop node resume --mode plain|immediate on the enqueued node is refused with exit 78, node_not_paused, allowed=pause,cancel.
• loop node pause --mode cancel followed by resume --mode immediate is accepted, but the tool is never invoked again (13 and 26 min observed).
• loop node cancel fails the run.

Related: the automation scheduler spins on SQLITE_BUSY

When the database is busy, the automation scheduler retries the job claim in a tight loop: automation.scheduler.dispatch_failed … claim scheduled job … database is locked (5), about 18k lines in one minute. The log rotated 5 times in 15 s, and since only 5 backups are kept, the earlier history was overwritten.

Expected

• A reconciliation cycle that examines 0 runs should not hold the write lock for seconds, and its cost should not grow with history (an indexed or incremental scan, or short transactions).
• When the daemon cannot persist a tool result, it should retry the write instead of dropping it. A node whose tool already returned should never stay running or enqueued, and should not be retried as a transport failure.

Workaround on our side

An external supervisor polls the run tree and cancels or recovers stuck nodes. It treats the symptom only.

Related

#678 (sustained SQLite contention degrading session tools) looks like the same lock pressure seen from the session side.