1"""SQLite persistence (aiosqlite, WAL)."""23from __future__ import annotations45import json6import time7from pathlib import Path8from typing import Any, Iterable910import aiosqlite1112SCHEMA = """13CREATE TABLE IF NOT EXISTS users (14 id INTEGER PRIMARY KEY AUTOINCREMENT,15 email TEXT UNIQUE NOT NULL,16 password_hash TEXT NOT NULL,17 role TEXT NOT NULL DEFAULT 'admin',18 created_at REAL NOT NULL,19 last_login_at REAL20);21CREATE TABLE IF NOT EXISTS api_keys (22 id INTEGER PRIMARY KEY AUTOINCREMENT,23 name TEXT NOT NULL,24 prefix TEXT NOT NULL,25 key_hash TEXT UNIQUE NOT NULL,26 scopes TEXT NOT NULL DEFAULT 'inference',27 created_at REAL NOT NULL,28 last_used_at REAL,29 request_count INTEGER NOT NULL DEFAULT 0,30 revoked_at REAL31);32CREATE TABLE IF NOT EXISTS models (33 id TEXT PRIMARY KEY,34 name TEXT NOT NULL,35 family TEXT,36 provider TEXT,37 architecture TEXT,38 model_type TEXT,39 parameter_count INTEGER,40 active_parameter_count INTEGER,41 quantization TEXT,42 quant_bits REAL,43 runtime TEXT NOT NULL,44 format TEXT NOT NULL,45 path TEXT NOT NULL,46 weights_file TEXT,47 mmproj_file TEXT,48 disk_size_bytes INTEGER NOT NULL DEFAULT 0,49 weights_bytes INTEGER NOT NULL DEFAULT 0,50 estimated_ram_gb REAL,51 kv_bytes_per_token INTEGER,52 recommended_context INTEGER,53 max_context INTEGER,54 task TEXT NOT NULL DEFAULT 'text-generation',55 vision INTEGER NOT NULL DEFAULT 0,56 embedding INTEGER NOT NULL DEFAULT 0,57 reranker INTEGER NOT NULL DEFAULT 0,58 thinking INTEGER NOT NULL DEFAULT 0,59 tools INTEGER NOT NULL DEFAULT 0,60 size_class TEXT,61 installed INTEGER NOT NULL DEFAULT 1,62 enabled INTEGER NOT NULL DEFAULT 1,63 favorite INTEGER NOT NULL DEFAULT 0,64 pinned INTEGER NOT NULL DEFAULT 0,65 verified INTEGER NOT NULL DEFAULT 0,66 compatible INTEGER NOT NULL DEFAULT 1,67 compatibility_status TEXT,68 compatibility_reason TEXT,69 tags TEXT NOT NULL DEFAULT '[]',70 repository TEXT,71 manifest TEXT,72 notes TEXT,73 overrides TEXT NOT NULL DEFAULT '{}',74 created_at REAL NOT NULL,75 updated_at REAL NOT NULL,76 last_loaded_at REAL,77 last_used_at REAL,78 load_count INTEGER NOT NULL DEFAULT 0,79 request_count INTEGER NOT NULL DEFAULT 0,80 tokens_generated INTEGER NOT NULL DEFAULT 0,81 min_load_ms REAL,82 avg_load_ms REAL,83 max_load_ms REAL,84 last_load_ms REAL,85 avg_tps REAL,86 first_token_latency_ms REAL87);88CREATE TABLE IF NOT EXISTS model_aliases (89 alias TEXT PRIMARY KEY,90 model_id TEXT NOT NULL,91 created_at REAL NOT NULL92);93CREATE TABLE IF NOT EXISTS model_benchmarks (94 id INTEGER PRIMARY KEY AUTOINCREMENT,95 model_id TEXT NOT NULL,96 created_at REAL NOT NULL,97 load_ms REAL,98 prompt_tokens INTEGER,99 prompt_tps REAL,100 generation_tokens INTEGER,101 generation_tps REAL,102 ttft_ms REAL,103 peak_memory_gb REAL,104 avg_memory_gb REAL,105 cpu_percent REAL,106 gpu_percent REAL,107 thermal_state TEXT,108 context INTEGER,109 runtime TEXT,110 params TEXT,111 notes TEXT112);113CREATE TABLE IF NOT EXISTS model_events (114 id INTEGER PRIMARY KEY AUTOINCREMENT,115 created_at REAL NOT NULL,116 model_id TEXT,117 event TEXT NOT NULL,118 detail TEXT119);120CREATE TABLE IF NOT EXISTS inference_requests (121 id INTEGER PRIMARY KEY AUTOINCREMENT,122 created_at REAL NOT NULL,123 model_id TEXT,124 requested_model TEXT,125 endpoint TEXT NOT NULL,126 api_key_id INTEGER,127 stream INTEGER NOT NULL DEFAULT 0,128 prompt_tokens INTEGER,129 completion_tokens INTEGER,130 ttft_ms REAL,131 total_ms REAL,132 tps REAL,133 load_wait_ms REAL,134 status INTEGER,135 error_code TEXT,136 prompt TEXT,137 completion TEXT138);139CREATE INDEX IF NOT EXISTS idx_inference_created ON inference_requests(created_at);140CREATE TABLE IF NOT EXISTS system_metrics (141 ts REAL PRIMARY KEY,142 mem_used_gb REAL,143 mem_available_gb REAL,144 mem_pressure INTEGER,145 swap_used_gb REAL,146 cpu_percent REAL,147 gpu_percent REAL,148 disk_free_gb REAL,149 thermal TEXT,150 worker_rss_gb REAL,151 loaded_model TEXT152);153CREATE TABLE IF NOT EXISTS jobs (154 id TEXT PRIMARY KEY,155 kind TEXT NOT NULL,156 status TEXT NOT NULL,157 title TEXT,158 payload TEXT,159 progress REAL NOT NULL DEFAULT 0,160 detail TEXT,161 result TEXT,162 error TEXT,163 created_at REAL NOT NULL,164 started_at REAL,165 finished_at REAL166);167CREATE TABLE IF NOT EXISTS settings (168 key TEXT PRIMARY KEY,169 value TEXT NOT NULL,170 updated_at REAL NOT NULL171);172CREATE TABLE IF NOT EXISTS audit_logs (173 id INTEGER PRIMARY KEY AUTOINCREMENT,174 created_at REAL NOT NULL,175 actor TEXT,176 action TEXT NOT NULL,177 target TEXT,178 detail TEXT,179 ip TEXT180);181CREATE TABLE IF NOT EXISTS harvest_candidates (182 repo_id TEXT PRIMARY KEY,183 runtime TEXT NOT NULL,184 family TEXT,185 base_model TEXT,186 name TEXT,187 task TEXT,188 quantization TEXT,189 parameter_count INTEGER,190 download_bytes INTEGER,191 estimated_ram_gb REAL,192 size_class TEXT,193 compatibility_status TEXT,194 compatibility_reason TEXT,195 downloads INTEGER,196 likes INTEGER,197 last_modified TEXT,198 files TEXT,199 duplicate_of TEXT,200 installed INTEGER NOT NULL DEFAULT 0,201 selected INTEGER NOT NULL DEFAULT 0,202 dismissed INTEGER NOT NULL DEFAULT 0,203 score REAL,204 scanned_at REAL NOT NULL,205 raw TEXT206);207"""208209210class Database:211 def __init__(self, path: Path):212 self.path = path213 self._conn: aiosqlite.Connection | None = None214215 async def connect(self) -> None:216 self.path.parent.mkdir(parents=True, exist_ok=True)217 self._conn = await aiosqlite.connect(self.path)218 self._conn.row_factory = aiosqlite.Row219 await self._conn.execute("PRAGMA journal_mode=WAL")220 await self._conn.execute("PRAGMA synchronous=NORMAL")221 await self._conn.execute("PRAGMA foreign_keys=ON")222 await self._conn.executescript(SCHEMA)223 await self._conn.commit()224225 async def close(self) -> None:226 if self._conn:227 await self._conn.commit()228 await self._conn.close()229 self._conn = None230231 @property232 def conn(self) -> aiosqlite.Connection:233 assert self._conn is not None, "database not connected"234 return self._conn235236 # -- helpers ----------------------------------------------------------237 async def execute(self, sql: str, params: Iterable[Any] = ()) -> int:238 cur = await self.conn.execute(sql, tuple(params))239 await self.conn.commit()240 return cur.lastrowid or 0241242 async def executemany(self, sql: str, rows: Iterable[Iterable[Any]]) -> None:243 await self.conn.executemany(sql, [tuple(r) for r in rows])244 await self.conn.commit()245246 async def fetchone(self, sql: str, params: Iterable[Any] = ()) -> dict | None:247 cur = await self.conn.execute(sql, tuple(params))248 row = await cur.fetchone()249 return dict(row) if row else None250251 async def fetchall(self, sql: str, params: Iterable[Any] = ()) -> list[dict]:252 cur = await self.conn.execute(sql, tuple(params))253 rows = await cur.fetchall()254 return [dict(r) for r in rows]255256 async def scalar(self, sql: str, params: Iterable[Any] = ()) -> Any:257 cur = await self.conn.execute(sql, tuple(params))258 row = await cur.fetchone()259 return row[0] if row else None260261 # -- settings -----------------------------------------------------------262 async def get_setting(self, key: str, default: Any = None) -> Any:263 row = await self.fetchone("SELECT value FROM settings WHERE key=?", (key,))264 if not row:265 return default266 try:267 return json.loads(row["value"])268 except json.JSONDecodeError:269 return row["value"]270271 async def set_setting(self, key: str, value: Any) -> None:272 await self.execute(273 "INSERT INTO settings(key, value, updated_at) VALUES(?,?,?) "274 "ON CONFLICT(key) DO UPDATE SET value=excluded.value, updated_at=excluded.updated_at",275 (key, json.dumps(value), time.time()),276 )277278 async def all_settings(self) -> dict[str, Any]:279 rows = await self.fetchall("SELECT key, value FROM settings")280 out = {}281 for r in rows:282 try:283 out[r["key"]] = json.loads(r["value"])284 except json.JSONDecodeError:285 out[r["key"]] = r["value"]286 return out287288 # -- audit / events -----------------------------------------------------289 async def audit(self, action: str, *, actor: str | None = None, target: str | None = None,290 detail: Any = None, ip: str | None = None) -> None:291 await self.execute(292 "INSERT INTO audit_logs(created_at, actor, action, target, detail, ip) VALUES(?,?,?,?,?,?)",293 (time.time(), actor, action, target, json.dumps(detail) if detail is not None else None, ip),294 )295296 async def model_event(self, model_id: str | None, event: str, detail: Any = None) -> None:297 await self.execute(298 "INSERT INTO model_events(created_at, model_id, event, detail) VALUES(?,?,?,?)",299 (time.time(), model_id, event, json.dumps(detail) if detail is not None else None),300 )301