hermes_state_search.py 87 KB

12345678910111213141516171819202122232425262728293031323334353637383940414243444546474849505152535455565758596061626364656667686970717273747576777879808182838485868788899091929394959697989910010110210310410510610710810911011111211311411511611711811912012112212312412512612712812913013113213313413513613713813914014114214314414514614714814915015115215315415515615715815916016116216316416516616716816917017117217317417517617717817918018118218318418518618718818919019119219319419519619719819920020120220320420520620720820921021121221321421521621721821922022122222322422522622722822923023123223323423523623723823924024124224324424524624724824925025125225325425525625725825926026126226326426526626726826927027127227327427527627727827928028128228328428528628728828929029129229329429529629729829930030130230330430530630730830931031131231331431531631731831932032132232332432532632732832933033133233333433533633733833934034134234334434534634734834935035135235335435535635735835936036136236336436536636736836937037137237337437537637737837938038138238338438538638738838939039139239339439539639739839940040140240340440540640740840941041141241341441541641741841942042142242342442542642742842943043143243343443543643743843944044144244344444544644744844945045145245345445545645745845946046146246346446546646746846947047147247347447547647747847948048148248348448548648748848949049149249349449549649749849950050150250350450550650750850951051151251351451551651751851952052152252352452552652752852953053153253353453553653753853954054154254354454554654754854955055155255355455555655755855956056156256356456556656756856957057157257357457557657757857958058158258358458558658758858959059159259359459559659759859960060160260360460560660760860961061161261361461561661761861962062162262362462562662762862963063163263363463563663763863964064164264364464564664764864965065165265365465565665765865966066166266366466566666766866967067167267367467567667767867968068168268368468568668768868969069169269369469569669769869970070170270370470570670770870971071171271371471571671771871972072172272372472572672772872973073173273373473573673773873974074174274374474574674774874975075175275375475575675775875976076176276376476576676776876977077177277377477577677777877978078178278378478578678778878979079179279379479579679779879980080180280380480580680780880981081181281381481581681781881982082182282382482582682782882983083183283383483583683783883984084184284384484584684784884985085185285385485585685785885986086186286386486586686786886987087187287387487587687787887988088188288388488588688788888989089189289389489589689789889990090190290390490590690790890991091191291391491591691791891992092192292392492592692792892993093193293393493593693793893994094194294394494594694794894995095195295395495595695795895996096196296396496596696796896997097197297397497597697797897998098198298398498598698798898999099199299399499599699799899910001001100210031004100510061007100810091010101110121013101410151016101710181019102010211022102310241025102610271028102910301031103210331034103510361037103810391040104110421043104410451046104710481049105010511052105310541055105610571058105910601061106210631064106510661067106810691070107110721073107410751076107710781079108010811082108310841085108610871088108910901091109210931094109510961097109810991100110111021103110411051106110711081109111011111112111311141115111611171118111911201121112211231124112511261127112811291130113111321133113411351136113711381139114011411142114311441145114611471148114911501151115211531154115511561157115811591160116111621163116411651166116711681169117011711172117311741175117611771178117911801181118211831184118511861187118811891190119111921193119411951196119711981199120012011202120312041205120612071208120912101211121212131214121512161217121812191220122112221223122412251226122712281229123012311232123312341235123612371238123912401241124212431244124512461247124812491250125112521253125412551256125712581259126012611262126312641265126612671268126912701271127212731274127512761277127812791280128112821283128412851286128712881289129012911292129312941295129612971298129913001301130213031304130513061307130813091310131113121313131413151316131713181319132013211322132313241325132613271328132913301331133213331334133513361337133813391340134113421343134413451346134713481349135013511352135313541355135613571358135913601361136213631364136513661367136813691370137113721373137413751376137713781379138013811382138313841385138613871388138913901391139213931394139513961397139813991400140114021403140414051406140714081409141014111412141314141415141614171418141914201421142214231424142514261427142814291430143114321433143414351436143714381439144014411442144314441445144614471448144914501451145214531454145514561457145814591460146114621463146414651466146714681469147014711472147314741475147614771478147914801481148214831484148514861487148814891490149114921493149414951496149714981499150015011502150315041505150615071508150915101511151215131514151515161517151815191520152115221523152415251526152715281529153015311532153315341535153615371538153915401541154215431544154515461547154815491550155115521553155415551556155715581559156015611562
  1. """Full-text / trigram / CJK message search and FTS maintenance for SessionDB.
  2. Plain mixin for ``hermes_state.SessionDB`` (no ``__init__``/state of its own).
  3. Must never import hermes_state (cycle); shared constants live in hermes_state_common.
  4. """
  5. import contextlib
  6. import contextvars
  7. import logging
  8. import re
  9. import sqlite3
  10. import threading
  11. import time
  12. from dataclasses import dataclass, field
  13. from functools import partial
  14. from typing import Any, Callable, Collection, Dict, List, Optional, Tuple
  15. from agent.skill_commands import describe_skill_invocation
  16. from hermes_state_errors import is_malformed_db_error
  17. from hermes_state_common import (
  18. FTS_CJK_STALE_KEY, FTS_SQL, FTS_STALE_KEY, FTS_STORAGE_VERSION, FTS_TOOL_CONTENT_PREFIX_CHARS,
  19. FTS_TRIGRAM_EXCLUDED_SOURCES, FTS_TRIGRAM_SQL,
  20. MAX_FTS5_QUERY_CHARS, SCHEMA_VERSION, _FTS_CJK_TRIGGERS,
  21. escape_like as _escape_like, fts_rebuild_admission, fts_trigram_session_sql, routed_sessions_setting,
  22. )
  23. from hermes_state_fts import fts_layout_current, fts_layout_drift
  24. # Pre-split logger identity so log filtering/capture is unchanged.
  25. logger = logging.getLogger("hermes_state")
  26. @dataclass
  27. class _BackfillProgress:
  28. """How many messages a backfill pass (``backfill`` = base + trigram, ``backfill_cjk``) has
  29. indexed out of the messages at or below its high water. Counts rows, never ids: a pruned
  30. store's id gaps would otherwise read 0% then jump to 99%. The step engine adds the rows each
  31. window covered (:meth:`SessionSearchMixin._rebuild_step`)."""
  32. phase: str
  33. total: int = 0
  34. indexed: int = 0
  35. def info(self) -> dict[str, Any]:
  36. done, total = self.indexed, self.total
  37. return {"phase": self.phase, "percent": min(100, 100 * done // total) if total else 100,
  38. "indexed": min(done, total), "total": total}
  39. @dataclass
  40. class _TeardownProgress:
  41. """Which demoted shadow table optimize-storage is draining and how far (``progress_cb``
  42. payload). Owned and kept current by :meth:`SessionSearchMixin._fts_teardown_trash_step`.
  43. ``counted=False`` (no progress callback) skips the per-table ``COUNT(*)``."""
  44. table: str = ""
  45. table_index: int = 0
  46. table_count: int = 0
  47. total: int = 0
  48. drained: int = 0
  49. counted: bool = True
  50. def info(self) -> dict[str, Any]:
  51. done, total = self.drained, self.total
  52. return {"phase": "teardown", "percent": min(100, 100 * done // total) if total else 100,
  53. "indexed": done, "total": total, "table": self.table,
  54. "table_index": self.table_index, "table_count": self.table_count}
  55. @dataclass
  56. class _VacuumProgress:
  57. """VACUUM is one statement: liveness is elapsed time against the size before and the estimated
  58. size after (live pages only), never a percent."""
  59. size_bytes: int
  60. estimated_bytes: int
  61. started: float = field(default_factory=time.monotonic)
  62. def info(self) -> dict[str, Any]:
  63. return {"phase": "vacuum", "percent": 0, "indexed": 0, "total": 0,
  64. "elapsed": time.monotonic() - self.started, "size_bytes": self.size_bytes,
  65. "estimated_bytes": self.estimated_bytes}
  66. class _HolderWatch:
  67. """Re-runs the foreign-holder scan (the one the held-store refusal and ``--force`` use) every
  68. *interval* seconds on a side thread while optimize-storage rebuilds and tears down. The chunk
  69. loop only reads :attr:`held`, so a quiet store pays nothing per chunk, while a writer that opens
  70. state.db mid-run (Desktop launched, gateway restarted) moves the loop onto the paced cycle within
  71. one interval instead of waiting behind back-to-back chunks."""
  72. def __init__(self, scan: Callable[[], list], interval: float):
  73. self._scan, self._interval = scan, interval
  74. self._held, self._stop = threading.Event(), threading.Event()
  75. self._thread: Optional[threading.Thread] = None
  76. @property
  77. def held(self) -> bool:
  78. return self._held.is_set()
  79. def _refresh(self) -> None:
  80. (self._held.set if self._scan() else self._held.clear)()
  81. def _run(self) -> None:
  82. while not self._stop.wait(self._interval):
  83. self._refresh()
  84. def __enter__(self) -> "_HolderWatch":
  85. self._refresh() # a holder already there paces from the first chunk
  86. # copy_context().run: the watcher keeps the caller's profile scope (HX012).
  87. self._thread = threading.Thread(target=contextvars.copy_context().run, args=(self._run,),
  88. name="optimize-holder-watch", daemon=True)
  89. self._thread.start()
  90. return self
  91. def __exit__(self, *_exc) -> None:
  92. self._stop.set()
  93. if self._thread is not None:
  94. self._thread.join()
  95. def _search_slow_ms() -> float:
  96. """``sessions.search_slow_ms`` for the served profile (default 1000; 0 logs every call)."""
  97. value = routed_sessions_setting("search_slow_ms", "HERMES_SEARCH_SLOW_MS")
  98. try:
  99. return 1000.0 if value is None or str(value).strip() == "" else float(value)
  100. except (TypeError, ValueError):
  101. return 1000.0
  102. # Characters FTS5's query grammar rejects outside a quoted phrase (anything missing
  103. # reaches MATCH raw and raises -> zero results). ``%`` is deliberately excluded: the
  104. # CJK LIKE fallback needs it literal (that path escapes wildcards itself).
  105. _FTS5_SPECIAL_CHARS = '+{}():"^@/#&|~[]<>,;!?$=\\\''
  106. _FTS5_SPECIAL_RE = re.compile(f"[{re.escape(_FTS5_SPECIAL_CHARS)}]")
  107. _FTS_OPERATORS = frozenset({"AND", "OR", "NOT"})
  108. _LIKE_SKIP_TOKENS = _FTS_OPERATORS | {"NEAR"}
  109. _LIKE_TOKEN_RE = re.compile(r'"[^"]+"|\S+')
  110. _QUOTED_PHRASE_RE = re.compile(r'"[^"]*"')
  111. # Column list shared by every search route (snippet + metadata, never content).
  112. _SEARCH_SELECT_TAIL = "m.timestamp, m.tool_name, s.source, s.model, s.started_at AS session_started"
  113. _LIKE_SNIPPET_SQL = "substr(m.content, max(1, instr(m.content, ?) - 40), 120) AS snippet"
  114. _LIKE_ANY_COLUMN_SQL = (
  115. "(m.content LIKE ? ESCAPE '\\' OR m.tool_name LIKE ? ESCAPE '\\' OR m.tool_calls LIKE ? ESCAPE '\\')"
  116. )
  117. _LIKE_COALESCED_COLUMN_SQL = (
  118. "(COALESCE(m.content, '') LIKE ? ESCAPE '\\' OR "
  119. "COALESCE(m.tool_name, '') LIKE ? ESCAPE '\\' OR "
  120. "COALESCE(m.tool_calls, '') LIKE ? ESCAPE '\\')"
  121. )
  122. # ``sort`` -> ORDER BY for the FTS routes; unknown values are rank-only (user input passes through).
  123. _FTS_ORDER_BY = {"newest": "ORDER BY m.timestamp DESC, rank", "oldest": "ORDER BY m.timestamp ASC, rank"}
  124. # Indexed neighbor seeks avoid scanning whole sessions for a sparse set of hits.
  125. _CONTEXT_WINDOW_SQL = """WITH target AS (
  126. SELECT session_id, timestamp, id FROM messages WHERE id IN ({ids})
  127. )
  128. SELECT t.id AS match_id, m.role, m.content
  129. FROM target t JOIN messages m ON m.id IN (
  130. t.id,
  131. (SELECT p.id FROM messages p
  132. WHERE p.session_id = t.session_id AND (p.timestamp, p.id) < (t.timestamp, t.id)
  133. ORDER BY p.timestamp DESC, p.id DESC LIMIT 1),
  134. (SELECT n.id FROM messages n
  135. WHERE n.session_id = t.session_id AND (n.timestamp, n.id) > (t.timestamp, t.id)
  136. ORDER BY n.timestamp, n.id LIMIT 1)
  137. )
  138. ORDER BY t.id, m.timestamp, m.id"""
  139. # Unified Ideographs, Extension A, Extension B, CJK Symbols, Hiragana, Katakana, Hangul Syllables.
  140. _CJK_RANGES = (
  141. (0x4E00, 0x9FFF), (0x3400, 0x4DBF), (0x20000, 0x2A6DF), (0x3000, 0x303F), (0x3040, 0x309F),
  142. (0x30A0, 0x30FF), (0xAC00, 0xD7AF),
  143. )
  144. def _meta_row(conn, key: str) -> Optional[sqlite3.Row]:
  145. """Point-read one ``state_meta`` row (``None`` when absent)."""
  146. return conn.execute("SELECT value FROM state_meta WHERE key = ?", (key,)).fetchone()
  147. def _delete_meta(conn, *keys: str) -> None:
  148. conn.execute(f"DELETE FROM state_meta WHERE key IN ({','.join('?' for _ in keys)})", keys)
  149. def _is_cjk(cp: int) -> bool:
  150. return any(lo <= cp <= hi for lo, hi in _CJK_RANGES)
  151. def _non_operator_tokens(raw_query: str) -> list[str]:
  152. return [t for t in raw_query.split() if t.upper() not in _FTS_OPERATORS]
  153. def _quote_fts_tokens(raw_query: str) -> str:
  154. """Quote each non-operator token (neutralising FTS5 special characters), keeping AND/OR/NOT."""
  155. return " ".join(
  156. tok if tok.upper() in _FTS_OPERATORS else '"' + tok.replace('"', '""') + '"' for tok in raw_query.split()
  157. )
  158. def _like_params(term: str) -> list[str]:
  159. """One ``%term%`` bind per column of ``_LIKE_ANY_COLUMN_SQL``."""
  160. return [f"%{_escape_like(term)}%"] * 3
  161. def _strip_cjk_wildcards(raw_query: str) -> str:
  162. """Drop the trailing prefix wildcard callers append for ASCII ("nimb" -> "nimb*").
  163. None of the CJK routes can honour that star: the bigram and trigram routes
  164. quote every token before MATCH (so ``*`` matches a literal asterisk) and
  165. LIKE has no ``*`` wildcard at all (only ``%``/``_``). Left in place, every
  166. CJK search arriving from the web/desktop search box — which appends the
  167. star to each unquoted token so partial English words match — searches for
  168. a term ending in a literal ``*`` and returns nothing (#90636). Only
  169. TRAILING stars go: a star written inside a quoted phrase is the user's
  170. own text, and a token that is ALL stars keeps its original form so it
  171. cannot degrade to a match-everything empty term.
  172. """
  173. if "*" not in raw_query:
  174. return raw_query
  175. stripped: list[str] = []
  176. for token in raw_query.split():
  177. if token.upper() in _FTS_OPERATORS:
  178. stripped.append(token)
  179. else:
  180. stripped.append(token.rstrip("*") or token)
  181. return " ".join(stripped) or raw_query
  182. def _flatten_text(decoded: Any) -> str:
  183. """Multimodal part list -> joined text (or the placeholder); str passes through; else ''."""
  184. if isinstance(decoded, list):
  185. parts = [p.get("text", "") for p in decoded if isinstance(p, dict) and p.get("type") == "text"]
  186. return " ".join(t for t in parts if t).strip() or "[multimodal content]"
  187. return decoded if isinstance(decoded, str) else ""
  188. def _positive_int(name: str, value: Any) -> None:
  189. if isinstance(value, bool) or not isinstance(value, int):
  190. raise TypeError(f"{name} must be an integer")
  191. if value <= 0:
  192. raise ValueError(f"{name} must be greater than zero")
  193. def _search_select_sql(snippet_sql: str, from_sql: str, where: list[str], order_by: str, limit_sql: str) -> str:
  194. """Result-row SELECT shared by the FTS and LIKE routes (SQL text is pinned)."""
  195. return f"""
  196. SELECT m.id, m.session_id, m.role,
  197. {snippet_sql},
  198. {_SEARCH_SELECT_TAIL}
  199. FROM {from_sql}
  200. JOIN sessions s ON s.id = m.session_id
  201. WHERE {' AND '.join(where)}
  202. {order_by}
  203. {limit_sql}
  204. """
  205. def _search_filter_clauses(
  206. where: list[str], params: list, *, include_inactive: bool, source_filter: Optional[list[str]],
  207. exclude_sources: Optional[list[str]], role_filter: Optional[list[str]],
  208. after_ts: Optional[int] = None, before_ts: Optional[int] = None) -> None:
  209. """Append the visibility/source/role/session-start predicates every search route shares. Live
  210. rows (active=1) AND compaction-archived rows (compacted=1) are discoverable; only
  211. rewind/undo rows (active=0, compacted=0) are hidden. ``after_ts``/``before_ts`` bound
  212. ``sessions.started_at`` (inclusive / exclusive) inside the query so LIMIT cannot be
  213. filled by out-of-window hits."""
  214. if not include_inactive:
  215. where.append("(m.active = 1 OR m.compacted = 1)")
  216. # display_kind="hidden" rows are model-facing scaffolding the person never saw; a hit would confuse.
  217. where.append("COALESCE(m.display_kind, '') <> 'hidden'")
  218. if source_filter is not None:
  219. where.append(f"s.source IN ({','.join('?' for _ in source_filter)})")
  220. params.extend(source_filter)
  221. if exclude_sources is not None:
  222. where.append(f"s.source NOT IN ({','.join('?' for _ in exclude_sources)})")
  223. params.extend(exclude_sources)
  224. if role_filter:
  225. where.append(f"m.role IN ({','.join('?' for _ in role_filter)})")
  226. params.extend(role_filter)
  227. if after_ts is not None:
  228. where.append("s.started_at >= ?")
  229. params.append(int(after_ts))
  230. if before_ts is not None:
  231. where.append("s.started_at < ?")
  232. params.append(int(before_ts))
  233. class SessionSearchMixin:
  234. """See module docstring — mixin for SessionDB (Search cluster)."""
  235. _SEARCH_MESSAGE_RESULT_FIELDS = (
  236. "id", "session_id", "role", "snippet", "timestamp", "tool_name", "source", "model", "session_started", "context"
  237. )
  238. @classmethod
  239. def _search_message_fields(cls, fields: Optional[Collection[str]]) -> Optional[tuple[str, ...]]:
  240. """Validate and canonically order an optional result projection."""
  241. if fields is None:
  242. return None
  243. if isinstance(fields, str):
  244. raise TypeError("search fields must be a collection of field names, not a string")
  245. requested = set(fields)
  246. unknown = requested.difference(cls._SEARCH_MESSAGE_RESULT_FIELDS)
  247. if unknown:
  248. raise ValueError(f"unknown search result field(s): {', '.join(sorted(unknown))}")
  249. return tuple(field for field in cls._SEARCH_MESSAGE_RESULT_FIELDS if field in requested)
  250. def _try_incremental_merge_fts(self) -> None:
  251. """One bounded FTS5 merge pass that never fails the already-committed write (a caller
  252. must never replay an ambiguous, possibly-durable write — even on the bare
  253. SystemError CPython's sqlite3 layer can raise under cross-thread errmsg scrambling)."""
  254. if not self._fts_enabled:
  255. return
  256. try:
  257. self._merge_fts_incrementally(max_pages=self._FTS_MERGE_MAX_PAGES_PER_INDEX)
  258. except Exception as exc:
  259. # The canonical write is already committed before this cadence runs. No maintenance failure —
  260. # including the bare SystemError the CPython sqlite3 layer can raise under cross-thread errmsg
  261. # scrambling — may escape and make the caller replay an ambiguous, possibly-durable write
  262. # (#90734, #85079).
  263. logger.warning("FTS incremental merge failed after commit: %s", exc)
  264. # ── Deferred rebuild engine (base + CJK backfills) ─────────────────────
  265. def fts_rebuild_status(self) -> Optional[dict[str, Any]]:
  266. """Deferred-rebuild progress ``{"pending", "total", "indexed", "percent"}`` or None. Reads
  267. via the pooled reader (not get_meta/self._lock) so search never blocks on the writer."""
  268. return self._rebuild_status("fts_rebuild")
  269. def fts_cjk_rebuild_status(self) -> Optional[dict[str, Any]]:
  270. """CJK-index backfill progress, or None when none is pending."""
  271. return self._rebuild_status("fts_cjk_rebuild")
  272. def _rebuild_status(self, prefix: str) -> Optional[dict[str, Any]]:
  273. rows = self._read_all("SELECT key, value FROM state_meta WHERE key IN (?, ?)",
  274. (f"{prefix}_high_water", f"{prefix}_progress"))
  275. meta = {r["key"]: r["value"] for r in rows}
  276. high_water = meta.get(f"{prefix}_high_water")
  277. if high_water is None or int(high_water) <= 0:
  278. return None
  279. total, progress = int(high_water), int(meta.get(f"{prefix}_progress") or 0)
  280. return {"pending": True, "total": total, "indexed": progress, "percent": min(100, int(100 * progress / total))}
  281. # Re-index rows in an id window the index is missing. docsize has one row
  282. # per indexed doc, so the anti-join is exact. Params: (lo, hi).
  283. # NOTE: with the aligned projection (FTS_STORAGE_VERSION 3) every writer
  284. # of the index — this sweep, the chunked backfill, and the sync triggers —
  285. # feeds ``messages_fts`` through the ONE stable per-row expression:
  286. # tool rows are truncated to FTS_TOOL_CONTENT_PREFIX_CHARS, everything
  287. # else is verbatim, and nothing consults a moving state_meta marker.
  288. _BOUNDARY_SWEEP_SQL = (
  289. "INSERT INTO {table}(rowid, content, tool_name, tool_calls) "
  290. "SELECT m.id, m.content, m.tool_name, m.tool_calls FROM messages m WHERE m.id > ? AND m.id <= ? {extra}"
  291. "AND NOT EXISTS (SELECT 1 FROM {table}_docsize d WHERE d.id = m.id)"
  292. )
  293. _BASE_BOUNDARY_SWEEP_SQL = (
  294. "INSERT INTO messages_fts(rowid, content, tool_name, tool_calls) "
  295. "SELECT m.id, CASE WHEN m.role = 'tool' THEN substr(COALESCE(m.content, ''), 1, ?) "
  296. "ELSE m.content END, m.tool_name, m.tool_calls FROM messages m WHERE m.id > ? AND m.id <= ? "
  297. "AND NOT EXISTS (SELECT 1 FROM messages_fts_docsize d WHERE d.id = m.id)"
  298. )
  299. # Trigram excludes tool rows and FTS_TRIGRAM_EXCLUDED_SOURCES sessions; no tool_calls column.
  300. _TRIGRAM_BOUNDARY_SWEEP_SQL = (
  301. "INSERT INTO messages_fts_trigram(rowid, content, tool_name) "
  302. "SELECT m.id, m.content, m.tool_name FROM messages m JOIN sessions s ON s.id = m.session_id "
  303. f"WHERE m.id > ? AND m.id <= ? AND m.role <> 'tool' AND {fts_trigram_session_sql('s')} "
  304. "AND NOT EXISTS (SELECT 1 FROM messages_fts_trigram_docsize d WHERE d.id = m.id)"
  305. )
  306. _CHUNK_INSERT_SQL = (
  307. "INSERT INTO {table}(rowid, content, tool_name, tool_calls) "
  308. "SELECT id, CASE WHEN role = 'tool' "
  309. f"THEN substr(COALESCE(content, ''), 1, {FTS_TOOL_CONTENT_PREFIX_CHARS}) "
  310. "ELSE content END, tool_name, tool_calls FROM messages WHERE id > ? AND id <= ?{extra}"
  311. )
  312. _TRIGRAM_CHUNK_INSERT_SQL = (
  313. "INSERT INTO messages_fts_trigram(rowid, content, tool_name) "
  314. "SELECT m.id, m.content, m.tool_name FROM messages m JOIN sessions s ON s.id = m.session_id "
  315. f"WHERE m.id > ? AND m.id <= ? AND m.role <> 'tool' AND {fts_trigram_session_sql('s')}"
  316. )
  317. def _fts_rebuild_finish(self) -> None:
  318. """Finalize the deferred rebuild: boundary sweep + clear markers. The sweep is cheap
  319. insurance against a write that slipped between high_water capture and trigger
  320. activation. The trigram half is gated on ``_trigram_available``: without the
  321. tokenizer/table an unconditional INSERT raises and aborts the whole rebuild."""
  322. sweeps = [(self._BASE_BOUNDARY_SWEEP_SQL, True)]
  323. if self._trigram_available:
  324. sweeps.append((self._TRIGRAM_BOUNDARY_SWEEP_SQL, False))
  325. self._rebuild_finish("fts_rebuild", sweeps)
  326. logger.info("Deferred FTS rebuild complete — all messages indexed.")
  327. def _fts_cjk_rebuild_finish(self) -> None:
  328. """Boundary sweep + clear the cjk markers; index becomes servable."""
  329. sweep = self._BOUNDARY_SWEEP_SQL.format(table="messages_fts_cjk", extra="AND m.role <> 'tool' ")
  330. self._rebuild_finish("fts_cjk_rebuild", [(sweep, False)])
  331. self._fts_cjk_available = True
  332. logger.info("CJK FTS index backfill complete — serving CJK search.")
  333. def _rebuild_finish(self, prefix: str, sweep_sqls: list[tuple[str, bool]]) -> None:
  334. """Sweep a generous window around the high-water boundary, then clear the markers.
  335. ``(sql, bounded)``: a bounded sweep takes the tool-content prefix_chars param first."""
  336. def _do(conn):
  337. hw_row = _meta_row(conn, f"{prefix}_high_water")
  338. if hw_row is not None:
  339. hw = int(hw_row[0])
  340. for sql, bounded in sweep_sqls:
  341. params = (FTS_TOOL_CONTENT_PREFIX_CHARS,) if bounded else ()
  342. conn.execute(sql, (*params, hw - 1000, hw + 1000))
  343. _delete_meta(conn, f"{prefix}_high_water", f"{prefix}_progress")
  344. self._execute_write(_do)
  345. def fts_rebuild_step(self, progress: Optional[_BackfillProgress] = None) -> bool:
  346. """Backfill one chunk (``_fts_chunk_rows`` messages) of the deferred FTS rebuild; True while
  347. work remains. Chunks are claimed atomically inside the write transaction, so concurrent
  348. processes interleave instead of duplicating rows. A failed chunk raises (nothing committed;
  349. the caller decides whether to retry). *progress* gains the messages the chunk covered."""
  350. if not self._fts_enabled:
  351. return False
  352. inserts = [self._CHUNK_INSERT_SQL.format(table="messages_fts", extra="")]
  353. if self._trigram_available:
  354. inserts.append(self._TRIGRAM_CHUNK_INSERT_SQL)
  355. return self._rebuild_step("fts_rebuild", inserts, finish=self._fts_rebuild_finish, finish_when_empty=True,
  356. progress=progress)
  357. def fts_cjk_rebuild_step(self, progress: Optional[_BackfillProgress] = None) -> bool:
  358. """Backfill one chunk of the CJK index. True while work remains."""
  359. if not self._fts_enabled or not self._fts_cjk_loaded:
  360. return False
  361. insert = self._CHUNK_INSERT_SQL.format(table="messages_fts_cjk", extra=" AND role <> 'tool'")
  362. return self._rebuild_step("fts_cjk_rebuild", [insert], finish=self._fts_cjk_rebuild_finish, progress=progress)
  363. def _rebuild_step(self, prefix: str, insert_sqls: list[str], *, finish, finish_when_empty: bool = False,
  364. progress: Optional[_BackfillProgress] = None) -> bool:
  365. """Shared chunk engine for the base and CJK deferred backfills. ``finish_when_empty``
  366. finalizes a high_water <= 0 marker (empty messages table) instead of leaving it pending."""
  367. high_water_raw = self.get_meta(f"{prefix}_high_water")
  368. if high_water_raw is None:
  369. return False
  370. high_water = int(high_water_raw)
  371. chunk = self._fts_chunk_rows
  372. def _do(conn):
  373. # Re-reading progress inside the BEGIN IMMEDIATE held by _execute_write IS
  374. # the claim: two workers cannot read the same value.
  375. row = _meta_row(conn, f"{prefix}_progress")
  376. if row is None:
  377. return False, 0 # finished (or cleared) by another process
  378. done = int(row[0])
  379. if done >= high_water:
  380. return False, 0
  381. # The window is the next `chunk` messages, not `chunk` ids: an id gap (pruned
  382. # sessions) neither shrinks a chunk nor lets the next one span a million dense ids.
  383. nth = conn.execute("SELECT id FROM messages WHERE id > ? AND id <= ? ORDER BY id LIMIT 1 OFFSET ?",
  384. (done, high_water, chunk - 1)).fetchone()
  385. upper = int(nth[0]) if nth is not None else high_water
  386. covered = chunk if nth is not None else 0
  387. if nth is None and progress is not None: # the last, short window (< chunk rows)
  388. covered = conn.execute("SELECT COUNT(*) FROM messages WHERE id > ? AND id <= ?",
  389. (done, upper)).fetchone()[0]
  390. for sql in insert_sqls:
  391. conn.execute(sql, (done, upper))
  392. # Progress lands in the same transaction as its rows (crash-atomic).
  393. conn.execute("UPDATE state_meta SET value = ? WHERE key = ?", (str(upper), f"{prefix}_progress"))
  394. return upper < high_water, covered
  395. more, covered = self._execute_write(_do)
  396. if progress is not None:
  397. # Counted after the commit: _execute_write may replay _do on contention.
  398. progress.indexed += covered
  399. if more is False:
  400. status = self._rebuild_status(prefix)
  401. if (finish_when_empty and high_water <= 0) or (
  402. status is not None and status["indexed"] >= status["total"]
  403. ):
  404. finish()
  405. return False
  406. return bool(more)
  407. def _fts_teardown_trash_step(self, progress: Optional[_TeardownProgress] = None) -> bool:
  408. """Tear down one chunk (``_fts_chunk_rows`` rows) of a demoted v22 FTS shadow table (a PLAIN
  409. table now); True while work remains. A failed chunk raises (nothing committed).
  410. With *progress* (optimize-storage), the first step on each trash table deletes nothing: it
  411. points *progress* at the table and restarts the chunk size (rows cost ~1000x more in one
  412. shadow table than in another); later steps add the rows they deleted.
  413. Single-column INTEGER-key trash tables (the common shape — FTS shadow tables carry a rowid/integer
  414. PK) are drained with a high-water marker mirroring :meth:`fts_rebuild_step`: each chunk deletes only
  415. rows after the previously-drained key, so the per-chunk scan is bounded instead of re-scanning from
  416. the start of the table every chunk (O(n²) total on large trash tables, #79324). Compound-key tables
  417. keep the chunked ``LIMIT`` delete — they are small by construction.
  418. """
  419. limit = self._fts_chunk_rows
  420. with self._read_ctx() as conn:
  421. trash = [r[0] for r in conn.execute(
  422. "SELECT name FROM sqlite_master WHERE type = 'table' AND name LIKE ? ESCAPE '\\'",
  423. (self._FTS_TRASH_PREFIX.replace("_", "\\_") + "%",),
  424. ).fetchall()]
  425. if trash and progress is not None and progress.table != trash[0]:
  426. self._fts_teardown_start_table(conn, trash, progress)
  427. return True
  428. if not trash:
  429. return False
  430. tbl = trash[0]
  431. def _do(conn):
  432. pk_info = [(r[1], (r[2] or "").upper()) for r in conn.execute(f"PRAGMA table_info({tbl})") if r[5] > 0]
  433. key = ", ".join(name for name, _typ in pk_info) if pk_info else "rowid"
  434. if len(pk_info) == 1 and pk_info[0][1] == "INTEGER":
  435. # High-water drain; marker read/written in the same BEGIN IMMEDIATE as the
  436. # DELETE so concurrent callers claim disjoint ranges. Only INTEGER pks
  437. # anchor the comparison (the TEXT-pk config shadow table falls through).
  438. marker_key = f"fts_teardown_{tbl}_progress"
  439. row = _meta_row(conn, marker_key)
  440. high_water = int(row[0]) if row is not None else 0
  441. # Claim the LAST row of the LIMIT window so a full chunk goes per step.
  442. upper_row = conn.execute(
  443. f"SELECT {key} FROM {tbl} WHERE {key} > ? ORDER BY {key} LIMIT 1 OFFSET ?",
  444. (high_water, limit - 1),
  445. ).fetchone() or conn.execute(f"SELECT MAX({key}) FROM {tbl} WHERE {key} > ?", (high_water,)).fetchone()
  446. if upper_row is None or upper_row[0] is None:
  447. return _drop(conn, marker_key), 0
  448. upper = upper_row[0]
  449. cur = conn.execute(f"DELETE FROM {tbl} WHERE {key} > ? AND {key} <= ?", (high_water, upper))
  450. if cur.rowcount > 0:
  451. self.set_meta(marker_key, str(upper), cursor=conn)
  452. return True, cur.rowcount
  453. # Compound-key or rowid trash table: legacy chunked delete. These shadow tables are small, so
  454. # the quadratic re-scan is not a concern (#79324 keeps the high-water path for the big
  455. # single-key tables).
  456. cur = conn.execute(f"DELETE FROM {tbl} WHERE ({key}) IN (SELECT {key} FROM {tbl} LIMIT {int(limit)})")
  457. return (_drop(conn) if cur.rowcount == 0 else True), cur.rowcount
  458. def _drop(conn, marker_key: Optional[str] = None) -> bool:
  459. """Drained — the DROP is cheap now. True: re-check for more trash."""
  460. conn.execute(f"DROP TABLE IF EXISTS {tbl}")
  461. if marker_key is not None:
  462. _delete_meta(conn, marker_key)
  463. logger.info("Old FTS shadow table %s torn down.", tbl)
  464. return True
  465. more, deleted = self._execute_write(_do)
  466. if progress is not None:
  467. # Counted after the commit: _execute_write may replay _do on contention.
  468. progress.drained += deleted
  469. return bool(more)
  470. def _fts_teardown_start_table(self, conn, trash: list[str], progress: _TeardownProgress) -> None:
  471. """Point *progress* at ``trash[0]`` (its place in the run, the rows left past its resume
  472. marker) and restart the chunk size. One COUNT per table, on the pooled reader *conn*."""
  473. tbl = trash[0]
  474. # Only INTEGER-pk tables (rowid aliases) carry a resume marker; count what is left past it.
  475. marker = _meta_row(conn, f"fts_teardown_{tbl}_progress")
  476. where, params = ("WHERE rowid > ?", (int(marker[0]),)) if marker is not None else ("", ())
  477. table_count = progress.table_count or len(trash)
  478. # COUNT before publishing the table: a failed COUNT must leave progress on the previous
  479. # table so the retry runs this start step again instead of inheriting its totals.
  480. total = int(conn.execute(f"SELECT COUNT(*) FROM {tbl} {where}", params).fetchone()[0]) if progress.counted else 0
  481. progress.table_count, progress.total, progress.drained = table_count, total, 0
  482. progress.table_index = max(1, table_count - len(trash) + 1)
  483. self._fts_chunk_rows = self._FTS_REBUILD_CHUNK_ROWS
  484. progress.table = tbl
  485. def _fts_cjk_reset_if_stale(self) -> None:
  486. """From-scratch rebuild of a stale cjk index (triggers were dropped, gap extent unknown):
  487. drop table + triggers, clear the breadcrumb, recreate (fresh backfill markers)."""
  488. if not self._fts_cjk_loaded:
  489. return
  490. def _do(conn):
  491. if _meta_row(conn, FTS_CJK_STALE_KEY) is None:
  492. return False
  493. for trig in _FTS_CJK_TRIGGERS:
  494. conn.execute(f"DROP TRIGGER IF EXISTS {trig}")
  495. conn.execute("DROP TABLE IF EXISTS messages_fts_cjk")
  496. conn.execute("DROP VIEW IF EXISTS messages_fts_cjk_src")
  497. _delete_meta(conn, FTS_CJK_STALE_KEY, "fts_cjk_rebuild_high_water", "fts_cjk_rebuild_progress")
  498. return True
  499. if self._execute_write(_do):
  500. # Recreate OUTSIDE the write transaction: executescript() implicitly commits.
  501. self._ensure_cjk_schema_committed()
  502. def _ensure_cjk_schema_committed(self) -> None:
  503. with self._lock:
  504. self._ensure_fts_cjk_schema(self._conn)
  505. self._conn.commit()
  506. def _fts_external_index_empty_with_messages(self, conn) -> bool:
  507. """True when the base FTS table indexes nothing while ``messages`` has rows (post-demote
  508. empty-index shape). Caller holds ``self._lock``. docsize is the authoritative "is this
  509. rowid indexed" surface; EXISTS not COUNT(*) because this runs on every writable open."""
  510. try:
  511. if not conn.execute("SELECT EXISTS(SELECT 1 FROM messages)").fetchone()[0]:
  512. return False
  513. return not conn.execute("SELECT EXISTS(SELECT 1 FROM messages_fts_docsize)").fetchone()[0]
  514. except sqlite3.OperationalError:
  515. return False # table absent / FTS disabled mid-init — not this failure class
  516. def _reseed_missing_progress(self, conn) -> None:
  517. """high_water without progress: fts_rebuild_step reads missing progress as "done by
  518. another process" and optimize would no-op then stamp. Reset to known-empty, re-seed.
  519. Truncation goes through FTS5 ``'delete-all'`` (a plain DELETE is O(rows) and corrupts
  520. the index when indexed rows diverged from ``messages``); the backfill worker replays
  521. without an anti-join, so it needs a known-empty index. A missing docsize table counts
  522. as empty."""
  523. if _meta_row(conn, "fts_rebuild_progress") is None:
  524. if not self._fts_index_known_empty(conn):
  525. self._reset_fts_index_to_empty(conn)
  526. self.set_meta("fts_rebuild_progress", "0", cursor=conn)
  527. @staticmethod
  528. def _fts_index_known_empty(conn) -> bool:
  529. """True when the base external-content index holds no rows (a missing table counts as empty)."""
  530. try:
  531. return int(conn.execute("SELECT COUNT(*) FROM messages_fts_docsize").fetchone()[0]) == 0
  532. except sqlite3.OperationalError:
  533. return True
  534. @staticmethod
  535. def _reset_fts_index_to_empty(conn) -> None:
  536. """Truncate the v23 external-content tables via FTS5 ``'delete-all'`` (O(1); a plain DELETE is
  537. O(rows) and corrupts the index when indexed rows diverged from ``messages``)."""
  538. for tbl in ("messages_fts", "messages_fts_trigram"):
  539. with contextlib.suppress(sqlite3.OperationalError): # table absent — already an empty surface
  540. conn.execute(f"INSERT INTO {tbl}({tbl}) VALUES('delete-all')")
  541. def _seed_fts_rebuild_markers(self, conn, *, force: bool = False) -> int:
  542. """Write ``fts_rebuild_high_water`` / ``fts_rebuild_progress`` for a full backfill; returns
  543. the high-water id. Without ``force`` an existing high_water only gets a missing
  544. progress key repaired. Caller holds the write transaction."""
  545. existing_hw = _meta_row(conn, "fts_rebuild_high_water")
  546. if existing_hw is not None and not force:
  547. self._reseed_missing_progress(conn)
  548. return int(existing_hw[0])
  549. hw = conn.execute("SELECT COALESCE(MAX(id), 0) FROM messages").fetchone()[0]
  550. self.set_meta("fts_rebuild_high_water", str(hw), cursor=conn)
  551. self.set_meta("fts_rebuild_progress", "0", cursor=conn)
  552. return int(hw)
  553. def _repair_optimize_bookkeeping(self) -> None:
  554. """Heal interrupted demote/backfill bookkeeping before optimize runs: orphan high_water
  555. gets progress re-seeded; an empty external index with messages and no markers gets
  556. a full backfill claim. Never invents markers on a still-legacy inline DB — optimize
  557. would then skip demote and INSERT against the inline table forever.
  558. Covers two post-#65798 failure classes:
  559. """
  560. def _do(conn):
  561. if _meta_row(conn, "fts_rebuild_high_water") is not None:
  562. self._reseed_missing_progress(conn)
  563. return
  564. if self._db_has_legacy_inline_fts(conn):
  565. return # demote owns marker creation
  566. if self._fts_external_index_empty_with_messages(conn):
  567. _delete_meta(conn, "fts_storage_version")
  568. self._seed_fts_rebuild_markers(conn, force=True)
  569. self._execute_write(_do)
  570. def fts_optimize_available(self) -> bool:
  571. """True when `optimize_fts_storage()` has work: legacy inline FTS or a v23 trigram still
  572. carrying ``tool_calls`` (``_db_needs_fts_storage_upgrade``), an interrupted optimize
  573. (markers/trash), a CJK backfill on this tokenizer-capable host, or an empty external
  574. index without markers. False when FTS5 is unavailable."""
  575. if not self._fts_enabled or self.read_only:
  576. return False
  577. with self._read_ctx() as conn:
  578. return (
  579. self._db_needs_fts_storage_upgrade(conn)
  580. or _meta_row(conn, "fts_rebuild_high_water") is not None # interrupted optimize
  581. # CJK work is only offerable when THIS process can tokenize.
  582. or (self._fts_cjk_loaded and (
  583. _meta_row(conn, "fts_cjk_rebuild_high_water") is not None
  584. or _meta_row(conn, FTS_CJK_STALE_KEY) is not None
  585. ))
  586. or self._has_fts_trash(conn)
  587. or self._fts_external_index_empty_with_messages(conn)
  588. )
  589. def _demote_legacy_fts_to_trash(self) -> int:
  590. """Demote upgrade-eligible FTS vtables and stage their shadow tables for chunked
  591. teardown; returns MAX(messages.id) as the rebuild high water. O(1) schema surgery
  592. — the heavy delete is deferred. Markers land in the same BEGIN IMMEDIATE, BEFORE
  593. the empty v23 schema is created (``executescript`` implicitly COMMITs), closing
  594. the crash window where trash + empty v23 tables exist with no backfill claim."""
  595. def _stage(conn):
  596. self._drop_fts_triggers(conn)
  597. conn.execute("DROP VIEW IF EXISTS messages_fts_trigram_src")
  598. if conn.execute(
  599. "SELECT 1 FROM sqlite_master WHERE type = 'table' AND name IN ('messages_fts', 'messages_fts_trigram') "
  600. "AND sql LIKE 'CREATE VIRTUAL TABLE%' LIMIT 1"
  601. ).fetchone():
  602. conn.execute("PRAGMA writable_schema=ON")
  603. conn.execute(
  604. "DELETE FROM sqlite_master WHERE type = 'table' "
  605. "AND name IN ('messages_fts', 'messages_fts_trigram') AND sql LIKE 'CREATE VIRTUAL TABLE%'"
  606. )
  607. conn.execute("PRAGMA writable_schema=RESET")
  608. for row in conn.execute(
  609. "SELECT name FROM sqlite_master WHERE type = 'table' "
  610. "AND (name LIKE 'messages_fts_%' ESCAPE '\\' "
  611. "OR name LIKE 'messages_fts_trigram_%' ESCAPE '\\') "
  612. # messages_fts_cjk* is an independent v23+ index, not part of the
  613. # demoted legacy layout: fts5's xRename renames the entire shadow
  614. # family in one step, so sweeping the cjk vtable here aborts the
  615. # loop on the next cjk shadow entry and drags _config — needed by
  616. # the vtable constructor — into the trash family (#103647).
  617. "AND name NOT LIKE 'messages\\_fts\\_cjk%' ESCAPE '\\'"
  618. ).fetchall():
  619. conn.execute(f"ALTER TABLE {row[0]} RENAME TO fts_v22_trash_{row[0]}")
  620. # Claim the backfill BEFORE the empty v23 tables exist so a crash before
  621. # schema ensure resumes instead of stamping an empty index.
  622. hw = self._seed_fts_rebuild_markers(conn, force=True)
  623. _delete_meta(conn, "fts_optimize_available")
  624. return hw
  625. hw = int(self._execute_write(_stage))
  626. # Outside the write transaction (executescript commits); markers are durable.
  627. self._ensure_v23_fts_tables("failed to create v23 messages_fts during optimize-storage demote")
  628. return hw
  629. def _ensure_v23_fts_tables(self, failure_message: str) -> None:
  630. """Ensure the v23 base + trigram tables under the lock (IF NOT EXISTS, cheap); raise
  631. *failure_message* without the base table (the backfill loop would retry forever)."""
  632. with self._lock:
  633. base_ok = self._ensure_fts_schema(self._conn, "messages_fts", FTS_SQL)
  634. trigram_ok = self._ensure_fts_schema(self._conn, "messages_fts_trigram", FTS_TRIGRAM_SQL)
  635. self._trigram_available = bool(trigram_ok)
  636. if not base_ok:
  637. raise sqlite3.OperationalError(failure_message)
  638. self._conn.commit()
  639. def _backfill_progress(self, phase: str, prefix: str) -> Optional[_BackfillProgress]:
  640. """Messages at or below *prefix*'s high water, and how many of them the resume marker already
  641. covers (one scan per pass); None when the pass has nothing pending."""
  642. st = self._rebuild_status(prefix)
  643. if st is None:
  644. return None
  645. with self._read_ctx() as conn:
  646. # ``+id`` keeps the planner off the rowid range: counting from a covering index reads
  647. # far fewer pages (1.97M messages, cold: 73 s on the table b-tree, 1.9 s here).
  648. total, done = conn.execute("SELECT COUNT(*), COALESCE(SUM(id <= ?), 0) FROM messages WHERE +id <= ?",
  649. (st["indexed"], st["total"])).fetchone()
  650. return _BackfillProgress(phase=phase, total=int(total), indexed=int(done))
  651. # A chunk that fails this many times in a row ends the run (each lock failure already waited out
  652. # _execute_write's patience); a full disk or I/O error never heals by retrying.
  653. _OPTIMIZE_CHUNK_ATTEMPTS = 3
  654. _VACUUM_TICK_S = 1.0
  655. # How often the side thread re-scans for another process holding state.db (~15 ms on Linux).
  656. _HOLDER_SCAN_INTERVAL_S = 2.0
  657. def _optimize_drive(self, step: Callable[[], bool], info: Optional[Callable[[], dict[str, Any]]],
  658. emit: Callable[[dict[str, Any]], None], watch: _HolderWatch) -> None:
  659. """Run *step* to completion, emitting ``info()`` (plus ``paced``) after every chunk. Back to
  660. back while no other process holds state.db (see the chunk engine note in hermes_state_fts);
  661. while *watch* sees a holder, the duty cycle (short chunks + a pause) keeps its writes moving.
  662. Chunk size adapts to its measured cost; a step may restart it (teardown on each new table)."""
  663. self._fts_chunk_rows = self._FTS_REBUILD_CHUNK_ROWS
  664. failures = 0
  665. while True:
  666. t0 = time.monotonic()
  667. try:
  668. more = step()
  669. except sqlite3.OperationalError as exc:
  670. failures += 1
  671. if failures >= self._OPTIMIZE_CHUNK_ATTEMPTS:
  672. raise
  673. logger.warning("FTS optimize chunk failed (attempt %d, retrying): %s", failures, exc)
  674. time.sleep(0.25 * failures)
  675. continue
  676. failures = 0
  677. if not more:
  678. return
  679. cost, paced = time.monotonic() - t0, watch.held
  680. if info is not None:
  681. emit({**info(), "paced": paced})
  682. target = self._FTS_REBUILD_PACED_TARGET_S if paced else self._FTS_REBUILD_CHUNK_TARGET_S
  683. self._fts_chunk_rows = self._next_chunk_rows(self._fts_chunk_rows, cost, target)
  684. if paced:
  685. time.sleep(max(self._FTS_REBUILD_MIN_PAUSE, cost * self._FTS_REBUILD_DUTY_FACTOR))
  686. def _optimize_vacuum(self, tick: Optional[Callable[[dict[str, Any]], None]] = None) -> bool:
  687. """Phase 3: reclaim freed pages to the OS. False when VACUUM failed (usually no free disk
  688. for its temp copy; a later VACUUM reclaims). VACUUM is one statement that runs for minutes
  689. on a big store, so *tick* gets a :class:`_VacuumProgress` payload at once and every
  690. ``_VACUUM_TICK_S`` from a side thread, which is stopped and joined before this returns."""
  691. ticker, stop = None, threading.Event()
  692. if tick is not None:
  693. with self._read_ctx() as conn:
  694. page_size, pages, free = (conn.execute(f"PRAGMA {p}").fetchone()[0]
  695. for p in ("page_size", "page_count", "freelist_count"))
  696. state = _VacuumProgress(size_bytes=pages * page_size, estimated_bytes=(pages - free) * page_size)
  697. def _tick() -> None:
  698. tick(state.info())
  699. while not stop.wait(self._VACUUM_TICK_S):
  700. tick(state.info())
  701. # copy_context().run: the ticker keeps the caller's profile scope (HX012).
  702. ticker = threading.Thread(target=contextvars.copy_context().run, args=(_tick,),
  703. name="optimize-vacuum-ticker", daemon=True)
  704. ticker.start()
  705. try:
  706. with self._lock:
  707. self._conn.execute("VACUUM")
  708. vacuum_ok = True
  709. except sqlite3.OperationalError as exc:
  710. logger.warning("VACUUM after FTS optimize failed: %s", exc)
  711. vacuum_ok = False
  712. finally:
  713. stop.set()
  714. if ticker is not None:
  715. ticker.join()
  716. # Best-effort WAL fold-back, REFUSED (SQLITE_BUSY) while another connection holds a
  717. # read-mark (callers size via logical_size_bytes, not stat()). PASSIVE, never TRUNCATE:
  718. # a TRUNCATE reset from a transient CLI would race a live writer.
  719. try:
  720. with self._lock:
  721. # Best-effort: fold the WAL back into the main file so the on-disk size settles now rather
  722. # than at close(). Callers must therefore NOT size the result by stat()ing the file; use
  723. # :meth:`logical_size_bytes`, which is truthful immediately regardless of readers. See
  724. # #45383.
  725. self._conn.execute("PRAGMA wal_checkpoint(PASSIVE)")
  726. except Exception as exc:
  727. logger.debug("WAL checkpoint (PASSIVE) after optimize VACUUM failed: %s", exc)
  728. return vacuum_ok
  729. def _optimize_settle(self, conn) -> Optional[str]:
  730. """Phase 4 (inside the write transaction, so a concurrent writer cannot race a stamp past
  731. incomplete work): stamp the FTS layout (source of truth for "optimized"), clear the
  732. "available" flag, advance a lagging schema_version. Returns a refusal reason or None.
  733. Refuses while optimize work remains; an empty base index against non-empty messages
  734. also refuses (settling there meant permanent search-index loss)."""
  735. if _meta_row(conn, "fts_rebuild_high_water") is not None:
  736. return "backfill_incomplete"
  737. if self._has_fts_trash(conn):
  738. return "teardown_incomplete"
  739. if self._fts_external_index_empty_with_messages(conn):
  740. return "backfill_incomplete"
  741. current = fts_layout_current(conn)
  742. if not current and fts_layout_drift(conn):
  743. return "layout_drift"
  744. # Not current with no older layout left means a definition this code does not recognise (a newer
  745. # release's): the work is done, but the stamp is not this release's to write.
  746. if current:
  747. self.set_meta("fts_storage_version", str(FTS_STORAGE_VERSION), cursor=conn)
  748. _delete_meta(conn, "fts_optimize_available")
  749. conn.execute("UPDATE schema_version SET version = ? WHERE version < ?", (SCHEMA_VERSION, SCHEMA_VERSION))
  750. return None
  751. def optimize_fts_storage(
  752. self, *, progress_cb: Optional[Callable[[dict[str, Any]], None]] = None, vacuum: bool = True,
  753. ) -> dict[str, Any]:
  754. """Repair an older FTS layout into the current v23 shape, foreground and to completion:
  755. legacy-v22 inline -> external-content, or a v23 ``messages_fts_trigram`` that still stores
  756. ``tool_calls``. Re-running resumes; a chunk that keeps failing raises (nothing is lost).
  757. ``progress_cb`` receives {"phase", "percent", "indexed", "total", "paced"} after every chunk
  758. (``backfill``/``backfill_cjk`` count messages; plus :meth:`_TeardownProgress.info` fields)
  759. and every second of the VACUUM (:meth:`_VacuumProgress.info`). Whenever another process holds
  760. state.db (re-scanned every ``_HOLDER_SCAN_INTERVAL_S``) chunks are paced so its writes get
  761. through. A missing trigram tokenizer is not fatal (CJK falls back to LIKE)."""
  762. if not self._fts_enabled:
  763. return {"ok": False, "reason": "fts5_unavailable"}
  764. if self.read_only:
  765. return {"ok": False, "reason": "read_only"}
  766. # Heal bookkeeping BEFORE deciding whether to demote again.
  767. self._repair_optimize_bookkeeping()
  768. with self._lock:
  769. needs_storage_upgrade = self._db_needs_fts_storage_upgrade(self._conn)
  770. pending = self.get_meta("fts_rebuild_high_water") is not None
  771. if needs_storage_upgrade and not pending:
  772. self._demote_legacy_fts_to_trash()
  773. elif pending and not needs_storage_upgrade:
  774. # Resume mid-demote: the process may have died between the staged demote
  775. # commit and schema ensure.
  776. self._ensure_v23_fts_tables("failed to re-create v23 messages_fts on optimize-storage resume")
  777. # A stale CJK index can only be recovered from scratch; then ensure table +
  778. # markers exist (a v23 DB gaining the cjk index for the first time).
  779. self._fts_cjk_reset_if_stale()
  780. if self._fts_cjk_loaded:
  781. self._ensure_cjk_schema_committed()
  782. emit = progress_cb or (lambda _info: None)
  783. teardown = _TeardownProgress(counted=progress_cb is not None)
  784. passes = (("backfill", "fts_rebuild", self.fts_rebuild_step), ("backfill_cjk", "fts_cjk_rebuild",
  785. self.fts_cjk_rebuild_step))
  786. with _HolderWatch(self._foreign_state_db_holders, self._HOLDER_SCAN_INTERVAL_S) as watch:
  787. # Phase 1: base backfill; 1b: CJK-bigram backfill (own marker pair, own progress line).
  788. for phase, prefix, step in passes:
  789. bf = self._backfill_progress(phase, prefix) if progress_cb else None
  790. if bf is not None:
  791. emit({**bf.info(), "paced": watch.held})
  792. self._optimize_drive(partial(step, bf) if bf else step, bf.info if bf else None, emit, watch)
  793. # Phase 2: tear down the demoted legacy shadow tables in chunks.
  794. self._optimize_drive(partial(self._fts_teardown_trash_step, teardown), teardown.info, emit, watch)
  795. with self._read_ctx() as conn:
  796. still_pending = _meta_row(conn, "fts_rebuild_high_water") is not None
  797. still_trash = self._has_fts_trash(conn)
  798. empty_index = self._fts_external_index_empty_with_messages(conn)
  799. if still_pending or still_trash or empty_index:
  800. reason = "backfill_incomplete" if still_pending or empty_index else "teardown_incomplete"
  801. logger.warning("FTS storage optimization did not settle (%s): pending=%s trash=%s empty_index=%s",
  802. reason, still_pending, still_trash, empty_index)
  803. return {"ok": False, "reason": reason, "vacuumed": None}
  804. vacuum_ok, vacuum_skipped = None, False
  805. if vacuum and self._foreign_state_db_holders():
  806. # VACUUM holds the write lock for minutes; a live writer would time out mid-append.
  807. logger.info("Skipping VACUUM after FTS optimize: another process is using %s", self.db_path)
  808. vacuum_ok, vacuum_skipped = False, True
  809. elif vacuum:
  810. vacuum_ok = self._optimize_vacuum(progress_cb)
  811. refusal = self._execute_write(self._optimize_settle)
  812. if refusal is not None:
  813. # A concurrent process changed state since the pre-vacuum check; a re-run can still settle.
  814. logger.warning("FTS storage optimization settle refused (%s)", refusal)
  815. return {"ok": False, "reason": refusal, "vacuumed": vacuum_ok}
  816. emit({"phase": "done", "percent": 100, "indexed": 0, "total": 0})
  817. logger.info("FTS storage optimization complete (layout v%d).", FTS_STORAGE_VERSION)
  818. return {"ok": True, "vacuumed": vacuum_ok, **({"vacuum_skipped": "in_use"} if vacuum_skipped else {})}
  819. # ── Read views ─────────────────────────────────────────────────────────
  820. def get_anchored_view(
  821. self, session_id: str, around_message_id: int, window: int = 5, bookend: int = 3,
  822. keep_roles: Optional[tuple[str, ...]] = ("user", "assistant")) -> dict[str, Any]:
  823. """Anchored window (``get_messages_around``) plus session bookends, so one call yields the
  824. goal and the resolution of a long session. ``window`` is filtered to ``keep_roles``
  825. (None disables) EXCEPT the anchor; ``bookend_start`` / ``bookend_end`` are the
  826. first/last ``bookend`` non-empty-content messages with ids strictly outside the
  827. window (empty when it overlaps the head/tail). Empty result when the anchor isn't
  828. in the session."""
  829. bookend = max(bookend, 0)
  830. primitive = self.get_messages_around(session_id, around_message_id, window=window)
  831. window_rows = primitive["window"]
  832. if not window_rows:
  833. return {"window": [], "messages_before": 0, "messages_after": 0, "bookend_start": [], "bookend_end": []}
  834. filtered_window = window_rows
  835. if keep_roles is not None:
  836. keep_set = set(keep_roles)
  837. filtered_window = [m for m in window_rows if m.get("id") == around_message_id or m.get("role") in keep_set]
  838. bookend_start_rows: list[Any] = []
  839. bookend_end_rows: list[Any] = []
  840. if bookend > 0:
  841. role_clause = "" if keep_roles is None else f" AND role IN ({','.join('?' for _ in keep_roles)})"
  842. role_params = [] if keep_roles is None else list(keep_roles)
  843. with self._read_ctx() as conn:
  844. def _bookend(op: str, boundary_id: int, order: str):
  845. return conn.execute(
  846. f"SELECT * FROM messages "
  847. f"WHERE session_id = ? AND id {op} ?{role_clause} "
  848. f"AND length(content) > 0 "
  849. f"ORDER BY id {order} LIMIT ?",
  850. (session_id, boundary_id, *role_params, bookend),
  851. ).fetchall()
  852. bookend_start_rows = _bookend("<", window_rows[0]["id"], "ASC")
  853. # End rows come back DESC for the LIMIT cap; flip to ASC.
  854. bookend_end_rows = list(reversed(_bookend(">", window_rows[-1]["id"], "DESC")))
  855. def _hydrate(row) -> dict[str, Any]:
  856. return self._row_to_message_dict(row, warn_context="get_anchored_view", summary_flag=False)
  857. return {
  858. "window": filtered_window, "messages_before": primitive["messages_before"],
  859. "messages_after": primitive["messages_after"],
  860. "bookend_start": [_hydrate(r) for r in bookend_start_rows],
  861. "bookend_end": [_hydrate(r) for r in bookend_end_rows],
  862. }
  863. def list_recent_user_messages(
  864. self, session_id: str, limit: int = 20, include_inactive: bool = False) -> list[dict[str, Any]]:
  865. """The *limit* most-recent real user turns, newest first, as ``{id, timestamp, preview}``
  866. (80 chars, whitespace collapsed); used by /rewind and ``/undo [N]``. Bookkeeping rows
  867. (``display_kind`` set) are excluded. Legacy compaction handoffs are role='user' rows
  868. with NO display_kind — invisible to SQL — so fetch with headroom and drop them in the
  869. decode loop; otherwise ``/undo N`` pairs an in-memory count that excludes handoffs
  870. with a DB pick that includes them."""
  871. active_clause = "" if include_inactive else " AND active = 1"
  872. # A /steer row is typed for the renderer but is human input: keep it so the DB pick agrees
  873. # with the in-memory user_originated_turn_view count.
  874. display_clause = " AND (display_kind IS NULL OR display_kind = '' OR display_kind = 'steer')"
  875. with self._read_ctx() as conn:
  876. rows = conn.execute(
  877. "SELECT id, timestamp, content FROM messages WHERE session_id = ? AND role = 'user'"
  878. f"{active_clause}{display_clause} "
  879. "ORDER BY id DESC LIMIT ?",
  880. (session_id, int(limit) * 2 + 5),
  881. ).fetchall()
  882. from agent.context_compressor import ContextCompressor
  883. result: list[dict[str, Any]] = []
  884. for row in rows:
  885. if len(result) >= int(limit):
  886. break
  887. decoded = self._decode_content(row["content"])
  888. if ContextCompressor._is_context_summary_content(decoded):
  889. continue # compaction handoff — never a user-originated turn
  890. if isinstance(decoded, str): # a /skill turn embeds the whole skill body; show what was typed
  891. preview = describe_skill_invocation(decoded) or decoded
  892. else:
  893. preview = _flatten_text(decoded)
  894. preview = " ".join(preview.split())
  895. if len(preview) > 80:
  896. preview = preview[:77] + "..."
  897. result.append({"id": row["id"], "timestamp": row["timestamp"], "preview": preview})
  898. return result
  899. # ── Query analysis ─────────────────────────────────────────────────────
  900. @staticmethod
  901. def _sanitize_fts5_query(query: str) -> str:
  902. """Sanitize user input for FTS5 MATCH (raw special characters raise): preserve paired
  903. quoted phrases, strip unmatched special characters, and quote hyphenated/dotted
  904. terms so FTS5 matches them as phrases (``chat-send``, ``P2.2``, ``my-app.config.ts``)."""
  905. # Cap before any regex processing so adversarial input stays bounded.
  906. query = query[:MAX_FTS5_QUERY_CHARS]
  907. # 1. Protect balanced quoted phrases via numbered placeholders (``"[^"]*"`` pairs
  908. # left-to-right without backtracking); a leftover unmatched quote becomes whitespace.
  909. _quoted_parts: list = []
  910. def _hold(m: re.Match[str]) -> str:
  911. _quoted_parts.append(m.group(0))
  912. return f"\x00Q{len(_quoted_parts) - 1}\x00"
  913. sanitized = _QUOTED_PHRASE_RE.sub(_hold, query).replace('"', " ")
  914. # 2. Strip FTS5-special characters (an unquoted ``TODO: fix`` parses as
  915. # ``column:term``). ``%`` is only spared for the CJK LIKE fallback.
  916. sanitized = _FTS5_SPECIAL_RE.sub(" ", sanitized)
  917. if "%" in sanitized and not SessionSearchMixin._contains_cjk(sanitized):
  918. sanitized = sanitized.replace("%", " ")
  919. # 3. Collapse repeated * and drop leading * (prefix needs a char).
  920. sanitized = re.sub(r"\*+", "*", sanitized)
  921. sanitized = re.sub(r"(^|\s)\*", r"\1", sanitized)
  922. # 4. Drop dangling boolean operators at start/end (syntax errors).
  923. sanitized = re.sub(r"(?i)^(AND|OR|NOT)\b\s*", "", sanitized.strip())
  924. sanitized = re.sub(r"(?i)\s+(AND|OR|NOT)\s*$", "", sanitized.strip())
  925. # 5. Quote dotted/hyphenated/underscored terms in ONE pass (sequential passes
  926. # double-quote ``my-app.config``).
  927. sanitized = re.sub(r"\b(\w+(?:[._-]\w+)+)\b", r'"\1"', sanitized)
  928. # 6. Restore preserved quoted phrases.
  929. for i, quoted in enumerate(_quoted_parts):
  930. sanitized = sanitized.replace(f"\x00Q{i}\x00", quoted)
  931. return sanitized.strip()
  932. @staticmethod
  933. def _contains_cjk(text: str) -> bool:
  934. return any(_is_cjk(ord(ch)) for ch in text)
  935. @staticmethod
  936. def _count_cjk(text: str) -> int:
  937. return sum(1 for ch in text if _is_cjk(ord(ch)))
  938. @staticmethod
  939. def _has_lone_cjk_run(query: str) -> bool:
  940. """True when any maximal CJK run is a single char: the cjk-bigram index stores unigrams
  941. only for isolated chars, so such a term can't match inside longer runs — keep LIKE."""
  942. run = 0
  943. for ch in query:
  944. if _is_cjk(ord(ch)):
  945. run += 1
  946. else:
  947. if run == 1:
  948. return True
  949. run = 0
  950. return run == 1
  951. @staticmethod
  952. def _or_relaxed_query(query: str) -> Optional[str]:
  953. """The sanitized implicit-AND query rewritten as an any-term OR query, or ``None`` when
  954. relaxation does not apply: fewer than two searchable units (a single term cannot relax)
  955. or explicit ``OR``/``NOT`` (the caller expressed exact semantics). Quoted phrases stay
  956. whole units: ``"docker networking" tls`` -> ``"docker networking" OR tls``."""
  957. units: list[str] = []
  958. for raw_token in _LIKE_TOKEN_RE.findall(query):
  959. upper = raw_token.upper()
  960. if upper in {"OR", "NOT"}:
  961. return None
  962. if upper != "AND":
  963. units.append(raw_token)
  964. return " OR ".join(units) if len(units) >= 2 else None
  965. @staticmethod
  966. def _trigram_eligible_tokens(query: str) -> bool:
  967. """True when every non-operator token is >=3 chars: a shorter token produces no
  968. trigrams, and with FTS5's implicit AND one such token empties the whole MATCH."""
  969. tokens = _non_operator_tokens(query.strip('"').strip())
  970. return bool(tokens) and all(len(t) >= 3 for t in tokens)
  971. @classmethod
  972. def _has_short_cjk_token(cls, raw_query: str) -> bool:
  973. """True when any non-operator CJK token has fewer than 3 CJK chars — the trigram
  974. tokenizer needs >=3 per token, so such a query must take the LIKE route."""
  975. return any(cls._count_cjk(t) < 3 for t in _non_operator_tokens(raw_query) if cls._contains_cjk(t))
  976. def _trigram_route_ok(self, raw_query: str) -> bool:
  977. """Per-token CJK length gate for the trigram index: ``广西 OR 桂林 OR 漓江`` has 6
  978. CJK chars total but 2 per token, so trigram returns 0."""
  979. return (self._count_cjk(raw_query) >= 3 and not self._has_short_cjk_token(raw_query)
  980. and self._trigram_available)
  981. def _describe_search_path(self, query: str) -> str:
  982. """Best-effort name of the routing path a query takes (log-only)."""
  983. try:
  984. if self._fts_stale:
  985. return "like_scan_fts_stale"
  986. sanitized = self._sanitize_fts5_query(query or "")
  987. if not sanitized:
  988. return "empty"
  989. if not self._contains_cjk(sanitized):
  990. return "fts5"
  991. raw = sanitized.strip('"').strip()
  992. if self._fts_cjk_available and not self._has_lone_cjk_run(raw):
  993. return "fts_cjk"
  994. return "trigram" if self._trigram_route_ok(raw) else "like_scan"
  995. except Exception:
  996. return "unknown"
  997. # ── Query builders / runners ───────────────────────────────────────────
  998. @staticmethod
  999. def _fts_match_sql(table: str, match_query: str, order_by_sql: str, *, limit: int, offset: int,
  1000. **filters) -> tuple[str, list]:
  1001. """MATCH query + params against one FTS5 index joined to messages/sessions."""
  1002. where = [f"{table} MATCH ?"]
  1003. params: list = [match_query]
  1004. _search_filter_clauses(where, params, **filters)
  1005. params.extend([limit, offset])
  1006. sql = _search_select_sql(
  1007. f"snippet({table}, -1, '>>>', '<<<', '...', 40) AS snippet",
  1008. f"{table}\n JOIN messages m ON m.id = {table}.rowid", where, order_by_sql, "LIMIT ? OFFSET ?",
  1009. )
  1010. return sql, params
  1011. def _match_rows(self, table: str, match_query: str, order_by_sql: str, *, fail_open: Optional[str] = None,
  1012. operational_debug: Optional[str] = None, **kwargs) -> Optional[list[dict[str, Any]]]:
  1013. """Run one MATCH against *table*; ``None`` when the query cannot execute (tokenizer /
  1014. syntax) so the caller falls back. *fail_open* names the index for the
  1015. substring-capable routes: a corruption-class ``DatabaseError`` there detaches the
  1016. derived indexes (``_enter_fts_fail_open``) and answers from canonical rows — a live
  1017. search never runs the unbounded rebuild. Other ``DatabaseError``s propagate."""
  1018. sql, params = self._fts_match_sql(table, match_query, order_by_sql, **kwargs)
  1019. try:
  1020. return [dict(row) for row in self._read_all(sql, params)]
  1021. except sqlite3.OperationalError:
  1022. if operational_debug:
  1023. logger.debug(operational_debug, exc_info=True)
  1024. return None
  1025. except sqlite3.DatabaseError as exc:
  1026. if fail_open is None or not self._enter_fts_fail_open(exc):
  1027. raise
  1028. logger.warning(
  1029. "%s FTS search hit a corruption error (%s); detached FTS and falling back to canonical LIKE.",
  1030. fail_open, exc)
  1031. return None
  1032. def _like_rows(self, where: list[str], params: list, *, order_by: str, limit_sql: str) -> list[dict[str, Any]]:
  1033. """Canonical-table LIKE scan; ``params[0]`` is the snippet anchor term."""
  1034. sql = _search_select_sql(_LIKE_SNIPPET_SQL, "messages m", where, order_by, limit_sql)
  1035. return [dict(row) for row in self._read_all(sql, params)]
  1036. @staticmethod
  1037. def _compile_like_boolean_query(query: str) -> tuple[str, list[Any], Optional[str]]:
  1038. """Compile the supported FTS boolean subset into LIKE predicates: terms within an OR
  1039. group are ANDed (FTS5's implicit conjunction) and ``NOT`` negates the next term."""
  1040. groups: list[list[tuple[str, bool]]] = [[]]
  1041. negate_next = False
  1042. for raw_token in _LIKE_TOKEN_RE.findall(query):
  1043. operator = raw_token.upper()
  1044. if operator == "OR":
  1045. if groups[-1]:
  1046. groups.append([])
  1047. negate_next = False
  1048. continue
  1049. if operator in {"AND", "NEAR"}:
  1050. continue
  1051. if operator == "NOT":
  1052. negate_next = True
  1053. continue
  1054. term = raw_token.strip('"').strip("*").strip()
  1055. if term:
  1056. groups[-1].append((term, negate_next))
  1057. negate_next = False
  1058. compiled_groups: list[str] = []
  1059. params: list[Any] = []
  1060. snippet_term: Optional[str] = None
  1061. for group in groups:
  1062. if not group or not any(not negated for _, negated in group):
  1063. continue
  1064. clauses: list[str] = []
  1065. for term, negated in group:
  1066. clauses.append(f"NOT {_LIKE_COALESCED_COLUMN_SQL}" if negated else _LIKE_COALESCED_COLUMN_SQL)
  1067. params.extend(_like_params(term))
  1068. if snippet_term is None and not negated:
  1069. snippet_term = term
  1070. compiled_groups.append(f"({' AND '.join(clauses)})")
  1071. return " OR ".join(compiled_groups), params, snippet_term
  1072. def _search_messages_like_fallback(
  1073. self, query: str, *, limit: int, offset: int, sort: Optional[str], **filters) -> list[dict[str, Any]]:
  1074. """Search canonical messages while derived FTS state is stale."""
  1075. predicate, params, snippet_term = self._compile_like_boolean_query(query)
  1076. if not predicate or snippet_term is None:
  1077. return []
  1078. where = [f"({predicate})"]
  1079. _search_filter_clauses(where, params, **filters)
  1080. order = "ASC" if isinstance(sort, str) and sort.strip().lower() == "oldest" else "DESC"
  1081. return self._like_rows(where, [snippet_term, *params, limit, offset],
  1082. order_by=f"ORDER BY m.timestamp {order}, m.id {order}", limit_sql="LIMIT ? OFFSET ?")
  1083. def _refresh_fts_stale_state(self) -> None:
  1084. """Observe fail-open initiated by another process sharing state.db."""
  1085. if self._fts_stale or not self._fts_enabled:
  1086. return
  1087. try:
  1088. stale = self._read_one("SELECT 1 FROM state_meta WHERE key = ? LIMIT 1", (FTS_STALE_KEY,))
  1089. except sqlite3.Error:
  1090. return
  1091. if stale is not None:
  1092. self._fts_stale = True
  1093. self._fts_enabled = self._trigram_available = self._fts_cjk_available = False
  1094. def _finalize_search_matches(
  1095. self, matches: list[dict[str, Any]], result_fields: Optional[Collection[str]] = None) -> list[dict[str, Any]]:
  1096. """Attach neighboring messages in bounded batches, only when context is requested."""
  1097. if result_fields is None or "context" in result_fields:
  1098. for start in range(0, len(matches), 500):
  1099. batch = matches[start:start + 500]
  1100. contexts = {match["id"]: [] for match in batch}
  1101. try:
  1102. sql = _CONTEXT_WINDOW_SQL.format(ids=",".join("?" for _ in contexts))
  1103. with self._read_ctx() as conn:
  1104. rows = conn.execute(sql, list(contexts)).fetchall()
  1105. for row in rows:
  1106. contexts[row["match_id"]].append(row)
  1107. except Exception:
  1108. contexts = {}
  1109. for match in batch:
  1110. try:
  1111. match["context"] = [
  1112. {"role": row["role"], "content": _flatten_text(self._decode_content(row["content"]))[:200]}
  1113. for row in contexts.get(match["id"], [])]
  1114. except Exception:
  1115. match["context"] = []
  1116. # No route selects full content; the pop guards any future one that does.
  1117. for match in matches:
  1118. match.pop("content", None)
  1119. if result_fields is not None:
  1120. matches = [{field: match[field] for field in result_fields if field in match} for match in matches]
  1121. return matches
  1122. # ── search_messages ────────────────────────────────────────────────────
  1123. def search_messages(
  1124. self, query: str, source_filter: list[str] | None = None, exclude_sources: list[str] | None = None,
  1125. role_filter: list[str] | None = None, limit: int = 20, offset: int = 0, sort: str | None = None,
  1126. include_inactive: bool = False, fields: Optional[Collection[str]] = None,
  1127. after_ts: Optional[int] = None, before_ts: Optional[int] = None,
  1128. ) -> list[dict[str, Any]]:
  1129. """:meth:`_search_messages_impl` plus one log line per slow search with the routing
  1130. path taken. Threshold HERMES_SEARCH_SLOW_MS (default 1000; 0 logs every call)."""
  1131. started = time.time()
  1132. rows = None
  1133. try:
  1134. rows = self._search_messages_impl(
  1135. query, source_filter=source_filter, exclude_sources=exclude_sources, role_filter=role_filter,
  1136. limit=limit, offset=offset, sort=sort, include_inactive=include_inactive, fields=fields,
  1137. after_ts=after_ts, before_ts=before_ts)
  1138. return rows
  1139. finally:
  1140. elapsed_ms = (time.time() - started) * 1000.0
  1141. if elapsed_ms >= _search_slow_ms():
  1142. logger.info("slow session search: path=%s elapsed=%.0fms rows=%s query=%r",
  1143. self._describe_search_path(query), elapsed_ms, len(rows) if rows is not None else "err",
  1144. query[: 200])
  1145. def _search_messages_impl(
  1146. self, query: str, source_filter: list[str] | None = None, exclude_sources: list[str] | None = None,
  1147. role_filter: list[str] | None = None, limit: int = 20, offset: int = 0, sort: str | None = None,
  1148. include_inactive: bool = False, fields: Optional[Collection[str]] = None,
  1149. after_ts: Optional[int] = None, before_ts: Optional[int] = None,
  1150. ) -> list[dict[str, Any]]:
  1151. """FTS5 search across session messages (keywords, ``"phrases"``, AND/OR/NOT, ``prefix*``).
  1152. Returns snippet + session metadata + 1-message context per hit; ``fields`` selects a
  1153. projection. ``sort``: None = BM25 rank; "newest"/"oldest" = timestamp then rank (the
  1154. CJK LIKE fallback ignores it). Rewound rows (``active=0, compacted=0``) are excluded
  1155. by default; compaction-archived rows ARE included; ``include_inactive`` = every row.
  1156. ``after_ts``/``before_ts`` bound ``sessions.started_at`` on every route (FTS5, CJK,
  1157. trigram, LIKE fallback, unindexed-gap supplement)."""
  1158. result_fields = self._search_message_fields(fields)
  1159. if not query or not query.strip():
  1160. return []
  1161. query = self._sanitize_fts5_query(query)
  1162. if not query:
  1163. return []
  1164. filters = dict(include_inactive=include_inactive, source_filter=source_filter,
  1165. exclude_sources=exclude_sources, role_filter=role_filter,
  1166. after_ts=after_ts, before_ts=before_ts)
  1167. # New oversized tool results index only a bounded prefix; an explicit tool-role search is the
  1168. # opt-in full-body path and scans canonical rows via LIKE.
  1169. if role_filter and "tool" in role_filter:
  1170. matches = self._search_messages_like_fallback(query, limit=limit, offset=offset, sort=sort, **filters)
  1171. return self._finalize_search_matches(matches, result_fields=result_fields)
  1172. self._refresh_fts_stale_state()
  1173. if self._fts_stale:
  1174. matches = self._search_messages_like_fallback(query, limit=limit, offset=offset, sort=sort, **filters)
  1175. return self._finalize_search_matches(matches, result_fields=result_fields)
  1176. if not self._fts_enabled:
  1177. return []
  1178. order_by_sql = _FTS_ORDER_BY.get(sort.strip().lower() if isinstance(sort, str) else None, "ORDER BY rank")
  1179. route = dict(order_by_sql=order_by_sql, limit=limit, offset=offset, **filters)
  1180. # Tool rows and FTS_TRIGRAM_EXCLUDED_SOURCES sessions are excluded from the trigram/cjk
  1181. # indexes (see FTS_TRIGRAM_SQL); an explicit filter for them must scan the base table.
  1182. wants_unindexed_rows = (bool(role_filter) and "tool" in role_filter) or (
  1183. bool(source_filter) and any(src in FTS_TRIGRAM_EXCLUDED_SOURCES for src in source_filter))
  1184. is_cjk = self._contains_cjk(query)
  1185. if is_cjk:
  1186. matches = self._search_cjk(query, wants_unindexed_rows, route)
  1187. else:
  1188. sql, params = self._fts_match_sql("messages_fts", query, **route)
  1189. try:
  1190. matches = [dict(row) for row in self._read_all(sql, params)]
  1191. except sqlite3.OperationalError:
  1192. return [] # FTS5 syntax error despite sanitization
  1193. except sqlite3.DatabaseError as exc:
  1194. # Corruption parent class: detach the derived indexes and answer from
  1195. # canonical rows; repair paths own the rebuild.
  1196. # A corrupt FTS index raises the malformed / "fts5: corrupt structure record" class on the
  1197. # MATCH read, the same class the write path handles (#66296). OperationalError (query
  1198. # syntax) is a subclass caught above; this arm is the corruption parent. The existing
  1199. # stale-open/repair paths retain rebuild ownership.
  1200. if not self._enter_fts_fail_open(exc):
  1201. raise
  1202. matches = self._search_messages_like_fallback(query, limit=limit, offset=offset, sort=sort, **filters)
  1203. # Deferred-rebuild supplement: while the backfill is pending the FTS indexes miss
  1204. # the (progress, high_water] gap; top up with a bounded LIKE scan so old messages
  1205. # never vanish mid-rebuild. Cost decays to zero as the backfill advances.
  1206. if self.fts_rebuild_status() is not None and len(matches) < limit:
  1207. try:
  1208. gap_matches = self._search_unindexed_gap(query, limit - len(matches), **filters)
  1209. seen_ids = {m["id"] for m in matches}
  1210. matches.extend(m for m in gap_matches if m["id"] not in seen_ids)
  1211. except sqlite3.OperationalError as exc:
  1212. logger.debug("Unindexed-gap supplement skipped: %s", exc)
  1213. # unicode61 puts no boundary between Latin and adjacent CJK ("修改youer服务端" is
  1214. # one token, so MATCH "youer" misses). On a zero-result Latin miss retry the
  1215. # substring-capable indexes: cjk first (exact ranked match), then trigram (>=3-char
  1216. # tokens). Gated on a miss so hits keep their ranking ("cat" may then match
  1217. # "concatenate"). Skipped for role='tool' (both indexes exclude tool rows).
  1218. if not matches and not is_cjk and not (bool(role_filter) and "tool" in role_filter):
  1219. fb_query = _quote_fts_tokens(query.strip('"').strip())
  1220. # ── CJK-bigram route (messages_fts_cjk, cjk_unicode61) ────── When the bigram index is
  1221. # available it serves EVERY CJK query shape the legacy code split between trigram (>=3
  1222. # chars/token) and LIKE full scans (1-2 char tokens) — the whole point of the index (PR #65544).
  1223. # Exceptions stay on the legacy routes: - role_filter=['tool'] queries (tool rows aren't in the
  1224. # cjk index, same exclusion as trigram), - queries containing a LONE 1-char CJK run: the index
  1225. # stores bigrams for runs >=2, so a single-char term can only match isolated chars — LIKE
  1226. # substring semantics are broader.
  1227. if self._fts_cjk_available:
  1228. matches = self._match_rows("messages_fts_cjk", fb_query, **route) or matches
  1229. if not matches and self._trigram_available and self._trigram_eligible_tokens(query):
  1230. matches = self._match_rows("messages_fts_trigram", fb_query, **route) or matches
  1231. # OR-relaxed retry: the implicit AND between terms means a paraphrased multi-word query
  1232. # misses a stored sentence that lacks even ONE word ("when does Sarah like her standup
  1233. # scheduled" vs "Sarah prefers the standup meeting scheduled ... Thursday mornings"). Once
  1234. # the exact query and the substring fallbacks all miss, retry the unicode61 index matching
  1235. # ANY term. The caller's ``sort`` still applies (``route`` carries order_by_sql): rank order
  1236. # puts rows covering more terms first, newest/oldest keep their timestamp order. Gated on a
  1237. # zero-result miss so hits keep exact-match semantics; explicit OR/NOT, single-term and
  1238. # CJK-routed queries are left alone.
  1239. if not matches and not is_cjk and not self._fts_stale:
  1240. relaxed = self._or_relaxed_query(query)
  1241. if relaxed is not None:
  1242. matches = self._match_rows("messages_fts", relaxed, fail_open="OR-relaxed",
  1243. operational_debug="OR-relaxed FTS retry failed; keeping empty result",
  1244. **route) or matches
  1245. return self._finalize_search_matches(matches, result_fields=result_fields)
  1246. def _search_cjk(self, query: str, wants_unindexed_rows: bool, route: dict[str, Any]) -> list[dict[str, Any]]:
  1247. """CJK routing: the unicode61 table splits CJK into single characters (false positives,
  1248. missed phrases). cjk-bigram serves every shape except queries wanting rows the
  1249. substring indexes exclude (role='tool', cron/subagent sources) and LONE
  1250. 1-char CJK runs (bigrams only exist for runs >=2 — LIKE is broader); then trigram
  1251. (>=3 CJK chars per token); then a LIKE substring scan with one clause per
  1252. non-operator token so "广西 OR 桂林 OR 漓江" matches each term."""
  1253. raw_query = _strip_cjk_wildcards(query).strip('"').strip()
  1254. match_query = _quote_fts_tokens(raw_query)
  1255. if self._fts_cjk_available and not wants_unindexed_rows and not self._has_lone_cjk_run(raw_query):
  1256. matches = self._match_rows(
  1257. "messages_fts_cjk", match_query, fail_open="CJK-bigram",
  1258. operational_debug="messages_fts_cjk query failed; falling back to trigram/LIKE", **route)
  1259. if matches is not None:
  1260. return matches
  1261. if self._trigram_route_ok(raw_query) and not wants_unindexed_rows:
  1262. matches = self._match_rows("messages_fts_trigram", match_query, fail_open="Trigram", **route)
  1263. if matches is not None:
  1264. return matches
  1265. non_op_tokens = _non_operator_tokens(raw_query) or [raw_query]
  1266. like_params: list = [p for tok in non_op_tokens for p in _like_params(tok)]
  1267. like_where = [f"({' OR '.join([_LIKE_ANY_COLUMN_SQL] * len(non_op_tokens))})"]
  1268. filters = {k: route[k] for k in ("include_inactive", "source_filter", "exclude_sources", "role_filter",
  1269. "after_ts", "before_ts")}
  1270. _search_filter_clauses(like_where, like_params, **filters)
  1271. # instr() for the snippet uses the first search token.
  1272. return self._like_rows(like_where, [non_op_tokens[0], *like_params, route["limit"], route["offset"]],
  1273. order_by="ORDER BY m.timestamp DESC", limit_sql="LIMIT ? OFFSET ?")
  1274. def _search_unindexed_gap(self, fts_query: str, limit: int, **filters) -> list[dict[str, Any]]:
  1275. """LIKE-scan ids in (fts_rebuild_progress, fts_rebuild_high_water] — rows the deferred
  1276. rebuild hasn't indexed yet. The FTS query degrades to AND-joined substring terms
  1277. (quoted phrases kept whole): recall-over-precision mid-rebuild."""
  1278. status = self.fts_rebuild_status()
  1279. if status is None or limit <= 0:
  1280. return []
  1281. terms = [tok for tok in (t.strip('"').strip("*").strip() for t in _LIKE_TOKEN_RE.findall(fts_query))
  1282. if tok and tok.upper() not in _LIKE_SKIP_TOKENS]
  1283. if not terms:
  1284. return []
  1285. where = ["m.id > ? AND m.id <= ?", *([_LIKE_ANY_COLUMN_SQL] * len(terms))]
  1286. params: list = [status["indexed"], status["total"], *(p for term in terms for p in _like_params(term))]
  1287. _search_filter_clauses(where, params, **filters)
  1288. return self._like_rows(where, [terms[0], *params, limit], order_by="ORDER BY m.timestamp DESC",
  1289. limit_sql="LIMIT ?")
  1290. def search_sessions_by_id(
  1291. self, query: str, limit: int = 20, include_archived: bool = True, source: str | None = None,
  1292. sources: list[str] | None = None, exclude_sources: list[str] | None = None) -> list[dict[str, Any]]:
  1293. """Search surfaced sessions by exact/prefix/substring session id. Also matches
  1294. ``_lineage_root_id`` so an old compression root id resolves to the live continuation."""
  1295. needle = (query or "").strip().lower()
  1296. if not needle or limit <= 0:
  1297. return []
  1298. # list_sessions_rich pushes the id LIKE filter (own id + forward compression
  1299. # chain) into SQL; over-fetch so the in-Python ranking has candidates.
  1300. candidates = self.list_sessions_rich(
  1301. source=source, sources=sources, exclude_sources=exclude_sources, limit=max(limit * 4, limit),
  1302. offset=0, include_archived=include_archived, order_by_last_active=True, id_query=needle)
  1303. def score(row: dict[str, Any]) -> int:
  1304. normalized = [v.lower() for v in (str(row.get("id") or ""), str(row.get("_lineage_root_id") or "")) if v]
  1305. if any(value == needle for value in normalized):
  1306. return 0
  1307. return 1 if any(value.startswith(needle) for value in normalized) else 2
  1308. ranked = sorted(enumerate(candidates), key=lambda item: (score(item[1]), item[0]))
  1309. return [row for _, row in ranked[:limit]]
  1310. # ── FTS maintenance commands ───────────────────────────────────────────
  1311. def _fts_table_exists(self, name: str) -> bool:
  1312. """True if an FTS5 virtual table is queryable ("no such table" and "vtable
  1313. constructor failed" — missing tokenizer / mid-teardown — both count as not)."""
  1314. try:
  1315. self._conn.execute(f"SELECT 1 FROM {name} LIMIT 0")
  1316. return True
  1317. except sqlite3.DatabaseError:
  1318. return False
  1319. def _present_fts_tables(self) -> list[str]:
  1320. """Queryable FTS tables (caller holds ``self._lock``)."""
  1321. return [tbl for tbl in self._FTS_TABLES if self._fts_table_exists(tbl)]
  1322. def optimize_fts(self) -> int:
  1323. """Merge fragmented FTS5 segments into one per index (``'optimize'``). Pure
  1324. maintenance: changes neither results nor ``snippet()`` output, only layout and
  1325. speed; VACUUM then returns the freed pages. Returns the number optimized. A quarantined
  1326. handle never issues ``'optimize'``: it rewrites index segments in place and would compound
  1327. structural damage (or a split WAL generation) instead of leaving it diagnosable."""
  1328. self._raise_if_db_corrupt()
  1329. optimized = 0
  1330. with self._lock:
  1331. self._raise_if_db_replaced()
  1332. if self._conn is None:
  1333. self._reopen_after_close_locked(context="write")
  1334. for tbl in self._present_fts_tables():
  1335. try:
  1336. self._conn.execute(f"INSERT INTO {tbl}({tbl}) VALUES('optimize')")
  1337. optimized += 1
  1338. except sqlite3.DatabaseError as exc: # SQLITE_CORRUPT is not an OperationalError
  1339. logger.warning("FTS optimize failed for %s: %s", tbl, exc)
  1340. return optimized
  1341. def rebuild_fts(self) -> int:
  1342. """Rebuild FTS5 indexes from ``messages`` (``'rebuild'``) — the recovery for a corrupt index
  1343. that rejects writes while reads succeed. Two processes rebuilding one state.db
  1344. concurrently corrupted production DBs, so this admits through
  1345. ``fts_rebuild_admission`` and FAILS CLOSED, returning 0 on deferral (callers treat 0
  1346. as "no progress" and use the stale-FTS breadcrumb path). Returns indexes rebuilt.
  1347. Uses the FTS5 ``'rebuild'`` command, which rewrites the internal b-tree segments from the content
  1348. rows. Unlike ``optimize_fts`` (which merges existing segments), ``rebuild`` discards and recreates
  1349. the index data entirely — the more destructive of the two, so it is quarantined the same way. See
  1350. #50502.
  1351. A full structural rebuild must never run concurrently in two processes sharing one state.db — that
  1352. interleaving has structurally corrupted the database in production (PR #93200) — so this admits
  1353. through the cross-process ``fts_rebuild_admission`` authority and FAILS CLOSED: if another process
  1354. holds the rebuild lock beyond the bounded wait, this call defers (returns 0) rather than racing it.
  1355. Callers already treat 0 as "rebuild made no progress" and fall back to the stale-FTS breadcrumb
  1356. path, which retries in-process from the gateway housekeeping tick (``retry_deferred_fts_recovery``)
  1357. and at next startup.
  1358. """
  1359. self._raise_if_db_corrupt()
  1360. rebuilt = 0
  1361. with fts_rebuild_admission(self.db_path) as admitted:
  1362. if not admitted:
  1363. logger.warning(
  1364. "Deferred in-place FTS rebuild: another process holds the rebuild authority for this state.db.")
  1365. return 0
  1366. with self._lock:
  1367. self._raise_if_db_replaced()
  1368. if self._conn is None:
  1369. self._reopen_after_close_locked(context="write")
  1370. for tbl in self._present_fts_tables():
  1371. try:
  1372. self._conn.execute(f"INSERT INTO {tbl}({tbl}) VALUES('rebuild')")
  1373. self._conn.commit()
  1374. rebuilt += 1
  1375. except sqlite3.DatabaseError as exc: # SQLITE_CORRUPT is not an OperationalError (#133375)
  1376. self._conn.rollback()
  1377. if is_malformed_db_error(exc):
  1378. logger.error(
  1379. "FTS rebuild failed with a corruption-class error for %s: %s; "
  1380. "run 'hermes sessions repair' (or 'hermes doctor --fix') to rebuild the index offline",
  1381. tbl, exc)
  1382. else:
  1383. logger.warning("FTS rebuild failed for %s: %s", tbl, exc)
  1384. return rebuilt
  1385. def _merge_fts_incrementally(self, *, max_pages: int, max_commands: Optional[int] = None) -> int:
  1386. """Run bounded FTS5 ``'merge'`` commands against each present index. A positive merge rank
  1387. stops after ~that many output pages, so each command holds the write lock for
  1388. milliseconds regardless of index size (``'optimize'`` takes 9-18 s per index on a
  1389. 10 GB DB). ``usermerge`` is lowered to its minimum of 2 (persisted in ``%_config``,
  1390. once per instance) so a merge acts on ANY level with >= 2 segments; at the default
  1391. 4 a fragmented index cannot converge. Up to *max_commands* per index, stopping on
  1392. the no-progress signal ``total_changes`` delta < 2 (the INSERT itself is 1). Each
  1393. command is its own implicit transaction, so processes interleave mid-pass. Missing
  1394. tables are skipped (optimize_fts_storage drops + backfills them live); other SQLite
  1395. errors propagate. Returns commands executed."""
  1396. _positive_int("max_pages", max_pages)
  1397. if max_commands is None:
  1398. max_commands = self._FTS_MERGE_COMMANDS_PER_PASS
  1399. _positive_int("max_commands", max_commands)
  1400. executed = 0
  1401. with self._lock:
  1402. for tbl in self._present_fts_tables():
  1403. if not self._fts_usermerge_floor_applied:
  1404. self._conn.execute(f"INSERT INTO {tbl}({tbl}, rank) VALUES('usermerge', 2)")
  1405. for _ in range(max_commands):
  1406. before = self._conn.total_changes
  1407. self._conn.execute(f"INSERT INTO {tbl}({tbl}, rank) VALUES('merge', ?)", (max_pages,))
  1408. executed += 1
  1409. if self._conn.total_changes - before < 2:
  1410. break
  1411. self._fts_usermerge_floor_applied = True
  1412. return executed