Coverage for src/copytrading_engine/execution/adapters/sqlite_ledger.py: 89%

159 statements  

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

1"""Durable, account-scoped execution ledger and local notification outbox.""" 

2 

3import datetime as dt 

4import json 

5import sqlite3 

6from collections.abc import Generator 

7from contextlib import contextmanager 

8from pathlib import Path 

9 

10from copytrading_engine.execution.domain.events import JournalEvent 

11from copytrading_engine.execution.domain.ledger_state import ( 

12 EarlierLedgerVersion, 

13 LedgerSnapshot, 

14 is_earlier_version, 

15) 

16from copytrading_engine.execution.domain.values import BrokerAccountNumber 

17from copytrading_engine.execution.presentation.notifications import execution_notification 

18from copytrading_engine.shared.notification_models import NotificationIntent 

19from copytrading_engine.shared.sqlite import SchemaComponent, ensure_schema 

20 

21EXECUTION_SCHEMA = SchemaComponent( 

22 "execution", 

23 5, 

24 """ 

25 CREATE TABLE IF NOT EXISTS identity ( 

26 singleton INTEGER PRIMARY KEY CHECK (singleton = 1), 

27 environment TEXT NOT NULL CHECK (environment IN ('paper', 'live')), 

28 account_id TEXT NOT NULL 

29 ); 

30 CREATE TABLE IF NOT EXISTS snapshot ( 

31 singleton INTEGER PRIMARY KEY CHECK (singleton = 1), 

32 data TEXT NOT NULL 

33 ); 

34 CREATE TABLE IF NOT EXISTS journal ( 

35 id INTEGER PRIMARY KEY AUTOINCREMENT, 

36 event TEXT NOT NULL, 

37 published INTEGER NOT NULL DEFAULT 0 CHECK (published IN (0, 1)), 

38 enqueued_at TEXT NOT NULL 

39 ); 

40 CREATE INDEX IF NOT EXISTS journal_waiting ON journal(id) WHERE published=0; 

41 CREATE TABLE IF NOT EXISTS notifications ( 

42 id INTEGER PRIMARY KEY AUTOINCREMENT, 

43 key TEXT NOT NULL UNIQUE, 

44 stream_id TEXT NOT NULL, 

45 payload TEXT NOT NULL, 

46 attempts INTEGER NOT NULL DEFAULT 0, 

47 retry_at REAL NOT NULL DEFAULT 0, 

48 delivered_at REAL, 

49 enqueued_at TEXT NOT NULL 

50 ); 

51 CREATE INDEX IF NOT EXISTS notifications_waiting 

52 ON notifications(id) WHERE delivered_at IS NULL; 

53 """, 

54) 

55 

56 

57def decode_ledger_snapshot(raw: str) -> LedgerSnapshot: 

58 """Decode the persisted snapshot row; its format version and control must be explicit.""" 

59 payload = json.loads(raw) 

60 if not isinstance(payload, dict) or type(payload.get("schema_version")) is not int: 

61 raise RuntimeError("Execution snapshot schema version is missing") 

62 if is_earlier_version(payload["schema_version"]): 

63 raise EarlierLedgerVersion("Execution snapshot was written by an earlier version") 

64 if payload["schema_version"] != LedgerSnapshot.model_fields["schema_version"].default: 

65 raise ValueError("Unsupported execution snapshot schema version") 

66 if "control" not in payload: 

67 raise ValueError("Execution snapshot is missing account control evidence") 

68 return LedgerSnapshot.model_validate_json(raw) 

69 

70 

71class CurrentExecutionSnapshotSchema: 

72 """Expose authoritative execution and snapshot schema metadata to outer services.""" 

73 

74 @property 

75 def execution_schema_revision(self) -> int: 

76 return EXECUTION_SCHEMA.revision 

77 

78 @property 

79 def snapshot_json_schema_version(self) -> int: 

80 version = LedgerSnapshot.model_fields["schema_version"].default 

81 if type(version) is not int: 81 ↛ 82line 81 didn't jump to line 82 because the condition on line 81 was never true

82 raise RuntimeError("Execution snapshot schema version is invalid") 

83 return version 

84 

85 def validate_snapshot(self, serialized: str) -> tuple[str | None, str | None]: 

86 snapshot = decode_ledger_snapshot(serialized) 

87 return snapshot.environment, snapshot.account_id 

88 

89 

90current_execution_snapshot_schema = CurrentExecutionSnapshotSchema() 

91 

92 

93class Store: 

94 """One SQLite connection confined to one execution owner thread. 

95 

96 A failed COMMIT has an uncertain outcome. The connection is discarded and the 

97 owner must stop; it cannot safely retry the transition in this process. 

98 """ 

99 

100 def __init__(self, path: Path) -> None: 

101 path.parent.mkdir(parents=True, exist_ok=True) 

102 self.db = sqlite3.connect(path, timeout=5, isolation_level=None) 

103 self._usable = True 

104 self._closed = False 

105 self._expected_identity: tuple[str, str] | None = None 

106 try: 

107 self.db.execute("PRAGMA journal_mode=WAL") 

108 self.db.execute("PRAGMA synchronous=FULL") 

109 self.db.execute("PRAGMA foreign_keys=ON") 

110 self.db.execute("PRAGMA busy_timeout=5000") 

111 ensure_schema(self.db, EXECUTION_SCHEMA) 

112 except BaseException: 

113 self.db.close() 

114 raise 

115 

116 @property 

117 def usable(self) -> bool: 

118 return self._usable and not self._closed 

119 

120 def _require_usable(self) -> None: 

121 if not self.usable: 

122 raise RuntimeError("Execution SQLite session is closed or unusable") 

123 

124 def _discard(self) -> None: 

125 self._usable = False 

126 self.close() 

127 

128 def bind_identity(self, account_id: BrokerAccountNumber, environment: str) -> None: 

129 if not account_id or environment not in {"paper", "live"}: 129 ↛ 130line 129 didn't jump to line 130 because the condition on line 129 was never true

130 raise ValueError("Verified broker account and environment are required") 

131 self._require_usable() 

132 row = self.db.execute( 

133 "SELECT environment, account_id FROM identity WHERE singleton=1" 

134 ).fetchone() 

135 if row is not None and row != (environment, account_id): 

136 raise RuntimeError( 

137 "Execution state belongs to a different broker account or environment" 

138 ) 

139 snapshot = self.db.execute("SELECT data FROM snapshot WHERE singleton=1").fetchone() 

140 if snapshot is not None: 

141 ledger = decode_ledger_snapshot(snapshot[0]) 

142 if (ledger.account_id, ledger.environment) != (account_id, environment): 142 ↛ 143line 142 didn't jump to line 143 because the condition on line 142 was never true

143 raise RuntimeError("Execution snapshot belongs to a different broker account") 

144 if (row is None) != (snapshot is None): 144 ↛ 145line 144 didn't jump to line 145 because the condition on line 144 was never true

145 raise RuntimeError("Execution identity and snapshot are incomplete") 

146 self._expected_identity = (environment, account_id) 

147 

148 def load(self) -> LedgerSnapshot: 

149 self._require_usable() 

150 row = self.db.execute("SELECT data FROM snapshot WHERE singleton=1").fetchone() 

151 return decode_ledger_snapshot(row[0]) if row else LedgerSnapshot() 

152 

153 def save(self, snapshot: LedgerSnapshot, event: JournalEvent) -> None: 

154 with self._transaction(): 

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

156 self._expected_identity is None 

157 or (snapshot.environment, snapshot.account_id) != self._expected_identity 

158 ): 

159 raise RuntimeError("Execution snapshot identity was not verified") 

160 identity = self.db.execute( 

161 "SELECT environment, account_id FROM identity WHERE singleton=1" 

162 ).fetchone() 

163 if identity is None: 

164 self.db.execute( 

165 "INSERT INTO identity(singleton, environment, account_id) VALUES (1, ?, ?)", 

166 self._expected_identity, 

167 ) 

168 elif identity != self._expected_identity: 168 ↛ 169line 168 didn't jump to line 169 because the condition on line 168 was never true

169 raise RuntimeError( 

170 "Execution snapshot account does not match verified broker account" 

171 ) 

172 self.db.execute( 

173 "INSERT INTO snapshot(singleton, data) VALUES (1, ?) " 

174 "ON CONFLICT(singleton) DO UPDATE SET data=excluded.data", 

175 (snapshot.model_dump_json(),), 

176 ) 

177 self.db.execute( 

178 "INSERT INTO journal(event, enqueued_at) VALUES (?, ?)", 

179 (event.model_dump_json(), dt.datetime.now(dt.UTC).isoformat()), 

180 ) 

181 notification = execution_notification(snapshot, event) 

182 if notification is not None: 

183 self._enqueue_notification(notification) 

184 

185 def _enqueue_notification(self, notification: NotificationIntent) -> None: 

186 payload = notification.payload 

187 self.db.execute( 

188 "INSERT OR IGNORE INTO notifications(key, stream_id, payload, enqueued_at) " 

189 "VALUES (?, ?, ?, ?)", 

190 ( 

191 notification.key, 

192 notification.stream_id, 

193 json.dumps( 

194 { 

195 "labels": dict(payload.labels), 

196 "annotations": dict(payload.annotations), 

197 "starts_at": payload.starts_at.isoformat(), 

198 }, 

199 ensure_ascii=False, 

200 ), 

201 dt.datetime.now(dt.UTC).isoformat(), 

202 ), 

203 ) 

204 

205 def message_events(self, message_ids: set[str]) -> tuple[JournalEvent, ...]: 

206 """Every journal event of these posts, plus the account's control changes over the 

207 same span, for Activity's timeline.""" 

208 self._require_usable() 

209 return message_events(self.db, message_ids) 

210 

211 def event_page( 

212 self, before_seq: int | None, limit: int 

213 ) -> tuple[tuple[int, JournalEvent], ...]: 

214 if not 1 <= limit <= 100 or (before_seq is not None and before_seq < 1): 214 ↛ 215line 214 didn't jump to line 215 because the condition on line 214 was never true

215 raise ValueError("Invalid account event page") 

216 self._require_usable() 

217 rows = self.db.execute( 

218 "SELECT id,event FROM journal WHERE (? IS NULL OR id < ?) ORDER BY id DESC LIMIT ?", 

219 (before_seq, before_seq, limit), 

220 ).fetchall() 

221 return tuple((seq, JournalEvent.model_validate_json(raw)) for seq, raw in rows) 

222 

223 def feed_events( 

224 self, before_seq: int | None, limit: int 

225 ) -> tuple[tuple[int, JournalEvent], ...]: 

226 self._require_usable() 

227 return feed_events(self.db, before_seq, limit) 

228 

229 def pending_notifications(self) -> tuple[tuple[int, str, str, dict[str, object]], ...]: 

230 self._require_usable() 

231 rows = self.db.execute( 

232 "SELECT id, key, stream_id, payload FROM notifications " 

233 "WHERE delivered_at IS NULL ORDER BY id LIMIT 100" 

234 ).fetchall() 

235 return tuple( 

236 (id_, key, stream_id, json.loads(payload)) for id_, key, stream_id, payload in rows 

237 ) 

238 

239 def confirm_notification(self, notification_id: int) -> None: 

240 with self._transaction(): 

241 self.db.execute( 

242 "UPDATE notifications SET delivered_at=? WHERE id=? AND delivered_at IS NULL", 

243 (dt.datetime.now(dt.UTC).timestamp(), notification_id), 

244 ) 

245 

246 def close(self) -> None: 

247 if not self._closed: 247 ↛ exitline 247 didn't return from function 'close' because the condition on line 247 was always true

248 self._closed = True 

249 self.db.close() 

250 

251 @contextmanager 

252 def _transaction(self) -> Generator[None]: 

253 self._require_usable() 

254 self.db.execute("BEGIN IMMEDIATE") 

255 try: 

256 yield 

257 except BaseException as failure: 

258 try: 

259 self.db.execute("ROLLBACK") 

260 except BaseException as rollback_failure: 

261 self._discard() 

262 failure.add_note(f"SQLite rollback failed: {type(rollback_failure).__name__}") 

263 raise failure from rollback_failure 

264 raise 

265 else: 

266 try: 

267 self.db.execute("COMMIT") 

268 except BaseException: 

269 self._discard() 

270 raise 

271 

272 

273def message_events(db: sqlite3.Connection, message_ids: set[str]) -> tuple[JournalEvent, ...]: 

274 """The journal events of these posts in order, with the account's control changes between 

275 the first and the last of them (a resume explains a held buy).""" 

276 if not message_ids: 

277 return () 

278 marks = ",".join("?" * len(message_ids)) 

279 rows = db.execute( 

280 "SELECT event FROM journal WHERE json_extract(event, '$.payload.message_id') " 

281 f"IN ({marks}) ORDER BY id", 

282 tuple(sorted(message_ids)), 

283 ).fetchall() 

284 events = tuple(JournalEvent.model_validate_json(raw) for (raw,) in rows) 

285 if not events: 

286 return () 

287 first, last = min(event.at for event in events), max(event.at for event in events) 

288 controls = db.execute( 

289 "SELECT event FROM journal WHERE json_extract(event, '$.payload.kind') = " 

290 "'account_control_changed' ORDER BY id" 

291 ).fetchall() 

292 changes = tuple( 

293 event 

294 for event in (JournalEvent.model_validate_json(raw) for (raw,) in controls) 

295 if first <= event.at <= last 

296 ) 

297 return events + changes 

298 

299 

300# What the account feed shows: each order's first final update, a sale the owner made at the 

301# broker, entries paused or resumed, and a holdings review the owner settled. Each order's first 

302# final update is found in one grouped pass over the journal, not by searching every earlier 

303# event again for each one. 

304FEED_EVENTS = """ 

305WITH first_final AS ( 

306 SELECT min(id) AS id FROM journal 

307 WHERE json_extract(event, '$.payload.kind') = 'order_update' 

308 AND json_extract(event, '$.payload.status') IN ('filled','canceled','expired','rejected') 

309 GROUP BY json_extract(event, '$.payload.client_id')) 

310SELECT j.id, j.event FROM journal j 

311WHERE (? IS NULL OR j.id < ?) AND ( 

312 j.id IN (SELECT id FROM first_final) 

313 OR json_extract(j.event, '$.payload.kind') 

314 IN ('manual_sale_recorded', 'ownership_resolved', 'limits_changed') 

315 OR (json_extract(j.event, '$.payload.kind') = 'account_control_changed' 

316 AND json_extract(j.event, '$.payload.result.command.action') IN ('pause', 'resume'))) 

317ORDER BY j.id DESC LIMIT ? 

318""" 

319 

320 

321def feed_events( 

322 db: sqlite3.Connection, before_seq: int | None, limit: int 

323) -> tuple[tuple[int, JournalEvent], ...]: 

324 """The journal events the account feed turns into rows, newest first.""" 

325 if not 1 <= limit <= 100 or (before_seq is not None and before_seq < 1): 325 ↛ 326line 325 didn't jump to line 326 because the condition on line 325 was never true

326 raise ValueError("Invalid account feed page") 

327 rows = db.execute(FEED_EVENTS, (before_seq, before_seq, limit)).fetchall() 

328 return tuple((seq, JournalEvent.model_validate_json(raw)) for seq, raw in rows)