acra-backend / code /local_store.py
Zeref8425's picture
fix(storage): require owned image access and expire files
4065a20
Raw History Blame Contribute Delete
11.1 kB
"""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'],))