session_lost_and_found.py 41 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888
  1. """Last-resort page-level salvage for an unreadable session database schema, via the sqlite3 shell's
  2. ``.recover`` (rows it cannot attribute to a schema land in ``lost_and_found`` tables:
  3. ``rootpgno, pgno, nfield, id, c0..cN``)."""
  4. from __future__ import annotations
  5. import logging
  6. import os
  7. import re
  8. import shutil
  9. import sqlite3
  10. import subprocess
  11. import tempfile
  12. from pathlib import Path
  13. from typing import Any, Callable, Optional, Sequence
  14. from hermes_cli.session_schema_history import SCHEMA_HISTORY, reachable_physical_layouts
  15. from hermes_state_ids import is_known_session_id # every minted id shape: sentinel for schema-less rows
  16. from hermes_cli.session_recovery import (
  17. _AUXILIARY_TABLE_SCHEMAS, _AUXILIARY_TABLES, _CANONICAL_TABLES, _DANGLING_TOOL_PIN, _count_rows,
  18. _immediate_transaction, _placeholder_titles, _quoted_columns, _table_columns,
  19. )
  20. logger = logging.getLogger(__name__)
  21. MESSAGE_ROLES = frozenset({"user", "assistant", "tool", "system"})
  22. # Values observed in sessions.source across gateway platforms and tooling.
  23. KNOWN_SOURCES = frozenset({
  24. "cli", "telegram", "discord", "slack", "whatsapp", "signal", "matrix",
  25. "irc", "email", "x", "twitter", "api", "gateway", "web", "dashboard",
  26. "tool", "subagent", "cron", "recovered", "imported", "acp",
  27. })
  28. # A record's field count is the column count of the table when the row was last written. It classifies
  29. # the record but says nothing about which column each cell belongs to — live stores gain columns through
  30. # ``_reconcile_columns()`` (ALTER TABLE ADD COLUMN), appended in physical add order, while SCHEMA_SQL
  31. # declares several mid-definition (#101409). Cells are mapped by name through the layout
  32. # ``infer_physical_layouts`` recovers, never zipped onto declared order.
  33. SESSIONS_LEGACY_MINIMAL_NFIELD = 14
  34. SESSION_MODEL_USAGE_NFIELD = 18
  35. # Per-table cells whose salvaged value must look like what the column name
  36. # says. On stores created at the original schema these sit in the shared
  37. # base prefix, so they veto a width outright (-> unrecognized) rather than
  38. # pick between candidates; the per-column type and text-shape rules below
  39. # carry the discrimination between layouts.
  40. _LAYOUT_SENTINELS: dict[str, tuple[str, ...]] = {
  41. "sessions": ("id", "source", "started_at"),
  42. "messages": ("session_id", "role", "timestamp"),
  43. "session_model_usage": ("session_id", "model"),
  44. }
  45. # Plausible unix-epoch window for started_at heuristics on legacy layouts.
  46. _EPOCH_LOW = 1_000_000_000.0 # 2001
  47. _EPOCH_HIGH = 4_000_000_000.0 # 2096
  48. # Title label/prefix of every session row this lane synthesises (legacy-layout rows and stubbed parents).
  49. # The recovery verifier keys on the prefix to tell synthesised rows from positionally mapped ones.
  50. _STUB_TITLE_LABEL = "best-effort recovered"
  51. STUB_TITLE_PREFIX = f"[{_STUB_TITLE_LABEL}"
  52. SQLITE3_CLI_GUIDANCE = (
  53. "A last-resort page-level salvage is available when a `.recover`-capable `sqlite3` command-line shell is "
  54. "installed: its `.recover` command can rebuild rows into lost_and_found tables even when the table schemas are "
  55. "unreadable (this is a CLI-only feature, not part of Python's sqlite3 module, and some distro builds lack it — "
  56. "the shell must include the sqlite_dbpage extension, as the official builds from sqlite.org do). Install such a "
  57. "sqlite3 CLI (e.g. `brew install sqlite` or the precompiled sqlite-tools from sqlite.org) so it is on PATH, then "
  58. "re-run with --allow-partial."
  59. )
  60. # SQLite's WAL-reset bug (https://sqlite.org/wal.html#walresetbug) lets a
  61. # fresh opener unlink a live WAL/SHM sidecar pair and split the database into
  62. # two concurrent generations whose acknowledged writes can silently vanish.
  63. # It is real in CLI builds up to 3.51.2; fixed in 3.51.3+ with backports
  64. # 3.50.7 and 3.44.6 — the same version gate hermes_state applies to the
  65. # embedded library (#69784). The system `sqlite3` CLI on Debian/Ubuntu is
  66. # routinely in the vulnerable band (e.g. 3.45.1), and #100368's forensics
  67. # caught exactly this shell converting a live Hermes state.db into two
  68. # generations. A salvage shell must therefore be version-gated, not just
  69. # capability-gated, before it is pointed at (a copy of) a Hermes database.
  70. #
  71. # The predicate lives in hermes_cli.sqlite_runtime (stdlib-only, shared with
  72. # the installer/update gates) so the embedded runtime and the salvage shell
  73. # can never disagree about which versions are safe.
  74. from hermes_cli.sqlite_runtime import is_sqlite_wal_reset_vulnerable as _wal_reset_vulnerable
  75. _WAL_RESET_VULNERABLE_GUIDANCE = (
  76. "salvage against a Hermes database with the WAL-reset bug "
  77. "(https://sqlite.org/wal.html#walresetbug, fixed in 3.51.3+ / backports "
  78. "3.50.7 / 3.44.6; the vulnerable fresh-opener can unlink a live WAL/SHM "
  79. "pair and split the database into two generations, losing acknowledged "
  80. "writes — #100368). Install a fixed sqlite3 CLI (3.51.3+, e.g. `brew "
  81. "install sqlite` or the precompiled sqlite-tools from sqlite.org)"
  82. )
  83. class LostAndFoundError(RuntimeError):
  84. """Raised when the CLI .recover pass cannot produce a usable database."""
  85. def _parse_sqlite3_cli_version(binary: str) -> Optional[tuple[int, int, int]]:
  86. """Version of the sqlite3 CLI at *binary* via ``--version``, or None when it cannot run or be parsed."""
  87. try:
  88. probe = subprocess.run([binary, "--version"], capture_output=True, timeout=30, check=False)
  89. except (OSError, subprocess.SubprocessError):
  90. return None
  91. if probe.returncode != 0:
  92. return None
  93. match = re.search(rb"(\d+)\.(\d+)\.(\d+)", probe.stdout)
  94. if match is None:
  95. return None
  96. return tuple(int(part) for part in match.groups())
  97. _last_cli_refusal: dict[str, Any] = {}
  98. def find_sqlite3_cli_refusal() -> dict[str, Any]:
  99. """Why the last :func:`find_sqlite3_cli` call in this process refused: ``{"reason": ...}`` with reason in
  100. ``missing``, ``no_dbpage`` (shell cannot run ``.recover``), ``wal_reset_vulnerable``; empty if it succeeded."""
  101. return dict(_last_cli_refusal)
  102. def find_sqlite3_cli() -> Optional[str]:
  103. """A salvage-safe ``.recover``-capable sqlite3 CLI path, or None.
  104. PATH presence is not enough, and neither is ``.recover`` support alone: (1) distro builds can lack the
  105. ``sqlite_dbpage`` virtual table ``.recover`` needs — probed once on a scratch DB; (2) a capable CLI can still
  106. carry the WAL-reset opener bug (fixed 3.51.3+ / backports 3.50.7 / 3.44.6). The salvage lane only runs it on a
  107. snapshot copy, but refusing it keeps vulnerable shells out of the documented workflow. Refusals are recorded
  108. for :func:`find_sqlite3_cli_refusal` so callers can say exactly what to install.
  109. """
  110. global _last_cli_refusal
  111. _last_cli_refusal = {}
  112. binary = shutil.which("sqlite3")
  113. if binary is None:
  114. _last_cli_refusal = {"reason": "missing"}
  115. return None
  116. if not _cli_supports_recover(binary):
  117. _last_cli_refusal = {"reason": "no_dbpage", "binary": binary}
  118. return None
  119. version = _parse_sqlite3_cli_version(binary)
  120. if version is not None and _wal_reset_vulnerable(version):
  121. version_str = ".".join(str(part) for part in version)
  122. logger.warning(
  123. "sqlite3 CLI %s reports version %s, which still carries the "
  124. "WAL-reset opener bug; refusing to use it for salvage",
  125. binary,
  126. version_str,
  127. )
  128. _last_cli_refusal = {
  129. "reason": "wal_reset_vulnerable",
  130. "binary": binary,
  131. "version": version_str,
  132. "detail": f"reports version {version_str}, which has " + _WAL_RESET_VULNERABLE_GUIDANCE,
  133. }
  134. return None
  135. return binary
  136. def _cli_supports_recover(binary: str) -> bool:
  137. """True when ``binary`` can run ``.recover`` (has sqlite_dbpage)."""
  138. scratch_dir = tempfile.mkdtemp(prefix="hermes-recover-probe-")
  139. scratch = Path(scratch_dir) / "probe.db"
  140. try:
  141. conn = sqlite3.connect(str(scratch))
  142. try:
  143. conn.execute("CREATE TABLE t (x)")
  144. conn.execute("INSERT INTO t VALUES (1)")
  145. conn.commit()
  146. finally:
  147. conn.close()
  148. probe = subprocess.run([binary, "-readonly", str(scratch), ".recover"], capture_output=True, timeout=30, check=False)
  149. return probe.returncode == 0 and b"sqlite_dbpage" not in probe.stderr
  150. except (OSError, subprocess.SubprocessError, sqlite3.Error):
  151. return False
  152. finally:
  153. shutil.rmtree(scratch_dir, ignore_errors=True)
  154. SQLITE_HEADER_LENGTH = 100
  155. def run_cli_lost_and_found_recover(
  156. source: Path, lf_path: Path, sqlite3_bin: str, *, timeout: float = 3600.0,
  157. ) -> dict[str, Any]:
  158. """Run ``sqlite3 <source> .recover`` streamed into a fresh scratch DB.
  159. A file whose page-1 header is garbage (SIGKILL mid-write) is refused outright by the shell
  160. (``file is not a database``, rc 26) although the data pages after it survive. ``.recover``
  161. walks pages via sqlite_dbpage and only trips on the magic check, so on that refusal the
  162. 100-byte header of the private snapshot is zeroed and the attempts rerun; a zeroed header
  163. makes .recover infer page size and layout from the pages themselves (a spliced donor header
  164. would instead report a database size/freelist that contradicts the file). ``source`` is
  165. the caller's snapshot copy, never the user's file (#106667).
  166. """
  167. attempts = _cli_recover_attempts(source, lf_path, sqlite3_bin, timeout=timeout)
  168. if attempts[-1]["usable"]:
  169. return {"binary": sqlite3_bin, "attempts": attempts}
  170. if any("not a database" in a["dump_stderr_tail"] for a in attempts):
  171. with source.open("r+b") as handle:
  172. handle.write(bytes(SQLITE_HEADER_LENGTH))
  173. attempts += _cli_recover_attempts(source, lf_path, sqlite3_bin, timeout=timeout)
  174. if attempts[-1]["usable"]:
  175. return {"binary": sqlite3_bin, "attempts": attempts, "header_zeroed": True}
  176. details = "; ".join(
  177. f"[{a['command']}] dump rc={a['dump_returncode']} load rc={a['load_returncode']} "
  178. f"{a['dump_stderr_tail'] or a['load_stderr_tail']}".strip()
  179. for a in attempts
  180. )
  181. raise LostAndFoundError(f"sqlite3 .recover did not produce a usable lost_and_found database: {details}")
  182. def _cli_recover_attempts(source: Path, lf_path: Path, sqlite3_bin: str, *, timeout: float) -> list[dict[str, Any]]:
  183. """``--ignore-freelist`` first (no resurrected deleted rows), plain ``.recover`` for older shells."""
  184. attempts: list[dict[str, Any]] = []
  185. for command in (".recover --ignore-freelist", ".recover"):
  186. if lf_path.exists():
  187. lf_path.unlink()
  188. # .recover reports per-page diagnostics on stderr; a heavily damaged source can emit
  189. # far more than a pipe buffer holds, so stderr drains to a temp file — an undrained
  190. # PIPE would block the dump child, stall load's stdin, and burn the whole timeout.
  191. with tempfile.TemporaryFile(prefix="hermes-recover-dump-stderr-") as dump_stderr:
  192. dump = subprocess.Popen(
  193. [sqlite3_bin, "-readonly", str(source), command], stdout=subprocess.PIPE, stderr=dump_stderr
  194. )
  195. load = subprocess.Popen(
  196. [sqlite3_bin, str(lf_path)], stdin=dump.stdout, stdout=subprocess.DEVNULL, stderr=subprocess.PIPE
  197. )
  198. assert dump.stdout is not None
  199. dump.stdout.close() # let dump receive SIGPIPE if load dies
  200. try:
  201. _, load_err = load.communicate(timeout=timeout)
  202. dump.wait(timeout=60)
  203. dump_stderr.seek(0, os.SEEK_END)
  204. tail_size = min(dump_stderr.tell(), 65536)
  205. dump_stderr.seek(-tail_size, os.SEEK_END)
  206. dump_err = dump_stderr.read()
  207. except subprocess.TimeoutExpired:
  208. dump.kill()
  209. load.kill()
  210. dump.wait() # reap: kill() alone leaves returncode None and a zombie until GC
  211. load.wait()
  212. raise LostAndFoundError(f"sqlite3 .recover timed out after {timeout:.0f}s")
  213. attempts.append({
  214. "command": command, "dump_returncode": dump.returncode, "load_returncode": load.returncode,
  215. "dump_stderr_tail": dump_err.decode("utf-8", "replace")[-2000:],
  216. "load_stderr_tail": load_err.decode("utf-8", "replace")[-2000:],
  217. "usable": _lost_and_found_db_usable(lf_path),
  218. })
  219. if attempts[-1]["usable"]:
  220. break
  221. return attempts
  222. def _lost_and_found_db_usable(lf_path: Path) -> bool:
  223. if not lf_path.exists() or lf_path.stat().st_size == 0:
  224. return False
  225. try:
  226. conn = sqlite3.connect(str(lf_path))
  227. try:
  228. return conn.execute("SELECT 1 FROM sqlite_master WHERE type='table' LIMIT 1").fetchone() is not None
  229. finally:
  230. conn.close()
  231. except sqlite3.DatabaseError:
  232. return False
  233. def _notnull_defaults(conn: sqlite3.Connection, table: str) -> dict[int, Any]:
  234. """Column index -> substitute for NOT NULL columns. Salvage can return NULLs where the schema says
  235. NOT NULL (torn cells, old rows); dropping a row over one damaged counter would defeat the lane, so
  236. such NULLs get the schema default (or '' / 0 when none is declared)."""
  237. substitutes: dict[int, Any] = {}
  238. for index, row in enumerate(conn.execute(f'PRAGMA table_info("{table}")')):
  239. if not row[3]: # notnull flag
  240. continue
  241. if row[4] is not None:
  242. substitutes[index] = _parse_sql_default(str(row[4]))
  243. else:
  244. declared = str(row[2] or "").upper()
  245. substitutes[index] = 0 if ("INT" in declared or "REAL" in declared) else ""
  246. return substitutes
  247. def _parse_sql_default(text: str) -> Any:
  248. """Coerce a ``PRAGMA table_info`` default literal: quoted string, int, float, or raw."""
  249. if text.startswith("'") and text.endswith("'"):
  250. return text[1:-1]
  251. for cast in (int, float):
  252. try:
  253. return cast(text)
  254. except ValueError:
  255. continue
  256. return text
  257. def _is_session_id(value: Any) -> bool:
  258. """A cell that can be a session id: any shape in ``SESSION_ID_RECOGNIZERS``."""
  259. return isinstance(value, str) and is_known_session_id(value)
  260. def _looks_like_source(value: Any) -> bool:
  261. return bool(value) and isinstance(value, str) and (
  262. value in KNOWN_SOURCES or bool(re.fullmatch(r"[a-z][a-z0-9_-]{0,31}", value))
  263. )
  264. def _session_source_index(cells: tuple[Any, ...]) -> Optional[int]:
  265. if len(cells) > 1 and _looks_like_source(cells[1]):
  266. return 1
  267. # Fresh runtime schemas inserted counters after id; upgraded databases
  268. # append those same columns. Width alone cannot distinguish the layouts.
  269. if (len(cells) > 3 and _looks_like_source(cells[3])
  270. and all(v is None or isinstance(v, int) for v in cells[1:3])):
  271. return 3
  272. return None
  273. def classify_lost_and_found_row(nfield: int, cells: tuple[Any, ...]) -> Optional[str]:
  274. """Classify one lost_and_found record by field count + sentinel values."""
  275. if len(cells) >= 3 and cells[0] is None:
  276. # Rowid-alias tables store their INTEGER PRIMARY KEY as NULL; messages is the only canonical
  277. # table shaped like that with a session id second and a role third.
  278. is_message = (
  279. isinstance(cells[1], str) and cells[1] and isinstance(cells[2], str) and cells[2] in MESSAGE_ROLES
  280. )
  281. return "messages" if is_message else None
  282. if not _is_session_id(cells[0] if cells else None):
  283. return None
  284. second = cells[1] if len(cells) > 1 else None
  285. if nfield == SESSION_MODEL_USAGE_NFIELD: # session id first, model string second
  286. return "session_model_usage" if isinstance(second, str) and second else None
  287. # Any historical sessions width: session id first + recognizable source (every sessions layout ever
  288. # shipped has at least the 14 original columns). Stores created at the runtime-authority schema carry
  289. # runtime_revision/runtime_generation right after id, so the source cell sits at 3 there (#101409 lane).
  290. if nfield >= SESSIONS_LEGACY_MINIMAL_NFIELD and _session_source_index(cells) is not None:
  291. return "sessions"
  292. return None
  293. def _heuristic_started_at(cells: tuple[Any, ...]) -> float:
  294. for value in cells:
  295. if isinstance(value, (int, float)) and _EPOCH_LOW <= float(value) <= _EPOCH_HIGH:
  296. return float(value)
  297. return 0.0
  298. def _insert_prefix_row(
  299. dest: sqlite3.Connection, table: str, dest_columns: list[str], values: list[Any],
  300. notnull_substitutes: Optional[dict[int, Any]] = None,
  301. ) -> bool:
  302. if notnull_substitutes:
  303. values = [
  304. notnull_substitutes[index] if value is None and index in notnull_substitutes else value
  305. for index, value in enumerate(values)
  306. ]
  307. return _execute_insert(dest, table, dest_columns[: len(values)], values)
  308. def _sessions_prefix_columns(
  309. columns: list[str], defaults: dict[int, Any], cells: tuple[Any, ...],
  310. ) -> tuple[list[str], dict[int, Any]]:
  311. """Positional fallback for a sessions record no history layout claimed.
  312. The destination declares runtime_revision/runtime_generation right after ``id``; an
  313. upgraded store appended them at the end instead. Which order produced the record is
  314. read off the cells (where the source sits), so the prefix zip lands ``source`` on
  315. ``source`` instead of shifting every field by two.
  316. """
  317. runtime_columns = ["runtime_revision", "runtime_generation"]
  318. base_columns = [c for c in columns if c not in runtime_columns]
  319. ordered = (
  320. [base_columns[0], *runtime_columns, *base_columns[1:]]
  321. if _session_source_index(cells) == 3
  322. else [*base_columns, *runtime_columns]
  323. )
  324. named_defaults = {columns[i]: value for i, value in defaults.items()}
  325. return ordered, {i: named_defaults[c] for i, c in enumerate(ordered) if c in named_defaults}
  326. def _declared_types(conn: sqlite3.Connection, table: str) -> dict[str, str]:
  327. return {str(row[1]): str(row[2] or "") for row in conn.execute(f'PRAGMA table_info("{table}")')}
  328. def _type_conflicts(value: Any, declared: str) -> bool:
  329. """True when a salvaged cell cannot have come from a column of this type.
  330. Stricter than SQLite affinity on purpose: SQLite would happily keep a
  331. non-numeric string in an INTEGER column, but Hermes never writes one, so
  332. text sitting where a counter is declared means the layout is wrong. A
  333. TEXT column always yields ``str`` (numbers are coerced on write), a REAL
  334. column always yields ``float``, an INTEGER column yields ``int``.
  335. """
  336. if value is None:
  337. return False
  338. affinity = declared.upper()
  339. if "CHAR" in affinity or "CLOB" in affinity or "TEXT" in affinity:
  340. return not isinstance(value, str)
  341. if "REAL" in affinity or "FLOA" in affinity or "DOUB" in affinity:
  342. return not isinstance(value, float)
  343. if "INT" in affinity:
  344. return not isinstance(value, int)
  345. return False
  346. # Shapes of the text columns that move between the declared and physical
  347. # orders. A salvaged string that does not fit the shape rules out the
  348. # column for that position; a string that fits does not prove it. Only
  349. # invariants every writer in this repo honours belong here (this is how the
  350. # columns are actually written, not how they could theoretically be).
  351. _SESSION_KEY_PATTERN = re.compile(r"^agent:[^:]+:")
  352. # Machine tokens (``compression``, ``agent.compression_timeout``, ``api_key``,
  353. # ``compute_host_shutdown``): never spaces, never mixed case.
  354. _TOKEN_PATTERN = re.compile(r"^[a-z][a-z0-9_.:-]*$")
  355. # The closed sets SessionDB writes (hermes_state.py: request/claim/complete/
  356. # fail_handoff; TITLE_SOURCE_* + _title_rank). chat_type is platform-supplied
  357. # and open-ended, so it only gets the identifier shape.
  358. _HANDOFF_STATES = frozenset({"pending", "running", "completed", "failed"})
  359. _TITLE_SOURCES = frozenset({"derived", "llm", "user"})
  360. def _is_epoch(value: Any) -> bool:
  361. return isinstance(value, (int, float)) and _EPOCH_LOW <= float(value) <= _EPOCH_HIGH
  362. def _is_nonempty_str(value: Any) -> bool:
  363. return isinstance(value, str) and bool(value)
  364. # Sentinel columns: the cells that differ hardest between candidate layouts, so a wrong layout is
  365. # rejected instead of silently shifting every field. messages.id is a rowid alias, never a sentinel.
  366. _SENTINEL_RULES: dict[str, Callable[[Any], bool]] = {
  367. "id": _is_session_id,
  368. "source": _looks_like_source,
  369. "started_at": _is_epoch,
  370. "timestamp": _is_epoch,
  371. "session_id": _is_nonempty_str,
  372. "role": lambda value: value in MESSAGE_ROLES,
  373. "model": _is_nonempty_str,
  374. }
  375. def _sentinel_holds(name: str, value: Any) -> bool:
  376. rule = _SENTINEL_RULES.get(name)
  377. return rule(value) if rule else True
  378. def _is_token(value: str) -> bool:
  379. return bool(_TOKEN_PATTERN.match(value))
  380. def _is_json_start(value: str) -> bool:
  381. return value[:1] in "{["
  382. def _is_path(value: str) -> bool:
  383. return value[:1] in "/~" or (len(value) > 1 and value[1] == ":")
  384. def _is_url(value: str) -> bool:
  385. return "://" in value
  386. def _blank_or(rule: Callable[[str], bool]) -> Callable[[str], bool]:
  387. return lambda value: value == "" or rule(value)
  388. # Cheap per-column shape rules for text cells, by table (see module comment above).
  389. _TEXT_SHAPE_RULES: dict[str, dict[str, Callable[[str], bool]]] = {
  390. "sessions": {
  391. "session_key": lambda value: bool(_SESSION_KEY_PATTERN.match(value)),
  392. **dict.fromkeys(("chat_type", "end_reason", "cost_status", "cost_source", "billing_mode",
  393. "last_activity_provenance", "handoff_platform"), _is_token),
  394. "pricing_version": lambda value: bool(re.fullmatch(r"[a-z0-9][a-z0-9._-]*", value)),
  395. "title_source": lambda value: value in _TITLE_SOURCES,
  396. "handoff_state": lambda value: value in _HANDOFF_STATES,
  397. "parent_session_id": _is_session_id,
  398. "system_prompt_hash": lambda value: bool(re.fullmatch(r"[0-9a-f]{64}", value)),
  399. "model_config": _is_json_start, "origin_json": _is_json_start,
  400. "cwd": _is_path, "git_repo_root": _is_path,
  401. "billing_base_url": _is_url,
  402. },
  403. "messages": {
  404. **dict.fromkeys(("effect_disposition", "finish_reason", "display_kind"), _is_token),
  405. **dict.fromkeys(("tool_calls", "reasoning_details", "codex_reasoning_items", "codex_message_items",
  406. "api_content", "display_metadata"), _blank_or(_is_json_start)),
  407. },
  408. "session_model_usage": {
  409. "billing_base_url": _blank_or(_is_url),
  410. **dict.fromkeys(("billing_mode", "cost_status", "cost_source"), _blank_or(_is_token)),
  411. },
  412. }
  413. def _text_shape_holds(kind: str, name: str, value: str) -> bool:
  414. rule = _TEXT_SHAPE_RULES.get(kind, {}).get(name)
  415. return rule(value) if rule else True
  416. def _cell_fits(kind: str, name: str, value: Any, dest_types: dict[str, str]) -> bool:
  417. if value is None:
  418. return True
  419. if name in _LAYOUT_SENTINELS[kind] and not _sentinel_holds(name, value):
  420. return False
  421. declared = dest_types.get(name)
  422. # Columns since removed from the schema have no destination type; any
  423. # value is admissible there (and is dropped on insert).
  424. if declared is None:
  425. return True
  426. if _type_conflicts(value, declared):
  427. return False
  428. return not isinstance(value, str) or _text_shape_holds(kind, name, value)
  429. def _row_invariants_hold(kind: str, layout: tuple[str, ...], rows: Sequence[tuple[Any, ...]]) -> bool:
  430. """Cross-column invariants every writer honours, checked per record.
  431. ``handoff_error`` is only ever written together with ``handoff_state``
  432. (``mark_handoff_failed``); a record with a value at the position a
  433. candidate calls ``handoff_error`` but NULL where it calls
  434. ``handoff_state`` cannot have come from that layout. This is what
  435. separates ``cwd`` from ``handoff_error`` when a store's rows never
  436. handed off — the two are otherwise both free-form TEXT.
  437. """
  438. if kind != "sessions":
  439. return True
  440. positions = {name: index for index, name in enumerate(layout)}
  441. error_at = positions.get("handoff_error")
  442. state_at = positions.get("handoff_state")
  443. if error_at is None or state_at is None:
  444. return True
  445. # rows are exactly len(layout) wide (bucketed by width), so both positions are in range.
  446. return all(row[error_at] is None or row[state_at] is not None for row in rows)
  447. _SAMPLE_CAP = 512
  448. class LayoutEvidence:
  449. """What layout inference needs from a population, gathered in one streaming pass.
  450. Distinct non-NULL values per position (a column's admissibility is a property of the value, not the
  451. row, and salvaged populations repeat values heavily — a few hundred distinct values per position
  452. discriminate as well as 150k) plus, for ``sessions`` only, the rows themselves for the cross-column
  453. invariant. Keeping the whole population — every ``messages.content`` included — until pass 2 would
  454. hold the entire corrupted store in memory.
  455. """
  456. def __init__(self, kind: str) -> None:
  457. self.kind = kind
  458. self.widths: set[int] = set()
  459. self.by_position: list[set[Any]] = []
  460. self.rows_by_width: dict[int, list[tuple[Any, ...]]] = {}
  461. def add(self, cells: tuple[Any, ...]) -> None:
  462. self.widths.add(len(cells))
  463. while len(self.by_position) < len(cells):
  464. self.by_position.append(set())
  465. for index, value in enumerate(cells):
  466. if value is not None and len(self.by_position[index]) < _SAMPLE_CAP:
  467. self.by_position[index].add(value)
  468. if self.kind == "sessions":
  469. self.rows_by_width.setdefault(len(cells), []).append(cells)
  470. def infer_physical_layouts(evidence: LayoutEvidence, dest_types: dict[str, str]) -> dict[int, list[Optional[str]]]:
  471. """Infer which source column each record position holds, per field count.
  472. Salvaged records carry no schema. Every layout a real store can have is,
  473. however, a known chain over the shipped schema history (see
  474. ``session_schema_history``): the declared order at creation plus every
  475. later column appended at upgrade time. This walks that graph, pruning
  476. every branch whose column names contradict the salvaged cells (sentinel
  477. columns must look like what they name; every cell must fit its column's
  478. type affinity), and keeps the layouts that fit ALL records at once — the
  479. whole population of a table was written by one store, so one physical
  480. order produced every record of a given width.
  481. Returns, for each distinct record width, the column name each position
  482. resolves to. A position is ``None`` when the surviving layouts disagree
  483. on it (the cells there did not discriminate — typically all NULL/zero
  484. counters) so the caller can leave that column alone rather than guess.
  485. Returns an empty dict when no known layout fits the records.
  486. """
  487. kind = evidence.kind
  488. if kind not in SCHEMA_HISTORY or not evidence.widths:
  489. return {}
  490. by_position = evidence.by_position
  491. verdicts: dict[tuple[str, Any], bool] = {}
  492. def fits(name: str, value: Any) -> bool:
  493. key = (name, value)
  494. verdict = verdicts.get(key)
  495. if verdict is None:
  496. verdict = verdicts[key] = _cell_fits(kind, name, value, dest_types)
  497. return verdict
  498. # The invariant verdict depends only on where a candidate puts the two handoff columns, and most
  499. # candidates of one width agree on that — memoise so the rows are not rescanned per candidate.
  500. invariant_verdicts: dict[tuple[int, Optional[int], Optional[int]], bool] = {}
  501. def invariants_hold(layout: tuple[str, ...]) -> bool:
  502. same_width = evidence.rows_by_width.get(len(layout))
  503. if not same_width:
  504. return True
  505. positions = {name: index for index, name in enumerate(layout)}
  506. key = (len(layout), positions.get("handoff_error"), positions.get("handoff_state"))
  507. verdict = invariant_verdicts.get(key)
  508. if verdict is None:
  509. verdict = invariant_verdicts[key] = _row_invariants_hold(kind, layout, same_width)
  510. return verdict
  511. def accept(layout: tuple[str, ...], first_new: int) -> bool:
  512. for index in range(first_new, min(len(layout), len(by_position))):
  513. name = layout[index]
  514. for value in by_position[index]:
  515. if not fits(name, value):
  516. return False
  517. return invariants_hold(layout)
  518. # Enumerate once and bucket by width. A record of width ``k`` was written
  519. # while the table had exactly ``k`` columns (ADD COLUMN runs at startup,
  520. # before any row is written), so its layout is a chain state of exactly
  521. # that length.
  522. survivors_by_width: dict[int, list[tuple[str, ...]]] = {w: [] for w in sorted(evidence.widths)}
  523. for layout in reachable_physical_layouts(kind, accept):
  524. bucket = survivors_by_width.get(len(layout))
  525. if bucket is not None and layout not in bucket:
  526. bucket.append(layout)
  527. result: dict[int, list[Optional[str]]] = {}
  528. for width, survivors in survivors_by_width.items():
  529. if not survivors:
  530. continue
  531. # Per-position consensus. Where the survivors disagree the cells did
  532. # not discriminate (they fit every candidate column), so no name is
  533. # evidence — the destination's own declared order included. Leave
  534. # such positions alone rather than guess; the cells there are, by
  535. # construction, values several columns could legitimately hold.
  536. consensus: list[Optional[str]] = []
  537. for index in range(width):
  538. names = {layout[index] for layout in survivors}
  539. consensus.append(names.pop() if len(names) == 1 else None)
  540. result[width] = consensus
  541. return result
  542. def _insert_named_row(
  543. dest: sqlite3.Connection,
  544. table: str,
  545. layout: Sequence[Optional[str]],
  546. cells: tuple[Any, ...],
  547. dest_columns: list[str],
  548. notnull_substitutes: dict[int, Any],
  549. overrides: Optional[dict[str, Any]] = None,
  550. ) -> bool:
  551. """INSERT salvaged cells by source column name, never by position.
  552. ``layout[i]`` names the source column of ``cells[i]``; ``None`` positions
  553. (ambiguous or since-removed columns) are skipped and take the destination
  554. default. ``notnull_substitutes`` is index-keyed on ``dest_columns`` as
  555. returned by ``_notnull_defaults``.
  556. """
  557. dest_index = {name: index for index, name in enumerate(dest_columns)}
  558. mapped: dict[str, Any] = {}
  559. for name, value in zip(layout, cells):
  560. index = dest_index.get(name) if name is not None else None
  561. if index is None:
  562. continue
  563. if value is None and index in notnull_substitutes:
  564. value = notnull_substitutes[index]
  565. mapped[name] = value
  566. # NOT NULL columns the layout could not place (ambiguous or absent) take
  567. # the same substitute the positional path uses, so the row still lands;
  568. # the recovery verifier's plausibility gate audits the result.
  569. for index, substitute in notnull_substitutes.items():
  570. mapped.setdefault(dest_columns[index], substitute)
  571. mapped.update(overrides or {})
  572. return _execute_insert(dest, table, list(mapped), list(mapped.values()))
  573. def _execute_insert(
  574. dest: sqlite3.Connection, table: str, columns: list[str], values: list[Any]
  575. ) -> bool:
  576. quoted, placeholders = _quoted_columns(columns)
  577. cursor = dest.execute(f'INSERT OR IGNORE INTO "{table}" ({quoted}) VALUES ({placeholders})', values)
  578. return cursor.rowcount == 1
  579. def _copy_direct_tables(lf_conn: sqlite3.Connection, dest: sqlite3.Connection) -> dict[str, int]:
  580. """Copy rows .recover managed to attribute to real canonical tables."""
  581. copied: dict[str, int] = {}
  582. for table in (*_CANONICAL_TABLES, *_AUXILIARY_TABLES):
  583. source_columns = _table_columns(lf_conn, table)
  584. if not source_columns:
  585. continue
  586. dest_columns = _table_columns(dest, table)
  587. if not dest_columns and table in _AUXILIARY_TABLE_SCHEMAS: # lazily-created gateway table
  588. _AUXILIARY_TABLE_SCHEMAS[table](dest)
  589. dest_columns = _table_columns(dest, table)
  590. columns = [c for c in dest_columns if c in source_columns]
  591. if not columns:
  592. continue
  593. quoted, placeholders = _quoted_columns(columns)
  594. rows = lf_conn.execute(f'SELECT {quoted} FROM "{table}"').fetchall()
  595. if not rows:
  596. copied[table] = 0
  597. continue
  598. before = _count_rows(dest, table)
  599. dest.executemany(f'INSERT OR IGNORE INTO "{table}" ({quoted}) VALUES ({placeholders})', rows)
  600. copied[table] = _count_rows(dest, table) - before
  601. return copied
  602. def map_lost_and_found_rows(lf_conn: sqlite3.Connection, dest: sqlite3.Connection) -> dict[str, Any]:
  603. """Best-effort mapping of a .recover output DB into a fresh SessionDB."""
  604. report: dict[str, Any] = {
  605. "direct_table_rows": {}, "mapped": {"sessions": 0, "messages": 0, "session_model_usage": 0},
  606. "legacy_minimal_sessions": 0, "mapped_by_layout": 0, "unrecognized_layout_rows": 0,
  607. "unrecognized_layout_widths": {}, "inferred_layouts": {},
  608. "unmapped_rows": 0, "insert_conflicts": 0, "lost_and_found_tables": [],
  609. }
  610. with _immediate_transaction(dest):
  611. report["direct_table_rows"] = _copy_direct_tables(lf_conn, dest)
  612. # Per-kind destination columns + NOT NULL substitutes. Identity fields are never fabricated:
  613. # rows with a NULL session id / role / source were already rejected by classify_lost_and_found_row.
  614. targets: dict[str, tuple[list[str], dict[int, Any]]] = {}
  615. for kind_name, protected in (
  616. ("sessions", ("id", "source")), ("messages", ("session_id", "role")),
  617. ("session_model_usage", ("session_id", "model")),
  618. ):
  619. columns = _table_columns(dest, kind_name)
  620. defaults = _notnull_defaults(dest, kind_name)
  621. for name in protected:
  622. defaults.pop(columns.index(name), None)
  623. targets[kind_name] = (columns, defaults)
  624. dest_types = {table: _declared_types(dest, table) for table in targets}
  625. lf_tables = [
  626. str(row[0]) for row in
  627. lf_conn.execute("SELECT name FROM sqlite_master WHERE type='table' AND name LIKE 'lost_and_found%'")
  628. ]
  629. report["lost_and_found_tables"] = lf_tables
  630. def records():
  631. """Yield (kind, lf_rowid, nfield, cells) for every classifiable lost_and_found row."""
  632. for lf_table in lf_tables:
  633. if _table_columns(lf_conn, lf_table)[:3] != ["rootpgno", "pgno", "nfield"]:
  634. continue
  635. for row in lf_conn.execute(f'SELECT * FROM "{lf_table}"'):
  636. try:
  637. nfield = int(row[2]) if row[2] is not None else 0
  638. except (TypeError, ValueError):
  639. yield None, None, 0, ()
  640. continue
  641. cells = tuple(row[4 : 4 + max(nfield, 0)])
  642. yield classify_lost_and_found_row(nfield, cells), row[3], nfield, cells
  643. # Pass 1: stream the population once, keeping only what layout inference needs. The physical
  644. # layout is a property of the whole population (one store wrote all of them), so it is inferred
  645. # once per kind, not per row.
  646. evidence = {kind: LayoutEvidence(kind) for kind in targets}
  647. for kind, _, _, cells in records():
  648. if kind is None:
  649. report["unmapped_rows"] += 1
  650. else:
  651. evidence[kind].add(cells)
  652. layouts = {kind: infer_physical_layouts(evidence[kind], dest_types[kind]) for kind in targets}
  653. # Per kind and record width, the column each position resolved to (None where the surviving
  654. # layouts disagreed and the cell was left to the destination default).
  655. report["inferred_layouts"] = {
  656. kind: {str(width): list(layout) for width, layout in by_width.items()}
  657. for kind, by_width in layouts.items() if by_width
  658. }
  659. # Pass 2: insert. Records whose width resolved to a layout are mapped by column name (#101409);
  660. # the rest take the historical positional prefix, audited by the recovery verifier's plausibility gate.
  661. for kind, lf_rowid, nfield, cells in records():
  662. if kind is None:
  663. continue # counted in pass 1
  664. columns, defaults = targets[kind]
  665. layout = layouts[kind].get(len(cells))
  666. legacy_minimal = kind == "sessions" and nfield == SESSIONS_LEGACY_MINIMAL_NFIELD
  667. if layout is None and not legacy_minimal:
  668. report["unrecognized_layout_rows"] += 1
  669. widths = report["unrecognized_layout_widths"].setdefault(kind, [])
  670. if len(cells) not in widths:
  671. widths.append(len(cells))
  672. try:
  673. if layout is not None:
  674. # messages.id is a rowid alias: NULL in the record, carried by the lost_and_found row id.
  675. inserted = _insert_named_row(
  676. dest, kind, layout, cells, columns, defaults,
  677. {"id": lf_rowid} if kind == "messages" else None,
  678. )
  679. report["mapped_by_layout"] += int(inserted)
  680. elif legacy_minimal:
  681. # A 14-field record matching no known layout (torn cells, or a pre-history store):
  682. # salvage identity + timing rather than guessing 14 positional meanings.
  683. row_values = (
  684. cells[0], cells[1] if _looks_like_source(cells[1]) else "recovered",
  685. _heuristic_started_at(cells),
  686. f"{STUB_TITLE_PREFIX}] legacy session row (layout unknown)",
  687. )
  688. inserted = dest.execute(
  689. "INSERT OR IGNORE INTO sessions (id, source, started_at, title) VALUES (?, ?, ?, ?)",
  690. row_values,
  691. ).rowcount == 1
  692. report["legacy_minimal_sessions"] += int(inserted)
  693. else:
  694. values = list(cells[:len(columns)])
  695. if kind == "messages":
  696. values[0] = lf_rowid
  697. elif kind == "sessions":
  698. columns, defaults = _sessions_prefix_columns(columns, defaults, cells)
  699. inserted = _insert_prefix_row(dest, kind, columns, values, defaults)
  700. except sqlite3.DatabaseError:
  701. report["unmapped_rows"] += 1
  702. continue
  703. if inserted:
  704. report["mapped"][kind] += 1
  705. else:
  706. report["insert_conflicts"] += 1
  707. return report
  708. def stub_missing_parent_sessions(dest: sqlite3.Connection) -> dict[str, Any]:
  709. """Fabricate clearly-marked placeholder parents for salvaged child rows: children (messages,
  710. model-usage rows) are NEVER deleted for FK cleanup — a stub parent beats losing the only copy."""
  711. result: dict[str, Any] = {"sessions_stubbed": 0, "messages_retained": 0, "usage_rows_retained": 0}
  712. with _immediate_transaction(dest):
  713. orphan_ids: dict[str, dict[str, Any]] = {}
  714. for session_id, first_ts, count in dest.execute(
  715. "SELECT m.session_id, MIN(m.timestamp), COUNT(*) FROM messages AS m WHERE m.session_id IS NOT NULL AND "
  716. "NOT EXISTS (SELECT 1 FROM sessions WHERE sessions.id = m.session_id) GROUP BY m.session_id"
  717. ):
  718. orphan_ids[str(session_id)] = {
  719. "started_at": float(first_ts) if first_ts is not None else 0.0,
  720. "message_count": int(count),
  721. }
  722. for (session_id,) in dest.execute(
  723. "SELECT DISTINCT u.session_id FROM session_model_usage AS u WHERE u.session_id IS NOT NULL AND NOT "
  724. "EXISTS (SELECT 1 FROM sessions WHERE sessions.id = u.session_id)"
  725. ):
  726. orphan_ids.setdefault(str(session_id), {"started_at": 0.0, "message_count": 0})
  727. titles = _placeholder_titles(dest, _STUB_TITLE_LABEL)
  728. for session_id, info in sorted(orphan_ids.items()):
  729. title = next(titles)
  730. dest.execute(
  731. "INSERT INTO sessions (id, source, started_at, title, message_count) "
  732. "VALUES (?, 'recovered', ?, ?, ?)",
  733. (session_id, info["started_at"], title, info["message_count"]),
  734. )
  735. result["sessions_stubbed"] += 1
  736. result["messages_retained"] += info["message_count"]
  737. result["usage_rows_retained"] = int(dest.execute("SELECT COUNT(*) FROM session_model_usage").fetchone()[0])
  738. # Repair dangling intra-sessions references without deleting rows.
  739. dest.execute(
  740. "UPDATE sessions SET parent_session_id = NULL WHERE parent_session_id IS NOT NULL AND NOT EXISTS (SELECT "
  741. "1 FROM sessions AS p WHERE p.id = sessions.parent_session_id)"
  742. )
  743. dest.execute(
  744. "UPDATE sessions SET system_prompt_hash = NULL WHERE system_prompt_hash IS NOT NULL AND NOT EXISTS "
  745. "(SELECT 1 FROM system_prompts WHERE system_prompts.hash = sessions.system_prompt_hash)"
  746. )
  747. dest.execute(f"UPDATE sessions SET tool_names = NULL WHERE {_DANGLING_TOOL_PIN}")
  748. return result
  749. def rebuild_fts_indexes(dest: sqlite3.Connection) -> dict[str, str]:
  750. """Rebuild derived FTS indexes from the salvaged canonical rows."""
  751. results: dict[str, str] = {}
  752. for table in ("messages_fts", "messages_fts_trigram", "messages_fts_cjk"):
  753. if not _table_columns(dest, table):
  754. continue
  755. try:
  756. dest.execute(f'INSERT INTO "{table}" ("{table}") VALUES (\'rebuild\')')
  757. results[table] = "rebuilt"
  758. except sqlite3.DatabaseError as exc:
  759. results[table] = f"rebuild failed: {exc}"
  760. return results