Summary

A legacy kanban.db can fail dashboard/Kanban initialization with:

sqlite3.OperationalError: no such column: session_id

The issue appears when the existing tasks table predates the newer optional session_id column.

Root cause

hermes_cli/kanban_db.py::connect() runs conn.executescript(SCHEMA_SQL) before _migrate_add_optional_columns(conn).

For a legacy DB, this statement is a no-op because the table already exists:

CREATE TABLE IF NOT EXISTS tasks (... session_id TEXT ...);

But SCHEMA_SQL then immediately runs:

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

Because _migrate_add_optional_columns() has not run yet, the legacy tasks table still has no session_id, so SQLite raises no such column: session_id before the migration gets a chance to add it.

Fresh DBs are fine; legacy DBs can fail.

Reproduction shape

1. Create a SQLite DB with a legacy tasks table that has the older columns but no session_id.
2. Call kanban_db.connect(db_path).
3. Observe sqlite3.OperationalError: no such column: session_id from conn.executescript(SCHEMA_SQL).

Minimal regression-test shape:

def test_connect_migrates_legacy_tasks_before_session_index(tmp_path):
    db_path = tmp_path / "legacy-kanban.db"
    conn = kb.sqlite3.connect(str(db_path))
    try:
        conn.executescript(
            """
            CREATE TABLE tasks (
                id TEXT PRIMARY KEY,
                title TEXT NOT NULL,
                body TEXT,
                assignee TEXT,
                status TEXT NOT NULL,
                priority INTEGER DEFAULT 0,
                created_by TEXT,
                created_at INTEGER NOT NULL,
                started_at INTEGER,
                completed_at INTEGER,
                workspace_kind TEXT NOT NULL DEFAULT 'scratch',
                workspace_path TEXT,
                claim_lock TEXT,
                claim_expires INTEGER
            );
            """
        )
    finally:
        conn.close()

    kb._INITIALIZED_PATHS.discard(str(db_path.resolve()))
    with kb.connect(db_path) as migrated:
        cols = {row["name"] for row in migrated.execute("PRAGMA table_info(tasks)")}
        indexes = {row["name"] for row in migrated.execute("PRAGMA index_list(tasks)")}

    assert "session_id" in cols
    assert "idx_tasks_session_id" in indexes

Proposed fix

Create indexes only after additive migrations have run:

1. Keep table creation in SCHEMA_SQL.
2. Move index DDL into a separate list/helper, e.g. REQUIRED_INDEX_SQL + _ensure_indexes(conn).
3. In connect(), call:

conn.executescript(SCHEMA_SQL)
_migrate_add_optional_columns(conn)
_ensure_indexes(conn)

This keeps fresh DB behavior unchanged while allowing legacy DBs to add optional columns before any index references them.

Local verification

I tested this approach locally against current main base 070eeaae6:

tests/hermes_cli/test_kanban_db_init.py + tests/plugins/test_kanban_dashboard_plugin.py
96 passed

Dashboard API smoke also passed for:

• GET /api/plugins/kanban/boards
• GET /api/plugins/kanban/board?board=default
• POST /api/plugins/kanban/tasks?board=default
• GET /api/plugins/kanban/tasks/{id}?board=default
• DELETE /api/plugins/kanban/tasks/{id}?board=default

Filed by Dhananjay Jagtap (mrdjaycreations) while integrating Hermes into a local Nexus/Brand Wisdom single-user setup.