hermes_state_telegram.py 19 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370
  1. """Telegram DM topic-mode mixin for :class:`hermes_state.SessionDB`."""
  2. from __future__ import annotations
  3. import contextlib
  4. import logging
  5. import sqlite3
  6. import time
  7. from typing import Any, Dict, List, Optional
  8. from hermes_state_common import _sql_preview_raw, _sql_session_last_active
  9. from hermes_state_errors import StateDbReplacedError
  10. # caplog tests pin the "hermes_state" logger name.
  11. logger = logging.getLogger("hermes_state")
  12. def _normalize_telegram_topic_profile_name(profile_name: Optional[str] = None) -> str:
  13. """Empty/missing → ``"default"`` (single namespace for non-multiplexed gateways).
  14. Multiplexed callers must pass the *routed* profile (``source.profile``), never the
  15. process-global active profile."""
  16. name = str(profile_name or "").strip()
  17. return name if name else "default"
  18. # (table, column list, DDL body). profile_name leads the PK: a private chat_id is the
  19. # user id, identical across bots sharing one state.db.
  20. _TOPIC_TABLES = (
  21. (
  22. "telegram_dm_topic_mode",
  23. "profile_name, chat_id, user_id, enabled, activated_at, updated_at, "
  24. "has_topics_enabled, allows_users_to_create_topics, capability_checked_at, intro_message_id, pinned_message_id",
  25. """
  26. profile_name TEXT NOT NULL DEFAULT 'default',
  27. chat_id TEXT NOT NULL,
  28. user_id TEXT NOT NULL,
  29. enabled INTEGER NOT NULL DEFAULT 1,
  30. activated_at REAL NOT NULL,
  31. updated_at REAL NOT NULL,
  32. has_topics_enabled INTEGER,
  33. allows_users_to_create_topics INTEGER,
  34. capability_checked_at REAL,
  35. intro_message_id TEXT,
  36. pinned_message_id TEXT,
  37. PRIMARY KEY (profile_name, chat_id)
  38. """,
  39. ),
  40. (
  41. "telegram_dm_topic_bindings",
  42. "profile_name, chat_id, thread_id, user_id, session_key, session_id, managed_mode, linked_at, updated_at",
  43. """
  44. profile_name TEXT NOT NULL DEFAULT 'default',
  45. chat_id TEXT NOT NULL,
  46. thread_id TEXT NOT NULL,
  47. user_id TEXT NOT NULL,
  48. session_key TEXT NOT NULL,
  49. session_id TEXT NOT NULL REFERENCES sessions(id) ON DELETE CASCADE,
  50. managed_mode TEXT NOT NULL DEFAULT 'auto',
  51. linked_at REAL NOT NULL,
  52. updated_at REAL NOT NULL,
  53. PRIMARY KEY (profile_name, chat_id, thread_id)
  54. """,
  55. ),
  56. )
  57. # Shared SELECT for the unlinked-session listing; the profile/bindings clauses are
  58. # spliced in only when the bindings table exists.
  59. _UNLINKED_SELECT_HEAD = f"""
  60. SELECT s.*,
  61. COALESCE(sp.prompt, s.system_prompt)
  62. AS _system_prompt_resolved,
  63. {_sql_preview_raw()},
  64. {_sql_session_last_active("s")} AS last_active
  65. FROM sessions s
  66. LEFT JOIN system_prompts sp
  67. ON sp.hash = s.system_prompt_hash
  68. WHERE s.source = 'telegram'
  69. AND s.user_id = ?
  70. """
  71. _UNLINKED_SELECT_TAIL = """ ORDER BY last_active DESC, s.started_at DESC
  72. LIMIT ?
  73. """
  74. # sessions.profile_name is NULL/empty for legacy rows → treat as default.
  75. _UNLINKED_SCOPE_CLAUSES = """ AND COALESCE(NULLIF(TRIM(s.profile_name), ''), 'default') = ?
  76. AND NOT EXISTS (
  77. SELECT 1 FROM telegram_dm_topic_bindings b
  78. WHERE b.session_id = s.id
  79. )
  80. """
  81. class SessionTelegramTopicsMixin:
  82. """Telegram DM topic-mode tables, bindings and lookups. Read paths tolerate absent
  83. tables (nobody ran ``/topic``) by returning their empty value and never create them;
  84. pre-v3 tables left by an upgrade are self-healed on read."""
  85. def _topic_read(self, read, empty):
  86. """Run ``read()``; an absent table reads as ``empty``. A pre-v3 table left by an upgrade
  87. (#103363) raises ``no such column: profile_name`` — heal it and retry, otherwise the
  88. write-only migration is never reached and topic mode silently reads as off forever."""
  89. try:
  90. return read()
  91. except sqlite3.OperationalError as exc:
  92. if "no such column: profile_name" not in str(exc):
  93. return empty
  94. try:
  95. self.apply_telegram_topic_migration()
  96. except (sqlite3.Error, StateDbReplacedError):
  97. logger.warning("telegram topic tables are pre-v3 and the heal failed; reading as empty", exc_info=True)
  98. return empty
  99. return read()
  100. def _topic_read_one(self, sql: str, params):
  101. return self._topic_read(lambda: self._read_one(sql, params), None)
  102. def _topic_read_all(self, sql: str, params):
  103. return self._topic_read(lambda: self._read_all(sql, params), [])
  104. def apply_telegram_topic_migration(self) -> None:
  105. """Create Telegram DM topic-mode tables on explicit /topic opt-in. Deliberately NOT
  106. part of startup reconciliation: operators can upgrade and keep the old bot
  107. behavior until a user runs /topic. Also invoked by ``_topic_read`` to heal a
  108. pre-v3 table an upgrade left behind (#103363). Schema versions: v1 initial; v2 session_id FK
  109. ON DELETE CASCADE (pruning clears bindings); v3 ``profile_name`` on both tables so
  110. multiplexed gateways sharing one state.db isolate topic state per profile.
  111. See #76423.
  112. """
  113. def _do(conn):
  114. for table, columns, ddl in _TOPIC_TABLES:
  115. conn.execute(f"CREATE TABLE IF NOT EXISTS {table} ({ddl})")
  116. have = {row[1] for row in conn.execute(f"PRAGMA table_info('{table}')")}
  117. if "profile_name" in have:
  118. continue
  119. # v1/v2 → v3. SQLite can't ALTER a PK or FK, so rebuild (also supplies v2's
  120. # ON DELETE CASCADE); _rebuild_table runs inside the open BEGIN IMMEDIATE so a crash
  121. # rolls back instead of stranding rows (#42004). A {table}_new left by an older
  122. # build's executescript crash is dropped first; its legacy table is still intact.
  123. # v1 bindings had no ON DELETE CASCADE, so pruned sessions left orphan rows that
  124. # the v3 FK (foreign_keys=ON on the writer) would reject — copy only live ones.
  125. legacy_columns = columns.replace("profile_name, ", "", 1)
  126. live = " WHERE EXISTS (SELECT 1 FROM sessions s WHERE s.id = session_id)" if "session_id" in columns else ""
  127. conn.execute(f"DROP TABLE IF EXISTS {table}_new")
  128. self._rebuild_table(
  129. conn.cursor(), table, f"{table}_legacy", f"CREATE TABLE {table} ({ddl})",
  130. f"INSERT INTO {table} ({columns}) SELECT 'default', {legacy_columns} FROM {table}_legacy{live}",
  131. )
  132. # Indexes after any rebuild: the user index needs profile_name.
  133. conn.execute(
  134. "CREATE UNIQUE INDEX IF NOT EXISTS idx_telegram_dm_topic_bindings_session "
  135. "ON telegram_dm_topic_bindings(session_id)"
  136. )
  137. conn.execute(
  138. "CREATE INDEX IF NOT EXISTS idx_telegram_dm_topic_bindings_user "
  139. "ON telegram_dm_topic_bindings(profile_name, user_id, chat_id)"
  140. )
  141. conn.execute(
  142. "INSERT INTO state_meta (key, value) VALUES (?, ?) "
  143. "ON CONFLICT(key) DO UPDATE SET value = excluded.value",
  144. ("telegram_dm_topic_schema_version", "3"),
  145. )
  146. self._execute_write(_do)
  147. def enable_telegram_topic_mode(
  148. self, *, chat_id: str, user_id: str, profile_name: str="default", has_topics_enabled: Optional[bool]=None,
  149. allows_users_to_create_topics: Optional[bool]=None,
  150. ) -> None:
  151. """Enable Telegram DM topic mode for one private chat/user. Owns the explicit topic
  152. migration; SessionDB startup must not create these tables.
  153. ``profile_name`` namespaces rows under a shared multiplex ``state.db`` (issue #76423). Callers
  154. handling a multiplexed event must pass the routed profile from ``source.profile``, not the
  155. process-global active profile.
  156. """
  157. self.apply_telegram_topic_migration()
  158. now = time.time()
  159. profile_name = _normalize_telegram_topic_profile_name(profile_name)
  160. def _to_int(value: Optional[bool]) -> Optional[int]:
  161. return None if value is None else (1 if value else 0)
  162. self._write_sql("""
  163. INSERT INTO telegram_dm_topic_mode (
  164. profile_name, chat_id, user_id, enabled, activated_at, updated_at,
  165. has_topics_enabled, allows_users_to_create_topics,
  166. capability_checked_at
  167. ) VALUES (?, ?, ?, 1, ?, ?, ?, ?, ?)
  168. ON CONFLICT(profile_name, chat_id) DO UPDATE SET
  169. user_id = excluded.user_id,
  170. enabled = 1,
  171. updated_at = excluded.updated_at,
  172. has_topics_enabled = excluded.has_topics_enabled,
  173. allows_users_to_create_topics = excluded.allows_users_to_create_topics,
  174. capability_checked_at = excluded.capability_checked_at
  175. """, (profile_name, str(chat_id), str(user_id), now, now,
  176. _to_int(has_topics_enabled), _to_int(allows_users_to_create_topics), now))
  177. def disable_telegram_topic_mode(
  178. self, *, chat_id: str, profile_name: str = "default", clear_bindings: bool = True
  179. ) -> None:
  180. """Disable Telegram DM topic mode for one private chat. ``clear_bindings`` also drops
  181. the chat's bindings so a later re-enable starts clean. Never creates the tables;
  182. absent tables are a no-op."""
  183. profile_name = _normalize_telegram_topic_profile_name(profile_name)
  184. def _do(conn):
  185. with contextlib.suppress(sqlite3.OperationalError):
  186. conn.execute(
  187. "UPDATE telegram_dm_topic_mode SET enabled = 0, updated_at = ? "
  188. "WHERE profile_name = ? AND chat_id = ?",
  189. (time.time(), profile_name, str(chat_id)),
  190. )
  191. if clear_bindings:
  192. conn.execute(
  193. "DELETE FROM telegram_dm_topic_bindings WHERE profile_name = ? AND chat_id = ?",
  194. (profile_name, str(chat_id)),
  195. )
  196. self._execute_write(_do)
  197. def is_telegram_topic_mode_enabled(self, *, chat_id: str, user_id: str, profile_name: str = "default") -> bool:
  198. """Return whether Telegram DM topic mode is enabled for this chat/user."""
  199. profile_name = _normalize_telegram_topic_profile_name(profile_name)
  200. row = self._topic_read_one("""
  201. SELECT enabled FROM telegram_dm_topic_mode
  202. WHERE profile_name = ? AND chat_id = ? AND user_id = ?
  203. """, (profile_name, str(chat_id), str(user_id)))
  204. return bool(row[0]) if row is not None else False
  205. def get_telegram_topic_binding(
  206. self, *, chat_id: str, thread_id: str, profile_name: str = "default"
  207. ) -> Optional[dict[str, Any]]:
  208. """Return the session binding for a Telegram DM topic, if present."""
  209. profile_name = _normalize_telegram_topic_profile_name(profile_name)
  210. row = self._topic_read_one("""
  211. SELECT * FROM telegram_dm_topic_bindings
  212. WHERE profile_name = ? AND chat_id = ? AND thread_id = ?
  213. """, (profile_name, str(chat_id), str(thread_id)))
  214. return dict(row) if row else None
  215. def list_telegram_topic_bindings_for_chat(
  216. self, *, chat_id: str, profile_name: str = "default"
  217. ) -> list[dict[str, Any]]:
  218. """All bindings for one chat, newest first ([] when the table is absent)."""
  219. profile_name = _normalize_telegram_topic_profile_name(profile_name)
  220. rows = self._topic_read_all(
  221. "SELECT * FROM telegram_dm_topic_bindings WHERE profile_name = ? AND chat_id = ? ORDER BY updated_at DESC",
  222. (profile_name, str(chat_id)),
  223. )
  224. return [dict(row) for row in rows]
  225. def get_telegram_topic_binding_by_session(self, *, session_id: str) -> Optional[dict[str, Any]]:
  226. """Reverse lookup via the UNIQUE INDEX on session_id; None when unbound."""
  227. row = self._topic_read_one("""
  228. SELECT * FROM telegram_dm_topic_bindings
  229. WHERE session_id = ?
  230. """, (str(session_id),))
  231. return dict(row) if row else None
  232. def delete_telegram_topic_binding(self, *, chat_id: str, thread_id: str, profile_name: str = "default") -> int:
  233. """Remove the binding row for one (chat, thread) pair. Called when the Bot API confirms
  234. a topic was deleted externally (``Thread not found`` after the same-thread retry
  235. failed); otherwise ``gateway.run._recover_telegram_topic_thread_id`` keeps
  236. redirecting inbound messages to the dead topic. If this removes the chat's *last*
  237. binding, ``telegram_dm_topic_mode`` is flipped to ``enabled = 0`` in the same
  238. transaction, or a user who disabled topics in the Telegram client (not via
  239. ``/topic off``) stays stuck. Returns the number of rows deleted; absent binding or
  240. unmigrated tables are silent no-ops (never raise from a cleanup hot path).
  241. Without this prune, the stale row keeps living in ``telegram_dm_topic_bindings`` and the recovery
  242. logic in ``gateway.run._recover_telegram_topic_thread_id`` cheerfully redirects future inbound
  243. messages to the deleted topic, causing tool progress, approvals, and replies to land in the wrong
  244. place. Issue #31501.
  245. """
  246. chat_id, thread_id = str(chat_id), str(thread_id)
  247. profile_name = _normalize_telegram_topic_profile_name(profile_name)
  248. def _do(conn) -> int:
  249. try:
  250. deleted = conn.execute("""
  251. DELETE FROM telegram_dm_topic_bindings
  252. WHERE profile_name = ? AND chat_id = ? AND thread_id = ?
  253. """, (profile_name, chat_id, thread_id)).rowcount or 0
  254. except sqlite3.OperationalError:
  255. return 0
  256. if not deleted:
  257. return 0
  258. # Last binding gone → disable topic mode in the same transaction (no
  259. # read-after-prune race). telegram_dm_topic_mode absent — binding prune still stands.
  260. with contextlib.suppress(sqlite3.OperationalError):
  261. remaining = conn.execute("""
  262. SELECT 1 FROM telegram_dm_topic_bindings
  263. WHERE profile_name = ? AND chat_id = ? LIMIT 1
  264. """, (profile_name, chat_id)).fetchone()
  265. if remaining is None:
  266. conn.execute(
  267. "UPDATE telegram_dm_topic_mode SET enabled = 0, updated_at = ? "
  268. "WHERE profile_name = ? AND chat_id = ?",
  269. (time.time(), profile_name, chat_id),
  270. )
  271. return deleted
  272. return self._execute_write(_do)
  273. def bind_telegram_topic(
  274. self, *, chat_id: str, thread_id: str, user_id: str, session_key: str,
  275. session_id: str, managed_mode: str = "auto", profile_name: str = "default",
  276. ) -> None:
  277. """Bind one Telegram DM topic thread to one Hermes session. A session may be linked to
  278. only one topic: rebinding the same pair is idempotent; linking the session to a
  279. different topic raises ValueError."""
  280. self.apply_telegram_topic_migration()
  281. now = time.time()
  282. chat_id, thread_id, user_id = str(chat_id), str(thread_id), str(user_id)
  283. session_key, session_id = str(session_key), str(session_id)
  284. profile_name = _normalize_telegram_topic_profile_name(profile_name)
  285. def _do(conn):
  286. existing_session = conn.execute("""
  287. SELECT profile_name, chat_id, thread_id
  288. FROM telegram_dm_topic_bindings
  289. WHERE session_id = ?
  290. """, (session_id,)).fetchone()
  291. if existing_session is not None:
  292. linked_profile, linked_chat, linked_thread = existing_session
  293. if (str(linked_profile), str(linked_chat), str(linked_thread)) != (profile_name, chat_id, thread_id):
  294. raise ValueError("session is already linked to another Telegram topic")
  295. conn.execute("""
  296. INSERT INTO telegram_dm_topic_bindings (
  297. profile_name, chat_id, thread_id, user_id, session_key, session_id,
  298. managed_mode, linked_at, updated_at
  299. ) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)
  300. ON CONFLICT(profile_name, chat_id, thread_id) DO UPDATE SET
  301. user_id = excluded.user_id,
  302. session_key = excluded.session_key,
  303. session_id = excluded.session_id,
  304. managed_mode = excluded.managed_mode,
  305. updated_at = excluded.updated_at
  306. """, (profile_name, chat_id, thread_id, user_id, session_key, session_id, managed_mode, now, now))
  307. self._execute_write(_do)
  308. def is_telegram_session_linked_to_topic(self, *, session_id: str) -> bool:
  309. """True if the session is bound to any Telegram DM topic (absent tables → False)."""
  310. row = self._topic_read_one("""
  311. SELECT 1 FROM telegram_dm_topic_bindings
  312. WHERE session_id = ?
  313. LIMIT 1
  314. """, (str(session_id),))
  315. return row is not None
  316. def list_unlinked_telegram_sessions_for_user(
  317. self, *, chat_id: str, user_id: str, profile_name: str = "default", limit: int = 10
  318. ) -> list[dict[str, Any]]:
  319. """This user's Telegram sessions not bound to a topic. Read-only: if the bindings table
  320. is absent, every session is unlinked and the profile-unscoped query is used.
  321. Scoped by ``profile_name`` so multiplexed profiles do not surface each other.
  322. See #76423.
  323. """
  324. profile_name = _normalize_telegram_topic_profile_name(profile_name)
  325. with self._read_ctx() as conn:
  326. try:
  327. rows = conn.execute(
  328. _UNLINKED_SELECT_HEAD + _UNLINKED_SCOPE_CLAUSES + _UNLINKED_SELECT_TAIL,
  329. (str(user_id), profile_name, int(limit)),
  330. ).fetchall()
  331. except sqlite3.OperationalError:
  332. rows = conn.execute(
  333. _UNLINKED_SELECT_HEAD + _UNLINKED_SELECT_TAIL, (str(user_id), int(limit)),
  334. ).fetchall()
  335. return [self._rich_row(row) for row in rows]