""" NeuroCheck — Persistent Database Layer SQLite-backed storage for users, chat sessions, and messages. Tables: users — registered accounts (id, username, email, hashed_password, created_at) chat_sessions — per-user conversation sessions (id, user_id, title, created_at, updated_at) messages — every message in a session (id, session_db_id, role, content, created_at) """ from __future__ import annotations import sqlite3 import os from datetime import datetime, timezone from pathlib import Path from contextlib import contextmanager from typing import List, Dict, Any, Optional # ── DB path ─────────────────────────────────────────────────────────────────── DB_PATH = Path(os.environ.get("NEUROCHECK_DB", "/tmp/neurocheck.db")) def _now() -> str: return datetime.now(timezone.utc).isoformat() @contextmanager def get_conn(): """Thread-safe SQLite connection with WAL mode for concurrency.""" conn = sqlite3.connect(str(DB_PATH), check_same_thread=False) conn.row_factory = sqlite3.Row conn.execute("PRAGMA journal_mode=WAL") conn.execute("PRAGMA foreign_keys=ON") try: yield conn conn.commit() except Exception: conn.rollback() raise finally: conn.close() # ── Schema Init ─────────────────────────────────────────────────────────────── def init_db() -> None: """Create all tables if they don't exist. Safe to call on every startup.""" # Ensure parent directory exists (important for /data/db/ on HuggingFace) DB_PATH.parent.mkdir(parents=True, exist_ok=True) with get_conn() as conn: conn.executescript(""" CREATE TABLE IF NOT EXISTS users ( id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE COLLATE NOCASE, email TEXT NOT NULL UNIQUE COLLATE NOCASE, password_hash TEXT NOT NULL, created_at TEXT NOT NULL, is_admin INTEGER NOT NULL DEFAULT 0 ); -- Add is_admin column if upgrading from older schema -- (safe to run multiple times — ALTER TABLE fails silently via Python) CREATE TABLE IF NOT EXISTS chat_sessions ( id INTEGER PRIMARY KEY AUTOINCREMENT, session_uuid TEXT NOT NULL UNIQUE, user_id INTEGER NOT NULL REFERENCES users(id), title TEXT NOT NULL DEFAULT 'New Chat', created_at TEXT NOT NULL, updated_at TEXT NOT NULL, deleted_at TEXT DEFAULT NULL, ms_tier TEXT DEFAULT NULL, ms_tier_label TEXT DEFAULT NULL, ms_symptoms TEXT DEFAULT NULL, ms_workup TEXT DEFAULT NULL, ms_action TEXT DEFAULT NULL, concluded_at TEXT DEFAULT NULL ); CREATE TABLE IF NOT EXISTS messages ( id INTEGER PRIMARY KEY AUTOINCREMENT, session_uuid TEXT NOT NULL REFERENCES chat_sessions(session_uuid), role TEXT NOT NULL CHECK(role IN ('human','assistant','system')), content TEXT NOT NULL, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_sessions_user ON chat_sessions(user_id); CREATE INDEX IF NOT EXISTS idx_messages_session ON messages(session_uuid); CREATE TABLE IF NOT EXISTS password_reset_otps ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL REFERENCES users(id), email TEXT NOT NULL, otp_hash TEXT NOT NULL, expires_at TEXT NOT NULL, used INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL ); CREATE TABLE IF NOT EXISTS user_logs ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER REFERENCES users(id), username TEXT, action TEXT NOT NULL, detail TEXT DEFAULT NULL, ip_address TEXT DEFAULT NULL, session_uuid TEXT DEFAULT NULL, created_at TEXT NOT NULL ); CREATE INDEX IF NOT EXISTS idx_logs_user ON user_logs(user_id); CREATE INDEX IF NOT EXISTS idx_logs_time ON user_logs(created_at); """) # Auto-backup on every startup — keeps last 7 daily backups _backup_db() def _backup_db() -> None: """Copy the DB to /data/backups/ with a timestamp. Keep last 7 backups.""" import shutil, glob backup_dir = DB_PATH.parent.parent / "backups" try: backup_dir.mkdir(parents=True, exist_ok=True) if not DB_PATH.exists(): return ts = datetime.now(timezone.utc).strftime("%Y%m%d") dest = backup_dir / f"neurocheck_{ts}.db" if not dest.exists(): # one backup per day max shutil.copy2(str(DB_PATH), str(dest)) # Keep only last 7 backups backups = sorted(glob.glob(str(backup_dir / "neurocheck_*.db"))) for old in backups[:-7]: Path(old).unlink(missing_ok=True) except Exception: pass # never crash startup due to backup failure # ── User CRUD ───────────────────────────────────────────────────────────────── def create_user(username: str, email: str, password_hash: str) -> int: """Insert a new user. Returns the new user's integer id.""" with get_conn() as conn: cur = conn.execute( "INSERT INTO users (username, email, password_hash, created_at) VALUES (?,?,?,?)", (username.strip(), email.strip().lower(), password_hash, _now()), ) return cur.lastrowid def get_user_by_username(username: str) -> Optional[Dict[str, Any]]: with get_conn() as conn: row = conn.execute( "SELECT * FROM users WHERE username = ? COLLATE NOCASE", (username,) ).fetchone() return dict(row) if row else None def get_user_by_email(email: str) -> Optional[Dict[str, Any]]: with get_conn() as conn: row = conn.execute( "SELECT * FROM users WHERE email = ? COLLATE NOCASE", (email.strip().lower(),) ).fetchone() return dict(row) if row else None def get_user_by_id(user_id: int) -> Optional[Dict[str, Any]]: with get_conn() as conn: row = conn.execute("SELECT * FROM users WHERE id = ?", (user_id,)).fetchone() return dict(row) if row else None # ── Session CRUD ────────────────────────────────────────────────────────────── def create_db_session(session_uuid: str, user_id: int, title: str = "New Chat") -> None: """Persist a new chat session linked to a user.""" now = _now() with get_conn() as conn: conn.execute( "INSERT OR IGNORE INTO chat_sessions (session_uuid, user_id, title, created_at, updated_at) " "VALUES (?,?,?,?,?)", (session_uuid, user_id, title, now, now), ) def get_user_sessions(user_id: int) -> List[Dict[str, Any]]: """Return all active (non-deleted) sessions for a user, newest first.""" with get_conn() as conn: rows = conn.execute( "SELECT * FROM chat_sessions WHERE user_id = ? AND deleted_at IS NULL ORDER BY updated_at DESC", (user_id,), ).fetchall() return [dict(r) for r in rows] def update_session_title(session_uuid: str, title: str) -> None: now = _now() with get_conn() as conn: conn.execute( "UPDATE chat_sessions SET title=?, updated_at=? WHERE session_uuid=?", (title[:80], now, session_uuid), ) def touch_session(session_uuid: str) -> None: """Update the updated_at timestamp on a session.""" with get_conn() as conn: conn.execute( "UPDATE chat_sessions SET updated_at=? WHERE session_uuid=?", (_now(), session_uuid), ) def delete_db_session(session_uuid: str) -> None: """Soft-delete — marks session as deleted but NEVER removes data from disk.""" with get_conn() as conn: conn.execute( "UPDATE chat_sessions SET deleted_at=? WHERE session_uuid=?", (_now(), session_uuid) ) def get_session_owner(session_uuid: str) -> Optional[int]: """Return user_id that owns this session, or None.""" with get_conn() as conn: row = conn.execute( "SELECT user_id FROM chat_sessions WHERE session_uuid=?", (session_uuid,) ).fetchone() return row["user_id"] if row else None # ── Message CRUD ────────────────────────────────────────────────────────────── def save_message(session_uuid: str, role: str, content: str) -> int: """Append a message to a session. Returns new message id.""" now = _now() with get_conn() as conn: cur = conn.execute( "INSERT INTO messages (session_uuid, role, content, created_at) VALUES (?,?,?,?)", (session_uuid, role, content, now), ) # touch session updated_at conn.execute( "UPDATE chat_sessions SET updated_at=? WHERE session_uuid=?", (now, session_uuid), ) return cur.lastrowid def get_messages(session_uuid: str) -> List[Dict[str, Any]]: """Return all messages for a session in chronological order.""" with get_conn() as conn: rows = conn.execute( "SELECT role, content, created_at FROM messages " "WHERE session_uuid=? ORDER BY id ASC", (session_uuid,), ).fetchall() return [dict(r) for r in rows] def get_message_count(session_uuid: str) -> int: with get_conn() as conn: row = conn.execute( "SELECT COUNT(*) as n FROM messages WHERE session_uuid=?", (session_uuid,) ).fetchone() return row["n"] if row else 0 # ── Admin helpers ───────────────────────────────────────────────────────────── def migrate_add_admin_column() -> None: """Safe migration — adds is_admin and conclusion columns to existing databases.""" cols = [ "ALTER TABLE users ADD COLUMN is_admin INTEGER NOT NULL DEFAULT 0", "ALTER TABLE chat_sessions ADD COLUMN ms_tier TEXT DEFAULT NULL", "ALTER TABLE chat_sessions ADD COLUMN ms_tier_label TEXT DEFAULT NULL", "ALTER TABLE chat_sessions ADD COLUMN ms_symptoms TEXT DEFAULT NULL", "ALTER TABLE chat_sessions ADD COLUMN ms_workup TEXT DEFAULT NULL", "ALTER TABLE chat_sessions ADD COLUMN ms_action TEXT DEFAULT NULL", "ALTER TABLE chat_sessions ADD COLUMN concluded_at TEXT DEFAULT NULL", ] for sql in cols: try: with get_conn() as conn: conn.execute(sql) except Exception: pass # Column already exists — safe to ignore def set_admin(username: str, is_admin: bool = True) -> bool: """Grant or revoke admin access for a user.""" with get_conn() as conn: cur = conn.execute( "UPDATE users SET is_admin=? WHERE username=? COLLATE NOCASE", (1 if is_admin else 0, username) ) return cur.rowcount > 0 def get_all_users_admin() -> list: """Return all users with session and message counts for admin panel.""" with get_conn() as conn: rows = conn.execute(""" SELECT u.id, u.username, u.email, u.created_at, u.is_admin, COUNT(DISTINCT cs.id) AS session_count, COUNT(DISTINCT m.id) AS message_count, MAX(CASE WHEN cs.ms_tier='CRITICAL_EMERGENCY' THEN 5 WHEN cs.ms_tier='HIGH' THEN 4 WHEN cs.ms_tier='MODERATE' THEN 3 WHEN cs.ms_tier='WATCH' THEN 2 WHEN cs.ms_tier='LOW' THEN 1 ELSE 0 END) AS highest_risk_score, (SELECT cs2.ms_tier FROM chat_sessions cs2 WHERE cs2.user_id=u.id AND cs2.ms_tier IS NOT NULL ORDER BY cs2.concluded_at DESC LIMIT 1) AS latest_tier FROM users u LEFT JOIN chat_sessions cs ON cs.user_id = u.id AND cs.deleted_at IS NULL LEFT JOIN messages m ON m.session_uuid = cs.session_uuid GROUP BY u.id ORDER BY u.created_at DESC """).fetchall() return [dict(r) for r in rows] def get_all_sessions_admin() -> tuple: """Return total session and message counts across all users.""" with get_conn() as conn: s = conn.execute("SELECT COUNT(*) FROM chat_sessions WHERE deleted_at IS NULL").fetchone()[0] m = conn.execute("SELECT COUNT(*) FROM messages").fetchone()[0] return s, m def get_user_sessions_with_messages(user_id: int) -> list: """Return all sessions for a user including full message content and conclusion.""" import json as _json with get_conn() as conn: sessions = conn.execute( "SELECT * FROM chat_sessions WHERE user_id=? AND deleted_at IS NULL ORDER BY created_at DESC", (user_id,) ).fetchall() result = [] for s in sessions: s = dict(s) msgs = conn.execute( "SELECT role, content, created_at FROM messages WHERE session_uuid=? ORDER BY id ASC", (s["session_uuid"],) ).fetchall() s["messages"] = [dict(m) for m in msgs] # Attach parsed conclusion if s.get("ms_tier"): s["conclusion"] = { "tier": s["ms_tier"], "tier_label": s["ms_tier_label"], "symptoms": _json.loads(s.get("ms_symptoms") or "[]"), "workup": _json.loads(s.get("ms_workup") or "[]"), "action": s.get("ms_action", ""), "concluded_at": s.get("concluded_at"), } else: s["conclusion"] = None result.append(s) return result def save_conclusion(session_uuid: str, tier: str, tier_label: str, symptoms: list, workup: list, action: str) -> None: """Save the MS classification conclusion to the session record.""" import json as _json now = _now() with get_conn() as conn: conn.execute(""" UPDATE chat_sessions SET ms_tier=?, ms_tier_label=?, ms_symptoms=?, ms_workup=?, ms_action=?, concluded_at=?, updated_at=? WHERE session_uuid=? """, ( tier, tier_label, _json.dumps(symptoms), _json.dumps(workup), action, now, now, session_uuid, )) def get_conclusion(session_uuid: str) -> dict | None: """Return the saved conclusion for a session, or None.""" import json as _json with get_conn() as conn: row = conn.execute( "SELECT ms_tier, ms_tier_label, ms_symptoms, ms_workup, ms_action, concluded_at " "FROM chat_sessions WHERE session_uuid=?", (session_uuid,) ).fetchone() if not row or not row["ms_tier"]: return None return { "tier": row["ms_tier"], "tier_label": row["ms_tier_label"], "symptoms": _json.loads(row["ms_symptoms"] or "[]"), "workup": _json.loads(row["ms_workup"] or "[]"), "action": row["ms_action"], "concluded_at": row["concluded_at"], } # ── User Activity Logging ───────────────────────────────────────────────────── def log_action( action: str, user_id: int | None = None, username: str | None = None, detail: str | None = None, ip_address: str | None = None, session_uuid: str | None = None, ) -> None: """Log a user action. Never raises — logging must not crash the app.""" try: DB_PATH.parent.mkdir(parents=True, exist_ok=True) with get_conn() as conn: conn.execute( "INSERT INTO user_logs (user_id, username, action, detail, ip_address, session_uuid, created_at) " "VALUES (?,?,?,?,?,?,?)", (user_id, username, action, detail, ip_address, session_uuid, _now()), ) except Exception: pass def get_user_logs(user_id: int, limit: int = 200) -> list: """Return recent activity logs for a specific user.""" with get_conn() as conn: rows = conn.execute( "SELECT action, detail, ip_address, session_uuid, created_at " "FROM user_logs WHERE user_id=? ORDER BY id DESC LIMIT ?", (user_id, limit) ).fetchall() return [dict(r) for r in rows] def get_all_logs(limit: int = 500) -> list: """Return recent activity logs across all users (admin view).""" with get_conn() as conn: rows = conn.execute( "SELECT ul.created_at, ul.username, ul.action, ul.detail, ul.ip_address, ul.session_uuid " "FROM user_logs ul ORDER BY ul.id DESC LIMIT ?", (limit,) ).fetchall() return [dict(r) for r in rows] def migrate_add_logs_table() -> None: """Safe migration — creates user_logs table if not exists.""" try: with get_conn() as conn: conn.execute(""" CREATE TABLE IF NOT EXISTS user_logs ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER REFERENCES users(id), username TEXT, action TEXT NOT NULL, detail TEXT DEFAULT NULL, ip_address TEXT DEFAULT NULL, session_uuid TEXT DEFAULT NULL, created_at TEXT NOT NULL ) """) conn.execute("CREATE INDEX IF NOT EXISTS idx_logs_user ON user_logs(user_id)") conn.execute("CREATE INDEX IF NOT EXISTS idx_logs_time ON user_logs(created_at)") except Exception: pass # ── Password Reset OTP ──────────────────────────────────────────────────────── def save_otp(user_id: int, email: str, otp_hash: str, expires_at: str) -> None: """Save a new OTP for password reset. Invalidates any previous OTPs.""" now = _now() with get_conn() as conn: # Invalidate old OTPs for this user conn.execute("UPDATE password_reset_otps SET used=1 WHERE user_id=?", (user_id,)) conn.execute( "INSERT INTO password_reset_otps (user_id, email, otp_hash, expires_at, used, created_at) " "VALUES (?,?,?,?,0,?)", (user_id, email, otp_hash, expires_at, now) ) def get_valid_otp(email: str) -> dict | None: """Get the latest unused, unexpired OTP for an email.""" from datetime import datetime, timezone now = datetime.now(timezone.utc).isoformat() with get_conn() as conn: row = conn.execute( "SELECT * FROM password_reset_otps " "WHERE email=? AND used=0 AND expires_at > ? " "ORDER BY id DESC LIMIT 1", (email.lower(), now) ).fetchone() return dict(row) if row else None def mark_otp_used(otp_id: int) -> None: with get_conn() as conn: conn.execute("UPDATE password_reset_otps SET used=1 WHERE id=?", (otp_id,)) def update_user_password(user_id: int, new_hash: str) -> None: with get_conn() as conn: conn.execute("UPDATE users SET password_hash=? WHERE id=?", (new_hash, user_id)) def migrate_add_otp_table() -> None: """Safe migration — adds OTP table to existing databases.""" try: with get_conn() as conn: conn.execute(""" CREATE TABLE IF NOT EXISTS password_reset_otps ( id INTEGER PRIMARY KEY AUTOINCREMENT, user_id INTEGER NOT NULL REFERENCES users(id), email TEXT NOT NULL, otp_hash TEXT NOT NULL, expires_at TEXT NOT NULL, used INTEGER NOT NULL DEFAULT 0, created_at TEXT NOT NULL ) """) except Exception: pass