V3 Issue #264 — Version SQLite State and Migrate Safely

Issue-specific implementation plan for #264, prerequisite to #241 and #242.

Goal: Make SQLite schema and persisted payload upgrades explicit, ordered, transactional, recoverable, and safe for existing users.

Architecture: Extend SQLiteDatabase and existing repositories. Keep SQLite authoritative; preserve old task and interaction APIs. Add no placeholder plan service in this issue. Working Plan tables will be introduced by #241 when there is a concrete consumer.

Compatibility policy: Upgrade recognized historical schemas automatically after creating a restrictive, consistent SQLite backup. Reject future versions and malformed/inconsistent core schemas without modifying the source database. Keep unrelated extension tables intact. Never restore or replay work automatically.

Caller and data map

• openkyrozen/persistence/database.py: schema initialization, version tracking, connection setup, locking, and SQLite transaction behavior.
• openkyrozen/persistence/tasks.py: legacy replace_tasks() currently deletes and reinserts using autocommit; make that replacement atomic.
• openkyrozen/persistence/events.py and openkyrozen/security/permissions.py: persisted event and approval-decision records; approval history must never become reusable authority.
• openkyrozen/persistence/history.py, task recovery, and JSON payload loaders: preserve existing state and reject unsupported structured payload versions at their trust boundary.
• Existing persistence tests and historical schemas: verify migrations from shipped schema versions and additive same-version variants without dropping extension tables.

Task 1 — Establish historical and failure regressions

• [ ] Capture minimal schema-1, schema-2, and schema-3 databases from Git history, plus shipped additive-schema variants, as test fixtures or fixture builders.
• [ ] Add tests for fresh initialization, upgrades, idempotence, preserved scoped records, unrelated tables, concurrent initializers, and compacted migration-marker upgrades.
• [ ] Add tests proving future schema versions and inconsistent core schemas (including task columns used by repositories) fail before writes, and failed migration leaves original data/version intact.
• [ ] Add a task replacement regression proving a later invalid row cannot leave a partially deleted or replaced scope.
• [ ] Run the focused tests and record the expected failures before production edits.

Task 2 — Add guarded, ordered SQLite migrations

• [ ] Replace the create-all-and-stamp-version path with ordered migrations from the recognized legacy versions to the current version.
• [ ] Inspect schema version and core-table consistency before any mutation; preserve additive/extension tables and reject future or unsupported layouts explicitly. Accept a contiguous suffix when a compacted latest-version marker is followed by new migrations, while rejecting interior gaps.
• [ ] Create a restrictive-permission backup that includes committed WAL state before changing an existing database; report only its path.
• [ ] Serialize concurrent migration attempts, recheck version after acquiring the lock, and apply each migration plus its version record in one explicit transaction. Avoid executescript() where its implicit commits break atomicity.
• [ ] Keep current schema objects and scoped records intact; add no Working Plan tables until #241.

Task 3 — Harden persisted replacement and payload compatibility

• [ ] Make replace_tasks() delete and insert inside one transaction so failure rolls back the prior state.
• [ ] Persist and validate explicit versions for newly versioned structured payloads used by this migration scope; reject unknown versions safely.
• [ ] Persist each approval outcome once as scoped history only. Do not restore a token, mode override, or implicit permission from approval events.
• [ ] Keep recovery load-only: migration or restart cannot execute work or mark it successful.
• [ ] Document backup restoration and downgrade limitations in the existing persistence documentation.

Task 4 — Verify and deliver #264

• [ ] Run targeted migration, rollback, backup, replacement, approval-history, and recovery tests on Python 3.12 and 3.13.
• [ ] Run full tests, make check, make lint, make docs-check, Go TUI tests, offline agent/subagent/TUI acceptance, and Docker persistence/restart smoke where available.
• [ ] Review the complete diff independently; reproduce actionable findings and fix material defects with failing regressions.
• [ ] Sign the commit with the configured GPG key, verify locally and on GitHub, push Evan/v3-264-state-migrations, open and attach one PR, then wait for final-head CI and review. Leave it unmerged.

Risks and acceptance

PASS requires recognized legacy state to upgrade without losing scoped records, unsupported state to remain untouched, rollback to preserve source data, and approval history to remain non-authoritative. Automatic backups consume disk proportional to database size; operators can restore the reported backup to recover from a failed future application downgrade. No new dependency, paid provider call, automatic recovery execution, or placeholder plan service is in scope.