kanban_db_connect.py 57 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616617618619620621622623624625626627628629630631632633634635636637638639640641642643644645646647648649650651652653654655656657658659660661662663664665666667668669670671672673674675676677678679680681682683684685686687688689690691692693694695696697698699700701702703704705706707708709710711712713714715716717718719720721722723724725726727728729730731732733734735736737738739740741742743744745746747748749750751752753754755756757758759760761762763764765766767768769770771772773774775776777778779780781782783784785786787788789790791792793794795796797798799800801802803804805806807808809810811812813814815816817818819820821822823824825826827828829830831832833834835836837838839840841842843844845846847848849850851852853854855856857858859860861862863864865866867868869870871872873874875876877878879880881882883884885886887888889890891892893894895896897898899900901902903904905906907908909910911912913914915916917918919920921922923924925926927928929930931932933934935936937938939940941942943944945946947948949950951952953954955956957958959960961962963964965966967968969970971972973974975976977978979980981982983984985986987988989990991992993994995996997998999100010011002100310041005100610071008100910101011101210131014101510161017101810191020102110221023102410251026102710281029103010311032103310341035103610371038103910401041104210431044104510461047104810491050105110521053105410551056105710581059106010611062106310641065106610671068106910701071107210731074107510761077107810791080108110821083108410851086108710881089109010911092109310941095109610971098109911001101110211031104110511061107110811091110111111121113111411151116111711181119112011211122112311241125112611271128112911301131113211331134113511361137113811391140114111421143114411451146114711481149115011511152115311541155115611571158115911601161116211631164116511661167116811691170117111721173117411751176117711781179118011811182118311841185118611871188118911901191119211931194119511961197119811991200120112021203120412051206120712081209121012111212121312141215121612171218121912201221122212231224122512261227122812291230123112321233123412351236123712381239124012411242124312441245124612471248124912501251125212531254125512561257125812591260126112621263126412651266126712681269127012711272127312741275127612771278127912801281128212831284128512861287128812891290129112921293129412951296
  1. """SQLite connection lifecycle for the Kanban DB: open/configure, cross-process init and dispatch-tick locks, WAL checkpoints, corruption detection + quarantine + repair, additive migrations and the busy-retrying ``write_txn`` boundary.
  2. Split out of ``hermes_cli.kanban_db``; origin-resident helpers are reached
  3. late-bound via ``_kb`` (import-cycle breaking) so monkeypatching
  4. ``kanban_db.<name>`` keeps working.
  5. """
  6. from __future__ import annotations
  7. import contextlib
  8. import hashlib
  9. import random
  10. import re
  11. import secrets
  12. import shutil
  13. import sqlite3
  14. import threading
  15. import time
  16. from dataclasses import dataclass
  17. from dataclasses import field
  18. from agent.deadline import poll_until
  19. from hermes_cli.sqlite_util import add_column_if_missing as _add_column_if_missing
  20. from pathlib import Path
  21. from typing import Any
  22. from typing import Optional
  23. # ---------------------------------------------------------------------------
  24. # Connection helpers
  25. # ---------------------------------------------------------------------------
  26. _INITIALIZED_PATHS: set[str] = set()
  27. _INIT_LOCK = threading.RLock()
  28. _SQLITE_HEADER = b"SQLite format 3\x00"
  29. DEFAULT_BUSY_TIMEOUT_MS = 120_000
  30. # Cap on ``<db>.corrupt.<hash>.bak`` quarantines per board: content-addressing
  31. # dedupes identical bytes, but mutating corruption mints a new fingerprint each
  32. # time (one user hit 124). Oldest-by-mtime beyond the cap are pruned after each
  33. # new backup.
  34. _CORRUPT_BACKUP_RETENTION = 10
  35. # Bounded init-lock acquire: a bare blocking flock let a wedged holder block the
  36. # dispatcher's next-tick connect forever. Poll non-blocking until the deadline,
  37. # then proceed without the lock (in-process _INIT_LOCK + idempotent init backstop).
  38. _INIT_LOCK_TIMEOUT_SECONDS = 10.0
  39. _INIT_LOCK_POLL_SECONDS = 0.05
  40. def _resolve_busy_timeout_ms() -> int:
  41. """Return the SQLite busy timeout for Kanban connections. Kanban is the
  42. shared cross-profile dispatch bus, so worker stampedes are expected; a
  43. long timeout lets WAL serialize writers instead of surfacing transient
  44. ``database is locked`` failures."""
  45. return _kb._env_int("HERMES_KANBAN_BUSY_TIMEOUT_MS", DEFAULT_BUSY_TIMEOUT_MS, minimum=1)
  46. def _sqlite_connect(path: Path) -> sqlite3.Connection:
  47. """Open a Kanban SQLite connection via ``connect_tracked``: while registered,
  48. byte-level probes of the file are refused because an ``open()``/``close()``
  49. would cancel this process's POSIX advisory locks (see ``sqlite_safe_read``)."""
  50. from hermes_cli.sqlite_safe_read import connect_tracked
  51. busy_timeout_ms = _resolve_busy_timeout_ms()
  52. conn = connect_tracked(
  53. path,
  54. connect_fn=sqlite3.connect,
  55. isolation_level=None,
  56. timeout=busy_timeout_ms / 1000.0,
  57. )
  58. try:
  59. # Explicit PRAGMA (besides connect(timeout=)) so it is observable and
  60. # survives wrapper changes; PRAGMA assignments can't bind parameters.
  61. conn.execute(f"PRAGMA busy_timeout={busy_timeout_ms}")
  62. except BaseException:
  63. # A half-open connection would leak its fd AND leave a stale entry in the
  64. # connect_tracked registry (cleared only on close), permanently blocking
  65. # byte-level probes of this file.
  66. with contextlib.suppress(Exception):
  67. conn.close()
  68. raise
  69. return conn
  70. def _try_lock_nb(handle) -> bool:
  71. """One non-blocking exclusive lock attempt on ``handle``; False when held elsewhere.
  72. Windows: 1-byte ``msvcrt.locking`` range at offset 0; POSIX: ``flock``."""
  73. if _kb._IS_WINDOWS:
  74. import msvcrt
  75. handle.seek(0)
  76. msvcrt.locking(handle.fileno(), msvcrt.LK_NBLCK, 1)
  77. else:
  78. import fcntl
  79. try:
  80. fcntl.flock(handle.fileno(), fcntl.LOCK_EX | fcntl.LOCK_NB)
  81. except BlockingIOError:
  82. return False
  83. return True
  84. def _unlock(handle) -> None:
  85. """Release a lock taken by :func:`_try_lock_nb` (same byte range / flock)."""
  86. if _kb._IS_WINDOWS:
  87. import msvcrt
  88. handle.seek(0)
  89. msvcrt.locking(handle.fileno(), msvcrt.LK_UNLCK, 1)
  90. else:
  91. import fcntl
  92. fcntl.flock(handle.fileno(), fcntl.LOCK_UN)
  93. @contextlib.contextmanager
  94. def _cross_process_init_lock(path: Path):
  95. """Serialize first-connect WAL/schema/integrity setup across processes.
  96. ``_INIT_LOCK`` only covers one process's threads; a dispatcher burst has
  97. many worker processes hit a fresh/legacy board with empty
  98. ``_INITIALIZED_PATHS`` caches. Post-init usage stays concurrent under WAL.
  99. **Bounded** acquire: a blocking ``flock`` let one stalled/stale holder hang
  100. every ``connect()`` (the gateway dispatcher's next tick included) with no
  101. traceback. After the deadline we WARN and proceed WITHOUT the lock — safe
  102. because ``_INIT_LOCK`` still serializes same-process threads and init is
  103. idempotent: two racing first-inits mean redundant work, not corruption.
  104. """
  105. path.parent.mkdir(parents=True, exist_ok=True)
  106. lock_path = path.with_name(path.name + ".init.lock")
  107. handle = lock_path.open("a+b")
  108. acquired = False
  109. try:
  110. def _try_lock() -> bool:
  111. try:
  112. return _try_lock_nb(handle)
  113. except OSError:
  114. return False
  115. acquired = poll_until(_try_lock, _INIT_LOCK_TIMEOUT_SECONDS, _INIT_LOCK_POLL_SECONDS)
  116. if not acquired:
  117. _kb._log.warning(
  118. "kanban init lock for %s not acquired within %.0fs — proceeding "
  119. "without the cross-process lock (in-process lock + idempotent "
  120. "init are the correctness backstop). A stuck holder is no longer "
  121. "able to block this connect indefinitely (#36644).",
  122. lock_path, _INIT_LOCK_TIMEOUT_SECONDS,
  123. )
  124. yield
  125. finally:
  126. try:
  127. if acquired:
  128. _unlock(handle)
  129. finally:
  130. handle.close()
  131. @contextlib.contextmanager
  132. def _dispatch_tick_lock(db_path: Path):
  133. """Non-blocking single-writer guard around one dispatcher tick; yields
  134. ``True`` if this process holds the board's ``.dispatch.lock``, else
  135. ``False`` (caller skips the tick).
  136. Two dispatchers (e.g. an orphan gateway escaping its service cgroup) both
  137. pass ``busy_timeout`` and race on WAL frames — the root cause of
  138. multi-writer corruption; this is defense-in-depth behind
  139. ``_guard_supervised_gateway_conflict``. Non-blocking on purpose: the
  140. gateway's async watcher must never stall; the loser retries next interval.
  141. Without ``fcntl``/``msvcrt`` it degrades to a no-op (yields ``True``).
  142. Motivation (issue #35240): a ``hermes gateway run --replace`` / ``gateway restart`` invoked from a shell
  143. on a systemd/launchd host can leave an orphan gateway whose dispatcher escapes the service cgroup,
  144. survives ``systemctl restart``, and becomes a *second* long-lived writer on the same ``kanban.db``. The
  145. startup guard (``_guard_supervised_gateway_conflict``) blocks the common way an orphan is born, but this
  146. lock is the defense-in-depth that prevents two dispatchers from ever writing concurrently *regardless of
  147. how the second one got there*.
  148. """
  149. lock_path = db_path.with_name(db_path.name + ".dispatch.lock")
  150. handle = None
  151. acquired = False
  152. try:
  153. lock_path.parent.mkdir(parents=True, exist_ok=True)
  154. handle = lock_path.open("a+b")
  155. try:
  156. acquired = _try_lock_nb(handle)
  157. except (OSError, AttributeError):
  158. acquired = False
  159. except OSError:
  160. # Can't even open the lock file (permissions, read-only FS): degrade to
  161. # a no-op so a probe failure never blocks dispatch.
  162. acquired = True
  163. handle = None
  164. try:
  165. yield acquired
  166. finally:
  167. if handle is not None:
  168. try:
  169. if acquired:
  170. _unlock(handle)
  171. except (OSError, AttributeError):
  172. pass
  173. finally:
  174. handle.close()
  175. # Periodic explicit WAL checkpoint from the dispatcher tick: a passive
  176. # autocheckpoint can be starved on a busy multi-process board (any open reader
  177. # snapshot blocks the WAL reset), letting -wal grow between gateway restarts.
  178. # PASSIVE, not TRUNCATE (same fix class as state.db): the dispatch flock only
  179. # makes this the sole *dispatcher* — CLI kanban commands in other processes
  180. # write to the same board without it, so a TRUNCATE would race live writers.
  181. # PASSIVE never takes the exclusive checkpoint lock; WAL size is bounded by
  182. # ``journal_size_limit`` (set at connection init) on the writer's natural
  183. # post-checkpoint reset. Best-effort, keyed per resolved DB path.
  184. # Once per coarse interval the dispatcher issues an explicit ``wal_checkpoint(PASSIVE)``. Best-effort: a
  185. # busy/locked checkpoint is logged at DEBUG and retried next interval. See #44795, #45383, #80255.
  186. _WAL_CHECKPOINT_INTERVAL_SECONDS = 300.0
  187. _LAST_WAL_CHECKPOINT: dict[str, float] = {}
  188. _WAL_CHECKPOINT_LOCK = threading.Lock()
  189. def _maybe_checkpoint_wal(conn: sqlite3.Connection, db_path: Path) -> None:
  190. """``PRAGMA wal_checkpoint(PASSIVE)`` at most once per interval per board,
  191. from the dispatcher tick under the dispatch lock. Never raises: pure
  192. hygiene, must not fail a tick."""
  193. try:
  194. key = str(db_path.resolve())
  195. except OSError:
  196. key = str(db_path)
  197. now = time.monotonic()
  198. with _WAL_CHECKPOINT_LOCK:
  199. last = _LAST_WAL_CHECKPOINT.get(key)
  200. if last is not None and (now - last) < _WAL_CHECKPOINT_INTERVAL_SECONDS:
  201. return
  202. # Claim the slot first so concurrent same-process ticks don't
  203. # double-checkpoint on the boundary.
  204. _LAST_WAL_CHECKPOINT[key] = now
  205. try:
  206. row = conn.execute("PRAGMA wal_checkpoint(PASSIVE)").fetchone()
  207. _kb._log.debug(
  208. "kanban WAL checkpoint (PASSIVE) on %s -> %s "
  209. "(busy, wal_frames, checkpointed_frames)",
  210. key, tuple(row) if row is not None else None,
  211. )
  212. except sqlite3.Error as exc:
  213. _kb._log.debug("kanban WAL checkpoint on %s skipped: %s", key, exc)
  214. def _looks_like_tls_record_at(data: bytes, offset: int) -> bool:
  215. """Return True for a TLS record header at ``data[offset:]``."""
  216. if len(data) < offset + 5:
  217. return False
  218. content_type, major, minor = data[offset], data[offset + 1], data[offset + 2]
  219. length = int.from_bytes(data[offset + 3:offset + 5], "big")
  220. return (
  221. content_type in {0x14, 0x15, 0x16, 0x17}
  222. and major == 0x03
  223. and minor in {0x00, 0x01, 0x02, 0x03, 0x04}
  224. and 0 < length <= 18432
  225. )
  226. def _validate_sqlite_header(path: Path) -> None:
  227. """Fail early with an actionable error for non-SQLite Kanban DB files.
  228. ``sqlite3.connect()`` creates missing and zero-byte files, so those pass;
  229. non-empty files must carry the SQLite header, so a corrupt page 0 isn't
  230. collapsed into a generic PRAGMA error and the gateway's corrupt-board
  231. handling can identify the board by fingerprint."""
  232. try:
  233. if path.stat().st_size == 0:
  234. return
  235. except OSError:
  236. return
  237. # Byte-level probe: must run BEFORE any connection to this path exists
  238. # (read_header_bytes_preopen refuses once one is live, because the close()
  239. # would cancel this process's POSIX locks).
  240. from hermes_cli.sqlite_safe_read import read_header_bytes_preopen
  241. head = read_header_bytes_preopen(path, length=64)
  242. if head is None or head.startswith(_SQLITE_HEADER):
  243. return
  244. signature = ""
  245. if head.startswith(b"SQLit") and _looks_like_tls_record_at(head, 5):
  246. signature = " (TLS record header detected at byte offset 5)"
  247. elif _looks_like_tls_record_at(head, 0):
  248. signature = " (TLS record header detected at byte offset 0)"
  249. raise sqlite3.DatabaseError(
  250. "file is not a database: invalid SQLite header for "
  251. f"{path}{signature}; first_32={head[:32].hex(' ')}"
  252. )
  253. class KanbanDbCorruptError(RuntimeError):
  254. """Raised when an existing kanban DB file fails integrity checks — a
  255. fail-closed guard against silently recreating a corrupt board (which would
  256. destroy the user's tasks). Carries the path and the backup made first."""
  257. def __init__(self, db_path: Path, backup_path: Optional[Path], reason: str):
  258. self.db_path = db_path
  259. self.backup_path = backup_path
  260. self.reason = reason
  261. super().__init__(
  262. f"Refusing to open corrupt kanban DB at {db_path}: {reason}. "
  263. f"Original preserved; backup at {_backup_label(backup_path)}."
  264. )
  265. def _backup_label(backup_path: Optional[Path]) -> str:
  266. return str(backup_path) if backup_path is not None else "<backup failed>"
  267. def _prune_corrupt_backups(parent: Path, base_name: str, keep: Optional[Path] = None) -> None:
  268. """Keep only the ``_CORRUPT_BACKUP_RETENTION`` newest (by mtime)
  269. ``<db>.corrupt.<hash>.bak`` files plus their ``-wal``/``-shm`` copies.
  270. ``keep`` (the just-created backup) is never pruned regardless of mtime —
  271. ``shutil.copy2`` preserves the source timestamp, which may be older than
  272. existing backups. Best-effort: prune failures never mask the corruption
  273. error the caller is about to raise."""
  274. try:
  275. backups = [
  276. candidate
  277. for candidate in parent.glob(f"{base_name}.corrupt.*.bak")
  278. if candidate.is_file() and candidate != keep
  279. ]
  280. except OSError:
  281. return
  282. budget = max(_CORRUPT_BACKUP_RETENTION - (1 if keep is not None else 0), 0)
  283. if len(backups) <= budget:
  284. return
  285. def _mtime(item: Path) -> float:
  286. try:
  287. return item.stat().st_mtime
  288. except OSError:
  289. return 0.0
  290. backups.sort(key=_mtime, reverse=True)
  291. for stale in backups[budget:]:
  292. for victim in (stale, stale.with_name(stale.name + "-wal"), stale.with_name(stale.name + "-shm")):
  293. with contextlib.suppress(OSError):
  294. victim.unlink(missing_ok=True)
  295. def _backup_corrupt_db(path: Path) -> Optional[Path]:
  296. """Copy a corrupt DB (and WAL/SHM sidecars) to a content-addressed backup.
  297. The name is deterministic in the main DB's sha256, so repeated quarantines
  298. of the same bytes reuse one backup while changed bytes get a separate one.
  299. Returns the main backup path, or ``None`` if the copy failed (the caller
  300. still raises loudly). Writes are confined to the DB's parent directory:
  301. the basename derives only from ``path.name`` + content hash."""
  302. # Pin the resolved parent (``resolve()`` collapses ``..`` and symlinks); we
  303. # only ever write inside it.
  304. resolved = path.resolve()
  305. parent = resolved.parent
  306. base_name = resolved.name # basename only
  307. # Fingerprinting reads the whole file — a close()-on-a-database-file hazard
  308. # (cancels this process's POSIX advisory locks; see sqlite_safe_read), so it
  309. # must only run once the board is out of service. Another SessionDB/kanban
  310. # connection in this process would still be at risk — so REFUSE rather than
  311. # warn-and-proceed: losing a forensic copy beats corrupting the live DB.
  312. from hermes_cli.sqlite_safe_read import has_live_connection
  313. if has_live_connection(resolved):
  314. _kb._log.error(
  315. "refusing to quarantine %s: a connection to it is still open in "
  316. "this process, and fingerprinting the file would cancel that "
  317. "connection's POSIX locks. Close all connections first.",
  318. resolved,
  319. )
  320. return None
  321. digest = hashlib.sha256()
  322. try:
  323. with resolved.open("rb") as handle:
  324. for chunk in iter(lambda: handle.read(1024 * 1024), b""):
  325. digest.update(chunk)
  326. except OSError:
  327. return None
  328. candidate = parent / f"{base_name}.corrupt.{digest.hexdigest()[:16]}.bak"
  329. # Defensive: candidate must still be inside parent after construction.
  330. if candidate.parent != parent:
  331. return None
  332. if not candidate.exists():
  333. try:
  334. shutil.copy2(resolved, candidate)
  335. except OSError:
  336. return None
  337. # A NEW backup landed — enforce the retention cap so mutating-corruption
  338. # loops can't accumulate quarantines forever.
  339. _prune_corrupt_backups(parent, base_name, keep=candidate)
  340. for suffix in ("-wal", "-shm"):
  341. sidecar = parent / (base_name + suffix)
  342. sidecar_backup = parent / (candidate.name + suffix)
  343. if (
  344. sidecar.parent != parent or not sidecar.exists()
  345. or sidecar_backup.parent != parent or sidecar_backup.exists()
  346. ):
  347. continue
  348. with contextlib.suppress(OSError):
  349. shutil.copy2(sidecar, sidecar_backup)
  350. return candidate
  351. # Repairable integrity_check error classes — both *index-scoped*: the table
  352. # b-tree is intact and REINDEX rebuilds the index losslessly. The index name is
  353. # parsed generically (no hardcoded list). Anything else (page corruption,
  354. # "malformed", freelist damage, …) keeps the fail-closed behavior.
  355. _REPAIRABLE_INDEX_ERROR_PATTERNS = (
  356. re.compile(r"^wrong # of entries in index (?P<index>.+)$"),
  357. re.compile(r"^row \d+ missing from index (?P<index>.+)$"),
  358. )
  359. def _integrity_messages_ok(messages: list[str]) -> bool:
  360. """True iff ``PRAGMA integrity_check`` output is the single ``ok`` row."""
  361. return len(messages) == 1 and messages[0].strip().lower() == "ok"
  362. def _run_integrity_check(conn: sqlite3.Connection) -> list[str]:
  363. """Return all ``PRAGMA integrity_check`` message rows as strings."""
  364. rows = conn.execute("PRAGMA integrity_check").fetchall()
  365. return [str(row[0]) for row in rows if row is not None and row[0] is not None]
  366. def _probe_integrity(path: Path) -> list[str]:
  367. """Open ``path`` read/write (so SQLite can recover/checkpoint a healthy WAL
  368. / hot-journal DB before we judge it) and return ``integrity_check``
  369. messages. ``OperationalError`` (locked/busy) propagates raw — not corruption."""
  370. probe = _sqlite_connect(path)
  371. try:
  372. return _run_integrity_check(probe)
  373. finally:
  374. probe.close()
  375. def _repairable_index_names(messages: list[str]) -> Optional[list[str]]:
  376. """Distinct index names iff EVERY message is index-repairable, else ``None``
  377. (caller fails closed; also ``None`` for no messages). First-appearance
  378. order is preserved so the REINDEX pass is deterministic."""
  379. names: list[str] = []
  380. for raw in messages:
  381. message = (raw or "").strip()
  382. if not message:
  383. continue
  384. for pattern in _REPAIRABLE_INDEX_ERROR_PATTERNS:
  385. match = pattern.match(message)
  386. if match:
  387. break
  388. else:
  389. return None
  390. name = match.group("index").strip()
  391. if name and name not in names:
  392. names.append(name)
  393. return names or None
  394. def _attempt_index_reindex_repair(path: Path, index_names: list[str]) -> tuple[bool, list[str]]:
  395. """REINDEX the named indexes (per-index first; bare ``REINDEX`` fallback if
  396. a parsed name is an internal/auto index), then re-run integrity_check.
  397. Returns ``(clean, post_repair_messages)``; never raises. Callers must hold
  398. the board's cross-process init flock so nothing connects mid-repair."""
  399. try:
  400. conn = _sqlite_connect(path)
  401. except sqlite3.Error as exc:
  402. return False, [f"could not reopen for REINDEX: {exc}"]
  403. try:
  404. try:
  405. for name in index_names:
  406. escaped = name.replace('"', '""')
  407. conn.execute(f'REINDEX "{escaped}"')
  408. except sqlite3.Error:
  409. # Per-index rebuild failed — bare REINDEX rebuilds every index.
  410. conn.execute("REINDEX")
  411. messages = _run_integrity_check(conn)
  412. except sqlite3.Error as exc:
  413. return False, [f"REINDEX failed: {exc}"]
  414. finally:
  415. conn.close()
  416. return _integrity_messages_ok(messages), messages
  417. def _missing_or_empty(resolved: Path) -> bool:
  418. """True for a missing / zero-byte / unstat-able DB file — nothing to probe."""
  419. try:
  420. return not resolved.exists() or resolved.stat().st_size == 0
  421. except OSError:
  422. return True
  423. def _probe_for_corruption(resolved: Path) -> tuple[Optional[list[str]], Optional[str]]:
  424. """``(messages, reason)`` from an integrity probe; ``reason`` is ``None``
  425. when healthy and ``messages`` is ``None`` when sqlite refused to open the
  426. file at all. ``OperationalError`` (lock/busy) is NOT corruption and
  427. propagates raw so a locked healthy DB is never quarantined."""
  428. try:
  429. messages = _probe_integrity(resolved)
  430. except sqlite3.OperationalError:
  431. raise
  432. except sqlite3.DatabaseError as exc:
  433. return None, f"sqlite refused to open file: {exc}"
  434. if _integrity_messages_ok(messages):
  435. return messages, None
  436. return messages, f"integrity_check returned {messages[0] if messages else '<no row>'!r}"
  437. def _guard_existing_db_is_healthy(path: Path) -> None:
  438. """Run ``PRAGMA integrity_check`` on an existing non-empty DB file.
  439. Narrow auto-repair when the failure is ONLY index-scoped (table b-trees
  440. intact): content-addressed corrupt backup FIRST, REINDEX under the
  441. caller-held init flock, re-check, proceed only if clean. Anything else
  442. (page corruption, ``malformed``, unclean re-check) fails closed: back up
  443. the file + sidecars and raise :class:`KanbanDbCorruptError` so callers
  444. never recreate the schema on top of a damaged DB. ``OperationalError``
  445. (lock/busy) is NOT corruption and propagates raw (no spurious backup).
  446. No-op for missing / zero-byte files and paths already proven healthy this
  447. process. All writes are confined to the resolved path's parent.
  448. """
  449. try:
  450. resolved = path.resolve()
  451. except OSError:
  452. return
  453. if _missing_or_empty(resolved) or str(resolved) in _INITIALIZED_PATHS:
  454. return
  455. messages, reason = _probe_for_corruption(resolved)
  456. if reason is None:
  457. return
  458. # Quarantine FIRST — both the repair and fail-closed paths preserve the
  459. # pre-touch bytes before anything mutates the file.
  460. backup = _backup_corrupt_db(resolved)
  461. index_names = _repairable_index_names(messages or [])
  462. if index_names:
  463. _kb._log.warning(
  464. "kanban DB %s failed integrity_check with index-only errors "
  465. "(%s); pre-repair backup at %s — attempting REINDEX auto-repair.",
  466. resolved, ", ".join(index_names), _backup_label(backup),
  467. )
  468. repaired, post = _attempt_index_reindex_repair(resolved, index_names)
  469. if repaired:
  470. _kb._log.warning(
  471. "kanban DB %s auto-repaired via REINDEX (%s); "
  472. "integrity_check now clean. Pre-repair copy kept at %s.",
  473. resolved, ", ".join(index_names), _backup_label(backup),
  474. )
  475. return
  476. reason = (
  477. f"{reason}; REINDEX auto-repair attempted but integrity_check "
  478. f"still returned {post[0] if post else '<no row>'!r}"
  479. )
  480. raise KanbanDbCorruptError(resolved, backup, reason)
  481. @dataclass
  482. class RepairResult:
  483. """Outcome of :func:`repair_db`. ``status``: ``"ok"`` (already clean),
  484. ``"repaired"`` (index-only errors, REINDEX applied, re-check clean;
  485. ``backup_path`` is the pre-repair copy), ``"corrupt"`` (non-index error
  486. class, or re-check still dirty), ``"missing"`` (no / zero-byte file)."""
  487. status: str
  488. db_path: Path
  489. messages: list[str] = field(default_factory=list)
  490. post_repair_messages: list[str] = field(default_factory=list)
  491. backup_path: Optional[Path] = None
  492. reindexed: list[str] = field(default_factory=list)
  493. def repair_db(db_path: Optional[Path] = None, *, board: Optional[str] = None) -> RepairResult:
  494. """Probe a kanban DB and apply the narrow index-REINDEX repair if needed.
  495. Same policy as :func:`_guard_existing_db_is_healthy` (quarantine BEFORE
  496. any mutation; REINDEX under the init flock; anything non-index stays
  497. corrupt), but returns a :class:`RepairResult` instead of raising so
  498. ``hermes kanban repair`` can pick its exit code. ``OperationalError``
  499. (locked/busy) still propagates raw: a locked healthy DB must not be
  500. quarantined."""
  501. path = db_path if db_path is not None else _kb.kanban_db_path(board=board)
  502. try:
  503. resolved = path.resolve()
  504. except OSError:
  505. resolved = path
  506. if _missing_or_empty(resolved):
  507. return RepairResult(status="missing", db_path=resolved)
  508. with _cross_process_init_lock(resolved):
  509. messages, reason = _probe_for_corruption(resolved)
  510. if messages is None:
  511. # Same quarantine the connect-time guard takes when sqlite
  512. # refuses to open the file at all.
  513. return RepairResult(
  514. status="corrupt", db_path=resolved, messages=[str(reason)],
  515. backup_path=_backup_corrupt_db(resolved),
  516. )
  517. if reason is None:
  518. return RepairResult(status="ok", db_path=resolved, messages=messages)
  519. # Quarantine FIRST — identical policy to the connect-time guard.
  520. backup = _backup_corrupt_db(resolved)
  521. index_names = _repairable_index_names(messages)
  522. if not index_names:
  523. return RepairResult(status="corrupt", db_path=resolved, messages=messages, backup_path=backup)
  524. repaired, post = _attempt_index_reindex_repair(resolved, index_names)
  525. # The file changed on disk; force the next connect() in this process
  526. # to re-probe instead of trusting the stale healthy-path cache.
  527. with _INIT_LOCK:
  528. _INITIALIZED_PATHS.discard(str(resolved))
  529. return RepairResult(
  530. status="repaired" if repaired else "corrupt",
  531. db_path=resolved,
  532. messages=messages,
  533. post_repair_messages=post,
  534. backup_path=backup,
  535. reindexed=index_names,
  536. )
  537. def _schema_is_present(conn: sqlite3.Connection) -> bool:
  538. """Whether an open connection actually sees the kanban schema. ``tasks`` is
  539. the sentinel (SCHEMA_SQL always creates it; SQLite loses tables
  540. all-or-nothing), so one ``sqlite_master`` lookup on the resident page 1
  541. suffices — cheap by design, it runs on every steady-state connect()."""
  542. try:
  543. row = conn.execute(
  544. "SELECT 1 FROM sqlite_master WHERE type='table' AND name='tasks' LIMIT 1"
  545. ).fetchone()
  546. except sqlite3.DatabaseError:
  547. # Unreadable schema table is not this guard's call — the full init
  548. # path's header/integrity probes classify and quarantine it.
  549. return False
  550. return row is not None
  551. def _open_configured(path: Path, under_lock) -> tuple[sqlite3.Connection, Any]:
  552. """Open ``path`` with the kanban PRAGMA set, then run ``under_lock(conn)``.
  553. WAL activation and ``under_lock`` share the ``_INIT_LOCK`` critical section:
  554. WAL setup can take an exclusive lock while SQLite creates sidecars for a
  555. fresh DB, and concurrent gateway startup threads must not race before
  556. ``_INITIALIZED_PATHS`` is populated. Closed if anything raises."""
  557. conn = _sqlite_connect(path)
  558. try:
  559. conn.row_factory = sqlite3.Row
  560. conn.text_factory = _kb._lossy_text
  561. with _INIT_LOCK:
  562. # WAL doesn't work on network filesystems; the helper falls back to
  563. # DELETE with one ERROR log (see hermes_state_wal._WAL_INCOMPAT_MARKERS).
  564. from hermes_state_wal import apply_wal_with_fallback
  565. apply_wal_with_fallback(conn, db_label=f"kanban.db ({path.name})")
  566. # FULL (not NORMAL): fsync before each checkpoint to narrow the
  567. # crash window that can leave a b-tree page header torn.
  568. conn.execute("PRAGMA synchronous=FULL")
  569. conn.execute("PRAGMA wal_autocheckpoint=100")
  570. # Bound the WAL file: the periodic explicit checkpoint is PASSIVE
  571. # (never truncates), so SQLite trims -wal to this limit on the
  572. # writer's natural post-checkpoint reset.
  573. conn.execute("PRAGMA journal_size_limit=8388608")
  574. conn.execute("PRAGMA foreign_keys=ON")
  575. # Zero freed pages so a later torn write can't expose stale cells.
  576. conn.execute("PRAGMA secure_delete=ON")
  577. # Surface corrupt cells as read errors instead of silent wrong data.
  578. conn.execute("PRAGMA cell_size_check=ON")
  579. out = under_lock(conn)
  580. except Exception:
  581. conn.close()
  582. raise
  583. return conn, out
  584. def _refuse_dead_board_resurrection(path: Path, board: Optional[str]) -> None:
  585. """Refuse to create a fresh DB for a board that is not live.
  586. ``board=None`` (or ``default``) is unaffected — except that a None board
  587. can still *resolve* through the current-board context (``--board`` scope,
  588. ``HERMES_KANBAN_BOARD``, the ``current`` file) to a named board dir; an
  589. ``archived`` tombstone in that dir gets the same refusal. For any other
  590. slug whose DB file does not exist yet, opening it would mkdir the board
  591. directory and leave an empty DB-only stub — exactly how archived boards
  592. (moved to ``_archived/`` with an ``archived`` tombstone ``board.json``) and
  593. hard-deleted boards (no directory left at all) used to reappear as empty
  594. active boards via stale dashboard/gateway read paths (#43243). Creation
  595. is the job of :func:`kanban_db.create_board` (which writes the metadata
  596. first); read paths get an actionable error instead of a resurrected board.
  597. """
  598. if path.exists():
  599. return # existing DB: a plain open, never a resurrection
  600. if board is None:
  601. # Default-DB and env-pinned paths never sit under a board dir, so a
  602. # board.json here means the current-board context resolved to that
  603. # board's dir; refuse only the archived case (a fresh home has no
  604. # board.json next to its kanban.db, so creation still self-heals).
  605. tombstone = path.parent / "board.json"
  606. if tombstone.exists():
  607. import json
  608. try:
  609. raw = json.loads(tombstone.read_text(encoding="utf-8-sig"))
  610. except (OSError, ValueError):
  611. return
  612. if isinstance(raw, dict) and raw.get("archived"):
  613. raise ValueError(
  614. f"kanban board {path.parent.name!r} is archived; "
  615. "refusing to recreate its database"
  616. )
  617. return
  618. try:
  619. slug = _kb._normalize_board_slug(board)
  620. except ValueError:
  621. return # malformed slug: let the later open surface the error
  622. if not slug or slug == _kb.DEFAULT_BOARD:
  623. return
  624. meta = _kb.read_board_metadata(slug)
  625. if meta.get("archived"):
  626. raise ValueError(f"kanban board {slug!r} is archived; refusing to recreate its database")
  627. if not _kb.board_metadata_path(slug).exists():
  628. raise ValueError(f"kanban board {slug!r} does not exist; create it with `hermes kanban boards create {slug}`")
  629. def connect(db_path: Optional[Path] = None, *, board: Optional[str] = None) -> sqlite3.Connection:
  630. """Open (and initialize if needed) the kanban DB. WAL is (re)enabled on
  631. every connection so a re-created file stays robust; the first connection
  632. per path auto-runs :func:`init_db`, later ones skip via
  633. ``_INITIALIZED_PATHS``. Path: explicit ``db_path``, else ``board``, else
  634. :func:`kanban_db_path` (``HERMES_KANBAN_DB`` -> ``HERMES_KANBAN_BOARD`` ->
  635. ``<root>/kanban/current`` -> ``default``)."""
  636. path = db_path if db_path is not None else _kb.kanban_db_path(board=board)
  637. from agent.delegation_context import kanban_path_is_fenced
  638. if kanban_path_is_fenced(path):
  639. # Reads must not enter schema/backfill write transactions. Never create a
  640. # missing board or migrate on a descendant's behalf; the owner initializes it.
  641. conn = sqlite3.connect(path.resolve().as_uri() + "?mode=ro", uri=True)
  642. conn.row_factory = sqlite3.Row
  643. conn.text_factory = _kb._lossy_text
  644. if not _schema_is_present(conn):
  645. conn.close()
  646. raise PermissionError("Kanban descendants require an initialized board; ask its owner to initialize it")
  647. return conn
  648. _refuse_dead_board_resurrection(path, board)
  649. path.parent.mkdir(parents=True, exist_ok=True)
  650. # Fast path: once THIS process has initialized this path, skip the
  651. # cross-process init lock. Taking it on every connect let a single stalled
  652. # holder (e.g. an external `hermes kanban list` mid-integrity-probe) block
  653. # the gateway dispatcher's next-tick connect() forever, and steady-state has
  654. # nothing for it to protect (no schema/migration writes).
  655. resolved = str(path.resolve())
  656. if resolved in _INITIALIZED_PATHS:
  657. conn, schema_present = _open_configured(path, _schema_is_present)
  658. if schema_present:
  659. return conn
  660. # Cache says "initialized", file says otherwise: it was deleted or
  661. # replaced under a live process and the open silently recreated an empty
  662. # DB. Left alone, every query fails with "no such table: tasks" for the
  663. # rest of the process's life. Drop the stale entry and re-init.
  664. conn.close()
  665. with _INIT_LOCK:
  666. # Drop the stale cache entry and fall through to the full init path, which re-runs the header
  667. # and integrity probes and the schema script under the cross-process lock. See #83445.
  668. _INITIALIZED_PATHS.discard(resolved)
  669. _kb._log.warning(
  670. "kanban DB %s lost its schema after this process initialized it "
  671. "(deleted or replaced externally); re-initializing.",
  672. path,
  673. )
  674. with _cross_process_init_lock(path):
  675. # Read-only file/sidecar preflight first, so a stray read-only kanban.db
  676. # fails actionably instead of "attempt to write a readonly database".
  677. # See #12508.
  678. from hermes_state import preflight_db_writability
  679. preflight_db_writability(path, db_label=f"kanban.db ({path.name})")
  680. # Cheap byte-level header check before any sqlite connection, then the
  681. # full integrity probe (cached per path via _INITIALIZED_PATHS).
  682. _validate_sqlite_header(path)
  683. _guard_existing_db_is_healthy(path)
  684. resolved = str(path.resolve())
  685. def _init_if_needed(conn: sqlite3.Connection) -> None:
  686. # Idempotent; runs under _INIT_LOCK so same-process dispatcher
  687. # threads can't race the ALTER TABLE pass with stale PRAGMA snapshots.
  688. if resolved not in _INITIALIZED_PATHS:
  689. conn.executescript(_kb.SCHEMA_SQL)
  690. _migrate_add_optional_columns(conn)
  691. _INITIALIZED_PATHS.add(resolved)
  692. conn, _ = _open_configured(path, _init_if_needed)
  693. return conn
  694. @contextlib.contextmanager
  695. def connect_closing(db_path: Optional[Path] = None, *, board: Optional[str] = None):
  696. """Open a kanban DB connection and guarantee it is closed on exit. Use
  697. instead of ``with kb.connect() as conn:`` — sqlite3's context manager only
  698. commits/rolls back, it does NOT close the fd, so long-lived processes
  699. (gateway, dashboard) leak FDs until ``[Errno 24] Too many open files``.
  700. See #33159 for the production incident.
  701. """
  702. conn = connect(db_path=db_path, board=board)
  703. try:
  704. yield conn
  705. finally:
  706. with contextlib.suppress(Exception):
  707. conn.close()
  708. def init_db(db_path: Optional[Path] = None, *, board: Optional[str] = None) -> Path:
  709. """Create the schema if it doesn't exist; return the path used. Unlike
  710. :func:`connect`'s cached first-time auto-init, this always re-runs the
  711. migration pass — callers that know the on-disk schema may have drifted
  712. (tests writing legacy event kinds, external upgrades) use it to force it."""
  713. path = db_path if db_path is not None else _kb.kanban_db_path(board=board)
  714. _refuse_dead_board_resurrection(path, board)
  715. path.parent.mkdir(parents=True, exist_ok=True)
  716. # Clear the cache entry so connect() re-runs schema + migrations.
  717. with _INIT_LOCK:
  718. _INITIALIZED_PATHS.discard(str(path.resolve()))
  719. with contextlib.closing(connect(path)):
  720. pass
  721. return path
  722. # Nullable/defaulted columns of the v1 ``tasks`` CREATE TABLE that external
  723. # harnesses seeding a board with a reduced schema have omitted. Hermes's own
  724. # DBs always carry them, so this is a no-op there; without it a board that
  725. # also has ``task_runs`` fails every ``connect()`` inside
  726. # ``_backfill_legacy_inflight_runs`` ("no such column: claim_lock") — before
  727. # ``_INITIALIZED_PATHS`` caches, so the dispatcher re-raises each tick (#112953).
  728. # DDL must match SCHEMA_SQL exactly.
  729. _BASE_TASK_COLUMNS = (
  730. ("body", "body TEXT"),
  731. ("assignee", "assignee TEXT"),
  732. ("priority", "priority INTEGER DEFAULT 0"),
  733. ("created_by", "created_by TEXT"),
  734. ("started_at", "started_at INTEGER"),
  735. ("completed_at", "completed_at INTEGER"),
  736. ("workspace_kind", "workspace_kind TEXT NOT NULL DEFAULT 'scratch'"),
  737. ("workspace_path", "workspace_path TEXT"),
  738. ("claim_lock", "claim_lock TEXT"),
  739. ("claim_expires", "claim_expires INTEGER"),
  740. )
  741. # Additive ``tasks`` columns in the order legacy DBs receive them (= physical
  742. # column order for ``SELECT *`` on migrated boards).
  743. _EARLY_TASK_COLUMNS = (
  744. ("tenant", "tenant TEXT"),
  745. ("result", "result TEXT"),
  746. ("branch_name", "branch_name TEXT"),
  747. ("project_id", "project_id TEXT"),
  748. ("idempotency_key", "idempotency_key TEXT"),
  749. )
  750. # (new column, ddl, legacy source column, copy statement) — see the
  751. # RENAME-avoidance note in ``_migrate_add_optional_columns``.
  752. _RENAMED_TASK_COLUMNS = (
  753. (
  754. "consecutive_failures", "consecutive_failures INTEGER NOT NULL DEFAULT 0",
  755. "spawn_failures", "UPDATE tasks SET consecutive_failures = COALESCE(spawn_failures, 0)",
  756. ),
  757. ("worker_pid", "worker_pid INTEGER", None, None),
  758. (
  759. "last_failure_error", "last_failure_error TEXT",
  760. "last_spawn_error", "UPDATE tasks SET last_failure_error = last_spawn_error",
  761. ),
  762. )
  763. # NULL / 0 defaults below reproduce the behaviour existing rows had before the
  764. # column existed.
  765. _LATER_TASK_COLUMNS = (
  766. ("max_runtime_seconds", "max_runtime_seconds INTEGER"),
  767. ("last_heartbeat_at", "last_heartbeat_at INTEGER"),
  768. ("current_run_id", "current_run_id INTEGER"),
  769. ("workflow_template_id", "workflow_template_id TEXT"),
  770. ("current_step_key", "current_step_key TEXT"),
  771. # JSON array of skill names the dispatcher force-loads via --skills.
  772. ("skills", "skills TEXT"),
  773. # Per-task override for the consecutive-failure circuit breaker; NULL =
  774. # ``kanban.failure_limit`` config, then ``DEFAULT_FAILURE_LIMIT``.
  775. ("max_retries", "max_retries INTEGER"),
  776. ("model_override", "model_override TEXT"),
  777. ("provider_override", "provider_override TEXT"),
  778. ("reasoning_effort", "reasoning_effort TEXT"),
  779. # Ralph-style goal loop toggle; 0 = classic single-shot worker.
  780. ("goal_mode", "goal_mode INTEGER NOT NULL DEFAULT 0"),
  781. ("goal_max_turns", "goal_max_turns INTEGER"),
  782. ("completion_contract", "completion_contract TEXT"),
  783. ("session_id", "session_id TEXT"),
  784. # Typed block reason (VALID_BLOCK_KINDS); NULL = generic human blocker.
  785. ("block_kind", "block_kind TEXT"),
  786. ("block_recurrences", "block_recurrences INTEGER NOT NULL DEFAULT 0"),
  787. # Spawn-time start fingerprint of worker_pid (PID-reuse guard; NULL = legacy row).
  788. ("worker_started_at", "worker_started_at INTEGER"),
  789. )
  790. _NOTIFY_SUB_COLUMNS = (
  791. ("last_ping_event_id", "last_ping_event_id INTEGER NOT NULL DEFAULT 0"),
  792. ("notifier_profile", "notifier_profile TEXT"),
  793. ("delivery_mode", "delivery_mode TEXT NOT NULL DEFAULT 'notify'"),
  794. ("chat_type", "chat_type TEXT"),
  795. # Platform-specific stable alt ID (Signal UUID, Feishu union_id, ...)
  796. # so an active-wake replay reconstructs the SAME ``build_session_key``
  797. # (which prefers ``user_id_alt``). NULL is inert.
  798. ("user_id_alt", "user_id_alt TEXT"),
  799. ("delivery_metadata", "delivery_metadata TEXT"),
  800. )
  801. _TASK_RUN_COLUMNS = (
  802. # Spawn-time start fingerprint of the run's worker_pid (PID-reuse guard for the
  803. # terminal-worker reaper; NULL = legacy row, never signalled).
  804. ("worker_started_at", "worker_started_at INTEGER"),
  805. )
  806. def _column_names(conn: sqlite3.Connection, table: str) -> set[str]:
  807. return {row["name"] for row in conn.execute(f"PRAGMA table_info({table})")}
  808. def _table_exists(conn: sqlite3.Connection, table: str) -> bool:
  809. return conn.execute(
  810. f"SELECT name FROM sqlite_master WHERE type='table' AND name='{table}'"
  811. ).fetchone() is not None
  812. def _migrate_add_optional_columns(conn: sqlite3.Connection) -> None:
  813. """Add columns introduced after v1 to legacy DBs (called via ``init_db``)."""
  814. cols = _column_names(conn, "tasks")
  815. for name, ddl in _BASE_TASK_COLUMNS + _EARLY_TASK_COLUMNS:
  816. if name not in cols:
  817. _add_column_if_missing(conn, "tasks", name, ddl)
  818. # Re-snapshot: DBs partially migrated by older releases may already carry
  819. # later columns (e.g. ``consecutive_failures``), keeping the legacy-column
  820. # migration idempotent.
  821. cols = _column_names(conn, "tasks")
  822. # Legacy renames via ADD-then-copy rather than ``RENAME COLUMN``: very old
  823. # DBs may lack the legacy column entirely (RENAME raises "no such column"),
  824. # and RENAME reparses the whole schema, failing if views/triggers reference
  825. # the old name. Historical counter values are preserved when present.
  826. for name, ddl, legacy, copy_sql in _RENAMED_TASK_COLUMNS:
  827. if name not in cols:
  828. added = _add_column_if_missing(conn, "tasks", name, ddl)
  829. if added and legacy is not None and legacy in cols:
  830. conn.execute(copy_sql)
  831. for name, ddl in _LATER_TASK_COLUMNS:
  832. if name not in cols:
  833. _add_column_if_missing(conn, "tasks", name, ddl)
  834. # Indexes over additive ``tasks`` columns must be created AFTER the columns
  835. # exist: ``executescript`` parses each statement against the live schema,
  836. # so a ``CREATE INDEX`` over a missing column in SCHEMA_SQL would abort
  837. # init on legacy boards before the ALTER TABLE pass runs. ``IF NOT EXISTS``
  838. # keeps re-running here cheap and correct on fresh DBs.
  839. conn.execute("CREATE INDEX IF NOT EXISTS idx_tasks_assignee_status ON tasks(assignee, status)")
  840. conn.execute("CREATE INDEX IF NOT EXISTS idx_tasks_tenant ON tasks(tenant)")
  841. conn.execute("CREATE INDEX IF NOT EXISTS idx_tasks_idempotency ON tasks(idempotency_key)")
  842. conn.execute("CREATE INDEX IF NOT EXISTS idx_tasks_session_id ON tasks(session_id)")
  843. # task_events.run_id back-fills as NULL for historical events (they predate
  844. # runs and can't be attributed).
  845. if "run_id" not in _column_names(conn, "task_events"):
  846. _add_column_if_missing(conn, "task_events", "run_id", "run_id INTEGER")
  847. # Same ordering rule as the ``tasks`` indexes above: index after column.
  848. conn.execute("CREATE INDEX IF NOT EXISTS idx_events_run ON task_events(run_id, id)")
  849. if _table_exists(conn, "kanban_notify_subs"):
  850. notify_cols = _column_names(conn, "kanban_notify_subs")
  851. for name, ddl in _NOTIFY_SUB_COLUMNS:
  852. if name in notify_cols:
  853. continue
  854. _add_column_if_missing(conn, "kanban_notify_subs", name, ddl)
  855. if name == "delivery_mode":
  856. # Backfill ONLY on first-add: pre-column gateway subscriptions
  857. # had de facto active wake; defaulting them to 'notify' would
  858. # silently disable that on upgrade. TUI/CLI rows keep 'notify'
  859. # (matches _maybe_auto_subscribe). A later explicit downgrade
  860. # is never overwritten.
  861. conn.execute(
  862. "UPDATE kanban_notify_subs SET delivery_mode = 'notify+wake' "
  863. "WHERE platform != 'tui'"
  864. )
  865. if _table_exists(conn, "task_runs"):
  866. run_cols = _column_names(conn, "task_runs")
  867. for name, ddl in _TASK_RUN_COLUMNS:
  868. if name not in run_cols:
  869. _add_column_if_missing(conn, "task_runs", name, ddl)
  870. _backfill_legacy_inflight_runs(conn)
  871. # One-shot event-kind rename: old names still worked but were awkward on
  872. # the wire. Fires once per DB — after the UPDATE no rows match.
  873. for old, new in (
  874. ("ready", "promoted"),
  875. ("priority", "reprioritized"),
  876. ("spawn_auto_blocked", "gave_up"),
  877. ):
  878. conn.execute("UPDATE task_events SET kind = ? WHERE kind = ?", (new, old))
  879. _rebuild_drifted_tables(conn)
  880. def _backfill_legacy_inflight_runs(conn: sqlite3.Connection) -> None:
  881. """One-shot backfill: tasks 'running' before runs existed carried
  882. claim_lock / claim_expires / worker_pid on the task row; synthesize a
  883. matching task_runs row so end-run / heartbeat have something to write.
  884. write_txn serializes against concurrent dispatchers, and the per-row
  885. UPDATE uses ``current_run_id IS NULL`` as a CAS guard so a racing claim
  886. can't produce an orphaned row."""
  887. with write_txn(conn):
  888. inflight = conn.execute(
  889. "SELECT id, assignee, claim_lock, claim_expires, worker_pid, "
  890. " max_runtime_seconds, last_heartbeat_at, started_at "
  891. "FROM tasks "
  892. "WHERE status = 'running' AND current_run_id IS NULL"
  893. ).fetchall()
  894. for row in inflight:
  895. started = row["started_at"] or int(time.time())
  896. cur = conn.execute(
  897. """
  898. INSERT INTO task_runs (
  899. task_id, profile, status,
  900. claim_lock, claim_expires, worker_pid,
  901. max_runtime_seconds, last_heartbeat_at,
  902. started_at
  903. ) VALUES (?, ?, 'running', ?, ?, ?, ?, ?, ?)
  904. """,
  905. (
  906. row["id"], row["assignee"], row["claim_lock"],
  907. row["claim_expires"], row["worker_pid"],
  908. row["max_runtime_seconds"], row["last_heartbeat_at"],
  909. started,
  910. ),
  911. )
  912. # CAS: only install the pointer if nothing claimed the task
  913. # since our SELECT (belt-and-suspenders under write_txn). On
  914. # failure mark the orphan run row reclaimed so it doesn't
  915. # look in-flight.
  916. upd = conn.execute(
  917. "UPDATE tasks SET current_run_id = ? "
  918. "WHERE id = ? AND current_run_id IS NULL",
  919. (cur.lastrowid, row["id"]),
  920. )
  921. if upd.rowcount != 1:
  922. conn.execute(
  923. "UPDATE task_runs SET status = 'reclaimed', "
  924. " outcome = 'reclaimed', ended_at = ? "
  925. "WHERE id = ?",
  926. (int(time.time()), cur.lastrowid),
  927. )
  928. # Legacy DBs used a ``TEXT PRIMARY KEY`` id (nullable ``TEXT last_event_id``
  929. # for ``kanban_notify_subs``); the additive migrations can't change a column
  930. # type, so drift requires a rebuild. Each entry pairs the canonical CREATE
  931. # TABLE with the indexes DROP TABLE takes down with it.
  932. # ``test_rebuilt_schema_matches_fresh`` guards this against SCHEMA_SQL drift.
  933. # The current schema uses ``INTEGER PRIMARY KEY AUTOINCREMENT`` / ``INTEGER NOT NULL DEFAULT 0``. ``CREATE
  934. # TABLE IF NOT EXISTS`` skips existing tables regardless of schema and ``_add_column_if_missing`` only adds
  935. # columns, so neither can fix a drifted column type — the table must be rebuilt. See #35096. Each entry
  936. # pairs the canonical CREATE TABLE with the CREATE INDEX statements that DROP TABLE would otherwise take
  937. # down with it (including ``idx_events_run``, added by the additive pass above). To guard against this list
  938. # drifting from SCHEMA_SQL, ``test_rebuilt_schema_matches_fresh`` asserts a rebuilt legacy DB is
  939. # byte-identical to a fresh one.
  940. _REBUILD_SPECS = {
  941. "task_events": (
  942. "CREATE TABLE task_events ("
  943. " id INTEGER PRIMARY KEY AUTOINCREMENT,"
  944. " task_id TEXT NOT NULL, run_id INTEGER, kind TEXT NOT NULL,"
  945. " payload TEXT, created_at INTEGER NOT NULL)",
  946. (
  947. "CREATE INDEX idx_events_task ON task_events(task_id, created_at)",
  948. "CREATE INDEX idx_events_run ON task_events(run_id, id)",
  949. ),
  950. ),
  951. "task_comments": (
  952. "CREATE TABLE task_comments ("
  953. " id INTEGER PRIMARY KEY AUTOINCREMENT,"
  954. " task_id TEXT NOT NULL, author TEXT NOT NULL, body TEXT NOT NULL,"
  955. " created_at INTEGER NOT NULL)",
  956. ("CREATE INDEX idx_comments_task ON task_comments(task_id, created_at)",),
  957. ),
  958. "task_runs": (
  959. "CREATE TABLE task_runs ("
  960. " id INTEGER PRIMARY KEY AUTOINCREMENT,"
  961. " task_id TEXT NOT NULL, profile TEXT, step_key TEXT,"
  962. " status TEXT NOT NULL, claim_lock TEXT, claim_expires INTEGER,"
  963. " worker_pid INTEGER, worker_started_at INTEGER, max_runtime_seconds INTEGER,"
  964. " last_heartbeat_at INTEGER, started_at INTEGER NOT NULL,"
  965. " ended_at INTEGER, outcome TEXT, summary TEXT, metadata TEXT,"
  966. " error TEXT)",
  967. (
  968. "CREATE INDEX idx_runs_task ON task_runs(task_id, started_at)",
  969. "CREATE INDEX idx_runs_status ON task_runs(status)",
  970. ),
  971. ),
  972. "kanban_notify_subs": (
  973. "CREATE TABLE kanban_notify_subs ("
  974. " task_id TEXT NOT NULL, platform TEXT NOT NULL, chat_id TEXT NOT NULL,"
  975. " thread_id TEXT NOT NULL DEFAULT '', user_id TEXT, user_id_alt TEXT,"
  976. " chat_type TEXT,"
  977. " notifier_profile TEXT, delivery_mode TEXT NOT NULL DEFAULT 'notify',"
  978. " delivery_metadata TEXT, created_at INTEGER NOT NULL,"
  979. " last_event_id INTEGER NOT NULL DEFAULT 0,"
  980. " last_ping_event_id INTEGER NOT NULL DEFAULT 0,"
  981. " PRIMARY KEY (task_id, platform, chat_id, thread_id))",
  982. ("CREATE INDEX idx_notify_task ON kanban_notify_subs(task_id)",),
  983. ),
  984. }
  985. def _table_has_drifted(conn: sqlite3.Connection, table: str) -> bool:
  986. """True when ``table`` still carries the legacy (pre-AUTOINCREMENT) shape."""
  987. info = conn.execute(f"PRAGMA table_info({table})").fetchall()
  988. if not info:
  989. return False # table absent — nothing to rebuild
  990. if table == "kanban_notify_subs":
  991. lei = next((c for c in info if c["name"] == "last_event_id"), None)
  992. return lei is not None and (lei["type"] or "").upper() != "INTEGER"
  993. # task_events / task_comments / task_runs: id must be INTEGER and a PK.
  994. id_col = next((c for c in info if c["name"] == "id"), None)
  995. if id_col is None:
  996. return False
  997. return not ((id_col["type"] or "").upper() == "INTEGER" and id_col["pk"])
  998. def _rebuild_drifted_tables(conn: sqlite3.Connection) -> None:
  999. """Rebuild any kanban table whose column types drifted from SCHEMA_SQL.
  1000. Drifted boards crash the gateway notifier (``int(None)`` on a NULL id) and
  1001. never match ``id > cursor``, silently losing every notification. Legacy
  1002. TEXT ids are dropped (AUTOINCREMENT reassigns) and cursors reset to 0, so
  1003. the first post-migration tick replays history once — safe for a feature
  1004. that was already fully broken. One transaction under ``connect()``'s init
  1005. locks so an interruption can't leave a table half-renamed. Idempotent.
  1006. Each affected table is rebuilt with the standard SQLite pattern — CREATE new → INSERT shared columns →
  1007. DROP old → RENAME — recreating its indexes too (DROP TABLE takes them down). See #35096.
  1008. """
  1009. drifted = [t for t in _REBUILD_SPECS if _table_has_drifted(conn, t)]
  1010. if not drifted:
  1011. return
  1012. conn.execute("BEGIN IMMEDIATE")
  1013. try:
  1014. for table in drifted:
  1015. create_sql, index_sqls = _REBUILD_SPECS[table]
  1016. old_cols = [c["name"] for c in conn.execute(f"PRAGMA table_info({table})")]
  1017. _kb._log.info("kanban migration: rebuilding %s to match current schema", table)
  1018. conn.execute(f"ALTER TABLE {table} RENAME TO {table}_legacy")
  1019. conn.execute(create_sql)
  1020. new_cols = _column_names(conn, table)
  1021. if table == "kanban_notify_subs":
  1022. # Cast the legacy TEXT cursor to INTEGER; NULL / non-numeric → 0.
  1023. drop, extra_cols = "last_event_id", ", last_event_id"
  1024. extra_select = ", COALESCE(CAST(last_event_id AS INTEGER), 0)"
  1025. else:
  1026. # Drop the legacy TEXT id; AUTOINCREMENT reassigns it.
  1027. drop, extra_cols, extra_select = "id", "", ""
  1028. cols_csv = ", ".join(c for c in old_cols if c in new_cols and c != drop)
  1029. conn.execute(
  1030. f"INSERT INTO {table} ({cols_csv}{extra_cols}) "
  1031. f"SELECT {cols_csv}{extra_select} FROM {table}_legacy"
  1032. )
  1033. conn.execute(f"DROP TABLE {table}_legacy")
  1034. for index_sql in index_sqls:
  1035. conn.execute(index_sql)
  1036. conn.execute("COMMIT")
  1037. except Exception:
  1038. with contextlib.suppress(sqlite3.OperationalError):
  1039. conn.execute("ROLLBACK")
  1040. raise
  1041. def _check_file_length_invariant(conn: sqlite3.Connection) -> None:
  1042. """Raise ``sqlite3.DatabaseError`` if the file is shorter than its header
  1043. page count claims (torn-extend). Both sides are read WITHOUT opening the
  1044. database file (``PRAGMA page_count`` on the existing connection; ``stat()``
  1045. for disk): an earlier bare ``open(path,"rb")`` probe was wrong because
  1046. ``close()`` cancels every POSIX advisory lock this process holds on the
  1047. file, silently dropping concurrent writers' (and a running VACUUM's) locks
  1048. and letting other processes write into a database a writer still believed
  1049. it owned (sqlite.org/howtocorrupt.html §2.2)."""
  1050. from hermes_cli.sqlite_safe_read import file_length_matches_header
  1051. # In WAL mode a just-committed page can still live in -wal, so the main
  1052. # file legitimately lags its page count; only enforce under a rollback
  1053. # journal, where every committed page must already be in the main file.
  1054. try:
  1055. row = conn.execute("PRAGMA journal_mode").fetchone()
  1056. journal_mode = str(row[0]).lower() if row and row[0] is not None else ""
  1057. except sqlite3.Error:
  1058. return
  1059. if journal_mode == "wal":
  1060. return
  1061. if file_length_matches_header(conn) is False:
  1062. raise sqlite3.DatabaseError(
  1063. "torn-extend detected: the database file is shorter than its "
  1064. "header page count claims"
  1065. )
  1066. # SQLite's busy_timeout backoff is near-deterministic, so stampeding writers
  1067. # re-collide in lockstep; a jittered 20-150ms retry on the transaction boundary
  1068. # breaks the convoy (mirrors state.db). Only BEGIN IMMEDIATE and COMMIT are
  1069. # retried — idempotent re-issues, so a CAS inside write_txn is never replayed.
  1070. # 5 retries (not state.db's 15): the 120s busy_timeout absorbs most waits.
  1071. _BUSY_MAX_RETRIES = 5
  1072. _BUSY_RETRY_MIN_S = 0.020 # 20ms
  1073. _BUSY_RETRY_MAX_S = 0.150 # 150ms
  1074. def _is_busy_error(exc: BaseException) -> bool:
  1075. return isinstance(exc, sqlite3.OperationalError) and (
  1076. "database is locked" in str(exc).lower()
  1077. or "database is busy" in str(exc).lower()
  1078. )
  1079. def _execute_boundary_with_retry(conn: sqlite3.Connection, sql: str) -> None:
  1080. for attempt in range(_BUSY_MAX_RETRIES + 1):
  1081. try:
  1082. conn.execute(sql)
  1083. return
  1084. except sqlite3.OperationalError as exc:
  1085. if not _is_busy_error(exc) or attempt == _BUSY_MAX_RETRIES:
  1086. raise
  1087. time.sleep(random.uniform(_BUSY_RETRY_MIN_S, _BUSY_RETRY_MAX_S))
  1088. def _main_db_file(conn: sqlite3.Connection) -> Optional[str]:
  1089. """Filesystem path of *conn*'s main database (None for in-memory / unreadable)."""
  1090. try:
  1091. for _seq, name, file in conn.execute("PRAGMA database_list") or ():
  1092. if name == "main":
  1093. return file or None
  1094. except (sqlite3.Error, TypeError, ValueError):
  1095. pass
  1096. return None
  1097. @contextlib.contextmanager
  1098. def write_txn(conn: sqlite3.Connection, *, allow_nested: bool = False):
  1099. """IMMEDIATE write transaction; a claim CAS inside is atomic — at most one
  1100. concurrent writer succeeds.
  1101. Nesting is an explicit opt-in (``allow_nested=True`` → savepoint; otherwise
  1102. a loud ``RuntimeError``). Only composition primitives (``create_task``,
  1103. ``add_comment``) opt in — helpers with post-commit side effects
  1104. (``complete_task`` & co.) must never run under an open outer transaction,
  1105. since those side effects would fire while the outer txn can still roll back.
  1106. """
  1107. _kb._assert_not_delegated_child_mutation(_main_db_file(conn))
  1108. if getattr(conn, "in_transaction", False):
  1109. if not allow_nested:
  1110. raise RuntimeError(
  1111. "write_txn: already inside a transaction. Nested composition "
  1112. "must opt in explicitly with write_txn(conn, allow_nested=True) "
  1113. "(savepoint semantics; the inner RELEASE is not durable until "
  1114. "the outer transaction commits)."
  1115. )
  1116. savepoint = f"hermes_nested_{secrets.token_hex(8)}"
  1117. conn.execute(f"SAVEPOINT {savepoint}")
  1118. try:
  1119. yield conn
  1120. except Exception:
  1121. with contextlib.suppress(sqlite3.OperationalError):
  1122. conn.execute(f"ROLLBACK TO {savepoint}")
  1123. conn.execute(f"RELEASE {savepoint}")
  1124. raise
  1125. else:
  1126. conn.execute(f"RELEASE {savepoint}")
  1127. return
  1128. _execute_boundary_with_retry(conn, "BEGIN IMMEDIATE")
  1129. try:
  1130. yield conn
  1131. except Exception:
  1132. # SQLite may already have auto-rolled-back (EIO, contention, corruption);
  1133. # don't let this secondary failure shadow the real one.
  1134. with contextlib.suppress(sqlite3.OperationalError):
  1135. conn.execute("ROLLBACK")
  1136. raise
  1137. else:
  1138. try:
  1139. _execute_boundary_with_retry(conn, "COMMIT")
  1140. except Exception:
  1141. # COMMIT exhausted retries with the txn still open; roll back so the
  1142. # connection isn't poisoned for the next BEGIN IMMEDIATE.
  1143. with contextlib.suppress(sqlite3.OperationalError):
  1144. conn.execute("ROLLBACK")
  1145. raise
  1146. # Post-commit torn-extend check — raise now rather than silently corrupt.
  1147. _check_file_length_invariant(conn)
  1148. # Late-bound origin namespace (see module docstring); imported LAST so this
  1149. # module is fully populated before ``kanban_db`` imports from it.
  1150. from hermes_cli import kanban_db as _kb