Summary

Fixes #28464.

Fixes a Kanban DB migration ordering bug that can crash hermes dashboard or any Kanban board load for users with an existing/legacy kanban.db.

connect() runs SCHEMA_SQL before _migrate_add_optional_columns(). The schema currently creates indexes on additive columns (tasks.session_id and task_events.run_id) before legacy tables have those columns. On an older board DB, SQLite aborts schema initialization before the migration can add the missing column:

sqlite3.OperationalError: no such column: session_id

This moves the indexes for additive columns out of SCHEMA_SQL and creates them after the additive migration has guaranteed the columns exist.

What changed

• Move idx_tasks_session_id creation from SCHEMA_SQL to _migrate_add_optional_columns() after tasks.session_id is added if missing.
• Move idx_events_run creation from SCHEMA_SQL to _migrate_add_optional_columns() after task_events.run_id is added if missing.
• Add a regression test that starts with a legacy DB missing both additive indexed columns and verifies connect() migrates it successfully and creates both indexes.

Why

Fresh DBs are fine because the additive columns are present when the schema creates the indexes. Existing boards from before these columns were added can fail before migration runs. This is especially visible through the Kanban dashboard plugin because /api/plugins/kanban/board opens the board DB during page load.

Validation

Confirmed the issue against origin/main by applying its SCHEMA_SQL to a legacy DB shape:

origin/main reproduces: OperationalError no such column: session_id

Confirmed this branch migrates the same legacy DB and preserves the expected indexes:

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

Ran the focused regression test:

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

Also verified local real board DBs migrated/opened successfully after the fix:

OK .hermes/kanban.db: session_id=True idx=True
OK .hermes/kanban/boards/cores-mech-wars/kanban.db: session_id=True idx=True
OK .hermes/kanban/boards/donkey-demo/kanban.db: session_id=True idx=True
OK .hermes/kanban/boards/osint-project/kanban.db: session_id=True idx=True
OK .hermes/kanban/boards/real-project/kanban.db: session_id=True idx=True

Suggested Labels

• type/bug
• Kanban / migration area label if maintainers have one

Platform tested

• OS: Linux 6.19.10-arch1-1 x86_64
• Python: 3.11.15
• Hermes version command currently cannot run on my checkout because origin/main contains conflict-marker text in hermes_cli/config.py around dispatch_stale_timeout_seconds, producing a SyntaxError before CLI startup. That appears unrelated to this migration fix.