Issue Station
Sign In
Tools hereToolbox

hermes - ✅(Solved) Fix Kanban legacy DB migration can fail creating indexes before additive columns exist [11 pull requests, 2 comments, 2 participants]

Official PRs (…)
ON THIS PAGE

Recommended Tools

×6

Utilities matched from this issue’s tags and category — try them while you read without losing context.

GitHub issue graph ai analysis

Paste a GitHub issue URL. We fetch that issue, discover linked issues from bodies/comments/timeline, collect linked pull requests, and produce a structured English report.

The report is written in English Markdown for sharing and archival.

Helpful · Quick feedback

Loading…
GitHub stats
NousResearch/hermes-agent#28464•Fetched 2026-05-20 04:03:39
View on GitHub
Comments
2
Participants
2
Timeline
23
Reactions
0
Author
itsreverenceitsreverence
Participants
ferranbonasferranbonasitsreverenceitsreverence
Timeline (top)
cross-referenced ×16labeled ×3commented ×2closed ×1

Error Message

sqlite3.OperationalError: no such column: session_id

Fix Action

Fixed

PR fix notes

PR #28461: fix(kanban): create additive-column indexes after migration

Description (problem / solution / changelog)

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.

Changed files

  • hermes_cli/kanban_db.py (modified, +15/-12)
  • tests/hermes_cli/test_kanban_db_init.py (modified, +72/-0)

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

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 #28597: fix(kanban): migrate session_id before indexing

Description (problem / solution / changelog)

Summary

  • Move idx_tasks_session_id creation out of the initial schema script so legacy Kanban boards add the nullable session_id column before indexing it.
  • Add regression coverage for pre-session_id Kanban databases.
  • Keep kanban-worker spawn tests aligned with resolvable bundled skill behavior.

Test Plan

  • python -m pytest tests/hermes_cli/test_kanban_db.py tests/hermes_cli/test_kanban_core_functionality.py -q

Changed files

  • hermes_cli/kanban_db.py (modified, +18/-7)
  • tests/hermes_cli/test_kanban_core_functionality.py (modified, +81/-0)

PR #28602: fix(kanban): create additive indexes after migrations

Description (problem / solution / changelog)

Summary

Fixes Kanban DB initialization for legacy boards whose existing tasks / task_events tables predate additive columns that now have indexes.

connect() runs SCHEMA_SQL before _migrate_add_optional_columns(). Because CREATE TABLE IF NOT EXISTS does not add missing columns to an existing table, any CREATE INDEX in SCHEMA_SQL that references an additive column can fail before the migration has a chance to add that column.

This moves all currently indexed additive-column indexes out of SCHEMA_SQL and creates them after the additive migration guarantees the columns exist:

  • tasks.tenant / idx_tasks_tenant
  • tasks.idempotency_key / idx_tasks_idempotency
  • tasks.session_id / idx_tasks_session_id
  • task_events.run_id / idx_events_run

Relationship to #28461

This intentionally overlaps with #28461, which fixes the same ordering class for session_id and run_id. This PR covers the broader additive-index set (tenant, idempotency_key, session_id, and run_id) and adds a regression test with a legacy DB missing all four indexed additive columns.

If maintainers prefer #28461 as the landing path, this can be closed or used as supplemental regression coverage / patch guidance.

Test Plan

  • python -m py_compile hermes_cli/kanban_db.py tests/hermes_cli/test_kanban_db_init.py
  • python -m pytest tests/hermes_cli/test_kanban_db_init.py -q -o addopts= --tb=short
  • git diff --check

Changed files

  • hermes_cli/kanban_db.py (modified, +16/-17)
  • tests/hermes_cli/test_kanban_db_init.py (modified, +81/-0)

PR #28620: fix(kanban): remove duplicate idx_tasks_session_id from SCHEMA_SQL

Description (problem / solution / changelog)

Fix: Remove duplicate idx_tasks_session_id from SCHEMA_SQL

Issue: #28617

Root Cause

When the kanban schema gains a new indexed column (session_id in the tasks table), the CREATE INDEX IF NOT EXISTS statement inside SCHEMA_SQL references the new column — but on existing databases, that column doesn't exist yet. SQLite throws OperationalError: no such column: session_id during conn.executescript(SCHEMA_SQL) in connect(), before _migrate_add_optional_columns() gets a chance to add the column.

IF NOT EXISTS only skips if the index exists — SQLite still validates the column reference. On an existing DB without session_id, this fails before the migration code runs.

Fix

Remove the CREATE INDEX IF NOT EXISTS idx_tasks_session_id ON tasks(session_id); from SCHEMA_SQL. The migration function _migrate_add_optional_columns() already adds both the column and the index correctly for legacy databases.

This is consistent with how run_id on task_events is handled — the idx_events_run index lives only in the migration function, not in SCHEMA_SQL.

Verification

  • idx_tasks_session_id now only appears once, in _migrate_add_optional_columns() (line ~1176)
  • idx_tasks_session_id no longer appears in SCHEMA_SQL
  • Fresh installs: unaffected (migration runs, creates the index)
  • Legacy installs: migration now runs successfully without crashing

Changed files

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

PR #28705: fix(achievements): persist lifetime counters to prevent regression after prune

Description (problem / solution / changelog)

Problem

When sessions.auto_prune deletes old rows from state.db, achievement lifetime counters regress because they are recomputed only from remaining sessions (issue #28661).

Fix

Merge scanned aggregate counters with persisted lifetime_counters in state.json using max(). Once a counter reaches a value it never goes backward.

Fixes #28661

Changed files

  • gateway/platforms/email.py (modified, +47/-2)

PR #28754: fix(kanban): migrate task session index after columns

Description (problem / solution / changelog)

What

  • Move indexes for additive Kanban task columns out of the initial schema SQL and create them after optional-column migration has run.
  • Cover legacy Kanban DB initialization through the normal kb.connect(db_path) path.
  • Stub _pid_alive in an existing max-runtime retry test so the repo live-system guard does not block the intended runtime assertion.

Why

Legacy board DBs created before tasks.session_id existed can crash on startup after hermes update. CREATE TABLE IF NOT EXISTS tasks (...) leaves the old table unchanged, then SQLite tries to create idx_tasks_session_id ON tasks(session_id) before _migrate_add_optional_columns() can add the column. The same index-before-column hazard also applied to tenant and idempotency_key, so all three optional-column indexes now live after the additive migration.

How to test

  • git diff --check
  • scripts/run_tests.sh tests/hermes_cli/test_kanban_db.py

Bug reproduction covered by the new regression test:

  1. Create a legacy Kanban DB with tasks missing session_id.
  2. Open it through kb.connect(db_path).
  3. Verify no init crash.
  4. Verify tasks.session_id exists.
  5. Verify idx_tasks_session_id exists.

Platforms tested

  • macOS via the repo test wrapper.

Related issues

  • None filed. Reported from local legacy boards failing with sqlite3.OperationalError: no such column: session_id.

Changed files

  • hermes_cli/kanban_db.py (modified, +11/-9)
  • tests/hermes_cli/test_kanban_db.py (modified, +48/-1)

PR #28752: fix: surface kanban completion evidence and validate skills

Description (problem / solution / changelog)

Summary

  • fix Kanban legacy DB initialization when tasks.session_id is missing by creating the session_id index after additive migrations
  • validate per-task skill identifiers so natural-language labels like ppt skill fail fast instead of causing worker startup crash loops
  • add dashboard completion evidence rollups for task details: run counts, suspicious completion reasons, dependency completion, deliverables, and invalid skills

Context

A legacy Kanban board upgraded to the newer schema can fail loading the dashboard with:

sqlite3.OperationalError: no such column: session_id

The root cause is that SCHEMA_SQL created idx_tasks_session_id before _migrate_add_optional_columns() had a chance to add tasks.session_id to older DBs.

A separate but related operational issue: if a task stores a natural-language per-task skill such as ppt skill, workers are spawned with --skills "ppt skill" and exit before doing work with:

Error: Unknown skill(s): ppt skill

The dashboard had the raw run history/logs, but completion evidence was not summarized above the fold, making it hard to tell whether dependency tasks completed and why the root task kept failing.

Test Plan

  • ./venv/bin/python -m py_compile hermes_cli/kanban_db.py plugins/kanban/dashboard/plugin_api.py
  • ./venv/bin/python -m pytest tests/hermes_cli/test_kanban_db_init.py -q
  • ./venv/bin/python -m pytest tests/hermes_cli/test_kanban_db.py::test_session_id_index_exists tests/hermes_cli/test_kanban_db.py::test_create_task_rejects_natural_language_skill_names tests/hermes_cli/test_kanban_db.py::test_create_task_accepts_slug_like_skill_names -q
  • ./venv/bin/python -m pytest tests/plugins/test_kanban_dashboard_plugin.py::test_task_detail_completion_evidence_rolls_up_dependencies tests/plugins/test_kanban_dashboard_plugin.py::test_dashboard_bundle_renders_completion_evidence_section -q

Note: I also ran the broader Kanban DB/dashboard plugin tests locally. Three unrelated home-channel tests failed because my local real ~/.hermes has a Weixin home channel configured, which leaks into those tests' expectations.

Changed files

  • hermes_cli/kanban_db.py (modified, +20/-6)
  • plugins/kanban/dashboard/dist/index.js (modified, +65/-0)
  • plugins/kanban/dashboard/plugin_api.py (modified, +147/-9)
  • tests/hermes_cli/test_kanban_db.py (modified, +19/-0)
  • tests/hermes_cli/test_kanban_db_init.py (modified, +78/-0)
  • tests/plugins/test_kanban_dashboard_plugin.py (modified, +44/-0)

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

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:

IndexAdditive columnIntroduced in
idx_tasks_session_idtasks.session_id#28447
idx_tasks_tenanttasks.tenant#16081
idx_tasks_idempotencytasks.idempotency_key#17805
idx_events_runtask_events.run_id#17805

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)

PR #28776: fix(kanban): move session_id index out of SCHEMA_SQL to avoid legacy DB crash

Description (problem / solution / changelog)

Summary

Fixes a bug where the kanban dispatcher crashes every tick on legacy kanban.dbs with:

sqlite3.OperationalError: no such column: session_id

Root Cause

Commit 31fe22903 added the session_id column to the tasks table and created an index on it. The column was correctly added via _migrate_add_optional_columns(), but the index (idx_tasks_session_id) was placed inside SCHEMA_SQL.

When connect() opens a legacy DB:

  1. executescript(SCHEMA_SQL) runs CREATE TABLE IF NOT EXISTS tasks(...) — does nothing because the table already exists (SQLite does not add missing columns via IF NOT EXISTS).
  2. Next in the script: CREATE INDEX IF NOT EXISTS idx_tasks_session_id ON tasks(session_id) — crashes because the column does not exist yet.
  3. _migrate_add_optional_columns() never runs, so the migration never self-heals.

Fix

Move CREATE INDEX idx_tasks_session_id from SCHEMA_SQL into _migrate_add_optional_columns() alongside the existing ALTER TABLE ... ADD COLUMN session_id call.

This ensures the column is created before the index.

Testing

  • Verified with python3 -c ... that executescript crashes on a legacy table missing the column, and succeeds when the index is deferred until after the column is added.
  • Manually applied same fix to a live kanban.db that had been failing every minute since commit 31fe22903 landed.

Related

  • Introduced in commit 31fe22903
  • Closes any issue where kanban dispatcher fails with no such column: session_id

Changed files

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

PR #28797: fix: migrate legacy kanban session indexes

Description (problem / solution / changelog)

Summary

  • Migrate legacy kanban session indexes so older session records remain compatible.
  • Adds regression coverage for kanban DB initialisation/migration paths.

Test plan

  • Not run in this push-only step; this PR contains the existing local commit 9e8dfb758 only.

Safety notes

  • Pushed to fork branch mikebrownlee:fix/kanban-session-indexes.
  • No force push.
  • No new commits or code changes made during PR creation.

Changed files

  • hermes_cli/kanban_db.py (modified, +13/-17)
  • tests/hermes_cli/test_kanban_db_init.py (modified, +35/-0)

Code Example

sqlite3.OperationalError: no such column: session_id

---

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

---

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

---

sqlite3.OperationalError: no such column: session_id

---

origin/main reproduces: OperationalError no such column: session_id

---

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

---

python -m pytest tests/hermes_cli/test_kanban_db_init.py -q
2 passed
RAW_BUFFERClick to expand / collapse

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

Vote matrix · Quick signals

Works
Did the solution work? Tap to confirm.
Easy Fix
Was it a quick fix?
Time Saver
Did it save you time?
Blocking
Was it severely blocking?
Common Issue
Are others likely hitting this too?
Flaky / Intermittent
Is it intermittent?
Verified / Reproducible
Can you reproduce it reliably?
Loading…
#api#configuration error#environment variable#network issue#logging issue

Still need to ship something?

×6

Another batch ranked right after the header list — different links, same matching logic.

Back to top recommendations

TRENDING