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

1from __future__ import annotations 

2 

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 

12 

13from pydantic import BaseModel 

14 

15from governed_harness.domain.errors import NotFoundError 

16 

17 

18class SQLiteStateStore: 

19 """Typed record storage. Events remain the audit authority; records are query projections.""" 

20 

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

53 

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) 

65 

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) 

78 

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 ) 

111 

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 

122 

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

125 

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] 

151 

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 ] 

170 

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 ) 

176 

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] 

183 

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 } 

191 

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

206 

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 ) 

215 

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 

222 

223 def close(self) -> None: 

224 self.connection.close() 

225 

226 def __enter__(self) -> Self: 

227 return self 

228 

229 def __exit__( 

230 self, 

231 exc_type: type[BaseException] | None, 

232 exc: BaseException | None, 

233 traceback: TracebackType | None, 

234 ) -> None: 

235 self.close()