"""Local SQLite + filesystem persistence for ACRA when Supabase is not configured.""" from __future__ import annotations import json import shutil import sqlite3 from contextlib import contextmanager from datetime import datetime, timedelta, timezone from pathlib import Path from typing import Any, Dict, List, Optional def init_paths(data_dir: Path) -> tuple[Path, Path]: static_dir = data_dir / "static" static_dir.mkdir(parents=True, exist_ok=True) (static_dir / "jobs").mkdir(exist_ok=True) (static_dir / "test-runs").mkdir(exist_ok=True) db_path = data_dir / "jobs.db" init_db(db_path) return static_dir, db_path def init_db(db_path: Path) -> None: with _connect(db_path) as con: con.executescript( """ CREATE TABLE IF NOT EXISTS jobs ( job_id TEXT PRIMARY KEY, user_id TEXT NOT NULL, cvd_type TEXT NOT NULL, severity REAL NOT NULL, conf_threshold REAL NOT NULL, use_segmentation INTEGER NOT NULL DEFAULT 1, seg_soft REAL NOT NULL DEFAULT 3.5, n_clusters INTEGER, seg_clusters_per_roi INTEGER DEFAULT 3, created_at TEXT NOT NULL, expires_at TEXT NOT NULL, original_url TEXT NOT NULL, corrected_url TEXT NOT NULL, metrics TEXT NOT NULL, boxes TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_jobs_user ON jobs(user_id); CREATE INDEX IF NOT EXISTS idx_jobs_expires ON jobs(expires_at); CREATE TABLE IF NOT EXISTS test_runs ( run_id TEXT PRIMARY KEY, user_id TEXT NOT NULL, filename TEXT, cvd_type TEXT NOT NULL, severity REAL NOT NULL, conf_threshold REAL NOT NULL, use_segmentation INTEGER NOT NULL DEFAULT 1, seg_soft REAL NOT NULL DEFAULT 3.5, n_clusters INTEGER, seg_clusters_per_roi INTEGER DEFAULT 3, created_at TEXT NOT NULL, original_url TEXT NOT NULL, corrected_url TEXT NOT NULL, metrics TEXT NOT NULL, boxes TEXT NOT NULL, de_improvement REAL, conflict_resolution REAL, naturalness REAL, conflicts_found INTEGER, boxes_detected INTEGER, inference_ms REAL, pass_de INTEGER, pass_resolution INTEGER, pass_naturalness INTEGER ); CREATE INDEX IF NOT EXISTS idx_runs_user ON test_runs(user_id); """ ) @contextmanager def _connect(db_path: Path): con = sqlite3.connect(str(db_path), check_same_thread=False) con.row_factory = sqlite3.Row try: yield con con.commit() except Exception: con.rollback() raise finally: con.close() def public_url(base_url: str, storage_path: str) -> str: return f"{base_url.rstrip('/')}/static/{storage_path.lstrip('/')}" def save_image(static_dir: Path, storage_path: str, data: bytes) -> None: dest = static_dir / storage_path dest.parent.mkdir(parents=True, exist_ok=True) dest.write_bytes(data) def delete_images(static_dir: Path, prefix: str) -> None: folder = static_dir / prefix try: shutil.rmtree(folder) except FileNotFoundError: pass # A prior cleanup or concurrent explicit deletion already removed it. def _encode_job(row: dict) -> dict: out = dict(row) out["use_segmentation"] = 1 if row.get("use_segmentation", True) else 0 out["metrics"] = json.dumps(row.get("metrics") or {}) out["boxes"] = json.dumps(row.get("boxes") or []) return out def _decode_job(row: sqlite3.Row) -> dict: d = dict(row) d["use_segmentation"] = bool(d.get("use_segmentation", 1)) d["metrics"] = json.loads(d.get("metrics") or "{}") d["boxes"] = json.loads(d.get("boxes") or "[]") return d def _encode_run(row: dict) -> dict: out = _encode_job(row) for key in ("pass_de", "pass_resolution", "pass_naturalness"): if key in out: out[key] = 1 if out[key] else 0 return out def _decode_run(row: sqlite3.Row) -> dict: d = _decode_job(row) for key in ("pass_de", "pass_resolution", "pass_naturalness"): if key in d: d[key] = bool(d[key]) return d def insert_job(db_path: Path, row: dict) -> None: encoded = _encode_job(row) with _connect(db_path) as con: con.execute( """ INSERT INTO jobs ( job_id, user_id, cvd_type, severity, conf_threshold, use_segmentation, seg_soft, n_clusters, seg_clusters_per_roi, created_at, expires_at, original_url, corrected_url, metrics, boxes ) VALUES ( :job_id, :user_id, :cvd_type, :severity, :conf_threshold, :use_segmentation, :seg_soft, :n_clusters, :seg_clusters_per_roi, :created_at, :expires_at, :original_url, :corrected_url, :metrics, :boxes ) """, encoded, ) def insert_test_run(db_path: Path, row: dict) -> None: encoded = _encode_run(row) with _connect(db_path) as con: con.execute( """ INSERT INTO test_runs ( run_id, user_id, filename, cvd_type, severity, conf_threshold, use_segmentation, seg_soft, n_clusters, seg_clusters_per_roi, created_at, original_url, corrected_url, metrics, boxes, de_improvement, conflict_resolution, naturalness, conflicts_found, boxes_detected, inference_ms, pass_de, pass_resolution, pass_naturalness ) VALUES ( :run_id, :user_id, :filename, :cvd_type, :severity, :conf_threshold, :use_segmentation, :seg_soft, :n_clusters, :seg_clusters_per_roi, :created_at, :original_url, :corrected_url, :metrics, :boxes, :de_improvement, :conflict_resolution, :naturalness, :conflicts_found, :boxes_detected, :inference_ms, :pass_de, :pass_resolution, :pass_naturalness ) """, encoded, ) def list_jobs(db_path: Path, user_id: str) -> List[dict]: with _connect(db_path) as con: rows = con.execute( "SELECT * FROM jobs WHERE user_id = ? ORDER BY created_at DESC", (user_id,), ).fetchall() return [_decode_job(r) for r in rows] def get_job(db_path: Path, job_id: str, user_id: str) -> Optional[dict]: with _connect(db_path) as con: row = con.execute( "SELECT * FROM jobs WHERE job_id = ? AND user_id = ?", (job_id, user_id), ).fetchone() return _decode_job(row) if row else None def delete_job(db_path: Path, job_id: str) -> None: with _connect(db_path) as con: con.execute("DELETE FROM jobs WHERE job_id = ?", (job_id,)) def list_all_test_runs(db_path: Path, user_id: str) -> List[dict]: with _connect(db_path) as con: rows = con.execute( "SELECT * FROM test_runs WHERE user_id = ? ORDER BY created_at DESC", (user_id,), ).fetchall() return [_decode_run(r) for r in rows] def list_test_runs(db_path: Path, user_id: str, limit: int, offset: int) -> List[dict]: with _connect(db_path) as con: rows = con.execute( """ SELECT * FROM test_runs WHERE user_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ? """, (user_id, limit, offset), ).fetchall() return [_decode_run(r) for r in rows] def get_test_run(db_path: Path, run_id: str, user_id: str) -> Optional[dict]: with _connect(db_path) as con: row = con.execute( "SELECT * FROM test_runs WHERE run_id = ? AND user_id = ?", (run_id, user_id), ).fetchone() return _decode_run(row) if row else None def delete_test_run(db_path: Path, run_id: str) -> None: with _connect(db_path) as con: con.execute("DELETE FROM test_runs WHERE run_id = ?", (run_id,)) def clear_test_runs(db_path: Path, user_id: str) -> List[str]: with _connect(db_path) as con: rows = con.execute( "SELECT run_id FROM test_runs WHERE user_id = ?", (user_id,), ).fetchall() ids = [r["run_id"] for r in rows] con.execute("DELETE FROM test_runs WHERE user_id = ?", (user_id,)) return ids def analytics_rows(db_path: Path, user_id: str) -> List[dict]: with _connect(db_path) as con: rows = con.execute( """ SELECT de_improvement, conflict_resolution, naturalness, pass_de, pass_resolution, pass_naturalness FROM test_runs WHERE user_id = ? """, (user_id,), ).fetchall() out = [] for r in rows: d = dict(r) for key in ("pass_de", "pass_resolution", "pass_naturalness"): d[key] = bool(d.get(key)) out.append(d) return out def analytics_page(db_path: Path, user_id: str, limit: int, offset: int) -> List[dict]: with _connect(db_path) as con: rows = con.execute( """ SELECT run_id, filename, cvd_type, severity, conf_threshold, created_at, metrics, de_improvement, conflict_resolution, naturalness, pass_de, pass_resolution, pass_naturalness, conflicts_found, boxes_detected, inference_ms FROM test_runs WHERE user_id = ? ORDER BY created_at DESC LIMIT ? OFFSET ? """, (user_id, limit, offset), ).fetchall() out = [] for r in rows: d = dict(r) d["metrics"] = json.loads(d.get("metrics") or "{}") for key in ("pass_de", "pass_resolution", "pass_naturalness"): d[key] = bool(d.get(key)) out.append(d) return out def purge_expired(db_path: Path, static_dir: Path) -> None: now = datetime.now(timezone.utc).isoformat() with _connect(db_path) as con: rows = con.execute( "SELECT job_id FROM jobs WHERE expires_at <= ?", (now,), ).fetchall() for r in rows: delete_images(static_dir, f"jobs/{r['job_id']}") con.execute("DELETE FROM jobs WHERE expires_at <= ?", (now,)) cutoff = (datetime.now(timezone.utc) - timedelta(hours=24)).isoformat() runs = con.execute( "SELECT run_id FROM test_runs WHERE created_at <= ? AND original_url != ''", (cutoff,), ).fetchall() for row in runs: delete_images(static_dir, f"test-runs/{row['run_id']}") con.execute("UPDATE test_runs SET original_url = '', corrected_url = '' WHERE run_id = ?", (row['run_id'],))