Schema Migration v8 Silently Fails

[mimosel]

Issue Summary

Schema migration v8 (008_fix_spike_reports_columns.sql) is incorrectly marked as "applied" in the schema_version table, but the ALTER TABLE statements did not execute successfully. This results in missing columns in the spike_reports table, causing runtime errors.

Environment

• OS: Windows 11
• ELF Version: v0.3.12 (commit 91e9014)
• Database: SQLite 3.x
• Database File: ~/.claude/emergent-learning/memory/index.db

Symptoms

When running python ~/.claude/emergent-learning/src/query/query.py --context, the following error occurs:

UNEXPECTED ERROR: no such column: t1.topic [QS000]

Root Cause Analysis

Expected Behavior

Migration v8 should add missing columns to the spike_reports table:

• topic TEXT NOT NULL
• gotchas TEXT
• resources TEXT
• domain TEXT
• tags TEXT
• access_count INTEGER DEFAULT 0
• updated_at DATETIME

Actual Behavior

Schema version table shows migration v8 as applied:

SELECT * FROM schema_version ORDER BY version;
-- Returns:
-- (7, '2026-01-06 05:07:02', 'ensure_all_core_tables')
-- (8, '2026-01-06 05:07:02', 'fix_spike_reports_columns')
-- (9, '2026-01-06 05:07:02', 'conductor_workflow_tables')

But spike_reports table is missing columns:

PRAGMA table_info(spike_reports);
-- Returns only:
-- id, title, usefulness_score, time_invested_minutes, created_at, question, findings

Missing columns:

• topic (the one causing the error)
• gotchas
• resources
• domain
• tags
• access_count
• updated_at

Suspected Bug Location

The bug is likely in src/query/migrations.py, lines 86-118 (the apply_migration() method):

def apply_migration(self, version: int, migration_file: Path) -> bool:
    try:
        # ... SQL execution ...
        for statement in sql_statements.split(";"):
            statement = statement.strip()
            if statement and not statement.startswith("--"):
                try:
                    cursor.execute(statement)
                except sqlite3.OperationalError as e:
                    if "already exists" not in str(e) and \
                       "duplicate column" not in str(e) and \
                       "no such column" not in str(e):
                        raise
                    logger.debug(f"Skipping idempotent statement: {statement[:50]}...")

Hypothesis: The ALTER TABLE ADD COLUMN statements may be throwing exceptions that are silently ignored by the overly broad error handling. The migration then records itself as successful even though critical statements failed.

Possible scenarios:

1. SQLite version constraint on ALTER TABLE ADD COLUMN with NOT NULL (requires SQLite 3.32+)
2. Syntax error in the migration SQL that doesn't match the caught error patterns
3. Some other OperationalError not covered by the ignore list

Reproduction Steps

1. Install ELF v0.3.12
2. Start with a pre-v0.3 database that has spike_reports table with old schema
3. Run python ~/.claude/emergent-learning/src/query/query.py --context
4. Observe:
   • Migration v8 is recorded as "applied"
   • But topic column is missing
   • Error occurs: no such column: t1.topic

Verification

To verify the bug on an affected system:

# Check schema_version
sqlite3 ~/.claude/emergent-learning/memory/index.db \
  "SELECT * FROM schema_version WHERE version = 8;"

# Check spike_reports columns
sqlite3 ~/.claude/emergent-learning/memory/index.db \
  "PRAGMA table_info(spike_reports);"

If migration v8 shows as applied but topic column is missing, the bug is confirmed.

Impact

• Severity: High
• Users Affected: Any user upgrading from pre-v0.3 to v0.3.12
• Consequence: Query system is completely broken (cannot run --context queries)

Suggested Fix

Short-term: Manual Fix for Users

Add a migration rollback/retry mechanism or manual fix instructions:

# Backup database
cp ~/.claude/emergent-learning/memory/index.db \
   ~/.claude/emergent-learning/memory/index.db.backup.$(date +%Y%m%d-%H%M%S)

# Manually add missing columns
sqlite3 ~/.claude/emergent-learning/memory/index.db << 'EOF'
ALTER TABLE spike_reports ADD COLUMN topic TEXT NOT NULL DEFAULT '';
ALTER TABLE spike_reports ADD COLUMN gotchas TEXT;
ALTER TABLE spike_reports ADD COLUMN resources TEXT;
ALTER TABLE spike_reports ADD COLUMN domain TEXT;
ALTER TABLE spike_reports ADD COLUMN tags TEXT;
ALTER TABLE spike_reports ADD COLUMN access_count INTEGER DEFAULT 0;
ALTER TABLE spike_reports ADD COLUMN updated_at DATETIME;

CREATE INDEX IF NOT EXISTS idx_spike_reports_domain ON spike_reports(domain);
CREATE INDEX IF NOT EXISTS idx_spike_reports_topic ON spike_reports(topic);
CREATE INDEX IF NOT EXISTS idx_spike_reports_created_at ON spike_reports(created_at DESC);
CREATE INDEX IF NOT EXISTS idx_spike_reports_tags ON spike_reports(tags);
EOF

Long-term: Code Fix

1. Improve error logging in apply_migration():

   • Log the actual exception message before ignoring it
   • Make it clear which statements are being skipped vs. which succeed

2. Validate migration success:

   • After applying migration v8, verify that required columns exist
   • If columns are missing, mark migration as failed

3. Transaction safety:

   • Wrap migration in transaction
   • Rollback if any critical statement fails

Example fix for migrations.py:

def apply_migration(self, version: int, migration_file: Path) -> bool:
    try:
        # ... existing code ...

        for statement in sql_statements.split(";"):
            statement = statement.strip()
            if statement and not statement.startswith("--"):
                try:
                    cursor.execute(statement)
                except sqlite3.OperationalError as e:
                    error_msg = str(e)
                    # Log what we're skipping and why
                    if "already exists" in error_msg or \
                       "duplicate column" in error_msg:
                        logger.debug(f"Skipping idempotent: {statement[:50]}... (reason: {error_msg})")
                    elif "no such column" in error_msg:
                        # This might indicate a real problem
                        logger.warning(f"Migration statement failed: {statement[:100]}... Error: {error_msg}")
                    else:
                        # Unknown error - fail migration
                        logger.error(f"Migration failed on: {statement[:100]}... Error: {error_msg}")
                        raise

        # ... rest of existing code ...

Additional Notes

• The database file ~/.claude/emergent-learning/memory/learnings.db exists (0 bytes) but is unused. System uses index.db (defined in core.py:182).
• Multiple schema version backups exist, suggesting previous migration issues.

Workaround Applied

User has manually added the missing columns using the SQL commands above. Query system now works correctly.

---

Reported by: Claude Code User (via automated diagnostics)
Date: 2026-01-06
Related Issue: #63 (if exists in GitHub repo)

[Spacehunterz]

Resolved in commits:

• f530df1 - fix: Prevent schema migrations from silently failing
• fa224f0 - fix: Learning extractor was reading from wrong database
• 8cfabce - chore: Remove stale backup scripts

What was fixed:

1. Silent migration failures - The migration code caught "no such column" errors and silently continued, marking migrations complete even when they failed. Now only truly idempotent errors (like "already exists") are skipped, and all migrations run in transactions with rollback on failure.
2. Post-migration validation - migrate() now validates the schema after applying migrations and reports validation_failed status if columns are missing.
3. Dual database issue - The learning extractor was reading from learnings.db (which doesn't exist) instead of index.db. Fixed.
4. Repair capability - Added repair_schema() method to fix corrupted databases by adding missing columns.

The schema version gaps (1, 4 missing) are historical artifacts from before this fix - they won't cause issues going forward.

---