SCQC / server /db.py
raianand's picture
Upload folder using huggingface_hub
e0d876f verified
Raw History Blame Contribute Delete
4.85 kB
"""SQLite storage. One file, WAL mode; adequate for a field hub or a demo
server. Speaker records hold a pseudonymous ID and optional self-reported
subgroup fields only; names and payment details belong in a separate
restricted vault keyed by speaker_id (deck slide 17), not here.
"""
from __future__ import annotations
import json
import sqlite3
import threading
import time
from contextlib import contextmanager
SCHEMA = """
CREATE TABLE IF NOT EXISTS projects (
project_id TEXT PRIMARY KEY, manifest TEXT NOT NULL, created_at REAL NOT NULL);
CREATE TABLE IF NOT EXISTS tasks (
project_id TEXT NOT NULL, task_id TEXT NOT NULL, ord INTEGER NOT NULL,
task_type TEXT NOT NULL, language TEXT NOT NULL,
PRIMARY KEY (project_id, task_id));
CREATE TABLE IF NOT EXISTS speakers (
speaker_id TEXT PRIMARY KEY, project_id TEXT NOT NULL, consent_version TEXT NOT NULL,
consented_at REAL NOT NULL, language TEXT, gender TEXT, age_group TEXT, region TEXT,
condition TEXT, withdrawn_at REAL);
CREATE TABLE IF NOT EXISTS recordings (
recording_id TEXT PRIMARY KEY, client_upload_id TEXT UNIQUE, project_id TEXT NOT NULL,
task_id TEXT NOT NULL, speaker_id TEXT NOT NULL, attempt INTEGER NOT NULL,
status TEXT NOT NULL, auto_decision TEXT NOT NULL, required_reviewers INTEGER NOT NULL,
audio_path TEXT, qc TEXT NOT NULL, client_meta TEXT, created_at REAL NOT NULL,
superseded_by TEXT, final_at REAL, condition TEXT);
CREATE INDEX IF NOT EXISTS rec_by_task ON recordings(project_id, task_id, status);
CREATE INDEX IF NOT EXISTS rec_by_speaker ON recordings(speaker_id, task_id);
CREATE TABLE IF NOT EXISTS reviews (
review_id INTEGER PRIMARY KEY AUTOINCREMENT, recording_id TEXT NOT NULL, reviewer_id TEXT NOT NULL,
verdict TEXT NOT NULL CHECK (verdict IN ('accept','reject')), issues TEXT, scores TEXT,
seconds_spent REAL, created_at REAL NOT NULL, UNIQUE (recording_id, reviewer_id));
CREATE TABLE IF NOT EXISTS ledger (
entry_id INTEGER PRIMARY KEY AUTOINCREMENT, speaker_id TEXT NOT NULL, project_id TEXT NOT NULL,
recording_id TEXT NOT NULL UNIQUE, amount REAL NOT NULL, currency TEXT NOT NULL,
status TEXT NOT NULL DEFAULT 'earned', created_at REAL NOT NULL);
CREATE TABLE IF NOT EXISTS payout_batches (
batch_id TEXT PRIMARY KEY, project_id TEXT NOT NULL, created_at REAL NOT NULL, created_by TEXT,
status TEXT NOT NULL, n INTEGER NOT NULL, total REAL NOT NULL, currency TEXT NOT NULL,
settled_at REAL, simulated INTEGER NOT NULL DEFAULT 1);
CREATE TABLE IF NOT EXISTS surveys (
speaker_id TEXT NOT NULL, project_id TEXT NOT NULL, instrument TEXT NOT NULL, answers TEXT NOT NULL,
score REAL, created_at REAL NOT NULL, PRIMARY KEY (speaker_id, instrument));
CREATE TABLE IF NOT EXISTS audit_log (
id INTEGER PRIMARY KEY AUTOINCREMENT, actor TEXT, action TEXT NOT NULL, target TEXT,
detail TEXT, at REAL NOT NULL);
"""
class DB:
def __init__(self, path: str):
self.path = path
self._lock = threading.RLock()
self._conn = sqlite3.connect(path, check_same_thread=False, isolation_level=None)
self._conn.row_factory = sqlite3.Row
self._conn.execute("PRAGMA journal_mode=WAL")
self._conn.execute("PRAGMA foreign_keys=ON")
self._conn.executescript(SCHEMA)
# Additive migrations for databases created by earlier versions.
for table, col, typ in (("speakers", "condition", "TEXT"), ("recordings", "condition", "TEXT"),
("speakers", "consent_sha", "TEXT"), ("speakers", "is_test", "INTEGER DEFAULT 0"),
("projects", "ethics_key", "TEXT"), ("projects", "ethics_scope", "TEXT"),
("ledger", "batch_id", "TEXT"), ("ledger", "txn_id", "TEXT"), ("ledger", "paid_at", "REAL")):
cols = {r[1] for r in self._conn.execute(f"PRAGMA table_info({table})")}
if col not in cols:
self._conn.execute(f"ALTER TABLE {table} ADD COLUMN {col} {typ}")
@contextmanager
def tx(self):
with self._lock:
self._conn.execute("BEGIN IMMEDIATE")
try:
yield self._conn
self._conn.execute("COMMIT")
except Exception:
self._conn.execute("ROLLBACK")
raise
def q(self, sql: str, params=()) -> list[sqlite3.Row]:
with self._lock:
return self._conn.execute(sql, params).fetchall()
def one(self, sql: str, params=()):
rows = self.q(sql, params)
return rows[0] if rows else None
def log(self, conn, actor: str, action: str, target: str = "", detail: dict | None = None):
conn.execute("INSERT INTO audit_log(actor, action, target, detail, at) VALUES (?,?,?,?,?)",
(actor, action, target, json.dumps(detail or {}, ensure_ascii=False), time.time()))