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

1"""SQLite parser inbox, request budget, diagnostics and atomic decision outboxes.""" 

2 

3import datetime as dt 

4import hashlib 

5import json 

6import sqlite3 

7from contextlib import closing 

8from pathlib import Path 

9from uuid import uuid4 

10 

11from pydantic import TypeAdapter 

12 

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 

28 

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) 

83 

84 

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) 

98 

99# How many of a channel's latest posts the reader's history is replayed from. 

100HISTORY_POSTS = 300 

101 

102_INSTRUCTIONS = TypeAdapter(tuple[Instruction, ...]) 

103 

104 

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 ) 

118 

119 

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) 

139 

140 

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") 

144 

145 

146class SQLiteExtractionStore: 

147 def __init__(self, unit: SQLiteUnit) -> None: 

148 self._unit = unit 

149 

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) 

165 

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 

172 

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 ) 

214 

215 await self._unit.run(save, write=True) 

216 

217 async def next(self, now: dt.datetime) -> ExtractionJob | None: 

218 _aware(now) 

219 

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 ) 

238 

239 return await self._unit.run(read) 

240 

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") 

245 

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 

270 

271 return await self._unit.run(save, write=True) 

272 

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} 

283 

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 ) 

318 

319 return await self._unit.run(save, write=True) 

320 

321 async def reserve_destination_attempt(self, key: str, account_id: str) -> DestinationIdentity: 

322 """Persist one destination retry admission before calling its account owner.""" 

323 

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) 

344 

345 return await self._unit.run(save, write=True) 

346 

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() 

351 

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 ) 

392 

393 await self._unit.run(save, write=True) 

394 

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.""" 

397 

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) 

408 

409 return await self._unit.run(read) 

410 

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 ) 

428 

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") 

434 

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 ) 

448 

449 return await self._unit.run(read) 

450 

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 ) 

457 

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) 

468 

469 return await self._unit.run(read) 

470 

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 ) 

480 

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") 

484 

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 ) 

505 

506 return await self._unit.run(read) 

507 

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 ) 

517 

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 ) 

524 

525 async def close(self) -> None: 

526 await self._unit.close()