Coverage for src/copytrading_engine/parsing/sqlite.py: 85%
186 statements
« prev ^ index » next coverage.py v7.16.2, created at 2026-11-16 16:55 +0000
« prev ^ index » next coverage.py v7.16.2, created at 2026-11-16 16:55 +0000
1"""SQLite parser inbox, request budget, diagnostics and atomic decision outboxes."""
3import datetime as dt
4import hashlib
5import json
6import sqlite3
7from contextlib import closing
8from pathlib import Path
9from uuid import uuid4
11from pydantic import TypeAdapter
13from copytrading_engine.parsing.contracts import (
14 DestinationIdentity,
15 DestinationRegistration,
16 ExtractionJob,
17 PendingSignal,
18 RequestReservation,
19)
20from copytrading_engine.parsing.diagnostics import ValidationIssue
21from copytrading_engine.parsing.history import PastCall
22from copytrading_engine.parsing.notifications import decision_notification
23from copytrading_engine.shared.notification_models import NotificationIntent, NotificationPayload
24from copytrading_engine.shared.queue_snapshot import QueueSnapshot
25from copytrading_engine.shared.raw_message import RawMessage
26from copytrading_engine.shared.signals import Instruction, SourceIdentityConflict, StockSignal
27from copytrading_engine.shared.sqlite import SchemaComponent, SQLiteUnit, ensure_schema
29PARSER_SCHEMA = SchemaComponent(
30 "parser",
31 2,
32 """
33CREATE TABLE IF NOT EXISTS parser_inbox (
34 seq INTEGER PRIMARY KEY AUTOINCREMENT,
35 id TEXT NOT NULL UNIQUE,
36 source TEXT NOT NULL,
37 channel_id TEXT NOT NULL,
38 hash TEXT NOT NULL,
39 payload TEXT NOT NULL,
40 attempts INTEGER NOT NULL DEFAULT 0,
41 retry_at REAL NOT NULL DEFAULT 0,
42 result TEXT,
43 enqueued_at TEXT NOT NULL,
44 completed_at TEXT
45);
46CREATE TABLE IF NOT EXISTS parser_workflows (
47 message_id TEXT PRIMARY KEY REFERENCES parser_inbox(id),
48 workflow_id TEXT NOT NULL UNIQUE,
49 trace_id TEXT NOT NULL UNIQUE,
50 created_at TEXT NOT NULL
51);
52CREATE TABLE IF NOT EXISTS parser_destinations (
53 message_id TEXT NOT NULL REFERENCES parser_inbox(id),
54 account_id TEXT NOT NULL,
55 destination_id TEXT NOT NULL UNIQUE,
56 configuration_revision TEXT NOT NULL,
57 attempts INTEGER NOT NULL DEFAULT 0 CHECK(attempts >= 0),
58 PRIMARY KEY(message_id, account_id)
59);
60CREATE INDEX IF NOT EXISTS parser_inbox_waiting ON parser_inbox(seq) WHERE result IS NULL;
61CREATE TABLE IF NOT EXISTS parser_requests (day TEXT PRIMARY KEY, count INTEGER NOT NULL);
62CREATE TABLE IF NOT EXISTS parser_diagnostics (
63 message_id TEXT NOT NULL REFERENCES parser_inbox(id),
64 attempt INTEGER NOT NULL,
65 at TEXT NOT NULL,
66 reason TEXT NOT NULL,
67 issues TEXT NOT NULL,
68 PRIMARY KEY(message_id,attempt)
69);
70CREATE TABLE IF NOT EXISTS parser_signal_deliveries (
71 message_id TEXT PRIMARY KEY REFERENCES parser_inbox(id),
72 result TEXT NOT NULL,
73 delivered_at TEXT
74);
75CREATE TABLE IF NOT EXISTS parser_notifications (
76 message_id TEXT PRIMARY KEY REFERENCES parser_inbox(id),
77 stream_id TEXT NOT NULL,
78 payload TEXT NOT NULL,
79 delivered_at TEXT
80);
81""",
82)
85# The owner's corrections of what a post said, which the reader's history prefers to its own
86# reading (ADR-0010). Its own component, so adding it leaves the parser's revision as it was.
87PARSER_CORRECTIONS_SCHEMA = SchemaComponent(
88 "parser_corrections",
89 1,
90 """
91CREATE TABLE IF NOT EXISTS parser_corrections (
92 message_id TEXT PRIMARY KEY REFERENCES parser_inbox(id),
93 instructions TEXT NOT NULL,
94 recorded_at TEXT NOT NULL
95);
96""",
97)
99# How many of a channel's latest posts the reader's history is replayed from.
100HISTORY_POSTS = 300
102_INSTRUCTIONS = TypeAdapter(tuple[Instruction, ...])
105def record_owner_correction(
106 database: Path, source_id: str, instructions: tuple[Instruction, ...], at: dt.datetime
107) -> None:
108 """Keep the owner's latest reading of a post, which the guru's history then uses."""
109 _aware(at)
110 with closing(sqlite3.connect(database, timeout=5, isolation_level=None)) as db:
111 ensure_schema(db, PARSER_CORRECTIONS_SCHEMA)
112 db.execute(
113 "INSERT INTO parser_corrections(message_id,instructions,recorded_at) VALUES (?,?,?) "
114 "ON CONFLICT(message_id) DO UPDATE SET "
115 "instructions=excluded.instructions,recorded_at=excluded.recorded_at",
116 (source_id, _INSTRUCTIONS.dump_json(instructions).decode(), at.isoformat()),
117 )
120def _past_calls(
121 rows: list[tuple[str, str, str | None]],
122) -> tuple[PastCall, ...]:
123 """The calls a channel's posts made: the owner's correction where there is one, otherwise
124 each post the engine traded."""
125 calls: list[PastCall] = []
126 for key, result, corrected in rows:
127 signal = StockSignal.model_validate_json(result)
128 if corrected is not None:
129 instructions = _INSTRUCTIONS.validate_json(corrected)
130 elif signal.decision == "trade":
131 instructions = signal.instructions
132 else:
133 continue
134 calls.extend(
135 PastCall(source_key=key, index=index, at=signal.timestamp, instruction=instruction)
136 for index, instruction in enumerate(instructions)
137 )
138 return tuple(calls)
141def _aware(now: dt.datetime) -> None:
142 if now.tzinfo is None or now.utcoffset() is None: 142 ↛ 143line 142 didn't jump to line 143 because the condition on line 142 was never true
143 raise ValueError("Parser time must be timezone-aware")
146class SQLiteExtractionStore:
147 def __init__(self, unit: SQLiteUnit) -> None:
148 self._unit = unit
150 @classmethod
151 async def open(cls, path: Path) -> SQLiteExtractionStore:
152 unit = await SQLiteUnit.open(path)
153 try:
154 await unit.run(
155 lambda db: (
156 ensure_schema(db, PARSER_SCHEMA),
157 ensure_schema(db, PARSER_CORRECTIONS_SCHEMA),
158 ),
159 write=True,
160 )
161 except BaseException:
162 await unit.close()
163 raise
164 return cls(unit)
166 async def add(self, event: RawMessage) -> None:
167 key = event.identity
168 payload = json.dumps(event.model_dump(mode="json"), sort_keys=True, ensure_ascii=False)
169 digest = hashlib.sha256(payload.encode()).hexdigest()
170 workflow_id = uuid4().hex
171 trace_id = uuid4().hex
173 def save(db: sqlite3.Connection) -> None:
174 row = db.execute("SELECT hash FROM parser_inbox WHERE id=?", (key,)).fetchone()
175 if row:
176 if row[0] != digest: 176 ↛ 178line 176 didn't jump to line 178 because the condition on line 176 was always true
177 raise SourceIdentityConflict("Source ID reused with a different payload")
178 return
179 source_lineage = None
180 source_store_exists = db.execute(
181 "SELECT 1 FROM sqlite_master WHERE type='table' AND name='source_captures'"
182 ).fetchone()
183 if source_store_exists:
184 source_lineage = db.execute(
185 "SELECT workflow_id,trace_id FROM source_captures WHERE id=?", (key,)
186 ).fetchone()
187 saved_workflow_id, saved_trace_id = (
188 (source_lineage[0], source_lineage[1])
189 if source_lineage is not None
190 else (workflow_id, trace_id)
191 )
192 db.execute(
193 "INSERT INTO parser_inbox(id,source,channel_id,hash,payload,enqueued_at) "
194 "VALUES (?,?,?,?,?,?)",
195 (
196 key,
197 event.source,
198 event.channel_id,
199 digest,
200 payload,
201 dt.datetime.now(dt.UTC).isoformat(),
202 ),
203 )
204 db.execute(
205 "INSERT INTO parser_workflows(message_id,workflow_id,trace_id,created_at) "
206 "VALUES (?,?,?,?)",
207 (
208 key,
209 saved_workflow_id,
210 saved_trace_id,
211 dt.datetime.now(dt.UTC).isoformat(),
212 ),
213 )
215 await self._unit.run(save, write=True)
217 async def next(self, now: dt.datetime) -> ExtractionJob | None:
218 _aware(now)
220 def read(db: sqlite3.Connection) -> ExtractionJob | None:
221 row = db.execute(
222 "SELECT current.id,current.payload,current.attempts,workflow.workflow_id,"
223 "workflow.trace_id FROM parser_inbox current JOIN parser_workflows workflow "
224 "ON workflow.message_id=current.id "
225 "WHERE current.result IS NULL AND current.retry_at<=? AND NOT EXISTS ("
226 "SELECT 1 FROM parser_inbox prior WHERE prior.result IS NULL "
227 "AND prior.seq<current.seq AND prior.source=current.source "
228 "AND prior.channel_id=current.channel_id) ORDER BY current.seq LIMIT 1",
229 (now.timestamp(),),
230 ).fetchone()
231 return (
232 None
233 if row is None
234 else ExtractionJob(
235 row[0], RawMessage.model_validate_json(row[1]), row[2], row[3], row[4]
236 )
237 )
239 return await self._unit.run(read)
241 async def reserve(self, request: RequestReservation) -> bool:
242 _aware(request.retry_at)
243 if request.daily_limit < 0 or request.expected_attempts < 0: 243 ↛ 244line 243 didn't jump to line 244 because the condition on line 243 was never true
244 raise ValueError("Invalid parser request reservation")
246 def save(db: sqlite3.Connection) -> bool:
247 row = db.execute(
248 "SELECT attempts,result FROM parser_inbox WHERE id=?", (request.key,)
249 ).fetchone()
250 if row is None: 250 ↛ 251line 250 didn't jump to line 251 because the condition on line 250 was never true
251 raise ValueError("Cannot reserve an unknown source message")
252 if row[0] != request.expected_attempts or row[1] is not None: 252 ↛ 253line 252 didn't jump to line 253 because the condition on line 252 was never true
253 raise ValueError("Cannot reserve with stale extraction attempts")
254 day = request.day.isoformat()
255 db.execute("INSERT OR IGNORE INTO parser_requests VALUES (?,0)", (day,))
256 count = db.execute("SELECT count FROM parser_requests WHERE day=?", (day,)).fetchone()[
257 0
258 ]
259 if count >= request.daily_limit:
260 return False
261 db.execute("UPDATE parser_requests SET count=count+1 WHERE day=?", (day,))
262 updated = db.execute(
263 "UPDATE parser_inbox SET attempts=attempts+1,retry_at=? "
264 "WHERE id=? AND attempts=? AND result IS NULL",
265 (request.retry_at.timestamp(), request.key, request.expected_attempts),
266 )
267 if updated.rowcount != 1: 267 ↛ 268line 267 didn't jump to line 268 because the condition on line 267 was never true
268 raise ValueError("Cannot reserve with stale extraction attempts")
269 return True
271 return await self._unit.run(save, write=True)
273 async def prepare_destinations(
274 self,
275 key: str,
276 registrations: tuple[DestinationRegistration, ...],
277 ) -> tuple[DestinationIdentity, ...]:
278 """Commit stable route identities for every account before any account receipt."""
279 accounts = tuple(sorted(registrations, key=lambda item: item.account_id))
280 if len({item.account_id for item in accounts}) != len(accounts): 280 ↛ 281line 280 didn't jump to line 281 because the condition on line 280 was never true
281 raise ValueError("A workflow cannot route to the same account twice")
282 generated = {item.account_id: uuid4().hex for item in accounts}
284 def save(db: sqlite3.Connection) -> tuple[DestinationIdentity, ...]:
285 workflow = db.execute(
286 "SELECT workflow_id,trace_id FROM parser_workflows WHERE message_id=?", (key,)
287 ).fetchone()
288 if workflow is None: 288 ↛ 289line 288 didn't jump to line 289 because the condition on line 288 was never true
289 raise ValueError("Cannot route a workflow without persisted lineage")
290 existing = db.execute(
291 "SELECT account_id,configuration_revision FROM parser_destinations "
292 "WHERE message_id=? ORDER BY account_id",
293 (key,),
294 ).fetchall()
295 expected = [(item.account_id, item.configuration_revision) for item in accounts]
296 if existing and existing != expected:
297 raise SourceIdentityConflict(
298 "Persisted workflow destinations differ from active route"
299 )
300 for item in accounts:
301 db.execute(
302 "INSERT OR IGNORE INTO parser_destinations"
303 "(message_id,account_id,destination_id,configuration_revision) "
304 "VALUES (?,?,?,?)",
305 (key, item.account_id, generated[item.account_id], item.configuration_revision),
306 )
307 rows = db.execute(
308 "SELECT message_id,account_id,destination_id,configuration_revision,attempts "
309 "FROM parser_destinations WHERE message_id=? ORDER BY account_id",
310 (key,),
311 ).fetchall()
312 return tuple(
313 DestinationIdentity(
314 row[0], row[1], row[2], row[3], workflow[0], workflow[1], row[4]
315 )
316 for row in rows
317 )
319 return await self._unit.run(save, write=True)
321 async def reserve_destination_attempt(self, key: str, account_id: str) -> DestinationIdentity:
322 """Persist one destination retry admission before calling its account owner."""
324 def save(db: sqlite3.Connection) -> DestinationIdentity:
325 row = db.execute(
326 "SELECT destination.message_id,destination.account_id,destination.destination_id,"
327 "destination.configuration_revision,destination.attempts,workflow.workflow_id,"
328 "workflow.trace_id FROM parser_destinations destination "
329 "JOIN parser_workflows workflow "
330 "ON workflow.message_id=destination.message_id "
331 "WHERE destination.message_id=? AND destination.account_id=?",
332 (key, account_id),
333 ).fetchone()
334 if row is None: 334 ↛ 335line 334 didn't jump to line 335 because the condition on line 334 was never true
335 raise ValueError("Cannot admit an unregistered destination")
336 changed = db.execute(
337 "UPDATE parser_destinations SET attempts=attempts+1 "
338 "WHERE message_id=? AND account_id=? AND destination_id=?",
339 (key, account_id, row[2]),
340 )
341 if changed.rowcount != 1: 341 ↛ 342line 341 didn't jump to line 342 because the condition on line 341 was never true
342 raise RuntimeError("Destination attempt admission was lost")
343 return DestinationIdentity(row[0], row[1], row[2], row[3], row[5], row[6], row[4] + 1)
345 return await self._unit.run(save, write=True)
347 async def finish(self, key: str, result: StockSignal) -> None:
348 if key != f"{result.source}:{result.channel_id}:{result.id}": 348 ↛ 349line 348 didn't jump to line 349 because the condition on line 348 was never true
349 raise ValueError("Parser result identity differs from source")
350 payload = result.model_dump_json()
352 def save(db: sqlite3.Connection) -> None:
353 row = db.execute("SELECT result FROM parser_inbox WHERE id=?", (key,)).fetchone()
354 if row is None: 354 ↛ 355line 354 didn't jump to line 355 because the condition on line 354 was never true
355 raise ValueError("Cannot finish an unknown source message")
356 if row[0] is not None: 356 ↛ 357line 356 didn't jump to line 357 because the condition on line 356 was never true
357 if row[0] != payload:
358 raise SourceIdentityConflict("Parser result differs from persisted decision")
359 return
360 completed_at = dt.datetime.now(dt.UTC)
361 db.execute(
362 "UPDATE parser_inbox SET result=?,completed_at=? WHERE id=? AND result IS NULL",
363 (payload, completed_at.isoformat(), key),
364 )
365 db.execute(
366 "INSERT INTO parser_signal_deliveries(message_id,result) VALUES (?,?)",
367 (key, payload),
368 )
369 diagnostic = db.execute(
370 "SELECT issues FROM parser_diagnostics WHERE message_id=? "
371 "ORDER BY attempt DESC LIMIT 1",
372 (key,),
373 ).fetchone()
374 issues = (
375 tuple(ValidationIssue.model_validate(item) for item in json.loads(diagnostic[0]))
376 if diagnostic
377 else ()
378 )
379 notification = decision_notification(key, result, issues, observed_at=completed_at)
380 notification_payload = json.dumps(
381 {
382 "labels": dict(notification.payload.labels),
383 "annotations": dict(notification.payload.annotations),
384 "starts_at": notification.payload.starts_at.isoformat(),
385 },
386 ensure_ascii=False,
387 )
388 db.execute(
389 "INSERT INTO parser_notifications(message_id,stream_id,payload) VALUES (?,?,?)",
390 (key, notification.stream_id, notification_payload),
391 )
393 await self._unit.run(save, write=True)
395 async def past_calls(self, channel_id: str, before: str) -> tuple[PastCall, ...]:
396 """The calls the channel's guru made before the post `before`, newest posts first."""
398 def read(db: sqlite3.Connection) -> tuple[PastCall, ...]:
399 rows = db.execute(
400 "SELECT inbox.id,inbox.result,correction.instructions FROM parser_inbox inbox "
401 "LEFT JOIN parser_corrections correction ON correction.message_id=inbox.id "
402 "WHERE inbox.channel_id=? AND inbox.result IS NOT NULL AND inbox.id<>? "
403 "AND inbox.seq<(SELECT seq FROM parser_inbox WHERE id=?) "
404 "ORDER BY inbox.seq DESC LIMIT ?",
405 (channel_id, before, before, HISTORY_POSTS),
406 ).fetchall()
407 return _past_calls(rows)
409 return await self._unit.run(read)
411 async def diagnose(
412 self,
413 key: str,
414 attempt: int,
415 at: dt.datetime,
416 reason: str,
417 issues: tuple[ValidationIssue, ...],
418 ) -> None:
419 _aware(at)
420 payload = json.dumps([issue.model_dump() for issue in issues])
421 await self._unit.run(
422 lambda db: db.execute(
423 "INSERT OR IGNORE INTO parser_diagnostics VALUES (?,?,?,?,?)",
424 (key, attempt, at.isoformat(), reason, payload),
425 ),
426 write=True,
427 )
429 async def claim_pending_signals(
430 self, limit: int = 100, after_seq: int = 0
431 ) -> tuple[PendingSignal, ...]:
432 if limit < 1 or type(after_seq) is not int or after_seq < 0: 432 ↛ 433line 432 didn't jump to line 433 because the condition on line 432 was never true
433 raise ValueError("Delivery limit must be positive and cursor nonnegative")
435 def read(db: sqlite3.Connection) -> tuple[PendingSignal, ...]:
436 rows = db.execute(
437 "SELECT inbox.seq,delivery.message_id,delivery.result "
438 "FROM parser_signal_deliveries delivery "
439 "JOIN parser_inbox inbox ON inbox.id=delivery.message_id "
440 "WHERE delivery.delivered_at IS NULL AND inbox.seq>? "
441 "ORDER BY inbox.seq LIMIT ?",
442 (after_seq, limit),
443 ).fetchall()
444 return tuple(
445 PendingSignal(key, StockSignal.model_validate_json(payload), seq)
446 for seq, key, payload in rows
447 )
449 return await self._unit.run(read)
451 async def pending_delivery_count(self) -> int:
452 return await self._unit.run(
453 lambda db: db.execute(
454 "SELECT count(*) FROM parser_signal_deliveries WHERE delivered_at IS NULL"
455 ).fetchone()[0]
456 )
458 async def pending_snapshot(self) -> QueueSnapshot:
459 def read(db: sqlite3.Connection) -> QueueSnapshot:
460 count, oldest = db.execute(
461 "SELECT count(*),min(enqueued_at) FROM ("
462 "SELECT enqueued_at FROM parser_inbox WHERE result IS NULL "
463 "UNION ALL SELECT inbox.enqueued_at FROM parser_signal_deliveries delivery "
464 "JOIN parser_inbox inbox ON inbox.id=delivery.message_id "
465 "WHERE delivery.delivered_at IS NULL)"
466 ).fetchone()
467 return QueueSnapshot(count, dt.datetime.fromisoformat(oldest) if oldest else None, 0)
469 return await self._unit.run(read)
471 async def confirm_signal(self, key: str) -> None:
472 await self._unit.run(
473 lambda db: db.execute(
474 "UPDATE parser_signal_deliveries "
475 "SET delivered_at=COALESCE(delivered_at,?) WHERE message_id=?",
476 (dt.datetime.now(dt.UTC).isoformat(), key),
477 ),
478 write=True,
479 )
481 async def claim_pending_notifications(self, limit: int = 100) -> tuple[NotificationIntent, ...]:
482 if limit < 1: 482 ↛ 483line 482 didn't jump to line 483 because the condition on line 482 was never true
483 raise ValueError("Delivery limit must be positive")
485 def read(db: sqlite3.Connection) -> tuple[NotificationIntent, ...]:
486 rows = db.execute(
487 "SELECT notifications.message_id,notifications.stream_id,notifications.payload "
488 "FROM parser_notifications notifications JOIN parser_inbox inbox "
489 "ON inbox.id=notifications.message_id WHERE notifications.delivered_at IS NULL "
490 "ORDER BY inbox.seq LIMIT ?",
491 (limit,),
492 ).fetchall()
493 return tuple(
494 NotificationIntent(
495 key=key,
496 stream_id=stream_id,
497 payload=NotificationPayload(
498 labels=(data := json.loads(payload))["labels"],
499 annotations=data["annotations"],
500 starts_at=dt.datetime.fromisoformat(data["starts_at"]),
501 ),
502 )
503 for key, stream_id, payload in rows
504 )
506 return await self._unit.run(read)
508 async def confirm_notification(self, key: str) -> None:
509 await self._unit.run(
510 lambda db: db.execute(
511 "UPDATE parser_notifications "
512 "SET delivered_at=COALESCE(delivered_at,?) WHERE message_id=?",
513 (dt.datetime.now(dt.UTC).isoformat(), key),
514 ),
515 write=True,
516 )
518 async def pending_count(self) -> int:
519 return await self._unit.run(
520 lambda db: db.execute(
521 "SELECT count(*) FROM parser_inbox WHERE result IS NULL"
522 ).fetchone()[0]
523 )
525 async def close(self) -> None:
526 await self._unit.close()