Context

During the 2026-04-23 wipe-mechanism audit (session 24aa1fa1), I identified a silent-wipe fallback path in the SQLite storage layer. Not today's culprit but captured for observability + future schema-migration work. Lower priority than the sibling tickets (upsertNode lazy-load, PermissionService stub, GraphService self-seed).

The Problem

ai/graph/storage/SQLite.mjs:56-69:

initSchema() {
    let upgradeRequired = false;

    try {
        this.db.prepare('SELECT log_id FROM GraphLog LIMIT 1').get();
    } catch (e) {
        upgradeRequired = true;
    }

    if (upgradeRequired) {
        this.db.exec('DROP TABLE IF EXISTS Edges');
        this.db.exec('DROP TABLE IF EXISTS Nodes');
    }
    // ... CREATE TABLE IF NOT EXISTS for Nodes, Edges, GraphLog, triggers
}

Three concerns:

1. Silent wipe: if the GraphLog SELECT throws for ANY reason (expected: "first-boot, table doesn't exist"; unexpected: WAL corruption, permission error, disk flake, concurrent lock contention), Nodes + Edges get dropped. No log. No audit trail. Operator has no diagnostic signal that this happened.
2. No schema version tracking: "upgrade" is "nuke-and-rebuild." Any future schema evolution can't MIGRATE data; has to throw it away. This is technical debt the framework will regret when schema changes become necessary.
3. No production safeguard: a one-time first-boot case (legitimate nuke) shares code path with the "unexpected failure" case (should alert loudly). Operator-serving logic shouldn't silently DROP tables.

Rare vector today (GraphLog table survives all current operations including DELETE FROM GraphLog in test fixtures), but represents a latent hazard that's invisible when triggered.

The Architectural Reality

• ai/graph/storage/SQLite.mjs:56-69 — initSchema with upgrade-required branch
• No existing schema-version field in the SQLite layer
• GraphLog table has existed since the cross-process cache coherence substrate was added (ADR 0001 §2.1)
• Fire conditions today: first-ever boot (legitimate), WAL corruption (unexpected), concurrent-lock flake (unexpected), future schema migration that drops/renames GraphLog (breaking)

The Fix

Three graduated improvements, all land together in one ticket:

1. Log warnings (observability): when upgradeRequired fires, log the trigger exception's message at WARN level. Operator sees WHY the upgrade fired. ~3 lines.

2. Production safeguard (data-loss guard): if env var NEO_ALLOW_SCHEMA_WIPE is not set AND Nodes/Edges tables exist with row counts > 0, refuse to DROP and throw a clear error. First-boot case unaffected because tables don't exist yet. Opt-in explicit (CI provisioning flows set the flag). ~10 lines.

3. Schema version field (migration substrate): add a SchemaVersion table with a single row containing version INTEGER. On upgradeRequired, check current version; if unrecognized mismatch, route to MIGRATE (future-reserved, starts as an empty stub branch). Sets up the migration substrate for future schema evolution without nuke-and-rebuild. ~15 lines.

Contract Ledger Matrix

Acceptance Criteria

• When upgradeRequired fires, a WARN-level log entry describes the trigger (exception message) + the upgrade action taken + whether the DROP was executed or refused
• If Nodes or Edges tables contain rows AND NEO_ALLOW_SCHEMA_WIPE is unset, DROP is refused with a clear error (first-boot case unaffected because tables don't exist yet)
• SchemaVersion table scaffolding added with initial version = 1; future upgrades check the version and route to a migrate-or-nuke decision
• Regression test: corrupt GraphLog table state (e.g., DROP only GraphLog) while Nodes/Edges contain rows → upgradeRequired fires → DROP refused without env opt-in → error surfaces; with opt-in → DROP proceeds + WARN logged
• Regression test: fresh boot (no tables) → upgradeRequired fires → DROP no-ops (tables already absent) → CREATE proceeds → WARN still logged (for operator transparency)

Out of Scope

• Actually implementing any specific schema migration (just the migration-substrate scaffolding)
• Backup/restore before DROP (larger design; separate ticket if needed)
• Converting Edges + GraphLog to their own versioned patterns (this ticket only adds the scaffolding for Nodes; other tables follow same pattern when needed)
• Changing WAL-mode pragmas or file-locking semantics

Avoided Traps

• "Remove the DROP path entirely": rejected. There IS a legitimate fresh-provisioning case (first-ever boot with no schema). DROP is correct for first-boot; the problem is it shouldn't be the SILENT catch-all for unrelated failures.
• "Add env flag without schema-version substrate": rejected in part. Env flag alone treats the symptom; versioning addresses the root cause (no way to detect "expected upgrade" vs "unexpected failure" without versions). Ship both together.
• "Refactor to a migration framework (Prisma-style)": rejected as over-scope. The SchemaVersion table + WARN logging is the minimum substrate for future migrations without committing to a framework. Framework-choice is a separate design decision.

Related

• Adjacent: ADR 0001 (cache coherence substrate that introduced GraphLog)
• Observed during: session 24aa1fa1 wipe-mechanism audit (2026-04-23)
• Lower priority than sibling tickets A/B/C — this is a rare fallback path, not an actively-firing hazard
• Sibling audit context: upsertNode lazy-load + PermissionService stub fix + GraphService self-seed — all four tickets filed together as the comprehensive wipe-mechanism audit outcome

Origin Session ID: 24aa1fa1-9a22-498e-97f2-760c12e5a79d

Handoff Retrieval Hints

• query_raw_memories(query="SQLite initSchema upgrade DROP wipe schema version")
• query_raw_memories(query="wipe mechanism audit GraphLog upgrade silent")