chatnct / mainAgent /db /database.py
Mina Diaa
Deploy to Hugging Face Space
3c3b1f4
Raw History Blame Contribute Delete
6.51 kB
"""
database.py - Database Connection Manager
This file manages the connection to our PostgreSQL database (hosted on Supabase).
Instead of opening a new database connection every time someone makes a request,
we use a "connection pool" - a set of pre-opened connections that get reused.
This is much faster and more efficient.
Main functions:
- get_connection() : Borrow a database connection from the pool
- release_connection() : Return a connection back to the pool when done
- run_migrations() : Create any missing database tables on startup
"""
import os
import psycopg2
from psycopg2.extras import DictCursor
from psycopg2.pool import ThreadedConnectionPool
from dotenv import load_dotenv
# ── Load Environment Variables ─────────────────────────────────────────
# Navigate from this file's location up to the project root to find .env
# Path: mainAgent/db/database.py -> mainAgent/db -> mainAgent -> project root
_DIR = os.path.dirname(__file__)
_MAIN_AGENT_DIR = os.path.dirname(_DIR)
_ROOT_DIR = os.path.dirname(_MAIN_AGENT_DIR)
_env_path = os.path.join(_ROOT_DIR, ".env")
if os.path.exists(_env_path):
load_dotenv(_env_path) # Load from project root .env file
else:
load_dotenv() # Fallback: look for .env in current directory
# ── Connection Pool ────────────────────────────────────────────────────
# The pool starts empty (None) and gets created on first use.
# It maintains between 1 and 10 connections to the database.
_pool = None
def _get_pool():
"""
Create the connection pool if it doesn't exist yet (lazy initialization).
Reads the DATABASE_URL from environment variables to know where to connect.
"""
global _pool
if _pool is None:
db_url = os.getenv("DATABASE_URL")
if not db_url:
raise ValueError("DATABASE_URL is not set in the environment variables.")
# ThreadedConnectionPool is thread-safe, meaning multiple requests
# can safely borrow connections at the same time.
# min=1 connection, max=10 connections, DictCursor returns rows as dicts
_pool = ThreadedConnectionPool(1, 10, db_url, cursor_factory=DictCursor)
return _pool
def get_connection():
"""Borrow a database connection from the pool. Remember to release it when done!"""
return _get_pool().getconn()
def release_connection(conn):
"""
Return a connection back to the pool so it can be reused.
If something goes wrong, close the connection entirely as a fallback.
"""
try:
_get_pool().putconn(conn)
except Exception:
try:
conn.close()
except Exception:
pass
# ── Database Schema (Tables) ──────────────────────────────────────────
# These SQL statements create the tables our app needs.
# "IF NOT EXISTS" means they're safe to run multiple times - they won't
# duplicate tables that already exist.
_MIGRATIONS = """
-- Chat sessions: Each conversation a student starts with the AI
CREATE TABLE IF NOT EXISTS chat_sessions (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
student_id UUID NOT NULL REFERENCES profiles(id) ON DELETE CASCADE,
title TEXT NOT NULL DEFAULT 'New Chat',
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_chat_sessions_student ON chat_sessions(student_id);
-- Chat messages: Individual messages within a chat session
CREATE TABLE IF NOT EXISTS chat_messages (
id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
session_id UUID NOT NULL REFERENCES chat_sessions(id) ON DELETE CASCADE,
role TEXT NOT NULL CHECK (role IN ('user', 'bot')),
content TEXT NOT NULL,
created_at TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_chat_messages_session ON chat_messages(session_id, created_at);
-- QR tokens: One-time-use tokens embedded in attendance QR codes
CREATE TABLE IF NOT EXISTS qr_tokens (
id SERIAL PRIMARY KEY,
session_id INTEGER NOT NULL,
token TEXT NOT NULL UNIQUE,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
used BOOLEAN NOT NULL DEFAULT false
);
CREATE INDEX IF NOT EXISTS idx_qr_tokens_token ON qr_tokens(token);
CREATE INDEX IF NOT EXISTS idx_qr_tokens_session ON qr_tokens(session_id);
-- Rate limiting: Prevents students from spamming attendance attempts
CREATE TABLE IF NOT EXISTS rate_limits (
id SERIAL PRIMARY KEY,
student_id TEXT NOT NULL,
session_id INTEGER NOT NULL,
action TEXT NOT NULL,
attempts INTEGER NOT NULL DEFAULT 1,
blocked_until TIMESTAMPTZ,
last_attempt TIMESTAMPTZ NOT NULL DEFAULT now()
);
CREATE INDEX IF NOT EXISTS idx_rate_limits_lookup ON rate_limits(student_id, session_id, action);
-- Device binding: Links a student to a specific device per session
-- Prevents one person from marking attendance for multiple students
CREATE TABLE IF NOT EXISTS device_bindings (
id SERIAL PRIMARY KEY,
student_id TEXT NOT NULL,
session_id INTEGER NOT NULL,
ip_address TEXT,
user_agent TEXT,
device_fingerprint TEXT,
created_at TIMESTAMPTZ NOT NULL DEFAULT now(),
UNIQUE(student_id, session_id)
);
-- Speed up lookups on existing tables (attendance & students)
CREATE INDEX IF NOT EXISTS idx_attendance_student_session ON attendance(student_id, session_id);
CREATE INDEX IF NOT EXISTS idx_attendance_sessions_code ON attendance_sessions(session_code);
CREATE INDEX IF NOT EXISTS idx_attendance_sessions_active ON attendance_sessions(is_active, course_id);
CREATE INDEX IF NOT EXISTS idx_profiles_id ON profiles(id);
"""
def run_migrations():
"""
Create all required database tables if they don't already exist.
This runs once when the server starts up.
Safe to run multiple times (idempotent) - won't break existing data.
"""
conn = get_connection()
try:
cur = conn.cursor()
cur.execute(_MIGRATIONS)
conn.commit()
print(" Database migrations applied.")
except Exception as e:
# If something goes wrong, undo any partial changes
conn.rollback()
print(f"[WARNING] Migration warning: {e}")
finally:
# Always return the connection to the pool, even if there was an error
release_connection(conn)