bug(storage): migration 0007 writable_schema string-replace silently fails if CHECK text drifts

[fireddd]

Bug

Migration 0007_allow_implemented_harnesses.sql uses PRAGMA writable_schema = ON and UPDATE sqlite_master SET sql = replace(sql, ...) to widen the sessions.harness CHECK constraint. The replace() is a literal string match against the exact CHECK text from migration 0001. If the CHECK constraint text doesn't match character-for-character (different whitespace, quoting, prior manual edit, or a hypothetical intermediate migration), the replace() silently returns the original string unchanged. The migration succeeds (goose sees no SQL error), but the schema is NOT updated.

Analyzed against: 96d1649 (current main)
Confidence: High — the mechanism is inherently fragile; one mismatch = silent no-op.

Root Cause

backend/internal/storage/sqlite/migrations/0007_allow_implemented_harnesses.sql:15-21:

UPDATE sqlite_master
SET sql = replace(
    sql,
    'CHECK (harness IN ('''', ''claude-code'', ''codex'', ''aider'', ''opencode''))',
    'CHECK (harness IN ('''', ''claude-code'', ..., ''autohand''))'
)
WHERE type = 'table' AND name = 'sessions';

If the stored CREATE TABLE text for sessions has any deviation from the expected substring (e.g., different quoting style, extra whitespace, or an intermediate migration already modified it), replace() matches nothing and returns the input unchanged. The UPDATE affects 1 row (the row exists), so goose considers the migration successful.

Reproduction

Scenario 1 — Normal path (works today):

Run fresh: 0001 creates CHECK → 0007 replace matches → new harnesses allowed ✓

Scenario 2 — Drift (silent failure):

1. Apply 0001-0006
2. Manually compact/reformat the sessions table (e.g., via .dump + reimport, or a tool that normalizes whitespace)
3. Apply 0007 → replace() doesn't match → CHECK unchanged → 0007 "succeeds"
4. ao spawn --harness grok → SQLITE_CONSTRAINT: CHECK constraint failed ✗

Scenario 3 — Future migration adds a column to sessions:

If a migration between 0001 and 0007 uses ALTER TABLE to modify the sessions table, 
SQLite may normalize the stored CREATE TABLE text, breaking the exact match.

Impact

If the replace fails silently, newer agent harnesses (grok, droid, amp, agy, crush, cursor, qwen, copilot, goose, auggie, continue, devin, cline, kimi, kiro, kilocode, vibe, pi, autohand) will be rejected by the CHECK constraint, causing InsertSession to fail with an opaque constraint violation at runtime.

Suggested Fix

After the UPDATE sqlite_master and PRAGMA writable_schema = RESET, add a verification query:

SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'sessions';

And assert (in Go migration code or a post-migration check) that the new harness values appear in the result. Alternatively, consider migrating to ALTER TABLE ... DROP CONSTRAINT + ALTER TABLE ... ADD CONSTRAINT when SQLite adds that support, or use the twelve-step ALTER TABLE process (create new table with correct CHECK, copy data, drop old, rename).