Spaces:
Sleeping
Sleeping
Download api/database.py from NeuroCheckAI/neurocheck: direct link, hf CLI and curl.
- Browser
- Download file 21.5 kB
-
https://huggingface.co/spaces/NeuroCheckAI/neurocheck/resolve/main/api/database.py
- Command line
-
hf download hf://spaces/NeuroCheckAI/neurocheck/api/database.py
-
curl -L -o database.py https://huggingface.co/spaces/NeuroCheckAI/neurocheck/resolve/main/api/database.py
21.5 kB
| """ | |
| 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() | |
| 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 | |