Bug Description

Existing Kanban board databases can fail to open after upgrading when connect() runs SCHEMA_SQL before additive migrations have added newer indexed columns.

The immediate failure I hit was from loading the Kanban dashboard:

sqlite3.OperationalError: no such column: session_id

Stack location:

plugins/kanban/dashboard/plugin_api.py:get_board
  -> hermes_cli.kanban_db.connect()
  -> conn.executescript(SCHEMA_SQL)

SCHEMA_SQL creates this index before legacy tasks tables necessarily have the additive session_id column:

CREATE INDEX IF NOT EXISTS idx_tasks_session_id ON tasks(session_id);

The same migration-ordering risk also exists for task_events.run_id / idx_events_run.

Steps to Reproduce

1. Start with a legacy Kanban DB where:
   • tasks exists but does not include session_id
   • task_events exists but does not include run_id
2. Run current kanban_db.connect(db_path) / load the Kanban dashboard.
3. connect() executes SCHEMA_SQL before _migrate_add_optional_columns().
4. SQLite attempts to create an index on a missing additive column.

Expected Behavior

Legacy Kanban DBs should migrate cleanly:

• Add missing additive columns first.
• Then create indexes that depend on those columns.
• Dashboard board load should not crash during DB initialization.

Actual Behavior

Schema initialization aborts before the migration can run:

sqlite3.OperationalError: no such column: session_id

Validation / Reproduction Evidence

I reproduced this against origin/main by applying its SCHEMA_SQL to a legacy DB shape:

origin/main reproduces: OperationalError no such column: session_id

I also verified the proposed fix migrates the same legacy shape and creates the expected columns/indexes:

current fix: True True True True
# session_id column, run_id column, idx_tasks_session_id, idx_events_run

Focused regression test on the fix branch:

python -m pytest tests/hermes_cli/test_kanban_db_init.py -q
2 passed

Proposed Fix

Move indexes over additive columns out of SCHEMA_SQL and into _migrate_add_optional_columns() after the relevant ALTER TABLE path has guaranteed the column exists.

Related PR: #28461

Environment

• OS: Linux 6.19.10-arch1-1 x86_64
• Python: 3.11.15
• Area: Kanban DB migration / dashboard board loading

Suggested Labels

• type/bug
• needs-triage if maintainers prefer all new issues to enter triage first