Download server/db.py from raianand/SCQC: direct link, hf CLI and curl.
- Browser
- Download file 4.85 kB
-
https://huggingface.co/spaces/raianand/SCQC/resolve/main/server/db.py
- Command line
-
hf download hf://spaces/raianand/SCQC/server/db.py
-
curl -L -o db.py https://huggingface.co/spaces/raianand/SCQC/resolve/main/server/db.py
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}") | |
| 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())) | |