Workhub / server /db.py
ag235772's picture
files Replacement
e298636
Raw History Blame Contribute Delete
1.5 kB
"""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)