"""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()))