session_recovery.py 65 KB

1234567891011121314151617181920212223242526272829303132333435363738394041424344454647484950515253545556575859606162636465666768697071727374757677787980818283848586878889909192939495969798991001011021031041051061071081091101111121131141151161171181191201211221231241251261271281291301311321331341351361371381391401411421431441451461471481491501511521531541551561571581591601611621631641651661671681691701711721731741751761771781791801811821831841851861871881891901911921931941951961971981992002012022032042052062072082092102112122132142152162172182192202212222232242252262272282292302312322332342352362372382392402412422432442452462472482492502512522532542552562572582592602612622632642652662672682692702712722732742752762772782792802812822832842852862872882892902912922932942952962972982993003013023033043053063073083093103113123133143153163173183193203213223233243253263273283293303313323333343353363373383393403413423433443453463473483493503513523533543553563573583593603613623633643653663673683693703713723733743753763773783793803813823833843853863873883893903913923933943953963973983994004014024034044054064074084094104114124134144154164174184194204214224234244254264274284294304314324334344354364374384394404414424434444454464474484494504514524534544554564574584594604614624634644654664674684694704714724734744754764774784794804814824834844854864874884894904914924934944954964974984995005015025035045055065075085095105115125135145155165175185195205215225235245255265275285295305315325335345355365375385395405415425435445455465475485495505515525535545555565575585595605615625635645655665675685695705715725735745755765775785795805815825835845855865875885895905915925935945955965975985996006016026036046056066076086096106116126136146156166176186196206216226236246256266276286296306316326336346356366376386396406416426436446456466476486496506516526536546556566576586596606616626636646656666676686696706716726736746756766776786796806816826836846856866876886896906916926936946956966976986997007017027037047057067077087097107117127137147157167177187197207217227237247257267277287297307317327337347357367377387397407417427437447457467477487497507517527537547557567577587597607617627637647657667677687697707717727737747757767777787797807817827837847857867877887897907917927937947957967977987998008018028038048058068078088098108118128138148158168178188198208218228238248258268278288298308318328338348358368378388398408418428438448458468478488498508518528538548558568578588598608618628638648658668678688698708718728738748758768778788798808818828838848858868878888898908918928938948958968978988999009019029039049059069079089099109119129139149159169179189199209219229239249259269279289299309319329339349359369379389399409419429439449459469479489499509519529539549559569579589599609619629639649659669679689699709719729739749759769779789799809819829839849859869879889899909919929939949959969979989991000100110021003100410051006100710081009101010111012101310141015101610171018101910201021102210231024102510261027102810291030103110321033103410351036103710381039104010411042104310441045104610471048104910501051105210531054105510561057105810591060106110621063106410651066106710681069107010711072107310741075107610771078107910801081108210831084108510861087108810891090109110921093109410951096109710981099110011011102110311041105110611071108110911101111111211131114111511161117111811191120112111221123112411251126112711281129113011311132113311341135113611371138113911401141114211431144114511461147114811491150115111521153115411551156115711581159116011611162116311641165116611671168116911701171117211731174117511761177117811791180118111821183118411851186118711881189119011911192119311941195119611971198119912001201120212031204120512061207120812091210121112121213121412151216121712181219122012211222122312241225122612271228122912301231123212331234123512361237123812391240124112421243124412451246124712481249125012511252125312541255125612571258125912601261126212631264126512661267126812691270127112721273127412751276127712781279128012811282128312841285128612871288128912901291129212931294129512961297
  1. """Offline, non-destructive recovery for a damaged Hermes session database.
  2. The source is never opened by SQLite: it and its WAL/SHM/journal sidecars are copied to a disposable
  3. work dir first. Canonical rows are copied into a fresh current-schema database; derived FTS tables and
  4. migration bookkeeping are rebuilt, not copied; the result is never installed over the active database.
  5. """
  6. from __future__ import annotations
  7. import json
  8. import os
  9. import shutil
  10. import sqlite3
  11. import tempfile
  12. from contextlib import contextmanager
  13. from pathlib import Path
  14. from typing import Any, Callable, Iterator, Optional
  15. from hermes_cli.timefmt import EPOCH_MAX, EPOCH_MIN
  16. from hermes_state import SessionDB
  17. from hermes_state_common import FTS_STORAGE_VERSION, SCHEMA_VERSION
  18. from hermes_state_repair import _db_opens_cleanly
  19. ProgressCallback = Callable[[dict[str, Any]], None]
  20. _CANONICAL_TABLES = (
  21. "system_prompts", "sessions", "messages", "session_model_usage", "compression_locks", "gateway_routing",
  22. "async_delegations",
  23. )
  24. _TOPIC_TABLES = ("telegram_dm_topic_mode", "telegram_dm_topic_bindings")
  25. def _init_delivery_ledger_schema(conn: sqlite3.Connection) -> None:
  26. from gateway.delivery_ledger import _initialize_schema
  27. _initialize_schema(conn)
  28. # state.db tables created lazily by a gateway module (base ``SessionDB`` never creates them on a fresh
  29. # destination) -> the initializer owning their DDL. Recovery creates them before copying so owed rows
  30. # don't silently vanish from a "complete" salvage. Register new lazy tables HERE, not as ``if table ==``.
  31. # See #100313, #86236.
  32. _AUXILIARY_TABLE_SCHEMAS: dict[str, Callable[[sqlite3.Connection], None]] = {
  33. "delivery_obligations": _init_delivery_ledger_schema,
  34. }
  35. _AUXILIARY_TABLES = tuple(_AUXILIARY_TABLE_SCHEMAS)
  36. # Salvage is a transcript output, not a runnable clone of accepted work. Copying
  37. # only part of this ledger could replay already-started effects or adopt workers
  38. # from another owner. Inventory and report the exclusions rather than losing them
  39. # silently. Activation under exclusive ownership creates a fresh runtime epoch;
  40. # ordinary full backup/restore instead preserves the complete ledger for startup
  41. # reconciliation (started -> unknown; never blindly replay started work).
  42. _RUNTIME_TABLES = ("runtime_epoch", "session_admissions", "worker_executions", "worker_receipts")
  43. _INVENTORY_TABLES = (*_CANONICAL_TABLES, "state_meta", *_TOPIC_TABLES, *_AUXILIARY_TABLES, *_RUNTIME_TABLES)
  44. # Derived-index / optional-schema markers: a fresh destination regenerates these, never copies them.
  45. _GENERATED_META_KEYS = frozenset({
  46. "fts_storage_version", "fts_optimize_available", "fts_rebuild_high_water", "fts_rebuild_progress",
  47. "fts_cjk_stale", "fts_cjk_rebuild_high_water", "fts_cjk_rebuild_progress", "telegram_dm_topic_schema_version",
  48. "fts_tool_full_content_high_water", # retired marker; never copied into a recovered store
  49. })
  50. _SIDECAR_SUFFIXES = ("", "-wal", "-shm", "-journal")
  51. _MINIMUM_SPACE_HEADROOM = 256 * 1024 * 1024
  52. _MAX_SALVAGE_RANGE_QUERIES = 10_000
  53. _MIN_SQLITE_ROWID = -(2**63)
  54. _MAX_SQLITE_ROWID = 2**63 - 1
  55. class SessionRecoveryError(RuntimeError):
  56. """Base error for offline session recovery."""
  57. class SessionRecoverySafetyError(SessionRecoveryError):
  58. """Raised before recovery when a path or overwrite guard fails."""
  59. class SessionRecoverySourceError(SessionRecoveryError):
  60. """Raised when the source cannot provide the required canonical tables."""
  61. def _sidecar_path(db_path: Path, suffix: str) -> Path:
  62. return db_path if not suffix else db_path.with_name(db_path.name + suffix)
  63. def _resolved_output_path(path: Path) -> Path:
  64. """Resolve a not-yet-created output path without requiring it to exist."""
  65. return path.expanduser().parent.resolve(strict=True) / path.name
  66. def _validate_paths(
  67. source_path: Path, output_path: Optional[Path] = None, work_dir: Optional[Path] = None,
  68. ) -> tuple[Path, Optional[Path], Path]:
  69. source = source_path.expanduser().resolve(strict=True)
  70. if not source.is_file():
  71. raise SessionRecoverySafetyError(f"Source is not a file: {source}")
  72. output: Optional[Path] = None
  73. if output_path is not None:
  74. output = _resolved_output_path(output_path)
  75. protected = {_sidecar_path(source, suffix).resolve(strict=False) for suffix in _SIDECAR_SUFFIXES}
  76. if output.resolve(strict=False) in protected:
  77. raise SessionRecoverySafetyError(
  78. "The recovery output must not be the source database or one of its journal sidecars."
  79. )
  80. for suffix in _SIDECAR_SUFFIXES:
  81. candidate = _sidecar_path(output, suffix)
  82. if os.path.lexists(candidate):
  83. raise SessionRecoverySafetyError(f"Refusing to overwrite existing recovery output: {candidate}")
  84. work_root = (
  85. work_dir.expanduser().resolve(strict=True)
  86. if work_dir is not None
  87. else (output.parent if output is not None else source.parent)
  88. )
  89. if not work_root.is_dir():
  90. raise SessionRecoverySafetyError(f"Recovery work directory is not a directory: {work_root}")
  91. return source, output, work_root
  92. def _source_fingerprint(source: Path) -> dict[str, dict[str, int]]:
  93. fingerprint: dict[str, dict[str, int]] = {}
  94. for suffix in _SIDECAR_SUFFIXES:
  95. path = _sidecar_path(source, suffix)
  96. if path.exists():
  97. stat = path.stat()
  98. fingerprint[suffix or "main"] = {"size": stat.st_size, "mtime_ns": stat.st_mtime_ns}
  99. return fingerprint
  100. def _format_bytes(value: int) -> str:
  101. units = ("B", "KiB", "MiB", "GiB", "TiB")
  102. amount = float(value)
  103. for unit in units:
  104. if amount < 1024 or unit == units[-1]:
  105. return f"{amount:.1f} {unit}"
  106. amount /= 1024
  107. return f"{value} B"
  108. def _same_filesystem(left: Path, right: Path) -> bool:
  109. try:
  110. return os.stat(left).st_dev == os.stat(right).st_dev
  111. except OSError: # both dirs exist (_validate_paths); fallback for incomplete st_dev support
  112. return left.anchor.casefold() == right.anchor.casefold()
  113. def _disk_space_preflight(source: Path, work_root: Path, output_parent: Optional[Path]) -> dict[str, Any]:
  114. """Require space for the disposable bundle, output, and safety headroom."""
  115. bundle_bytes = sum(info["size"] for info in _source_fingerprint(source).values())
  116. # The v23 external-content rebuild is usually much smaller than a legacy database, but estimating
  117. # with the complete bundle avoids betting the user's disk on that.
  118. output_allowance = bundle_bytes if output_parent is not None else 0
  119. headroom = max(_MINIMUM_SPACE_HEADROOM, int((bundle_bytes + output_allowance) * 0.05))
  120. work_free = int(shutil.disk_usage(work_root).free)
  121. report: dict[str, Any] = {
  122. "source_bundle_bytes": bundle_bytes, "estimated_output_bytes": output_allowance, "headroom_bytes": headroom,
  123. "work_dir": str(work_root), "work_dir_free_bytes": work_free,
  124. }
  125. if output_parent is None or _same_filesystem(work_root, output_parent):
  126. required = bundle_bytes + output_allowance + headroom
  127. report.update(shared_filesystem=True, work_dir_required_bytes=required)
  128. if work_free < required:
  129. raise SessionRecoverySafetyError(
  130. f"Not enough free disk space for a safe recovery copy: {_format_bytes(work_free)} available at "
  131. f"{work_root}, {_format_bytes(required)} required ({_format_bytes(bundle_bytes)} source bundle + "
  132. f"{_format_bytes(output_allowance)} output allowance + {_format_bytes(headroom)} headroom). "
  133. "Use --work-dir or --output on a filesystem with more free space."
  134. )
  135. return report
  136. output_free = int(shutil.disk_usage(output_parent).free)
  137. work_required = bundle_bytes + headroom
  138. output_required = output_allowance + headroom
  139. report.update({
  140. "shared_filesystem": False, "work_dir_required_bytes": work_required, "output_dir": str(output_parent),
  141. "output_dir_free_bytes": output_free, "output_dir_required_bytes": output_required,
  142. })
  143. shortages = [
  144. f"{where}: {_format_bytes(free)} available, {_format_bytes(required)} required"
  145. for where, free, required in (
  146. (work_root, work_free, work_required), (output_parent, output_free, output_required),
  147. )
  148. if free < required
  149. ]
  150. if shortages:
  151. raise SessionRecoverySafetyError(
  152. f"Not enough free disk space for safe recovery: {'; '.join(shortages)}. "
  153. "Choose work/output filesystems with more free space."
  154. )
  155. return report
  156. def _copy_source_bundle(source: Path, snapshot_dir: Path) -> tuple[Path, list[str]]:
  157. """Copy the source DB bundle aside so SQLite never opens the original.
  158. The whole copy runs inside ``offline_file_access`` (holds the connection-lifecycle lock). Recovery
  159. normally runs as its own CLI process against an offline file, so the refusal should never fire; the
  160. guard keeps this path consistent with ``hermes_state_repair._backup_db_file``.
  161. Checking for a live connection and *then* copying would be a check/use race: a connection could open in
  162. that window, and the copy's ``close()`` would cancel its POSIX advisory locks -- the failure class
  163. ``hermes_cli.sqlite_safe_read`` exists to prevent (see #71724). Holding the lock means no connection can
  164. appear mid-copy, across the main file and every sidecar.
  165. """
  166. from hermes_cli.sqlite_safe_read import LiveConnectionError, offline_file_access
  167. snapshot_source = snapshot_dir / source.name
  168. copied: list[str] = []
  169. try:
  170. with offline_file_access(source, what="snapshot"):
  171. for suffix in _SIDECAR_SUFFIXES:
  172. source_part = _sidecar_path(source, suffix)
  173. if source_part.exists():
  174. destination_part = _sidecar_path(snapshot_source, suffix)
  175. shutil.copy2(source_part, destination_part)
  176. copied.append(destination_part.name)
  177. except LiveConnectionError as exc:
  178. raise SessionRecoverySafetyError(str(exc)) from exc
  179. return snapshot_source, copied
  180. def _table_columns(conn: sqlite3.Connection, table: str) -> list[str]:
  181. return [str(row[1]) for row in conn.execute(f'PRAGMA table_info("{table}")')]
  182. def _count_rows(conn: sqlite3.Connection, table: str) -> int:
  183. return int(conn.execute(f'SELECT COUNT(*) FROM "{table}"').fetchone()[0])
  184. def _connect(path: Path) -> sqlite3.Connection:
  185. """Autocommit connection with a short busy timeout (source snapshot or fresh output)."""
  186. return sqlite3.connect(str(path), isolation_level=None, timeout=1.0)
  187. @contextmanager
  188. def _immediate_transaction(conn: sqlite3.Connection) -> Iterator[None]:
  189. """``BEGIN IMMEDIATE`` ... ``COMMIT``, rolling back on any exception."""
  190. conn.execute("BEGIN IMMEDIATE")
  191. try:
  192. yield
  193. conn.execute("COMMIT")
  194. except BaseException:
  195. conn.execute("ROLLBACK")
  196. raise
  197. def _compatible_columns(
  198. source: sqlite3.Connection, destination: sqlite3.Connection, table: str, result: dict[str, Any],
  199. ) -> Optional[list[str]]:
  200. """Columns shared by source and destination; sets a terminal status and returns None otherwise."""
  201. source_columns = _table_columns(source, table)
  202. columns = [column for column in _table_columns(destination, table) if column in source_columns]
  203. result["columns"] = columns
  204. if not source_columns:
  205. result["status"] = "missing"
  206. return None
  207. if not columns:
  208. result["status"] = "failed"
  209. result["error"] = "source and destination have no compatible columns"
  210. return None
  211. return columns
  212. def _quoted_columns(columns: list[str]) -> tuple[str, str]:
  213. """``("a", "b")`` column list and matching ``?, ?`` placeholders."""
  214. return ", ".join(f'"{column}"' for column in columns), ", ".join("?" for _ in columns)
  215. def _copy_rows(
  216. source: sqlite3.Connection, destination: sqlite3.Connection, select_sql: str, params: tuple[Any, ...],
  217. insert_sql: str, *, table: str, chunk_size: int, progress_cb: Optional[ProgressCallback],
  218. expected_rows: Optional[int], result: dict[str, Any],
  219. ) -> dict[str, Any]:
  220. """Chunked straight copy; fills ``status``/``error`` on ``result``."""
  221. try:
  222. cursor = source.execute(select_sql, params)
  223. while True:
  224. rows = cursor.fetchmany(chunk_size)
  225. if not rows:
  226. break
  227. with _immediate_transaction(destination):
  228. destination.executemany(insert_sql, rows)
  229. result["copied_rows"] += len(rows)
  230. if progress_cb is not None:
  231. progress_cb({"table": table, "copied_rows": result["copied_rows"], "source_rows": expected_rows})
  232. except sqlite3.DatabaseError as exc:
  233. result["status"] = "partial" if result["copied_rows"] else "failed"
  234. result["error"] = str(exc)
  235. return result
  236. if expected_rows is None or result["copied_rows"] == expected_rows:
  237. result["status"] = "complete"
  238. else:
  239. result["status"] = "partial"
  240. result["error"] = f"copied {result['copied_rows']} of {expected_rows} readable rows"
  241. return result
  242. def _table_inventory(conn: sqlite3.Connection, table: str) -> dict[str, Any]:
  243. result: dict[str, Any] = {"available": False, "columns": [], "rows": None}
  244. try:
  245. columns = _table_columns(conn, table)
  246. if not columns:
  247. return result
  248. result.update(available=True, columns=columns)
  249. result["rows"] = _count_rows(conn, table)
  250. except sqlite3.DatabaseError as exc:
  251. result["error"] = str(exc)
  252. return result
  253. def _journal_mode(conn: sqlite3.Connection) -> Optional[str]:
  254. row = conn.execute("PRAGMA journal_mode").fetchone()
  255. return str(row[0]).lower() if row else None
  256. def _inspect_connection(conn: sqlite3.Connection) -> dict[str, Any]:
  257. conn.execute("PRAGMA writable_schema=ON")
  258. report: dict[str, Any] = {"tables": {}, "errors": [], "warnings": []}
  259. try:
  260. report["journal_mode"] = _journal_mode(conn)
  261. except sqlite3.DatabaseError as exc:
  262. report["journal_mode"] = None
  263. # Journal metadata is context, not canonical data: a damaged pragma must not block readable rows.
  264. report["warnings"].append(f"journal mode: {exc}")
  265. report["tables"] = {table: _table_inventory(conn, table) for table in _INVENTORY_TABLES}
  266. for required in ("sessions", "messages"):
  267. table_report = report["tables"][required]
  268. if not table_report.get("available") or table_report.get("rows") is None:
  269. report["errors"].append(f"required table {required} is not completely readable")
  270. report["recoverable"] = not report["errors"]
  271. return report
  272. def _snapshot_and_inspect(
  273. source: Path, work_root: Path,
  274. ) -> tuple[tempfile.TemporaryDirectory[str], Path, dict[str, Any]]:
  275. before = _source_fingerprint(source)
  276. temp_dir = tempfile.TemporaryDirectory(prefix="hermes-session-recovery-", dir=str(work_root))
  277. try:
  278. snapshot_source, copied = _copy_source_bundle(source, Path(temp_dir.name))
  279. if _source_fingerprint(source) != before:
  280. raise SessionRecoverySafetyError(
  281. "The source database bundle changed while it was being copied. Stop every Hermes process using this "
  282. "profile and retry. This includes the interactive `hermes` CLI session this command may have been "
  283. "launched from: a running parent CLI writes session bookkeeping (compression ticks, context "
  284. "tracking) to state.db in the background and counts as a Hermes process even after the gateway is "
  285. "stopped. Run the recovery from a fresh shell with no `hermes` session open, or point --source at an "
  286. "immutable snapshot copy of the database."
  287. )
  288. conn = _connect(snapshot_source)
  289. try:
  290. inspection = _inspect_connection(conn)
  291. finally:
  292. conn.close()
  293. inspection.update(source_bundle=copied, source_fingerprint=before)
  294. return temp_dir, snapshot_source, inspection
  295. except BaseException:
  296. temp_dir.cleanup()
  297. raise
  298. def inspect_session_database(source_path: Path, *, work_dir: Optional[Path] = None) -> dict[str, Any]:
  299. """Inspect canonical table readability without opening the source itself."""
  300. source, _, work_root = _validate_paths(source_path, work_dir=work_dir)
  301. disk_space = _disk_space_preflight(source, work_root, None)
  302. temp_dir, _, inspection = _snapshot_and_inspect(source, work_root)
  303. try:
  304. return {
  305. "operation": "inspect", "source": str(source), "disk_space": disk_space, **inspection,
  306. "source_unchanged": _source_fingerprint(source) == inspection["source_fingerprint"],
  307. }
  308. finally:
  309. temp_dir.cleanup()
  310. def _fresh_destination(output: Path, *, topic_tables: bool = False) -> sqlite3.Connection:
  311. """Initialize a current-schema database at ``output`` and open it with foreign keys off."""
  312. with SessionDB(db_path=output) as destination_db:
  313. if topic_tables:
  314. destination_db.apply_telegram_topic_migration()
  315. conn = _connect(output)
  316. conn.execute("PRAGMA foreign_keys=OFF")
  317. return conn
  318. def _copy_table(
  319. source: sqlite3.Connection, destination: sqlite3.Connection, table: str, *, salvage: bool, chunk_size: int,
  320. progress_cb: Optional[ProgressCallback], source_rows: Optional[int],
  321. ) -> dict[str, Any]:
  322. """Copy one canonical table: straight chunked copy, or rowid-range salvage when ``salvage``."""
  323. copy_kwargs = dict(chunk_size=chunk_size, progress_cb=progress_cb, source_rows=source_rows)
  324. if table == "state_meta":
  325. return _copy_state_meta(source, destination, salvage=salvage, **copy_kwargs)
  326. if salvage:
  327. return _copy_table_salvage(source, destination, table, **copy_kwargs)
  328. result: dict[str, Any] = {"source_rows": source_rows, "copied_rows": 0}
  329. columns = _compatible_columns(source, destination, table, result)
  330. if columns is None:
  331. return result
  332. quoted, placeholders = _quoted_columns(columns)
  333. return _copy_rows(
  334. source, destination, f'SELECT {quoted} FROM "{table}"', (),
  335. f'INSERT INTO "{table}" ({quoted}) VALUES ({placeholders})', table=table, chunk_size=chunk_size,
  336. progress_cb=progress_cb, expected_rows=source_rows, result=result,
  337. )
  338. def _append_skipped_range(ranges: list[dict[str, Any]], low: int, high: int, error: str) -> None:
  339. """Record skipped rowid ranges, merging adjacent same-error ranges (not one entry per row)."""
  340. if ranges and ranges[-1]["high"] + 1 == low and ranges[-1]["error"] == error:
  341. ranges[-1]["high"] = high
  342. return
  343. ranges.append({"low": low, "high": high, "error": error})
  344. def _salvage_rowid_bounds(source: sqlite3.Connection, table: str) -> dict[str, Any]:
  345. """Find the readable rowid edges without scanning the complete table."""
  346. result: dict[str, Any] = {"errors": [], "fallback_edges": []}
  347. rows: dict[str, Optional[int]] = {"low": None, "high": None}
  348. for edge, direction in (("low", "ASC"), ("high", "DESC")):
  349. try:
  350. row = source.execute(f'SELECT rowid FROM "{table}" ORDER BY rowid {direction} LIMIT 1').fetchone()
  351. if row is not None:
  352. rows[edge] = int(row[0])
  353. except sqlite3.DatabaseError as exc:
  354. result["errors"].append(f"{edge} rowid: {exc}")
  355. if rows["low"] is None and rows["high"] is None:
  356. result["empty" if not result["errors"] else "unavailable"] = True
  357. return result
  358. # An ordered LIMIT 1 walks the table b-tree and dies on a damaged edge leaf, while the
  359. # aggregate lets the planner answer from any covering index (every Hermes table has at
  360. # least a PRIMARY KEY autoindex). Ask it before falling back to the synthetic domain:
  361. # bisecting from INT64_MIN burned the whole query budget on a 4-row table (#98050).
  362. missing = [edge for edge in ("low", "high") if rows[edge] is None]
  363. if missing:
  364. try:
  365. aggregate = source.execute(f'SELECT min(rowid), max(rowid) FROM "{table}"').fetchone()
  366. except sqlite3.DatabaseError as exc:
  367. result["errors"].append(f"aggregate rowid bounds: {exc}")
  368. else:
  369. for edge, value in zip(("low", "high"), aggregate):
  370. if rows[edge] is None and value is not None:
  371. rows[edge] = int(value)
  372. result.setdefault("aggregate_edges", []).append(edge)
  373. # A damaged edge can stop one ordered probe. Keep the readable edge and bound the other side by the
  374. # SQLite rowid domain, so bisection never assumes user databases hold only positive ids.
  375. if rows["low"] is None:
  376. rows["low"] = _MIN_SQLITE_ROWID
  377. result["fallback_edges"].append("low")
  378. if rows["high"] is None:
  379. rows["high"] = _MAX_SQLITE_ROWID
  380. result["fallback_edges"].append("high")
  381. result.update(rows)
  382. # Bisecting the whole synthetic domain tail used to exhaust the range-query budget before readable
  383. # tail rows were copied (#80205); gallop outward from the surviving edge for a finite bound first.
  384. if result["fallback_edges"]:
  385. result["edge_probes"] = []
  386. for edge, anchor_edge in (("high", "low"), ("low", "high")):
  387. if edge in result["fallback_edges"]:
  388. probe = _probe_populated_edge(source, table, edge=edge, anchor=int(result[anchor_edge]))
  389. result["edge_probes"].append(probe)
  390. if probe["capped"]:
  391. result[edge] = int(probe["bound"])
  392. return result
  393. def _probe_populated_edge(source: sqlite3.Connection, table: str, *, edge: str, anchor: int) -> dict[str, Any]:
  394. """Finite bound for a damaged rowid edge: gallop outward from the readable ``anchor`` with doubling
  395. offsets. A clean "no rows beyond X" caps the domain; an error or a hit keeps growing (~64 probes max).
  396. """
  397. ascending = edge == "high"
  398. comparison = ">" if ascending else "<"
  399. probe_sql = (
  400. f'SELECT rowid FROM "{table}" WHERE rowid {comparison} ? '
  401. f'ORDER BY rowid {"ASC" if ascending else "DESC"} LIMIT 1'
  402. )
  403. domain_limit = _MAX_SQLITE_ROWID if ascending else _MIN_SQLITE_ROWID
  404. result: dict[str, Any] = {"edge": edge, "probes": 0, "capped": False}
  405. position = anchor
  406. span = 1
  407. while True:
  408. candidate = position + span if ascending else position - span
  409. if (ascending and candidate >= domain_limit) or (not ascending and candidate <= domain_limit):
  410. result["bound"] = domain_limit # no clean empty-tail answer: keep the domain fallback
  411. return result
  412. result["probes"] += 1
  413. try:
  414. row = source.execute(probe_sql, (candidate,)).fetchone()
  415. except sqlite3.DatabaseError: # damage on the probe path: inconclusive, widen further
  416. span *= 2
  417. continue
  418. if row is None: # nothing beyond candidate: the synthetic domain tail is provably empty
  419. result.update(bound=candidate, capped=True)
  420. return result
  421. position = int(row[0]) # rows exist beyond; advance. Span never resets -> O(log range)
  422. span *= 2
  423. class _RowidRangeSalvage:
  424. """Bisecting rowid-range copy for one table; counters live on the shared ``result`` dict."""
  425. def __init__(
  426. self, source: sqlite3.Connection, destination: sqlite3.Connection, table: str, columns: list[str], *,
  427. chunk_size: int, progress_cb: Optional[ProgressCallback], source_rows: Optional[int], insert_prefix: str,
  428. row_filter: Optional[Callable[[tuple[Any, ...], tuple[str, ...]], bool]], result: dict[str, Any],
  429. ) -> None:
  430. self.source, self.destination, self.table = source, destination, table
  431. self.chunk_size, self.progress_cb, self.source_rows = chunk_size, progress_cb, source_rows
  432. self.row_filter, self.result = row_filter, result
  433. self.column_names = tuple(columns)
  434. quoted, placeholders = _quoted_columns(columns)
  435. self.select_sql = f'SELECT rowid, {quoted} FROM "{table}" WHERE rowid BETWEEN ? AND ? ORDER BY rowid'
  436. self.insert_sql = f'{insert_prefix} INTO "{table}" ({quoted}) VALUES ({placeholders})'
  437. self.exact_sql = f'SELECT {quoted} FROM "{table}" WHERE rowid = ?'
  438. self.stopped_at_query_limit = False
  439. def _keep(self, values: list[tuple[Any, ...]]) -> list[tuple[Any, ...]]:
  440. return values if self.row_filter is None else [r for r in values if self.row_filter(r, self.column_names)]
  441. def _skip(self, low: int, high: int, error: str) -> None:
  442. _append_skipped_range(self.result["skipped_rowid_ranges"], low, high, error)
  443. def recover_exact_rowid(self, rowid: int) -> bool:
  444. """Salvage one row by exact-key lookup (issue #80205).
  445. A singleton range scan (``rowid BETWEEN x AND x ORDER BY rowid``) must advance the cursor
  446. past ``x`` to prove the range is exhausted; when the *next* cell or page is damaged that
  447. advance raises AFTER the row was produced, and the driver discards the already-fetched row.
  448. """
  449. result = self.result
  450. result["range_queries"] += 1
  451. try:
  452. row = self.source.execute(self.exact_sql, (rowid,)).fetchone()
  453. except sqlite3.DatabaseError:
  454. return False
  455. if row is None:
  456. return True # genuinely absent: nothing to skip
  457. value = tuple(row)
  458. if not self._keep([value]):
  459. result["excluded_rows"] += 1
  460. return True
  461. try:
  462. with _immediate_transaction(self.destination):
  463. self.destination.execute(self.insert_sql, value)
  464. except sqlite3.IntegrityError as exc:
  465. # A phantom row from a damaged page (NULL in a NOT NULL column, FK to nothing) is rejected
  466. # by the destination schema; report it as a skipped singleton instead of aborting the table.
  467. result["destination_rejected_rows"] += 1
  468. self._skip(rowid, rowid, f"destination constraint rejected row: {exc}")
  469. return True
  470. result["copied_rows"] += 1
  471. result["exact_lookup_recovered"] += 1
  472. return True
  473. def copy_range(self, low: int, high: int) -> None:
  474. """Copy ``[low, high]``; on a read error bisect the unread remainder, exact-lookup singletons."""
  475. result = self.result
  476. if low > high:
  477. return
  478. if result["range_queries"] >= _MAX_SALVAGE_RANGE_QUERIES:
  479. self.stopped_at_query_limit = True
  480. self._skip(low, high, "salvage range query limit reached")
  481. return
  482. result["range_queries"] += 1
  483. last_committed_rowid: Optional[int] = None
  484. try:
  485. cursor = self.source.execute(self.select_sql, (low, high))
  486. while True:
  487. fetched = cursor.fetchmany(self.chunk_size)
  488. if not fetched:
  489. return
  490. values = [tuple(row[1:]) for row in fetched]
  491. included = self._keep(values)
  492. if included:
  493. with _immediate_transaction(self.destination):
  494. self.destination.executemany(self.insert_sql, included)
  495. result["copied_rows"] += len(included)
  496. result["excluded_rows"] += len(values) - len(included)
  497. last_committed_rowid = int(fetched[-1][0])
  498. if self.progress_cb is not None:
  499. self.progress_cb({
  500. "table": self.table, "copied_rows": result["copied_rows"], "source_rows": self.source_rows,
  501. "skipped_ranges": len(result["skipped_rowid_ranges"]),
  502. })
  503. except sqlite3.DatabaseError as exc:
  504. retry_low = last_committed_rowid + 1 if last_committed_rowid is not None else low
  505. if retry_low > high:
  506. return
  507. if retry_low == high:
  508. if not self.recover_exact_rowid(retry_low):
  509. self._skip(retry_low, high, str(exc))
  510. return
  511. midpoint = retry_low + (high - retry_low) // 2
  512. self.copy_range(retry_low, midpoint)
  513. self.copy_range(midpoint + 1, high)
  514. def _copy_table_salvage(
  515. source: sqlite3.Connection, destination: sqlite3.Connection, table: str, *, chunk_size: int,
  516. progress_cb: Optional[ProgressCallback], source_rows: Optional[int], insert_prefix: str = "INSERT",
  517. row_filter: Optional[Callable[[tuple[Any, ...], tuple[str, ...]], bool]] = None,
  518. ) -> dict[str, Any]:
  519. """Best-effort rowid-range copy that continues past damaged source pages."""
  520. result: dict[str, Any] = {
  521. "mode": "rowid_range_salvage", "source_rows": source_rows, "copied_rows": 0, "excluded_rows": 0,
  522. "columns": [], "range_queries": 0, "exact_lookup_recovered": 0, "destination_rejected_rows": 0,
  523. "skipped_rowid_ranges": [],
  524. }
  525. columns = _compatible_columns(source, destination, table, result)
  526. if columns is None:
  527. return result
  528. bounds = _salvage_rowid_bounds(source, table)
  529. result["rowid_bounds"] = bounds
  530. if bounds.get("empty"):
  531. result["status"] = "complete"
  532. return result
  533. if bounds.get("low") is None or bounds.get("high") is None:
  534. details = "; ".join(bounds.get("errors") or [])
  535. result["status"] = "failed"
  536. result["error"] = "could not determine a rowid range for salvage" + (f": {details}" if details else "")
  537. return result
  538. salvage = _RowidRangeSalvage(
  539. source, destination, table, columns, chunk_size=chunk_size, progress_cb=progress_cb, source_rows=source_rows,
  540. insert_prefix=insert_prefix, row_filter=row_filter, result=result,
  541. )
  542. salvage.copy_range(int(bounds["low"]), int(bounds["high"]))
  543. skipped_ranges = result["skipped_rowid_ranges"]
  544. result["skipped_rowid_span"] = sum(item["high"] - item["low"] + 1 for item in skipped_ranges)
  545. result["query_limit_reached"] = salvage.stopped_at_query_limit
  546. if skipped_ranges:
  547. result["status"] = "partial" if result["copied_rows"] else "failed"
  548. result["error"] = f"{len(skipped_ranges)} rowid range(s) skipped"
  549. elif source_rows is not None and result["copied_rows"] + result["excluded_rows"] != source_rows:
  550. result["status"] = "partial"
  551. copied, excluded = result["copied_rows"], result["excluded_rows"]
  552. result["error"] = f"copied {copied} and excluded {excluded} of {source_rows} source rows"
  553. else:
  554. result["status"] = "complete"
  555. return result
  556. def _state_meta_result(source_rows: Optional[int], **extra: Any) -> dict[str, Any]:
  557. return {
  558. "source_meta_rows": source_rows, "copied_rows": 0, "columns": ["key", "value"],
  559. "excluded_keys": sorted(_GENERATED_META_KEYS), **extra,
  560. }
  561. def _state_meta_precheck(
  562. source: sqlite3.Connection, destination: sqlite3.Connection, source_rows: Optional[int], *, salvage: bool,
  563. ) -> Optional[dict[str, Any]]:
  564. """Terminal ``state_meta`` result when the key/value schema is unusable, else ``None``.
  565. In salvage mode an unusable-but-PRESENT table reports ``failed``, not ``missing``: verification
  566. only escalates ``failed``/``partial`` into a warning + ``loss_detected``, so ``missing`` would
  567. silently drop real metadata and still claim ``complete=True``.
  568. """
  569. extra = {"mode": "rowid_range_salvage"} if salvage else {}
  570. source_columns = _table_columns(source, "state_meta")
  571. if not {"key", "value"}.issubset(source_columns):
  572. if salvage and source_columns: # present but unusable: real data loss
  573. found = ", ".join(source_columns) or "none"
  574. error = f"source state_meta exists but is missing the key/value columns (found: {found})"
  575. return _state_meta_result(source_rows, **extra, status="failed", error=error)
  576. return _state_meta_result(source_rows, **extra, status="missing") # genuinely absent: nothing lost
  577. if not {"key", "value"}.issubset(_table_columns(destination, "state_meta")):
  578. error = "destination state_meta schema is incomplete"
  579. return _state_meta_result(source_rows, **extra, status="failed", error=error)
  580. return None
  581. def _copy_state_meta(
  582. source: sqlite3.Connection, destination: sqlite3.Connection, *, salvage: bool, chunk_size: int,
  583. progress_cb: Optional[ProgressCallback], source_rows: Optional[int],
  584. ) -> dict[str, Any]:
  585. """Copy user metadata rows; derived FTS/topic keys (``_GENERATED_META_KEYS``) are regenerated, not copied."""
  586. problem = _state_meta_precheck(source, destination, source_rows, salvage=salvage)
  587. if problem is not None:
  588. return problem
  589. if salvage:
  590. def keep_user_meta(row: tuple[Any, ...], columns: tuple[str, ...]) -> bool:
  591. return str(row[columns.index("key")]) not in _GENERATED_META_KEYS
  592. result = _copy_table_salvage(
  593. source, destination, "state_meta", chunk_size=chunk_size, progress_cb=progress_cb,
  594. source_rows=source_rows, insert_prefix="INSERT OR REPLACE", row_filter=keep_user_meta,
  595. )
  596. result["source_meta_rows"] = result.pop("source_rows")
  597. result["excluded_keys"] = sorted(_GENERATED_META_KEYS)
  598. return result
  599. placeholders = ", ".join("?" for _ in _GENERATED_META_KEYS)
  600. params = tuple(_GENERATED_META_KEYS)
  601. filtered_source_rows: Optional[int] = None
  602. try:
  603. filtered_source_rows = int(
  604. source.execute(f"SELECT COUNT(*) FROM state_meta WHERE key NOT IN ({placeholders})", params).fetchone()[0]
  605. )
  606. except sqlite3.DatabaseError:
  607. pass # the copy loop below will return the concrete read error
  608. return _copy_rows(
  609. source, destination, f"SELECT key, value FROM state_meta WHERE key NOT IN ({placeholders})", params,
  610. "INSERT OR REPLACE INTO state_meta(key, value) VALUES (?, ?)", table="state_meta", chunk_size=chunk_size,
  611. progress_cb=progress_cb, expected_rows=filtered_source_rows, result=_state_meta_result(source_rows),
  612. )
  613. def _placeholder_titles(destination: sqlite3.Connection, prefix: str) -> Iterator[str]:
  614. """Yield ``[<prefix> N] session metadata was unreadable`` titles not yet present in ``sessions``."""
  615. sequence = 1
  616. while True:
  617. title = f"[{prefix} {sequence}] session metadata was unreadable"
  618. sequence += 1
  619. if destination.execute("SELECT 1 FROM sessions WHERE title = ? LIMIT 1", (title,)).fetchone() is None:
  620. yield title
  621. def _reconstruct_missing_sessions(destination: sqlite3.Connection) -> dict[str, Any]:
  622. """Recreate placeholder session rows for salvaged orphaned messages.
  623. When ``sessions`` is damaged worse than ``messages``, deleting the recovered messages as orphans
  624. would discard the only readable copy of the user's data. Instead synthesize a minimal session per
  625. orphaned ``session_id`` (``started_at`` = earliest surviving message) so FKs hold.
  626. """
  627. result: dict[str, Any] = {"sessions_reconstructed": 0, "messages_retained": 0}
  628. if not (_table_columns(destination, "sessions") and _table_columns(destination, "messages")):
  629. return result
  630. orphaned = destination.execute(
  631. "SELECT m.session_id, MIN(m.timestamp), COUNT(*) FROM messages AS m WHERE m.session_id IS NOT NULL AND NOT "
  632. "EXISTS (SELECT 1 FROM sessions WHERE sessions.id = m.session_id) GROUP BY m.session_id"
  633. ).fetchall()
  634. if not orphaned:
  635. return result
  636. titles = _placeholder_titles(destination, "recovered")
  637. for session_id, first_timestamp, message_count in orphaned:
  638. started_at = float(first_timestamp) if first_timestamp is not None else 0.0
  639. title = next(titles)
  640. cursor = destination.execute(
  641. "INSERT INTO sessions (id, source, started_at, title, message_count) VALUES (?, 'recovered', ?, ?, ?)",
  642. (session_id, started_at, title, int(message_count)),
  643. )
  644. if cursor.rowcount != 1:
  645. raise sqlite3.IntegrityError(f"failed to reconstruct missing session {session_id!r}")
  646. result["sessions_reconstructed"] += 1
  647. result["messages_retained"] += int(message_count)
  648. return result
  649. def _reconcile(destination: sqlite3.Connection, table: str, where: str, mutation: str) -> int:
  650. """Count rows of ``table`` matching ``where``; run ``mutation WHERE where`` only when there are any."""
  651. count = int(destination.execute(f'SELECT COUNT(*) FROM "{table}" WHERE {where}').fetchone()[0])
  652. if count:
  653. destination.execute(f"{mutation} WHERE {where}")
  654. return count
  655. _DEPENDENT_TABLES = ("messages", "session_model_usage", "compression_locks", "telegram_dm_topic_bindings")
  656. _DANGLING_TOOL_PIN = (
  657. "length(tool_names) = 64 AND NOT EXISTS (SELECT 1 FROM system_prompts WHERE system_prompts.hash = sessions.tool_names)")
  658. _RELINK_COUNTERS = ("session_prompt_refs_cleared", "sessions_parent_cleared")
  659. def _cleanup_partial_orphans(destination: sqlite3.Connection) -> dict[str, Any]:
  660. """Reconcile references to unsalvageable sessions. Messages are never dropped for lack of a
  661. session row (placeholders are rebuilt first); only rows still orphaned after that are removed."""
  662. result: dict[str, Any] = {
  663. "session_prompt_refs_cleared": 0, "system_prompts_removed": 0, "sessions_parent_cleared": 0,
  664. "sessions_reconstructed": 0, "messages_retained": 0,
  665. **{f"{table}_removed": 0 for table in _DEPENDENT_TABLES},
  666. }
  667. with _immediate_transaction(destination):
  668. # Rebuild owners BEFORE any orphan deletion.
  669. result.update(_reconstruct_missing_sessions(destination))
  670. result["sessions_parent_cleared"] = _reconcile(
  671. destination, "sessions",
  672. "parent_session_id IS NOT NULL AND NOT EXISTS ("
  673. "SELECT 1 FROM sessions AS parent WHERE parent.id = sessions.parent_session_id)",
  674. "UPDATE sessions SET parent_session_id = NULL",
  675. )
  676. result["session_prompt_refs_cleared"] = _reconcile(
  677. destination, "sessions",
  678. "system_prompt_hash IS NOT NULL AND NOT EXISTS ("
  679. "SELECT 1 FROM system_prompts WHERE system_prompts.hash = sessions.system_prompt_hash)",
  680. "UPDATE sessions SET system_prompt_hash = NULL",
  681. )
  682. # A tools[] pin is a system_prompts row too, referenced by hash from sessions.tool_names
  683. # (legacy rows hold an inline JSON list, never 64 chars of hex).
  684. result["session_prompt_refs_cleared"] += _reconcile(
  685. destination, "sessions",
  686. _DANGLING_TOOL_PIN,
  687. "UPDATE sessions SET tool_names = NULL",
  688. )
  689. result["system_prompts_removed"] = _reconcile(
  690. destination, "system_prompts",
  691. "NOT EXISTS (SELECT 1 FROM sessions WHERE sessions.system_prompt_hash = system_prompts.hash) "
  692. "AND NOT EXISTS (SELECT 1 FROM sessions WHERE sessions.tool_names = system_prompts.hash)",
  693. "DELETE FROM system_prompts",
  694. )
  695. for table in _DEPENDENT_TABLES:
  696. if _table_columns(destination, table):
  697. result[f"{table}_removed"] = _reconcile(
  698. destination, table,
  699. f'NOT EXISTS (SELECT 1 FROM sessions WHERE sessions.id = "{table}".session_id)',
  700. f'DELETE FROM "{table}"',
  701. )
  702. # Only destructive/relinking actions count: reconstruction counters describe RETAINED data.
  703. result["total_removed_or_relinked"] = sum(
  704. int(result[key]) for key in (*_RELINK_COUNTERS, *(f"{table}_removed" for table in _DEPENDENT_TABLES))
  705. )
  706. return result
  707. def _verify_structure(conn: sqlite3.Connection, verification: dict[str, Any]) -> None:
  708. """Integrity, foreign keys, journal mode, schema version and FTS meta of the recovered file."""
  709. errors = verification["errors"]
  710. integrity_rows = [str(row[0]) for row in conn.execute("PRAGMA integrity_check").fetchall()]
  711. verification["integrity_check"] = integrity_rows
  712. if integrity_rows != ["ok"]:
  713. errors.append("PRAGMA integrity_check did not return exactly 'ok'")
  714. foreign_key_rows = [list(row) for row in conn.execute("PRAGMA foreign_key_check").fetchall()]
  715. verification["foreign_key_check"] = foreign_key_rows
  716. if foreign_key_rows:
  717. errors.append("foreign key violations remain")
  718. verification["journal_mode"] = _journal_mode(conn)
  719. schema_row = conn.execute("SELECT version FROM schema_version LIMIT 1").fetchone()
  720. verification["schema_version"] = int(schema_row[0]) if schema_row else None
  721. if verification["schema_version"] != SCHEMA_VERSION:
  722. errors.append(f"schema version is {verification['schema_version']}, expected {SCHEMA_VERSION}")
  723. meta = dict(conn.execute("SELECT key, value FROM state_meta WHERE key LIKE 'fts_%'").fetchall())
  724. meta = {str(key): value for key, value in meta.items()}
  725. verification["fts_meta"] = meta
  726. if meta.get("fts_storage_version") != str(FTS_STORAGE_VERSION):
  727. errors.append("fresh FTS storage version was not established")
  728. pending_keys = sorted(
  729. key for key in _GENERATED_META_KEYS if key.startswith("fts_") and key != "fts_storage_version" and key in meta
  730. )
  731. verification["pending_fts_keys"] = pending_keys
  732. if pending_keys:
  733. errors.append("derived FTS transition markers remain in the recovered database")
  734. def _verify_fts_indexes(conn: sqlite3.Connection, verification: dict[str, Any]) -> None:
  735. fts_checks: dict[str, str] = {}
  736. for table in ("messages_fts", "messages_fts_trigram", "messages_fts_cjk"):
  737. if not _table_columns(conn, table):
  738. continue
  739. try:
  740. conn.execute(f'INSERT INTO "{table}" ("{table}") VALUES (\'integrity-check\')')
  741. conn.execute(f'SELECT 1 FROM "{table}" WHERE "{table}" MATCH \'""\' LIMIT 1').fetchone()
  742. fts_checks[table] = "ok"
  743. except sqlite3.DatabaseError as exc:
  744. fts_checks[table] = str(exc)
  745. verification["errors"].append(f"{table} integrity check failed: {exc}")
  746. verification["fts_checks"] = fts_checks
  747. def _verify_row_counts(
  748. conn: sqlite3.Connection, verification: dict[str, Any], *, expected_counts: dict[str, Optional[int]],
  749. copy_report: dict[str, dict[str, Any]], allow_partial: bool, orphan_cleanup: Optional[dict[str, Any]],
  750. ) -> None:
  751. """Compare recovered counts and copy statuses against the source; classify shortfalls as loss."""
  752. def flag(message: str, *, soft: bool) -> None:
  753. """Record data loss as a warning (``soft``) or as a verification error."""
  754. if soft:
  755. verification["warnings"].append(message)
  756. verification["loss_detected"] = True
  757. else:
  758. verification["errors"].append(message)
  759. counts = {table: _count_rows(conn, table) for table in _INVENTORY_TABLES if _table_columns(conn, table)}
  760. verification["table_counts"] = counts
  761. for table in ("sessions", "messages", *_AUXILIARY_TABLES):
  762. expected = expected_counts.get(table)
  763. if expected is not None and counts.get(table) != expected:
  764. flag(f"{table} count is {counts.get(table)}, expected {expected}", soft=allow_partial)
  765. cleanup = orphan_cleanup or {}
  766. rebuilt_sessions = int(cleanup.get("sessions_reconstructed") or 0)
  767. retained_messages = int(cleanup.get("messages_retained") or 0)
  768. removed_messages = int(cleanup.get("messages_removed") or 0)
  769. # A wholly unreadable sessions b-tree is recoverable when every output parent was rebuilt from
  770. # the surviving messages and none were dropped: data loss, but not structural failure.
  771. sessions_fully_reconstructed = bool(
  772. rebuilt_sessions > 0 and counts.get("sessions") == rebuilt_sessions
  773. and counts.get("messages") == retained_messages and removed_messages == 0
  774. )
  775. for table, table_report in copy_report.items():
  776. status = table_report.get("status")
  777. if status not in {"failed", "partial"}:
  778. continue
  779. tolerable = (
  780. status == "partial" or table not in {"sessions", "messages"}
  781. or (table == "sessions" and sessions_fully_reconstructed)
  782. )
  783. flag(f"{table} copy status is {status}", soft=allow_partial and tolerable)
  784. if orphan_cleanup:
  785. orphan_count = int(orphan_cleanup.get("total_removed_or_relinked") or 0)
  786. if orphan_count:
  787. flag(f"{orphan_count} orphaned reference(s) were removed or relinked", soft=True)
  788. if rebuilt_sessions:
  789. # Not a clean recovery: the conversation text survived but its session metadata did not.
  790. flag(
  791. f"{rebuilt_sessions} session(s) could not be salvaged and were reconstructed as placeholders to "
  792. f"retain {retained_messages} message(s); their metadata (title, model, timestamps, cost) is lost",
  793. soft=True,
  794. )
  795. def _verify_recovered_database(
  796. output: Path, *, expected_counts: dict[str, Optional[int]], copy_report: dict[str, dict[str, Any]],
  797. allow_partial: bool = False, orphan_cleanup: Optional[dict[str, Any]] = None,
  798. ) -> dict[str, Any]:
  799. verification: dict[str, Any] = {"errors": [], "warnings": [], "loss_detected": False}
  800. open_error = _db_opens_cleanly(output)
  801. verification["opens_cleanly"] = open_error is None
  802. if open_error is not None:
  803. verification["errors"].append(f"database health probe: {open_error}")
  804. conn = sqlite3.connect(str(output), isolation_level=None)
  805. try:
  806. _verify_structure(conn, verification)
  807. _verify_row_counts(
  808. conn, verification, expected_counts=expected_counts, copy_report=copy_report, allow_partial=allow_partial,
  809. orphan_cleanup=orphan_cleanup,
  810. )
  811. _verify_fts_indexes(conn, verification)
  812. except sqlite3.DatabaseError as exc:
  813. verification["errors"].append(f"verification query failed: {exc}")
  814. finally:
  815. conn.close()
  816. verification["healthy"] = not verification["errors"]
  817. verification["complete"] = bool(verification["healthy"] and not verification["loss_detected"])
  818. return verification
  819. def _sanitize_session_model_config(destination: sqlite3.Connection) -> int:
  820. """Rewrite unparseable ``sessions.model_config`` blobs to ``'{}'``; returns the row count.
  821. ``integrity_check`` validates b-tree structure, never column *contents*: a row whose
  822. JSON was truncated by the damage verifies clean, and the recovered store then raises
  823. ``OperationalError: malformed JSON`` the first time ``reopen_session`` rewrites the
  824. reset-child markers with ``json_set`` (``hermes_state_sessions.py::reopen_session``) —
  825. i.e. on the first resume of a parent session. Read paths are already guarded
  826. (``_sql_json_extract`` wraps every extract in ``CASE WHEN json_valid``), so this is
  827. about the write path. The blob is unrecoverable either way, so neutralise it at the
  828. copy boundary both lanes pass through rather than shipping a store that breaks on
  829. the first resume.
  830. """
  831. if "model_config" not in _table_columns(destination, "sessions"):
  832. return 0
  833. with _immediate_transaction(destination):
  834. return _reconcile(
  835. destination, "sessions",
  836. "model_config IS NOT NULL AND json_valid(model_config) = 0",
  837. "UPDATE sessions SET model_config = '{}'",
  838. )
  839. def _repair_out_of_window_timestamps(destination: sqlite3.Connection) -> int:
  840. """Rewrite ``messages``/``sessions`` timestamp cells outside the ``coerce_epoch`` window; returns the count.
  841. A damaged cell can decode as a valid-looking garbage double (``5.49e+246``) that the salvage copies
  842. verbatim, and one such row pins the session's recency and breaks every renderer that turns it into
  843. a date (#91536). A message takes its nearest valid neighbour's time in the same session (keeps the
  844. order), then its session's start, then now; ``started_at`` takes the earliest valid message; the
  845. nullable ``ended_at``/``last_activity_at`` become NULL.
  846. """
  847. message_columns, session_columns = _table_columns(destination, "messages"), _table_columns(destination, "sessions")
  848. ok = f"BETWEEN {EPOCH_MIN!r} AND {EPOCH_MAX!r}"
  849. now = "CAST(strftime('%s', 'now') AS REAL)"
  850. repaired = 0
  851. with _immediate_transaction(destination):
  852. if "timestamp" in message_columns:
  853. repaired += _reconcile(
  854. destination, "messages", f"NOT (timestamp {ok})",
  855. "UPDATE messages SET timestamp = COALESCE("
  856. f"(SELECT p.timestamp FROM messages p WHERE p.session_id = messages.session_id AND p.id < messages.id"
  857. f" AND p.timestamp {ok} ORDER BY p.id DESC LIMIT 1),"
  858. f"(SELECT n.timestamp FROM messages n WHERE n.session_id = messages.session_id AND n.id > messages.id"
  859. f" AND n.timestamp {ok} ORDER BY n.id LIMIT 1),"
  860. f"(SELECT s.started_at FROM sessions s WHERE s.id = messages.session_id AND s.started_at {ok}), {now})",
  861. )
  862. if "started_at" in session_columns:
  863. repaired += _reconcile(
  864. destination, "sessions", f"NOT (started_at {ok})",
  865. "UPDATE sessions SET started_at = COALESCE((SELECT MIN(m.timestamp) FROM messages m"
  866. f" WHERE m.session_id = sessions.id AND m.timestamp {ok}), {now})",
  867. )
  868. for column in ("ended_at", "last_activity_at"):
  869. if column in session_columns:
  870. repaired += _reconcile(
  871. destination, "sessions", f"NOT ({column} {ok})", f"UPDATE sessions SET {column} = NULL")
  872. return repaired
  873. def _finalize_derived_metadata(destination: sqlite3.Connection) -> dict[str, Any]:
  874. """Sanitize copied JSON columns and timestamps, and stamp metadata the new destination actually owns."""
  875. model_config_reset = _sanitize_session_model_config(destination)
  876. timestamps_repaired = _repair_out_of_window_timestamps(destination)
  877. fts_tables = {
  878. str(row[0])
  879. for row in destination.execute(
  880. "SELECT name FROM sqlite_master WHERE type='table' AND name IN ('messages_fts', 'messages_fts_trigram')"
  881. ).fetchall()
  882. }
  883. result: dict[str, Any] = {
  884. "fts_tables": sorted(fts_tables), "finalized": False, "model_config_reset": model_config_reset,
  885. "timestamps_repaired": timestamps_repaired}
  886. if fts_tables != {"messages_fts", "messages_fts_trigram"}:
  887. result["error"] = "fresh destination is missing required FTS tables"
  888. return result
  889. fts_keys = tuple(key for key in _GENERATED_META_KEYS if key.startswith("fts_"))
  890. placeholders = ", ".join("?" for _ in fts_keys)
  891. with _immediate_transaction(destination):
  892. destination.execute(f"DELETE FROM state_meta WHERE key IN ({placeholders})", fts_keys)
  893. destination.execute(
  894. "INSERT INTO state_meta(key, value) VALUES (?, ?) ON CONFLICT(key) DO UPDATE SET value = excluded.value",
  895. ("fts_storage_version", str(FTS_STORAGE_VERSION)),
  896. )
  897. result["finalized"] = True
  898. return result
  899. def _lost_and_found_plausibility_errors(
  900. conn: sqlite3.Connection,
  901. ) -> list[str]:
  902. """Flag systematic timestamp mis-mapping in a salvaged database.
  903. Structural checks (integrity, FK, FTS, row counts) pass on mis-mapped
  904. salvage because every row still inserts. Only semantics give it away:
  905. the physical column order of a source upgraded via ALTER TABLE differs
  906. from the destination template's declared order, so positional cell
  907. mapping lands counters/strings where ``started_at``/``timestamp``
  908. belong — and the NOT NULL substitutes turn gaps into 0.0. When every
  909. mapped row violates the epoch floor, the mapping was wrong.
  910. Stub rows written by ``stub_missing_parent_sessions`` legitimately carry
  911. ``started_at = 0.0`` when no timestamped message survived, so they are
  912. excluded from the denominator.
  913. """
  914. from hermes_cli.session_lost_and_found import _EPOCH_LOW, STUB_TITLE_PREFIX
  915. errors: list[str] = []
  916. checks = (
  917. ("sessions", "started_at", f"WHERE COALESCE(title, '') NOT LIKE '{STUB_TITLE_PREFIX}%'"),
  918. ("messages", "timestamp", ""),
  919. )
  920. for table, column, mapped_filter in checks:
  921. (total,) = conn.execute(f"SELECT COUNT(*) FROM {table} {mapped_filter}").fetchone()
  922. if not total:
  923. continue
  924. (implausible,) = conn.execute(
  925. f"SELECT COUNT(*) FROM {table} {mapped_filter} "
  926. f"{'AND' if mapped_filter else 'WHERE'} ({column} IS NULL OR {column} < ?)",
  927. (_EPOCH_LOW,),
  928. ).fetchone()
  929. if implausible == total:
  930. errors.append(
  931. f"{table}.{column} is implausible in all {total} salvaged row(s) "
  932. "(NULL or before 2001-09): the source's physical column order "
  933. "did not match the destination template, so cells were mapped "
  934. "onto the wrong columns"
  935. )
  936. return errors
  937. def _recover_via_lost_and_found(
  938. *, source: Path, snapshot_source: Path, snapshot_dir: Path, output: Path, inspection: dict[str, Any],
  939. disk_space: dict[str, Any], missing_required: list[str],
  940. ) -> dict[str, Any]:
  941. """Best-effort page-level salvage when table schemas are unreadable: the sqlite3 CLI's ``.recover``
  942. (shell-only, not in Python's ``sqlite3``) rebuilds rows into a scratch lost_and_found database which
  943. is then heuristically mapped into a fresh current-schema database."""
  944. from hermes_cli.session_lost_and_found import (
  945. SQLITE3_CLI_GUIDANCE, LostAndFoundError, find_sqlite3_cli, find_sqlite3_cli_refusal, map_lost_and_found_rows,
  946. rebuild_fts_indexes, run_cli_lost_and_found_recover, stub_missing_parent_sessions,
  947. )
  948. missing = ", ".join(missing_required)
  949. sqlite3_bin = find_sqlite3_cli()
  950. if sqlite3_bin is None:
  951. refusal = find_sqlite3_cli_refusal()
  952. if refusal.get("reason") == "wal_reset_vulnerable":
  953. raise SessionRecoverySourceError(
  954. "Partial recovery requires a page-level salvage shell, but "
  955. "the only sqlite3 CLI on PATH is not safe to use for it: it "
  956. + refusal["detail"]
  957. + ". The readable table schemas for: "
  958. + ", ".join(missing_required)
  959. + " are still required."
  960. )
  961. raise SessionRecoverySourceError(
  962. f"Partial recovery still requires readable table schemas for: {missing}. {SQLITE3_CLI_GUIDANCE}"
  963. )
  964. lf_path = snapshot_dir / "lost_and_found.db"
  965. try:
  966. cli_report = run_cli_lost_and_found_recover(snapshot_source, lf_path, sqlite3_bin)
  967. except (LostAndFoundError, OSError) as exc:
  968. raise SessionRecoverySourceError(
  969. f"Partial recovery could not read the table schemas for: {missing}, "
  970. f"and page-level .recover salvage failed: {exc}"
  971. ) from exc
  972. lf_conn = sqlite3.connect(str(lf_path), isolation_level=None)
  973. destination_conn = _fresh_destination(output)
  974. try:
  975. mapping = map_lost_and_found_rows(lf_conn, destination_conn)
  976. stubbing = stub_missing_parent_sessions(destination_conn)
  977. fts = rebuild_fts_indexes(destination_conn)
  978. derived_metadata = _finalize_derived_metadata(destination_conn)
  979. finally:
  980. lf_conn.close()
  981. destination_conn.close()
  982. copy_report: dict[str, dict[str, Any]] = {
  983. table: {
  984. "mode": "lost_and_found_salvage", "status": "partial",
  985. "copied_rows": int(mapping["direct_table_rows"].get(table) or 0) + int(mapping["mapped"].get(table) or 0),
  986. "error": "recovered via page-level lost_and_found salvage; "
  987. "row completeness cannot be verified against the source",
  988. }
  989. for table in ("sessions", "messages", "session_model_usage")
  990. }
  991. orphan_cleanup = {
  992. "sessions_reconstructed": stubbing["sessions_stubbed"], "messages_retained": stubbing["messages_retained"],
  993. "messages_removed": 0, "total_removed_or_relinked": 0,
  994. }
  995. verification = _verify_recovered_database(
  996. output, expected_counts={"sessions": None, "messages": None}, copy_report=copy_report, allow_partial=True,
  997. orphan_cleanup=orphan_cleanup,
  998. )
  999. verification["warnings"].append(
  1000. "BEST-EFFORT page-level salvage: the source table schemas were unreadable, so rows were rebuilt from raw "
  1001. "pages and mapped heuristically. Review every count before trusting this output."
  1002. )
  1003. if cli_report.get("header_zeroed"):
  1004. verification["warnings"].append(
  1005. "header salvage: SQLite refused the source outright (page-1 header damaged, 'file is not a "
  1006. "database'); the header of the private snapshot copy was zeroed so .recover could walk the "
  1007. "surviving pages. Rows written only to a -wal after the last checkpoint are not included."
  1008. )
  1009. verification.update(loss_detected=True, complete=False)
  1010. # Structural checks cannot see a positional mis-mapping: every row still inserts, so integrity/FK/FTS
  1011. # stay green. A systematic timestamp violation is the semantic tell — never report such a salvage as verified.
  1012. # See #101409.
  1013. plausibility_conn = sqlite3.connect(str(output), isolation_level=None)
  1014. try:
  1015. plausibility_errors = _lost_and_found_plausibility_errors(plausibility_conn)
  1016. finally:
  1017. plausibility_conn.close()
  1018. # Records whose width matched no known physical layout were inserted
  1019. # positionally as a guess. Once the recognised rows map correctly the
  1020. # all-rows timestamp gate above no longer sees them, so they need their
  1021. # own tell: the guessed rows may be shifted while the report otherwise
  1022. # looks clean.
  1023. guessed = int(mapping.get("unrecognized_layout_rows") or 0)
  1024. if guessed:
  1025. widths = ", ".join(
  1026. f"{kind} rows with {'/'.join(map(str, sorted(ws)))} fields"
  1027. for kind, ws in sorted((mapping.get("unrecognized_layout_widths") or {}).items())
  1028. )
  1029. plausibility_errors.append(
  1030. f"{guessed} salvaged row(s) matched no known physical column layout and were mapped "
  1031. f"positionally ({widths}); their fields may be shifted. Inspect those sessions before trusting them."
  1032. )
  1033. if plausibility_errors:
  1034. verification["errors"].extend(plausibility_errors)
  1035. verification["healthy"] = False
  1036. return _recovery_report(
  1037. source, output, inspection, disk_space, verification, on_source_change="healthy",
  1038. allow_partial=True, mode="lost_and_found_salvage", best_effort=True, unreadable_schemas=missing_required,
  1039. sqlite3_cli=cli_report, lost_and_found=mapping, session_stubs=stubbing, fts_rebuild=fts, copy=copy_report,
  1040. orphan_cleanup=orphan_cleanup, derived_metadata=derived_metadata,
  1041. )
  1042. def _recovery_report(
  1043. source: Path, output: Path, inspection: dict[str, Any], disk_space: dict[str, Any], verification: dict[str, Any],
  1044. *, on_source_change: str, **fields: Any,
  1045. ) -> dict[str, Any]:
  1046. """The ``recover`` report: shared header, mode-specific ``fields``, then the verdict. A source bundle
  1047. that changed during recovery is a verification error and also clears ``verification[on_source_change]``.
  1048. """
  1049. source_unchanged = _source_fingerprint(source) == inspection["source_fingerprint"]
  1050. if not source_unchanged:
  1051. verification["errors"].append("the source database bundle changed during recovery")
  1052. verification[on_source_change] = False
  1053. return {
  1054. "operation": "recover", "source": str(source), "output": str(output),
  1055. "source_bundle": inspection["source_bundle"], "source_fingerprint": inspection["source_fingerprint"],
  1056. "source_unchanged": source_unchanged, "disk_space": disk_space,
  1057. "inspection": {
  1058. "journal_mode": inspection.get("journal_mode"), "tables": inspection["tables"],
  1059. "errors": inspection["errors"], "warnings": inspection["warnings"],
  1060. },
  1061. **fields,
  1062. "verification": verification,
  1063. "complete": bool(verification.get("complete") and source_unchanged),
  1064. "partial": bool(verification.get("loss_detected")),
  1065. "verified": bool(verification.get("healthy") and source_unchanged),
  1066. "installed": False,
  1067. "runtime_state": {
  1068. "policy": "transcript-only; runtime ledger excluded; fresh epoch on activation",
  1069. "excluded_tables": {table: inspection["tables"].get(table, {}) for table in _RUNTIME_TABLES},
  1070. },
  1071. }
  1072. def recover_session_database(
  1073. source_path: Path, output_path: Path, *, work_dir: Optional[Path] = None, chunk_size: int = 1_000,
  1074. progress_cb: Optional[ProgressCallback] = None, allow_partial: bool = False,
  1075. ) -> dict[str, Any]:
  1076. """Separate-output salvage is allowed; a canonical output requires offline ownership."""
  1077. from gateway.runtime_ownership import OwnershipConflict, exclusive_maintenance
  1078. from contextlib import nullcontext
  1079. # Validate before creating any lock or output. Reserve only a canonical
  1080. # destination, not the live source or an unrelated requested output file.
  1081. _, output, _ = _validate_paths(source_path, output_path=output_path, work_dir=work_dir)
  1082. assert output is not None
  1083. guard = exclusive_maintenance([output.parent]) if output.name == 'state.db' else nullcontext()
  1084. try:
  1085. with guard:
  1086. # Repeat the no-overwrite validation under the reservation: startup
  1087. # may have created state.db between the first check and lock claim.
  1088. return _recover_session_database_output(
  1089. source_path, output_path, work_dir=work_dir, chunk_size=chunk_size,
  1090. progress_cb=progress_cb, allow_partial=allow_partial,
  1091. )
  1092. except OwnershipConflict as exc:
  1093. raise SessionRecoverySafetyError(str(exc)) from exc
  1094. def _recover_session_database_output(
  1095. source_path: Path, output_path: Path, *, work_dir: Optional[Path] = None, chunk_size: int = 1_000,
  1096. progress_cb: Optional[ProgressCallback] = None, allow_partial: bool = False,
  1097. ) -> dict[str, Any]:
  1098. """Recover canonical rows into a separate current-schema database. The source and its sidecars are
  1099. copied before SQLite opens anything; ``output_path`` must not exist and is never swapped into place."""
  1100. if chunk_size <= 0:
  1101. raise SessionRecoverySafetyError("chunk_size must be greater than zero")
  1102. source, output, work_root = _validate_paths(source_path, output_path=output_path, work_dir=work_dir)
  1103. assert output is not None
  1104. disk_space = _disk_space_preflight(source, work_root, output.parent)
  1105. temp_dir, snapshot_source, inspection = _snapshot_and_inspect(source, work_root)
  1106. try:
  1107. if not inspection.get("recoverable") and not allow_partial:
  1108. reasons = "; ".join(inspection.get("errors") or ["unknown source error"])
  1109. raise SessionRecoverySourceError(
  1110. f"Required canonical tables are not readable: {reasons}. "
  1111. "Re-run with --allow-partial to salvage every readable row "
  1112. "into a new database (the source is never modified)."
  1113. )
  1114. missing_required = [t for t in ("sessions", "messages") if not inspection["tables"][t].get("available")]
  1115. if allow_partial and missing_required: # no readable schema -> page-level lost_and_found salvage
  1116. return _recover_via_lost_and_found(
  1117. source=source, snapshot_source=snapshot_source, snapshot_dir=Path(temp_dir.name), output=output,
  1118. inspection=inspection, disk_space=disk_space, missing_required=missing_required,
  1119. )
  1120. source_conn = _connect(snapshot_source)
  1121. source_conn.execute("PRAGMA writable_schema=ON")
  1122. destination_conn: Optional[sqlite3.Connection] = None
  1123. try:
  1124. destination_conn = _fresh_destination(
  1125. output, topic_tables=any(inspection["tables"][table].get("available") for table in _TOPIC_TABLES),
  1126. )
  1127. copy_report: dict[str, dict[str, Any]] = {}
  1128. for table in (*_CANONICAL_TABLES, "state_meta", *_TOPIC_TABLES, *_AUXILIARY_TABLES):
  1129. if table not in _CANONICAL_TABLES and not inspection["tables"][table].get("available"):
  1130. copy_report[table] = {"status": "missing", "copied_rows": 0}
  1131. continue
  1132. if table in _AUXILIARY_TABLES: # lazy gateway table: create it or the copy reports "missing"
  1133. _AUXILIARY_TABLE_SCHEMAS[table](destination_conn)
  1134. copy_report[table] = _copy_table(
  1135. source_conn, destination_conn, table, salvage=allow_partial, chunk_size=chunk_size,
  1136. progress_cb=progress_cb, source_rows=inspection["tables"][table].get("rows"),
  1137. )
  1138. orphan_cleanup = _cleanup_partial_orphans(destination_conn) if allow_partial else None
  1139. derived_metadata = _finalize_derived_metadata(destination_conn)
  1140. finally:
  1141. source_conn.close()
  1142. if destination_conn is not None:
  1143. destination_conn.close()
  1144. expected_counts = {
  1145. table: inspection["tables"][table].get("rows")
  1146. for table in (*_CANONICAL_TABLES, *_AUXILIARY_TABLES)
  1147. if table in _CANONICAL_TABLES or inspection["tables"].get(table, {}).get("available")
  1148. }
  1149. verification = _verify_recovered_database(
  1150. output, expected_counts=expected_counts, copy_report=copy_report, allow_partial=allow_partial,
  1151. orphan_cleanup=orphan_cleanup,
  1152. )
  1153. return _recovery_report(
  1154. source, output, inspection, disk_space, verification, on_source_change="complete",
  1155. allow_partial=allow_partial, copy=copy_report, orphan_cleanup=orphan_cleanup,
  1156. derived_metadata=derived_metadata,
  1157. )
  1158. finally:
  1159. temp_dir.cleanup()
  1160. def write_recovery_report(path: Path, report: dict[str, Any]) -> Path:
  1161. """Write a JSON report without overwriting an existing file."""
  1162. destination = _resolved_output_path(path)
  1163. with destination.open("x", encoding="utf-8") as handle:
  1164. json.dump(report, handle, indent=2, sort_keys=True)
  1165. handle.write("\n")
  1166. return destination