AxonIQ-Testing / api /database.py
ZeroNinerOne's picture
Upload 12 files
e803935 verified
Raw History Blame
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()
@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