hermes_state_maintenance.py 35 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586
  1. """Retention pruning, stale-session archiving and VACUUM policy mixin for SessionDB."""
  2. from __future__ import annotations
  3. import logging
  4. import time
  5. from pathlib import Path
  6. from typing import Any, Dict, Iterable, List, Optional, Tuple
  7. from hermes_state_common import (
  8. AUTO_VACUUM_MIN_FREELIST_RATIO, _id_chunks, _non_continuation_child_sql, _placeholders, _sql_session_last_active,
  9. escape_like as _escape_like, emit_session_deleted,
  10. )
  11. from hermes_startup_watchdog import report_startup_progress
  12. # caplog tests pin the "hermes_state" logger name.
  13. logger = logging.getLogger("hermes_state")
  14. _LAST_ACTIVE_SQL = _sql_session_last_active("s")
  15. _TOKENS_SQL = "(COALESCE(s.input_tokens, 0) + COALESCE(s.output_tokens, 0))"
  16. _COST_SQL = "COALESCE(s.actual_cost_usd, s.estimated_cost_usd, 0)"
  17. def _like(value: str) -> str:
  18. return f"%{_escape_like(value.lower())}%"
  19. def _cwd_prefix_filter(value: str) -> tuple[list[str], list]:
  20. from hermes_state_sessions import _cwd_prefix_clause
  21. clause, params = _cwd_prefix_clause(value)
  22. return [clause], list(params)
  23. def _one(clause: str, conv=None):
  24. return lambda v: ([clause], [conv(v) if conv else v])
  25. def _seconds_since(now: float, raw) -> Optional[float]:
  26. """Age of a state_meta timestamp; None when unset or corrupt (= no prior run)."""
  27. try:
  28. return now - float(raw) if raw else None
  29. except (TypeError, ValueError):
  30. return None
  31. # Prune/archive filters in evaluation order: (kwarg, applies-when, builder -> (clauses, params)).
  32. # ``applies-when``: "notnone" (numeric/time bounds; 0 is a real bound) or "truthy" ("" = unset).
  33. _PRUNE_FILTERS = (
  34. # Orphan-swept rows age from the sweep, not old activity, or the next prune deletes them before recovery.
  35. ("last_active_before", "notnone", lambda v: (
  36. [_LAST_ACTIVE_SQL + " < ?",
  37. "(COALESCE(s.end_reason, '') != 'startup_orphan_reap' OR s.ended_at < ?)"],
  38. [v, v])),
  39. ("last_active_after", "notnone", _one(_LAST_ACTIVE_SQL + " >= ?")),
  40. ("started_before", "notnone", _one("s.started_at < ?")),
  41. ("started_after", "notnone", _one("s.started_at >= ?")),
  42. ("source", "truthy", _one("s.source = ?")),
  43. ("title_like", "truthy", _one("LOWER(COALESCE(s.title, '')) LIKE ? ESCAPE '\\'", _like)),
  44. ("end_reason", "truthy", _one("s.end_reason = ?")),
  45. ("cwd_prefix", "truthy", _cwd_prefix_filter),
  46. ("min_messages", "notnone", _one("s.message_count >= ?")),
  47. ("max_messages", "notnone", _one("s.message_count <= ?")),
  48. ("model_like", "truthy", _one("LOWER(COALESCE(s.model, '')) LIKE ? ESCAPE '\\'", _like)),
  49. ("provider", "truthy", _one("LOWER(COALESCE(s.billing_provider, '')) = ?", str.lower)),
  50. ("user_id", "truthy", _one("s.user_id = ?")),
  51. ("chat_id", "truthy", _one("s.chat_id = ?")),
  52. ("chat_type", "truthy", _one("s.chat_type = ?")),
  53. ("branch_like", "truthy", _one("LOWER(COALESCE(s.git_branch, '')) LIKE ? ESCAPE '\\'", _like)),
  54. ("min_tokens", "notnone", _one(_TOKENS_SQL + " >= ?")),
  55. ("max_tokens", "notnone", _one(_TOKENS_SQL + " <= ?")),
  56. ("min_cost", "notnone", _one(_COST_SQL + " >= ?")),
  57. ("max_cost", "notnone", _one(_COST_SQL + " <= ?")),
  58. ("min_tool_calls", "notnone", _one("COALESCE(s.tool_call_count, 0) >= ?")),
  59. ("max_tool_calls", "notnone", _one("COALESCE(s.tool_call_count, 0) <= ?")),
  60. )
  61. _PRUNE_FILTER_NAMES = frozenset(name for name, _, _ in _PRUNE_FILTERS) | {"archived", "include_pinned", "lineage_tips_only"}
  62. # Child ``c`` continues compression-ended ``p`` (same predicate as compression's child lookup).
  63. _CONTINUATION_EDGE_SQL = "p.end_reason = 'compression'\n" + _non_continuation_child_sql("c.", "p.id")
  64. def _continued_ancestors_sql(candidates_where: str) -> str:
  65. """Compression ancestors of every row *candidates_where* (alias ``s``) does not select."""
  66. return ("WITH RECURSIVE kept(id) AS ("
  67. " SELECT p.id FROM sessions c JOIN sessions p ON p.id = c.parent_session_id"
  68. f" WHERE {_CONTINUATION_EDGE_SQL}"
  69. f" AND NOT EXISTS (SELECT 1 FROM sessions s WHERE s.id = c.id AND {candidates_where})"
  70. " UNION"
  71. " SELECT p.id FROM kept k JOIN sessions c ON c.id = k.id JOIN sessions p ON p.id = c.parent_session_id"
  72. f" WHERE {_CONTINUATION_EDGE_SQL}"
  73. ") SELECT id FROM kept")
  74. # A pin covers the whole conversation, but a store can hold a pinned segment whose later
  75. # continuations were published unpinned; those still belong to the pinned chat.
  76. _PINNED_TAIL_SQL = ("WITH RECURSIVE tail(id) AS ("
  77. " SELECT c.id FROM sessions c JOIN sessions p ON p.id = c.parent_session_id"
  78. f" WHERE COALESCE(p.pinned, 0) = 1 AND {_CONTINUATION_EDGE_SQL}"
  79. " UNION"
  80. " SELECT c.id FROM tail t JOIN sessions p ON p.id = t.id JOIN sessions c ON c.parent_session_id = p.id"
  81. f" WHERE {_CONTINUATION_EDGE_SQL}"
  82. ") SELECT id FROM tail")
  83. def _not_pinned_sql(alias: str = "s") -> str:
  84. """Predicate sparing pinned rows and the unpinned continuations a pinned segment covers."""
  85. return f"COALESCE({alias}.pinned, 0) = 0 AND {alias}.id NOT IN ({_PINNED_TAIL_SQL})"
  86. class SessionMaintenanceMixin:
  87. """Retention pruning, stale-session archiving and VACUUM policy for SessionDB."""
  88. def prune_empty_ghost_sessions(self, sessions_dir: Optional[Path] = None) -> int:
  89. """Remove empty TUI ghost sessions (no messages, no title, >24hr old)."""
  90. cutoff = time.time() - 86400
  91. def _do(conn):
  92. ids = [r[0] for r in conn.execute("""
  93. SELECT id FROM sessions
  94. WHERE source = 'tui'
  95. AND title IS NULL
  96. AND ended_at IS NOT NULL
  97. AND started_at < ?
  98. AND NOT EXISTS (
  99. SELECT 1 FROM messages WHERE messages.session_id = sessions.id
  100. )
  101. """, (cutoff,)).fetchall()]
  102. from hermes_state_mutation_retirement import retire_prunable
  103. ids = retire_prunable(conn, ids)
  104. for chunk in _id_chunks(ids):
  105. conn.execute(f"DELETE FROM sessions WHERE id IN ({_placeholders(chunk)})", chunk)
  106. if ids:
  107. self._delete_unreferenced_system_prompts(conn)
  108. return ids
  109. removed_ids = self._execute_write(_do) or []
  110. emit_session_deleted(self, removed_ids, reason="ghost_prune", sessions_dir=sessions_dir)
  111. from hermes_state_media import collect_retired_media
  112. collect_retired_media(self)
  113. for sid in removed_ids if sessions_dir else ():
  114. self._remove_session_files(sessions_dir, sid)
  115. return len(removed_ids)
  116. def _guarded_ids(self, conn, ids: Iterable[str]) -> set:
  117. """Ids in *ids* protected by a live turn lease / compression lock. Idle compression-ended
  118. parents are closed, not live, so they are not guarded (prune and delete share this)."""
  119. return {sid for sid in ids if self._write_guards_reject(conn, sid, allow_closed_compression_parent=True)}
  120. def _write_guards_reject(self, conn, sid: str, **kwargs) -> bool:
  121. """True when a live turn lease / compression lock protects ``sid``; expired or
  122. dead-holder guards are reclaimed and fenced as a side effect."""
  123. from hermes_state import SessionCompressionInProgressError
  124. from hermes_state_errors import SessionTurnLeaseLostError
  125. try:
  126. self._check_transcript_write_guards(
  127. conn, sid, compression_lock_holder=None, turn_lease_holder=None,
  128. reject_active_turn_lease=True, reject_active_compression_lock=True, **kwargs)
  129. except (SessionCompressionInProgressError, SessionTurnLeaseLostError):
  130. return True
  131. return False
  132. def sweep_orphaned_sessions(
  133. self, *, max_idle_seconds: float, sources: tuple[str, ...] = ("tui", "desktop", "subagent"),
  134. exclude_ids: tuple[str, ...] = (), exclude_pinned: bool = False,
  135. heartbeat_staleness_seconds: Optional[float] = None,
  136. heartbeat_ownership_grace_seconds: Optional[float] = None, respect_gateway_heartbeats: bool = True,
  137. ) -> list[str]:
  138. """Close session rows orphaned by a dead gateway process (its in-process disconnect grace timer died
  139. with it, leaving ``ended_at IS NULL`` forever). Rows of ``sources`` whose ``started_at`` AND
  140. canonical last activity are both older than ``max_idle_seconds`` get
  141. ``end_reason='startup_orphan_reap'`` (the ``started_at`` predicate protects fresh compression/branch
  142. children whose copied activity is old). Only pass sources whose lifecycle the caller owns — never
  143. messaging platforms like ``telegram`` (ending those triggers a routing loop). ``exclude_ids`` spares
  144. rows this process still holds. Non-destructive: messages kept, row resumable, first-reason-wins.
  145. With ``respect_gateway_heartbeats`` a row is reaped only when no live backend (heartbeat within
  146. ``heartbeat_staleness_seconds``, default ``2 * max_idle_seconds``) could own it: B owns S if
  147. ``B.started_at <= S.started_at + grace`` (default = staleness) — grace covers a migrating backend
  148. whose sessions predate its first heartbeat, bounded so a PID-reuse respawn cannot protect rows
  149. forever. Disable the gate only for state.db-owned sources. SELECT, live-lease validation and UPDATE
  150. run in one ``BEGIN IMMEDIATE`` transaction; active leases/locks spare the row, expired guards are
  151. removed so their owner is fenced.
  152. See #65194.
  153. ``exclude_pinned`` is intended for broad automatic sweeps; pinned rows remain explicitly
  154. recoverable. See #60609.
  155. """
  156. srcs = tuple(s for s in sources if s)
  157. if max_idle_seconds <= 0 or not srcs:
  158. return []
  159. hb_staleness, hb_grace = heartbeat_staleness_seconds, heartbeat_ownership_grace_seconds
  160. if not (hb_staleness and hb_staleness > 0):
  161. hb_staleness = max_idle_seconds * 2
  162. if not (hb_grace is not None and hb_grace >= 0):
  163. hb_grace = hb_staleness
  164. cutoff = (now := time.time()) - max_idle_seconds
  165. pin_scope = f" AND {_not_pinned_sql('sessions')}" if exclude_pinned else ""
  166. orphan_predicate = f"started_at < ? AND {_sql_session_last_active('sessions')} < ?"
  167. heartbeat_params: tuple[float, ...] = ()
  168. if respect_gateway_heartbeats:
  169. orphan_predicate += (" AND NOT EXISTS (SELECT 1 FROM gateway_heartbeats h WHERE"
  170. " h.last_heartbeat >= ? AND h.started_at <= sessions.started_at + ?)")
  171. heartbeat_params = (now - hb_staleness, hb_grace)
  172. scope_sql = f" AND source IN ({_placeholders(srcs)}){pin_scope} AND {orphan_predicate}"
  173. scope_params = (*srcs, cutoff, cutoff, *heartbeat_params)
  174. def _do(conn):
  175. rows = conn.execute(f"SELECT id FROM sessions WHERE ended_at IS NULL{scope_sql}",
  176. scope_params).fetchall()
  177. excluded = {str(x) for x in exclude_ids if x}
  178. victims = [sid for sid in (str(row["id"]) for row in rows)
  179. if sid not in excluded and not self._write_guards_reject(conn, sid)]
  180. if not victims:
  181. return []
  182. # Re-apply every predicate under the write lock.
  183. conn.execute(
  184. f"UPDATE sessions SET ended_at = ?, end_reason = 'startup_orphan_reap'"
  185. f" WHERE id IN ({_placeholders(victims)}) AND ended_at IS NULL{scope_sql}",
  186. (time.time(), *victims, *scope_params))
  187. return victims
  188. return self._execute_write(_do) or []
  189. @staticmethod
  190. def _prune_filter_where(*, archived: Optional[bool] = None, include_pinned: bool = False,
  191. lineage_tips_only: bool = False, include_open: bool = False,
  192. **filters) -> tuple[str, list]:
  193. """Shared WHERE clause for bulk prune/archive selection (alias ``s``): ``_PRUNE_FILTERS``
  194. AND together, only ended sessions are ever candidates, ``archived`` is tri-state
  195. (None = both), ``*_like`` are case-insensitive substrings, the rest exact.
  196. ``lineage_tips_only`` (bulk archive) drops compression ancestors: they are archived with
  197. their tip, never on their own age — matching an old ancestor would fan out over the lineage
  198. and hide its OPEN, recently active tip (#115489). ``include_open`` lifts the ended guard
  199. for reversible archive only — never for prune/delete (#139597: desktop sessions never set
  200. ``ended_at``)."""
  201. unknown = set(filters) - _PRUNE_FILTER_NAMES
  202. if unknown:
  203. raise TypeError("SessionMaintenanceMixin._prune_filter_where() got an unexpected "
  204. f"keyword argument {min(unknown)!r}")
  205. clauses = [] if include_open else ["s.ended_at IS NOT NULL"]
  206. if lineage_tips_only:
  207. clauses.append("COALESCE(s.end_reason, '') <> 'compression'")
  208. params: list = []
  209. for name, applies, build in _PRUNE_FILTERS:
  210. value = filters.get(name)
  211. if (value is not None) if applies == "notnone" else bool(value):
  212. new_clauses, new_params = build(value)
  213. clauses.extend(new_clauses)
  214. params.extend(new_params)
  215. if isinstance(archived, bool):
  216. clauses.append(f"s.archived = {int(archived)}")
  217. # Pinned is a durable "keep" flag: bulk prune/delete/archive exclude pinned rows unless opted in.
  218. if not include_pinned:
  219. clauses.append(_not_pinned_sql())
  220. return " AND ".join(clauses), params
  221. def _prune_where(self, older_than_days, source, filters, *, whole_lineages: bool = False) -> tuple[str, list]:
  222. """Translate the legacy age window into the shared activity filter, then build WHERE.
  223. ``whole_lineages`` (prune) keeps a compression ancestor while any continuation after it
  224. is unmatched."""
  225. if (older_than_days is not None and filters.get("last_active_before") is None
  226. and filters.get("started_before") is None):
  227. if older_than_days < 0:
  228. raise ValueError(
  229. f"older_than_days must be >= 0, got {older_than_days!r}: a negative "
  230. "retention builds a future cutoff that matches every ended session.")
  231. filters["last_active_before"] = time.time() - (older_than_days * 86400)
  232. where, params = self._prune_filter_where(source=source, **filters)
  233. if not whole_lineages:
  234. return where, params
  235. # A compressed-away segment ages with its conversation, not on its own: while any later
  236. # segment stays, deleting it would cut the start off a chat that is still in use.
  237. return f"{where} AND s.id NOT IN ({_continued_ancestors_sql(where)})", [*params, *params]
  238. def list_prune_candidates(self, older_than_days: Optional[float] = None, source: str | None = None, *,
  239. whole_lineages: bool = False, **filters) -> list[dict[str, Any]]:
  240. """Dry-run: sessions a matching prune/archive would touch, oldest first (``older_than_days``
  241. = inactivity threshold: freshest of ``last_activity_at`` / latest message / ``started_at``)."""
  242. where, params = self._prune_where(older_than_days, source, filters, whole_lineages=whole_lineages)
  243. return [dict(row) for row in self._read_all(
  244. f"""SELECT s.id, s.source, s.title, s.model, s.started_at,
  245. {_LAST_ACTIVE_SQL} AS last_active,
  246. s.ended_at, s.message_count, s.archived
  247. FROM sessions s WHERE {where}
  248. ORDER BY last_active ASC, s.started_at ASC""", params)]
  249. def count_prune_matches(self, older_than_days: Optional[float] = None, source: str | None = None, *,
  250. pinned_only: bool = False, **filters) -> int:
  251. """Count-only :meth:`list_prune_candidates`; ``pinned_only`` counts rows carrying the pin
  252. itself, not the continuations it protects (CLI reports spared pinned sessions)."""
  253. if pinned_only:
  254. filters["include_pinned"] = True
  255. where, params = self._prune_where(older_than_days, source, filters)
  256. if pinned_only:
  257. where += " AND COALESCE(s.pinned, 0) = 1"
  258. return int(self._read_one(f"SELECT COUNT(*) FROM sessions s WHERE {where}", params)[0])
  259. def count_open_prune_matches(self, older_than_days: Optional[float] = None, source: str | None = None,
  260. **filters) -> int:
  261. """Count open sessions a matching prune/archive skips (``ended_at`` guard inverted);
  262. visibility-only. With ``include_open`` the selection already contains them: nothing is skipped."""
  263. if filters.get("include_open"):
  264. return 0
  265. where, params = self._prune_where(older_than_days, source, filters)
  266. ended_guard = "s.ended_at IS NOT NULL"
  267. if not where.startswith(ended_guard):
  268. raise RuntimeError("prune filter lost its ended-session safety guard")
  269. open_where = f"s.ended_at IS NULL{where[len(ended_guard):]}"
  270. return int(self._read_one(f"SELECT COUNT(*) FROM sessions s WHERE {open_where}", params)[0])
  271. def archive_stale_sessions(self, idle_days: float, *, exclude_pinned: bool = True) -> int:
  272. """Archive every session untouched for ``idle_days`` (freshest of ``last_activity_at`` /
  273. latest message / ``started_at``); may archive unended sessions. ``archived = 0`` makes
  274. repeats no-ops; only lineage tips (``end_reason <> 'compression'``) are candidates — a
  275. stale tip archives its chain via :meth:`set_session_archived`, so an old compressed-away
  276. root with a recent continuation is never matched. The hidden canonical Bot Chat (same
  277. predicate as :meth:`set_session_pinned`) is exempt: only a deliberate archive may retire
  278. it, since archiving releases its registry title to the next Bot open."""
  279. if idle_days is None or idle_days < 0:
  280. return 0
  281. cutoff = time.time() - float(idle_days) * 86400.0
  282. pin_clause = f"AND {_not_pinned_sql()}" if exclude_pinned else ""
  283. rows = self._read_all(
  284. f"""
  285. SELECT s.id FROM sessions s
  286. WHERE s.archived = 0
  287. AND COALESCE(s.end_reason, '') <> 'compression'
  288. {pin_clause}
  289. AND NOT (COALESCE(s.hidden, 0) <> 0 AND COALESCE(s.title, '') = ?)
  290. AND {_LAST_ACTIVE_SQL} < ?
  291. ORDER BY s.started_at ASC
  292. """, (self.CANONICAL_BOT_CHAT_TITLE, cutoff))
  293. for row in rows:
  294. # Sweep provenance: a later compression/resume of this lineage un-hides it (#117713).
  295. self._auto_archive_lineage(row[0])
  296. return len(rows)
  297. def maybe_auto_archive(
  298. self, idle_days: float = 3, min_interval_hours: int = 24, exclude_pinned: bool = True,
  299. ) -> dict[str, Any]:
  300. """Idempotent, non-destructive auto-archive of sessions idle for ``idle_days``; state_meta
  301. ``last_auto_archive`` gates runs within ``min_interval_hours``. Never raises."""
  302. result: dict[str, Any] = {"skipped": False, "archived": 0}
  303. try:
  304. now = time.time()
  305. try:
  306. last = float(self.get_meta("last_auto_archive") or 0.0)
  307. except (TypeError, ValueError):
  308. last = 0.0 # corrupt meta; treat as no prior run
  309. if last and now - last < min_interval_hours * 3600:
  310. result["skipped"] = True
  311. return result
  312. # Startup-watchdog lease: the archive sweep is I/O-bound (near-zero CPU),
  313. # which the watchdog's CPU fallback misreads as a parked deadlock.
  314. # No-op when the watchdog is not armed; never raises.
  315. report_startup_progress(900.0, phase="state_db_auto_archive")
  316. archived = result["archived"] = self.archive_stale_sessions(idle_days, exclude_pinned=exclude_pinned)
  317. # Record even a zero-archive run so we don't re-sweep every call.
  318. self.set_meta("last_auto_archive", str(now))
  319. if archived > 0:
  320. logger.info(
  321. "state.db auto-archive: archived %d session(s) idle >= %s days", archived, idle_days,
  322. )
  323. except Exception as exc:
  324. logger.warning("state.db auto-archive failed: %s", exc)
  325. result["error"] = str(exc)
  326. return result
  327. def prune_sessions(self, older_than_days: Optional[float] = 90, source: str | None = None,
  328. sessions_dir: Optional[Path] = None, exclude_active_write_guards: bool = False,
  329. deleted_ids: Optional[list] = None, **filters) -> int:
  330. """Delete ended sessions inactive for ``older_than_days`` (an explicit ``started_before`` /
  331. ``last_active_before`` overrides it; None = no implicit bound) matching the filters.
  332. Children outside the window are orphaned (parent NULLed), not cascade-deleted. With
  333. *sessions_dir*, transcript files are removed outside the DB transaction.
  334. ``exclude_active_write_guards`` (automatic maintenance) skips rows under a live turn lease
  335. or compression lock while expired/dead holders are reclaimed and fenced. A compression
  336. ancestor is deleted only together with every continuation after it (``whole_lineages``).
  337. ``deleted_ids`` (the owning gateway) receives the removed ids, so it can retire their
  338. in-memory routes and live sessions after the commit."""
  339. if filters.get("include_open"):
  340. # Deletion is not reversible, so the ended-session guard is structural here: the
  341. # archive-only escape hatch (#139597) must never reach the delete path.
  342. raise ValueError("prune_sessions() never deletes open sessions: include_open is archive-only")
  343. where, where_params = self._prune_where(older_than_days, source, filters, whole_lineages=True)
  344. removed_ids: list[str] = []
  345. def _do(conn):
  346. cursor = conn.execute(f"SELECT s.id FROM sessions s WHERE {where}", where_params)
  347. session_ids = {row["id"] for row in cursor.fetchall()}
  348. if exclude_active_write_guards:
  349. session_ids -= self._guarded_ids(conn, session_ids)
  350. from hermes_state_mutation_retirement import prune_retired_tombstones, retire_prunable
  351. session_ids = retire_prunable(conn, sorted(session_ids))
  352. prune_retired_tombstones(conn) # every prune pass also reclaims expired deletion tombstones
  353. if not session_ids:
  354. return 0
  355. # Batched: a cron-heavy store prunes tens of thousands of ids in one call.
  356. for chunk in _id_chunks(session_ids):
  357. ph = _placeholders(chunk)
  358. conn.execute(f"UPDATE sessions SET parent_session_id = NULL WHERE parent_session_id IN ({ph})", chunk)
  359. conn.execute(f"DELETE FROM messages WHERE session_id IN ({ph})", chunk)
  360. conn.execute(f"DELETE FROM sessions WHERE id IN ({ph})", chunk)
  361. removed_ids.extend(chunk)
  362. self._delete_unreferenced_system_prompts(conn)
  363. return len(session_ids)
  364. count = self._execute_write(_do)
  365. if count:
  366. emit_session_deleted(self, removed_ids, reason="prune", sessions_dir=sessions_dir)
  367. from hermes_state_media import collect_retired_media
  368. collect_retired_media(self)
  369. for sid in removed_ids:
  370. self._remove_session_files(sessions_dir, sid)
  371. if deleted_ids is not None:
  372. deleted_ids.extend(removed_ids)
  373. return count
  374. def _page_pragmas(self, names: tuple[str, ...], fail_msg: str) -> Optional[list]:
  375. """Integer PRAGMAs over the existing connection (never a byte probe); None + debug log on failure."""
  376. try:
  377. with self._read_ctx() as conn:
  378. if self._conn is None:
  379. return None
  380. return [int(conn.execute(f"PRAGMA {name}").fetchone()[0]) for name in names]
  381. except Exception as exc:
  382. logger.debug(fail_msg, exc)
  383. return None
  384. def logical_size_bytes(self) -> Optional[int]:
  385. """``page_count * page_size``: main-file size once the WAL is checkpointed in. Prefer
  386. over ``os.path.getsize`` when reporting a VACUUM: in WAL mode the rewrite lands in
  387. ``-wal`` and the checkpoint is refused while another connection holds a read-mark, so
  388. a stat() delta understates the win and can go negative."""
  389. values = self._page_pragmas(("page_count", "page_size"), "Could not read logical DB size: %s")
  390. return None if values is None else values[0] * values[1]
  391. def _freelist_ratio(self) -> Optional[float]:
  392. """Reclaimable fraction (``freelist_count / page_count``) gating VACUUM in
  393. :meth:`maybe_auto_prune_and_vacuum`; None = fall back to the time throttle.
  394. ``PRAGMA freelist_count / PRAGMA page_count`` read over the existing connection (never a byte-level
  395. probe of the live file — see ``sqlite_safe_read``). This is what VACUUM would actually give back; it
  396. is the gate :meth:`maybe_auto_prune_and_vacuum` uses to decide whether a full rewrite pays off
  397. (#54189).
  398. """
  399. values = self._page_pragmas(("page_count", "freelist_count"), "Could not read freelist ratio: %s")
  400. return None if values is None else (values[1] / values[0] if values[0] > 0 else 0.0)
  401. def _try_checkpoint(self, mode: str, fail_msg: str) -> None:
  402. try:
  403. self._conn.execute(f"PRAGMA wal_checkpoint({mode})")
  404. except Exception as exc:
  405. logger.debug(fail_msg, exc)
  406. def vacuum(self) -> int:
  407. """VACUUM to reclaim space after large deletes (SQLite never shrinks on its own).
  408. Takes an exclusive lock — callers must ensure no other writers are active. FTS5
  409. segments are merged first (:meth:`optimize_fts`) so their pages are reclaimed too;
  410. returns the number of FTS indexes optimized (0 on merge failure / no FTS). A quarantined
  411. handle (corrupt image, replaced file, lost WAL generation) raises before any rewrite: a
  412. VACUUM reads every page and commits the result back, turning contained damage into an
  413. amplified one (#105670). Same guard ``_execute_write`` applies to every write."""
  414. self._raise_if_db_corrupt()
  415. optimized = 0
  416. try:
  417. optimized = self.optimize_fts() # manages its own lock
  418. except Exception as exc:
  419. logger.warning("FTS optimize before VACUUM failed: %s", exc)
  420. with self._lock:
  421. self._raise_if_db_replaced()
  422. if self._conn is None:
  423. self._reopen_after_close_locked(context="write")
  424. # PASSIVE, not TRUNCATE: a manual `hermes sessions vacuum` runs in a transient CLI
  425. # process; a TRUNCATE reset here would race a live gateway writer.
  426. self._try_checkpoint("PASSIVE", "WAL checkpoint (PASSIVE) before VACUUM failed: %s")
  427. self._conn.execute("VACUUM")
  428. # VACUUM rewrites every page THROUGH the WAL; without this TRUNCATE a 3 GB DB leaves a 3 GB -wal.
  429. self._try_checkpoint("TRUNCATE", "WAL checkpoint (TRUNCATE) after VACUUM failed: %s")
  430. # TRUNCATE may replace the WAL inode; adopt the new sidecars so the
  431. # write-path generation guard does not halt this connection.
  432. self._record_db_file_identity()
  433. return optimized
  434. def maybe_auto_prune_and_vacuum(
  435. self, retention_days: int = 90, min_interval_hours: int = 24, vacuum: bool = True,
  436. sessions_dir: Optional[Path] = None, min_vacuum_interval_days: int = 30,
  437. min_vacuum_freelist_ratio: float = AUTO_VACUUM_MIN_FREELIST_RATIO,
  438. ) -> dict[str, Any]:
  439. """Idempotent startup auto-maintenance (never raises): prune inactive sessions, reap stale open
  440. state-owned rows, optional VACUUM. Runs at most once per ``min_interval_hours``; VACUUM has its own
  441. ``min_vacuum_interval_days`` throttle and also requires ``freelist_count / page_count`` >
  442. ``min_vacuum_freelist_ratio`` so a small prune on a dense multi-GB database never triggers a full
  443. rewrite. Stale-open reconciliation: cron/kanban/subagent/one-shot CLI rows never set ``ended_at``
  444. when their process dies and prune only deletes ended rows, so after pruning, open rows from
  445. :attr:`_AUTO_PRUNE_STALE_OPEN_SOURCES` older than ``retention_days`` are closed
  446. (``startup_orphan_reap``); they stay resumable and age from their close. Returns ``{"skipped",
  447. "pruned", "closed", "vacuumed"}`` plus ``"freelist_ratio"`` when a VACUUM was considered and
  448. ``"error"`` on failure.
  449. Records the last run timestamp in state_meta so subsequent calls within ``min_interval_hours``
  450. no-op. Designed to be called once at startup from long-lived entrypoints (CLI, gateway, cron
  451. scheduler). See #54189.
  452. When *sessions_dir* is provided, on-disk transcript files (``.json`` / ``.jsonl`` /
  453. ``request_dump_*``) for pruned sessions are removed as part of the same sweep (issue #3015).
  454. Messaging and UI sources are never touched here. See #54189.
  455. """
  456. from hermes_state_repair import _release_auto_maintenance_lock, _try_acquire_auto_maintenance_lock
  457. result: dict[str, Any] = {"skipped": False, "pruned": 0, "closed": 0, "vacuumed": False}
  458. if retention_days is None or retention_days < 0:
  459. # A negative retention would build a future cutoff and match every ended
  460. # session; auto_prune=false is the disable switch, not a negative bound.
  461. logger.warning(
  462. "state.db auto-maintenance skipped: sessions.retention_days=%r is outside the allowed "
  463. "range (a whole number of days >= 0); set sessions.auto_prune: false to disable pruning",
  464. retention_days)
  465. result["skipped"] = True
  466. return result
  467. maintenance_lock = _try_acquire_auto_maintenance_lock(self.db_path)
  468. if maintenance_lock is None:
  469. result["skipped"] = True
  470. return result
  471. try:
  472. now = time.time()
  473. since_prune = _seconds_since(now, self.get_meta("last_auto_prune"))
  474. if since_prune is not None and since_prune < min_interval_hours * 3600:
  475. result["skipped"] = True
  476. return result
  477. # Prune first: orphans closed below get a full retention window.
  478. # Startup-watchdog leases: each long step is I/O-bound (near-zero CPU), which the
  479. # watchdog's CPU fallback misreads as a parked deadlock. Leases are clamped to
  480. # _MAX_LEASE_S=900 per call, so a multi-minute step renews per step rather than
  481. # once at entry. No-op when the watchdog is not armed; never raises.
  482. report_startup_progress(900.0, phase="state_db_auto_prune")
  483. result["pruned"] = pruned = self.prune_sessions(
  484. older_than_days=retention_days, sessions_dir=sessions_dir, exclude_active_write_guards=True)
  485. report_startup_progress(900.0, phase="state_db_auto_sweep")
  486. closed = self.sweep_orphaned_sessions(
  487. max_idle_seconds=float(retention_days) * 86400.0,
  488. sources=self._AUTO_PRUNE_STALE_OPEN_SOURCES, exclude_pinned=True,
  489. respect_gateway_heartbeats=False, # state-owned lifecycles, not gateway heartbeats
  490. )
  491. result["closed"] = len(closed)
  492. # VACUUM only if rows were freed, the time throttle passed AND the
  493. # freelist ratio passed — it holds an exclusive lock for a full rewrite.
  494. since_vacuum = _seconds_since(now, self.get_meta("last_vacuum"))
  495. vacuum_due = since_vacuum is None or since_vacuum >= min_vacuum_interval_days * 86400
  496. if vacuum and pruned > 0 and vacuum_due:
  497. result["freelist_ratio"] = ratio = self._freelist_ratio()
  498. # Same admission `hermes sessions optimize` runs: VACUUM plus the TRUNCATE checkpoint
  499. # retire the WAL generation a sibling writer (gateway, Desktop, dashboard, cron) still
  500. # holds, and that is exactly the state every agent then refuses turns in (#110054).
  501. # The foreign scan skips our own pid, so it is paired with the in-process arm: under
  502. # one multiplexed process another live SessionDB generation for this same path is
  503. # just as much a holder as another process would be.
  504. # Automatic maintenance only ever SKIPS — a turn is never refused over housekeeping.
  505. from hermes_state_holders import (
  506. foreign_state_db_holders, in_process_state_db_holders)
  507. holders = (foreign_state_db_holders(self.db_path)
  508. + in_process_state_db_holders(self.db_path, exclude=self))
  509. if holders:
  510. result["vacuum_skipped_holders"] = len(holders)
  511. logger.debug(
  512. "state.db auto-maintenance: skipping VACUUM, %d other process(es) hold the "
  513. "store or a WAL sidecar (%s)",
  514. len(holders), ", ".join(f"{pid}:{target}" for pid, target in holders[:3]))
  515. elif ratio is None or ratio > min_vacuum_freelist_ratio:
  516. try:
  517. # VACUUM rewrites every page with ~zero CPU: renew the lease here so
  518. # a multi-minute rewrite on a large state.db never outlives the clamp.
  519. report_startup_progress(900.0, phase="state_db_auto_vacuum")
  520. self.vacuum()
  521. result["vacuumed"] = True
  522. self.set_meta("last_vacuum", str(now))
  523. except Exception as exc:
  524. logger.warning("state.db VACUUM failed: %s", exc)
  525. else:
  526. logger.debug("state.db auto-maintenance: skipping VACUUM, only "
  527. "%.1f%% of pages reclaimable (threshold %.0f%%)",
  528. ratio * 100.0, min_vacuum_freelist_ratio * 100.0)
  529. # Record even when pruned == 0 so the throttle holds.
  530. self.set_meta("last_auto_prune", str(now))
  531. if closed or pruned > 0:
  532. logger.info("state.db auto-maintenance: closed %d stale open session(s), "
  533. "pruned %d session(s) inactive for %d days%s",
  534. len(closed), pruned, retention_days, " + VACUUM" if result["vacuumed"] else "")
  535. except Exception as exc:
  536. # Maintenance must never block startup.
  537. logger.warning("state.db auto-maintenance failed: %s", exc)
  538. result["error"] = str(exc)
  539. finally:
  540. _release_auto_maintenance_lock(maintenance_lock)
  541. return result