hermes_state_common.py 73 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496
  1. """Shared constants and helpers for the SessionDB family of modules. Lives outside hermes_state so
  2. the mixin modules can import it without a cycle."""
  3. import contextlib
  4. import errno
  5. import json
  6. import logging
  7. import os
  8. import sys
  9. import time
  10. from typing import Any
  11. from hermes_cli.timefmt import EPOCH_MAX, EPOCH_MIN
  12. from hermes_state_keys import CRON_PRINCIPAL_ID
  13. from hermes_state_pidns import persistent_record_pidns_checkable, pid_namespace_id
  14. from agent.skill_commands import AUTO_LOAD_SCAFFOLD_SQL_LIKE, SKILL_EXCERPT_JOINT, SKILL_SCAFFOLD_SQL_LIKE, describe_skill_invocation
  15. from agent.context_compressor import (LEGACY_SUMMARY_PREFIX, SUMMARY_PREFIX, _MERGED_PRIOR_CONTEXT_HEADER,
  16. _MERGED_SUMMARY_DELIMITER, _SUMMARY_END_MARKER)
  17. # Persisted title provenance: automatic display labels are not user-selected identities.
  18. TITLE_SOURCE_DERIVED = "derived"
  19. TITLE_SOURCE_LLM = "llm"
  20. TITLE_SOURCE_USER = "user"
  21. # Session preview = head of the first user message (shown when a session has no title). A /skill invocation
  22. # embeds the whole skill body, so scaffolded rows take a wider excerpt (whole message under budget, else head +
  23. # tail where the typed instruction lands) and ``_shape_preview`` recovers ``/work — fix ...`` from it.
  24. _PREVIEW_HEAD_CHARS = 63
  25. _PREVIEW_SCAFFOLD_WINDOW = 400
  26. _PREVIEW_MAX_CHARS = 60
  27. def routed_sessions_setting(key: str, env_var: str) -> Any:
  28. """``sessions.<key>`` for the profile whose state.db this process is touching.
  29. ``gateway/run.py`` bridges the LAUNCH profile's ``sessions.*`` into ``env_var`` (the cross-process
  30. carrier CLI/cron children read). Under a multiplexer a routed turn runs with a HERMES_HOME override
  31. and that env slot holds the default profile's value, so a served profile with different
  32. ``sessions.*`` settings must read its own config.yaml. Unscoped: the env bridge, as before.
  33. Returns ``None`` when neither source sets the key.
  34. """
  35. from hermes_constants import get_hermes_home_override
  36. if get_hermes_home_override():
  37. try:
  38. from hermes_cli.config import load_config_readonly
  39. return (load_config_readonly().get("sessions") or {}).get(key)
  40. except Exception:
  41. return None
  42. return os.environ.get(env_var)
  43. def escape_like(text: str) -> str:
  44. """Escape LIKE wildcards (``%``, ``_``) so derived text matches literally; pair with ``ESCAPE '\\'``.
  45. ``_`` is common in branch names/titles/paths and a substring match must not silently widen."""
  46. return text.replace("\\", "\\\\").replace("%", "\\%").replace("_", "\\_")
  47. _PREVIEW_CONTENT_SQL = "REPLACE(REPLACE(m.content, X'0A', ' '), X'0D', ' ')"
  48. # User-invoked and gateway auto-load scaffolds both get the head+tail excerpt window:
  49. # the typed request sits at the tail (auto-load) or behind the instruction marker.
  50. # No LIKE wildcards in either prefix, so no ESCAPE clause is needed.
  51. _PREVIEW_SCAFFOLDED_SQL = (
  52. f"(m.content LIKE '{SKILL_SCAFFOLD_SQL_LIKE}'"
  53. f" OR m.content LIKE '{AUTO_LOAD_SCAFFOLD_SQL_LIKE}')")
  54. _SQL_WHITESPACE = "CHAR(9) || CHAR(10) || CHAR(13) || CHAR(32)"
  55. def _sql_literal(text: str) -> str:
  56. return "'" + text.replace("'", "''") + "'"
  57. # ``display_metadata`` flag stamped on the user row a busy-queue accept writes (#111868/#121374
  58. # accept-time durability). The in-memory queue drain re-places an unmarked row at the transcript
  59. # end and deactivates this one; a backend restart discards the queue with nothing left to retire
  60. # the accept-time row, so ``reopen_session`` deactivates still-marked rows (#125577) — the marker
  61. # is exactly what distinguishes a never-drained accept row from a dispatched-then-interrupted
  62. # turn's row (which #123532 deliberately keeps active).
  63. QUEUED_PROMPT_METADATA_KEY = "_queued_prompt"
  64. def _sql_json_extract(expression: str, path: str) -> str:
  65. """Build a non-throwing JSON marker lookup for a JSON TEXT column."""
  66. safe_json = (
  67. f"(CASE WHEN json_valid({expression}) "
  68. f"THEN {expression} ELSE json_object() END)"
  69. )
  70. return f"json_extract({safe_json}, {_sql_literal(path)})"
  71. def _sql_ltrim_whitespace(expression: str) -> str:
  72. return f"LTRIM({expression}, {_SQL_WHITESPACE})"
  73. def _sql_trim_whitespace(expression: str) -> str:
  74. return f"TRIM({expression}, {_SQL_WHITESPACE})"
  75. def _sql_starts_with(expression: str, prefixes: tuple[str, ...]) -> str:
  76. trimmed = _sql_ltrim_whitespace(expression)
  77. return "(" + " OR ".join(f"SUBSTR({trimmed}, 1, {len(p)}) = {_sql_literal(p)}" for p in prefixes) + ")"
  78. def _sql_after_marker(marker: str) -> str:
  79. """``m.content`` after the first occurrence of *marker*."""
  80. return f"SUBSTR(m.content, INSTR(m.content, {_sql_literal(marker)}) + {len(marker)})"
  81. # Match the whole introduction shared by current and legacy prefixes, so a message that merely starts with the
  82. # bracketed label is not a compaction carrier.
  83. _PREVIEW_LONG_FORM_PREFIX = SUMMARY_PREFIX.split("Do NOT answer", 1)[0]
  84. _PREVIEW_SUMMARY_PREFIXES = (_PREVIEW_LONG_FORM_PREFIX, LEGACY_SUMMARY_PREFIX)
  85. _PREVIEW_STANDALONE_SUMMARY_SQL = _sql_starts_with("m.content", _PREVIEW_SUMMARY_PREFIXES)
  86. _PREVIEW_MERGED_AFTER_SQL = _sql_after_marker(_MERGED_SUMMARY_DELIMITER)
  87. _PREVIEW_MERGED_SUMMARY_SQL = (f"(INSTR(m.content, {_sql_literal(_MERGED_SUMMARY_DELIMITER)}) > 0"
  88. f" AND {_sql_starts_with(_PREVIEW_MERGED_AFTER_SQL, _PREVIEW_SUMMARY_PREFIXES)})")
  89. _PREVIEW_MERGED_PRIOR_SQL = _sql_trim_whitespace(
  90. f"SUBSTR(m.content, 1, INSTR(m.content, {_sql_literal(_MERGED_SUMMARY_DELIMITER)}) - 1)")
  91. _PREVIEW_MERGED_PRIOR_LTRIMMED_SQL = _sql_ltrim_whitespace(_PREVIEW_MERGED_PRIOR_SQL)
  92. _PREVIEW_MERGED_PRIOR_UNWRAPPED_SQL = (f"CASE WHEN SUBSTR({_PREVIEW_MERGED_PRIOR_LTRIMMED_SQL}, 1,"
  93. f" {len(_MERGED_PRIOR_CONTEXT_HEADER)}) = {_sql_literal(_MERGED_PRIOR_CONTEXT_HEADER)}"
  94. f" THEN {_sql_ltrim_whitespace(f'SUBSTR({_PREVIEW_MERGED_PRIOR_LTRIMMED_SQL}, {len(_MERGED_PRIOR_CONTEXT_HEADER) + 1})')}"
  95. f" ELSE {_PREVIEW_MERGED_PRIOR_SQL} END")
  96. _PREVIEW_FORCE_USER_REMAINDER_SQL = _sql_after_marker(_SUMMARY_END_MARKER)
  97. # Pure compaction rows are ineligible; force-user-leading and merged carriers only when authentic content survives.
  98. # A display_kind="hidden" row is model-facing scaffolding the gateway never paints; the preview must not paint it either.
  99. _PREVIEW_ELIGIBLE_SQL = (f"(COALESCE(m.display_kind, '') <> 'hidden'"
  100. f" AND ((NOT {_PREVIEW_STANDALONE_SUMMARY_SQL} AND NOT {_PREVIEW_MERGED_SUMMARY_SQL})"
  101. f" OR ({_PREVIEW_STANDALONE_SUMMARY_SQL} AND INSTR(m.content, {_sql_literal(_SUMMARY_END_MARKER)}) > 0"
  102. f" AND LENGTH({_sql_trim_whitespace(_PREVIEW_FORCE_USER_REMAINDER_SQL)}) > 0)"
  103. f" OR ({_PREVIEW_MERGED_SUMMARY_SQL}"
  104. f" AND LENGTH({_sql_trim_whitespace(_PREVIEW_MERGED_PRIOR_UNWRAPPED_SQL)}) > 0)))")
  105. # ``_preview_raw`` SELECT for every listing query (scaffolded rows: head + tail around SKILL_EXCERPT_JOINT).
  106. _PREVIEW_RAW_SELECT = (
  107. f"CASE WHEN {_PREVIEW_STANDALONE_SUMMARY_SQL} THEN {_PREVIEW_FORCE_USER_REMAINDER_SQL}"
  108. f" WHEN {_PREVIEW_MERGED_SUMMARY_SQL} THEN {_PREVIEW_MERGED_PRIOR_UNWRAPPED_SQL}"
  109. f" WHEN {_PREVIEW_SCAFFOLDED_SQL} AND LENGTH(m.content) > {_PREVIEW_SCAFFOLD_WINDOW * 2}"
  110. f" THEN SUBSTR({_PREVIEW_CONTENT_SQL}, 1, {_PREVIEW_SCAFFOLD_WINDOW}) || '{SKILL_EXCERPT_JOINT}'"
  111. f" || SUBSTR({_PREVIEW_CONTENT_SQL}, -{_PREVIEW_SCAFFOLD_WINDOW})"
  112. f" WHEN {_PREVIEW_SCAFFOLDED_SQL} THEN SUBSTR({_PREVIEW_CONTENT_SQL}, 1, {_PREVIEW_SCAFFOLD_WINDOW * 2})"
  113. f" ELSE SUBSTR({_PREVIEW_CONTENT_SQL}, 1, {_PREVIEW_HEAD_CHARS}) END")
  114. def _shape_preview(raw: Any) -> str:
  115. """Turn a ``_preview_raw`` column into the short preview callers show."""
  116. text = str(raw or "").strip()
  117. if not text:
  118. return ""
  119. text = text.replace("\n", " ").replace("\r", " ")
  120. described = describe_skill_invocation(text)
  121. text = described if described is not None else text.split(SKILL_EXCERPT_JOINT)[0]
  122. return text[:_PREVIEW_MAX_CHARS] + "..." if len(text) > _PREVIEW_MAX_CHARS else text
  123. def _sql_preview_raw(session_id_expr: str = "s.id") -> str:
  124. """Correlated ``_preview_raw`` column: the first eligible user message of *session_id_expr*.
  125. ``INDEXED BY`` walks the session in timestamp order and stops at the first eligible row; with planner
  126. stats older than idx_messages_session_id, SQLite otherwise reads and sorts the whole session (#119403)."""
  127. return (f"COALESCE((SELECT {_PREVIEW_RAW_SELECT} FROM messages m INDEXED BY idx_messages_session"
  128. f" WHERE m.session_id = {session_id_expr} AND m.role = 'user' AND m.content IS NOT NULL"
  129. f" AND {_PREVIEW_ELIGIBLE_SQL} ORDER BY m.timestamp, m.id LIMIT 1), '') AS _preview_raw")
  130. # ── Session lineage predicates ({a} = sessions alias) ───────────────────────
  131. # /branch child (kept visible, never cascade-deleted): stable marker OR legacy end_reason heuristic.
  132. _BRANCH_CHILD_SQL = (f"{_sql_json_extract('{a}.model_config', '$._branched_from')} IS NOT NULL"
  133. " OR EXISTS (SELECT 1 FROM sessions p WHERE p.id = {a}.parent_session_id"
  134. " AND p.end_reason = 'branched' AND {a}.started_at >= p.ended_at)")
  135. _COMPRESSION_CHILD_SQL = ("EXISTS (SELECT 1 FROM sessions p WHERE p.id = {a}.parent_session_id"
  136. " AND p.end_reason = 'compression')")
  137. # 'session_switch' creates no child row today, but pre-marker DBs hold legacy reset children whose parent
  138. # ended that way. Must stay identical to the recovery fence in find_latest_gateway_session_for_peer.
  139. _RESET_END_REASONS = ("session_reset", "session_switch", "idle", "daily", "suspended", "resume_pending_expired")
  140. _RESET_END_REASONS_SQL = ", ".join(f"'{reason}'" for reason in _RESET_END_REASONS)
  141. # Deliberate conversation boundaries: the reset set plus CLI /new, which ends the predecessor as
  142. # 'new_session' (hermes_cli/cli_session_mixin.py) without a reset child row. A compression rotation must
  143. # never heal one of these (#106459); tools/session_search_tool.py derives its fresh-reset set from it.
  144. _BOUNDARY_END_REASONS = frozenset(_RESET_END_REASONS) | {"new_session"}
  145. # Accidental end reasons recovery treats as resumable (website/docs/developer-guide/gateway-session-lifecycle.md); single source of truth for
  146. # recovery SQL and SessionDB.RECOVERABLE_END_REASONS. superseded_by_resume = sentinel-parked runtime replaced
  147. # by a fresh session.resume; startup_orphan_reap = dead-gateway sweep, same class as ws_orphan_reap but kept
  148. # distinct for forensics.
  149. _RECOVERABLE_END_REASONS = ("agent_close", "ws_orphan_reap", "superseded_by_resume", "startup_orphan_reap")
  150. # Startup sweep of rows orphaned by a dead gateway process (#65194): the in-process ws-orphan grace timer
  151. # died with the process, so the row was closed at the next boot instead.
  152. _RECOVERABLE_END_REASONS_SQL = ", ".join(f"'{reason}'" for reason in _RECOVERABLE_END_REASONS)
  153. # End reasons written by AUTOMATIC cleanup (shutdown, orphan reapers, idle/LRU eviction), not a deliberate
  154. # conversation boundary: "some runtime went away", so a writer that can prove liveness (e.g. a compression
  155. # rotation holding the lease) may clear it. Recoverable set plus the TUI gateway's automatic reasons.
  156. # Superset of the recoverable set: those are already resumable accidents; the extra TUI reasons are the same
  157. # accident class but were historically only known to tui_gateway's _AUTOMATIC_SESSION_END_REASONS. See
  158. # #88197.
  159. _AUTOMATIC_END_REASONS = frozenset(_RECOVERABLE_END_REASONS) | {
  160. "tui_shutdown", "ws_disconnect", "idle_timeout", "lru_evict"}
  161. def is_automatic_end_reason(reason) -> bool:
  162. """True when *reason* is an automatic-cleanup end stamp; compression-liveness sites must call this.
  163. Single owner of the "accidental vs deliberate end" predicate — every compression-liveness site must call
  164. this instead of re-implementing the reason taxonomy (#88197, never-patch-predicates).
  165. """
  166. return isinstance(reason, str) and reason in _AUTOMATIC_END_REASONS
  167. def _legacy_reset_child_sql(alias: str, reasons_sql: str) -> str:
  168. """Pre-marker reset-continuation heuristic: child rides its parent's exact non-empty routing key and the
  169. parent ended at a reset boundary. Shared by ``_RESET_CHILD_SQL`` and ``reopen_session()`` so the two
  170. cannot drift; ``reasons_sql`` is a literal or placeholder list."""
  171. return (f"EXISTS (SELECT 1 FROM sessions p WHERE p.id = {alias}.parent_session_id"
  172. f" AND p.end_reason IN ({reasons_sql}) AND {alias}.session_key IS NOT NULL"
  173. f" AND {alias}.session_key != '' AND {alias}.session_key = p.session_key)")
  174. def _non_continuation_child_sql(child: str = "", parent: str = "?") -> str:
  175. """`` AND ...`` clauses rejecting children that are NOT compression continuations of *parent*
  176. (branch/delegate/reset forks, tool sessions). Markers are bound to the parent id: continuations
  177. inherit ``model_config`` verbatim, so a marker naming another row is inherited, not a fork.
  178. ``child`` is the column prefix (``""``, ``"c."``); single owner so prune and compression agree."""
  179. return "".join(
  180. f" AND COALESCE({_sql_json_extract(f'{child}model_config', f'$.{marker}')}, '') != {parent}\n"
  181. for marker in ("_branched_from", "_delegate_from", "_reset_from")
  182. ) + f" AND COALESCE({child}source, '') != 'tool'\n"
  183. # A reset starts a separate user-visible conversation though rows keep parent_session_id for lineage.
  184. # Stable marker, or the same-key fallback for pre-marker rows (exact key keeps subagent children out).
  185. _RESET_CHILD_SQL = (f"{_sql_json_extract('{a}.model_config', '$._reset_from')} IS NOT NULL"
  186. " OR " + _legacy_reset_child_sql("{a}", _RESET_END_REASONS_SQL))
  187. # Picker-visible rows: roots + branch/reset children (not subagent runs or compression continuations).
  188. _LISTABLE_CHILD_SQL = (f"(s.parent_session_id IS NULL OR {_BRANCH_CHILD_SQL.format(a='s')}"
  189. f" OR {_RESET_CHILD_SQL.format(a='s')})")
  190. def _ephemeral_child_sql(alias: str = "s") -> str:
  191. """Subagent runs, not branch, reset, or compression children."""
  192. return (f"({alias}.parent_session_id IS NOT NULL AND NOT ({_BRANCH_CHILD_SQL.format(a=alias)})"
  193. f" AND NOT ({_COMPRESSION_CHILD_SQL.format(a=alias)}) AND NOT ({_RESET_CHILD_SQL.format(a=alias)}))")
  194. _SQL_IN_WINDOW = f"BETWEEN {EPOCH_MIN!r} AND {EPOCH_MAX!r}"
  195. def _sql_in_window(expr: str) -> str:
  196. """*expr* when it is inside the ``coerce_epoch`` window, else NULL."""
  197. return f"(SELECT _win.v FROM (SELECT {expr} AS v) _win WHERE _win.v {_SQL_IN_WINDOW})"
  198. def _sql_freshest_of(activity: str, session_id_expr: str, started: str) -> str:
  199. """Freshest of *activity* and the latest message timestamp for *session_id_expr*, else *started*.
  200. Heartbeats are rate-limited (~60s) so ``last_activity_at`` can lag a newer message; never use it alone.
  201. Cells outside the ``coerce_epoch`` window (garbage doubles salvaged from a damaged page, TEXT) are
  202. skipped, fallback included, or one bad row pins the session's recency to ``5e+246`` (#91536); a
  203. session with no trusted cell at all is NULL."""
  204. # Pinned for the same reason as _sql_preview_raw (#119403).
  205. msg_max = (f"(SELECT MAX(_act_m.timestamp) FROM messages _act_m INDEXED BY idx_messages_session"
  206. f" WHERE _act_m.session_id = {session_id_expr}"
  207. f" AND _act_m.timestamp {_SQL_IN_WINDOW})")
  208. return (f"COALESCE((SELECT MAX(_act_v.v) FROM (SELECT {activity} AS v UNION ALL SELECT {msg_max}) _act_v"
  209. f" WHERE _act_v.v {_SQL_IN_WINDOW}), {_sql_in_window(started)})")
  210. def _sql_session_last_active(alias: str = "s") -> str:
  211. """Session recency expression for a ``sessions {alias}`` row."""
  212. return _sql_freshest_of(f"{alias}.last_activity_at", f"{alias}.id", f"{alias}.started_at")
  213. def _sql_session_last_active_by_id(session_id_expr: str) -> str:
  214. """Same freshest-of expression keyed by a session-id SQL expression."""
  215. return _sql_freshest_of(
  216. f"(SELECT last_activity_at FROM sessions _act_s WHERE _act_s.id = {session_id_expr})", session_id_expr,
  217. f"(SELECT started_at FROM sessions _act_s WHERE _act_s.id = {session_id_expr})")
  218. # v32 (#140315): no data migration. #106742 added runtime_epoch, session_admissions, worker_executions,
  219. # worker_receipts and three columns to SCHEMA_SQL under v31. Drift is detected by the schema read probe
  220. # (schema_read_probe_statements), never by this number, and its catch-up is the declarative
  221. # executescript(SCHEMA_SQL) + _reconcile_columns() every writable open already runs (additive,
  222. # idempotent). The bump only records that the declared shape changed; a new table or column needs no bump.
  223. SCHEMA_VERSION = 32
  224. # Auto-maintenance VACUUMs only above this freelist fraction; below it a rewrite costs more I/O than it returns.
  225. # Auto-maintenance only VACUUMs when at least this fraction of the database file is reclaimable (``PRAGMA
  226. # freelist_count / PRAGMA page_count``). Below it a full rewrite costs more I/O than it returns — pruning a
  227. # handful of small sessions on a dense multi-GB state.db should never rewrite the whole file to reclaim a
  228. # few MB (#54189). Composes with ``min_vacuum_interval_days``.
  229. AUTO_VACUUM_MIN_FREELIST_RATIO = 0.25
  230. # FTS storage-layout version, tracked INDEPENDENTLY of SCHEMA_VERSION in the
  231. # state_meta key ``fts_storage_version``. The main schema version advances
  232. # freely on open (so future migrations always land); the FTS *layout* only
  233. # reaches the current version when a DB is either born fresh or explicitly
  234. # optimized via ``hermes sessions optimize-storage``. A legacy DB sits at
  235. # layout 0 (marker absent) with a working inline index until the user opts in.
  236. # 1 = v23 external-content layout with a tool-row-excluded trigram
  237. # 2 = trigram also excludes structured tool_calls JSON
  238. # 3 = messages_fts source aligned to a stable projection view
  239. # (``messages_fts_src``): always-truncate tool rows to the prefix, no
  240. # moving high-water boundary. The external-content source now reads
  241. # back EXACTLY what the triggers indexed, so the rank=1
  242. # 'integrity-check' probe cannot drift from the stored index (the
  243. # recurring fts5 "checksum mismatch" / leaked-token failures).
  244. FTS_STORAGE_VERSION = 3
  245. # Tool results are often multi-megabyte machine payloads. The base FTS index
  246. # stores only a bounded prefix of every tool row; tool rows are skipped by
  247. # default in search, and explicit tool-only search uses a LIKE fallback over
  248. # the full stored content, so no search capability is lost. The projection
  249. # below is STABLE — it depends only on the row being written, never on
  250. # mutable ``state_meta`` markers — which is what keeps the external-content
  251. # integrity checker and the trigger 'delete'/'update' commands in agreement
  252. # with the stored index forever.
  253. FTS_TOOL_CONTENT_PREFIX_CHARS = 8_192
  254. def _fts_indexed_content_sql(alias: str) -> str:
  255. return f"""CASE WHEN {alias}.role = 'tool'
  256. THEN substr(COALESCE({alias}.content, ''), 1, {FTS_TOOL_CONTENT_PREFIX_CHARS})
  257. ELSE {alias}.content END"""
  258. _FTS_NEW_INDEXED_CONTENT_SQL = _fts_indexed_content_sql("new")
  259. _FTS_OLD_INDEXED_CONTENT_SQL = _fts_indexed_content_sql("old")
  260. # Cap on user-controlled FTS5 query input before sanitizer processing.
  261. MAX_FTS5_QUERY_CHARS = 2_048
  262. def stat_db_file_identity(path) -> tuple[int, int] | None:
  263. """``(st_dev, st_ino)`` for *path*, or None. st_ino=0 (Windows, some network FS) would false-positive
  264. every replaced-file check, so it counts as unknown."""
  265. try:
  266. st = os.stat(path)
  267. except OSError:
  268. return None
  269. return (st.st_dev, st.st_ino) if st.st_dev and st.st_ino else None
  270. # Row probes shared by the messages / compression mixins.
  271. _ENDED_ROW_SQL = "SELECT ended_at, end_reason FROM sessions WHERE id = ?"
  272. _COMPRESSION_LOCK_ROW_SQL = "SELECT holder, expires_at FROM compression_locks WHERE session_id = ?"
  273. def _ended_by_compression(row) -> bool:
  274. return row is not None and row["ended_at"] is not None and row["end_reason"] == "compression"
  275. def _placeholders(items) -> str:
  276. """``?,?,?`` for one bound parameter per element of *items* (a sequence or an int count)."""
  277. return ",".join("?" for _ in range(items if isinstance(items, int) else len(items)))
  278. # A first prompt is durable in ``session_admissions`` from admission on, while its transcript row lands
  279. # only at turn start; a restart in between leaves the admission ``unknown`` and the session at 0
  280. # messages. Only a session with neither messages nor an admission is an empty launch stub.
  281. _NOT_A_LAUNCH_STUB_SQL = (
  282. "(s.message_count > 0 OR EXISTS (SELECT 1 FROM session_admissions a WHERE a.target_session_id = s.id))")
  283. def launch_stub_filter_sql(db) -> str:
  284. """``sessions s`` predicate that drops empty launch stubs (the CLI crashed or a resume failed before
  285. the first turn). A read-only open does not migrate, so a store from before the runtime tables may
  286. lack ``session_admissions``; messages alone decide there."""
  287. if db._read_all("SELECT 1 FROM sqlite_master WHERE type = 'table' AND name = 'session_admissions'"):
  288. return _NOT_A_LAUNCH_STUB_SQL
  289. return "s.message_count > 0"
  290. def _source_match_clause(sources, local_created_sources=None) -> tuple[str, list]:
  291. """``sessions s`` source predicate. With ``local_created_sources``, a ``source='local'`` row
  292. matches only when ``created_source`` is one of those surfaces; the placeholder ``'unknown'``
  293. is not a surface and stays unmatched until an upsert stamps the real creator."""
  294. labels = list(sources or [])
  295. if not labels:
  296. return "", []
  297. surfaces = list(local_created_sources or ())
  298. if not surfaces:
  299. return f"s.source IN ({_placeholders(labels)})", labels
  300. non_local = [item for item in labels if item != "local"]
  301. local_sql = f"(s.source = 'local' AND s.created_source IN ({_placeholders(surfaces)}))"
  302. if not non_local:
  303. return local_sql, list(surfaces)
  304. return (
  305. f"(s.source IN ({_placeholders(non_local)}) OR {local_sql})",
  306. [*non_local, *surfaces],
  307. )
  308. # Ids per ``IN (?,...)`` list: SQLite caps bound parameters at SQLITE_MAX_VARIABLE_NUMBER (999 on builds
  309. # < 3.32, 32766 after); a bulk prune of a cron-heavy store bound tens of thousands of ids into one list and
  310. # died with "too many SQL variables". Every IN-list over session ids goes through ``_id_chunks``.
  311. _SQL_IN_CHUNK = 900
  312. def _id_chunks(ids, size: int = _SQL_IN_CHUNK):
  313. """Yield *ids* (any iterable) as lists of at most *size* elements."""
  314. ids = list(ids)
  315. for start in range(0, len(ids), size):
  316. yield ids[start:start + size]
  317. def emit_session_deleted(db, deleted_ids, *, reason, primary=None, sessions_dir=None):
  318. """Fire ``on_session_delete`` after a committed delete or deliberate archive (#124511).
  319. Call right after the write transaction commits (before follow-up cleanup that can raise), with
  320. the ids it removed or, for ``reason="archive"``, hid. *primary* is the session the caller asked
  321. for; without it the first id stands in (bulk sweeps). ``deleted_ids`` rides along when more
  322. than one row went. Dispatch runs under the store's own profile home, so a process touching
  323. another profile's store (``sessions repair-profiles``) reaches THAT profile's plugins.
  324. Late-bound and fail-open: never breaks the delete.
  325. """
  326. ids = sorted({sid for sid in (deleted_ids or ()) if isinstance(sid, str) and sid})
  327. if not ids:
  328. return
  329. try:
  330. from hermes_cli.lifecycle import invoke_hook
  331. from hermes_constants import (
  332. get_hermes_home, profile_name_for_home, reset_hermes_home_override, set_hermes_home_override,
  333. )
  334. home = os.path.dirname(os.path.abspath(os.fspath(db.db_path)))
  335. profile = profile_name_for_home(home)
  336. payload: dict = {"session_id": primary if primary in ids else ids[0], "reason": reason, "profile": profile}
  337. if len(ids) > 1:
  338. payload["deleted_ids"] = ids
  339. if sessions_dir is not None:
  340. payload["sessions_dir"] = str(sessions_dir)
  341. rebind = profile is not None and os.path.realpath(home) != os.path.realpath(get_hermes_home())
  342. token = set_hermes_home_override(home) if rebind else None
  343. try:
  344. invoke_hook("on_session_delete", **payload)
  345. finally:
  346. if token is not None:
  347. reset_hermes_home_override(token)
  348. except Exception:
  349. logger.warning("on_session_delete dispatch failed", exc_info=True)
  350. _FTS_TRIGGERS = ("messages_fts_insert", "messages_fts_delete", "messages_fts_update",
  351. "messages_fts_trigram_insert", "messages_fts_trigram_delete", "messages_fts_trigram_update")
  352. SCHEMA_SQL = """
  353. CREATE TABLE IF NOT EXISTS schema_version (
  354. version INTEGER NOT NULL
  355. );
  356. CREATE TABLE IF NOT EXISTS system_prompts (
  357. hash TEXT PRIMARY KEY,
  358. prompt TEXT NOT NULL
  359. );
  360. CREATE TABLE IF NOT EXISTS sessions (
  361. id TEXT PRIMARY KEY,
  362. runtime_revision INTEGER NOT NULL DEFAULT 0,
  363. runtime_generation INTEGER NOT NULL DEFAULT 0,
  364. source TEXT NOT NULL,
  365. created_source TEXT,
  366. user_id TEXT,
  367. session_key TEXT,
  368. chat_id TEXT,
  369. chat_type TEXT,
  370. thread_id TEXT,
  371. display_name TEXT,
  372. origin_json TEXT,
  373. expiry_finalized INTEGER DEFAULT 0,
  374. model TEXT,
  375. model_config TEXT,
  376. system_prompt TEXT,
  377. system_prompt_hash TEXT,
  378. parent_session_id TEXT,
  379. started_at REAL NOT NULL,
  380. ended_at REAL,
  381. end_reason TEXT,
  382. message_count INTEGER DEFAULT 0,
  383. tool_call_count INTEGER DEFAULT 0,
  384. input_tokens INTEGER DEFAULT 0,
  385. output_tokens INTEGER DEFAULT 0,
  386. cache_read_tokens INTEGER DEFAULT 0,
  387. cache_write_tokens INTEGER DEFAULT 0,
  388. reasoning_tokens INTEGER DEFAULT 0,
  389. cwd TEXT,
  390. git_branch TEXT,
  391. git_repo_root TEXT,
  392. git_metadata_generation INTEGER NOT NULL DEFAULT 0,
  393. billing_provider TEXT,
  394. billing_base_url TEXT,
  395. billing_mode TEXT,
  396. estimated_cost_usd REAL,
  397. actual_cost_usd REAL,
  398. cost_status TEXT,
  399. cost_source TEXT,
  400. pricing_version TEXT,
  401. title TEXT,
  402. title_source TEXT,
  403. last_activity_at REAL,
  404. last_activity_description TEXT,
  405. last_activity_provenance TEXT,
  406. api_call_count INTEGER DEFAULT 0,
  407. handoff_state TEXT,
  408. handoff_platform TEXT,
  409. handoff_error TEXT,
  410. compression_failure_cooldown_until REAL,
  411. compression_failure_error TEXT,
  412. compression_fallback_streak INTEGER NOT NULL DEFAULT 0,
  413. compression_ineffective_count INTEGER NOT NULL DEFAULT 0,
  414. compression_recovery_deadline REAL,
  415. compression_overload_streak INTEGER NOT NULL DEFAULT 0,
  416. profile_name TEXT,
  417. transport_profile TEXT,
  418. rewind_count INTEGER NOT NULL DEFAULT 0,
  419. archived INTEGER NOT NULL DEFAULT 0,
  420. auto_archived INTEGER NOT NULL DEFAULT 0,
  421. pinned INTEGER NOT NULL DEFAULT 0,
  422. hidden INTEGER NOT NULL DEFAULT 0,
  423. last_read_at REAL,
  424. tool_names TEXT,
  425. transcript_revision INTEGER NOT NULL DEFAULT 0,
  426. FOREIGN KEY (parent_session_id) REFERENCES sessions(id),
  427. FOREIGN KEY (system_prompt_hash) REFERENCES system_prompts(hash)
  428. );
  429. CREATE TABLE IF NOT EXISTS messages (
  430. id INTEGER PRIMARY KEY AUTOINCREMENT,
  431. session_id TEXT NOT NULL REFERENCES sessions(id),
  432. role TEXT NOT NULL,
  433. content TEXT,
  434. tool_call_id TEXT,
  435. tool_calls TEXT,
  436. tool_name TEXT,
  437. effect_disposition TEXT,
  438. timestamp REAL NOT NULL,
  439. token_count INTEGER,
  440. finish_reason TEXT,
  441. reasoning TEXT,
  442. reasoning_content TEXT,
  443. reasoning_details TEXT,
  444. codex_reasoning_items TEXT,
  445. codex_message_items TEXT,
  446. platform_message_id TEXT,
  447. observed INTEGER DEFAULT 0,
  448. _compressed_summary INTEGER NOT NULL DEFAULT 0,
  449. active INTEGER NOT NULL DEFAULT 1,
  450. compacted INTEGER NOT NULL DEFAULT 0,
  451. api_content TEXT,
  452. display_kind TEXT,
  453. display_metadata TEXT,
  454. display_identity BLOB,
  455. display_order INTEGER,
  456. message_uid TEXT,
  457. absorbed_message_uids TEXT,
  458. tool_call_uids TEXT,
  459. tool_call_uid TEXT
  460. );
  461. CREATE TABLE IF NOT EXISTS session_model_usage (
  462. session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
  463. model TEXT NOT NULL,
  464. billing_provider TEXT NOT NULL DEFAULT '',
  465. billing_base_url TEXT NOT NULL DEFAULT '',
  466. billing_mode TEXT NOT NULL DEFAULT '',
  467. task TEXT NOT NULL DEFAULT '',
  468. api_call_count INTEGER NOT NULL DEFAULT 0,
  469. input_tokens INTEGER NOT NULL DEFAULT 0,
  470. output_tokens INTEGER NOT NULL DEFAULT 0,
  471. cache_read_tokens INTEGER NOT NULL DEFAULT 0,
  472. cache_write_tokens INTEGER NOT NULL DEFAULT 0,
  473. reasoning_tokens INTEGER NOT NULL DEFAULT 0,
  474. estimated_cost_usd REAL NOT NULL DEFAULT 0,
  475. actual_cost_usd REAL NOT NULL DEFAULT 0,
  476. cost_status TEXT,
  477. cost_source TEXT,
  478. first_seen REAL,
  479. last_seen REAL,
  480. PRIMARY KEY (session_id, model, billing_provider, billing_base_url, billing_mode, task)
  481. );
  482. CREATE TABLE IF NOT EXISTS runtime_epoch (
  483. singleton INTEGER PRIMARY KEY CHECK (singleton = 1),
  484. epoch INTEGER NOT NULL CHECK (epoch > 0),
  485. instance_id TEXT NOT NULL
  486. );
  487. CREATE TABLE IF NOT EXISTS session_admissions (
  488. seq INTEGER PRIMARY KEY AUTOINCREMENT,
  489. admission_id TEXT NOT NULL UNIQUE,
  490. request_id TEXT NOT NULL,
  491. principal_id TEXT NOT NULL,
  492. target_session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE RESTRICT,
  493. lineage_json TEXT NOT NULL,
  494. payload_json TEXT NOT NULL,
  495. payload_digest TEXT NOT NULL,
  496. intent TEXT NOT NULL CHECK (intent IN ('queue','steer','redirect')),
  497. status TEXT NOT NULL CHECK (status IN ('queued','started','unknown','terminal')),
  498. outcome TEXT,
  499. owner_epoch INTEGER,
  500. generation INTEGER,
  501. UNIQUE (principal_id, target_session_id, request_id),
  502. CHECK (status != 'started' OR (owner_epoch IS NOT NULL AND generation IS NOT NULL))
  503. );
  504. CREATE INDEX IF NOT EXISTS session_admissions_pending
  505. ON session_admissions(target_session_id, status, seq);
  506. -- Cron run history finds a job's unified-runtime runs by the scheduler principal's request ids
  507. -- (``cron:<job>:<execution>``); without this a lookup walked
  508. -- every cron admission of every job. Partial: interactive turns, the bulk of admissions, pay
  509. -- nothing for it. The constant leading column gives the planner equality + range over a covering
  510. -- index, so it beats the UNIQUE autoindex's bare principal_id prefix even without ANALYZE stats.
  511. -- The WHERE is built from CRON_PRINCIPAL_ID, the literal the run queries inline, so they cannot drift.
  512. CREATE INDEX IF NOT EXISTS idx_session_admissions_cron_request
  513. ON session_admissions(principal_id, request_id, target_session_id) WHERE principal_id = '""" + CRON_PRINCIPAL_ID + """';
  514. CREATE TABLE IF NOT EXISTS worker_executions (
  515. execution_id TEXT PRIMARY KEY,
  516. session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE RESTRICT,
  517. kind TEXT NOT NULL CHECK (kind IN ('cron','child','compute','kanban')),
  518. owner_epoch INTEGER NOT NULL,
  519. generation INTEGER NOT NULL,
  520. status TEXT NOT NULL CHECK (status IN ('registered','running','unknown','terminal')),
  521. adoption_digest TEXT NOT NULL,
  522. last_sequence INTEGER NOT NULL DEFAULT 0 CHECK (last_sequence >= 0)
  523. );
  524. -- Lineage-wide worker exclusion (claim/register/reset) probes every row of a conversation's lineage
  525. -- inside the write transaction; without this each probe scanned the whole (only-growing) table.
  526. CREATE INDEX IF NOT EXISTS idx_worker_executions_session_status
  527. ON worker_executions(session_id, status);
  528. CREATE TABLE IF NOT EXISTS worker_receipts (
  529. execution_id TEXT NOT NULL REFERENCES worker_executions(execution_id) ON DELETE RESTRICT,
  530. sequence INTEGER NOT NULL CHECK (sequence > 0),
  531. payload_digest TEXT NOT NULL,
  532. result_json TEXT NOT NULL,
  533. PRIMARY KEY (execution_id, sequence)
  534. );
  535. CREATE TABLE IF NOT EXISTS state_meta (
  536. key TEXT PRIMARY KEY,
  537. value TEXT
  538. );
  539. CREATE TABLE IF NOT EXISTS gateway_routing (
  540. scope TEXT NOT NULL DEFAULT '',
  541. session_key TEXT NOT NULL,
  542. entry_json TEXT NOT NULL,
  543. updated_at REAL NOT NULL,
  544. PRIMARY KEY (scope, session_key)
  545. );
  546. CREATE TABLE IF NOT EXISTS gateway_hygiene_state (
  547. session_key TEXT PRIMARY KEY,
  548. failure_streak INTEGER NOT NULL DEFAULT 0
  549. );
  550. -- Monotonic conversation generation per routing peer (#96811).
  551. --
  552. -- A host-declared conversation key (X-Hermes-Session-Key / build_session_key)
  553. -- is per-CHAT and outlives any single conversation on it, so the prompt-cache
  554. -- affinity scope derived from it must be qualified by which conversation is
  555. -- currently live. Deriving that from the session rows themselves
  556. -- (COUNT/MAX over _RESET_END_REASONS boundaries) cannot prove non-reuse:
  557. -- delete_session() and bulk pruning remove ended rows, so an aggregate can
  558. -- return a pair it already emitted and hand a new conversation a retired
  559. -- affinity identity.
  560. --
  561. -- This counter lives outside prunable session history and only ever
  562. -- increments, once per boundary actually written, so a generation can never
  563. -- be reused for a peer even if every session row behind it is deleted.
  564. --
  565. -- These rows are deliberately NEVER garbage-collected, including when every
  566. -- session row for the peer is gone. Collecting one resets that peer to "no
  567. -- generation", so its next boundary writes generation = 1 again and re-issues
  568. -- a gwk_ scope a retired conversation already used — exactly the ABA this
  569. -- table exists to close. Do not add it to delete_session()'s cascade or to any
  570. -- prune sweep. One (TEXT, TEXT, INTEGER) row per routing peer is the intended,
  571. -- bounded cost.
  572. CREATE TABLE IF NOT EXISTS conversation_generations (
  573. source TEXT NOT NULL,
  574. session_key TEXT NOT NULL,
  575. generation INTEGER NOT NULL DEFAULT 0,
  576. PRIMARY KEY (source, session_key)
  577. );
  578. -- Per-backend liveness heartbeat (#94895). Each serve / tui_gateway process
  579. -- registers a row at startup and refreshes ``last_heartbeat`` periodically.
  580. -- The startup orphan sweep (sessions.startup_orphan_reap) consults this
  581. -- table to avoid reaping rows whose owning backend is still alive but
  582. -- just idle (multi-backend state.db shared by isolated serve processes).
  583. -- A backend whose ``last_heartbeat`` is older than the heartbeat staleness
  584. -- window is treated as dead; rows without ANY matching heartbeat fall back
  585. -- to the original staleness predicate so legacy deployments keep working.
  586. CREATE TABLE IF NOT EXISTS gateway_heartbeats (
  587. backend_id TEXT PRIMARY KEY,
  588. pid INTEGER NOT NULL,
  589. started_at REAL NOT NULL,
  590. last_heartbeat REAL NOT NULL,
  591. profile TEXT NOT NULL DEFAULT '',
  592. host TEXT NOT NULL DEFAULT ''
  593. );
  594. CREATE TABLE IF NOT EXISTS compression_locks (
  595. session_id TEXT PRIMARY KEY,
  596. holder TEXT NOT NULL,
  597. acquired_at REAL NOT NULL,
  598. expires_at REAL NOT NULL
  599. );
  600. CREATE TABLE IF NOT EXISTS session_turn_leases (
  601. conversation_id TEXT PRIMARY KEY,
  602. holder TEXT NOT NULL,
  603. acquired_at REAL NOT NULL,
  604. expires_at REAL NOT NULL
  605. );
  606. CREATE TABLE IF NOT EXISTS async_delegations (
  607. delegation_id TEXT PRIMARY KEY,
  608. origin_session TEXT NOT NULL,
  609. origin_ui_session_id TEXT NOT NULL DEFAULT '',
  610. parent_session_id TEXT,
  611. state TEXT NOT NULL,
  612. dispatched_at REAL NOT NULL,
  613. completed_at REAL,
  614. updated_at REAL NOT NULL,
  615. event_json TEXT,
  616. result_json TEXT,
  617. delivery_state TEXT NOT NULL DEFAULT 'pending',
  618. delivery_attempts INTEGER NOT NULL DEFAULT 0,
  619. delivered_at REAL,
  620. owner_pid INTEGER,
  621. owner_started_at INTEGER,
  622. task_json TEXT,
  623. -- Owner-side execution fence (unified runtime).
  624. owner_execution_id TEXT,
  625. delivery_claim TEXT,
  626. delivery_claimed_at REAL,
  627. -- Mirrors the delegation tool's own CREATE TABLE (tools/async_delegation.py
  628. -- _initialize_schema). Keeping the canonical fresh-install shape identical
  629. -- to the tool's avoids a silent schema drift: the tool's lazy
  630. -- ALTER TABLE ADD COLUMN used to be the only source of this column, so two
  631. -- databases at the same schema_version had different
  632. -- async_delegations shapes depending on whether the delegation tool had
  633. -- ever run, breaking rebuild/replay pipelines that reconstruct state.db
  634. -- from the canonical schema (#94691).
  635. origin_session_id TEXT NOT NULL DEFAULT ''
  636. );
  637. CREATE INDEX IF NOT EXISTS idx_sessions_source ON sessions(source);
  638. CREATE INDEX IF NOT EXISTS idx_sessions_source_id ON sessions(source, id);
  639. CREATE INDEX IF NOT EXISTS idx_sessions_parent ON sessions(parent_session_id);
  640. CREATE INDEX IF NOT EXISTS idx_sessions_started ON sessions(started_at DESC);
  641. CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_id, timestamp);
  642. CREATE INDEX IF NOT EXISTS idx_messages_session_id ON messages(session_id, id);
  643. -- Partial index for the Insights assistant tool-call scan
  644. -- (agent/insights.py _get_tool_usage / _get_skill_usage): those queries filter
  645. -- messages by role='assistant' AND tool_calls IS NOT NULL, a small fraction of
  646. -- rows on a large state.db. role and tool_calls are base columns, so this can
  647. -- live in SCHEMA_SQL rather than DEFERRED_INDEX_SQL.
  648. CREATE INDEX IF NOT EXISTS idx_messages_assistant_calls_by_session
  649. ON messages(session_id)
  650. WHERE role = 'assistant' AND tool_calls IS NOT NULL;
  651. CREATE INDEX IF NOT EXISTS idx_compression_locks_expires ON compression_locks(expires_at);
  652. CREATE INDEX IF NOT EXISTS idx_session_turn_leases_expires ON session_turn_leases(expires_at);
  653. CREATE INDEX IF NOT EXISTS idx_session_model_usage_session ON session_model_usage(session_id);
  654. CREATE INDEX IF NOT EXISTS idx_session_model_usage_model ON session_model_usage(model);
  655. CREATE INDEX IF NOT EXISTS idx_async_delegations_delivery
  656. ON async_delegations(delivery_state, completed_at);
  657. """
  658. # Indexes on later-added columns must run AFTER _reconcile_columns(), or executescript fails on legacy DBs.
  659. # sessions_runtime_ledger_delete_guard: every delete path in this release retires a session's ledger rows
  660. # (session_admissions / worker_executions, ON DELETE RESTRICT) first, so only a release that predates the
  661. # gateway runtime reaches it. Such a release (schema_version 31 too, and it never refuses a newer schema)
  662. # otherwise dies on the bare FK with no hint. It prints the RAISE text verbatim and buckets errors by phrase,
  663. # so the text must stay free of the words it reads as storage damage, lock contention or disk trouble.
  664. DEFERRED_INDEX_SQL = """
  665. DROP TRIGGER IF EXISTS sessions_runtime_ledger_delete_guard;
  666. CREATE TRIGGER IF NOT EXISTS sessions_runtime_ledger_delete_guard
  667. BEFORE DELETE ON sessions
  668. WHEN EXISTS (SELECT 1 FROM session_admissions WHERE target_session_id = old.id)
  669. OR EXISTS (SELECT 1 FROM worker_executions WHERE session_id = old.id)
  670. BEGIN
  671. SELECT RAISE(ABORT, 'Refused to delete a session that has gateway runtime history: state.db was upgraded by a newer Hermes, and this older Hermes cannot delete such sessions. Downgrading is unsupported. Run hermes update to go back to the newer release, or restore the pre-update snapshot: https://hermes-agent.nousresearch.com/docs/getting-started/updating#rolling-back-to-an-older-release');
  672. END;
  673. DROP TRIGGER IF EXISTS messages_transcript_revision_update;
  674. CREATE TRIGGER IF NOT EXISTS messages_transcript_revision_update
  675. AFTER UPDATE OF role, content, api_content, tool_calls, tool_call_id, tool_name, reasoning, reasoning_content,
  676. reasoning_details ON messages
  677. BEGIN
  678. -- An in-place rewrite (prompt expansion, multimodal context, a redaction) moves neither the
  679. -- active row count nor the highest id; this write-side revision is what a transcript-derived
  680. -- cache keys on (conversation_revision). Whoever wrote the row, old builds included.
  681. UPDATE sessions SET transcript_revision = transcript_revision + 1 WHERE id = new.session_id;
  682. END;
  683. CREATE INDEX IF NOT EXISTS idx_messages_session_active
  684. ON messages(session_id, active, timestamp);
  685. CREATE INDEX IF NOT EXISTS idx_messages_display_page
  686. ON messages(session_id, display_order, active DESC, id DESC)
  687. WHERE active = 1 OR compacted = 1;
  688. CREATE INDEX IF NOT EXISTS idx_messages_display_backfill
  689. ON messages(session_id) WHERE (display_order IS NULL OR display_identity IS NULL)
  690. AND (active = 1 OR compacted = 1);
  691. CREATE INDEX IF NOT EXISTS idx_messages_display_identity
  692. ON messages(session_id, display_identity, display_order)
  693. WHERE display_identity IS NOT NULL AND (active = 1 OR compacted = 1);
  694. DROP TRIGGER IF EXISTS messages_message_uid_insert;
  695. CREATE TRIGGER IF NOT EXISTS messages_message_uid_insert
  696. AFTER INSERT ON messages WHEN new.message_uid IS NULL
  697. BEGIN
  698. -- Every row carries a message_uid, whoever wrote it: a build that predates the column binds NULL, so
  699. -- the store mints one on its behalf (the current build always binds a uid; this never fires for it).
  700. -- The note lives inside the body: a comment before DROP/CREATE would defeat the settled-trigger skip.
  701. UPDATE messages SET message_uid = lower(hex(randomblob(16))) WHERE id = new.id;
  702. END;
  703. DROP TRIGGER IF EXISTS messages_display_order_insert;
  704. CREATE TRIGGER IF NOT EXISTS messages_display_order_insert
  705. AFTER INSERT ON messages WHEN new.display_order IS NULL
  706. BEGIN
  707. UPDATE messages SET display_order = COALESCE((
  708. SELECT display_order FROM messages
  709. WHERE session_id = new.session_id AND id <> new.id
  710. AND (active = 1 OR compacted = 1)
  711. AND display_identity = new.display_identity AND display_order IS NOT NULL
  712. ORDER BY display_order LIMIT 1
  713. ), new.id) WHERE id = new.id;
  714. END;
  715. DROP TRIGGER IF EXISTS messages_display_visibility_update;
  716. CREATE TRIGGER IF NOT EXISTS messages_display_visibility_update
  717. AFTER UPDATE OF active, compacted ON messages
  718. WHEN (new.active = 1 OR new.compacted = 1) <> (old.active = 1 OR old.compacted = 1)
  719. BEGIN
  720. UPDATE messages SET display_order = MIN(new.id, COALESCE((
  721. SELECT display_order FROM messages
  722. WHERE session_id = new.session_id AND id <> new.id
  723. AND (active = 1 OR compacted = 1)
  724. AND display_identity = new.display_identity AND display_order IS NOT NULL
  725. ORDER BY display_order LIMIT 1
  726. ), new.id)) WHERE id = new.id
  727. AND (new.active = 1 OR new.compacted = 1);
  728. UPDATE messages SET display_order = (SELECT display_order FROM messages WHERE id = new.id)
  729. WHERE session_id = new.session_id AND id <> new.id AND (active = 1 OR compacted = 1)
  730. AND display_identity = new.display_identity
  731. AND (new.active = 1 OR new.compacted = 1);
  732. UPDATE messages SET display_order = (
  733. SELECT MIN(peer.id) FROM messages AS peer
  734. WHERE peer.session_id = old.session_id AND (peer.active = 1 OR peer.compacted = 1)
  735. AND peer.display_identity = old.display_identity
  736. ) WHERE session_id = old.session_id AND (active = 1 OR compacted = 1)
  737. AND display_identity = old.display_identity
  738. AND NOT (new.active = 1 OR new.compacted = 1);
  739. END;
  740. DROP TRIGGER IF EXISTS messages_display_identity_update;
  741. CREATE TRIGGER IF NOT EXISTS messages_display_identity_update
  742. AFTER UPDATE OF role, content, timestamp, tool_call_id, tool_calls, tool_name,
  743. display_kind ON messages
  744. WHEN new.role IS NOT old.role
  745. OR new.content IS NOT old.content
  746. OR new.timestamp IS NOT old.timestamp
  747. OR new.tool_call_id IS NOT old.tool_call_id
  748. OR new.tool_calls IS NOT old.tool_calls
  749. OR new.tool_name IS NOT old.tool_name
  750. OR new.display_kind IS NOT old.display_kind
  751. BEGIN
  752. UPDATE messages SET display_identity = NULL, display_order = NULL
  753. WHERE id = new.id OR (
  754. session_id = old.session_id AND display_identity = old.display_identity
  755. AND (active = 1 OR compacted = 1)
  756. );
  757. END;
  758. DROP TRIGGER IF EXISTS messages_display_identity_delete;
  759. CREATE TRIGGER IF NOT EXISTS messages_display_identity_delete
  760. AFTER DELETE ON messages WHEN old.active = 1 OR old.compacted = 1
  761. BEGIN
  762. UPDATE messages SET display_order = (
  763. SELECT MIN(peer.id) FROM messages AS peer
  764. WHERE peer.session_id = old.session_id AND (peer.active = 1 OR peer.compacted = 1)
  765. AND peer.display_identity = old.display_identity
  766. ) WHERE session_id = old.session_id AND (active = 1 OR compacted = 1)
  767. AND display_identity = old.display_identity;
  768. END;
  769. CREATE INDEX IF NOT EXISTS idx_messages_active_null
  770. ON messages(active) WHERE active IS NULL;
  771. -- Logical-identity recovery (agent.transcript_repair._active_logical_message_row) runs inside the write lock.
  772. CREATE INDEX IF NOT EXISTS idx_messages_session_uid
  773. ON messages(session_id, message_uid) WHERE active = 1;
  774. CREATE INDEX IF NOT EXISTS idx_sessions_session_key
  775. ON sessions(session_key, started_at DESC);
  776. CREATE INDEX IF NOT EXISTS idx_sessions_gateway_peer
  777. ON sessions(source, user_id, chat_id, chat_type, thread_id, started_at DESC);
  778. CREATE INDEX IF NOT EXISTS idx_sessions_handoff_state
  779. ON sessions(handoff_state, started_at);
  780. CREATE INDEX IF NOT EXISTS idx_sessions_system_prompt_hash
  781. ON sessions(system_prompt_hash);
  782. CREATE INDEX IF NOT EXISTS idx_sessions_tool_names
  783. ON sessions(tool_names);
  784. -- Recent-session browsing must never derive recency by scanning messages.
  785. -- This expression is the durable, indexable approximation used to preselect
  786. -- a small candidate set before compression-chain and preview hydration.
  787. CREATE INDEX IF NOT EXISTS idx_sessions_effective_activity
  788. ON sessions(COALESCE(last_activity_at, started_at) DESC, started_at DESC);
  789. -- CLI/TUI listings match gateway rows stored as source='local' by created_source,
  790. -- before LIMIT. created_source is reconciled onto legacy DBs, so this index lives
  791. -- here rather than in SCHEMA_SQL. A source-only index would walk every local row
  792. -- (Desktop and TUI included) to find one surface.
  793. CREATE INDEX IF NOT EXISTS idx_sessions_local_created_started
  794. ON sessions(created_source, started_at DESC)
  795. WHERE source = 'local';
  796. """
  797. # ── Deferred FTS rebuild bookkeeping (schema v23) ──
  798. # While a background index rebuild is pending, two state_meta keys define
  799. # which message rows are currently IN the FTS indexes:
  800. #
  801. # fts_rebuild_high_water H — MAX(messages.id) at the moment the old
  802. # indexes were dropped
  803. # fts_rebuild_progress P — highest id the chunked backfill has indexed
  804. #
  805. # A row is indexed iff id <= P (backfilled) OR id > H (inserted after
  806. # the drop; ids are AUTOINCREMENT so new rows are always > H and the insert
  807. # triggers index them live). Rows in (P, H] are not yet indexed.
  808. #
  809. # Every trigger below gates on that same predicate: firing an FTS5
  810. # external-content 'delete' for a row that is NOT in the index corrupts the
  811. # index, and skipping it for a row that IS indexed leaves a stale entry.
  812. # When no rebuild is pending both keys are absent and COALESCE turns the
  813. # predicate into a tautology (id > -1 OR id <= -1), i.e. normal operation.
  814. # The two state_meta PK probes per write are negligible next to the FTS
  815. # insert itself.
  816. #
  817. # messages_fts_src: the base word index no longer reads raw `messages` as its
  818. # external content. Tool rows are indexed as a bounded prefix, so the index
  819. # must read that SAME projection back or FTS5's 'integrity-check' / 'delete'
  820. # commands disagree with the stored tokens and corrupt the index (the
  821. # recurring fts5 checksum-mismatch drift: the projection used to depend on a
  822. # moving state_meta high-water key). The view/trigger/backfill all share the
  823. # one expression in `_fts_indexed_content_sql` — a fixed per-row function
  824. # with no marker lookups — so the boundary can never move again.
  825. FTS_SQL = f"""
  826. -- Stable projection the base word index reads and writes through: the view
  827. -- computes EXACTLY what the triggers/backfill insert, so 'rebuild' and the
  828. -- integrity checker always agree with the stored index.
  829. CREATE VIEW IF NOT EXISTS messages_fts_src AS
  830. SELECT id,
  831. CASE WHEN role = 'tool'
  832. THEN substr(COALESCE(content, ''), 1, {FTS_TOOL_CONTENT_PREFIX_CHARS})
  833. ELSE content END AS content,
  834. tool_name, tool_calls
  835. FROM messages;
  836. CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(
  837. content,
  838. tool_name,
  839. tool_calls,
  840. content='messages_fts_src',
  841. content_rowid='id'
  842. );
  843. CREATE TRIGGER IF NOT EXISTS messages_fts_insert AFTER INSERT ON messages
  844. WHEN (new.id > COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  845. WHERE key = 'fts_rebuild_high_water'), -1)
  846. OR new.id <= COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  847. WHERE key = 'fts_rebuild_progress'), -1))
  848. BEGIN
  849. INSERT INTO messages_fts(rowid, content, tool_name, tool_calls)
  850. VALUES (
  851. new.id,
  852. {_FTS_NEW_INDEXED_CONTENT_SQL},
  853. new.tool_name,
  854. new.tool_calls
  855. );
  856. END;
  857. CREATE TRIGGER IF NOT EXISTS messages_fts_delete AFTER DELETE ON messages
  858. WHEN (old.id > COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  859. WHERE key = 'fts_rebuild_high_water'), -1)
  860. OR old.id <= COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  861. WHERE key = 'fts_rebuild_progress'), -1))
  862. BEGIN
  863. INSERT INTO messages_fts(messages_fts, rowid, content, tool_name, tool_calls)
  864. VALUES (
  865. 'delete',
  866. old.id,
  867. {_FTS_OLD_INDEXED_CONTENT_SQL},
  868. old.tool_name,
  869. old.tool_calls
  870. );
  871. END;
  872. -- UPDATE OF skips the trigger entirely for non-content column writes
  873. -- (status/compacted/observed/etc.), which is stronger than the WHEN gate
  874. -- alone and avoids FTS I/O saturation on large state.db (#68858 / #73639).
  875. CREATE TRIGGER IF NOT EXISTS messages_fts_update
  876. AFTER UPDATE OF content, tool_name, tool_calls, role ON messages
  877. WHEN (old.content IS NOT new.content
  878. OR old.tool_name IS NOT new.tool_name
  879. OR old.tool_calls IS NOT new.tool_calls
  880. OR old.role IS NOT new.role)
  881. AND (old.id > COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  882. WHERE key = 'fts_rebuild_high_water'), -1)
  883. OR old.id <= COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  884. WHERE key = 'fts_rebuild_progress'), -1))
  885. BEGIN
  886. INSERT INTO messages_fts(messages_fts, rowid, content, tool_name, tool_calls)
  887. VALUES (
  888. 'delete',
  889. old.id,
  890. {_FTS_OLD_INDEXED_CONTENT_SQL},
  891. old.tool_name,
  892. old.tool_calls
  893. );
  894. INSERT INTO messages_fts(rowid, content, tool_name, tool_calls)
  895. VALUES (
  896. new.id,
  897. {_FTS_NEW_INDEXED_CONTENT_SQL},
  898. new.tool_name,
  899. new.tool_calls
  900. );
  901. END;
  902. """
  903. # Trigram FTS5 table for CJK substring search. The default unicode61
  904. # tokenizer splits CJK characters into individual tokens, breaking phrase
  905. # matching. The trigram tokenizer creates overlapping 3-byte sequences so
  906. # substring queries work natively for any script (CJK, Thai, etc.).
  907. #
  908. # The trigram index is the most expensive index in state.db (~2.6x the size
  909. # of the text it covers). Tool output (~90% of message bytes, machine noise)
  910. # and cron transcripts are excluded: the index reads through
  911. # ``messages_fts_trigram_src``, a view that skips both classes. They stay
  912. # fully stored in ``messages`` and searchable via the standard
  913. # ``messages_fts`` index; they just don't get trigram (CJK substring)
  914. # treatment. ``search_messages`` routes explicit tool/cron CJK searches to
  915. # LIKE for the same reason. Structured ``tool_calls`` JSON likewise stays
  916. # searchable through ``messages_fts``; excluding it here avoids indexing
  917. # repetitive JSON syntax as trigrams (FTS_STORAGE_VERSION 2).
  918. #
  919. # Delegate-child (subagent) transcripts are excluded the same way (v30):
  920. # on a fan-out-heavy install they were ~70% of all message bytes and
  921. # ``session_search`` hides ``source='subagent'`` sessions anyway. A child
  922. # is recognised by its source OR by the ``_delegate_from`` creation marker
  923. # (children spawned under a gateway turn inherit the gateway's source).
  924. # Compression/branch continuations of interactive sessions also carry
  925. # ``parent_session_id`` but NOT the marker, so they stay trigram-indexed.
  926. FTS_TRIGRAM_EXCLUDED_SOURCES = ("cron", "subagent")
  927. def fts_trigram_session_sql(alias: str = "") -> str:
  928. """Predicate over a ``sessions`` row selecting sessions whose rows belong in
  929. the trigram index; ``alias`` qualifies every column for joins. Shared by the
  930. view, the sync triggers, and the deferred-backfill INSERT ... SELECTs so they
  931. can never disagree about the index boundary."""
  932. q = f"{alias}." if alias else ""
  933. return (
  934. f"{q}source NOT IN ("
  935. + ", ".join(f"'{src}'" for src in FTS_TRIGRAM_EXCLUDED_SOURCES)
  936. + f") AND {_sql_json_extract(q + 'model_config', '$._delegate_from')} IS NULL"
  937. )
  938. FTS_TRIGRAM_SESSION_SQL = fts_trigram_session_sql()
  939. FTS_TRIGRAM_SQL = f"""
  940. CREATE VIEW IF NOT EXISTS messages_fts_trigram_src AS
  941. SELECT m.id, m.role, m.content, m.tool_name
  942. FROM messages AS m
  943. JOIN sessions AS s ON s.id = m.session_id
  944. WHERE m.role <> 'tool' AND {fts_trigram_session_sql('s')};
  945. CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts_trigram USING fts5(
  946. content,
  947. tool_name,
  948. content='messages_fts_trigram_src',
  949. content_rowid='id',
  950. tokenize='trigram'
  951. );
  952. CREATE TRIGGER IF NOT EXISTS messages_fts_trigram_insert AFTER INSERT ON messages
  953. WHEN new.role <> 'tool'
  954. AND EXISTS (SELECT 1 FROM sessions
  955. WHERE id = new.session_id AND {FTS_TRIGRAM_SESSION_SQL})
  956. AND (new.id > COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  957. WHERE key = 'fts_rebuild_high_water'), -1)
  958. OR new.id <= COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  959. WHERE key = 'fts_rebuild_progress'), -1))
  960. BEGIN
  961. INSERT INTO messages_fts_trigram(rowid, content, tool_name)
  962. VALUES (new.id, new.content, new.tool_name);
  963. END;
  964. CREATE TRIGGER IF NOT EXISTS messages_fts_trigram_delete AFTER DELETE ON messages
  965. WHEN old.role <> 'tool'
  966. AND EXISTS (SELECT 1 FROM sessions
  967. WHERE id = old.session_id AND {FTS_TRIGRAM_SESSION_SQL})
  968. AND (old.id > COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  969. WHERE key = 'fts_rebuild_high_water'), -1)
  970. OR old.id <= COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  971. WHERE key = 'fts_rebuild_progress'), -1))
  972. BEGIN
  973. INSERT INTO messages_fts_trigram(messages_fts_trigram, rowid, content, tool_name)
  974. VALUES ('delete', old.id, old.content, old.tool_name);
  975. END;
  976. CREATE TRIGGER IF NOT EXISTS messages_fts_trigram_update
  977. AFTER UPDATE OF content, tool_name, role ON messages
  978. WHEN (old.content IS NOT new.content
  979. OR old.tool_name IS NOT new.tool_name
  980. OR old.role IS NOT new.role)
  981. AND (old.id > COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  982. WHERE key = 'fts_rebuild_high_water'), -1)
  983. OR old.id <= COALESCE((SELECT CAST(value AS INTEGER) FROM state_meta
  984. WHERE key = 'fts_rebuild_progress'), -1))
  985. BEGIN
  986. INSERT INTO messages_fts_trigram(messages_fts_trigram, rowid, content, tool_name)
  987. SELECT 'delete', old.id, old.content, old.tool_name
  988. WHERE old.role <> 'tool'
  989. AND EXISTS (SELECT 1 FROM sessions
  990. WHERE id = old.session_id AND {FTS_TRIGRAM_SESSION_SQL});
  991. INSERT INTO messages_fts_trigram(rowid, content, tool_name)
  992. SELECT new.id, new.content, new.tool_name
  993. WHERE new.role <> 'tool'
  994. AND EXISTS (SELECT 1 FROM sessions
  995. WHERE id = new.session_id AND {FTS_TRIGRAM_SESSION_SQL});
  996. END;
  997. """
  998. _FTS_CJK_TRIGGERS = ("messages_fts_cjk_insert", "messages_fts_cjk_delete", "messages_fts_cjk_update")
  999. # Set when a tokenizer-less process dropped the cjk triggers to keep writes alive: the cjk index is missing rows
  1000. # and must not serve reads until `hermes sessions optimize-storage` rebuilds it on a capable host.
  1001. FTS_CJK_STALE_KEY = "fts_cjk_stale"
  1002. # Set when a base/trigram FTS index was detached after runtime corruption; startup must rebuild the complete
  1003. # index before reinstalling sync triggers (rows written while they were absent leave an unknown gap).
  1004. FTS_STALE_KEY = "fts_stale"
  1005. # Durable diagnostic for stale FTS recovery blocked across process restarts.
  1006. FTS_REBUILD_DEFERRAL_KEY = "fts_rebuild_deferral"
  1007. # ── Legacy (v22 / inline-content) FTS DDL ──────────────────────────────
  1008. # Used ONLY to keep an existing pre-v23 install's search working and its
  1009. # triggers repairable UNTIL the user opts into `hermes db optimize`. This is
  1010. # the exact inline shape v11..v22 shipped: each virtual table stores its own
  1011. # copy of ``content || tool_name || tool_calls`` and the trigram table indexes
  1012. # every row (including role='tool'). We never CREATE these on a fresh install —
  1013. # fresh installs are born on the v23 external-content schema above. These
  1014. # constants exist so a legacy DB is never accidentally handed the v23 DDL
  1015. # (which would create the external-content trigram source VIEW and leave the
  1016. # DB in a mixed, broken state). `optimize_fts_storage()` is what migrates a
  1017. # legacy DB to the v23 shape.
  1018. LEGACY_FTS_SQL = f"""
  1019. CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(
  1020. content
  1021. );
  1022. CREATE TRIGGER IF NOT EXISTS messages_fts_insert AFTER INSERT ON messages BEGIN
  1023. INSERT INTO messages_fts(rowid, content) VALUES (
  1024. new.id,
  1025. COALESCE({_FTS_NEW_INDEXED_CONTENT_SQL}, '')
  1026. || ' ' || COALESCE(new.tool_name, '') || ' ' || COALESCE(new.tool_calls, '')
  1027. );
  1028. END;
  1029. CREATE TRIGGER IF NOT EXISTS messages_fts_delete AFTER DELETE ON messages BEGIN
  1030. DELETE FROM messages_fts WHERE rowid = old.id;
  1031. END;
  1032. CREATE TRIGGER IF NOT EXISTS messages_fts_update
  1033. AFTER UPDATE OF content, tool_name, tool_calls, role ON messages BEGIN
  1034. DELETE FROM messages_fts WHERE rowid = old.id;
  1035. INSERT INTO messages_fts(rowid, content) VALUES (
  1036. new.id,
  1037. COALESCE({_FTS_NEW_INDEXED_CONTENT_SQL}, '')
  1038. || ' ' || COALESCE(new.tool_name, '') || ' ' || COALESCE(new.tool_calls, '')
  1039. );
  1040. END;
  1041. """
  1042. LEGACY_FTS_TRIGRAM_SQL = f"""
  1043. CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts_trigram USING fts5(
  1044. content,
  1045. tokenize='trigram'
  1046. );
  1047. CREATE TRIGGER IF NOT EXISTS messages_fts_trigram_insert AFTER INSERT ON messages BEGIN
  1048. INSERT INTO messages_fts_trigram(rowid, content) VALUES (
  1049. new.id,
  1050. COALESCE({_FTS_NEW_INDEXED_CONTENT_SQL}, '')
  1051. || ' ' || COALESCE(new.tool_name, '') || ' ' || COALESCE(new.tool_calls, '')
  1052. );
  1053. END;
  1054. CREATE TRIGGER IF NOT EXISTS messages_fts_trigram_delete AFTER DELETE ON messages BEGIN
  1055. DELETE FROM messages_fts_trigram WHERE rowid = old.id;
  1056. END;
  1057. CREATE TRIGGER IF NOT EXISTS messages_fts_trigram_update
  1058. AFTER UPDATE OF content, tool_name, tool_calls, role ON messages BEGIN
  1059. DELETE FROM messages_fts_trigram WHERE rowid = old.id;
  1060. INSERT INTO messages_fts_trigram(rowid, content) VALUES (
  1061. new.id,
  1062. COALESCE({_FTS_NEW_INDEXED_CONTENT_SQL}, '')
  1063. || ' ' || COALESCE(new.tool_name, '') || ' ' || COALESCE(new.tool_calls, '')
  1064. );
  1065. END;
  1066. """
  1067. # Cross-process full-FTS-rebuild admission (single authority). Several processes share one state.db and a
  1068. # structural rebuild (FTS5 'rebuild' or `_recover_stale_fts`'s drop/recreate) must run in ONE at a time —
  1069. # concurrent rebuilds corrupted state.db in production. Gates `rebuild_fts()`, `_rebuild_fts_indexes()`,
  1070. # `_recover_stale_fts()`; the chunked backfill (`fts_rebuild_step`) is deliberately NOT routed through it (it
  1071. # claims progress under SQLite transaction authority). Mirrors `hermes_state_repair._cross_process_repair_lock`:
  1072. # portable (msvcrt/flock), bounded wait, FAIL CLOSED; orphaned-fd holders (see `_acquire_db_flock`) are broken
  1073. # only when provably dead, indeterminate liveness defers. `<db>.fts_rebuild.lock` is distinct from
  1074. # `<db>.repair.lock` (offline schema surgery, minutes in VACUUM). Lives here: mixins cannot import hermes_state.
  1075. # ── Cross-process full-FTS-rebuild admission (single authority) ────────────── Several independent Hermes
  1076. # processes routinely share one state.db (gateway service, the Desktop app's `hermes serve` backend,
  1077. # interactive CLI sessions, the TUI slash worker). A full structural FTS rebuild — the FTS5 'rebuild'
  1078. # command or the drop/recreate script in `_recover_stale_fts` — must only ever run in ONE of them at a time:
  1079. # two concurrent rebuilds collide on write and have structurally corrupted state.db in production (PR
  1080. # #93200; the 2026-08-15 / 2026-08-23 incidents and issues #89293 / #90950). This is the single admission
  1081. # authority for every full structural rebuild entry point: `SessionSearchMixin.rebuild_fts()`,
  1082. # `SessionSchemaMixin._rebuild_fts_indexes()` (via `_init_schema`), and
  1083. # `SessionSchemaMixin._recover_stale_fts()`. The chunked deferred backfill (`fts_rebuild_step`) is
  1084. # deliberately NOT routed through it — it claims progress under `_execute_write`'s SQLite transaction
  1085. # authority and is intentionally multi-process. Semantics mirror `hermes_state_repair._cross_process_repair_lock`
  1086. # (the schema- surgery authority): portable (msvcrt on Windows, flock elsewhere), bounded wait, and FAIL
  1087. # CLOSED — a caller that cannot acquire the lock must NOT rebuild. The kernel drops both lock types when the
  1088. # holder dies — UNLESS a forked child inherited the lock fd (flock rides the open file description, which
  1089. # fork() duplicates), in which case the orphaned descriptor holds the lock forever (issue #100108).
  1090. # `_acquire_db_flock` therefore records the holder's pid + start time under the lock and, when the recorded
  1091. # holder is provably dead, breaks the orphaned lock by unlinking and retaking it on a fresh inode;
  1092. # indeterminate liveness still defers. It lives here (not hermes_state) because the search/schema mixins
  1093. # cannot import hermes_state (cycle). The lock file is `<db>.fts_rebuild.lock`, distinct from
  1094. # `<db>.repair.lock`: schema surgery runs on an EXCLUSIVE offline connection and can legitimately take
  1095. # minutes in VACUUM, while runtime rebuilds run on live connections. The timeout is sized for a full
  1096. # 'rebuild' of both indexes on a large DB.
  1097. logger = logging.getLogger("hermes_state")
  1098. _FTS_REBUILD_LOCK_TIMEOUT_SECONDS = 120.0
  1099. _FTS_REBUILD_LOCK_POLL_SECONDS = 0.1
  1100. _IS_WINDOWS = sys.platform == "win32"
  1101. # Post-break re-acquire budget: the fresh inode is contended only by live processes — never the full timeout.
  1102. _LOCK_BREAK_REACQUIRE_SECONDS = 5.0
  1103. # "Another process holds the lock": flock → EWOULDBLOCK/EAGAIN, msvcrt.locking → EACCES (EDEADLK when its retry
  1104. # gives up). Anything else (ESTALE, ENOTSUP, ENOLCK, EIO) is a persistent failure polling cannot fix.
  1105. _LOCK_CONTENTION_ERRNOS = {errno.EAGAIN, errno.EACCES, errno.EWOULDBLOCK, errno.EDEADLK}
  1106. def is_advisory_lock_contention(exc: BaseException) -> bool:
  1107. """True when *exc* means another process holds the lock; on any other ``OSError`` fail closed at once."""
  1108. return isinstance(exc, BlockingIOError) or (isinstance(exc, OSError) and exc.errno in _LOCK_CONTENTION_ERRNOS)
  1109. def _proc_start_ticks(pid: int):
  1110. """Kernel start time of *pid* (field 22 of ``/proc/<pid>/stat``; with the PID it identifies a process
  1111. uniquely). None off Linux or on any failure — callers must treat None as unknowable and FAIL CLOSED."""
  1112. try:
  1113. # comm (field 2) may contain spaces/parens; split after the LAST ')'.
  1114. with open(f"/proc/{pid}/stat", "rb") as fh:
  1115. return int(fh.read().rsplit(b")", 1)[1].split()[19])
  1116. except (OSError, ValueError, IndexError):
  1117. return None
  1118. def _read_lock_holder_record(handle):
  1119. """Best-effort parse of the holder metadata JSON in a lock file."""
  1120. try:
  1121. handle.seek(0)
  1122. raw = handle.read(4096)
  1123. record = json.loads(raw.decode("utf-8", "replace")) if raw else None
  1124. except (OSError, ValueError, UnicodeDecodeError):
  1125. return None
  1126. return record if isinstance(record, dict) else None
  1127. def _rewrite_lock_file(handle, payload: bytes) -> None:
  1128. """Best-effort truncate-and-write of *payload* at offset 0."""
  1129. with contextlib.suppress(OSError, ValueError):
  1130. handle.seek(0)
  1131. handle.truncate()
  1132. if payload:
  1133. handle.write(payload)
  1134. handle.flush()
  1135. def _write_lock_holder_record(handle) -> None:
  1136. """Record this process as holder (best effort) so timed-out contenders can tell an orphaned-fd holder
  1137. from a live wedged one.
  1138. Written under the flock so contenders that time out can tell an orphaned-fd holder (recorded process
  1139. dead, flock inherited by a forked child — issue #100108) from a live wedged holder. ``pidns``: see
  1140. ``hermes_state_pidns``.
  1141. """
  1142. record = {"pid": os.getpid(), "pidns": pid_namespace_id(),
  1143. "start_ticks": _proc_start_ticks(os.getpid()), "acquired_at": time.time()}
  1144. _rewrite_lock_file(handle, json.dumps(record, sort_keys=True).encode("utf-8"))
  1145. def _clear_lock_holder_record(handle) -> None:
  1146. """Erase holder metadata before a normal release: a surviving record means ABNORMAL exit (break allowed)."""
  1147. _rewrite_lock_file(handle, b"")
  1148. def _lock_holder_provably_dead(record) -> bool:
  1149. """True ONLY when the recorded holder is provably dead or PID-recycled. Anything indeterminate
  1150. (no/malformed record, PID owned by another user, /proc unavailable, foreign PID namespace)
  1151. is False: FAIL CLOSED and defer. Unstamped records keep probing: see ``hermes_state_pidns``."""
  1152. try:
  1153. pid = int(record["pid"])
  1154. except (KeyError, TypeError, ValueError):
  1155. return False
  1156. if pid <= 0:
  1157. return False
  1158. if not persistent_record_pidns_checkable(record.get("pidns")):
  1159. return False # foreign namespace: a local reading is not proof — defer
  1160. try:
  1161. os.kill(pid, 0)
  1162. except ProcessLookupError:
  1163. return True
  1164. except OSError:
  1165. return False # PermissionError et al.: PID exists (or unknowable) — closed
  1166. recorded_ticks = record.get("start_ticks")
  1167. if recorded_ticks is None:
  1168. return False
  1169. current_ticks = _proc_start_ticks(pid)
  1170. return current_ticks is not None and current_ticks != recorded_ticks # different start time: PID recycled
  1171. def _acquire_db_flock(lock_path, handle, timeout_seconds, poll_seconds, description):
  1172. """Bounded POSIX flock acquire with orphaned-holder break. Returns ``(acquired, handle)``; *handle* may
  1173. have been re-opened and the caller closes whichever comes back. *acquired*: True, False (a holder kept
  1174. the lock past the deadline) or None (non-contention ``OSError``, already logged: treat as not acquired
  1175. without the held-by-another-process warning). ``flock`` rides the open file DESCRIPTION, which ``fork()``
  1176. duplicates, so a holder that forks then dies leaves the lock held forever; when the acquirer is provably
  1177. dead the file is unlinked and retaken on a fresh inode (the orphan's flock excludes nobody). Every
  1178. acquire verifies its inode still names *lock_path*, so a racer on a dead inode retries.
  1179. A holder that forks (multiprocessing worker, daemonized helper) and then dies leaves the flock held by a
  1180. child that will never release it — the kernel's holder-death release never triggers, and every contender
  1181. defers forever. Indeterminate liveness always defers (fail closed). See #100108.
  1182. """
  1183. import fcntl
  1184. deadline = time.monotonic() + timeout_seconds
  1185. broke_lock = False
  1186. while True:
  1187. try:
  1188. fcntl.flock(handle.fileno(), fcntl.LOCK_EX | fcntl.LOCK_NB)
  1189. except (BlockingIOError, OSError) as exc:
  1190. if not is_advisory_lock_contention(exc):
  1191. logger.warning("Could not acquire %s %s (%s) — deferring rather than "
  1192. "waiting out the %.0fs holder timeout on a non-contention error.",
  1193. description, lock_path, exc, timeout_seconds)
  1194. return None, handle
  1195. if time.monotonic() < deadline:
  1196. time.sleep(poll_seconds)
  1197. continue
  1198. if broke_lock:
  1199. return False, handle
  1200. record = _read_lock_holder_record(handle)
  1201. if not _lock_holder_provably_dead(record):
  1202. return False, handle
  1203. logger.warning("%s %s is held by an orphaned file descriptor (recorded holder pid %s is dead — a "
  1204. "forked child inherited the lock fd); breaking the stale lock and retaking it on a "
  1205. "fresh file.", description, lock_path, (record or {}).get("pid"))
  1206. try:
  1207. os.unlink(lock_path)
  1208. handle.close()
  1209. handle = open(lock_path, "a+b")
  1210. except OSError as exc:
  1211. logger.warning("Could not break stale %s %s (%s) — deferring.", description, lock_path, exc)
  1212. return False, handle
  1213. broke_lock = True
  1214. deadline = time.monotonic() + _LOCK_BREAK_REACQUIRE_SECONDS
  1215. continue
  1216. # A breaker may have replaced the file while we waited; a lock on a dead inode excludes nobody.
  1217. try:
  1218. fd_stat, path_stat = os.fstat(handle.fileno()), os.stat(lock_path)
  1219. same_file = fd_stat.st_dev == path_stat.st_dev and fd_stat.st_ino == path_stat.st_ino
  1220. except OSError:
  1221. same_file = False
  1222. if same_file:
  1223. _write_lock_holder_record(handle)
  1224. return True, handle
  1225. try:
  1226. handle.close()
  1227. handle = open(lock_path, "a+b")
  1228. except OSError:
  1229. return False, handle
  1230. if time.monotonic() >= deadline:
  1231. return False, handle
  1232. def _describe_lock_holder(record) -> str:
  1233. """Human-readable holder identity for deferral warnings."""
  1234. if not isinstance(record, dict) or "pid" not in record:
  1235. return "unknown (no holder record; pre-fix writer or non-Hermes)"
  1236. age = ""
  1237. with contextlib.suppress(TypeError, ValueError):
  1238. if record.get("acquired_at") is not None:
  1239. age = f", acquired {time.time() - float(record['acquired_at']):.0f}s ago"
  1240. return f"pid {record.get('pid')}{age}"
  1241. def _acquire_msvcrt_lock(lock_path, handle, timeout):
  1242. """Windows counterpart of ``_acquire_db_flock`` (no orphan break); same True / False / None contract."""
  1243. import msvcrt
  1244. deadline = time.monotonic() + timeout
  1245. while True:
  1246. try:
  1247. handle.seek(0)
  1248. msvcrt.locking(handle.fileno(), msvcrt.LK_NBLCK, 1)
  1249. return True
  1250. except (BlockingIOError, OSError) as exc:
  1251. if not is_advisory_lock_contention(exc):
  1252. logger.warning("Could not acquire FTS rebuild lock %s (%s) — deferring on a non-contention error.",
  1253. lock_path, exc)
  1254. return None
  1255. if time.monotonic() >= deadline:
  1256. return False
  1257. time.sleep(_FTS_REBUILD_LOCK_POLL_SECONDS)
  1258. @contextlib.contextmanager
  1259. def fts_rebuild_admission(db_path, *, timeout_seconds=None):
  1260. """Serialize full structural FTS rebuilds on *db_path* across processes. Yields True when this process
  1261. holds the authority, False when the bounded acquire timed out or the lock file could not be opened: the
  1262. caller must NOT rebuild (fail closed; the stale breadcrumb guarantees a retry). ``db_path`` None
  1263. (in-memory) yields True. In-process retries pass ``timeout_seconds=0`` so a live holder never stalls a
  1264. long-lived writer; the orphan break still applies."""
  1265. if db_path is None:
  1266. yield True
  1267. return
  1268. timeout = _FTS_REBUILD_LOCK_TIMEOUT_SECONDS if timeout_seconds is None else max(float(timeout_seconds), 0.0)
  1269. lock_path = f"{db_path}.fts_rebuild.lock"
  1270. try:
  1271. handle = open(lock_path, "a+b")
  1272. except OSError as exc:
  1273. # Fail closed like a timed-out acquire: an unopenable lock file means the FS is out of
  1274. # space/inodes/descriptors and a sibling that opened earlier may still be rebuilding — yielding True
  1275. # gave every process on a full disk a concurrent rebuild. Deferring is free: the breadcrumb retries.
  1276. logger.warning("Could not open FTS rebuild lock %s (%s) — deferring this rebuild "
  1277. "rather than running it without cross-process authority.", lock_path, exc)
  1278. yield False
  1279. return
  1280. acquired = False
  1281. try:
  1282. if _IS_WINDOWS:
  1283. acquired = _acquire_msvcrt_lock(lock_path, handle, timeout)
  1284. else:
  1285. acquired, handle = _acquire_db_flock(
  1286. lock_path, handle, timeout, _FTS_REBUILD_LOCK_POLL_SECONDS, "FTS rebuild lock")
  1287. if acquired is None:
  1288. # Already logged with the real errno; "held by another process" would be a lie.
  1289. acquired = False
  1290. elif not acquired:
  1291. record = None if _IS_WINDOWS else _read_lock_holder_record(handle)
  1292. if timeout <= 0:
  1293. # Non-blocking probe from an in-process retry: keep it quiet.
  1294. logger.info("FTS rebuild lock %s is busy — deferring this retry "
  1295. "(the stale-FTS breadcrumb keeps it retryable). Recorded holder: %s.",
  1296. lock_path, _describe_lock_holder(record))
  1297. else:
  1298. logger.warning("FTS rebuild lock %s held by another process for more than %.0fs — deferring "
  1299. "this rebuild to avoid racing the holder (the stale-FTS breadcrumb keeps it "
  1300. "retryable). Recorded holder: %s.", lock_path, timeout, _describe_lock_holder(record))
  1301. yield acquired
  1302. finally:
  1303. try:
  1304. with contextlib.suppress(OSError): # best-effort release
  1305. if acquired and _IS_WINDOWS:
  1306. import msvcrt
  1307. handle.seek(0)
  1308. msvcrt.locking(handle.fileno(), msvcrt.LK_UNLCK, 1)
  1309. elif acquired:
  1310. import fcntl
  1311. _clear_lock_holder_record(handle)
  1312. fcntl.flock(handle.fileno(), fcntl.LOCK_UN)
  1313. finally:
  1314. handle.close()
  1315. def _json_or(raw: Any, fallback: Any, warning: str) -> Any:
  1316. """``json.loads(raw)``; on failure log *warning* and return *fallback*."""
  1317. try:
  1318. return json.loads(raw)
  1319. except (json.JSONDecodeError, TypeError):
  1320. logger.warning(warning)
  1321. return fallback