Coverage for src/governed_harness/storage/sqlite.py: 96%
91 statements
« prev ^ index » next coverage.py v7.17.2, created at 2026-11-12 16:06 +0000
« prev ^ index » next coverage.py v7.17.2, created at 2026-11-12 16:06 +0000
1from __future__ import annotations
3import builtins
4import json
5import sqlite3
6import threading
7from collections.abc import Iterable
8from datetime import UTC, datetime
9from pathlib import Path
10from types import TracebackType
11from typing import Any, Self
13from pydantic import BaseModel
15from governed_harness.domain.errors import NotFoundError
18class SQLiteStateStore:
19 """Typed record storage. Events remain the audit authority; records are query projections."""
21 def __init__(self, path: Path) -> None:
22 path.parent.mkdir(parents=True, exist_ok=True, mode=0o700)
23 self.connection = sqlite3.connect(path, timeout=30, check_same_thread=False)
24 self.connection.row_factory = sqlite3.Row
25 self.connection.execute("PRAGMA journal_mode=WAL")
26 self.connection.execute("PRAGMA synchronous=FULL")
27 self.connection.execute("PRAGMA busy_timeout=30000")
28 self._lock = threading.RLock()
29 self.connection.executescript(
30 """
31 CREATE TABLE IF NOT EXISTS records (
32 record_type TEXT NOT NULL,
33 record_id TEXT NOT NULL,
34 execution_id TEXT,
35 project_id TEXT,
36 created_at TEXT NOT NULL,
37 updated_at TEXT NOT NULL,
38 payload_json TEXT NOT NULL,
39 PRIMARY KEY(record_type, record_id)
40 );
41 CREATE INDEX IF NOT EXISTS records_execution_idx
42 ON records(record_type, execution_id, updated_at);
43 CREATE INDEX IF NOT EXISTS records_project_idx
44 ON records(record_type, project_id, updated_at);
45 CREATE TABLE IF NOT EXISTS flags (
46 key TEXT PRIMARY KEY,
47 value TEXT NOT NULL,
48 updated_at TEXT NOT NULL
49 );
50 """
51 )
52 self.connection.commit()
54 def put(
55 self,
56 record_type: str,
57 record_id: str,
58 value: BaseModel | dict[str, Any],
59 *,
60 execution_id: str | None = None,
61 project_id: str | None = None,
62 ) -> None:
63 with self._lock, self.connection:
64 self._upsert(record_type, record_id, value, execution_id, project_id)
66 def put_many(
67 self,
68 records: Iterable[tuple[str, str, BaseModel | dict[str, Any]]],
69 *,
70 execution_id: str | None = None,
71 project_id: str | None = None,
72 ) -> None:
73 """Store ``(record_type, record_id, value)`` records as :meth:`put` would, in one
74 transaction (one commit instead of one per record; since 2.0.1)."""
75 with self._lock, self.connection:
76 for record_type, record_id, value in records:
77 self._upsert(record_type, record_id, value, execution_id, project_id)
79 def _upsert(
80 self,
81 record_type: str,
82 record_id: str,
83 value: BaseModel | dict[str, Any],
84 execution_id: str | None,
85 project_id: str | None,
86 ) -> None:
87 payload = (
88 value.model_dump(mode="json", by_alias=True) if isinstance(value, BaseModel) else value
89 )
90 now = datetime.now(UTC).isoformat()
91 self.connection.execute(
92 """
93 INSERT INTO records(record_type,record_id,execution_id,project_id,created_at,updated_at,payload_json)
94 VALUES(?,?,?,?,?,?,?)
95 ON CONFLICT(record_type,record_id) DO UPDATE SET
96 execution_id=excluded.execution_id,
97 project_id=excluded.project_id,
98 updated_at=excluded.updated_at,
99 payload_json=excluded.payload_json
100 """,
101 (
102 record_type,
103 record_id,
104 execution_id,
105 project_id,
106 now,
107 now,
108 json.dumps(payload, sort_keys=True, ensure_ascii=False, default=str),
109 ),
110 )
112 def get_dict(self, record_type: str, record_id: str) -> dict[str, Any]:
113 with self._lock:
114 row = self.connection.execute(
115 "SELECT payload_json FROM records WHERE record_type=? AND record_id=?",
116 (record_type, record_id),
117 ).fetchone()
118 if row is None:
119 raise NotFoundError(f"{record_type} not found: {record_id}")
120 payload: dict[str, Any] = json.loads(row["payload_json"])
121 return payload
123 def get[T: BaseModel](self, record_type: str, record_id: str, model: type[T]) -> T:
124 return model.model_validate(self.get_dict(record_type, record_id))
126 def list_dicts(
127 self,
128 record_type: str,
129 *,
130 execution_id: str | None = None,
131 project_id: str | None = None,
132 newest_first: bool = False,
133 ) -> list[dict[str, Any]]:
134 clauses = ["record_type=?"]
135 params: list[Any] = [record_type]
136 if execution_id is not None:
137 clauses.append("execution_id=?")
138 params.append(execution_id)
139 if project_id is not None:
140 clauses.append("project_id=?")
141 params.append(project_id)
142 direction = "DESC" if newest_first else "ASC"
143 # Only code-defined literals are interpolated (the clause list and the direction);
144 # every value is bound as a parameter, so Bandit B608 is a false positive here.
145 with self._lock:
146 rows = self.connection.execute(
147 f"SELECT payload_json FROM records WHERE {' AND '.join(clauses)} ORDER BY updated_at {direction}", # nosec B608
148 tuple(params),
149 ).fetchall()
150 return [json.loads(row["payload_json"]) for row in rows]
152 def list[T: BaseModel](
153 self,
154 record_type: str,
155 model: type[T],
156 *,
157 execution_id: str | None = None,
158 project_id: str | None = None,
159 newest_first: bool = False,
160 ) -> list[T]:
161 return [
162 model.model_validate(item)
163 for item in self.list_dicts(
164 record_type,
165 execution_id=execution_id,
166 project_id=project_id,
167 newest_first=newest_first,
168 )
169 ]
171 def delete(self, record_type: str, record_id: str) -> None:
172 with self._lock, self.connection:
173 self.connection.execute(
174 "DELETE FROM records WHERE record_type=? AND record_id=?", (record_type, record_id)
175 )
177 def record_types(self, execution_id: str) -> builtins.list[str]:
178 rows = self.connection.execute(
179 "SELECT DISTINCT record_type FROM records WHERE execution_id=? ORDER BY record_type",
180 (execution_id,),
181 ).fetchall()
182 return [str(row["record_type"]) for row in rows]
184 def flags_for(self, execution_id: str) -> dict[str, str]:
185 """The flags the engine keys by run (``<name>:<run id>``)."""
186 suffix = f":{execution_id}"
187 rows = self.connection.execute("SELECT key, value FROM flags").fetchall()
188 return {
189 str(row["key"]): str(row["value"]) for row in rows if str(row["key"]).endswith(suffix)
190 }
192 def delete_execution(self, execution_id: str, *, keep: frozenset[str] = frozenset()) -> None:
193 """Delete the records and flags of a run (``retention.eventDays``), except the record
194 types in ``keep``."""
195 flags = list(self.flags_for(execution_id))
196 with self._lock, self.connection:
197 for record_type in self.record_types(execution_id):
198 if record_type in keep: 198 ↛ 199line 198 didn't jump to line 199 because the condition on line 198 was never true
199 continue
200 self.connection.execute(
201 "DELETE FROM records WHERE record_type=? AND execution_id=?",
202 (record_type, execution_id),
203 )
204 for key in flags:
205 self.connection.execute("DELETE FROM flags WHERE key=?", (key,))
207 def set_flag(self, key: str, value: str) -> None:
208 now = datetime.now(UTC).isoformat()
209 with self._lock, self.connection:
210 self.connection.execute(
211 "INSERT INTO flags(key,value,updated_at) VALUES(?,?,?) "
212 "ON CONFLICT(key) DO UPDATE SET value=excluded.value,updated_at=excluded.updated_at",
213 (key, value, now),
214 )
216 def get_flag(self, key: str) -> str | None:
217 # Validators may run side by side (#3, #58) and poll the cancellation flag while the
218 # process ledger writes: every use of the shared connection holds the lock.
219 with self._lock:
220 row = self.connection.execute("SELECT value FROM flags WHERE key=?", (key,)).fetchone()
221 return str(row["value"]) if row else None
223 def close(self) -> None:
224 self.connection.close()
226 def __enter__(self) -> Self:
227 return self
229 def __exit__(
230 self,
231 exc_type: type[BaseException] | None,
232 exc: BaseException | None,
233 traceback: TracebackType | None,
234 ) -> None:
235 self.close()