Coverage for src/copytrading_engine/sources/sqlite.py: 91%

258 statements  

« prev     ^ index     » next       coverage.py v7.16.2, created at 2026-11-16 16:55 +0000

1"""Durable Discord capture, recovery cursor and live parser delivery in SQLite.""" 

2 

3from __future__ import annotations 

4 

5import asyncio 

6import datetime as dt 

7import hashlib 

8import json 

9import logging 

10import sqlite3 

11from collections.abc import Callable, Mapping, Sequence 

12from dataclasses import dataclass 

13from pathlib import Path 

14from typing import cast 

15from uuid import uuid4 

16 

17import httpx2 as httpx 

18from pydantic import JsonValue, TypeAdapter 

19 

20from copytrading_engine.shared.payload_capture import CaptureStatus, PayloadCapture 

21from copytrading_engine.shared.queue_snapshot import QueueSnapshot 

22from copytrading_engine.shared.raw_message import RawMessage 

23from copytrading_engine.shared.sqlite import SchemaComponent, SQLiteUnit, ensure_schema 

24from copytrading_engine.sources.application import ( 

25 AttachmentReference, 

26 AttachmentStatus, 

27 Delivery, 

28 SourceAttachmentEvidence, 

29) 

30from copytrading_engine.sources.attachments import ( 

31 AttachmentCaptureResult, 

32 capture_attachments, 

33 read_managed_content, 

34) 

35from copytrading_engine.sources.evidence import ( 

36 OMITTED_ATTACHMENT_ID, 

37 bounded_source_event, 

38 is_evidence_id, 

39 normalized_source_event, 

40) 

41from copytrading_engine.sources.evidence import bounded_attachments as bound_attachment_references 

42from copytrading_engine.sources.recovery import ( 

43 Bootstrap, 

44 RecoveryProgress, 

45 RecoveryStart, 

46 RejectedCapture, 

47 plain_json, 

48) 

49 

50log = logging.getLogger(__name__) 

51 

52 

53def _seen_again(source_id: str, why: str) -> bool: 

54 """A Discord message seen again with other content was edited after it was first seen. The 

55 first version is the one that counts, since it may already be copied; the later one is only 

56 logged, never copied again, and never stops reading the channel.""" 

57 log.warning("source_message_seen_again id=%s why=%s", source_id, why) 

58 return False 

59 

60 

61SOURCE_SCHEMA = SchemaComponent( 

62 "source", 

63 3, 

64 """ 

65CREATE TABLE IF NOT EXISTS source_captures ( 

66 seq INTEGER PRIMARY KEY AUTOINCREMENT, 

67 id TEXT NOT NULL UNIQUE, 

68 hash TEXT NOT NULL, 

69 payload TEXT NOT NULL, 

70 source_at TEXT NOT NULL, 

71 captured_at TEXT NOT NULL, 

72 recovered INTEGER NOT NULL, 

73 mode TEXT NOT NULL CHECK(mode IN ('live','historical')), 

74 workflow_id TEXT NOT NULL, 

75 trace_id TEXT NOT NULL, 

76 assigned_seq INTEGER UNIQUE, 

77 confirmed INTEGER NOT NULL DEFAULT 0 

78); 

79CREATE INDEX IF NOT EXISTS source_captures_waiting 

80 ON source_captures(assigned_seq) WHERE mode='live' AND confirmed=0; 

81CREATE TABLE IF NOT EXISTS source_rejected ( 

82 id TEXT PRIMARY KEY, 

83 hash TEXT NOT NULL, 

84 payload TEXT NOT NULL, 

85 reason TEXT NOT NULL, 

86 rejected_at TEXT NOT NULL 

87); 

88CREATE TABLE IF NOT EXISTS source_event_evidence ( 

89 source_id TEXT PRIMARY KEY, 

90 accepted INTEGER NOT NULL CHECK(accepted IN (0,1)), 

91 event_payload TEXT, 

92 capture_status TEXT NOT NULL CHECK(capture_status IN ('complete','oversize','missing')), 

93 payload_bytes INTEGER NOT NULL CHECK(payload_bytes >= 0), 

94 captured_at TEXT NOT NULL 

95); 

96CREATE TABLE IF NOT EXISTS source_attachment_evidence ( 

97 source_id TEXT NOT NULL, 

98 evidence_id TEXT NOT NULL UNIQUE, 

99 attachment_id TEXT NOT NULL, 

100 filename TEXT NOT NULL, 

101 content_type TEXT, 

102 declared_size INTEGER NOT NULL CHECK(declared_size >= 0), 

103 capture_status TEXT NOT NULL CHECK(capture_status IN ( 

104 'pending','available','missing','oversize','download_failed', 

105 'origin_rejected','timed_out','count_exceeded' 

106 )), 

107 byte_size INTEGER CHECK(byte_size IS NULL OR byte_size >= 0), 

108 sha256 TEXT, 

109 managed_path TEXT, 

110 omitted_count INTEGER NOT NULL DEFAULT 0 CHECK(omitted_count >= 0), 

111 PRIMARY KEY(source_id,attachment_id), 

112 FOREIGN KEY(source_id) REFERENCES source_captures(id) 

113); 

114CREATE INDEX IF NOT EXISTS source_attachment_evidence_source 

115 ON source_attachment_evidence(source_id,attachment_id); 

116CREATE TABLE IF NOT EXISTS source_cursors ( 

117 channel_id TEXT PRIMARY KEY, 

118 last_id INTEGER NOT NULL, 

119 bootstrap TEXT NOT NULL, 

120 updated_at TEXT NOT NULL 

121); 

122""", 

123) 

124 

125REJECTED_ATTACHMENT_SCHEMA = SchemaComponent( 

126 "source_rejected_attachment", 

127 1, 

128 """ 

129CREATE TABLE IF NOT EXISTS source_rejected_attachment_evidence ( 

130 source_id TEXT NOT NULL, 

131 evidence_id TEXT NOT NULL UNIQUE, 

132 attachment_id TEXT NOT NULL, 

133 filename TEXT NOT NULL, 

134 content_type TEXT, 

135 declared_size INTEGER NOT NULL CHECK(declared_size >= 0), 

136 capture_status TEXT NOT NULL CHECK(capture_status='skipped_rejected'), 

137 byte_size INTEGER CHECK(byte_size IS NULL OR byte_size >= 0), 

138 sha256 TEXT, 

139 managed_path TEXT, 

140 omitted_count INTEGER NOT NULL DEFAULT 0 CHECK(omitted_count >= 0), 

141 PRIMARY KEY(source_id,attachment_id), 

142 FOREIGN KEY(source_id) REFERENCES source_rejected(id) 

143); 

144CREATE INDEX IF NOT EXISTS source_rejected_attachment_evidence_source 

145 ON source_rejected_attachment_evidence(source_id,attachment_id); 

146""", 

147) 

148 

149 

150def _serialize(event: RawMessage) -> tuple[str, str]: 

151 payload = json.dumps(event.model_dump(mode="json"), sort_keys=True, ensure_ascii=False) 

152 return payload, hashlib.sha256(payload.encode()).hexdigest() 

153 

154 

155def _time() -> dt.datetime: 

156 return dt.datetime.now(dt.UTC) 

157 

158 

159def _insert_source_event_evidence( 

160 db: sqlite3.Connection, 

161 *, 

162 source_id: str, 

163 accepted: bool, 

164 event_payload: str | None, 

165 capture_status: str, 

166 payload_bytes: int, 

167 captured_at: str, 

168) -> None: 

169 db.execute( 

170 "INSERT INTO source_event_evidence " 

171 "(source_id,accepted,event_payload,capture_status,payload_bytes,captured_at) " 

172 "VALUES (?,?,?,?,?,?)", 

173 (source_id, int(accepted), event_payload, capture_status, payload_bytes, captured_at), 

174 ) 

175 

176 

177def _insert_attachment_evidence( 

178 db: sqlite3.Connection, 

179 *, 

180 source_id: str, 

181 references: Sequence[AttachmentReference], 

182 omitted_count: int, 

183) -> None: 

184 for reference in references: 

185 initial: AttachmentStatus = "pending" 

186 if not reference.url: 

187 initial = "missing" 

188 elif reference.declared_size > 1024 * 1024: 

189 initial = "oversize" 

190 db.execute( 

191 "INSERT INTO source_attachment_evidence " 

192 "(source_id,evidence_id,attachment_id,filename,content_type,declared_size," 

193 "capture_status) " 

194 "VALUES (?,?,?,?,?,?,?)", 

195 ( 

196 source_id, 

197 uuid4().hex, 

198 reference.attachment_id, 

199 reference.filename, 

200 reference.content_type, 

201 max(0, reference.declared_size), 

202 initial, 

203 ), 

204 ) 

205 if omitted_count: 

206 db.execute( 

207 "INSERT INTO source_attachment_evidence " 

208 "(source_id,evidence_id,attachment_id,filename,declared_size,capture_status," 

209 "omitted_count) " 

210 "VALUES (?,?,?, ?,0,'count_exceeded',?)", 

211 (source_id, uuid4().hex, OMITTED_ATTACHMENT_ID, "", omitted_count), 

212 ) 

213 

214 

215def _insert_rejected_attachment_evidence( 

216 db: sqlite3.Connection, 

217 *, 

218 source_id: str, 

219 references: Sequence[AttachmentReference], 

220 omitted_count: int, 

221) -> None: 

222 for reference in references: 

223 db.execute( 

224 "INSERT INTO source_rejected_attachment_evidence " 

225 "(source_id,evidence_id,attachment_id,filename,content_type,declared_size," 

226 "capture_status) VALUES (?,?,?,?,?,?,'skipped_rejected')", 

227 ( 

228 source_id, 

229 uuid4().hex, 

230 reference.attachment_id, 

231 reference.filename, 

232 reference.content_type, 

233 max(0, reference.declared_size), 

234 ), 

235 ) 

236 if omitted_count: 236 ↛ 237line 236 didn't jump to line 237 because the condition on line 236 was never true

237 db.execute( 

238 "INSERT INTO source_rejected_attachment_evidence " 

239 "(source_id,evidence_id,attachment_id,filename,declared_size,capture_status," 

240 "omitted_count) VALUES (?,?,?,'',0,'skipped_rejected',?)", 

241 (source_id, uuid4().hex, OMITTED_ATTACHMENT_ID, omitted_count), 

242 ) 

243 

244 

245@dataclass(frozen=True, slots=True) 

246class _Evidence: 

247 """A source event and its attachment references, bounded for storage.""" 

248 

249 original: JsonValue 

250 stored: str | None 

251 status: CaptureStatus 

252 size: int 

253 attachments: tuple[AttachmentReference, ...] 

254 omitted: int 

255 

256 @classmethod 

257 def bound(cls, original: JsonValue, attachments: Sequence[AttachmentReference]) -> _Evidence: 

258 stored, status, size = bounded_source_event(original) 

259 references, omitted = bound_attachment_references(attachments) 

260 return cls(original, stored, status, size, references, omitted) 

261 

262 

263def _rejection_key(payload: Mapping[str, object]) -> str: 

264 return f"{payload['source']}:{payload['channel_id']}:{payload['id']}" 

265 

266 

267def _check_recovery_page( 

268 channel: str, events: list[RawMessage], rejected: list[RejectedCapture], last_id: int 

269) -> None: 

270 """A page holds only this channel's messages, and the cursor lands on the newest one.""" 

271 observed: list[int] = [] 

272 for event in events: 

273 if ( 273 ↛ 279line 273 didn't jump to line 279 because the condition on line 273 was never true

274 event.source != "discord" 

275 or event.channel_id != channel 

276 or not event.id.isascii() 

277 or not event.id.isdecimal() 

278 ): 

279 raise ValueError("Recovery page contains a message from another source") 

280 observed.append(int(event.id)) 

281 for capture in rejected: 

282 payload = capture.payload 

283 identity = payload.get("id") 

284 if ( 284 ↛ 291line 284 didn't jump to line 291 because the condition on line 284 was never true

285 payload.get("source") != "discord" 

286 or payload.get("channel_id") != channel 

287 or not isinstance(identity, str) 

288 or not identity.isascii() 

289 or not identity.isdecimal() 

290 ): 

291 raise ValueError("Recovery page contains a rejection from another source") 

292 observed.append(int(identity)) 

293 if not observed or max(observed) != last_id: 293 ↛ 294line 293 didn't jump to line 294 because the condition on line 293 was never true

294 raise ValueError("Recovery cursor must equal the highest observed message ID") 

295 

296 

297def _insert_capture( 

298 db: sqlite3.Connection, 

299 event: RawMessage, 

300 evidence: _Evidence, 

301 *, 

302 captured_at: dt.datetime, 

303 recovered: bool, 

304 mode: str, 

305 workflow_id: str, 

306 trace_id: str, 

307) -> bool: 

308 """Insert a capture and its evidence; False when the same content is already captured.""" 

309 payload, digest = _serialize(event) 

310 if db.execute("SELECT 1 FROM source_rejected WHERE id=?", (event.identity,)).fetchone(): 310 ↛ 311line 310 didn't jump to line 311 because the condition on line 310 was never true

311 return _seen_again(event.identity, "first_rejected") 

312 old = db.execute("SELECT hash FROM source_captures WHERE id=?", (event.identity,)).fetchone() 

313 if old: 

314 return _seen_again(event.identity, "edited") if old[0] != digest else False 

315 db.execute( 

316 "INSERT INTO source_captures" 

317 "(id,hash,payload,source_at,captured_at,recovered,mode,workflow_id,trace_id) " 

318 "VALUES (?,?,?,?,?,?,?,?,?)", 

319 ( 

320 event.identity, 

321 digest, 

322 payload, 

323 event.timestamp.isoformat(), 

324 captured_at.isoformat(), 

325 int(recovered), 

326 mode, 

327 workflow_id, 

328 trace_id, 

329 ), 

330 ) 

331 _insert_source_event_evidence( 

332 db, 

333 source_id=event.identity, 

334 accepted=True, 

335 event_payload=evidence.stored, 

336 capture_status=evidence.status, 

337 payload_bytes=evidence.size, 

338 captured_at=captured_at.isoformat(), 

339 ) 

340 _insert_attachment_evidence( 

341 db, 

342 source_id=event.identity, 

343 references=evidence.attachments, 

344 omitted_count=evidence.omitted, 

345 ) 

346 return True 

347 

348 

349def _insert_rejection( 

350 db: sqlite3.Connection, 

351 event: dict[str, JsonValue], 

352 reason: str, 

353 evidence: _Evidence, 

354 *, 

355 rejected_at: dt.datetime, 

356) -> bool: 

357 """Insert a rejection and its evidence; False when the same input is already rejected.""" 

358 key = _rejection_key(event) 

359 payload = json.dumps(event, sort_keys=True, ensure_ascii=False) 

360 digest = hashlib.sha256(payload.encode()).hexdigest() 

361 if db.execute("SELECT 1 FROM source_captures WHERE id=?", (key,)).fetchone(): 361 ↛ 362line 361 didn't jump to line 362 because the condition on line 361 was never true

362 return _seen_again(key, "first_captured") 

363 old = db.execute("SELECT hash FROM source_rejected WHERE id=?", (key,)).fetchone() 

364 if old: 

365 return _seen_again(key, "edited") if old[0] != digest else False 

366 db.execute( 

367 "INSERT INTO source_rejected VALUES (?,?,?,?,?)", 

368 (key, digest, payload, reason, rejected_at.isoformat()), 

369 ) 

370 _insert_source_event_evidence( 

371 db, 

372 source_id=key, 

373 accepted=False, 

374 event_payload=evidence.stored, 

375 capture_status=evidence.status, 

376 payload_bytes=evidence.size, 

377 captured_at=rejected_at.isoformat(), 

378 ) 

379 _insert_rejected_attachment_evidence( 

380 db, source_id=key, references=evidence.attachments, omitted_count=evidence.omitted 

381 ) 

382 return True 

383 

384 

385# How far back a post read back after a restart or reconnect is still read (ADR-0010). 

386READ_BACK_WINDOW = dt.timedelta(hours=24) 

387 

388 

389class SQLiteSourceStore: 

390 def __init__( 

391 self, 

392 unit: SQLiteUnit, 

393 path: Path, 

394 diagnostics: PayloadCapture | None = None, 

395 attachment_transport: httpx.AsyncBaseTransport | None = None, 

396 on_captured: Callable[[], None] | None = None, 

397 ) -> None: 

398 self._unit = unit 

399 self._attachment_root = path.parent / "attachments" 

400 self._diagnostics = diagnostics 

401 self._attachment_transport = attachment_transport 

402 self._on_captured = on_captured 

403 

404 @classmethod 

405 async def open( 

406 cls, 

407 path: Path, 

408 *, 

409 diagnostics: PayloadCapture | None = None, 

410 attachment_transport: httpx.AsyncBaseTransport | None = None, 

411 on_captured: Callable[[], None] | None = None, 

412 ) -> SQLiteSourceStore: 

413 unit = await SQLiteUnit.open(path) 

414 try: 

415 await unit.run( 

416 lambda db: ensure_schema(db, SOURCE_SCHEMA), 

417 write=True, 

418 ) 

419 await unit.run( 

420 lambda db: ensure_schema(db, REJECTED_ATTACHMENT_SCHEMA), 

421 write=True, 

422 ) 

423 await unit.run( 

424 lambda db: db.execute( 

425 "UPDATE source_attachment_evidence SET capture_status='download_failed' " 

426 "WHERE capture_status='pending'" 

427 ), 

428 write=True, 

429 ) 

430 except BaseException: 

431 await unit.close() 

432 raise 

433 return cls(unit, path, diagnostics, attachment_transport, on_captured) 

434 

435 async def add( 

436 self, 

437 event: RawMessage, 

438 *, 

439 source_event: JsonValue | None = None, 

440 attachments: Sequence[AttachmentReference] = (), 

441 ) -> None: 

442 """Capture a live message once; a replay with the same content is a no-op.""" 

443 evidence = _Evidence.bound( 

444 source_event if source_event is not None else normalized_source_event(event), 

445 attachments, 

446 ) 

447 workflow_id, trace_id = uuid4().hex, uuid4().hex 

448 inserted = await self._unit.run( 

449 lambda db: _insert_capture( 

450 db, 

451 event, 

452 evidence, 

453 captured_at=_time(), 

454 recovered=False, 

455 mode="live", 

456 workflow_id=workflow_id, 

457 trace_id=trace_id, 

458 ), 

459 write=True, 

460 ) 

461 if inserted: 

462 if self._on_captured is not None: 

463 # Wake the processing loop now instead of on its next tick. 

464 self._on_captured() 

465 await self._emit_capture(event.identity, evidence, workflow_id, trace_id) 

466 

467 async def reject( 

468 self, 

469 event: dict[str, JsonValue], 

470 reason: str, 

471 *, 

472 source_event: JsonValue | None = None, 

473 attachments: Sequence[AttachmentReference] = (), 

474 ) -> None: 

475 """Retain a rejected input whole, with the reason it was not captured.""" 

476 evidence = _Evidence.bound(source_event if source_event is not None else event, attachments) 

477 inserted = await self._unit.run( 

478 lambda db: _insert_rejection(db, event, reason, evidence, rejected_at=_time()), 

479 write=True, 

480 ) 

481 if inserted: 

482 self._emit_rejection(evidence) 

483 

484 async def recovery_start(self, channel_id: int, fallback_id: int) -> RecoveryStart: 

485 channel = str(channel_id) 

486 

487 def start(db: sqlite3.Connection) -> RecoveryStart: 

488 row = db.execute( 

489 "SELECT last_id,bootstrap FROM source_cursors WHERE channel_id=?", (channel,) 

490 ).fetchone() 

491 if row: 

492 return RecoveryStart(int(row[0]), _stored_bootstrap(row[1])) 

493 rows = db.execute( 

494 "SELECT id FROM source_captures WHERE id LIKE ? UNION ALL " 

495 "SELECT id FROM source_rejected WHERE id LIKE ?", 

496 (f"discord:{channel}:%", f"discord:{channel}:%"), 

497 ).fetchall() 

498 ids = [ 

499 int(value.rsplit(":", 1)[1]) 

500 for (value,) in rows 

501 if value.rsplit(":", 1)[1].isascii() and value.rsplit(":", 1)[1].isdecimal() 

502 ] 

503 earliest = min(ids) if ids else None 

504 bootstrap: Bootstrap = "earliest_retained" if earliest is not None else "recent_only" 

505 last_id = earliest - 1 if earliest is not None else fallback_id 

506 db.execute( 

507 "INSERT INTO source_cursors VALUES (?,?,?,?)", 

508 (channel, last_id, bootstrap, _time().isoformat()), 

509 ) 

510 return RecoveryStart(last_id, bootstrap) 

511 

512 return await self._unit.run(start, write=True) 

513 

514 async def capture_recovery_page( 

515 self, 

516 channel_id: int, 

517 events: list[RawMessage], 

518 rejected: list[RejectedCapture], 

519 last_id: int, 

520 *, 

521 source_events: Mapping[str, JsonValue] | None = None, 

522 attachments: Mapping[str, Sequence[AttachmentReference]] | None = None, 

523 ) -> None: 

524 """Capture one page of history and advance the channel cursor in one transaction.""" 

525 channel = str(channel_id) 

526 _check_recovery_page(channel, events, rejected, last_id) 

527 source_events = source_events or {} 

528 attachments = attachments or {} 

529 accepted = { 

530 event.identity: _Evidence.bound( 

531 source_events.get(event.identity, normalized_source_event(event)), 

532 attachments.get(event.identity, ()), 

533 ) 

534 for event in events 

535 } 

536 refused = { 

537 _rejection_key(capture.payload): _Evidence.bound( 

538 source_events.get(_rejection_key(capture.payload), plain_json(capture.payload)), 

539 attachments.get(_rejection_key(capture.payload), ()), 

540 ) 

541 for capture in rejected 

542 } 

543 

544 def save( 

545 db: sqlite3.Connection, 

546 ) -> tuple[tuple[tuple[str, str, str], ...], tuple[str, ...]]: 

547 row = db.execute( 

548 "SELECT last_id FROM source_cursors WHERE channel_id=?", (channel,) 

549 ).fetchone() 

550 if row is None or int(row[0]) > last_id: 550 ↛ 551line 550 didn't jump to line 551 because the condition on line 550 was never true

551 raise RuntimeError("Recovery cursor did not advance") 

552 assigned_at = _time() 

553 captured: list[tuple[str, str, str]] = [] 

554 for event in events: 

555 workflow_id, trace_id = uuid4().hex, uuid4().hex 

556 if _insert_capture( 

557 db, 

558 event, 

559 accepted[event.identity], 

560 captured_at=assigned_at, 

561 recovered=True, 

562 # A post read back after the app was away is read like any other: its 

563 # buys are too old past the account's signal age, its sells never are 

564 # (ADR-0010), and the ledger judges each instruction. Only the last day is 

565 # read: an older post is kept as history, so a long stop doesn't send every 

566 # missed post to the model. 

567 mode="historical" 

568 if event.timestamp < assigned_at - READ_BACK_WINDOW 

569 else "live", 

570 workflow_id=workflow_id, 

571 trace_id=trace_id, 

572 ): 

573 captured.append((event.identity, workflow_id, trace_id)) 

574 newly_refused: list[str] = [] 

575 for capture in rejected: 

576 key = _rejection_key(capture.payload) 

577 payload = cast(dict[str, JsonValue], plain_json(capture.payload)) 

578 if _insert_rejection( 578 ↛ 575line 578 didn't jump to line 575 because the condition on line 578 was always true

579 db, payload, capture.reason, refused[key], rejected_at=assigned_at 

580 ): 

581 newly_refused.append(key) 

582 db.execute( 

583 "UPDATE source_cursors SET last_id=?,updated_at=? WHERE channel_id=?", 

584 (last_id, assigned_at.isoformat(), channel), 

585 ) 

586 return tuple(captured), tuple(newly_refused) 

587 

588 captured, newly_refused = await self._unit.run(save, write=True) 

589 for identity, workflow_id, trace_id in captured: 

590 await self._emit_capture(identity, accepted[identity], workflow_id, trace_id) 

591 for key in newly_refused: 

592 self._emit_rejection(refused[key]) 

593 

594 async def claim_batch(self, limit: int = 100) -> tuple[Delivery, ...]: 

595 if limit < 1: 595 ↛ 596line 595 didn't jump to line 596 because the condition on line 595 was never true

596 raise ValueError("Delivery limit must be positive") 

597 

598 def claim(db: sqlite3.Connection) -> tuple[Delivery, ...]: 

599 rows = db.execute( 

600 "SELECT id,payload,mode FROM source_captures WHERE mode='live' " 

601 "AND confirmed=0 AND assigned_seq IS NOT NULL ORDER BY assigned_seq LIMIT ?", 

602 (limit,), 

603 ).fetchall() 

604 if not rows: 

605 fresh = db.execute( 

606 "SELECT id FROM source_captures WHERE mode='live' AND assigned_seq IS NULL " 

607 "ORDER BY source_at,seq LIMIT ?", 

608 (limit,), 

609 ).fetchall() 

610 next_seq = db.execute( 

611 "SELECT COALESCE(MAX(assigned_seq),0) FROM source_captures" 

612 ).fetchone()[0] 

613 for index, (key,) in enumerate(fresh, 1): 

614 db.execute( 

615 "UPDATE source_captures SET assigned_seq=? WHERE id=?", 

616 (next_seq + index, key), 

617 ) 

618 rows = db.execute( 

619 "SELECT id,payload,mode FROM source_captures WHERE mode='live' " 

620 "AND confirmed=0 AND assigned_seq IS NOT NULL ORDER BY assigned_seq LIMIT ?", 

621 (limit,), 

622 ).fetchall() 

623 return tuple(Delivery(*row) for row in rows) 

624 

625 return await self._unit.run(claim, write=True) 

626 

627 async def confirm(self, key: str) -> None: 

628 await self._unit.run( 

629 lambda db: db.execute("UPDATE source_captures SET confirmed=1 WHERE id=?", (key,)), 

630 write=True, 

631 ) 

632 

633 async def recovery_progress(self) -> tuple[RecoveryProgress, ...]: 

634 def read(db: sqlite3.Connection) -> tuple[RecoveryProgress, ...]: 

635 rows = db.execute( 

636 "SELECT channel_id,last_id,bootstrap,updated_at " 

637 "FROM source_cursors ORDER BY channel_id" 

638 ).fetchall() 

639 return tuple( 

640 RecoveryProgress(c, int(i), _stored_bootstrap(b), dt.datetime.fromisoformat(t)) 

641 for c, i, b, t in rows 

642 ) 

643 

644 return await self._unit.run(read) 

645 

646 async def pending_count(self) -> int: 

647 return await self._unit.run( 

648 lambda db: db.execute( 

649 "SELECT count(*) FROM source_captures WHERE mode='live' AND confirmed=0" 

650 ).fetchone()[0] 

651 ) 

652 

653 async def pending_snapshot(self) -> QueueSnapshot: 

654 def read(db: sqlite3.Connection) -> QueueSnapshot: 

655 count, oldest = db.execute( 

656 "SELECT count(*),min(captured_at) FROM source_captures " 

657 "WHERE mode='live' AND confirmed=0" 

658 ).fetchone() 

659 return QueueSnapshot(count, dt.datetime.fromisoformat(oldest) if oldest else None, 0) 

660 

661 return await self._unit.run(read) 

662 

663 async def rejected_count(self) -> int: 

664 return await self._unit.run( 

665 lambda db: db.execute("SELECT count(*) FROM source_rejected").fetchone()[0] 

666 ) 

667 

668 async def attachment_evidence(self, source_id: str) -> tuple[SourceAttachmentEvidence, ...]: 

669 def read(db: sqlite3.Connection) -> tuple[SourceAttachmentEvidence, ...]: 

670 rows = db.execute( 

671 "SELECT evidence_id,attachment_id,filename,content_type,declared_size," 

672 "capture_status," 

673 "byte_size,sha256,managed_path,omitted_count " 

674 "FROM source_attachment_evidence WHERE source_id=? " 

675 "UNION ALL " 

676 "SELECT evidence_id,attachment_id,filename,content_type,declared_size," 

677 "capture_status,byte_size,sha256,managed_path,omitted_count " 

678 "FROM source_rejected_attachment_evidence WHERE source_id=? " 

679 "ORDER BY attachment_id", 

680 (source_id, source_id), 

681 ).fetchall() 

682 return tuple( 

683 SourceAttachmentEvidence( 

684 evidence_id=row[0], 

685 attachment_id=row[1], 

686 filename=row[2], 

687 content_type=row[3], 

688 declared_size=int(row[4]), 

689 status=_stored_attachment_status(row[5]), 

690 byte_size=int(row[6]) if row[6] is not None else None, 

691 sha256=row[7], 

692 managed_path=row[8], 

693 omitted_count=int(row[9]), 

694 ) 

695 for row in rows 

696 ) 

697 

698 return await self._unit.run(read) 

699 

700 async def read_attachment(self, evidence_id: str) -> bytes: 

701 """Return only stored evidence addressed by its opaque source-owned reference.""" 

702 if not is_evidence_id(evidence_id): 

703 raise ValueError("attachment_evidence_unavailable") 

704 row = await self._unit.run( 

705 lambda db: db.execute( 

706 "SELECT managed_path,sha256,byte_size FROM source_attachment_evidence " 

707 "WHERE evidence_id=? AND capture_status='available'", 

708 (evidence_id,), 

709 ).fetchone() 

710 ) 

711 if row is None or row[0] is None or row[1] is None or row[2] is None: 711 ↛ 712line 711 didn't jump to line 712 because the condition on line 711 was never true

712 raise ValueError("attachment_evidence_unavailable") 

713 try: 

714 return await asyncio.to_thread( 

715 read_managed_content, 

716 self._attachment_root, 

717 row[0], 

718 expected_digest=row[1], 

719 expected_size=int(row[2]), 

720 ) 

721 except OSError, ValueError: 

722 raise ValueError("attachment_evidence_unavailable") from None 

723 

724 async def close(self) -> None: 

725 await self._unit.close() 

726 

727 async def _capture_attachment_rows( 

728 self, 

729 source_id: str, 

730 references: Sequence[AttachmentReference], 

731 ) -> None: 

732 if not references: 

733 return 

734 try: 

735 outcomes = await capture_attachments( 

736 references, 

737 self._attachment_root, 

738 transport=self._attachment_transport, 

739 ) 

740 except asyncio.CancelledError: 

741 raise 

742 except Exception: # noqa: BLE001 - attachment failures are recorded per reference 

743 outcomes = tuple(AttachmentCaptureResult("download_failed") for _ in references) 

744 for reference, result in zip(references, outcomes, strict=True): 

745 await self._unit.run( 

746 lambda db, reference=reference, result=result: db.execute( 

747 "UPDATE source_attachment_evidence SET capture_status=?,byte_size=?," 

748 "sha256=?,managed_path=? WHERE source_id=? AND attachment_id=?", 

749 ( 

750 result.status, 

751 result.byte_size, 

752 result.sha256, 

753 result.managed_path, 

754 source_id, 

755 reference.attachment_id, 

756 ), 

757 ), 

758 write=True, 

759 ) 

760 

761 async def _emit_capture( 

762 self, identity: str, evidence: _Evidence, workflow_id: str, trace_id: str 

763 ) -> None: 

764 self._record_source_event( 

765 evidence.original, 

766 workflow_id=workflow_id, 

767 trace_id=trace_id, 

768 status=evidence.status, 

769 source_bytes=evidence.size, 

770 ) 

771 await self._capture_attachment_rows(identity, evidence.attachments) 

772 

773 def _emit_rejection(self, evidence: _Evidence) -> None: 

774 self._record_source_event( 

775 evidence.original, 

776 workflow_id=None, 

777 trace_id=None, 

778 status=evidence.status, 

779 source_bytes=evidence.size, 

780 ) 

781 

782 def _record_source_event( 

783 self, 

784 payload: JsonValue, 

785 *, 

786 workflow_id: str | None, 

787 trace_id: str | None, 

788 status: CaptureStatus = "complete", 

789 source_bytes: int | None = None, 

790 ) -> None: 

791 if self._diagnostics is None: 

792 return 

793 if source_bytes is None: 793 ↛ 794line 793 didn't jump to line 794 because the condition on line 793 was never true

794 serialized = json.dumps(payload, ensure_ascii=False, separators=(",", ":")) 

795 source_bytes = len(serialized.encode("utf-8")) 

796 try: 

797 self._diagnostics.record_payload( 

798 capture_kind="source_event", 

799 workflow_id=workflow_id, 

800 trace_id=trace_id, 

801 destination_id=None, 

802 attempt=None, 

803 provider="other", 

804 payload=payload, 

805 source_bytes=source_bytes, 

806 status=status, 

807 ) 

808 except Exception: # noqa: BLE001 - diagnostics must not alter the operational result 

809 # Source commits are operational; diagnostics can only report a gap. 

810 return 

811 

812 

813_BOOTSTRAP: TypeAdapter[Bootstrap] = TypeAdapter(Bootstrap) 

814_ATTACHMENT_STATUS: TypeAdapter[AttachmentStatus] = TypeAdapter(AttachmentStatus) 

815 

816 

817def _stored_bootstrap(value: object) -> Bootstrap: 

818 """A cursor row read from disk is checked before it steers history recovery.""" 

819 return _BOOTSTRAP.validate_python(value, strict=True) 

820 

821 

822def _stored_attachment_status(value: object) -> AttachmentStatus: 

823 """An evidence row read from disk is checked before it reaches operator views.""" 

824 return _ATTACHMENT_STATUS.validate_python(value, strict=True)