kanban_db_notify.py 17 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441
  1. """Notification subscriptions consumed by the gateway kanban-notifier: per-(task, platform, chat, thread) rows with delivery metadata, unseen-event cursors and purge of stale done-task subs.
  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 json
  8. import sqlite3
  9. import time
  10. from pathlib import Path
  11. from typing import Any
  12. from typing import Iterable
  13. from typing import Mapping
  14. from typing import Optional
  15. from typing import TYPE_CHECKING
  16. if TYPE_CHECKING:
  17. from hermes_cli.kanban_db import Event
  18. # Notifier reaction to a terminal event: "notify" = passive adapter.send only
  19. # (default); "notify+wake" = send AND wake the destination agent; "wake" = wake only.
  20. _NOTIFY_DELIVERY_MODES = ("notify", "notify+wake", "wake")
  21. _SCALAR_TYPES = (str, int, float, bool)
  22. # Subscription primary key predicate; every per-row statement below binds
  23. # ``(task_id, platform, chat_id, thread_id or "")`` against it.
  24. _SUB_KEY_WHERE = "WHERE task_id = ? AND platform = ? AND chat_id = ? AND thread_id = ?"
  25. def _sub_key(task_id: str, platform: str, chat_id: str, thread_id: Optional[str]) -> tuple:
  26. return (task_id, platform, chat_id, thread_id or "")
  27. def _encode_notify_delivery_metadata(metadata: Optional[Mapping[str, Any]]) -> Optional[str]:
  28. """Serialize platform send metadata stored on notification subscriptions."""
  29. if not isinstance(metadata, Mapping):
  30. return None
  31. clean = {
  32. str(key): value
  33. for key, value in metadata.items()
  34. if value is not None and isinstance(value, _SCALAR_TYPES)
  35. }
  36. if not clean:
  37. return None
  38. return json.dumps(clean, sort_keys=True, separators=(",", ":"))
  39. def _decode_notify_delivery_metadata(raw: Any) -> dict[str, Any]:
  40. if isinstance(raw, Mapping):
  41. return dict(raw)
  42. if not raw:
  43. return {}
  44. try:
  45. data = json.loads(str(raw))
  46. except Exception:
  47. return {}
  48. if not isinstance(data, dict):
  49. return {}
  50. return {str(key): value for key, value in data.items() if isinstance(value, _SCALAR_TYPES)}
  51. def add_notify_sub(
  52. conn: sqlite3.Connection,
  53. *,
  54. task_id: str,
  55. platform: str,
  56. chat_id: str,
  57. thread_id: Optional[str] = None,
  58. user_id: Optional[str] = None,
  59. user_id_alt: Optional[str] = None,
  60. chat_type: Optional[str] = None,
  61. notifier_profile: Optional[str] = None,
  62. delivery_mode: Optional[str] = None,
  63. delivery_metadata: Optional[Mapping[str, Any]] = None,
  64. ) -> None:
  65. """Register a gateway source wanting terminal-state notifications for
  66. ``task_id``; idempotent on (task, platform, chat, thread).
  67. ``user_id_alt`` (Signal UUID, Feishu union_id, ...) and ``chat_type`` are
  68. replayed on active wake: ``build_session_key`` prefers the alt id, so
  69. omitting it would key the wake into a different session. ``None`` keeps an
  70. existing row's value. ``delivery_mode``: ``None`` leaves an existing row
  71. untouched, an explicit valid value is last-write-wins, unknown falls back
  72. to ``"notify"``. ``delivery_metadata`` merges supplied routing anchors
  73. into an existing row so re-subscribing never discards them. New subs start
  74. caught up (``last_event_id`` =
  75. ``MAX(task_events.id)``) so the notifier never replays history at boot.
  76. """
  77. valid_mode = delivery_mode if delivery_mode in _NOTIFY_DELIVERY_MODES else None
  78. # api_server is stateless: the adapter has no send(), the wake self-post IS
  79. # the delivery. A plain 'notify' default would leave those subs with no
  80. # delivery mechanism at all. Explicit modes still win.
  81. insert_mode = valid_mode or ("notify+wake" if platform == "api_server" else "notify")
  82. key = _sub_key(task_id, platform, chat_id, thread_id)
  83. with _kb.write_txn(conn):
  84. existing = conn.execute(
  85. "SELECT delivery_metadata FROM kanban_notify_subs " + _SUB_KEY_WHERE,
  86. key,
  87. ).fetchone()
  88. existing_metadata = _decode_notify_delivery_metadata(existing["delivery_metadata"]) if existing else {}
  89. merged_metadata = dict(existing_metadata)
  90. if delivery_metadata:
  91. merged_metadata.update(delivery_metadata)
  92. metadata_json = _encode_notify_delivery_metadata(merged_metadata) if merged_metadata else None
  93. conn.execute(
  94. """
  95. INSERT OR IGNORE INTO kanban_notify_subs
  96. (task_id, platform, chat_id, thread_id, user_id, user_id_alt,
  97. chat_type, notifier_profile, delivery_mode, delivery_metadata,
  98. created_at, last_event_id)
  99. VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,
  100. COALESCE((SELECT MAX(id) FROM task_events WHERE task_id = ?), 0))
  101. """,
  102. (
  103. *key, user_id, user_id_alt, chat_type or "dm", notifier_profile,
  104. insert_mode, metadata_json, int(time.time()), task_id,
  105. ),
  106. )
  107. # chat_type / delivery_mode are last-write-wins; delivery metadata
  108. # preserves existing routing fields while supplied fields overwrite them.
  109. # user_id, user_id_alt and notifier_profile only self-heal legacy rows lacking one.
  110. for column, value, fill_only in (
  111. ("chat_type", chat_type, False),
  112. ("user_id", user_id, True),
  113. ("user_id_alt", user_id_alt, True),
  114. ("notifier_profile", notifier_profile, True),
  115. ("delivery_mode", valid_mode, False),
  116. ("delivery_metadata", metadata_json, False),
  117. ):
  118. if not value:
  119. continue
  120. guard = f" AND ({column} IS NULL OR {column} = '')" if fill_only else ""
  121. conn.execute(
  122. f"UPDATE kanban_notify_subs SET {column} = ? " + _SUB_KEY_WHERE + guard,
  123. (value, *key),
  124. )
  125. def _notify_profile_filter(
  126. notifier_profiles: Optional[Iterable[str]],
  127. *,
  128. include_unowned: bool,
  129. ) -> tuple[str, list[str]]:
  130. """Build an optional SQL predicate for notification profile ownership."""
  131. if notifier_profiles is None:
  132. return "", []
  133. profiles = sorted({str(p).strip() for p in notifier_profiles if str(p).strip()})
  134. clauses: list[str] = []
  135. params: list[str] = []
  136. if profiles:
  137. clauses.append("notifier_profile IN (" + ",".join("?" for _ in profiles) + ")")
  138. params.extend(profiles)
  139. if include_unowned:
  140. clauses.append("notifier_profile IS NULL OR notifier_profile = ''")
  141. if not clauses:
  142. return "0", []
  143. return "(" + ") OR (".join(clauses) + ")", params
  144. def list_notify_subs(
  145. conn: sqlite3.Connection,
  146. task_id: Optional[str] = None,
  147. *,
  148. notifier_profiles: Optional[Iterable[str]] = None,
  149. include_unowned: bool = False,
  150. ) -> list[dict]:
  151. """List subscriptions, optionally restricted to notifier profile owners.
  152. No ``notifier_profiles`` -> all subscriptions. Gateway notifiers pass the
  153. profiles they own so they cannot claim another gateway's events;
  154. ``include_unowned`` (dispatch owner) covers legacy rows without a stamp.
  155. """
  156. owner_where, owner_params = _notify_profile_filter(
  157. notifier_profiles, include_unowned=include_unowned,
  158. )
  159. where: list[str] = []
  160. params: list[Any] = []
  161. if task_id is not None:
  162. where.append("task_id = ?")
  163. params.append(task_id)
  164. if owner_where:
  165. where.append(owner_where)
  166. params.extend(owner_params)
  167. sql = "SELECT * FROM kanban_notify_subs"
  168. if where:
  169. sql += " WHERE " + " AND ".join(f"({clause})" for clause in where)
  170. out: list[dict] = []
  171. for row in conn.execute(sql, params).fetchall():
  172. item = dict(row)
  173. if "delivery_metadata" in item:
  174. item["delivery_metadata"] = _decode_notify_delivery_metadata(item.get("delivery_metadata"))
  175. out.append(item)
  176. return out
  177. def count_notify_subs(
  178. db_path: Optional[Path] = None,
  179. *,
  180. board: Optional[str] = None,
  181. notifier_profiles: Optional[Iterable[str]] = None,
  182. include_unowned: bool = False,
  183. platform: Optional[str] = None,
  184. chat_id: Optional[str] = None,
  185. thread_id: Optional[str] = None,
  186. ) -> int:
  187. """Count ``kanban_notify_subs`` rows via a read-only connection — the
  188. notifier's cheap zero-subscription early exit. Unlike :func:`connect` it
  189. never creates the file, runs init/migration or opens writable; WAL rows are
  190. still visible so a fresh sub is never missed. Missing DB / missing table
  191. counts as zero; platform matches case-insensitively (as notifier routing),
  192. chat/thread exactly. Raises :class:`sqlite3.Error` if the DB exists but is
  193. unreadable — callers pick their own fallback.
  194. """
  195. path = db_path if db_path is not None else _kb.kanban_db_path(board=board)
  196. if not path.exists():
  197. return 0
  198. owner_where, owner_params = _notify_profile_filter(
  199. notifier_profiles, include_unowned=include_unowned,
  200. )
  201. clauses: list[str] = []
  202. params: list[Any] = []
  203. if owner_where:
  204. clauses.append(f"({owner_where})")
  205. params.extend(owner_params)
  206. for clause, value in (
  207. ("LOWER(platform) = LOWER(?)", platform),
  208. ("chat_id = ?", chat_id),
  209. ("thread_id = ?", thread_id),
  210. ):
  211. if value is not None:
  212. clauses.append(clause)
  213. params.append(value)
  214. query = "SELECT COUNT(*) FROM kanban_notify_subs"
  215. if clauses:
  216. query += " WHERE " + " AND ".join(clauses)
  217. conn = sqlite3.connect(path.resolve().as_uri() + "?mode=ro", uri=True)
  218. try:
  219. try:
  220. row = conn.execute(query, params).fetchone()
  221. except sqlite3.OperationalError as exc:
  222. if "no such table" in str(exc).lower():
  223. return 0
  224. raise
  225. return int(row[0]) if row else 0
  226. finally:
  227. conn.close()
  228. def remove_notify_sub(
  229. conn: sqlite3.Connection,
  230. *,
  231. task_id: str,
  232. platform: str,
  233. chat_id: str,
  234. thread_id: Optional[str] = None,
  235. ) -> bool:
  236. with _kb.write_txn(conn):
  237. cur = conn.execute(
  238. "DELETE FROM kanban_notify_subs " + _SUB_KEY_WHERE,
  239. _sub_key(task_id, platform, chat_id, thread_id),
  240. )
  241. return cur.rowcount > 0
  242. def purge_stale_done_notify_subs(conn: sqlite3.Connection, *, max_age_days: int = 30) -> int:
  243. """Delete notify subs whose task sat in ``done``/``blocked`` untouched for
  244. longer than ``max_age_days`` (``<= 0`` disables); returns rows deleted.
  245. Subs survive ``done`` because a reopened task must still notify its origin,
  246. which accumulates forever on never-archiving boards. ``blocked`` is
  247. abandoned (unlike ``backlog``/``ready``) so it reaps on the same clock. Age
  248. = latest event, else ``completed_at``, else ``created_at`` — any activity,
  249. including a reopen, exempts the sub.
  250. The notifier keeps subscriptions alive through ``done`` because a completed task can be reopened (review
  251. corrections, continuation) and the reopened cycle must still notify its origin session. On boards that
  252. never archive, that retention would otherwise accumulate subscription rows forever — each one scanned
  253. every notifier tick. This GC bounds that: a task that has been ``done`` with no new events for the
  254. retention window is treated as settled and its subscriptions are purged. ``blocked`` tasks
  255. (circuit-breaker trips, dead workers) are reaped on the same clock — they are abandoned, not idle,
  256. unlike a ``backlog``/``ready`` card that is merely waiting for pickup (#100955).
  257. """
  258. try:
  259. days = int(max_age_days)
  260. except (TypeError, ValueError):
  261. days = 30
  262. if days <= 0:
  263. return 0
  264. cutoff = int(time.time()) - days * 86400
  265. with _kb.write_txn(conn):
  266. cur = conn.execute(
  267. "DELETE FROM kanban_notify_subs WHERE task_id IN ("
  268. " SELECT t.id FROM tasks t"
  269. " WHERE t.status IN ('done', 'blocked')"
  270. " AND COALESCE("
  271. " (SELECT MAX(e.created_at) FROM task_events e"
  272. " WHERE e.task_id = t.id),"
  273. " t.completed_at, t.created_at, 0"
  274. " ) < ?)",
  275. (cutoff,),
  276. )
  277. return int(cur.rowcount or 0)
  278. def _notify_cursor(
  279. conn: sqlite3.Connection, task_id: str, platform: str, chat_id: str, thread_id: Optional[str],
  280. ) -> Optional[int]:
  281. """``last_event_id`` of one subscription row, or ``None`` when unsubscribed."""
  282. row = conn.execute(
  283. "SELECT last_event_id FROM kanban_notify_subs " + _SUB_KEY_WHERE,
  284. _sub_key(task_id, platform, chat_id, thread_id),
  285. ).fetchone()
  286. return None if row is None else int(row["last_event_id"])
  287. def unseen_events_for_sub(
  288. conn: sqlite3.Connection,
  289. *,
  290. task_id: str,
  291. platform: str,
  292. chat_id: str,
  293. thread_id: Optional[str] = None,
  294. kinds: Optional[Iterable[str]] = None,
  295. ) -> tuple[int, list[Event]]:
  296. """Return ``(new_cursor, events)`` with ``id > last_event_id``. The cursor
  297. is NOT advanced here; call :func:`advance_notify_cursor` after delivery.
  298. """
  299. cursor = _notify_cursor(conn, task_id, platform, chat_id, thread_id)
  300. if cursor is None:
  301. return 0, []
  302. kind_list = list(kinds) if kinds else None
  303. q = (
  304. "SELECT * FROM task_events WHERE task_id = ? AND id > ? "
  305. + ("AND kind IN (" + ",".join("?" * len(kind_list)) + ") " if kind_list else "")
  306. + "ORDER BY id ASC"
  307. )
  308. params: list[Any] = [task_id, cursor]
  309. if kind_list:
  310. params.extend(kind_list)
  311. rows = conn.execute(q, params).fetchall()
  312. out = [_kb.Event.from_row(r) for r in rows]
  313. max_id = max([cursor, *(int(r["id"]) for r in rows)])
  314. return max_id, out
  315. def claim_unseen_events_for_sub(
  316. conn: sqlite3.Connection,
  317. *,
  318. task_id: str,
  319. platform: str,
  320. chat_id: str,
  321. thread_id: Optional[str] = None,
  322. kinds: Optional[Iterable[str]] = None,
  323. ) -> tuple[int, int, list[Event]]:
  324. """Atomically claim unseen events for one subscription.
  325. Returns ``(old_cursor, new_cursor, events)``; when events are returned the
  326. row's ``last_event_id`` has already been advanced inside ``BEGIN IMMEDIATE``,
  327. so concurrent gateway watchers on the same board DB serialize on SQLite's
  328. writer lock and only the first claims a given event range. Callers send the
  329. events, then leave the cursor or call :func:`rewind_notify_cursor` on
  330. delivery failure.
  331. """
  332. with _kb.write_txn(conn):
  333. old_cursor = _notify_cursor(conn, task_id, platform, chat_id, thread_id)
  334. if old_cursor is None:
  335. return 0, 0, []
  336. new_cursor, events = unseen_events_for_sub(
  337. conn, task_id=task_id, platform=platform, chat_id=chat_id,
  338. thread_id=thread_id, kinds=kinds,
  339. )
  340. if not events:
  341. return old_cursor, old_cursor, []
  342. _cas_cursor(conn, _sub_key(task_id, platform, chat_id, thread_id), new_cursor, old_cursor)
  343. return old_cursor, new_cursor, events
  344. def _cas_cursor(conn: sqlite3.Connection, key: tuple, new_cursor: int, expected: int) -> sqlite3.Cursor:
  345. """Move ``last_event_id`` only if it still equals ``expected``."""
  346. return conn.execute(
  347. "UPDATE kanban_notify_subs SET last_event_id = ? " + _SUB_KEY_WHERE + " AND last_event_id = ?",
  348. (int(new_cursor), *key, int(expected)),
  349. )
  350. def advance_notify_cursor(
  351. conn: sqlite3.Connection,
  352. *,
  353. task_id: str,
  354. platform: str,
  355. chat_id: str,
  356. thread_id: Optional[str] = None,
  357. new_cursor: int,
  358. ) -> None:
  359. with _kb.write_txn(conn):
  360. conn.execute(
  361. "UPDATE kanban_notify_subs SET last_event_id = ? " + _SUB_KEY_WHERE,
  362. (int(new_cursor), *_sub_key(task_id, platform, chat_id, thread_id)),
  363. )
  364. def record_notify_ping(
  365. conn: sqlite3.Connection, *, task_id: str, platform: str, chat_id: str,
  366. thread_id: Optional[str] = None, event_id: int,
  367. ) -> None:
  368. """Checkpoint a sent ping independently of the retryable wake cursor."""
  369. with _kb.write_txn(conn):
  370. conn.execute(
  371. "UPDATE kanban_notify_subs SET last_ping_event_id = MAX(last_ping_event_id, ?) "
  372. + _SUB_KEY_WHERE,
  373. (int(event_id), *_sub_key(task_id, platform, chat_id, thread_id)),
  374. )
  375. def rewind_notify_cursor(
  376. conn: sqlite3.Connection,
  377. *,
  378. task_id: str,
  379. platform: str,
  380. chat_id: str,
  381. thread_id: Optional[str] = None,
  382. claimed_cursor: int,
  383. old_cursor: int,
  384. ) -> bool:
  385. """Undo a claim when delivery fails. The CAS guard only rewinds if no later
  386. notifier advanced the row, so retries never clobber newer progress.
  387. """
  388. with _kb.write_txn(conn):
  389. cur = _cas_cursor(conn, _sub_key(task_id, platform, chat_id, thread_id), old_cursor, claimed_cursor)
  390. return cur.rowcount > 0
  391. # Late-bound origin namespace (see module docstring); imported LAST so this
  392. # module is fully populated before ``kanban_db`` imports from it.
  393. from hermes_cli import kanban_db as _kb