hermes_state_gateway.py 60 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033
  1. """Gateway-facing SessionDB persistence: routing index, peers, orphans, heartbeats, handoffs.
  2. Mixin bound onto ``SessionDB`` via the MRO; built on its ``_read_ctx`` /
  3. ``_execute_write`` / ``_write_sql`` / ``_read_all`` primitives."""
  4. from __future__ import annotations
  5. import json
  6. import logging
  7. import sys
  8. import time
  9. from pathlib import Path
  10. from typing import Any, Dict, List, Optional, Set, Tuple
  11. from hermes_state_common import (
  12. _RECOVERABLE_END_REASONS_SQL, _RESET_CHILD_SQL, _RESET_END_REASONS_SQL, _sql_json_extract,
  13. _sql_session_last_active)
  14. # Log-record parity with the origin module (caplog tests pin "hermes_state").
  15. logger = logging.getLogger("hermes_state")
  16. # Recursive CTE naming a session plus its compression ancestors (rows a
  17. # resume must keep on one routing peer); branch/delegate/tool/reset rows stop it
  18. # (same membership rule as get_compression_lineage / _CHAIN_STEP_SQL, #114271).
  19. _COMPRESSION_LINEAGE_CTE = f"""
  20. WITH RECURSIVE compression_lineage(id) AS (
  21. SELECT ?
  22. UNION
  23. SELECT parent.id
  24. FROM compression_lineage lineage
  25. JOIN sessions child ON child.id = lineage.id
  26. JOIN sessions parent ON parent.id = child.parent_session_id
  27. WHERE parent.end_reason = 'compression'
  28. AND {_sql_json_extract('child.model_config', '$._branched_from')} IS NULL
  29. AND {_sql_json_extract('child.model_config', '$._delegate_from')} IS NULL
  30. AND NOT ({_RESET_CHILD_SQL.format(a='child')})
  31. AND COALESCE(child.source, '') != 'tool'
  32. )
  33. """
  34. # Projection shared by both peer-recovery queries (exact key, then peer tuple).
  35. _PEER_SELECT_HEAD = """
  36. SELECT s.*,
  37. COALESCE(sp.prompt, s.system_prompt)
  38. AS _system_prompt_resolved,
  39. (COALESCE(s.message_count, 0) > 0 OR EXISTS (
  40. SELECT 1 FROM messages WHERE messages.session_id = s.id LIMIT 1
  41. )) AS _has_messages
  42. FROM sessions s
  43. LEFT JOIN system_prompts sp ON sp.hash = s.system_prompt_hash
  44. """
  45. # A row a *completed handoff* owns stays recoverable whatever end_reason it carries: the source
  46. # interface's teardown runs AFTER ownership moved to the destination and can stamp a terminal reason
  47. # (``cli_close``) before the destination's first reply — the row must survive that race or the
  48. # handed-off leg is orphaned and a fresh empty session is minted in its place. A later DELIBERATE
  49. # close is still fenced the ordinary way: a reset-boundary row ended after this row's last activity
  50. # blocks recovery (the NOT EXISTS below). ``pending``/``failed``/NULL handoffs are untouched, so this
  51. # is strictly "ownership was transferred", never "a handoff was attempted".
  52. _HANDOFF_OWNED_ROW_SQL = "(s.handoff_state = 'completed')"
  53. _PEER_BY_KEY_SQL = f"""{_PEER_SELECT_HEAD} WHERE s.session_key = ?
  54. AND s.source = ?
  55. AND (s.ended_at IS NULL OR s.end_reason IN ({_RECOVERABLE_END_REASONS_SQL})
  56. OR {_HANDOFF_OWNED_ROW_SQL})
  57. AND NOT EXISTS (
  58. SELECT 1 FROM sessions b
  59. WHERE b.session_key = s.session_key
  60. AND b.source = s.source
  61. AND b.ended_at IS NOT NULL
  62. AND b.end_reason IN ({_RESET_END_REASONS_SQL})
  63. AND b.ended_at
  64. > COALESCE(s.last_activity_at, s.started_at)
  65. )
  66. ORDER BY _has_messages DESC,
  67. COALESCE(s.last_activity_at, s.started_at) DESC
  68. LIMIT 1
  69. """
  70. _PEER_BY_TUPLE_SQL = f"""{_PEER_SELECT_HEAD} WHERE s.source = ?
  71. AND COALESCE(s.user_id, '') = COALESCE(?, '')
  72. AND COALESCE(s.chat_id, '') = COALESCE(?, '')
  73. AND COALESCE(s.chat_type, '') = COALESCE(?, '')
  74. AND COALESCE(s.thread_id, '') = COALESCE(?, '')
  75. AND (? IS NULL OR COALESCE(s.profile_name, ?) = ?)
  76. AND (s.ended_at IS NULL OR s.end_reason IN ({_RECOVERABLE_END_REASONS_SQL})
  77. OR {_HANDOFF_OWNED_ROW_SQL})
  78. AND (COALESCE(s.message_count, 0) > 0 OR EXISTS (
  79. SELECT 1 FROM messages WHERE messages.session_id = s.id LIMIT 1
  80. ))
  81. AND NOT EXISTS (
  82. SELECT 1 FROM sessions b
  83. WHERE b.source = s.source
  84. AND COALESCE(b.user_id, '') = COALESCE(s.user_id, '')
  85. AND COALESCE(b.chat_id, '') = COALESCE(s.chat_id, '')
  86. AND COALESCE(b.chat_type, '') = COALESCE(s.chat_type, '')
  87. AND COALESCE(b.thread_id, '') = COALESCE(s.thread_id, '')
  88. AND (? IS NULL OR COALESCE(b.profile_name, ?) = ?)
  89. AND b.ended_at IS NOT NULL
  90. AND b.end_reason IN ({_RESET_END_REASONS_SQL})
  91. AND b.ended_at
  92. > COALESCE(s.last_activity_at, s.started_at)
  93. )
  94. ORDER BY COALESCE(s.last_activity_at, s.started_at) DESC
  95. LIMIT 1
  96. """
  97. _ORPHAN_DONOR_COLUMNS = (
  98. "d.id, d.session_key, d.chat_id, d.chat_type, d.thread_id, "
  99. "d.user_id, d.origin_json, d.display_name, d.end_reason"
  100. )
  101. _ORPHANS_SQL = f"""
  102. SELECT o.id, o.source, o.user_id, o.started_at,
  103. o.parent_session_id, o.profile_name,
  104. {_sql_session_last_active("o")} AS last_active,
  105. (SELECT COUNT(*) FROM messages m
  106. WHERE m.session_id = o.id) AS message_count
  107. FROM sessions o
  108. WHERE o.session_key IS NULL
  109. AND EXISTS (SELECT 1 FROM messages m
  110. WHERE m.session_id = o.id)
  111. AND COALESCE(o.source, '') != 'tool'
  112. AND {_sql_json_extract('o.model_config', '$._branched_from')} IS NULL
  113. AND {_sql_json_extract('o.model_config', '$._delegate_from')} IS NULL
  114. ORDER BY o.started_at ASC
  115. """
  116. # Donor profile fence: the donor's effective profile (NULL reads as the store's own, the same convention
  117. # the peer-tuple recovery predicate uses) must equal the orphan's. A sibling profile's keyed row in a
  118. # shared legacy store would otherwise stamp its ``agent:<other>:`` routing identity onto this profile's
  119. # orphan. Stores outside the profile tree derive no owner and keep the historical unfenced behavior.
  120. _ORPHAN_DONOR_PROFILE_FENCE_SQL = "AND (? IS NULL OR COALESCE(d.profile_name, ?) = ?)"
  121. _ORPHAN_LINEAGE_DONOR_SQL = f"""
  122. SELECT {_ORPHAN_DONOR_COLUMNS}
  123. FROM sessions d
  124. WHERE d.id = ?
  125. AND d.session_key IS NOT NULL
  126. AND COALESCE(d.source, '') = COALESCE(?, '')
  127. {_ORPHAN_DONOR_PROFILE_FENCE_SQL}
  128. """
  129. _ORPHAN_CONTIGUITY_DONORS_SQL = f"""
  130. SELECT {_ORPHAN_DONOR_COLUMNS}, {_sql_session_last_active("d")} AS last_active
  131. FROM sessions d
  132. WHERE d.session_key IS NOT NULL
  133. AND d.id != ?
  134. AND COALESCE(d.source, '') = COALESCE(?, '')
  135. AND (COALESCE(d.user_id, '') = ''
  136. OR COALESCE(?, '') = ''
  137. OR d.user_id = ?)
  138. AND {_sql_session_last_active("d")} BETWEEN ? AND ?
  139. AND {_sql_session_last_active("d")} < ?
  140. {_ORPHAN_DONOR_PROFILE_FENCE_SQL}
  141. ORDER BY last_active DESC
  142. LIMIT 2
  143. """
  144. _OPTIONAL_TABLE_NAMES = (
  145. "telegram_dm_topic_mode", "telegram_dm_topic_bindings", "delivery_obligations")
  146. def _optional_table_columns(conn) -> dict[str, set[str]]:
  147. """Live column sets of the lazily-created tables that exist (``{}`` when none do).
  148. ``apply_telegram_topic_migration`` runs only on explicit ``/topic`` opt-in, so a store
  149. can hold supported v1/v2 tables without ``profile_name`` for its whole life; likewise the
  150. delivery ledger adds ``delivery_obligations.adapter_profile`` only when a gateway opens
  151. it. Identity settlement must gate its column SQL on the column actually being there
  152. rather than on table existence, and must not force those migrations (#113757).
  153. """
  154. existing = {row[0] for row in conn.execute(
  155. "SELECT name FROM sqlite_master WHERE type='table' AND name IN (?, ?, ?)",
  156. _OPTIONAL_TABLE_NAMES)}
  157. return {
  158. table: {row[1] for row in conn.execute(f"PRAGMA table_info('{table}')")}
  159. for table in _OPTIONAL_TABLE_NAMES if table in existing
  160. }
  161. _HANDOFF_FAIL_SQL = "UPDATE sessions SET handoff_state = 'failed', handoff_error = ? WHERE "
  162. class SessionGatewayMixin:
  163. """Routing index, session peers/orphans, hygiene streaks, heartbeats, handoffs."""
  164. def _reap_inactive_orphan_desktop_holders(
  165. self, holders: list[tuple[int, str]], *, min_age_seconds: float) -> list[int]:
  166. """Terminate old PPID-1 Desktop ephemeral backends with no client.
  167. Fails closed: anything whose parent, age, argv, or network connections
  168. cannot be proved safe remains a repair-blocking holder."""
  169. from hermes_state import psutil
  170. from hermes_state_dbfile import _concrete_state_db_holder_pids, _is_inactive_orphan_desktop_holder
  171. if not sys.platform.startswith("linux") or psutil is None:
  172. return []
  173. try:
  174. from hermes_cli.dashboard_procs import _is_ephemeral_port_zero_backend
  175. except Exception:
  176. return []
  177. now = time.time()
  178. candidates = []
  179. for pid in _concrete_state_db_holder_pids(self.db_path, holders):
  180. try:
  181. process = psutil.Process(pid)
  182. statuses = [conn.status for conn in process.net_connections(kind="inet")]
  183. if not _is_inactive_orphan_desktop_holder(
  184. ppid=process.ppid(), age_seconds=now - process.create_time(),
  185. min_age_seconds=min_age_seconds,
  186. ephemeral_backend=_is_ephemeral_port_zero_backend(process.cmdline()),
  187. connection_statuses=statuses):
  188. continue
  189. except Exception:
  190. continue
  191. candidates.append(process)
  192. signalled: list[int] = []
  193. for process in candidates:
  194. try:
  195. process.terminate()
  196. signalled.append(process.pid)
  197. except (psutil.Error, OSError):
  198. continue
  199. if not signalled:
  200. return []
  201. try:
  202. _gone, alive = psutil.wait_procs(candidates, timeout=1.5)
  203. except Exception:
  204. alive = []
  205. for process in alive:
  206. try:
  207. process.kill()
  208. except (psutil.Error, OSError):
  209. continue
  210. if alive:
  211. try:
  212. psutil.wait_procs(alive, timeout=1.5)
  213. except Exception:
  214. pass
  215. return signalled
  216. def record_gateway_session_peer(
  217. self, session_id: str, *, source: str, user_id: str | None = None, session_key: str | None = None,
  218. chat_id: str | None = None, chat_type: str | None = None, thread_id: str | None = None, display_name: str | None = None,
  219. origin_json: str | None = None, include_compression_ancestors: bool = False,
  220. transport_profile: str | None = None) -> None:
  221. """Persist the gateway routing peer for an existing session row. ``display_name`` / ``origin_json``:
  222. ``None`` leaves the stored value untouched (consumers read routing data from state.db, not
  223. sessions.json). ``include_compression_ancestors`` keeps a compression lineage on one routing peer
  224. when an explicit resume moves its tip to another lane; per-turn refreshes update only the supplied
  225. row. Self-healing: a missing target row (deferred ``create_session`` write, or crash between routing
  226. publication and row creation) is INSERTed with full identity rather than no-opped, so a gateway row
  227. is never first-created by the identity-less lazy writer (``update_token_counts``) and left
  228. unroutable forever.
  229. See #9006.
  230. """
  231. if not session_id or not session_key:
  232. return
  233. identity = (
  234. session_key, source, user_id, chat_id, chat_type, thread_id, display_name, origin_json,
  235. transport_profile)
  236. ancestors = include_compression_ancestors
  237. query_params = [session_id, *identity] if ancestors else [*identity, session_id]
  238. def _do(conn):
  239. conn.execute(
  240. f"""{_COMPRESSION_LINEAGE_CTE if ancestors else ""}
  241. UPDATE sessions
  242. SET session_key = ?, source = ?, user_id = ?, chat_id = ?,
  243. chat_type = ?, thread_id = ?,
  244. display_name = COALESCE(?, display_name),
  245. origin_json = COALESCE(?, origin_json),
  246. transport_profile = COALESCE(?, transport_profile)
  247. {"WHERE id IN (SELECT id FROM compression_lineage)" if ancestors else "WHERE id = ?"}""",
  248. query_params,
  249. )
  250. if ancestors:
  251. # An explicit repoint moved these rows to this lane: the local conversation whose receipt
  252. # names one of them goes with it in the same write, or it would match neither lane (#143286).
  253. from gateway.session_route_recovery import _messaging_platform
  254. from hermes_state_local import hand_off_conversations, moved_receipts
  255. moved = {row[0] for row in conn.execute(
  256. f"{_COMPRESSION_LINEAGE_CTE} SELECT id FROM compression_lineage", (session_id,))}
  257. hand_off_conversations(conn, {owner: found for owner, found in moved_receipts(conn, moved).items()
  258. if _messaging_platform(found[1]) is not None})
  259. return
  260. # The UPDATE silently no-ops on a missing row — insert it with full identity.
  261. if conn.execute("SELECT 1 FROM sessions WHERE id = ? LIMIT 1", (session_id,)).fetchone() is None:
  262. conn.execute(
  263. """INSERT INTO sessions (
  264. id, source, created_source, user_id, session_key, chat_id,
  265. chat_type, thread_id, display_name, origin_json,
  266. profile_name, transport_profile, started_at
  267. )
  268. VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)
  269. ON CONFLICT(id) DO UPDATE SET
  270. session_key = COALESCE(sessions.session_key, excluded.session_key),
  271. chat_id = COALESCE(sessions.chat_id, excluded.chat_id),
  272. chat_type = COALESCE(sessions.chat_type, excluded.chat_type),
  273. thread_id = COALESCE(sessions.thread_id, excluded.thread_id),
  274. display_name = COALESCE(sessions.display_name, excluded.display_name),
  275. origin_json = COALESCE(sessions.origin_json, excluded.origin_json),
  276. transport_profile = COALESCE(sessions.transport_profile, excluded.transport_profile),
  277. created_source = COALESCE(sessions.created_source, excluded.created_source)""",
  278. # Same ownership stamp as _insert_session_row: an unowned (NULL) row
  279. # vanishes from profile-keyed consumers.
  280. (session_id, source, source, user_id, session_key, chat_id, chat_type, thread_id, display_name,
  281. origin_json, self._own_profile_name(), transport_profile, time.time()),
  282. )
  283. self._execute_write(_do)
  284. def save_gateway_routing_entry(self, session_key: str, entry_json: str, *, scope: str = "") -> None:
  285. """Upsert one gateway routing entry (session_key -> SessionEntry JSON); ``scope``
  286. namespaces the index per sessions_dir so two stores never share routing state."""
  287. if not session_key or not entry_json:
  288. return
  289. from hermes_state_mutation_retirement import ROUTE_RETIRED_SQL, route_retired_params
  290. self._write_sql(
  291. f"""INSERT INTO gateway_routing (scope, session_key, entry_json, updated_at)
  292. SELECT :scope, :key, :entry, :now WHERE NOT {ROUTE_RETIRED_SQL}
  293. ON CONFLICT(scope, session_key) DO UPDATE SET
  294. entry_json = excluded.entry_json,
  295. updated_at = excluded.updated_at""",
  296. {'scope': scope, 'key': session_key, **route_retired_params(session_key, entry_json, time.time())},
  297. )
  298. def replace_gateway_routing_entries(self, entries: dict[str, str], *, scope: str = "") -> set[str]:
  299. """Atomically replace the routing index for *scope* (keys absent from *entries*
  300. are removed); other scopes untouched. Returns the keys skipped as retired routes, which
  301. the sessions.json mirror must leave out too."""
  302. from hermes_state_mutation_retirement import ROUTE_RETIRED_SQL, route_retired_params
  303. now = time.time()
  304. rows = [{'scope': scope, 'key': k, **route_retired_params(k, v, now)} for k, v in entries.items() if k and v]
  305. def _do(conn):
  306. conn.execute("DELETE FROM gateway_routing WHERE scope = ?", (scope,))
  307. if rows:
  308. conn.executemany(
  309. "INSERT INTO gateway_routing (scope, session_key, entry_json, updated_at) "
  310. "SELECT :scope, :key, :entry, :now WHERE NOT " + ROUTE_RETIRED_SQL,
  311. rows)
  312. kept = {r[0] for r in conn.execute("SELECT session_key FROM gateway_routing WHERE scope = ?", (scope,))}
  313. return {row['key'] for row in rows} - kept
  314. return self._execute_write(_do)
  315. def load_gateway_routing_entries(self, *, scope: str = "") -> dict[str, str]:
  316. """Load routing entries for *scope* as {session_key: entry_json}."""
  317. rows = self._read_all("SELECT session_key, entry_json FROM gateway_routing WHERE scope = ?", (scope,))
  318. return {r["session_key"]: r["entry_json"] for r in rows}
  319. def list_never_active_keyed_sessions(self, *, older_than_days: float) -> list[dict[str, Any]]:
  320. """Keyed, still-open rows with no evidence of a single turn (no messages, tokens, tool/API calls,
  321. activity, or title): leaked fixtures or chats routed but never answered. Safe to drop — the gateway
  322. mints a fresh session on the next message. Needs its own selector because ``bulk prune``/``archive``
  323. are pinned to ``ended_at IS NOT NULL``. ``pinned``/``archived`` = explicit keep intent.
  324. That is exactly the shape of a leaked test fixture (#82770) — and also of a chat that was routed but
  325. never answered.
  326. """
  327. if older_than_days < 0:
  328. raise ValueError(
  329. f"older_than_days must be >= 0, got {older_than_days!r}: a negative "
  330. "retention builds a future cutoff that matches every never-active keyed row.")
  331. cutoff = time.time() - (float(older_than_days) * 86400.0)
  332. rows = self._read_all(
  333. """
  334. SELECT s.id, s.session_key, s.source, s.chat_id,
  335. s.chat_type, s.user_id, s.started_at
  336. FROM sessions s
  337. WHERE s.session_key IS NOT NULL
  338. AND s.ended_at IS NULL
  339. AND s.title IS NULL
  340. AND s.last_activity_at IS NULL
  341. AND COALESCE(s.message_count, 0) = 0
  342. AND COALESCE(s.tool_call_count, 0) = 0
  343. AND COALESCE(s.api_call_count, 0) = 0
  344. AND COALESCE(s.input_tokens, 0) = 0
  345. AND COALESCE(s.output_tokens, 0) = 0
  346. AND COALESCE(s.pinned, 0) = 0
  347. AND COALESCE(s.archived, 0) = 0
  348. AND s.started_at IS NOT NULL
  349. AND s.started_at < ?
  350. AND NOT EXISTS (
  351. SELECT 1 FROM messages m WHERE m.session_id = s.id
  352. )
  353. ORDER BY s.started_at
  354. """,
  355. (cutoff,),
  356. )
  357. return [dict(r) for r in rows]
  358. def gateway_routing_entry_for_session(self, session_id: str) -> Optional[dict[str, Any]]:
  359. """The routing entry (any scope) whose current owner is *session_id*, or None. The id lives
  360. only inside ``entry_json``, so matching is done in Python; an archived/rotated row has none."""
  361. for row in self._read_all("SELECT entry_json FROM gateway_routing"):
  362. try:
  363. entry = json.loads(row["entry_json"] or "{}")
  364. except (ValueError, TypeError, RecursionError): # malformed / non-text / over-nested entry
  365. continue
  366. if isinstance(entry, dict) and entry.get("session_id") == session_id:
  367. return entry
  368. return None
  369. def _routing_entries_for_sessions(self, session_ids: set[str]) -> list[tuple[str, str, str]]:
  370. """``(scope, session_key, session_id)`` of ``gateway_routing`` rows pointing at any of
  371. *session_ids*; the target id lives only inside ``entry_json``, so matching is in Python."""
  372. found: list[tuple[str, str, str]] = []
  373. if not session_ids:
  374. return found
  375. for row in self._read_all("SELECT scope, session_key, entry_json FROM gateway_routing"):
  376. try:
  377. entry = json.loads(row["entry_json"] or "{}")
  378. except (ValueError, TypeError, RecursionError): # malformed / non-text / over-nested entry
  379. continue
  380. if isinstance(entry, dict) and entry.get("session_id") in session_ids:
  381. found.append((row["scope"], row["session_key"], entry["session_id"]))
  382. return found
  383. def _delete_routing_entries_for_sessions(self, session_ids: set[str]) -> int:
  384. """Drop ``gateway_routing`` rows pointing at any of *session_ids*."""
  385. doomed = [(scope, key) for scope, key, _ in self._routing_entries_for_sessions(session_ids)]
  386. if not doomed:
  387. return 0
  388. self._write_sql("DELETE FROM gateway_routing WHERE scope = ? AND session_key = ?", doomed, many=True)
  389. return len(doomed)
  390. def retire_gateway_routes(self, session_ids, *, namespace, expiring=()) -> None:
  391. """Publish routing fences for *session_ids*, retired in the profile whose routing keys use
  392. *namespace*, in THIS (routing) database and drop that profile's routes naming them, in one
  393. transaction (``fence_shared_routes``). Routing saves skip a fenced route, so a whole-index
  394. snapshot taken before a delete in another profile's store cannot restore it when it is
  395. saved after it. The fence is not this store's own deletion marker: this profile's creates,
  396. binds and generation walks stay free. *expiring* ids (a sweep's) age out at the horizon."""
  397. ids = sorted({str(sid) for sid in session_ids if sid})
  398. if not ids:
  399. return
  400. def _do(conn):
  401. from hermes_state_mutation_retirement import fence_shared_routes
  402. fence_shared_routes(conn, namespace, ids, expiring=expiring)
  403. self._execute_write(_do)
  404. def prune_never_active_keyed_sessions(
  405. self, *, older_than_days: float, sessions_dir: Optional[Path] = None,
  406. deleted_ids: Optional[list] = None) -> tuple[int, int, int]:
  407. """Delete never-active keyed rows and the routing entries naming them; returns
  408. ``(sessions_deleted, routing_entries_deleted, sessions_skipped)``. Deletion goes through
  409. :meth:`delete_sessions` (one transaction; delegate cascade, FTS, transcripts).
  410. Rows under a live turn lease or compression lock are skipped (#123583): the
  411. never-active predicate reads committed state, so a keyed row whose first turn lease
  412. is already held — its messages not yet flushed — would otherwise be deleted
  413. mid-turn. Routing entries are dropped only after the rows commit, for every
  414. candidate except the guarded ones: rows that vanished concurrently are gone too,
  415. and a stale entry outliving its target would have the gateway resume a
  416. nonexistent id. The row sweep is one transaction; routing entries are dropped in a
  417. second write after it commits. ``sessions_skipped`` counts only the guarded rows.
  418. ``deleted_ids`` (the owning gateway) receives every candidate id that was not kept."""
  419. candidates = self.list_never_active_keyed_sessions(older_than_days=older_than_days)
  420. # A fresh local reset tip is its conversation's current target, not a never-active chat:
  421. # the conversation holds the earlier segments' history and the owner's queued work.
  422. from hermes_state_local import owned_lineage_ids
  423. with self._read_ctx() as conn:
  424. ids = {str(row["id"]) for row in candidates if owned_lineage_ids(conn, str(row["id"])) == [str(row["id"])]}
  425. if not ids:
  426. return (0, 0, 0)
  427. skipped: list[str] = []
  428. # ``delete_sessions`` retires the deleted rows' routing entries inside its own transaction
  429. # (``retire_routes``), so count what pointed at the candidates before it commits; the
  430. # follow-up sweep then only catches entries a concurrent writer re-added.
  431. routed_before = self._routing_entries_for_sessions(ids)
  432. deleted = self.delete_sessions(
  433. list(ids), sessions_dir=sessions_dir, exclude_active_write_guards=True, skipped_ids=skipped)
  434. kept = set(skipped)
  435. self._delete_routing_entries_for_sessions(ids - kept)
  436. routing_deleted = sum(1 for _, _, sid in routed_before if sid not in kept)
  437. if deleted_ids is not None:
  438. deleted_ids.extend(sorted(ids - kept))
  439. return (deleted, routing_deleted, len(skipped))
  440. def list_gateway_sessions(
  441. self, *, platform: Optional[str] = None, active_only: bool = True) -> list[dict[str, Any]]:
  442. """List gateway sessions (rows with a session_key): newest row per key, one live
  443. mapping per routing key. ``platform`` filters on ``source``."""
  444. # Full rows carry token/cost totals — drain queued async accounting deltas first.
  445. self.flush_for_read()
  446. query = f"""
  447. SELECT sessions.*,
  448. COALESCE(sp.prompt, sessions.system_prompt)
  449. AS _system_prompt_resolved,
  450. {_sql_session_last_active("sessions")} AS last_active
  451. FROM sessions
  452. LEFT JOIN system_prompts sp
  453. ON sp.hash = sessions.system_prompt_hash
  454. WHERE session_key IS NOT NULL
  455. AND started_at = (
  456. SELECT MAX(s2.started_at) FROM sessions s2
  457. WHERE s2.session_key = sessions.session_key
  458. )
  459. """
  460. params: list = [platform] if platform else []
  461. query += (" AND LOWER(source) = LOWER(?)" if platform else "") + (
  462. " AND ended_at IS NULL" if active_only else "") + " ORDER BY last_active DESC"
  463. return [self._session_row_dict(r) for r in self._read_all(query, params)]
  464. def find_latest_gateway_session_for_peer(
  465. self, *, source: str, user_id: Optional[str] = None, session_key: Optional[str] = None,
  466. chat_id: Optional[str] = None, chat_type: Optional[str] = None, thread_id: Optional[str] = None,
  467. ) -> Optional[dict[str, Any]]:
  468. """Find the latest recoverable gateway session for a routing peer. The durable ``session_key`` on the row rebuilds a missing/pruned ``sessions.json`` mapping. Rows
  469. ended only by the old ``agent_close`` bug or a mistaken TUI ``ws_orphan_reap`` are recoverable;
  470. explicit boundaries (/new, /resume switches, compression splits) are not. Ranked by
  471. ``last_activity_at`` (fallback ``started_at`` — alone it resurrected days-old zombies); rows with
  472. messages win, but an empty keyed row still beats ``None`` (which mints a new id while the transcript
  473. may live under a compression child). Reset fence: a candidate is rejected when a peer boundary row
  474. ended *after* its last activity, or the has-messages ranking could reach behind a /new. The
  475. exact-key fallback requires the complete peer tuple (never cross chats/threads/users) plus a profile
  476. fence: a Telegram DM's tuple is identical for every bot, so a sibling profile's legacy row would
  477. otherwise be adopted. Ours = profile_name is the owner or NULL; stores outside the profile tree
  478. derive no owner and stay unfenced.
  479. See #60609.
  480. Reset boundaries fence recovery (#68539): an intentional boundary such as ``session_reset`` (or any
  481. explicit non-recoverable end_reason) must block fallback to an *older* row for the same peer. Each
  482. candidate is therefore rejected when a boundary row for the peer ended *after* the candidate's last
  483. activity — if the conversation's most recent event is an intentional reset, recovery returns nothing
  484. rather than reaching behind it. The boundary row carries the same profile predicate as the
  485. candidate: a sibling profile's reset must not suppress this profile's recovery.
  486. """
  487. if not session_key:
  488. return None
  489. with self._read_ctx() as conn:
  490. row = conn.execute(_PEER_BY_KEY_SQL, (session_key, source)).fetchone()
  491. if row is not None:
  492. return self._session_row_dict(row)
  493. if chat_id is None or chat_type is None:
  494. return None
  495. # Profile fence (#74285): a Telegram DM's peer tuple is identical for every bot (chat_id ==
  496. # user_id, no thread), so a sibling profile's row written into this store before the per-profile
  497. # partition (legacy data) would otherwise be adopted here. Every profile-tree store has one
  498. # owner; a row is ours when its profile_name is the owner or NULL (legacy rows this store
  499. # minted). Stores outside the tree derive no owner and keep the historical unfenced behavior.
  500. owner = self._own_profile_name()
  501. row = conn.execute(
  502. _PEER_BY_TUPLE_SQL,
  503. (source, user_id, chat_id, chat_type, thread_id, owner, owner, owner, owner, owner, owner),
  504. ).fetchone()
  505. return self._session_row_dict(row) if row else None
  506. def find_orphaned_gateway_sessions(self, *, max_gap_s: Optional[float] = None) -> list[dict[str, Any]]:
  507. """Report message-bearing rows that lost their routing identity (messages, no ``session_key``).
  508. Adoptable only when exactly one keyed predecessor can be named: ``lineage`` (``parent_session_id``
  509. is a keyed row of the same source; no time window) or ``contiguity`` (exactly one keyed same-source
  510. row with compatible ``user_id`` fell quiet within *max_gap_s* of the orphan's start and is older
  511. than its last activity). Ambiguity is reported ``adoptable=False`` with a reason, never guessed —
  512. mis-adopting splices one person's conversation into another's chat. The donor must share the
  513. orphan's effective profile (NULL reads as this store's own): a sibling profile's keyed row in a
  514. shared legacy store must never stamp its routing identity here. Branch/delegate/tool rows are
  515. excluded: unkeyed by design, not damage."""
  516. gap = self._ORPHAN_ADOPTION_MAX_GAP_S if max_gap_s is None else float(max_gap_s)
  517. owner = self._own_profile_name()
  518. records: list[dict[str, Any]] = []
  519. with self._read_ctx() as conn:
  520. for orphan in conn.execute(_ORPHANS_SQL).fetchall():
  521. donor = None
  522. reason = ""
  523. orphan_owner = orphan["profile_name"] or owner
  524. if orphan["parent_session_id"]:
  525. evidence = "lineage"
  526. donor = conn.execute(
  527. _ORPHAN_LINEAGE_DONOR_SQL,
  528. (orphan["parent_session_id"], orphan["source"], owner, owner, orphan_owner),
  529. ).fetchone()
  530. if donor is None:
  531. reason = "parent session carries no gateway identity of this source"
  532. else:
  533. evidence = "contiguity"
  534. started = orphan["started_at"] or 0
  535. candidates = conn.execute(
  536. _ORPHAN_CONTIGUITY_DONORS_SQL,
  537. (orphan["id"], orphan["source"], orphan["user_id"], orphan["user_id"],
  538. started - gap, started + gap, orphan["last_active"],
  539. owner, owner, orphan_owner),
  540. ).fetchall()
  541. if not candidates:
  542. reason = f"no keyed predecessor fell quiet within {gap:.0f}s of this session's start"
  543. elif len(candidates) > 1:
  544. reason = "ambiguous: more than one keyed predecessor matches this window"
  545. else:
  546. donor = candidates[0]
  547. records.append({
  548. "orphan_id": orphan["id"], "source": orphan["source"],
  549. "message_count": orphan["message_count"], "started_at": orphan["started_at"],
  550. "last_active": orphan["last_active"], "donor_id": donor["id"] if donor else None,
  551. "session_key": donor["session_key"] if donor else None,
  552. "evidence": evidence if donor else "", "adoptable": donor is not None, "reason": reason})
  553. # Two unkeyed successors claiming one predecessor: at most one continues that chat.
  554. contested = {
  555. r["donor_id"] for r in records
  556. if r["adoptable"] and sum(1 for x in records if x["donor_id"] == r["donor_id"]) > 1
  557. }
  558. for record in records:
  559. if record["donor_id"] in contested:
  560. record["adoptable"] = False
  561. record["reason"] = "ambiguous: more than one unkeyed session claims this predecessor"
  562. return records
  563. def adopt_orphaned_gateway_session(self, orphan_id: str, donor_id: str) -> bool:
  564. """Stamp *orphan_id* with *donor_id*'s routing identity, retire *donor_id*.
  565. Re-verifies the pair inside the write txn so a concurrent gateway that healed
  566. either row makes this a no-op. Non-NULL orphan columns are preserved."""
  567. if not orphan_id or not donor_id or orphan_id == donor_id:
  568. return False
  569. owner = self._own_profile_name()
  570. def _do(conn):
  571. donor = conn.execute(
  572. "SELECT session_key, chat_id, chat_type, thread_id, user_id, "
  573. "origin_json, display_name, source, profile_name FROM sessions WHERE id = ?",
  574. (donor_id,),
  575. ).fetchone()
  576. orphan = conn.execute(
  577. "SELECT session_key, source, profile_name FROM sessions WHERE id = ?",
  578. (orphan_id,)).fetchone()
  579. if (donor is None or orphan is None or not donor["session_key"] or orphan["session_key"]
  580. or (donor["source"] or "") != (orphan["source"] or "")
  581. or (owner is not None
  582. and (donor["profile_name"] or owner) != (orphan["profile_name"] or owner))):
  583. return False
  584. # Belt-and-suspenders for gateway routing metadata (#59527): the gateway re-records the peer on
  585. # the child after rotation (d5b4879d4), but a hard crash between child creation and that write
  586. # leaves the child row without origin columns, so ``find_latest_gateway_session_for_peer`` can't
  587. # recover the mapping on restart. Inherit them from the parent at creation time — but ONLY for
  588. # compression forks (parent already ended with end_reason='compression'). Delegate/subagent
  589. # children are spawned while the parent is still live and must NOT inherit routing keys, or peer
  590. # recovery could repoint gateway traffic into a subagent's session.
  591. conn.execute(
  592. """UPDATE sessions
  593. SET session_key = ?,
  594. chat_id = COALESCE(chat_id, ?),
  595. chat_type = COALESCE(chat_type, ?),
  596. thread_id = COALESCE(thread_id, ?),
  597. user_id = COALESCE(user_id, ?),
  598. origin_json = COALESCE(origin_json, ?),
  599. display_name = COALESCE(display_name, ?),
  600. parent_session_id = COALESCE(parent_session_id, ?)
  601. WHERE id = ? AND session_key IS NULL""",
  602. (donor["session_key"], donor["chat_id"], donor["chat_type"], donor["thread_id"],
  603. donor["user_id"], donor["origin_json"], donor["display_name"], donor_id, orphan_id),
  604. )
  605. # Retire under a reason recovery does NOT treat as resumable — 'agent_close' /
  606. # 'ws_orphan_reap' would keep it in the running and the orphan could lose the chat again.
  607. conn.execute(
  608. "UPDATE sessions SET ended_at = COALESCE(ended_at, ?), "
  609. "end_reason = 'superseded_by_repair' WHERE id = ?",
  610. (time.time(), donor_id))
  611. return True
  612. return self._execute_write(_do)
  613. def increment_hygiene_failure_streak(self, session_key: str) -> int:
  614. """Atomically increment the session-hygiene failure streak for one chat."""
  615. if not session_key:
  616. return 1
  617. def _do(conn):
  618. conn.execute(
  619. """INSERT INTO gateway_hygiene_state (session_key, failure_streak)
  620. VALUES (?, 1)
  621. ON CONFLICT(session_key) DO UPDATE SET
  622. failure_streak = gateway_hygiene_state.failure_streak + 1""",
  623. (session_key,),
  624. )
  625. row = conn.execute(
  626. "SELECT failure_streak FROM gateway_hygiene_state WHERE session_key = ?", (session_key,),
  627. ).fetchone()
  628. return int(row[0])
  629. return self._execute_write(_do)
  630. def reset_hygiene_failure_streak(self, session_key: str) -> None:
  631. """Clear the persisted session-hygiene failure streak for one chat."""
  632. if not session_key:
  633. return
  634. self._write_sql("DELETE FROM gateway_hygiene_state WHERE session_key = ?", (session_key,))
  635. def rekey_profile_state(self, old_name: str, new_name: str) -> dict[str, int]:
  636. """Atomically rewrite exact profile identity in this state database."""
  637. old, new = (old_name or "").strip(), (new_name or "").strip()
  638. counts: dict[str, int] = {}
  639. if not old or not new or old == new:
  640. return counts
  641. old_ns, new_ns = f"agent:{old}:", f"agent:{new}:"
  642. ns_len = len(old_ns)
  643. def _do(conn):
  644. existing = {row[0] for row in conn.execute(
  645. "SELECT name FROM sqlite_master WHERE type='table'").fetchall()}
  646. topic_columns = _optional_table_columns(conn)
  647. collision = conn.execute(
  648. "SELECT old.scope, ? || substr(old.session_key, ?) "
  649. "FROM gateway_routing AS old JOIN gateway_routing AS target "
  650. "ON target.scope = old.scope "
  651. "AND target.session_key = ? || substr(old.session_key, ?) "
  652. "WHERE substr(old.session_key, 1, ?) = ? LIMIT 1",
  653. (new_ns, ns_len + 1, new_ns, ns_len + 1, ns_len, old_ns),
  654. ).fetchone()
  655. if collision is not None:
  656. raise ValueError(
  657. f"profile routing collision in scope {collision[0]!r}: {collision[1]!r}")
  658. for table, columns in (
  659. ("telegram_dm_topic_mode", ("chat_id",)),
  660. ("telegram_dm_topic_bindings", ("chat_id", "thread_id")),
  661. ):
  662. if "profile_name" not in topic_columns.get(table, set()):
  663. continue
  664. equality = " AND ".join(
  665. f"target.{column} = old.{column}" for column in columns)
  666. collision = conn.execute(
  667. f"SELECT 1 FROM {table} AS old JOIN {table} AS target "
  668. f"ON target.profile_name = ? AND {equality} "
  669. "WHERE old.profile_name = ? LIMIT 1", (new, old)).fetchone()
  670. if collision is not None:
  671. raise ValueError(f"profile identity collision in {table}")
  672. counts["sessions_profile_name"] = conn.execute(
  673. "UPDATE sessions SET profile_name = ? WHERE profile_name = ?", (new, old)).rowcount
  674. counts["gateway_heartbeats_profile"] = conn.execute(
  675. "UPDATE gateway_heartbeats SET profile = ? WHERE profile = ?", (new, old)).rowcount
  676. counts["sessions_session_key"] = conn.execute(
  677. "UPDATE sessions SET session_key = ? || substr(session_key, ?) "
  678. "WHERE substr(session_key, 1, ?) = ?",
  679. (new_ns, ns_len + 1, ns_len, old_ns)).rowcount
  680. origin_count = 0
  681. for session_id, origin_json in conn.execute(
  682. "SELECT id, origin_json FROM sessions WHERE origin_json IS NOT NULL").fetchall():
  683. try:
  684. payload = json.loads(origin_json)
  685. except (ValueError, TypeError):
  686. continue
  687. if isinstance(payload, dict) and payload.get("profile") == old:
  688. payload["profile"] = new
  689. conn.execute("UPDATE sessions SET origin_json = ? WHERE id = ?",
  690. (json.dumps(payload, ensure_ascii=False), session_id))
  691. origin_count += 1
  692. counts["sessions_origin_json"] = origin_count
  693. if "delivery_obligations" in existing:
  694. if "adapter_profile" in topic_columns.get("delivery_obligations", set()):
  695. counts["delivery_obligations_adapter_profile"] = conn.execute(
  696. "UPDATE delivery_obligations SET adapter_profile = ? WHERE adapter_profile = ?",
  697. (new, old)).rowcount
  698. counts["delivery_obligations_session_key"] = conn.execute(
  699. "UPDATE delivery_obligations SET session_key = ? || substr(session_key, ?) "
  700. "WHERE substr(session_key, 1, ?) = ?",
  701. (new_ns, ns_len + 1, ns_len, old_ns)).rowcount
  702. for table in ("telegram_dm_topic_mode", "telegram_dm_topic_bindings"):
  703. if "profile_name" in topic_columns.get(table, set()):
  704. counts[f"{table}_profile_name"] = conn.execute(
  705. f"UPDATE {table} SET profile_name = ? WHERE profile_name = ?",
  706. (new, old)).rowcount
  707. if "session_key" in topic_columns.get("telegram_dm_topic_bindings", set()):
  708. counts["telegram_dm_topic_bindings_session_key"] = conn.execute(
  709. "UPDATE telegram_dm_topic_bindings "
  710. "SET session_key = ? || substr(session_key, ?) "
  711. "WHERE substr(session_key, 1, ?) = ?",
  712. (new_ns, ns_len + 1, ns_len, old_ns)).rowcount
  713. routing = conn.execute(
  714. "SELECT rowid, session_key, entry_json FROM gateway_routing "
  715. "WHERE substr(session_key, 1, ?) = ?", (ns_len, old_ns)).fetchall()
  716. for rowid, session_key, entry_json in routing:
  717. new_session_key = new_ns + session_key[ns_len:]
  718. new_json = entry_json
  719. if entry_json:
  720. try:
  721. payload = json.loads(entry_json)
  722. except (ValueError, TypeError):
  723. payload = None
  724. if isinstance(payload, dict):
  725. if isinstance(payload.get("session_key"), str) and payload["session_key"].startswith(old_ns):
  726. payload["session_key"] = new_ns + payload["session_key"][ns_len:]
  727. origin = payload.get("origin")
  728. if isinstance(origin, dict) and origin.get("profile") == old:
  729. origin["profile"] = new
  730. new_json = json.dumps(payload, ensure_ascii=False)
  731. conn.execute(
  732. "UPDATE gateway_routing SET session_key = ?, entry_json = ? WHERE rowid = ?",
  733. (new_session_key, new_json, rowid))
  734. counts["gateway_routing"] = len(routing)
  735. self._execute_write(_do)
  736. return counts
  737. def purge_profile_state(self, profile: str) -> dict[str, int]:
  738. """Delete exact profile identity from this state database (#111926, delete side).
  739. The mirror of :meth:`rekey_profile_state`: a rename must rekey a profile's identity, a
  740. delete must purge it. ``agent:<name>:*`` routing keys, ``gateway_heartbeats.profile``,
  741. ``delivery_obligations`` and the telegram topic tables are bookkeeping for a profile that
  742. no longer exists — left behind, every inbound event on a chat keyed to the deleted name
  743. enters the routing index, resolves a profile whose directory is gone, and logs
  744. ``Profile '<name>' does not exist`` on each event for the life of the store.
  745. What each store gets, and why:
  746. * Routing keys and heartbeat rows are hard-deleted — pure bookkeeping for a dead name.
  747. * ``delivery_obligations`` rows are **terminalized** (``state='abandoned'``), not deleted:
  748. a pending obligation is delivery state that should not vanish silently, and the ledger's
  749. own retention prunes abandoned rows. Delivered history is left as it was.
  750. * ``sessions`` rows are not deleted here: this helper settles identity, not history, and it
  751. does not decide what a delete leaves of a profile's conversation record — ``delete_profile``
  752. removes the profile's own home, ``state.db`` included, with the directory. Rows in a shared
  753. store keep whatever ownership they had; re-binding or archiving them belongs to the flow
  754. that recreates the name, not to this purge.
  755. Idempotent.
  756. """
  757. name = (profile or "").strip()
  758. counts: dict[str, int] = {}
  759. if not name:
  760. return counts
  761. ns, ns_len = f"agent:{name}:", len(f"agent:{name}:")
  762. def _do(conn):
  763. existing = {row[0] for row in conn.execute(
  764. "SELECT name FROM sqlite_master WHERE type='table'").fetchall()}
  765. topic_columns = _optional_table_columns(conn)
  766. if "gateway_routing" in existing:
  767. counts["gateway_routing"] = conn.execute(
  768. "DELETE FROM gateway_routing WHERE substr(session_key, 1, ?) = ?",
  769. (ns_len, ns)).rowcount
  770. if "gateway_heartbeats" in existing:
  771. counts["gateway_heartbeats"] = conn.execute(
  772. "DELETE FROM gateway_heartbeats WHERE profile = ?", (name,)).rowcount
  773. if "delivery_obligations" in existing:
  774. # Terminalize, never hard-delete: a pending obligation is delivery state someone may
  775. # still care about, and the ledger's own retention prunes abandoned rows. Only
  776. # non-terminal rows are touched — delivered history is left exactly as it was.
  777. # A ledger created before ``adapter_profile`` existed matches on namespace alone.
  778. by_profile = ("adapter_profile = ? OR "
  779. if "adapter_profile" in topic_columns.get("delivery_obligations", set())
  780. else "")
  781. params = (time.time(), name, ns_len, ns) if by_profile else (time.time(), ns_len, ns)
  782. counts["delivery_obligations"] = conn.execute(
  783. "UPDATE delivery_obligations SET state='abandoned', updated_at=? "
  784. f"WHERE ({by_profile}substr(session_key, 1, ?) = ?) "
  785. "AND state NOT IN ('delivered', 'abandoned')", params).rowcount
  786. if "profile_name" in topic_columns.get("telegram_dm_topic_mode", set()):
  787. counts["telegram_dm_topic_mode"] = conn.execute(
  788. "DELETE FROM telegram_dm_topic_mode WHERE profile_name = ?", (name,)).rowcount
  789. binding_columns = topic_columns.get("telegram_dm_topic_bindings", set())
  790. if "session_key" in binding_columns:
  791. # A rename rewrites a binding's session_key namespace as well as its profile_name
  792. # (:meth:`rekey_profile_state`), so matching on one alone leaves the other behind.
  793. # Legacy v1/v2 bindings have no profile_name but keep the ``agent:<name>:`` namespace
  794. # in session_key, so the namespace match alone is the exact cleanup there.
  795. by_profile = "profile_name = ? OR " if "profile_name" in binding_columns else ""
  796. params = (name, ns_len, ns) if by_profile else (ns_len, ns)
  797. counts["telegram_dm_topic_bindings"] = conn.execute(
  798. "DELETE FROM telegram_dm_topic_bindings "
  799. f"WHERE {by_profile}substr(session_key, 1, ?) = ?", params).rowcount
  800. self._execute_write(_do)
  801. return counts
  802. @staticmethod
  803. def session_gateway_runtime(session_meta: Optional[dict[str, Any]]) -> dict[str, Any]:
  804. """Read the persisted runtime route off a session row dict (``model_config`` as
  805. JSON string or parsed dict). Precedence: nested ``gateway_runtime`` (gateway sync /
  806. CLI ``/model``), then top-level ``provider``/``base_url``/``api_mode`` (TUI), with
  807. ``billing_provider`` filling a missing provider so sessions that never ran ``/model``
  808. still restore the provider that served them; a bare ``custom`` billing class falls back
  809. to its ``billing_base_url`` endpoint for the readers' bare-custom heal (#136323).
  810. Empty dict on parse failure — resume uses ambient config."""
  811. from hermes_state import _BARE_BILLING_PROVIDERS
  812. raw = (session_meta or {}).get("model_config")
  813. if isinstance(raw, str):
  814. try:
  815. raw = json.loads(raw)
  816. except Exception:
  817. raw = {}
  818. if not isinstance(raw, dict):
  819. raw = {}
  820. runtime = raw.get("gateway_runtime")
  821. # Filter None: the persist path writes or-None to trigger deletion in the top-level
  822. # merge, but gateway_runtime is replaced whole (not deep-merged), so None survives here.
  823. if isinstance(runtime, dict) and runtime.get("provider"):
  824. return {k: v for k, v in runtime.items() if v is not None}
  825. top_level = {key: raw.get(key) for key in ("provider", "base_url", "api_mode") if raw.get(key)}
  826. # billing_provider is COALESCE-written on the first accounted API call — the only durable
  827. # record for sessions that never ran /model. Bare buckets ("auto"/"custom") are not
  828. # routable identities; "auto" is filtered so resume falls back to the ambient default.
  829. billing_provider = str((session_meta or {}).get("billing_provider") or "").strip()
  830. if billing_provider and billing_provider.lower() not in _BARE_BILLING_PROVIDERS:
  831. top_level.setdefault("provider", billing_provider)
  832. elif billing_provider.lower() == "custom" and not top_level.get("provider"):
  833. # Plain CLI turns persist only the billing class — "custom" — plus the endpoint they
  834. # served (billing_base_url, first-accounted overwrite on the row). Surface the pair so
  835. # the readers' bare-custom heal (canonical_custom_identity) can recover custom:<name>
  836. # from that endpoint instead of resume recombining the stored model with the ambient
  837. # default (#136323); an endpoint no longer configured still heals to nothing. "auto"
  838. # bills through the router with no attributable endpoint and stays filtered.
  839. billing_base_url = str((session_meta or {}).get("billing_base_url") or "").strip()
  840. if billing_base_url:
  841. top_level.setdefault("base_url", billing_base_url)
  842. top_level["provider"] = "custom"
  843. if top_level:
  844. return top_level
  845. if not isinstance(runtime, dict):
  846. return {}
  847. return {k: v for k, v in runtime.items() if v is not None}
  848. def register_backend_heartbeat(
  849. self, *, backend_id: str, pid: int, started_at: float, last_heartbeat: Optional[float] = None,
  850. profile: str = "", host: str = "") -> None:
  851. """Upsert this backend's liveness row. ``backend_id`` MUST be stable for the process
  852. lifetime (e.g. ``f"{profile}@{host}:{pid}"``) so a respawn cannot inherit a dead
  853. predecessor's heartbeat; ``started_at`` is when THIS process started, so a backend
  854. whose previous run died is not mistaken for a freshly-spawned sibling."""
  855. if not backend_id:
  856. return
  857. ts = time.time() if last_heartbeat is None else float(last_heartbeat)
  858. self._write_sql(
  859. "INSERT INTO gateway_heartbeats (backend_id, pid, started_at, last_heartbeat, profile, host)"
  860. " VALUES (?, ?, ?, ?, ?, ?) ON CONFLICT(backend_id) DO UPDATE SET pid = excluded.pid,"
  861. " started_at = excluded.started_at, last_heartbeat = excluded.last_heartbeat,"
  862. " profile = excluded.profile, host = excluded.host",
  863. (str(backend_id), int(pid), float(started_at), ts, str(profile), str(host)))
  864. def clear_backend_heartbeat(self, backend_id: str) -> bool:
  865. """Remove this backend's heartbeat row (from ``atexit``); True if removed. A crashed
  866. backend's row is reclaimed later by ``prune_stale_heartbeats``."""
  867. if not backend_id:
  868. return False
  869. return self._write_rowcount(
  870. "DELETE FROM gateway_heartbeats WHERE backend_id = ?", (str(backend_id),)) > 0
  871. def prune_stale_heartbeats(self, *, max_age_seconds: float) -> list[str]:
  872. """Drop heartbeat rows older than the staleness window; return removed backend ids.
  873. Safe from any process — only stale rows are touched."""
  874. if max_age_seconds <= 0:
  875. return []
  876. cutoff = time.time() - max_age_seconds
  877. def _do(conn):
  878. cur = conn.execute(
  879. "DELETE FROM gateway_heartbeats WHERE last_heartbeat < ? RETURNING backend_id",
  880. (cutoff,))
  881. return [str(r[0]) for r in cur.fetchall()]
  882. return list(self._execute_write(_do) or [])
  883. def list_backend_heartbeats(self) -> list[dict[str, Any]]:
  884. """Snapshot of every backend heartbeat (diagnostics/tests); fields mirror the table."""
  885. rows = self._read_all(
  886. "SELECT backend_id, pid, started_at, last_heartbeat, profile, host FROM gateway_heartbeats"
  887. " ORDER BY last_heartbeat DESC")
  888. return [dict(r) for r in rows]
  889. def request_handoff(self, session_id: str, platform: str) -> bool:
  890. """Mark a session pending handoff to *platform*; False if a handoff is already in flight."""
  891. return self._write_rowcount(
  892. "UPDATE sessions SET handoff_state = 'pending', handoff_platform = ?, "
  893. " handoff_error = NULL WHERE id = ? AND (handoff_state IS NULL "
  894. " OR handoff_state IN ('completed', 'failed'))",
  895. (platform, session_id),
  896. ) > 0
  897. def get_handoff_state(self, session_id: str) -> Optional[dict[str, Any]]:
  898. """Return ``{"state", "platform", "error"}`` or None if the session has no handoff record."""
  899. try:
  900. row = self._read_one(
  901. "SELECT handoff_state, handoff_platform, handoff_error FROM sessions WHERE id = ?",
  902. (session_id,))
  903. if not row:
  904. return None
  905. return {"state": row["handoff_state"], "platform": row["handoff_platform"],
  906. "error": row["handoff_error"]}
  907. except Exception:
  908. return None
  909. def list_pending_handoffs(self) -> list[dict[str, Any]]:
  910. """All sessions in handoff_state='pending', oldest first (gateway handoff watcher)."""
  911. try:
  912. rows = self._read_all(
  913. "SELECT s.*, COALESCE(sp.prompt, s.system_prompt) AS _system_prompt_resolved FROM sessions s "
  914. "LEFT JOIN system_prompts sp ON sp.hash = s.system_prompt_hash "
  915. "WHERE s.handoff_state = 'pending' "
  916. "ORDER BY s.started_at ASC")
  917. return [self._session_row_dict(r) for r in rows]
  918. except Exception:
  919. return []
  920. def claim_handoff(self, session_id: str) -> bool:
  921. """Atomically transition pending → running. Returns True if claimed."""
  922. return self._write_rowcount(
  923. "UPDATE sessions SET handoff_state = 'running' WHERE id = ? AND handoff_state = 'pending'",
  924. (session_id,),
  925. ) > 0
  926. def has_pending_handoffs(self) -> bool:
  927. """Bounded existence probe for the handoff watcher's idle gate (the gate fails open on error)."""
  928. return self._read_one("SELECT 1 FROM sessions WHERE handoff_state = 'pending' LIMIT 1") is not None
  929. def complete_handoff(self, session_id: str) -> None:
  930. """Mark a handoff as completed."""
  931. self._write_sql(
  932. "UPDATE sessions SET handoff_state = 'completed', handoff_error = NULL WHERE id = ?",
  933. (session_id,))
  934. def fail_handoff(
  935. self, session_id: str, error: str, *, only_states: Optional[tuple[str, ...]] = None) -> bool:
  936. """Mark a handoff failed and record the reason; True when a row transitioned. ``only_states`` makes
  937. the write a compare-and-swap on ``handoff_state``. Waiters that give up (CLI 60s poll, Desktop
  938. bounded poll) MUST pass ``only_states=("pending",)``: once the watcher has claimed the row
  939. (``running``) it owns the terminal state, and an unconditional waiter-side fail races the dispatch —
  940. the gateway later overwrites ``failed`` → ``completed`` after the user was told the gateway is down
  941. (split-brain: the handoff delivered and ``switch_session`` re-pointed the session). The watcher
  942. fails its OWN claimed row unconditionally."""
  943. states = tuple(only_states) if only_states else ()
  944. sql = _HANDOFF_FAIL_SQL + "id = ?" + (
  945. f" AND handoff_state IN ({', '.join('?' for _ in states)})" if states else "")
  946. return self._write_rowcount(sql, (error[:500], session_id, *states)) > 0
  947. def reclaim_stale_running_handoffs(self, error: str) -> list[str]:
  948. """Fail every handoff stuck in ``running``; returns the ids reclaimed. Only the gateway watcher sets
  949. ``running``, for one in-process dispatch — so a ``running`` row at watcher startup belongs to a
  950. PREVIOUS gateway that died mid-dispatch. It is poisonous: ``request_handoff`` only accepts
  951. NULL/``completed``/``failed``, so the session could never hand off again, with no error surfaced.
  952. Failing rather than re-queueing is deliberate: the dead gateway may already have switched the
  953. session key and dispatched the synthetic turn, so a blind retry risks double delivery; a clean
  954. terminal state the user can retry from is right."""
  955. def _do(conn):
  956. cur = conn.execute("SELECT id FROM sessions WHERE handoff_state = 'running'")
  957. ids = [r[0] for r in cur.fetchall()]
  958. if ids:
  959. conn.execute(_HANDOFF_FAIL_SQL + "handoff_state = 'running'", (error[:500],))
  960. return ids
  961. try:
  962. return self._execute_write(_do) or []
  963. except Exception:
  964. # Swallow but never silently: a persistently failing reclaim leaves poisonous
  965. # 'running' rows in place, so the operator needs a trace.
  966. logger.warning(
  967. "reclaim_stale_running_handoffs failed; stranded 'running' "
  968. "handoff rows (if any) were left in place", exc_info=True)
  969. return []