1"""SQLite cache of raw provider responses keyed by (provider, model, prompt_version, sha256(system+user)).23A cache hit replays the *recorded* text; it is counted separately (`cached_hits`) so a run can report4how many decisions were genuinely online. The same store serves as the frozen decision library."""5from __future__ import annotations67import json8import sqlite39import threading10import time11from pathlib import Path121314class DecisionCache:15 def __init__(self, path: str | Path) -> None:16 self.path = Path(path)17 self.path.parent.mkdir(parents=True, exist_ok=True)18 self._lock = threading.Lock()19 self._conn = sqlite3.connect(str(self.path), check_same_thread=False, timeout=60.0)20 self._conn.execute("""CREATE TABLE IF NOT EXISTS decisions (21 key TEXT PRIMARY KEY, provider TEXT, model TEXT, prompt_version TEXT, prompt_id TEXT,22 system_prompt TEXT, user_prompt TEXT, response_text TEXT, usage_json TEXT, created REAL)""")23 self._conn.commit()2425 @staticmethod26 def key(provider: str, model: str, prompt_version: str, system_prompt: str, user_prompt: str) -> str:27 import hashlib28 h = hashlib.sha256((provider + "|" + model + "|" + prompt_version + "|" + system_prompt + "|" + user_prompt).encode()).hexdigest()29 return h3031 def get(self, key: str) -> tuple[str, dict] | None:32 with self._lock:33 row = self._conn.execute("SELECT response_text, usage_json FROM decisions WHERE key=?", (key,)).fetchone()34 if row is None:35 return None36 return row[0], json.loads(row[1] or "{}")3738 def put(self, key: str, provider: str, model: str, prompt_version: str, prompt_id: str,39 system_prompt: str, user_prompt: str, response_text: str, usage: dict) -> None:40 with self._lock:41 self._conn.execute("INSERT OR REPLACE INTO decisions VALUES (?,?,?,?,?,?,?,?,?,?)",42 (key, provider, model, prompt_version, prompt_id, system_prompt, user_prompt,43 response_text, json.dumps(usage), time.time()))44 self._conn.commit()4546 def count(self) -> int:47 with self._lock:48 return int(self._conn.execute("SELECT COUNT(*) FROM decisions").fetchone()[0])4950 def close(self) -> None:51 self._conn.close()52