PR #28562: fix(kanban): move session_id index creation from SCHEMA_SQL to migration

• Repository: NousResearch/hermes-agent
• Author: kungunier
• State: closed | merged: False
• Link: https://github.com/NousResearch/hermes-agent/pull/28562

Description (problem / solution / changelog)

Problem

When upgrading from an older Hermes version to v0.14.0, existing kanban.db files lack the session_id column. SCHEMA_SQL includes:

CREATE TABLE IF NOT EXISTS tasks (...);
CREATE INDEX IF NOT EXISTS idx_tasks_session_id ON tasks(session_id);  -- 💥

CREATE TABLE IF NOT EXISTS is a no-op on existing tables (old schema, no session_id). But the CREATE INDEX on the next line references session_id before _migrate_add_optional_columns() gets a chance to add it via ALTER TABLE. Crash.

Fix

Remove the premature CREATE INDEX from SCHEMA_SQL. The index creation is already handled correctly inside _migrate_add_optional_columns() (line 1166–1178), where ALTER TABLE ADD COLUMN runs first.

One line deleted, no logic changed — the index still gets created, just at the right time.

Verification

• Fresh install: SCHEMA_SQL creates table + all columns → migration is a no-op → index created during migration ✓
• Upgrade from old DB: SCHEMA_SQL no-ops on existing table → migration adds session_id column → migration creates index ✓

Fixes #28554

Changed files

• hermes_cli/kanban_db.py (modified, +0/-2)

PR #28781: fix(kanban): hoist all additive-column indexes out of SCHEMA_SQL (#28461)

• Repository: NousResearch/hermes-agent
• Author: kshitijk4poor
• State: closed | merged: True
• Link: https://github.com/NousResearch/hermes-agent/pull/28781

Description (problem / solution / changelog)

Summary

Salvage of #28461 — fixes the Kanban DB legacy-migration crash reported in #28464 (and rediscovered in #28554, #28617, #28654, #28698) where connect() runs SCHEMA_SQL before _migrate_add_optional_columns(), so a legacy board missing any additive indexed column aborts schema init with OperationalError: no such column: <col> before the migration can add it.

The community filed six PRs for this bug (all on 2026-05-19). #28461 (itsreverence) was first and best-explained but only covered 2 of the 4 vulnerable indexes. #28754 (quocanh261997) extended coverage to 3 of the 4. This salvage combines the strengths of all the open PRs and closes the remaining gap.

What changed

Move every CREATE INDEX that depends on an additive column out of SCHEMA_SQL and create them after _migrate_add_optional_columns() adds the columns. All four additive-column indexes are now hoisted:

Three of these were ticking landmines for any user whose board predated the column. idx_tasks_session_id was the one that bit users today because it's the most recent (#28447 merged the day before the bug reports flooded in), but the same migration trap applied to all four.

Beyond #28754's coverage:

• idx_events_run: also hoisted out of SCHEMA_SQL and made unconditional in the migration. A legacy task_events table predating #17805 (no run_id) was still hitting the same SQLite trap that #28754 only addressed for tasks.
• idx_tasks_idempotency: removed the redundant inner-conditional create (line ~1085) that was nested inside if "idempotency_key" not in cols:. Since the unconditional create lower in the function already runs CREATE INDEX IF NOT EXISTS, the inner one was dead code on fresh DBs once we removed the SCHEMA_SQL line.
• Strengthened the regression test to cover all 4 indexes and to seed a pre-#17805 task_events shape that exercises the run_id migration path.
• Added explicit rationale comments so the next contributor adding tasks.<new_col> doesn't re-introduce the trap.
• Picked up an adjacent test fix from #28754: test_max_runtime_uses_current_run_start_after_retry now monkeypatch-es kb._pid_alive so it stops tripping tests/conftest.py's live-system guard on os.kill(<fake_pid>, 0). Pre-existing failure on origin/main, unrelated to the index ordering bug; carried in via the cherry-pick rather than split out separately.

Validation

Confirmed against origin/main:

origin/main reproduces against pre-#16081 fixture: no such column: session_id

Confirmed on this branch:

Scenario A: legacy DB missing all 4 additive columns      ✅
Scenario B: fresh DB                                       ✅
Scenario C: idempotency on already-migrated DB             ✅
Scenario D: partially-migrated DB (tenant+idempotency)     ✅

Test suite:

$ bash scripts/run_tests.sh tests/hermes_cli/test_kanban_db.py tests/hermes_cli/test_kanban_db_init.py
============================= 159 passed in 5.06s ==============================

Full tests/hermes_cli/ suite — 4888 passed, 13 pre-existing failures unrelated to kanban (gateway service / model picker / plugins) that match origin/main 1:1.

Lint diff: zero new ruff/ty issues vs origin/main (123 ty issues on changed files, identical on both base and head).

Credit

Original bug report and PR by @itsreverence (#28464 + #28461 — first to file, best write-up of the underlying ordering trap).

Cherry-picked commit on this branch authored by @quocanh261997 from #28754 (broadest open coverage — included tasks.tenant and tasks.idempotency_key).

Other PRs absorbed and to be closed as duplicates: #28741 (@stormhierta), #28562 (@kungunier), #28620 (@vanhci), #28602 (@verybigdog, DRAFT). All of them correctly diagnosed the bug; this salvage combines the broadest coverage with the most thorough comments and tests.

Closes #28464. Closes #28554. Closes #28617. Closes #28654. Closes #28698.

Changed files

• hermes_cli/kanban_db.py (modified, +26/-18)
• tests/hermes_cli/test_kanban_db.py (modified, +84/-1)