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
« 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."""
3import datetime as dt
4import json
5import sqlite3
6from collections.abc import Generator
7from contextlib import contextmanager
8from pathlib import Path
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
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)
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)
71class CurrentExecutionSnapshotSchema:
72 """Expose authoritative execution and snapshot schema metadata to outer services."""
74 @property
75 def execution_schema_revision(self) -> int:
76 return EXECUTION_SCHEMA.revision
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
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
90current_execution_snapshot_schema = CurrentExecutionSnapshotSchema()
93class Store:
94 """One SQLite connection confined to one execution owner thread.
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 """
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
116 @property
117 def usable(self) -> bool:
118 return self._usable and not self._closed
120 def _require_usable(self) -> None:
121 if not self.usable:
122 raise RuntimeError("Execution SQLite session is closed or unusable")
124 def _discard(self) -> None:
125 self._usable = False
126 self.close()
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)
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()
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)
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 )
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)
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)
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)
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 )
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 )
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()
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
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
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"""
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)