Repository navigation
Expand file tree
/
Copy pathdatabase.py
More file actions
805 lines (741 loc) · 31.1 KB
/
Copy pathdatabase.py
File metadata and controls
805 lines (741 loc) · 31.1 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487
488
489
490
491
492
493
494
495
496
497
498
499
500
501
502
503
504
505
506
507
508
509
510
511
512
513
514
515
516
517
518
519
520
521
522
523
524
525
526
527
528
529
530
531
532
533
534
535
536
537
538
539
540
541
542
543
544
545
546
547
548
549
550
551
552
553
554
555
556
557
558
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583
584
585
586
587
588
589
590
591
592
593
594
595
596
597
598
599
600
601
602
603
604
605
606
607
608
609
610
611
612
613
614
615
616
617
618
619
620
621
622
623
624
625
626
627
628
629
630
631
632
633
634
635
636
637
638
639
640
641
642
643
644
645
646
647
648
649
650
651
652
653
654
655
656
657
658
659
660
661
662
663
664
665
666
667
668
669
670
671
672
673
674
675
676
677
678
679
680
681
682
683
684
685
686
687
688
689
690
691
692
693
694
695
696
697
698
699
700
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722
723
724
725
726
727
728
729
730
731
732
733
734
735
736
737
738
739
740
741
742
743
744
745
746
747
748
749
750
751
752
753
754
755
756
757
758
759
760
761
762
763
764
765
766
767
768
769
770
771
772
773
774
775
776
777
778
779
780
781
782
783
784
785
786
787
788
789
790
791
792
793
794
795
796
797
798
799
800
801
802
803
804
805
"""Local conversation history database for a single agent."""
from __future__ import annotations
import json
from datetime import UTC, datetime, timedelta
import aiosqlite
from baal_agent.image_utils import extract_text_from_content
# Content-part kinds the chat API accepts, mapped to the key each one must
# carry. Anything else in a list makes the whole payload invalid, so a JSON
# array that isn't exclusively these blocks must stay the text it was stored as.
_CONTENT_PART_KEYS = {"text": "text", "image_url": "image_url"}
def _is_content_part(part: object) -> bool:
if not isinstance(part, dict):
return False
required = _CONTENT_PART_KEYS.get(part.get("type"))
return required is not None and required in part
def _is_content_parts(value: object) -> bool:
"""Whether a decoded JSON value is a multimodal content-part list.
Tool results are frequently JSON arrays (API listings, search hits). They
decode to a list too, so `isinstance(x, list)` alone is not enough to tell
them apart from content parts.
"""
return (
isinstance(value, list)
and len(value) > 0
and all(_is_content_part(part) for part in value)
)
class AgentDatabase:
"""Async SQLite wrapper for per-agent conversation history."""
def __init__(self, db_path: str = "agent.db") -> None:
self.db_path = db_path
self._db: aiosqlite.Connection | None = None
async def initialize(self) -> None:
self._db = await aiosqlite.connect(self.db_path)
self._db.row_factory = aiosqlite.Row
await self._db.execute("PRAGMA journal_mode=WAL")
await self._db.executescript("""
CREATE TABLE IF NOT EXISTS messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
chat_id TEXT NOT NULL,
role TEXT NOT NULL,
content TEXT,
tool_calls TEXT,
tool_call_id TEXT,
metadata TEXT,
compacted INTEGER NOT NULL DEFAULT 0,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_messages_chat
ON messages (chat_id, created_at);
CREATE TABLE IF NOT EXISTS pending_messages (
id INTEGER PRIMARY KEY AUTOINCREMENT,
chat_id TEXT NOT NULL,
content TEXT NOT NULL,
source TEXT NOT NULL DEFAULT 'system',
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS pending_cursors (
channel TEXT PRIMARY KEY,
last_id INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE IF NOT EXISTS telegram_contacts (
id INTEGER PRIMARY KEY AUTOINCREMENT,
telegram_id TEXT NOT NULL UNIQUE,
chat_id TEXT NOT NULL,
chat_type TEXT NOT NULL DEFAULT 'private',
display_name TEXT NOT NULL DEFAULT '',
username TEXT,
status TEXT NOT NULL DEFAULT 'pending',
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE TABLE IF NOT EXISTS runtime_events (
id INTEGER PRIMARY KEY AUTOINCREMENT,
chat_id TEXT NOT NULL,
type TEXT NOT NULL,
payload TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_runtime_events_chat
ON runtime_events (chat_id, id);
CREATE TABLE IF NOT EXISTS memory_records (
id INTEGER PRIMARY KEY AUTOINCREMENT,
kind TEXT NOT NULL,
content TEXT NOT NULL,
source TEXT NOT NULL DEFAULT 'agent',
metadata TEXT,
archived INTEGER NOT NULL DEFAULT 0,
-- chat_id scopes a record to a single conversation. NULL
-- means "global / visible to every chat" (used by the
-- markdown-style import path and pre-scope records).
chat_id TEXT,
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now'))
);
CREATE INDEX IF NOT EXISTS idx_memory_records_kind
ON memory_records (kind, archived, updated_at);
CREATE INDEX IF NOT EXISTS idx_memory_records_chat
ON memory_records (chat_id, archived, updated_at);
""")
# FTS5 full-text search index over message content
await self._db.execute("""
CREATE VIRTUAL TABLE IF NOT EXISTS messages_fts USING fts5(
content,
content=messages,
content_rowid=id
)
""")
# Drop old triggers — multimodal content needs application-level FTS management
# (the trigger-based approach can't handle JSON content vs text-only FTS entries)
await self._db.execute("DROP TRIGGER IF EXISTS messages_fts_insert")
await self._db.execute("DROP TRIGGER IF EXISTS messages_fts_delete")
# Rebuild FTS index to catch any messages added before FTS was enabled
await self._db.execute(
"INSERT INTO messages_fts(messages_fts) VALUES('rebuild')"
)
# Migration: add compacted column to existing databases
cursor = await self._db.execute("PRAGMA table_info(messages)")
columns = {row[1] for row in await cursor.fetchall()}
if "compacted" not in columns:
await self._db.execute(
"ALTER TABLE messages ADD COLUMN compacted INTEGER NOT NULL DEFAULT 0"
)
if "metadata" not in columns:
await self._db.execute(
"ALTER TABLE messages ADD COLUMN metadata TEXT"
)
# Migration: add chat_id scope to typed memory records.
cursor = await self._db.execute("PRAGMA table_info(memory_records)")
memory_columns = {row[1] for row in await cursor.fetchall()}
if "chat_id" not in memory_columns:
await self._db.execute(
"ALTER TABLE memory_records ADD COLUMN chat_id TEXT"
)
await self._db.execute(
"CREATE INDEX IF NOT EXISTS idx_memory_records_chat "
"ON memory_records (chat_id, archived, updated_at)"
)
await self._db.commit()
async def close(self) -> None:
if self._db is not None:
await self._db.close()
self._db = None
@property
def db(self) -> aiosqlite.Connection:
if self._db is None:
raise RuntimeError("Database not initialized")
return self._db
def _parse_created_at(self, value: str | None) -> datetime:
"""Parse both current ISO timestamps and older SQLite datetime strings."""
if not value:
return datetime.now(UTC)
try:
dt = datetime.fromisoformat(value)
if dt.tzinfo is None:
dt = dt.replace(tzinfo=UTC)
return dt
except (ValueError, TypeError):
pass
try:
return datetime.strptime(value, "%Y-%m-%d %H:%M:%S").replace(
tzinfo=UTC
)
except (ValueError, TypeError):
return datetime.now(UTC)
async def _index_message_fts(
self, rowid: int | None, content: str | list[dict] | None
) -> None:
"""Insert a text-only representation of a message into FTS."""
if rowid is None:
return
text = extract_text_from_content(content)
if text:
await self.db.execute(
"INSERT INTO messages_fts(rowid, content) VALUES (?, ?)",
(rowid, text),
)
async def add_message(
self,
chat_id: str,
role: str,
content: str | list[dict] | None,
*,
tool_calls: list[dict] | None = None,
tool_call_id: str | None = None,
metadata: dict | None = None,
) -> None:
now = datetime.now(UTC).isoformat()
tc_json = json.dumps(tool_calls) if tool_calls else None
metadata_json = json.dumps(metadata) if metadata else None
# Serialize list content to JSON for storage
stored = json.dumps(content) if isinstance(content, list) else content
cursor = await self.db.execute(
"INSERT INTO messages (chat_id, role, content, tool_calls, tool_call_id, metadata, created_at) "
"VALUES (?, ?, ?, ?, ?, ?, ?)",
(chat_id, role, stored, tc_json, tool_call_id, metadata_json, now),
)
await self._index_message_fts(cursor.lastrowid, content)
await self.db.commit()
async def add_event(self, chat_id: str, event_type: str, payload: dict) -> int:
"""Persist a runtime event for trace/replay/debug views."""
cursor = await self.db.execute(
"INSERT INTO runtime_events (chat_id, type, payload, created_at) "
"VALUES (?, ?, ?, ?)",
(
chat_id,
event_type,
json.dumps(payload),
datetime.now(UTC).isoformat(),
),
)
await self.db.commit()
return int(cursor.lastrowid or 0)
async def add_memory_record(
self,
*,
kind: str,
content: str,
source: str = "agent",
metadata: dict | None = None,
chat_id: str | None = None,
) -> int:
"""Store a typed memory record.
``chat_id=None`` makes the record visible to every conversation
(legacy behaviour). Pass the current chat_id to scope it.
"""
now = datetime.now(UTC).isoformat()
cursor = await self.db.execute(
"INSERT INTO memory_records "
"(kind, content, source, metadata, chat_id, created_at, updated_at) "
"VALUES (?, ?, ?, ?, ?, ?, ?)",
(
kind,
content,
source,
json.dumps(metadata) if metadata else None,
chat_id,
now,
now,
),
)
await self.db.commit()
return int(cursor.lastrowid or 0)
async def search_memory_records(
self,
query: str = "",
*,
kind: str | None = None,
limit: int = 20,
include_archived: bool = False,
chat_id: str | None = None,
) -> list[dict]:
"""Search typed memory records with simple SQLite LIKE matching.
Scope semantics:
- ``chat_id=None`` returns records from every chat (admin / debug
path).
- A non-empty ``chat_id`` returns rows that either belong to that
chat OR are global (``memory_records.chat_id IS NULL``).
"""
clauses = []
params: list[object] = []
if not include_archived:
clauses.append("archived = 0")
if kind:
clauses.append("kind = ?")
params.append(kind)
if query:
clauses.append("content LIKE ?")
params.append(f"%{query}%")
if chat_id is not None:
clauses.append("(chat_id IS NULL OR chat_id = ?)")
params.append(chat_id)
where = f"WHERE {' AND '.join(clauses)}" if clauses else ""
params.append(min(max(int(limit), 1), 100))
cursor = await self.db.execute(
"SELECT id, kind, content, source, metadata, archived, chat_id, "
"created_at, updated_at "
f"FROM memory_records {where} "
"ORDER BY updated_at DESC, id DESC LIMIT ?",
params,
)
rows = await cursor.fetchall()
return [self._memory_row_to_dict(row) for row in rows]
async def archive_memory_record(self, record_id: int) -> bool:
now = datetime.now(UTC).isoformat()
cursor = await self.db.execute(
"UPDATE memory_records SET archived = 1, updated_at = ? WHERE id = ?",
(now, record_id),
)
await self.db.commit()
return bool(cursor.rowcount)
async def delete_memory_record(self, record_id: int) -> bool:
"""Permanently remove a typed memory record."""
cursor = await self.db.execute(
"DELETE FROM memory_records WHERE id = ?", (record_id,)
)
await self.db.commit()
return bool(cursor.rowcount)
async def prune_runtime_events(self, retention_days: int) -> int:
"""Drop runtime_events older than `retention_days`. Returns deleted count.
A non-positive retention disables pruning.
"""
if retention_days <= 0:
return 0
cutoff = (
datetime.now(UTC) - timedelta(days=retention_days)
).isoformat()
cursor = await self.db.execute(
"DELETE FROM runtime_events WHERE created_at < ?", (cutoff,)
)
await self.db.commit()
return int(cursor.rowcount or 0)
def _memory_row_to_dict(self, row) -> dict:
metadata = None
if row["metadata"]:
try:
metadata = json.loads(row["metadata"])
except (json.JSONDecodeError, TypeError):
metadata = None
# `chat_id` is missing on rows from older schemas before the
# migration ran in the same session; defensive get protects tests
# that construct rows manually.
try:
row_chat_id = row["chat_id"]
except (IndexError, KeyError):
row_chat_id = None
return {
"id": row["id"],
"kind": row["kind"],
"content": row["content"],
"source": row["source"],
"metadata": metadata,
"archived": bool(row["archived"]),
"chat_id": row_chat_id,
"created_at": row["created_at"],
"updated_at": row["updated_at"],
}
async def get_events(
self,
chat_id: str,
limit: int = 200,
after_id: int | None = None,
) -> list[dict]:
"""Return persisted runtime events in chronological order."""
if after_id is not None:
cursor = await self.db.execute(
"SELECT id, type, payload, created_at "
"FROM runtime_events WHERE chat_id = ? AND id > ? "
"ORDER BY id ASC LIMIT ?",
(chat_id, after_id, limit),
)
else:
cursor = await self.db.execute(
"SELECT id, type, payload, created_at "
"FROM runtime_events WHERE chat_id = ? "
"ORDER BY id DESC LIMIT ?",
(chat_id, limit),
)
rows = await cursor.fetchall()
if after_id is None:
rows = list(reversed(rows))
events = []
for row in rows:
try:
payload = json.loads(row["payload"])
except (json.JSONDecodeError, TypeError):
payload = {}
events.append({
"id": row["id"],
"type": row["type"],
"payload": payload,
"created_at": row["created_at"],
})
return events
async def get_history(
self,
chat_id: str,
limit: int = 50,
include_timestamps: bool = False,
include_metadata: bool = False,
) -> list[dict]:
"""Return conversation history. When `include_timestamps=True`, each
row also carries `created_at` (UTC 'YYYY-MM-DD HH:MM:SS'). Off by
default so we never leak DB-only fields into LLM messages (the OpenAI-
compatible API rejects unknown keys on some backends)."""
cols = "role, content, tool_calls, tool_call_id"
if include_timestamps:
cols += ", created_at"
if include_metadata:
cols += ", metadata"
cursor = await self.db.execute(
f"SELECT {cols} "
"FROM messages WHERE chat_id = ? AND compacted = 0 "
"ORDER BY created_at DESC LIMIT ?",
(chat_id, limit),
)
rows = await cursor.fetchall()
messages = []
for r in reversed(rows):
msg: dict = {"role": r["role"]}
if r["content"] is not None:
content = r["content"]
# Deserialize multimodal content stored as JSON list
try:
parsed = json.loads(content)
if _is_content_parts(parsed):
content = parsed
except (json.JSONDecodeError, TypeError):
pass
msg["content"] = content
if r["tool_calls"]:
msg["tool_calls"] = json.loads(r["tool_calls"])
if r["tool_call_id"]:
msg["tool_call_id"] = r["tool_call_id"]
if include_timestamps and r["created_at"]:
msg["created_at"] = r["created_at"]
if include_metadata and r["metadata"]:
try:
msg["metadata"] = json.loads(r["metadata"])
except (json.JSONDecodeError, TypeError):
pass
messages.append(msg)
return messages
async def compact_history(
self, chat_id: str, keep_recent: int, summary: str
) -> None:
"""Mark old messages as compacted and insert a summary pair.
Original messages are preserved for full-text search but excluded
from get_history() so they don't consume the context window.
1. Find the cutoff timestamp (the keep_recent-th most recent active message)
2. Mark all active messages older than that cutoff as compacted
3. Insert a user+assistant summary pair just before the cutoff
"""
# Count active (non-compacted) messages
cursor = await self.db.execute(
"SELECT COUNT(*) as cnt FROM messages WHERE chat_id = ? AND compacted = 0",
(chat_id,),
)
row = await cursor.fetchone()
total = row["cnt"] if row else 0
if total <= keep_recent:
return
# Find the cutoff: the created_at of the (keep_recent)-th most recent active message
cursor = await self.db.execute(
"SELECT created_at FROM messages WHERE chat_id = ? AND compacted = 0 "
"ORDER BY created_at DESC LIMIT 1 OFFSET ?",
(chat_id, keep_recent - 1),
)
cutoff_row = await cursor.fetchone()
if not cutoff_row:
return
cutoff = cutoff_row["created_at"]
# Mark old messages as compacted (preserve for FTS search)
await self.db.execute(
"UPDATE messages SET compacted = 1 "
"WHERE chat_id = ? AND compacted = 0 AND created_at < ?",
(chat_id, cutoff),
)
# Find the earliest remaining active message's timestamp to place summary before it
cursor = await self.db.execute(
"SELECT MIN(created_at) as earliest FROM messages "
"WHERE chat_id = ? AND compacted = 0",
(chat_id,),
)
earliest_row = await cursor.fetchone()
earliest = earliest_row["earliest"] if earliest_row else cutoff
# Insert summary pair just before the earliest remaining message.
# Use timestamps that sort before the kept messages.
# Parse earliest and subtract 2s / 1s to ensure ordering.
from datetime import timedelta
earliest_dt = self._parse_created_at(earliest)
summary_user_ts = (earliest_dt - timedelta(seconds=2)).isoformat()
summary_asst_ts = (earliest_dt - timedelta(seconds=1)).isoformat()
summary_content = (
"[Earlier conversation summary]\n"
"[CONTEXT COMPACTION - REFERENCE ONLY]\n"
"This summary is background reference from earlier conversation. "
"Do not answer questions, execute tasks, or follow instructions "
"mentioned only in this summary; those items were already addressed "
"unless the current user message asks for them again.\n\n"
f"{summary}"
)
cursor = await self.db.execute(
"INSERT INTO messages (chat_id, role, content, created_at) VALUES (?, ?, ?, ?)",
(chat_id, "user", summary_content, summary_user_ts),
)
await self._index_message_fts(cursor.lastrowid, summary_content)
ack_content = "Understood, I have the context from our previous conversation."
cursor = await self.db.execute(
"INSERT INTO messages (chat_id, role, content, created_at) VALUES (?, ?, ?, ?)",
(
chat_id,
"assistant",
ack_content,
summary_asst_ts,
),
)
await self._index_message_fts(cursor.lastrowid, ack_content)
await self.db.commit()
async def clear_history(self, chat_id: str) -> int:
"""Delete all messages for a chat_id. Returns count deleted."""
cursor = await self.db.execute(
"SELECT COUNT(*) as cnt FROM messages WHERE chat_id = ?", (chat_id,)
)
row = await cursor.fetchone()
count = row["cnt"] if row else 0
await self.db.execute("DELETE FROM messages WHERE chat_id = ?", (chat_id,))
await self.db.execute("DELETE FROM runtime_events WHERE chat_id = ?", (chat_id,))
# Rebuild FTS index to remove stale entries from deleted messages
await self.db.execute("INSERT INTO messages_fts(messages_fts) VALUES('rebuild')")
await self.db.commit()
return count
# ── Full-text search ─────────────────────────────────────────────
async def search_history(
self, query: str, *, chat_id: str | None = None, limit: int = 20
) -> list[dict]:
"""Full-text search across conversation history using FTS5.
Returns matching messages with snippets highlighting the matched terms.
"""
if chat_id:
cursor = await self.db.execute(
"SELECT m.chat_id, m.role, m.created_at, "
" snippet(messages_fts, 0, '>>>', '<<<', '...', 64) as snippet "
"FROM messages_fts "
"JOIN messages m ON m.id = messages_fts.rowid "
"WHERE messages_fts MATCH ? AND m.chat_id = ? "
"ORDER BY rank LIMIT ?",
(query, chat_id, limit),
)
else:
cursor = await self.db.execute(
"SELECT m.chat_id, m.role, m.created_at, "
" snippet(messages_fts, 0, '>>>', '<<<', '...', 64) as snippet "
"FROM messages_fts "
"JOIN messages m ON m.id = messages_fts.rowid "
"WHERE messages_fts MATCH ? "
"ORDER BY rank LIMIT ?",
(query, limit),
)
rows = await cursor.fetchall()
return [
{
"chat_id": r["chat_id"],
"role": r["role"],
"created_at": r["created_at"],
"snippet": r["snippet"],
}
for r in rows
]
# ── Pending messages ──────────────────────────────────────────────
async def add_pending(
self, chat_id: str, content: str, source: str = "system"
) -> None:
"""Insert a pending proactive message."""
now = datetime.now(UTC).isoformat()
await self.db.execute(
"INSERT INTO pending_messages (chat_id, content, source, created_at) "
"VALUES (?, ?, ?, ?)",
(chat_id, content, source, now),
)
await self.db.commit()
async def get_and_clear_pending(
self, chat_id: str | None = None
) -> list[dict]:
"""Fetch pending messages (optionally for a chat_id), delete them, return the list."""
if chat_id:
cursor = await self.db.execute(
"SELECT id, chat_id, content, source, created_at "
"FROM pending_messages WHERE chat_id = ? ORDER BY created_at",
(chat_id,),
)
else:
cursor = await self.db.execute(
"SELECT id, chat_id, content, source, created_at "
"FROM pending_messages ORDER BY created_at"
)
rows = await cursor.fetchall()
messages = [
{
"chat_id": r["chat_id"],
"content": r["content"],
"source": r["source"],
"created_at": r["created_at"],
}
for r in rows
]
if rows:
ids = [r["id"] for r in rows]
placeholders = ",".join("?" for _ in ids)
await self.db.execute(
f"DELETE FROM pending_messages WHERE id IN ({placeholders})", ids
)
await self.db.commit()
return messages
async def count_pending(
self, chat_id: str | None = None, channel: str | None = None
) -> int:
"""Count pending messages WITHOUT clearing them (non-destructive peek).
With ``channel``, only rows that channel has not acked are counted —
otherwise cursor-based consumers (which never delete rows) would show
a stale unread count until the 7-day GC runs.
"""
last_id = 0
if channel:
cursor = await self.db.execute(
"SELECT last_id FROM pending_cursors WHERE channel = ?", (channel,)
)
row = await cursor.fetchone()
last_id = int(row["last_id"]) if row else 0
if chat_id:
cursor = await self.db.execute(
"SELECT COUNT(*) AS c FROM pending_messages "
"WHERE id > ? AND chat_id = ?",
(last_id, chat_id),
)
else:
cursor = await self.db.execute(
"SELECT COUNT(*) AS c FROM pending_messages WHERE id > ?",
(last_id,),
)
row = await cursor.fetchone()
return int(row["c"]) if row else 0
async def get_pending_for_channel(
self, channel: str, chat_id: str | None = None
) -> list[dict]:
"""Non-destructive fetch of pending rows this channel has not acked."""
cursor = await self.db.execute(
"SELECT last_id FROM pending_cursors WHERE channel = ?", (channel,)
)
row = await cursor.fetchone()
last_id = int(row["last_id"]) if row else 0
if chat_id:
cursor = await self.db.execute(
"SELECT id, chat_id, content, source, created_at "
"FROM pending_messages WHERE id > ? AND chat_id = ? ORDER BY id",
(last_id, chat_id),
)
else:
cursor = await self.db.execute(
"SELECT id, chat_id, content, source, created_at "
"FROM pending_messages WHERE id > ? ORDER BY id",
(last_id,),
)
rows = await cursor.fetchall()
return [
{
"id": r["id"],
"chat_id": r["chat_id"],
"content": r["content"],
"source": r["source"],
"created_at": r["created_at"],
}
for r in rows
]
async def ack_pending(self, channel: str, up_to_id: int) -> int:
"""Advance a channel cursor (monotonic) and GC rows older than 7 days."""
await self.db.execute(
"INSERT INTO pending_cursors (channel, last_id) VALUES (?, ?) "
"ON CONFLICT(channel) DO UPDATE SET last_id = excluded.last_id "
"WHERE excluded.last_id > pending_cursors.last_id",
(channel, up_to_id),
)
await self.db.execute(
"DELETE FROM pending_messages "
"WHERE created_at < datetime('now', '-7 days')"
)
await self.db.commit()
cursor = await self.db.execute(
"SELECT last_id FROM pending_cursors WHERE channel = ?", (channel,)
)
row = await cursor.fetchone()
return int(row["last_id"]) if row else up_to_id
# ── Telegram contacts ─────────────────────────────────────────────
def _contact_row_to_dict(self, row) -> dict:
return {
"telegram_id": row["telegram_id"],
"chat_id": row["chat_id"],
"chat_type": row["chat_type"],
"display_name": row["display_name"],
"username": row["username"],
"status": row["status"],
"created_at": row["created_at"],
"updated_at": row["updated_at"],
}
async def get_telegram_contact(self, telegram_id: str) -> dict | None:
cursor = await self.db.execute(
"SELECT * FROM telegram_contacts WHERE telegram_id = ?",
(telegram_id,),
)
row = await cursor.fetchone()
return self._contact_row_to_dict(row) if row else None
async def upsert_telegram_contact(
self,
telegram_id: str,
chat_id: str,
chat_type: str,
display_name: str,
username: str | None,
status: str = "pending",
) -> dict:
now = datetime.now(UTC).isoformat()
await self.db.execute(
"INSERT INTO telegram_contacts "
"(telegram_id, chat_id, chat_type, display_name, username, status, created_at, updated_at) "
"VALUES (?, ?, ?, ?, ?, ?, ?, ?) "
"ON CONFLICT(telegram_id) DO UPDATE SET "
"chat_id=excluded.chat_id, chat_type=excluded.chat_type, "
"display_name=excluded.display_name, username=excluded.username, "
"status=excluded.status, updated_at=excluded.updated_at",
(telegram_id, chat_id, chat_type, display_name, username, status, now, now),
)
await self.db.commit()
return (await self.get_telegram_contact(telegram_id)) # type: ignore[return-value]
async def list_telegram_contacts(self, status: str | None = None) -> list[dict]:
if status:
cursor = await self.db.execute(
"SELECT * FROM telegram_contacts WHERE status = ? ORDER BY created_at",
(status,),
)
else:
cursor = await self.db.execute(
"SELECT * FROM telegram_contacts ORDER BY created_at"
)
rows = await cursor.fetchall()
return [self._contact_row_to_dict(r) for r in rows]
async def update_telegram_contact_status(
self, telegram_id: str, status: str
) -> bool:
now = datetime.now(UTC).isoformat()
cursor = await self.db.execute(
"UPDATE telegram_contacts SET status = ?, updated_at = ? WHERE telegram_id = ?",
(status, now, telegram_id),
)
await self.db.commit()
return cursor.rowcount > 0
async def delete_telegram_contact(self, telegram_id: str) -> bool:
cursor = await self.db.execute(
"DELETE FROM telegram_contacts WHERE telegram_id = ?",
(telegram_id,),
)
await self.db.commit()
return cursor.rowcount > 0