hermes_state_schema.py 81 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297129812991300130113021303130413051306130713081309131013111312131313141315131613171318131913201321132213231324132513261327132813291330133113321333133413351336133713381339134013411342134313441345134613471348134913501351135213531354135513561357135813591360136113621363136413651366136713681369137013711372137313741375137613771378137913801381138213831384138513861387138813891390139113921393139413951396139713981399140014011402140314041405
  1. """Schema creation, column reconciliation, and FTS DDL management for SessionDB.
  2. Plain mixin for ``hermes_state.SessionDB`` (no ``__init__``/state of its own).
  3. Must never import hermes_state (cycle); shared constants live in hermes_state_common.
  4. """
  5. import contextlib
  6. import datetime
  7. import hashlib
  8. import logging
  9. import json
  10. import os
  11. import sqlite3
  12. import tempfile
  13. import time
  14. import uuid
  15. from typing import Dict, List, Optional, Sequence
  16. from hermes_constants import get_hermes_home
  17. from hermes_startup_watchdog import report_startup_progress
  18. from utils import safe_json_loads
  19. from hermes_state_common import (
  20. DEFERRED_INDEX_SQL, FTS_CJK_STALE_KEY, FTS_REBUILD_DEFERRAL_KEY, FTS_STALE_KEY, FTS_SQL,
  21. FTS_STORAGE_VERSION, FTS_TOOL_CONTENT_PREFIX_CHARS, FTS_TRIGRAM_SQL, LEGACY_FTS_SQL,
  22. LEGACY_FTS_TRIGRAM_SQL, SCHEMA_SQL,
  23. SCHEMA_VERSION, _FTS_CJK_TRIGGERS, _FTS_TRIGGERS, _ephemeral_child_sql, _sql_json_extract, fts_rebuild_admission,
  24. )
  25. from hermes_state_fts import (_drop_orphan_fts_shadow_tables, base_index_recognised, base_source_view_is_stale,
  26. fts_layout_current)
  27. from hermes_state_holders import _read_proc_argv
  28. from hermes_state_search import _delete_meta, _meta_row
  29. from hermes_state_errors import is_sqlite_lock_error
  30. from hermes_state_titles import next_title_in_lineage
  31. # Pre-split logger identity so log filtering/capture is unchanged.
  32. logger = logging.getLogger("hermes_state")
  33. _FTS_HOLDER_ESCALATE_ATTEMPTS = 3
  34. _FTS_HOLDER_ESCALATE_SECONDS = 60.0
  35. # The same holder PID set blocking this many deferrals over this long is a structurally resident
  36. # peer (a supervised service on the same HERMES_HOME), not a transient one worth waiting out (#106393).
  37. _FTS_HOLDER_FUTILE_ATTEMPTS = 10
  38. _FTS_HOLDER_FUTILE_SECONDS = 1800.0
  39. # retry_deferred_fts_recovery cadence: startup paid the full admission wait once; later
  40. # retries are non-blocking probes whose spacing doubles up to the cap.
  41. _FTS_STALE_RETRY_SECONDS = 60.0
  42. _FTS_STALE_RETRY_MAX_SECONDS = 3600.0
  43. # message_uid legacy backfill: rows per UPDATE (short write-lock holds) and the extra time one open may spend.
  44. _MESSAGE_UID_BACKFILL_DONE = "message_uid_backfill"
  45. _MESSAGE_UID_BACKFILL_CURSOR = "message_uid_backfill_id"
  46. _MESSAGE_UID_BACKFILL_CHUNK = 2000
  47. _MESSAGE_UID_BACKFILL_BUDGET_S = 1.0
  48. def _holder_cmdline(pid: int) -> str:
  49. argv = _read_proc_argv(pid)
  50. return " ".join(argv)[:120] if argv else "<cmdline unavailable>"
  51. # schema_read_probe_statements() cache (parses SCHEMA_SQL in an in-memory DB; once per process).
  52. _READ_PROBE_STATEMENTS: Optional[tuple] = None
  53. # Trigram triggers need the trigram tokenizer (SQLite >= 3.34); without it _ensure_fts_schema
  54. # soft-fails that DDL and "all six present" is unsatisfiable, so a trigger's absence is
  55. # measured only against the DDL that can create it.
  56. _FTS_TRIGRAM_TRIGGERS = tuple(n for n in _FTS_TRIGGERS if "_trigram_" in n)
  57. _FTS_BASE_TRIGGERS = tuple(n for n in _FTS_TRIGGERS if n not in _FTS_TRIGRAM_TRIGGERS)
  58. # (base DDL, trigram DDL) keyed by "legacy inline layout?" — v23 external-content vs pre-v23 inline.
  59. _FTS_DDL = {False: (FTS_SQL, FTS_TRIGRAM_SQL), True: (LEGACY_FTS_SQL, LEGACY_FTS_TRIGRAM_SQL)}
  60. _LEGACY_INLINE_CONCAT_SQL = (
  61. "COALESCE(content, '') || ' ' || COALESCE(tool_name, '') || ' ' || COALESCE(tool_calls, '') "
  62. )
  63. _SESSION_MODEL_USAGE_INDEX_SQL = (
  64. "CREATE INDEX IF NOT EXISTS idx_session_model_usage_session ON session_model_usage(session_id)",
  65. "CREATE INDEX IF NOT EXISTS idx_session_model_usage_model ON session_model_usage(model)",
  66. )
  67. _SESSION_MODEL_USAGE_HEAL_DDL = """CREATE TABLE session_model_usage (
  68. session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
  69. model TEXT NOT NULL,
  70. billing_provider TEXT NOT NULL DEFAULT '',
  71. billing_base_url TEXT NOT NULL DEFAULT '',
  72. billing_mode TEXT NOT NULL DEFAULT '',
  73. task TEXT NOT NULL DEFAULT '',
  74. api_call_count INTEGER NOT NULL DEFAULT 0,
  75. input_tokens INTEGER NOT NULL DEFAULT 0,
  76. output_tokens INTEGER NOT NULL DEFAULT 0,
  77. cache_read_tokens INTEGER NOT NULL DEFAULT 0,
  78. cache_write_tokens INTEGER NOT NULL DEFAULT 0,
  79. reasoning_tokens INTEGER NOT NULL DEFAULT 0,
  80. estimated_cost_usd REAL NOT NULL DEFAULT 0,
  81. actual_cost_usd REAL NOT NULL DEFAULT 0,
  82. cost_status TEXT,
  83. cost_source TEXT,
  84. first_seen REAL,
  85. last_seen REAL,
  86. PRIMARY KEY (session_id, model, billing_provider, billing_base_url, billing_mode, task)
  87. )"""
  88. # v22-migration rendering of the same table (column lines at 35 spaces, paren at 31; pinned SQL).
  89. _SESSION_MODEL_USAGE_V22_DDL = "\n".join(
  90. [_SESSION_MODEL_USAGE_HEAL_DDL.splitlines()[0]]
  91. + [" " * 35 + ln.strip() for ln in _SESSION_MODEL_USAGE_HEAL_DDL.splitlines()[1:-1]]
  92. + [" " * 31 + ")"]
  93. )
  94. # Statement text pinned by the SQL trace harness (whitespace included).
  95. _SESSION_MODEL_USAGE_V20_SEED_SQL = """INSERT OR IGNORE INTO session_model_usage (
  96. session_id, model, billing_provider,
  97. billing_base_url, billing_mode,
  98. api_call_count, input_tokens,
  99. output_tokens, cache_read_tokens,
  100. cache_write_tokens, reasoning_tokens,
  101. estimated_cost_usd, actual_cost_usd,
  102. cost_status, cost_source, first_seen, last_seen
  103. )
  104. SELECT id, COALESCE(model, 'unknown'),
  105. COALESCE(billing_provider, ''),
  106. COALESCE(billing_base_url, ''),
  107. COALESCE(billing_mode, ''),
  108. COALESCE(api_call_count, 0),
  109. COALESCE(input_tokens, 0),
  110. COALESCE(output_tokens, 0),
  111. COALESCE(cache_read_tokens, 0),
  112. COALESCE(cache_write_tokens, 0),
  113. COALESCE(reasoning_tokens, 0),
  114. COALESCE(estimated_cost_usd, 0),
  115. COALESCE(actual_cost_usd, 0),
  116. cost_status, cost_source,
  117. started_at, COALESCE(ended_at, started_at)
  118. FROM sessions
  119. WHERE COALESCE(input_tokens, 0)
  120. + COALESCE(output_tokens, 0)
  121. + COALESCE(cache_read_tokens, 0)
  122. + COALESCE(cache_write_tokens, 0)
  123. + COALESCE(reasoning_tokens, 0) > 0"""
  124. _TITLE_UNIQUE_INDEX_SQL = (
  125. "CREATE UNIQUE INDEX IF NOT EXISTS idx_sessions_title_unique ON sessions(title) WHERE title IS NOT NULL"
  126. )
  127. _STALE_KEY_UPSERT_SQL = (
  128. "INSERT INTO state_meta (key, value) VALUES (?, '1') ON CONFLICT(key) DO UPDATE SET value = excluded.value"
  129. )
  130. _STATE_META_UPSERT_SQL = (
  131. "INSERT INTO state_meta (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value"
  132. )
  133. _CLEAR_REBUILD_MARKERS_SQL = "DELETE FROM state_meta WHERE key IN ('fts_rebuild_high_water', 'fts_rebuild_progress')"
  134. # FTS_STORAGE_VERSION < 3 truncated tool rows only above a moving state_meta mark; the aligned
  135. # projection truncates by role alone, so the retired marker is dropped with the realign.
  136. _DROP_RETIRED_TOOL_HIGH_WATER_SQL = "DELETE FROM state_meta WHERE key = 'fts_tool_full_content_high_water'"
  137. def _legacy_inline_reinsert_sql(table: str, indent: int, *, delete_first: bool = False) -> str:
  138. """Legacy inline (pre-v23) FTS re-population script fragment (whitespace pinned)."""
  139. pad = " " * indent
  140. body = f"{pad}DELETE FROM {table};\n" if delete_first else ""
  141. return (
  142. f"\n{body}{pad}INSERT INTO {table}(rowid, content)\n"
  143. f"{pad}SELECT id,\n"
  144. f"{pad} COALESCE(content, '') || ' ' ||\n"
  145. f"{pad} COALESCE(tool_name, '') || ' ' ||\n"
  146. f"{pad} COALESCE(tool_calls, '')\n"
  147. f"{pad}FROM messages;\n{pad[:-4]}"
  148. )
  149. def _q(ident: str) -> str:
  150. """Double-quote an SQL identifier."""
  151. return '"' + ident.replace('"', '""') + '"'
  152. def schema_read_probe_statements() -> tuple:
  153. """SELECT statements that fail iff a live store is behind SCHEMA_SQL. Read-only opens skip
  154. ``_reconcile_columns()`` (no DDL against another profile's live DB), so healing callers
  155. run these afterwards: a missing table/column raises at prepare time. Derived from
  156. SCHEMA_SQL (a hand-maintained list went stale within days). Columns are
  157. table-qualified: an unqualified double-quoted identifier that fails to resolve silently
  158. degrades to a string literal (SQLite misfeature) and would pass on the stale store."""
  159. global _READ_PROBE_STATEMENTS
  160. if _READ_PROBE_STATEMENTS is None:
  161. tables = SessionSchemaMixin._parse_schema_columns(SCHEMA_SQL)
  162. _READ_PROBE_STATEMENTS = tuple(
  163. "SELECT {} FROM {} LIMIT 0".format(", ".join(f"{_q(table)}.{_q(col)}" for col in cols), _q(table))
  164. for table, cols in sorted(tables.items())
  165. )
  166. return _READ_PROBE_STATEMENTS
  167. class SessionSchemaMixin:
  168. """See module docstring — mixin for SessionDB (Schema cluster)."""
  169. def _dedupe_legacy_system_prompts(self, cursor: sqlite3.Cursor) -> None:
  170. """Move inline prompt snapshots into the shared content-addressed table. Any
  171. ``OperationalError`` mid-loop returns instead of raising: partial migration is safe
  172. (the legacy column stays a read fallback; next init resumes), whereas propagating
  173. left the version below 25 and re-ran this on every open (gateway crash loop)."""
  174. try:
  175. rows = cursor.execute("SELECT id, system_prompt FROM sessions WHERE system_prompt IS NOT NULL").fetchall()
  176. except sqlite3.OperationalError:
  177. return
  178. for session_id, prompt in rows:
  179. try:
  180. prompt_hash = self._store_system_prompt(cursor, prompt)
  181. cursor.execute(
  182. "UPDATE sessions SET system_prompt_hash = ?, system_prompt = NULL WHERE id = ?",
  183. (prompt_hash, session_id),
  184. )
  185. except sqlite3.OperationalError as exc:
  186. logger.warning(
  187. "v25 prompt dedupe paused after contention (%s); "
  188. "unmigrated rows keep the legacy inline prompt and the next schema init resumes the migration.",
  189. exc,
  190. )
  191. return
  192. def _sqlite_supports_fts5(self, cursor: sqlite3.Cursor) -> bool:
  193. try:
  194. cursor.execute("CREATE VIRTUAL TABLE temp._hermes_fts5_probe USING fts5(x)")
  195. cursor.execute("DROP TABLE temp._hermes_fts5_probe")
  196. return True
  197. except sqlite3.OperationalError as exc:
  198. if not self._is_fts5_unavailable_error(exc):
  199. raise
  200. self._warn_fts5_unavailable(exc)
  201. return False
  202. def _drop_all_fts_triggers(self, cursor: sqlite3.Cursor) -> None:
  203. self._drop_fts_triggers(cursor)
  204. for trigger in _FTS_CJK_TRIGGERS:
  205. with contextlib.suppress(sqlite3.OperationalError):
  206. cursor.execute(f"DROP TRIGGER IF EXISTS {trigger}")
  207. @staticmethod
  208. def _fts_triggers_missing(cursor: sqlite3.Cursor, names: Sequence[str]) -> bool:
  209. """True unless every trigger in *names* (one DDL half) exists."""
  210. if not names:
  211. return False # "name IN ()" is a SQLite syntax error
  212. placeholders = ",".join("?" for _ in names)
  213. sql = f"SELECT COUNT(*) FROM sqlite_master WHERE type = 'trigger' AND name IN ({placeholders})"
  214. return int(cursor.execute(sql, tuple(names)).fetchone()[0]) < len(names)
  215. @staticmethod
  216. def _fts_update_trigger_needs_narrowing(sql: Optional[str]) -> bool:
  217. """True when trigger SQL is a broad AFTER UPDATE (missing ``OF``)."""
  218. if not sql:
  219. return False
  220. compact = " ".join(sql.split()).upper() # multi-line DDL still matches
  221. return "AFTER UPDATE OF " not in compact and "AFTER UPDATE ON " in compact
  222. def _migrate_broad_fts_update_triggers(self, cursor: sqlite3.Cursor) -> int:
  223. """Replace broad AFTER UPDATE FTS triggers with AFTER UPDATE OF variants (``IF NOT EXISTS``
  224. never replaces an existing broad trigger). No FTS rebuild: correctness was already
  225. gated by WHEN clauses; OF only skips trigger evaluation. Returns the number dropped."""
  226. # CJK is v23-only. Decide the layout before selecting destructive candidates so the
  227. # legacy branch never drops a trigger it won't recreate.
  228. legacy_layout = self._db_has_legacy_inline_fts(cursor)
  229. update_names = ("messages_fts_update", "messages_fts_trigram_update") + (
  230. () if legacy_layout else ("messages_fts_cjk_update",)
  231. )
  232. placeholders = ", ".join("?" for _ in update_names)
  233. sql = f"SELECT name, sql FROM sqlite_master WHERE type = 'trigger' AND name IN ({placeholders})"
  234. rows = cursor.execute(sql, update_names).fetchall()
  235. to_drop = [name for name, sql in rows if self._fts_update_trigger_needs_narrowing(sql)]
  236. if not to_drop:
  237. return 0
  238. for name in to_drop:
  239. cursor.execute(f"DROP TRIGGER IF EXISTS {name}") # names from the literal allowlist above
  240. # Re-apply current DDL (legacy vs v23 as _init_schema does) so CREATE TRIGGER installs OF variants.
  241. base_sql, trigram_sql = _FTS_DDL[legacy_layout]
  242. self._ensure_fts_schema(cursor, "messages_fts", base_sql)
  243. self._ensure_fts_schema(cursor, "messages_fts_trigram", trigram_sql)
  244. # Only recreate the CJK trigger this migration dropped. ``_ensure_fts_cjk_schema`` soft-fails
  245. # (never raises), so afterwards require a narrowed trigger or durable quarantine.
  246. if "messages_fts_cjk_update" in to_drop:
  247. try:
  248. self._ensure_fts_cjk_schema(cursor)
  249. except Exception:
  250. self._quarantine_cjk_after_update_of_migration(cursor)
  251. logger.exception("CJK FTS re-ensure after UPDATE OF migration failed")
  252. raise
  253. row = cursor.execute(
  254. "SELECT sql FROM sqlite_master WHERE type = 'trigger' AND name = ?", ("messages_fts_cjk_update",),
  255. ).fetchone()
  256. if not row or self._fts_update_trigger_needs_narrowing(row[0]):
  257. self._quarantine_cjk_after_update_of_migration(cursor)
  258. logger.warning(
  259. "CJK FTS UPDATE trigger missing or still broad after "
  260. "UPDATE OF migration; marked stale and unavailable"
  261. )
  262. logger.info("Migrated %d broad FTS UPDATE trigger(s) to AFTER UPDATE OF (no rebuild required)", len(to_drop))
  263. return len(to_drop)
  264. @staticmethod
  265. def _fts_index_is_misaligned_source(cursor: sqlite3.Cursor) -> bool:
  266. """True when ``messages_fts`` is still external-content over the raw
  267. ``messages`` table (FTS_STORAGE_VERSION < 3): its index holds a TRUNCATED
  268. projection for long tool rows that the checker/'delete' commands re-read
  269. as FULL content, a mismatch by construction. Such an index cannot be
  270. repaired in place — it must be 'rebuild'-filled from the aligned
  271. ``messages_fts_src`` view exactly once."""
  272. row = cursor.execute(
  273. "SELECT sql FROM sqlite_master WHERE type = 'table' AND name = 'messages_fts'"
  274. ).fetchone()
  275. return row is not None and "messages_fts_src" not in (row[0] or "")
  276. @staticmethod
  277. def _execute_ddl_skipping_settled_triggers(cursor: sqlite3.Cursor, ddl: str) -> None:
  278. """Run *ddl* statement by statement, skipping each ``DROP TRIGGER IF EXISTS x`` /
  279. ``CREATE TRIGGER IF NOT EXISTS x …`` pair whose trigger already exists with the same body.
  280. ``DROP TRIGGER IF EXISTS`` on an existing trigger takes SQLite's write lock (the
  281. ``CREATE … IF NOT EXISTS`` forms do not), so the unconditional drop+recreate that lets a
  282. changed trigger body roll out would otherwise block every open of a settled database
  283. behind a sibling process's transaction. ``sqlite_master.sql`` stores the CREATE text
  284. verbatim minus ``IF NOT EXISTS`` and the ``;``, so an exact comparison decides.
  285. """
  286. stored = dict(cursor.execute("SELECT name, sql FROM sqlite_master WHERE type = 'trigger'").fetchall())
  287. pending_drop: Optional[str] = None
  288. statement = ""
  289. for line in ddl.splitlines():
  290. statement += line + "\n"
  291. if not sqlite3.complete_statement(statement):
  292. continue
  293. statement, current = "", statement.strip()
  294. upper = current.upper()
  295. if upper.startswith("DROP TRIGGER IF EXISTS "):
  296. pending_drop = current
  297. continue
  298. if upper.startswith("CREATE TRIGGER IF NOT EXISTS "):
  299. desired = current.replace("IF NOT EXISTS ", "", 1).rstrip(";")
  300. if stored.get(desired.split(None, 3)[2]) == desired:
  301. pending_drop = None
  302. continue
  303. if pending_drop is not None:
  304. cursor.execute(pending_drop)
  305. pending_drop = None
  306. cursor.execute(current)
  307. if statement.strip():
  308. raise sqlite3.OperationalError("incomplete DDL statement")
  309. @staticmethod
  310. def _execute_ddl_script_transactional(cursor: sqlite3.Cursor, ddl: str) -> None:
  311. """Execute a DDL script without ``executescript``'s implicit commit."""
  312. statement = ""
  313. for line in ddl.splitlines():
  314. statement += line + "\n"
  315. if sqlite3.complete_statement(statement):
  316. cursor.execute(statement)
  317. statement = ""
  318. if statement.strip():
  319. raise sqlite3.OperationalError("incomplete FTS DDL statement")
  320. def _migrate_misaligned_fts_source(self, cursor: sqlite3.Cursor, *, legacy: bool) -> None:
  321. """Re-point ``messages_fts`` at the stable ``messages_fts_src`` projection view and
  322. rebuild it ONCE (FTS_STORAGE_VERSION 2 -> 3). A v1/v2 base index carries token streams
  323. the raw-``messages`` external-content source cannot read back (truncated long tool
  324. rows, and tool rows whose full content was indexed under an old high-water mark), so
  325. in-place continuity is not achievable — the ONLY valid transition is a full rebuild
  326. from the view, under the shared cross-process rebuild admission. Legacy inline DBs
  327. skip this entirely (their index is self-contained; they still take the DDL on the
  328. optimize path), and so does a definition no release of this code created (a newer
  329. release's, after a downgrade)."""
  330. if legacy or not base_index_recognised(cursor):
  331. return
  332. stale_view = base_source_view_is_stale(cursor)
  333. if not stale_view and not self._fts_index_is_misaligned_source(cursor):
  334. return
  335. has_messages = cursor.execute("SELECT 1 FROM messages LIMIT 1").fetchone() is not None
  336. def do_align() -> None:
  337. for name in _FTS_BASE_TRIGGERS:
  338. cursor.execute(f"DROP TRIGGER IF EXISTS {name}")
  339. cursor.execute("DROP TABLE IF EXISTS messages_fts")
  340. if stale_view:
  341. # CREATE VIEW IF NOT EXISTS never replaces a differing view; the index is rebuilt from it below.
  342. cursor.execute("DROP VIEW messages_fts_src")
  343. self._ensure_fts_schema(cursor, "messages_fts", FTS_SQL)
  344. if has_messages:
  345. cursor.execute("INSERT INTO messages_fts(messages_fts) VALUES('rebuild')")
  346. # The cleared markers below are shared with the trigram backfill: finish that index too, or rows
  347. # an interrupted backfill had not reached stay out of it with nothing left to resume them.
  348. if _meta_row(cursor, "fts_rebuild_high_water") is not None:
  349. trigram = self._fts_table_probe(cursor, "messages_fts_trigram")
  350. if trigram:
  351. cursor.execute("INSERT INTO messages_fts_trigram(messages_fts_trigram) VALUES('rebuild')")
  352. elif trigram is None:
  353. # No usable tokenizer here: detach the index so the first open that has one rebuilds it
  354. # through the missing-trigger repair in _init_fts.
  355. for name in _FTS_TRIGRAM_TRIGGERS:
  356. cursor.execute(f"DROP TRIGGER IF EXISTS {name}")
  357. cursor.execute(_CLEAR_REBUILD_MARKERS_SQL)
  358. cursor.execute(_DROP_RETIRED_TOOL_HIGH_WATER_SQL)
  359. if not has_messages:
  360. # Nothing indexed and nothing to index: swap the shape in place, no rebuild authority needed.
  361. cursor.execute("SAVEPOINT fts_align_empty")
  362. try:
  363. do_align()
  364. cursor.execute("RELEASE SAVEPOINT fts_align_empty")
  365. except BaseException:
  366. cursor.execute("ROLLBACK TO SAVEPOINT fts_align_empty")
  367. cursor.execute("RELEASE SAVEPOINT fts_align_empty")
  368. raise
  369. return
  370. self._run_admitted_startup_rebuild(cursor, do_align)
  371. @staticmethod
  372. def _sqlite_table_exists(cursor: sqlite3.Cursor, name: str) -> bool:
  373. return cursor.execute(
  374. "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = ?", (name,),
  375. ).fetchone() is not None
  376. def _migrate_trigram_cron_exclusion(self, cursor: sqlite3.Cursor) -> bool:
  377. """Install the source-filtered trigram view and purge historical rows (v29 cron exclusion,
  378. v30 subagent exclusion: both only change the view/trigger predicate and rebuild from it).
  379. Legacy inline indexes stay opt-in (their content is private to the vtable). A v1 external
  380. layout whose vtable still declares ``tool_calls`` is left to ``optimize-storage``: swapping
  381. the view underneath would make 'rebuild' read a column the view no longer has. Otherwise
  382. the inverted index still holds excluded rows until FTS5 rebuilds from the new view, which
  383. runs under the shared cross-process admission gate. Returns False to hold schema_version back."""
  384. if self._db_has_legacy_inline_fts(cursor) or self._db_has_trigram_tool_calls_projection(cursor):
  385. return True
  386. trigram_exists = self._fts_table_probe(cursor, "messages_fts_trigram")
  387. if trigram_exists is not True:
  388. # Absent: the normal ensure path creates/backfills it. None: this runtime cannot
  389. # safely inspect an existing one, so leave the version behind for retry.
  390. return trigram_exists is False
  391. for name in _FTS_TRIGRAM_TRIGGERS:
  392. cursor.execute(f"DROP TRIGGER IF EXISTS {name}")
  393. cursor.execute("DROP VIEW IF EXISTS messages_fts_trigram_src")
  394. if not self._ensure_fts_schema(cursor, "messages_fts_trigram", FTS_TRIGRAM_SQL):
  395. return False
  396. # Always rebuild while schema_version is behind, even if the view already has the new
  397. # predicate: a process can die between replacing the view and rebuilding/stamping.
  398. self._run_admitted_startup_rebuild(
  399. cursor,
  400. lambda: cursor.execute("INSERT INTO messages_fts_trigram(messages_fts_trigram) VALUES('rebuild')"),
  401. )
  402. return True
  403. def _quarantine_cjk_after_update_of_migration(self, cursor: sqlite3.Cursor) -> None:
  404. """Fail closed after dropping the CJK UPDATE trigger mid-migration: clear availability,
  405. persist ``fts_cjk_stale``, drop any residual trigger so a later open cannot
  406. IF-NOT-EXISTS over a gap."""
  407. self._fts_cjk_available = False
  408. try:
  409. self.set_meta(FTS_CJK_STALE_KEY, "1", cursor=cursor)
  410. except Exception:
  411. logger.debug("Could not persist CJK FTS stale breadcrumb", exc_info=True)
  412. try:
  413. cursor.execute("DROP TRIGGER IF EXISTS messages_fts_cjk_update")
  414. except Exception:
  415. logger.debug("Could not drop residual CJK UPDATE trigger after quarantine", exc_info=True)
  416. @staticmethod
  417. def _rebuild_fts_indexes(cursor: sqlite3.Cursor, *, legacy: bool = False, include_trigram: bool = True) -> None:
  418. """v23+ external-content 'rebuild'. It indexes EVERY row, so the deferred-backfill
  419. markers are cleared or the worker would re-insert covered rows (duplicates).
  420. ``legacy`` (pre-v23 inline layout) has no external-content 'rebuild' source, so it
  421. DELETEs + reinserts the concatenated content the legacy triggers produced."""
  422. tables = ("messages_fts", "messages_fts_trigram") if include_trigram else ("messages_fts",)
  423. for tbl in tables:
  424. if legacy:
  425. cursor.execute(f"DELETE FROM {tbl}")
  426. cursor.execute(f"INSERT INTO {tbl}(rowid, content) SELECT id, {_LEGACY_INLINE_CONCAT_SQL}FROM messages")
  427. else:
  428. cursor.execute(f"INSERT INTO {tbl}({tbl}) VALUES('rebuild')")
  429. if not legacy:
  430. cursor.execute(_CLEAR_REBUILD_MARKERS_SQL)
  431. def _fts_table_probe(self, cursor: sqlite3.Cursor, table_name: str) -> Optional[bool]:
  432. """True = queryable, False = absent, None = FTS module/tokenizer missing or content
  433. undecodable (index degraded, store accessible). Invalid UTF-8 surfaces as a bare
  434. UnicodeDecodeError on some builds and OperationalError("Could not decode to UTF-8")
  435. on others; both are caught so the probe never raises into init/recovery flows.
  436. Anything else (malformed schema, corrupt vtable) re-raises."""
  437. try:
  438. cursor.execute(f"SELECT * FROM {table_name} LIMIT 0")
  439. return True
  440. except UnicodeDecodeError as exc:
  441. decode_exc = exc
  442. except sqlite3.OperationalError as exc:
  443. if self._is_fts5_unavailable_error(exc):
  444. # A missing trigram tokenizer only affects trigram search; only a missing
  445. # FTS5 module disables FTS entirely.
  446. if self._is_trigram_unavailable_error(exc):
  447. self._warn_trigram_unavailable(exc)
  448. else:
  449. self._warn_fts5_unavailable(exc)
  450. return None
  451. if "no such table" in str(exc).lower():
  452. return False
  453. if "decode to utf-8" not in str(exc).lower():
  454. raise
  455. decode_exc = exc
  456. logger.warning(
  457. "%s probe encountered invalid UTF-8 in FTS content; "
  458. "search may return incomplete results until FTS is rebuilt: %s", table_name, decode_exc,
  459. )
  460. return None
  461. # ── Stale-FTS recovery ─────────────────────────────────────────────────
  462. def _defer_stale_fts_for_holders(self, cursor: sqlite3.Cursor, foreign_holders) -> bool:
  463. """Record a deferral diagnostic for the foreign processes holding the DB; True = defer
  464. (holders remain). After ``_FTS_HOLDER_ESCALATE_ATTEMPTS`` deferrals spanning
  465. ``_FTS_HOLDER_ESCALATE_SECONDS``, provably inactive orphan Desktop backends are
  466. reaped and the holders re-checked. The orphan reap is the only exit, so a supervised
  467. peer (never an orphan) blocks forever: once the SAME PID set has blocked
  468. ``_FTS_HOLDER_FUTILE_ATTEMPTS`` deferrals over ``_FTS_HOLDER_FUTILE_SECONDS`` the
  469. record is marked ``futile`` and the escalation names the holders and the remedy that
  470. works from inside a gateway session (stop only the other holder; this process's own
  471. retry tick admits the rebuild). A changed holder set restarts that window."""
  472. now = time.time()
  473. try:
  474. row = cursor.execute(
  475. "SELECT value FROM state_meta WHERE key = ? LIMIT 1", (FTS_REBUILD_DEFERRAL_KEY,),
  476. ).fetchone()
  477. except sqlite3.Error:
  478. row = None
  479. parsed = safe_json_loads(row[0]) if row else None
  480. record = parsed if isinstance(parsed, dict) else {}
  481. try:
  482. first_seen = float(record.get("first_seen", now))
  483. attempts = int(record.get("attempts", 0)) + 1
  484. holders_since = float(record.get("holders_since", now))
  485. holders_attempts = int(record.get("holders_attempts", 0)) + 1
  486. except (TypeError, ValueError):
  487. first_seen, attempts, holders_since, holders_attempts = now, 1, now, 1
  488. if first_seen > now or first_seen < 0:
  489. first_seen = now
  490. holder_pids = sorted({pid for pid, _path in foreign_holders if pid > 0})
  491. if holder_pids != record.get("holder_pids"):
  492. holders_since, holders_attempts = now, 1
  493. if attempts >= _FTS_HOLDER_ESCALATE_ATTEMPTS and now - first_seen >= _FTS_HOLDER_ESCALATE_SECONDS:
  494. reaped = self._reap_inactive_orphan_desktop_holders(
  495. foreign_holders, min_age_seconds=_FTS_HOLDER_ESCALATE_SECONDS,
  496. )
  497. if reaped:
  498. logger.error(
  499. "Reaped inactive orphan Desktop backend(s) %s after %d "
  500. "state.db FTS rebuild deferrals; checking holders again.", reaped, attempts,
  501. )
  502. foreign_holders = self._foreign_state_db_holders()
  503. holder_pids = sorted({pid for pid, _path in foreign_holders if pid > 0})
  504. futile = bool(holder_pids) and (
  505. holders_attempts >= _FTS_HOLDER_FUTILE_ATTEMPTS and now - holders_since >= _FTS_HOLDER_FUTILE_SECONDS
  506. )
  507. diagnostic = {
  508. "first_seen": first_seen, "last_seen": now, "attempts": attempts, "holder_pids": holder_pids,
  509. "holders_since": holders_since, "holders_attempts": holders_attempts, "futile": futile,
  510. }
  511. cursor.execute(
  512. "INSERT INTO state_meta (key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",
  513. (FTS_REBUILD_DEFERRAL_KEY, json.dumps(diagnostic, sort_keys=True)),
  514. )
  515. if not foreign_holders:
  516. self._fts_deferred_holder_pids = None
  517. return False
  518. self._fts_deferred_holder_pids = holder_pids
  519. if futile:
  520. logger.error(
  521. "state.db FTS repair has been blocked by the same holder(s) for %d deferrals over %.0f min "
  522. "(%s); waiting is futile. Stop ONLY the other holder(s) — this process keeps running and its "
  523. "own retry admits the rebuild within %.0fs of the holder leaving. `hermes doctor` shows this.",
  524. holders_attempts, (now - holders_since) / 60.0,
  525. ", ".join(f"pid {pid}: {_holder_cmdline(pid)}" for pid in holder_pids), _FTS_STALE_RETRY_SECONDS,
  526. )
  527. elif attempts >= _FTS_HOLDER_ESCALATE_ATTEMPTS and now - first_seen >= _FTS_HOLDER_ESCALATE_SECONDS:
  528. logger.error(
  529. "state.db FTS repair remains blocked after %d deferrals by holder(s) %s. Stop the listed "
  530. "processes (this process's own retry then rebuilds), or run `hermes sessions optimize-storage` "
  531. "with every holder stopped. `hermes doctor` reports this degraded state.", attempts, foreign_holders,
  532. )
  533. logger.warning(
  534. "Deferred stale state.db FTS rebuild while foreign processes "
  535. "hold the database or WAL sidecars (%s); canonical writes and LIKE search remain available (deferral %d).",
  536. foreign_holders, attempts,
  537. )
  538. return True
  539. def _recover_stale_fts(self, cursor: sqlite3.Cursor, *, legacy: bool, timeout_seconds=None) -> bool:
  540. """Atomically rebuild stale base/trigram indexes and resume syncing. *timeout_seconds*
  541. bounds the admission wait (None = full startup budget, ``0`` = non-blocking retry).
  542. Fails closed: holders or a lost admission race leave the breadcrumb set."""
  543. foreign_holders = self._foreign_state_db_holders()
  544. if foreign_holders and self._defer_stale_fts_for_holders(cursor, foreign_holders):
  545. return False
  546. with fts_rebuild_admission(self.db_path, timeout_seconds=timeout_seconds) as admitted:
  547. if not admitted:
  548. logger.warning(
  549. "Deferred stale state.db FTS rebuild: another process holds the rebuild authority; "
  550. "canonical writes and LIKE search remain available."
  551. )
  552. return False
  553. return self._recover_stale_fts_locked(cursor, legacy=legacy)
  554. def retry_deferred_fts_recovery(self) -> bool:
  555. """Retry a deferred stale-FTS rebuild (gateway housekeeping tick). ``_recover_stale_fts``
  556. fails closed at open, leaving search on LIKE; live write/search paths must never
  557. start a full rebuild, and a gateway opens state.db once for days, so "next open"
  558. never comes. Bounded doubling backoff, non-blocking admission, no new thread. True
  559. only when the index was rebuilt and sync triggers restored. Never raises.
  560. This is the in-process retry: bounded backoff from ``_FTS_STALE_RETRY_SECONDS`` doubling to
  561. ``_FTS_STALE_RETRY_MAX_SECONDS``, non-blocking admission (``timeout=0``) so a live holder is skipped
  562. and tried again later, no new thread — the caller is an existing periodic tick (gateway
  563. housekeeping). See #100108, #97940.
  564. """
  565. if not self._fts_stale:
  566. return False
  567. if self._quarantine_reason() is not None:
  568. # Quarantined: never run FTS DDL/DML against a damaged image or a stale/replaced generation
  569. # (mirrors _try_wal_checkpoint / close). Reset the backoff so a future un-quarantine starts
  570. # from the default interval.
  571. self._fts_stale_retry_after = 0.0
  572. self._fts_stale_retry_interval = 0.0
  573. return False
  574. if self.read_only or self._conn is None:
  575. return False
  576. now = time.monotonic()
  577. deferred_pids = getattr(self, "_fts_deferred_holder_pids", None)
  578. if now < getattr(self, "_fts_stale_retry_after", 0.0):
  579. # The backoff was earned by a specific holder set; once that set changes (the other
  580. # service stopped) a capped backoff would idle up to an hour with nothing blocking (#106393).
  581. if deferred_pids is None or sorted(
  582. {pid for pid, _path in self._foreign_state_db_holders() if pid > 0}
  583. ) == deferred_pids:
  584. return False
  585. self._fts_stale_retry_interval = 0.0
  586. interval = float(getattr(self, "_fts_stale_retry_interval", 0.0))
  587. if interval <= 0.0:
  588. interval = _FTS_STALE_RETRY_SECONDS
  589. self._fts_stale_retry_after = now + interval
  590. self._fts_stale_retry_interval = min(
  591. max(interval, _FTS_STALE_RETRY_SECONDS, 1.0) * 2.0, _FTS_STALE_RETRY_MAX_SECONDS,
  592. )
  593. try:
  594. with self._lock:
  595. if self._conn is None or not self._fts_stale:
  596. return False
  597. cursor = self._conn.cursor()
  598. legacy = self._db_has_legacy_inline_fts(cursor)
  599. recovered = self._recover_stale_fts(cursor, legacy=legacy, timeout_seconds=0.0)
  600. if recovered:
  601. # CJK was detached alongside the base indexes; its own ensure path
  602. # decides when it comes back online.
  603. self._ensure_fts_cjk_schema(cursor)
  604. self._fts_stale_retry_interval = 0.0
  605. with contextlib.suppress(sqlite3.Error):
  606. self._conn.commit()
  607. return recovered
  608. except Exception:
  609. logger.warning(
  610. "In-process retry of the deferred stale state.db FTS rebuild failed; will retry later.", exc_info=True,
  611. )
  612. return False
  613. def _recover_stale_fts_locked(self, cursor: sqlite3.Cursor, *, legacy: bool) -> bool:
  614. """Body of :meth:`_recover_stale_fts`; caller holds rebuild authority. One write
  615. transaction, so no canonical writer slips between rebuild and trigger restoration."""
  616. try:
  617. trigram_present = self._fts_table_probe(cursor, "messages_fts_trigram") is True
  618. except (sqlite3.DatabaseError, UnicodeDecodeError):
  619. # A corrupt vtable may fail even a LIMIT 0 probe; still include it in the drop-and-recreate.
  620. include_trigram = True
  621. else:
  622. include_trigram = trigram_present or (not legacy and self._trigram_tokenizer_available(cursor))
  623. drop_sql = "".join(f"DROP TRIGGER IF EXISTS {trigger};" for trigger in _FTS_TRIGGERS)
  624. if include_trigram:
  625. drop_sql += "DROP TABLE IF EXISTS messages_fts_trigram;"
  626. drop_sql += "DROP VIEW IF EXISTS messages_fts_trigram_src;DROP TABLE IF EXISTS messages_fts;"
  627. if legacy:
  628. rebuild_sql = LEGACY_FTS_SQL + (LEGACY_FTS_TRIGRAM_SQL if include_trigram else "")
  629. rebuild_sql += _legacy_inline_reinsert_sql("messages_fts", 16)
  630. if include_trigram:
  631. rebuild_sql += _legacy_inline_reinsert_sql("messages_fts_trigram", 20, delete_first=True)
  632. else:
  633. rebuild_sql = FTS_SQL + (FTS_TRIGRAM_SQL if include_trigram else "")
  634. rebuild_sql += "INSERT INTO messages_fts(messages_fts) VALUES('rebuild');"
  635. if include_trigram:
  636. rebuild_sql += "INSERT INTO messages_fts_trigram(messages_fts_trigram) VALUES('rebuild');"
  637. rebuild_sql += _CLEAR_REBUILD_MARKERS_SQL + ";" + _DROP_RETIRED_TOOL_HIGH_WATER_SQL + ";"
  638. recovery_sql = (
  639. "BEGIN IMMEDIATE;" + drop_sql + rebuild_sql
  640. + f"DELETE FROM state_meta WHERE key IN ('{FTS_STALE_KEY}', '{FTS_REBUILD_DEFERRAL_KEY}');COMMIT;"
  641. )
  642. try:
  643. cursor.executescript(recovery_sql)
  644. except sqlite3.DatabaseError as exc:
  645. with contextlib.suppress(sqlite3.Error):
  646. self._conn.rollback()
  647. # Stale indexes must stay detached even on builds whose DDL transaction behavior differs.
  648. self._drop_all_fts_triggers(cursor)
  649. self._conn.commit()
  650. logger.error(
  651. "Automatic rebuild of stale FTS indexes failed (%s); "
  652. "canonical writes remain enabled with FTS detached.", exc,
  653. )
  654. return False
  655. self._fts_stale = False
  656. self._fts_enabled = True
  657. self._trigram_available = include_trigram
  658. logger.warning("Rebuilt stale state.db FTS indexes from canonical messages and restored sync triggers.")
  659. return True
  660. def _trigram_tokenizer_available(self, cursor: sqlite3.Cursor) -> bool:
  661. """Probe trigram support without publishing a persistent FTS object."""
  662. probe = "temp.hermes_fts5_trigram_probe"
  663. cursor.execute(f"DROP TABLE IF EXISTS {probe}")
  664. try:
  665. cursor.execute(f"CREATE VIRTUAL TABLE {probe} USING fts5(content, tokenize='trigram')")
  666. except sqlite3.OperationalError as exc:
  667. if not self._is_trigram_unavailable_error(exc):
  668. raise
  669. self._warn_trigram_unavailable(exc)
  670. return False
  671. finally:
  672. cursor.execute(f"DROP TABLE IF EXISTS {probe}")
  673. return True
  674. # ── Declarative column reconciliation ──────────────────────────────────
  675. @staticmethod
  676. def _parse_schema_columns(schema_sql: str) -> dict[str, dict[str, str]]:
  677. """Expected columns per table: execute SCHEMA_SQL in an in-memory database and read
  678. PRAGMA table_info (no regex). Memoized on disk keyed by a DDL hash (~85ms per
  679. startup otherwise); only the reference-side parse is cached — diffing the LIVE
  680. database still runs every startup. A corrupt/stale cache degrades to recomputation."""
  681. cache_path = None
  682. schema_hash = hashlib.sha256(schema_sql.encode("utf-8")).hexdigest()
  683. with contextlib.suppress(Exception): # missing/corrupt cache → recompute below
  684. # Late import: resolves a test-patched hermes_constants.get_hermes_home.
  685. from hermes_constants import get_hermes_home as _home
  686. cache_path = _home() / "cache" / "schema_columns.json"
  687. blob = json.loads(cache_path.read_text(encoding="utf-8"))
  688. tables = blob.get("tables") if isinstance(blob, dict) and blob.get("schema_hash") == schema_hash else None
  689. if isinstance(tables, dict) and all(
  690. isinstance(cols, dict) and all(isinstance(v, str) for v in cols.values()) for cols in tables.values()
  691. ):
  692. return tables
  693. ref = sqlite3.connect(":memory:")
  694. try:
  695. ref.executescript(schema_sql)
  696. table_columns: dict[str, dict[str, str]] = {}
  697. for (tbl,) in ref.execute(
  698. "SELECT name FROM sqlite_master WHERE type='table' AND name NOT LIKE 'sqlite_%'"
  699. ).fetchall():
  700. cols: dict[str, str] = {}
  701. info = ref.execute(f'PRAGMA table_info("{tbl}")').fetchall()
  702. for _cid, col_name, col_type, notnull, default, pk in info:
  703. # Reconstruct the type expression for ALTER TABLE ADD COLUMN
  704. parts = [col_type] if col_type else []
  705. if notnull and not pk:
  706. parts.append("NOT NULL")
  707. if default is not None:
  708. parts.append(f"DEFAULT {default}")
  709. cols[col_name] = " ".join(parts)
  710. table_columns[tbl] = cols
  711. finally:
  712. ref.close()
  713. if cache_path is not None:
  714. with contextlib.suppress(Exception): # cache write is best-effort
  715. cache_path.parent.mkdir(parents=True, exist_ok=True)
  716. fd, tmp = tempfile.mkstemp(dir=str(cache_path.parent), prefix=".schema_columns.")
  717. with os.fdopen(fd, "w", encoding="utf-8") as fh:
  718. json.dump({"schema_hash": schema_hash, "tables": table_columns}, fh)
  719. os.replace(tmp, cache_path)
  720. return table_columns
  721. def _reconcile_columns(self, cursor: sqlite3.Cursor) -> None:
  722. """ADD every SCHEMA_SQL column missing from the live tables (SCHEMA_SQL is the single
  723. source of truth; column additions need no version-gated migration)."""
  724. expected = self._parse_schema_columns(SCHEMA_SQL)
  725. for table_name, declared_cols in expected.items():
  726. try:
  727. rows = cursor.execute(f'PRAGMA table_info("{table_name}")').fetchall()
  728. except sqlite3.OperationalError:
  729. continue # Table doesn't exist yet (shouldn't happen after executescript)
  730. if not rows:
  731. continue # PRAGMA table_info returns [] for a missing table; the DDL creates it whole
  732. # PRAGMA table_info rows: (cid, name, type, notnull, dflt_value, pk)
  733. live_cols = {row[1] for row in rows}
  734. for col_name, col_type in declared_cols.items():
  735. if col_name in live_cols:
  736. continue
  737. try:
  738. cursor.execute(f'ALTER TABLE "{table_name}" ADD COLUMN {_q(col_name)} {col_type}')
  739. except sqlite3.OperationalError as exc:
  740. message = str(exc).lower()
  741. if "duplicate column" in message:
  742. # A sibling process won the ADD race; store is correct.
  743. logger.debug("reconcile %s.%s: %s", table_name, col_name, exc)
  744. continue
  745. if is_sqlite_lock_error(exc):
  746. # Swallowing lock contention left the store half-reconciled ("no such
  747. # column" on every read). Re-raise so the lock-patience wrapper retries init.
  748. raise
  749. # Anything else permanently strands the store behind SCHEMA_SQL — be loud.
  750. logger.warning(
  751. "reconcile %s.%s failed; store remains behind SCHEMA_SQL: %s", table_name, col_name, exc,
  752. )
  753. @staticmethod
  754. def _live_pk_columns(cursor: sqlite3.Cursor, table: str) -> Optional[list[str]]:
  755. """PRIMARY KEY column names of *table* in key order; None when the table is
  756. missing or has no columns (SCHEMA_SQL creates it correctly)."""
  757. try:
  758. rows = cursor.execute(f'PRAGMA table_info("{table}")').fetchall()
  759. except sqlite3.OperationalError:
  760. rows = None
  761. if not rows:
  762. return None
  763. # row: (cid, name, type, notnull, dflt_value, pk)
  764. return [r[1] for r in sorted((r for r in rows if r[5]), key=lambda r: r[5])]
  765. @staticmethod
  766. def _rebuild_table(cursor: sqlite3.Cursor, table: str, legacy_name: str, ddl: str, copy_sql: str, indexes=()) -> None:
  767. """RENAME *table* to *legacy_name*, CREATE it fresh from *ddl*, copy rows back with
  768. *copy_sql*, DROP the legacy copy, recreate *indexes* — as ONE write transaction.
  769. The writer connection is autocommit (``isolation_level=None``), so without an explicit
  770. BEGIN each statement commits on its own and a sibling process opening the same state.db
  771. between RENAME and CREATE runs SCHEMA_SQL's ``CREATE TABLE IF NOT EXISTS`` first: our
  772. CREATE then fails with "table already exists", every row is stranded in *legacy_name*
  773. and the live table is empty. BEGIN IMMEDIATE holds the write lock for the whole rebuild
  774. so the sibling blocks (and retries under its lock patience) instead of interleaving;
  775. any failure rolls the RENAME back."""
  776. conn = cursor.connection
  777. if conn.in_transaction: # caller already owns the transaction
  778. SessionSchemaMixin._rebuild_table_statements(cursor, table, legacy_name, ddl, copy_sql, indexes)
  779. return
  780. cursor.execute("BEGIN IMMEDIATE")
  781. try:
  782. SessionSchemaMixin._rebuild_table_statements(cursor, table, legacy_name, ddl, copy_sql, indexes)
  783. except BaseException:
  784. cursor.execute("ROLLBACK")
  785. raise
  786. cursor.execute("COMMIT")
  787. @staticmethod
  788. def _rebuild_table_statements(cursor, table, legacy_name, ddl, copy_sql, indexes) -> None:
  789. cursor.execute(f"ALTER TABLE {table} RENAME TO {legacy_name}")
  790. cursor.execute(ddl)
  791. cursor.execute(copy_sql)
  792. cursor.execute(f"DROP TABLE {legacy_name}")
  793. for sql in indexes:
  794. cursor.execute(sql)
  795. def _heal_gateway_routing_pk(self, cursor: sqlite3.Cursor) -> None:
  796. """Rebuild ``gateway_routing`` when its PRIMARY KEY predates scoping (``session_key TEXT
  797. PRIMARY KEY``): the reconciler ADDs ``scope`` but SQLite cannot ALTER a PK, so every
  798. routing write fails (ON CONFLICT mismatch / cross-scope UNIQUE violation). Newest
  799. row wins a cross-scope session_key collision (INSERT OR REPLACE in updated_at order).
  800. Early builds of the routing-index migration (#59203) created the table with ``session_key TEXT
  801. PRIMARY KEY`` and no ``scope`` column. ``_reconcile_columns()`` ADDs the missing ``scope`` column on
  802. those databases, but SQLite cannot ALTER a primary key, so the shipped composite ``PRIMARY KEY
  803. (scope, session_key)`` never lands. On such tables every write path is broken:
  804. """
  805. pk_cols = self._live_pk_columns(cursor, "gateway_routing")
  806. if pk_cols is None or pk_cols == ["scope", "session_key"]:
  807. return
  808. logger.info(
  809. "gateway_routing has legacy primary key %r; rebuilding with composite (scope, session_key) key", pk_cols,
  810. )
  811. self._rebuild_table(
  812. cursor, "gateway_routing", "gateway_routing_legacy_pk",
  813. """CREATE TABLE gateway_routing (
  814. scope TEXT NOT NULL DEFAULT '',
  815. session_key TEXT NOT NULL,
  816. entry_json TEXT NOT NULL,
  817. updated_at REAL NOT NULL,
  818. PRIMARY KEY (scope, session_key)
  819. )""",
  820. "INSERT OR REPLACE INTO gateway_routing (scope, session_key, entry_json, updated_at) "
  821. "SELECT COALESCE(scope, ''), session_key, entry_json, updated_at "
  822. "FROM gateway_routing_legacy_pk ORDER BY updated_at ASC",
  823. )
  824. def _heal_session_model_usage_pk(self, cursor: sqlite3.Cursor) -> None:
  825. """Rebuild ``session_model_usage`` when its PRIMARY KEY lacks ``task``: installs already at
  826. v22+ when ``task`` landed carry the 5-column PK, the reconciler ADDs ``task`` but
  827. SQLite cannot ALTER a PK, and the v22 rebuild is unreachable — every upsert then
  828. fails (ON CONFLICT mismatch), silently zeroing accounting. Idempotent. FK-off
  829. window: INSERT OR IGNORE does NOT suppress FK violations, so an orphaned usage row
  830. would abort the rebuild (PRAGMA foreign_keys is a no-op inside a transaction; none
  831. is open here). OR IGNORE: COALESCE(task, '') on legacy NULL rows can collide with a
  832. genuine ''-task row — keep the first.
  833. Installs whose ``state.db`` reached ``schema_version >= 22`` before the ``task`` dimension was added
  834. carry a 5-column PRIMARY KEY ``(session_id, model, billing_provider, billing_base_url,
  835. billing_mode)``. See #73823.
  836. """
  837. pk_cols = self._live_pk_columns(cursor, "session_model_usage")
  838. if pk_cols is None or "task" in pk_cols:
  839. return
  840. logger.info(
  841. "session_model_usage has legacy primary key %r (missing task); rebuilding with composite 6-column key",
  842. sorted(pk_cols),
  843. )
  844. cursor.execute("PRAGMA foreign_keys=OFF")
  845. try:
  846. self._rebuild_table(
  847. cursor, "session_model_usage", "session_model_usage_legacy_pk", _SESSION_MODEL_USAGE_HEAL_DDL,
  848. # v20: per-model usage attribution (issue #51607). Going forward update_token_counts()
  849. # records each API call into session_model_usage keyed by the live model, but existing
  850. # sessions only have their aggregate totals on the sessions row. Seed one usage row per
  851. # historical session from those aggregates so insights reads uniformly from the new table.
  852. # INSERT OR IGNORE keeps it idempotent: if newer code already wrote a (session_id, model,
  853. # provider) row for a session, the PK conflict skips the stale aggregate rather than
  854. # doubling it.
  855. """INSERT OR IGNORE INTO session_model_usage (
  856. session_id, model, billing_provider, billing_base_url,
  857. billing_mode, task, api_call_count, input_tokens,
  858. output_tokens, cache_read_tokens, cache_write_tokens,
  859. reasoning_tokens, estimated_cost_usd, actual_cost_usd,
  860. cost_status, cost_source, first_seen, last_seen
  861. )
  862. SELECT session_id, model,
  863. COALESCE(billing_provider, ''),
  864. COALESCE(billing_base_url, ''),
  865. COALESCE(billing_mode, ''),
  866. COALESCE(task, ''),
  867. api_call_count, input_tokens,
  868. output_tokens, cache_read_tokens, cache_write_tokens,
  869. reasoning_tokens, estimated_cost_usd, actual_cost_usd,
  870. cost_status, cost_source, first_seen, last_seen
  871. FROM session_model_usage_legacy_pk""",
  872. _SESSION_MODEL_USAGE_INDEX_SQL,
  873. )
  874. except sqlite3.OperationalError as exc:
  875. logger.debug("session_model_usage PK heal skipped: %s", exc)
  876. finally:
  877. cursor.execute("PRAGMA foreign_keys=ON")
  878. # ── _init_schema ───────────────────────────────────────────────────────
  879. def _init_schema(self):
  880. """Create tables and FTS if missing, reconcile columns, run data migrations. Column
  881. additions are declarative via _reconcile_columns(), so reordered migrations can
  882. never skip a column; schema_version remains for data migrations only."""
  883. # Startup-watchdog lease: on multi-GB files this is I/O-bound (near-zero CPU), which
  884. # the watchdog's CPU fallback would misread as a parked deadlock.
  885. # Declare a startup-watchdog progress lease before potentially long synchronous work: on multi-GB
  886. # state.db files the reconciliation + version-gated data migrations below are legitimately slow and
  887. # can be I/O-bound (near-zero CPU), which the watchdog's CPU fallback would misread as a parked
  888. # deadlock (OOF-298 / PR #89750). Single lease is deliberate: this is the one pre-loop phase that
  889. # can legitimately exceed the 300s default deadline (multi-GB DBs), and the lease is clamped to
  890. # _MAX_LEASE_S=900. Honest worst case: a genuinely wedged DB init delays supervisor respawn by up to
  891. # the lease duration. Per-chunk renewal would shrink that, but adds complexity to the migration
  892. # loops for a rare failure mode.
  893. report_startup_progress(600.0, phase="state_db_init_schema")
  894. cursor = self._conn.cursor()
  895. cursor.executescript(SCHEMA_SQL)
  896. # Column reconciliation, then the two table-shape repairs ADD COLUMN cannot express.
  897. self._reconcile_columns(cursor)
  898. self._heal_gateway_routing_pk(cursor)
  899. # Rebuild session_model_usage if its PRIMARY KEY lacks the ``task`` column (5-column PK on installs
  900. # already at v22+ when the column landed — the version-gated rebuild is unreachable there, #73823).
  901. # Same PK-rebuild constraint as gateway_routing above.
  902. self._heal_session_model_usage_pk(cursor)
  903. # Indexes referencing reconciler-added columns must be created AFTER _reconcile_columns
  904. # (in SCHEMA_SQL the executescript would fail on legacy DBs).
  905. # Heal NULL ``active`` rows unconditionally on every startup. On real-world DBs the reconciler-added
  906. # ``active`` column can lack its NOT NULL DEFAULT 1 (older reconciler builds reconstructed the type
  907. # without the default — see #51646: PRAGMA shows (17,'active','INTEGER',0,None,0) in the wild), so
  908. # INSERTs that omitted the column wrote NULL and the ``WHERE active = 1`` transcript loaders hid the
  909. # whole history. The INSERTs now set active=1 explicitly; this idempotent repair un-hides rows
  910. # written before the fix. It was previously gated at ``current_version < 12`` which never re-ran for
  911. # already-v12+ databases.
  912. try:
  913. cursor.execute(
  914. "CREATE INDEX IF NOT EXISTS idx_messages_platform_msg_id "
  915. "ON messages(session_id, platform_message_id) WHERE platform_message_id IS NOT NULL"
  916. )
  917. except sqlite3.OperationalError as exc:
  918. logger.debug("idx_messages_platform_msg_id create skipped: %s", exc)
  919. self._execute_ddl_skipping_settled_triggers(cursor, DEFERRED_INDEX_SQL) # same ordering constraint (``active``)
  920. # Heal NULL ``active`` rows on every startup: older reconciler builds added ``active``
  921. # without NOT NULL DEFAULT 1, so ``WHERE active = 1`` loaders hid whole histories. A
  922. # ``current_version < 12`` gate never re-ran for already-v12+ databases.
  923. # Read before writing: an UPDATE takes the write lock even when it matches no rows, so
  924. # the unconditional form blocked every open behind a sibling's write transaction. The
  925. # probe is short-circuited on the modern NOT NULL column and index-served on legacy
  926. # ones. Deliberately not INDEXED BY (raises "no query solution" on NOT NULL columns).
  927. with contextlib.suppress(sqlite3.OperationalError):
  928. if cursor.execute("SELECT 1 FROM messages WHERE active IS NULL LIMIT 1").fetchone() is not None:
  929. cursor.execute("UPDATE messages SET active = 1 WHERE active IS NULL")
  930. fts5_available = self._sqlite_supports_fts5(cursor)
  931. stale_row = cursor.execute("SELECT 1 FROM state_meta WHERE key = ? LIMIT 1", (FTS_STALE_KEY,)).fetchone()
  932. self._fts_stale = stale_row is not None
  933. if self._fts_stale:
  934. # A prior process detached FTS after corruption; stay detached until a full rebuild.
  935. self._drop_all_fts_triggers(cursor)
  936. if not fts5_available:
  937. # Existing FTS triggers would still fire though this runtime cannot read their
  938. # targets. Drop only the triggers; a future FTS5 runtime recreates them.
  939. self._drop_fts_triggers(cursor)
  940. row = cursor.execute("SELECT version FROM schema_version LIMIT 1").fetchone()
  941. if row is None:
  942. cursor.execute("INSERT INTO schema_version (version) VALUES (?)", (SCHEMA_VERSION,))
  943. # Store provenance so fresh vs wiped stores are distinguishable.
  944. # See #97568.
  945. now_iso = datetime.datetime.now(datetime.UTC).isoformat()
  946. cursor.executemany(
  947. "INSERT OR IGNORE INTO state_meta (key, value) VALUES (?, ?)",
  948. [("store_instance_id", str(uuid.uuid4())), ("store_created_at_utc", now_iso),
  949. (_MESSAGE_UID_BACKFILL_DONE, "1")], # a fresh store has no legacy rows to backfill
  950. )
  951. else:
  952. self._run_data_migrations(cursor, row[0], fts5_available)
  953. self._ensure_unique_title_index(cursor)
  954. if fts5_available:
  955. self._init_fts(cursor)
  956. self._stamp_settled_fts_layout(cursor)
  957. self._conn.commit()
  958. def _stamp_settled_fts_layout(self, cursor: sqlite3.Cursor) -> None:
  959. """Write ``fts_storage_version`` once the stored layout is exactly current and nothing is in flight.
  960. The only open-time writer of the stamp, run after _init_fts so a fresh store is stamped on creation
  961. and a realigned base index is stamped only when the trigram also matches. A definition this code does
  962. not recognise (a newer release's) is never stamped down to this release's version. An interrupted
  963. optimize (markers, trash, an empty external index against non-empty messages) keeps the stamp
  964. unwritten so the resume offer stays alive. Reads before writing: a settled store never takes the write
  965. lock here.
  966. """
  967. if (
  968. self._fts_stale
  969. or not fts_layout_current(cursor)
  970. or _meta_row(cursor, "fts_rebuild_high_water") is not None
  971. or self._has_fts_trash(cursor)
  972. or self._fts_external_index_empty_with_messages(cursor)
  973. ):
  974. return
  975. if cursor.execute(
  976. "SELECT 1 FROM state_meta WHERE key = 'fts_storage_version' AND value = ? LIMIT 1",
  977. (str(FTS_STORAGE_VERSION),),
  978. ).fetchone() is None:
  979. cursor.execute(_STATE_META_UPSERT_SQL, ("fts_storage_version", str(FTS_STORAGE_VERSION)))
  980. def _run_data_migrations(self, cursor: sqlite3.Cursor, current_version: int, fts5_available: bool) -> None:
  981. """Version-gated chain for DATA migrations only (row backfills); column additions never
  982. belong here. Advances schema_version at the end unless FTS5 is unavailable."""
  983. # Renew the lease: the chain can rewrite whole tables on large DBs.
  984. report_startup_progress(600.0, phase="state_db_data_migrations")
  985. # (v10 trigram backfill and v11 inline FTS re-index were superseded by v23 and removed.)
  986. # v11 (SUPERSEDED by v23): re-index FTS5 tables to cover tool_name + tool_calls in inline mode
  987. # (#16751). v23 drops and rebuilds both FTS tables in external-content form, so running the v11
  988. # inline backfill first would only burn startup time and WAL space before v23 throws the work away —
  989. # and its inline INSERT shape no longer matches the current external-content FTS_SQL anyway. Kept
  990. # only for source archaeology; unreachable while SCHEMA_VERSION >= 23.
  991. if current_version < 16:
  992. # v16: tag delegate subagent rows so pickers stay clean after parent deletes orphan them.
  993. with contextlib.suppress(sqlite3.OperationalError):
  994. cursor.execute(
  995. "UPDATE sessions SET model_config = json_set("
  996. "COALESCE(model_config, '{}'), '$._delegate_from', parent_session_id) "
  997. f"WHERE parent_session_id IS NOT NULL "
  998. f"AND {_sql_json_extract('model_config', '$._delegate_from')} IS NULL "
  999. f"AND {_ephemeral_child_sql('sessions')}"
  1000. )
  1001. cursor.execute(
  1002. "UPDATE sessions SET model_config = json_set("
  1003. "COALESCE(model_config, '{}'), '$._delegate_from', '__orphaned__') WHERE parent_session_id IS NULL "
  1004. f"AND {_sql_json_extract('model_config', '$._delegate_from')} IS NULL "
  1005. f"AND {_sql_json_extract('model_config', '$._branched_from')} IS NULL "
  1006. "AND title IS NULL AND message_count <= 25 AND EXISTS (SELECT 1 FROM messages m "
  1007. " WHERE m.session_id = sessions.id AND m.role = 'tool') "
  1008. "AND NOT EXISTS (SELECT 1 FROM sessions ch "
  1009. " WHERE ch.parent_session_id = sessions.id)"
  1010. )
  1011. if current_version < 18:
  1012. # v18: best-effort gateway metadata backfill from sessions.json.
  1013. try:
  1014. # Backfill display_name / origin_json / expiry_finalized from sessions.json so pre-migration
  1015. # gateway sessions are discoverable from state.db without the JSON index. See #9006.
  1016. self._backfill_gateway_metadata_from_sessions_json(cursor)
  1017. except Exception as exc:
  1018. logger.debug("v18 gateway metadata backfill skipped: %s", exc)
  1019. if current_version < 20:
  1020. # v20: seed session_model_usage from sessions aggregates (OR IGNORE: newer rows win).
  1021. with contextlib.suppress(sqlite3.OperationalError):
  1022. cursor.execute(_SESSION_MODEL_USAGE_V20_SEED_SQL)
  1023. if current_version < 22:
  1024. self._migrate_v22_session_model_usage(cursor)
  1025. # v23: FTS storage redesign (external-content tables). OPT-IN, NOT AUTOMATIC: the
  1026. # transition is disk-heavy (~2x transient) and long (hours on 25 GB), so an existing
  1027. # install only gets a flag; `hermes sessions optimize-storage` performs it. The FTS
  1028. # layout is tracked by the independent `fts_storage_version` marker, so
  1029. # schema_version still advances for legacy-FTS users.
  1030. if current_version < 23 and fts5_available and self._db_needs_fts_storage_upgrade(cursor):
  1031. self.set_meta("fts_optimize_available", "1", cursor=cursor)
  1032. if current_version < 25:
  1033. # v25: de-duplicate system prompt snapshots (old column stays a read fallback).
  1034. self._dedupe_legacy_system_prompts(cursor)
  1035. self._advance_message_uid_backfill(cursor)
  1036. fts_migrations_complete = True
  1037. if current_version < 30 and fts5_available:
  1038. # v29: cron sessions leave the trigram substring index (they stay in the word index);
  1039. # v30: delegate-child transcripts too (FTS_TRIGRAM_EXCLUDED_SOURCES + _delegate_from).
  1040. # Rebuild once so rows indexed by older view/trigger definitions do not linger.
  1041. fts_migrations_complete = self._migrate_trigram_cron_exclusion(cursor)
  1042. # The FTS layout stamp is written by _stamp_settled_fts_layout after _init_fts, not here.
  1043. # v23: FTS storage redesign (issues #22478, #43690, #55233). The v11 inline-mode FTS tables each
  1044. # store a full private copy of every message (content || tool_name || tool_calls), and the trigram
  1045. # index additionally covers role='tool' rows (~90% of message bytes: base64 payloads, file dumps) at
  1046. # ~2.6x amplification — together ~75% of state.db on heavy installs (observed: 18.9 GB of a 25 GB
  1047. # DB). OPT-IN, NOT AUTOMATIC. The transition (demote old vtables → new external-content schema →
  1048. # backfill → teardown → VACUUM) is disk-heavy (transient ~2x file size to fully reclaim via VACUUM)
  1049. # and long (~1-2h background on a 25 GB DB). Doing it silently on every big user's next open — with
  1050. # a completeness guarantee that depends on the process staying alive long enough — is the wrong
  1051. # default. So on an EXISTING install we touch nothing here: the v22 inline FTS keeps working exactly
  1052. # as before, and we only record a flag advertising that the optimization is available. `hermes
  1053. # sessions optimize-storage` performs the whole transition as one deliberate, disk-checked,
  1054. # progress-reported foreground operation. DECOUPLED VERSIONING. Crucially, this does NOT hold back
  1055. # the main schema_version. The FTS storage LAYOUT is tracked by an independent `fts_storage_version`
  1056. # marker (see _fts_storage_version / SETTLE below), so schema_version advances to SCHEMA_VERSION
  1057. # here like every other migration — future v24+ migrations land automatically for legacy-FTS users
  1058. # too. Only the FTS *layout* waits for opt-in.
  1059. # v32: nothing to run here. The declared tables/columns #106742 added under v31 were already
  1060. # created by _init_schema's executescript(SCHEMA_SQL) + _reconcile_columns() before this chain;
  1061. # the version only records that the store has them (#140315).
  1062. # Advance schema_version — deliberately NOT gated on the FTS opt-in (that would block
  1063. # every future migration for a user who never optimizes). FTS5 unavailable is the
  1064. # one skip: claiming current would lie.
  1065. if current_version < SCHEMA_VERSION and fts_migrations_complete and fts5_available:
  1066. cursor.execute("UPDATE schema_version SET version = ?", (SCHEMA_VERSION,))
  1067. def _advance_message_uid_backfill(self, cursor: sqlite3.Cursor) -> None:
  1068. """Mint ``message_uid`` onto rows written before the column existed, one bounded slice per open.
  1069. A single full-table UPDATE held the write lock for 261 s on an 835k-row / 4.8 GB store, so every
  1070. sibling process timed out with "database is locked". Rowid-range chunks commit one at a time (the
  1071. writer connection is autocommit) and an open spends at most ``_MESSAGE_UID_BACKFILL_BUDGET_S``
  1072. after its first chunk, so a large store converges over later opens. Until then a legacy row
  1073. simply has no uid, which every reader already treats as "no identity yet".
  1074. The walk is keyed on ``rowid``, not ``id``: on the declared table ``id INTEGER PRIMARY KEY`` is the
  1075. rowid alias (same plan), but a store converted from JSONL still carries ``id TEXT`` with no PRIMARY
  1076. KEY (``_reconcile_columns`` adds columns, never rebuilds the key), where ``MAX(id)`` is a str and an
  1077. ``id`` range compares lexicographically (#141467)."""
  1078. if _meta_row(cursor, _MESSAGE_UID_BACKFILL_DONE) is not None:
  1079. return
  1080. row = _meta_row(cursor, _MESSAGE_UID_BACKFILL_CURSOR)
  1081. done_through = int(row[0]) if row else 0
  1082. high = cursor.execute("SELECT MAX(rowid) FROM messages").fetchone()[0] or 0
  1083. deadline = time.monotonic() + _MESSAGE_UID_BACKFILL_BUDGET_S
  1084. while done_through < high:
  1085. upper = done_through + _MESSAGE_UID_BACKFILL_CHUNK
  1086. cursor.execute(
  1087. "UPDATE messages SET message_uid = lower(hex(randomblob(16))) "
  1088. "WHERE rowid > ? AND rowid <= ? AND message_uid IS NULL", (done_through, upper))
  1089. done_through = upper
  1090. if done_through < high and time.monotonic() >= deadline:
  1091. self.set_meta(_MESSAGE_UID_BACKFILL_CURSOR, str(done_through), cursor=cursor)
  1092. return
  1093. self.set_meta(_MESSAGE_UID_BACKFILL_DONE, "1", cursor=cursor)
  1094. _delete_meta(cursor, _MESSAGE_UID_BACKFILL_CURSOR)
  1095. def _migrate_v22_session_model_usage(self, cursor: sqlite3.Cursor) -> None:
  1096. """v22: ``task`` joins the session_model_usage PRIMARY KEY ('' = main loop; aux calls
  1097. named). SQLite cannot ALTER a PK, so rebuild; existing rows → task=''."""
  1098. try:
  1099. # v22: task-dimension usage attribution (issue #23270). session_model_usage gains a ``task``
  1100. # column ('' = main agent loop; 'vision'/'compression'/'title_generation'/... = auxiliary calls)
  1101. # so aux model spend is visible in analytics. The reconciler will have already ADDed the plain
  1102. # column on legacy DBs (harmless); the rebuild bakes it into the PK properly.
  1103. legacy_pk = cursor.execute(
  1104. "SELECT COUNT(*) FROM pragma_table_info('session_model_usage') WHERE name = 'task' AND pk > 0"
  1105. ).fetchone()[0]
  1106. if legacy_pk:
  1107. return
  1108. self._rebuild_table(
  1109. cursor, "session_model_usage", "session_model_usage_v21", _SESSION_MODEL_USAGE_V22_DDL,
  1110. """INSERT INTO session_model_usage (
  1111. session_id, model, billing_provider, billing_base_url,
  1112. billing_mode, task, api_call_count, input_tokens,
  1113. output_tokens, cache_read_tokens, cache_write_tokens,
  1114. reasoning_tokens, estimated_cost_usd, actual_cost_usd,
  1115. cost_status, cost_source, first_seen, last_seen
  1116. )
  1117. SELECT session_id, model, billing_provider, billing_base_url,
  1118. billing_mode, '', api_call_count, input_tokens,
  1119. output_tokens, cache_read_tokens, cache_write_tokens,
  1120. reasoning_tokens, estimated_cost_usd, actual_cost_usd,
  1121. cost_status, cost_source, first_seen, last_seen
  1122. FROM session_model_usage_v21""",
  1123. _SESSION_MODEL_USAGE_INDEX_SQL,
  1124. )
  1125. except sqlite3.OperationalError as exc:
  1126. logger.debug("v22 session_model_usage rebuild skipped: %s", exc)
  1127. def _ensure_unique_title_index(self, cursor: sqlite3.Cursor) -> None:
  1128. """Unique title index. Older DBs may hold duplicate titles from before the constraint.
  1129. Per title a user-owned (user/NULL) row beats llm > derived (``_title_rank``). User rows are
  1130. never dropped: the oldest keeps the title and newer ones become ``Title #k`` in started_at
  1131. order, so the "#N"-preferring lookup still opens the newest (#126764). Among auto titles
  1132. only, the newest highest-ranked keeps it; lower-ranked auto titles are cleared. Each change
  1133. is logged. Must never abort opening the DB, so the repair is guarded."""
  1134. try:
  1135. cursor.execute(_TITLE_UNIQUE_INDEX_SQL)
  1136. except sqlite3.IntegrityError:
  1137. # Savepoint: a failure part-way must not leave a half-repaired store for
  1138. # _init_schema to commit without the index.
  1139. cursor.execute("SAVEPOINT title_repair")
  1140. try:
  1141. rows = cursor.execute(
  1142. "SELECT rowid, title, title_source, started_at FROM sessions WHERE title IN "
  1143. "(SELECT title FROM sessions WHERE title IS NOT NULL GROUP BY title HAVING COUNT(*) > 1)"
  1144. ).fetchall()
  1145. groups: dict[str, list] = {}
  1146. for row in sorted(rows, key=lambda r: (self._title_rank(r[2]), r[3], r[0]), reverse=True):
  1147. groups.setdefault(row[1], []).append(row)
  1148. user_rank = self._TITLE_SOURCE_RANK[self.TITLE_SOURCE_USER]
  1149. for title, group in groups.items():
  1150. # User titles: the OLDEST keeps the base and newer ones get "#k" in
  1151. # chronological order, so resolve_session_by_title (which prefers the
  1152. # latest "#N") still lands on the newest session.
  1153. users = [r for r in reversed(group) if self._title_rank(r[2]) == user_rank]
  1154. keep = users[0] if users else group[0]
  1155. for rowid, *_ in users[1:]:
  1156. renamed = next_title_in_lineage(cursor.connection, title)
  1157. cursor.execute("UPDATE sessions SET title = ? WHERE rowid = ?", (renamed, rowid))
  1158. logger.warning("Renamed duplicate user session title %r to %r", title, renamed)
  1159. for rowid, _title, source, _started in group:
  1160. if rowid != keep[0] and self._title_rank(source) != user_rank:
  1161. cursor.execute("UPDATE sessions SET title = NULL WHERE rowid = ?", (rowid,))
  1162. logger.warning("Cleared duplicate %s session title %r", source, title)
  1163. cursor.execute(_TITLE_UNIQUE_INDEX_SQL)
  1164. cursor.execute("RELEASE title_repair")
  1165. except sqlite3.Error:
  1166. cursor.execute("ROLLBACK TO title_repair")
  1167. cursor.execute("RELEASE title_repair")
  1168. logger.exception("Could not repair duplicate session titles; unique title index not created")
  1169. except sqlite3.OperationalError:
  1170. pass # Index already exists
  1171. def _init_fts(self, cursor: sqlite3.Cursor) -> None:
  1172. """Create/repair the FTS objects on an FTS5-capable runtime. The DDL runs even when the
  1173. vtable exists so CREATE TRIGGER IF NOT EXISTS repairs trigger-only degradation.
  1174. OPT-IN v23 boundary: a legacy v22 inline install keeps its inline schema + triggers
  1175. (the v23 DDL would create the trigram source VIEW and leave a mixed state)."""
  1176. legacy_fts = self._db_has_legacy_inline_fts(cursor)
  1177. # A `.recover`-restored image keeps the shadow tables but not the vtable rows; the DDL
  1178. # below would fail on the first shadow. Drop only orphaned families, then rebuild the
  1179. # recreated (empty) index like a missing-trigger repair (#103840).
  1180. orphan_repaired = _drop_orphan_fts_shadow_tables(
  1181. cursor, ("messages_fts", "messages_fts_trigram", "messages_fts_cjk"),
  1182. )
  1183. if not self._fts_stale:
  1184. self._migrate_misaligned_fts_source(cursor, legacy=legacy_fts)
  1185. if self._fts_stale:
  1186. if self._recover_stale_fts(cursor, legacy=legacy_fts):
  1187. # CJK was detached alongside the base indexes; its ensure path decides when it returns.
  1188. self._ensure_fts_cjk_schema(cursor)
  1189. else:
  1190. self._fts_enabled = self._trigram_available = self._fts_cjk_available = False
  1191. else:
  1192. base_sql, trigram_sql = _FTS_DDL[legacy_fts]
  1193. # Measure before any DDL. Publishing missing base triggers before rebuild admission lets
  1194. # another process write through an index whose bootstrap/repair has no owner (#105790).
  1195. base_triggers_missing = (
  1196. self._fts_triggers_missing(cursor, _FTS_BASE_TRIGGERS) or "messages_fts" in orphan_repaired
  1197. )
  1198. trigram_triggers_missing = (
  1199. self._fts_triggers_missing(cursor, _FTS_TRIGRAM_TRIGGERS) or "messages_fts_trigram" in orphan_repaired
  1200. )
  1201. def ensure_and_rebuild() -> None:
  1202. self._fts_enabled = self._ensure_fts_schema(cursor, "messages_fts", base_sql)
  1203. if not self._fts_enabled:
  1204. return
  1205. self._trigram_available = self._ensure_fts_schema(cursor, "messages_fts_trigram", trigram_sql)
  1206. self._rebuild_fts_indexes(
  1207. cursor, legacy=legacy_fts, include_trigram=self._trigram_available,
  1208. )
  1209. if not legacy_fts:
  1210. self._ensure_fts_cjk_schema(cursor)
  1211. if base_triggers_missing:
  1212. # The authority covers the whole first-publication sequence, not merely the final rebuild.
  1213. # ``executescript`` commits DDL statement-by-statement, so acquiring after ensure exposed a
  1214. # partially initialized FTS family while another opener held the rebuild lock.
  1215. self._run_admitted_startup_rebuild(cursor, ensure_and_rebuild)
  1216. else:
  1217. self._fts_enabled = self._ensure_fts_schema(cursor, "messages_fts", base_sql)
  1218. if self._fts_enabled:
  1219. # Trigram is optional; without it CJK search falls back to LIKE.
  1220. trigram_enabled = self._ensure_fts_schema(cursor, "messages_fts_trigram", trigram_sql)
  1221. self._trigram_available = trigram_enabled
  1222. if trigram_enabled and trigram_triggers_missing:
  1223. self._run_admitted_startup_rebuild(
  1224. cursor,
  1225. lambda: self._rebuild_fts_indexes(
  1226. cursor, legacy=legacy_fts, include_trigram=trigram_enabled,
  1227. ),
  1228. )
  1229. if self._fts_enabled and not legacy_fts and not base_triggers_missing:
  1230. # CJK-bigram index: strictly additive, gated on the loadable tokenizer.
  1231. self._ensure_fts_cjk_schema(cursor)
  1232. # IF NOT EXISTS cannot rewrite pre-existing broad AFTER UPDATE triggers.
  1233. if self._fts_enabled:
  1234. self._migrate_broad_fts_update_triggers(cursor)
  1235. def _run_admitted_startup_rebuild(self, cursor, rebuild_fn) -> None:
  1236. """Run FTS bootstrap or trigger-repair rebuild under cross-process admission.
  1237. The fresh/base-missing path includes DDL in ``rebuild_fn`` so no process can publish
  1238. triggers before it owns the rebuild. Other repair paths may already have recreated an
  1239. optional trigger; deferral therefore still drops every trigger and persists the stale
  1240. breadcrumb. A later recovery path restores the complete family.
  1241. See #93200.
  1242. See #105790.
  1243. """
  1244. with fts_rebuild_admission(self.db_path) as admitted:
  1245. if admitted:
  1246. rebuild_fn()
  1247. return
  1248. logger.warning(
  1249. "Deferred startup FTS rebuild: another process holds the "
  1250. "rebuild authority for this state.db; detaching FTS sync until the stale-index recovery path rebuilds it."
  1251. )
  1252. cursor.execute(_STALE_KEY_UPSERT_SQL, (FTS_STALE_KEY,))
  1253. self._drop_all_fts_triggers(cursor)
  1254. self._fts_stale = True
  1255. self._fts_enabled = self._trigram_available = self._fts_cjk_available = False
  1256. def _backfill_gateway_metadata_from_sessions_json(self, cursor: sqlite3.Cursor) -> None:
  1257. """One-time v18 backfill of gateway metadata from sessions.json. Only fills NULL
  1258. columns — never overwrites data written by newer code."""
  1259. sessions_file = get_hermes_home() / "sessions" / "sessions.json"
  1260. if not sessions_file.exists():
  1261. return
  1262. with open(sessions_file, "r", encoding="utf-8") as f:
  1263. data = json.load(f)
  1264. if not isinstance(data, dict):
  1265. return
  1266. for key, entry in data.items():
  1267. if str(key).startswith("_") or not isinstance(entry, dict):
  1268. continue
  1269. session_id = entry.get("session_id")
  1270. if not session_id:
  1271. continue
  1272. origin = entry.get("origin")
  1273. origin_dict = origin if isinstance(origin, dict) else None
  1274. cursor.execute(
  1275. """UPDATE sessions
  1276. SET session_key = COALESCE(session_key, ?),
  1277. chat_id = COALESCE(chat_id, ?),
  1278. chat_type = COALESCE(chat_type, ?),
  1279. thread_id = COALESCE(thread_id, ?),
  1280. display_name = COALESCE(display_name, ?),
  1281. origin_json = COALESCE(origin_json, ?),
  1282. expiry_finalized = CASE
  1283. WHEN COALESCE(expiry_finalized, 0) = 0 AND ? = 1 THEN 1
  1284. ELSE expiry_finalized
  1285. END
  1286. WHERE id = ?""",
  1287. (
  1288. entry.get("session_key") or key, origin_dict.get("chat_id") if origin_dict is not None else None,
  1289. entry.get("chat_type"), origin_dict.get("thread_id") if origin_dict is not None else None,
  1290. entry.get("display_name"), json.dumps(origin) if origin_dict is not None else None,
  1291. 1 if entry.get("expiry_finalized") or entry.get("memory_flushed") else 0, str(session_id),
  1292. ),
  1293. )
  1294. def apply_declared_schema(cursor: sqlite3.Cursor) -> None:
  1295. """The one additive catch-up order for raw ``state.db`` connections: columns (SCHEMA_SQL indexes may
  1296. name columns an old table lacks), then the canonical DDL statement by statement (no
  1297. ``executescript`` implicit COMMIT, so callers keep their own transaction), then columns again
  1298. (a no-op for tables the script just created whole). Locking, snapshots and commit stay with the
  1299. callers. ``_reconcile_columns`` only touches the staticmethod ``_parse_schema_columns``, so a bare
  1300. instance is enough and the column reconciler keeps one implementation."""
  1301. shim = object.__new__(SessionSchemaMixin)
  1302. shim._reconcile_columns(cursor)
  1303. SessionSchemaMixin._execute_ddl_script_transactional(cursor, SCHEMA_SQL)
  1304. shim._reconcile_columns(cursor)
  1305. def reconcile_state_schema(conn: sqlite3.Connection) -> None:
  1306. """Bring a raw ``state.db`` connection to the canonical SCHEMA_SQL shape and commit.
  1307. Single durable-shape authority for callers that open ``state.db``
  1308. outside SessionDB. The async-delegation tool used to carry its own
  1309. CREATE TABLE and ALTER column list for ``async_delegations``; it drifted
  1310. from SCHEMA_SQL (same-name columns with different nullability/defaults
  1311. depending on which authority touched the database first, #94691). This
  1312. helper instead replays the canonical DDL (every statement is
  1313. IF NOT EXISTS/idempotent) through :func:`apply_declared_schema`.
  1314. """
  1315. apply_declared_schema(conn.cursor())
  1316. if conn.in_transaction:
  1317. conn.commit()