complete-task rollback leaves orphaned verification_evidence rows

[igouss]

Summary

When complete-task.ts fails during disk render, the rollback path resets task status to "pending" but does not clean up verification_evidence rows inserted in the same transaction.

Impact

After a disk-render failure and rollback:

• Task status is pending (correct)
• Verification evidence rows exist pointing to a pending task (incorrect — from a rolled-back completion attempt)
• On retry, insertVerificationEvidence INSERTs additional rows (no ON CONFLICT deduplication), so evidence accumulates across attempts

Root Cause

complete-task.ts:246-263 — The rollback at lines 251-260 runs a raw UPDATE tasks SET status = 'pending' but does not DELETE FROM verification_evidence for the same task.

The original transaction (lines 155-208) calls both insertTask() and insertVerificationEvidence() — both succeed and commit. The "rollback" is actually a compensating write after commit, not a true rollback. It compensates the task status but forgets the evidence.

Fix

Option A: Add DELETE FROM verification_evidence WHERE milestone_id = :mid AND slice_id = :sid AND task_id = :tid to the rollback path.

Option B (better): Use a SQLite savepoint or restructure so disk render happens inside the transaction, allowing a real rollback on failure.

Option C: Make insertVerificationEvidence use INSERT OR REPLACE keyed on (milestone_id, slice_id, task_id, command) so retries are idempotent.

Files

• src/resources/extensions/gsd/tools/complete-task.ts — lines 155-208 (transaction), 246-263 (rollback)

Confidence

83%

[igouss]

Investigation Results — CONFIRMED with detail

Transaction flow in complete-task.ts

Inside transaction (lines 155-208):

• Line 160-175: State machine guards (milestone/slice/task status checks)
• Line 183-195: insertTask() with status "complete"
• Line 197-207: insertVerificationEvidence() loop — plain INSERT, no UPSERT

Outside transaction (lines 214-263):

• Line 235: saveFile() writes SUMMARY.md to disk
• Line 246-263: Catch block if disk write fails

Rollback path (lines 251-260):

• Line 254: UPDATE tasks SET status = 'pending' via raw _getAdapter() SQL
• No DELETE of verification_evidence rows
• No CASCADE defined on the foreign key (gsd-db.ts:315-328)

Evidence accumulation on retry

insertVerificationEvidence at gsd-db.ts:1386-1409 uses plain INSERT INTO, not INSERT OR REPLACE. No unique constraint on (milestone_id, slice_id, task_id, command). Each retry attempt adds new rows.

After 3 failed disk renders + retries:

• Task status: pending (correct — rolled back each time)
• Evidence rows: 3x the expected count (one set per attempt, never cleaned up)

Recommended fix

Option C from the original issue is cleanest: add a unique index on verification_evidence(milestone_id, slice_id, task_id, command) and change insertVerificationEvidence to INSERT OR REPLACE. This makes retries idempotent without needing explicit cleanup in the rollback path.

Alternatively, wrap the rollback in its own transaction that both resets task status AND deletes evidence rows.