"""Tiny SQLite store. Holds ONLY accounts, devices and an audit trail - never work data.""" import os, sqlite3, threading, time from security import DATA_DIR PATH = os.path.join(DATA_DIR, "accounts.db") _lock = threading.RLock() def conn(): os.makedirs(DATA_DIR, exist_ok=True) c = sqlite3.connect(PATH, timeout=10) c.row_factory = sqlite3.Row return c def init(): with _lock, conn() as c: c.executescript(""" CREATE TABLE IF NOT EXISTS users( id INTEGER PRIMARY KEY, email TEXT UNIQUE NOT NULL, name TEXT, totp_secret TEXT, totp_confirmed INTEGER DEFAULT 0, fail_count INTEGER DEFAULT 0, locked_until REAL DEFAULT 0, last_step INTEGER DEFAULT 0, created REAL, last_login REAL); CREATE TABLE IF NOT EXISTS devices( id INTEGER PRIMARY KEY, user_id INTEGER NOT NULL, token_hash TEXT UNIQUE NOT NULL, label TEXT, created REAL, last_seen REAL, revoked INTEGER DEFAULT 0); CREATE TABLE IF NOT EXISTS audit(ts REAL, email TEXT, event TEXT, ip TEXT, detail TEXT); """) def q1(sql, *a): with _lock, conn() as c: return c.execute(sql, a).fetchone() def qa(sql, *a): with _lock, conn() as c: return c.execute(sql, a).fetchall() def ex(sql, *a): with _lock, conn() as c: cur = c.execute(sql, a) return cur.lastrowid def audit(email, event, ip="", detail=""): ex("INSERT INTO audit VALUES(?,?,?,?,?)", time.time(), email, event, ip, detail)