hermes - 💡(How to fix) Fix Kanban DB schema migration silently skipped — TEXT PRIMARY KEY tables never upgraded to INTEGER AUTOINCREMENT

Summary

After upgrading Hermes, kanban notification delivery completely broke because old SQLite table schemas were never migrated. The gateway notifier crashed on every tick (int(None) on NULL values) with no visible error to the user — tasks silently completed without notifications.

Root Cause

Hermes uses two mechanisms to initialize/migrate kanban databases:

1. CREATE TABLE IF NOT EXISTS in SCHEMA_SQL — silently skips tables that already exist, regardless of whether their schema matches
2. _migrate_add_optional_columns() — only does ALTER TABLE ADD COLUMN, never changes column types, constraints, or primary key definitions

When the code changed several tables from TEXT PRIMARY KEY to INTEGER PRIMARY KEY AUTOINCREMENT, existing databases kept the old schema. The migration code had no path to detect or fix this.

Affected Tables (in kanban_db.py)

Impact

• kanban_notify_subs.last_event_id: New subscriptions insert NULL → int(NULL) in unseen_events_for_sub() crashes → entire notifier tick fails every 5 seconds → all kanban notifications silently lost
• task_events.id: All event IDs are NULL → cursor-based notification matching (WHERE id > cursor) never matches → no events delivered even if notifier doesn't crash
• task_runs.id: Same NULL ID issue, affects kanban_complete and other operations that reference run IDs

Steps to Reproduce

1. Create a kanban board with an older version of Hermes (pre-AUTOINCREMENT migration)
2. Upgrade to a newer version that expects INTEGER PRIMARY KEY AUTOINCREMENT
3. Create tasks, subscribe to notifications (kanban notify-subscribe)
4. Complete tasks — notifications never arrive
5. Gateway logs show: kanban notifier tick failed: int() argument must be a string...or a real number, not 'NoneType'

Expected Behavior

After upgrading Hermes, connect() or init_db() should detect schema mismatches and rebuild affected tables, preserving data.

Suggested Fix

Add a schema versioning mechanism:

# In _migrate_add_optional_columns() or a new migration function:

def _check_table_schema_version(conn):
    """Compare actual table DDL against expected SCHEMA_SQL definitions."""
    mismatches = []
    for table_name, expected_ddl in EXPECTED_TABLES.items():
        actual = conn.execute(
            "SELECT sql FROM sqlite_master WHERE type='table' AND name=?", 
            (table_name,)
        ).fetchone()
        if actual and not schema_matches(actual[0], expected_ddl):
            mismatches.append(table_name)
    return mismatches

def _rebuild_table(conn, table_name, new_ddl, columns):
    """CREATE TABLE new → INSERT INTO new SELECT → DROP old → RENAME"""
    conn.execute("BEGIN")
    conn.execute(f"CREATE TABLE {table_name}_new (...)")  
    conn.execute(f"INSERT INTO {table_name}_new (...) SELECT ... FROM {table_name}")
    conn.execute(f"DROP TABLE {table_name}")
    conn.execute(f"ALTER TABLE {table_name}_new RENAME TO {table_name}")
    conn.execute("COMMIT")

Workaround

We wrote a standalone script to detect and fix schema mismatches:

• Scans all kanban DB files under ~/.hermes/kanban/
• Compares actual PRAGMA table_info against expected SCHEMA_SQL definitions
• Rebuilds mismatched tables with CREATE new → INSERT → DROP old → RENAME
• Ran daily via cron to catch future drift

Environment

• Hermes Agent v0.15.1 (2026.5.29)
• Linux (6.8.0-117-generic)
• Python 3.11.15
• SQLite 3.x
• Affected 3 kanban databases (default, osys-platform, base-infra)