Add database management: Supabase persistence, teacher profiles, admin dashboard

Dockerfile

@@ -4,7 +4,7 @@
 # =============================================================================
 # Stage 1: Build React Frontend
 # =============================================================================
-FROM node:20-slim AS frontend-builder
+FROM node:22-slim AS frontend-builder
 
 WORKDIR /app/frontend
 
@@ -47,6 +47,10 @@ RUN pip install --no-cache-dir --upgrade pip && \
 # Copy backend code
 COPY --chown=user chatkit/backend/app ./app
 
+# Copy Alembic migration config (applied on startup)
+COPY --chown=user chatkit/backend/alembic.ini ./alembic.ini
+COPY --chown=user chatkit/backend/alembic ./alembic
+
 # Copy built frontend from Stage 1
 COPY --from=frontend-builder --chown=user /app/frontend/dist ./static
 
@@ -57,5 +61,6 @@ EXPOSE 7860
 HEALTHCHECK --interval=30s --timeout=10s --start-period=5s --retries=3 \
     CMD python -c "import urllib.request; urllib.request.urlopen('http://localhost:7860/health')"
 
-# Run the server
-CMD ["uvicorn", "app.main:app", "--host", "0.0.0.0", "--port", "7860"]
+# Apply DB migrations (Supabase) then run the server.
+# Migrations are a no-op for non-Postgres providers.
+CMD ["sh", "-c", "alembic upgrade head && uvicorn app.main:app --host 0.0.0.0 --port 7860"]

chatkit/backend/DATABASE.md

@@ -0,0 +1,56 @@
+# ClassLens Database
+
+Durable data layer for teacher accounts, exam sessions, students, results, and
+reports — the foundation for the admin analytics dashboard and cross-quiz
+student tracking.
+
+## Stack
+
+- **Postgres on Supabase** (provider `supabase`), accessed via SQLAlchemy 2.0
+  async + `asyncpg`. Local dev/tests can use `sqlite` (provider `sqlite`).
+- All ClassLens tables live in a dedicated **`classlens` schema** so they never
+  collide with other apps sharing the same Supabase project.
+- Schema changes are managed by **Alembic** (`alembic/`), applied automatically
+  on container startup (`alembic upgrade head`).
+
+## Schema (`classlens.*`)
+
+| Table | Purpose |
+|---|---|
+| `teachers` | Accounts: email, password hash, full_name, school, `is_admin` |
+| `students` | First-class student per teacher; unique on `(teacher_id, normalized_name)` for cross-quiz matching |
+| `quizzes` | An exam/upload session (title, subject, status) |
+| `raw_files` | Uploaded source files (metadata; bytes in Supabase Storage) |
+| `parsed_data` | Structured parse of questions/answers + the confirmed answer grid |
+| `student_results` | One student's outcome per quiz — backbone for analytics |
+| `prompts` | Saved report prompts |
+| `reports` | Generated report HTML, linked to quiz + student |
+
+## How data is persisted
+
+- **On upload:** raw files are archived to Supabase Storage + `raw_files`;
+  parsed structured data is stored in `parsed_data`.
+- **On report generation:** the student is matched/created, a `student_results`
+  row is written, and the `reports` row links quiz + student.
+- **"Save to database" button** (`POST /api/sessions/{id}/save-to-db`): commits
+  every student in the confirmed grid as `students` + `student_results` and
+  marks the quiz `saved`.
+
+## Admin
+
+Set `ADMIN_EMAILS` (comma-separated); matching teachers are promoted on startup.
+Admin API is under `/api/admin/*` (stats, users, sessions, students + per-student
+cross-quiz timeline), surfaced in the in-app **管理後台** dashboard.
+
+## Commands
+
+```bash
+# Apply migrations
+alembic upgrade head
+
+# Create a new migration after changing models.py
+alembic revision -m "describe change"   # then edit, or --autogenerate
+
+# Run tests (SQLite, hermetic)
+DATABASE_PROVIDER=sqlite SUPABASE_DB_URL= pytest -q
+```

chatkit/backend/alembic.ini

@@ -0,0 +1,40 @@
+# Alembic config for ClassLens. The DB URL is built in alembic/env.py from
+# application settings (Supabase), so sqlalchemy.url is intentionally left blank.
+[alembic]
+script_location = alembic
+prepend_sys_path = .
+version_path_separator = os
+
+[loggers]
+keys = root,sqlalchemy,alembic
+
+[handlers]
+keys = console
+
+[formatters]
+keys = generic
+
+[logger_root]
+level = WARNING
+handlers = console
+qualname =
+
+[logger_sqlalchemy]
+level = WARNING
+handlers =
+qualname = sqlalchemy.engine
+
+[logger_alembic]
+level = INFO
+handlers =
+qualname = alembic
+
+[handler_console]
+class = StreamHandler
+args = (sys.stderr,)
+level = NOTSET
+formatter = generic
+
+[formatter_generic]
+format = %(levelname)-5.5s [%(name)s] %(message)s
+datefmt = %H:%M:%S

chatkit/backend/alembic/env.py

@@ -0,0 +1,69 @@
+"""Alembic environment — async, schema-isolated to the ClassLens schema.
+
+The DB connection and target schema come from application settings, so the same
+migrations run against Supabase (schema=classlens) or local SQLite (schema=None).
+Only the ClassLens schema is ever touched — other apps sharing the DB are ignored.
+"""
+
+from __future__ import annotations
+
+import asyncio
+from logging.config import fileConfig
+
+from alembic import context
+
+from app.db import active_schema, get_engine
+from app.models import Base  # noqa: F401  (registers all tables on Base.metadata)
+
+config = context.config
+if config.config_file_name:
+    fileConfig(config.config_file_name)
+
+target_metadata = Base.metadata
+SCHEMA = active_schema()
+
+
+def _include_object(obj, name, type_, reflected, compare_to):
+    """Never let autogenerate manage tables outside our schema."""
+    if type_ == "table":
+        return getattr(obj, "schema", None) == SCHEMA
+    return True
+
+
+def _configure(connection):
+    context.configure(
+        connection=connection,
+        target_metadata=target_metadata,
+        version_table_schema=SCHEMA,
+        include_schemas=False,
+        include_object=_include_object,
+        compare_type=True,
+    )
+
+
+def _run(connection):
+    _configure(connection)
+    with context.begin_transaction():
+        context.run_migrations()
+
+
+async def run_async():
+    engine = get_engine()
+    async with engine.connect() as conn:
+        await conn.run_sync(_run)
+    await engine.dispose()
+
+
+if context.is_offline_mode():
+    context.configure(
+        url=str(get_engine().url),
+        target_metadata=target_metadata,
+        version_table_schema=SCHEMA,
+        include_object=_include_object,
+        literal_binds=True,
+        dialect_opts={"paramstyle": "named"},
+    )
+    with context.begin_transaction():
+        context.run_migrations()
+else:
+    asyncio.run(run_async())

chatkit/backend/alembic/script.py.mako

@@ -0,0 +1,22 @@
+"""${message}
+
+Revision ID: ${up_revision}
+Revises: ${down_revision | comma,n}
+Create Date: ${create_date}
+"""
+from alembic import op
+import sqlalchemy as sa
+${imports if imports else ""}
+
+revision = ${repr(up_revision)}
+down_revision = ${repr(down_revision)}
+branch_labels = ${repr(branch_labels)}
+depends_on = ${repr(depends_on)}
+
+
+def upgrade() -> None:
+    ${upgrades if upgrades else "pass"}
+
+
+def downgrade() -> None:
+    ${downgrades if downgrades else "pass"}

chatkit/backend/alembic/versions/0001_initial.py

@@ -0,0 +1,32 @@
+"""initial ClassLens schema
+
+Creates all ClassLens tables (teachers, students, quizzes, raw_files,
+parsed_data, student_results, prompts, reports) in the dedicated schema.
+
+Revision ID: 0001_initial
+Revises:
+Create Date: 2026-06-03
+"""
+from alembic import op
+import sqlalchemy as sa
+
+from app.db import active_schema
+from app.models import Base
+
+revision = "0001_initial"
+down_revision = None
+branch_labels = None
+depends_on = None
+
+
+def upgrade() -> None:
+    bind = op.get_bind()
+    schema = active_schema()
+    if schema and bind.dialect.name == "postgresql":
+        op.execute(sa.text(f'CREATE SCHEMA IF NOT EXISTS "{schema}"'))
+    # Create every table defined on the models' metadata (single source of truth).
+    Base.metadata.create_all(bind)
+
+
+def downgrade() -> None:
+    Base.metadata.drop_all(op.get_bind())

chatkit/backend/app/admin.py

@@ -0,0 +1,84 @@
+"""Admin-only API: analytics dashboard, user/session management, schema info.
+
+All routes require an admin teacher (see auth.get_current_admin).
+"""
+
+from __future__ import annotations
+
+from fastapi import APIRouter, Depends, HTTPException
+from pydantic import BaseModel
+
+from .auth import get_current_admin
+from .database import (
+    admin_list_sessions,
+    admin_list_students,
+    delete_session,
+    delete_user,
+    get_schema_version,
+    get_session,
+    get_stats,
+    get_student,
+    get_student_timeline,
+    list_all_users,
+    set_user_admin,
+)
+
+router = APIRouter(prefix="/api/admin", tags=["admin"])
+
+
+@router.get("/stats")
+async def admin_stats(_admin=Depends(get_current_admin)):
+    stats = await get_stats()
+    stats["schema_version"] = await get_schema_version()
+    return stats
+
+
+@router.get("/users")
+async def admin_users(_admin=Depends(get_current_admin)):
+    return {"users": await list_all_users()}
+
+
+class SetAdminRequest(BaseModel):
+    is_admin: bool
+
+
+@router.put("/users/{user_id}/admin")
+async def admin_set_admin(user_id: int, body: SetAdminRequest, admin=Depends(get_current_admin)):
+    if user_id == admin["id"] and not body.is_admin:
+        raise HTTPException(status_code=400, detail="You cannot remove your own admin access")
+    await set_user_admin(user_id, body.is_admin)
+    return {"status": "ok", "user_id": user_id, "is_admin": body.is_admin}
+
+
+@router.delete("/users/{user_id}")
+async def admin_delete_user(user_id: int, admin=Depends(get_current_admin)):
+    if user_id == admin["id"]:
+        raise HTTPException(status_code=400, detail="You cannot delete your own account here")
+    await delete_user(user_id)
+    return {"status": "deleted"}
+
+
+@router.get("/sessions")
+async def admin_sessions(_admin=Depends(get_current_admin)):
+    return {"sessions": await admin_list_sessions()}
+
+
+@router.delete("/sessions/{session_id}")
+async def admin_delete_session(session_id: int, _admin=Depends(get_current_admin)):
+    if not await get_session(session_id):
+        raise HTTPException(status_code=404, detail="Session not found")
+    await delete_session(session_id)
+    return {"status": "deleted"}
+
+
+@router.get("/students")
+async def admin_students(_admin=Depends(get_current_admin)):
+    return {"students": await admin_list_students()}
+
+
+@router.get("/students/{student_id}")
+async def admin_student_detail(student_id: int, _admin=Depends(get_current_admin)):
+    student = await get_student(student_id)
+    if not student:
+        raise HTTPException(status_code=404, detail="Student not found")
+    return {"student": student, "timeline": await get_student_timeline(student_id)}

chatkit/backend/app/answer_grid.py

@@ -215,6 +215,21 @@ def seed_from_parsed(
     )
 
 
+def score_student(grid: AnswerGrid, student_index: int) -> tuple[int, int, float]:
+    """Return (graded_questions, correct_count, score_percent) for one student.
+
+    Only questions with a concrete official letter (not blank, not the "="
+    correct-marker) are graded. A student cell of "=" counts as correct.
+    """
+    graded = sum(
+        1 for c in grid.official_answers if c is not None and c != _CORRECT_MARKER
+    )
+    wrong = len(diff_student(grid, student_index))
+    correct = graded - wrong
+    score = round(correct / graded * 100, 1) if graded else 0.0
+    return graded, correct, score
+
+
 def diff_student(grid: AnswerGrid, student_index: int) -> list[WrongAnswer]:
     """Return the student's wrong answers ordered by question number.
 

chatkit/backend/app/auth.py

@@ -56,17 +56,28 @@ async def get_current_user(
     return user
 
 
-async def register_user(email: str, password: str) -> dict:
-    """Register a new user. Returns user dict."""
+async def register_user(
+    email: str, password: str, full_name: str = "", school: str = ""
+) -> dict:
+    """Register a new teacher. Returns user dict."""
     existing = await get_user_by_email(email)
     if existing:
         raise HTTPException(status_code=400, detail="Email already registered")
     hashed = hash_password(password)
-    user_id = await create_user(email, hashed)
+    user_id = await create_user(
+        email, hashed, display_name=full_name, full_name=full_name, school=school
+    )
     user = await get_user_by_id(user_id)
     return user
 
 
+async def get_current_admin(user: dict = Depends(get_current_user)) -> dict:
+    """FastAPI dependency: require the authenticated user to be an admin."""
+    if not user.get("is_admin"):
+        raise HTTPException(status_code=status.HTTP_403_FORBIDDEN, detail="Admin access required")
+    return user
+
+
 async def login_user(email: str, password: str) -> dict:
     """Validate credentials and return user dict + JWT token."""
     user = await get_user_by_email(email)
@@ -74,4 +85,16 @@ async def login_user(email: str, password: str) -> dict:
         raise HTTPException(status_code=401, detail="Invalid email or password")
     await update_last_login(user["id"])
     token = create_access_token({"sub": str(user["id"])})
-    return {"token": token, "user": {"id": user["id"], "email": user["email"], "display_name": user["display_name"]}}
+    return {"token": token, "user": public_user(user)}
+
+
+def public_user(user: dict) -> dict:
+    """User fields safe to return to the client (no password hash)."""
+    return {
+        "id": user["id"],
+        "email": user["email"],
+        "display_name": user.get("display_name", ""),
+        "full_name": user.get("full_name", ""),
+        "school": user.get("school", ""),
+        "is_admin": bool(user.get("is_admin")),
+    }

chatkit/backend/app/config.py

@@ -28,7 +28,18 @@ class Settings(BaseModel):
     jwt_expire_minutes: int = 1440  # 24 hours
 
     # Database
-    database_path: str = ""  # auto-resolved if empty
+    database_path: str = ""  # legacy SQLite path; auto-resolved if empty
+    database_provider: str = "sqlite"  # "supabase" | "sqlite"
+    db_schema: str = "classlens"  # Postgres schema to isolate ClassLens tables
+
+    # Supabase
+    supabase_url: str = ""
+    supabase_service_role_key: str = ""
+    supabase_db_url: str = ""  # postgresql://... (session pooler)
+    supabase_storage_bucket: str = "classlens-raw-files"
+
+    # Admin bootstrap: comma-separated emails promoted to admin on startup
+    admin_emails: str = ""
 
     # Encryption key for sensitive data
     encryption_key: str = ""
@@ -53,6 +64,13 @@ def get_settings() -> Settings:
         jwt_algorithm=os.getenv("JWT_ALGORITHM", "HS256"),
         jwt_expire_minutes=int(os.getenv("JWT_EXPIRE_MINUTES", "1440")),
         database_path=os.getenv("DATABASE_PATH", ""),
+        database_provider=os.getenv("DATABASE_PROVIDER", "sqlite"),
+        db_schema=os.getenv("DB_SCHEMA", "classlens"),
+        supabase_url=os.getenv("SUPABASE_URL", ""),
+        supabase_service_role_key=os.getenv("SUPABASE_SERVICE_ROLE_KEY", ""),
+        supabase_db_url=os.getenv("SUPABASE_DB_URL", ""),
+        supabase_storage_bucket=os.getenv("SUPABASE_STORAGE_BUCKET", "classlens-raw-files"),
+        admin_emails=os.getenv("ADMIN_EMAILS", ""),
         encryption_key=os.getenv("ENCRYPTION_KEY", ""),
         upload_dir=os.getenv("UPLOAD_DIR", ""),
         max_file_size_mb=int(os.getenv("MAX_FILE_SIZE_MB", "20")),

chatkit/backend/app/database.py

@@ -1,231 +1,263 @@
-"""SQLite database for ClassLens - users, sessions, parsed data, prompts, reports."""
+"""Data-access layer for ClassLens, backed by SQLAlchemy async (Supabase Postgres).
+
+Public function names/signatures are kept stable for existing callers:
+  - "session" in the API == a row in the ``quizzes`` table (a quiz/exam session)
+  - "user" == a row in the ``teachers`` table
+
+New capabilities (students as first-class entities, per-student results, and
+analytics) are added alongside the legacy CRUD helpers.
+"""
 
 from __future__ import annotations
 
-import aiosqlite
-import json
-from pathlib import Path
-from datetime import datetime
+import re
+import unicodedata
+from datetime import datetime, timezone
 from typing import Optional
 
+from sqlalchemy import delete, func, select, update
+
 from .config import get_settings
+from .db import get_engine, get_sessionmaker
+from .models import (
+    Base,
+    ParsedData,
+    Prompt,
+    Quiz,
+    RawFile,
+    Report,
+    Student,
+    StudentResult,
+    Teacher,
+)
 
-DATABASE_PATH = Path(__file__).parent.parent / "classlens.db"
+ANSWER_GRID_DATA_TYPE = "answer_grid"
 
 
-def _get_db_path() -> Path:
-    settings = get_settings()
-    if settings.database_path:
-        return Path(settings.database_path)
-    return DATABASE_PATH
+def _now() -> datetime:
+    return datetime.now(timezone.utc)
+
+
+def _row(obj) -> dict:
+    """Serialize an ORM object to a plain dict of its columns."""
+    return {c.key: getattr(obj, c.key) for c in obj.__table__.columns}
+
+
+def normalize_name(name: str) -> str:
+    """Canonical key for matching the same student across quizzes."""
+    s = unicodedata.normalize("NFKC", (name or "").strip()).casefold()
+    return re.sub(r"\s+", " ", s)
+
 
+# =============================================================================
+# Schema bootstrap
+# =============================================================================
 
 async def init_database():
-    """Initialize the database with required tables."""
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        await db.execute("""
-            CREATE TABLE IF NOT EXISTS users (
-                id INTEGER PRIMARY KEY AUTOINCREMENT,
-                email TEXT UNIQUE NOT NULL,
-                password_hash TEXT NOT NULL,
-                display_name TEXT,
-                created_at TEXT NOT NULL,
-                last_login TEXT
-            )
-        """)
-
-        await db.execute("""
-            CREATE TABLE IF NOT EXISTS exam_sessions (
-                id INTEGER PRIMARY KEY AUTOINCREMENT,
-                user_id INTEGER NOT NULL,
-                title TEXT DEFAULT 'Untitled Session',
-                status TEXT DEFAULT 'draft',
-                created_at TEXT NOT NULL,
-                updated_at TEXT NOT NULL,
-                FOREIGN KEY (user_id) REFERENCES users(id)
-            )
-        """)
-
-        await db.execute("""
-            CREATE TABLE IF NOT EXISTS parsed_data (
-                id INTEGER PRIMARY KEY AUTOINCREMENT,
-                session_id INTEGER NOT NULL,
-                data_type TEXT NOT NULL,
-                file_name TEXT,
-                raw_text TEXT,
-                structured_data TEXT NOT NULL,
-                created_at TEXT NOT NULL,
-                FOREIGN KEY (session_id) REFERENCES exam_sessions(id) ON DELETE CASCADE
-            )
-        """)
-
-        await db.execute("""
-            CREATE TABLE IF NOT EXISTS prompts (
-                id INTEGER PRIMARY KEY AUTOINCREMENT,
-                user_id INTEGER NOT NULL,
-                name TEXT NOT NULL,
-                content TEXT NOT NULL,
-                is_default BOOLEAN DEFAULT 0,
-                created_at TEXT NOT NULL,
-                updated_at TEXT NOT NULL,
-                FOREIGN KEY (user_id) REFERENCES users(id)
-            )
-        """)
-
-        await db.execute("""
-            CREATE TABLE IF NOT EXISTS reports (
-                id INTEGER PRIMARY KEY AUTOINCREMENT,
-                session_id INTEGER NOT NULL,
-                prompt_id INTEGER,
-                html_content TEXT NOT NULL,
-                created_at TEXT NOT NULL,
-                FOREIGN KEY (session_id) REFERENCES exam_sessions(id) ON DELETE CASCADE,
-                FOREIGN KEY (prompt_id) REFERENCES prompts(id)
-            )
-        """)
+    """Ensure schema exists and bootstrap admins.
 
+    On Supabase, tables are managed by Alembic migrations; here we only create
+    tables for the SQLite dev/test fallback. Admin bootstrap runs for both.
+    """
+    settings = get_settings()
+
+    if settings.database_provider != "supabase":
+        async with get_engine().begin() as conn:
+            await conn.run_sync(Base.metadata.create_all)
+
+    await _bootstrap_admins()
+
+
+async def _bootstrap_admins():
+    settings = get_settings()
+    emails = [e.strip().lower() for e in (settings.admin_emails or "").split(",") if e.strip()]
+    if not emails:
+        return
+    async with get_sessionmaker()() as db:
+        await db.execute(
+            update(Teacher).where(func.lower(Teacher.email).in_(emails)).values(is_admin=True)
+        )
         await db.commit()
 
 
-# ---- User CRUD ----
+# =============================================================================
+# Teachers (users)
+# =============================================================================
 
-async def create_user(email: str, password_hash: str, display_name: str = "") -> int:
-    db_path = _get_db_path()
-    now = datetime.utcnow().isoformat()
-    async with aiosqlite.connect(db_path) as db:
-        cursor = await db.execute(
-            "INSERT INTO users (email, password_hash, display_name, created_at) VALUES (?, ?, ?, ?)",
-            (email, password_hash, display_name or email.split("@")[0], now),
+async def create_user(
+    email: str,
+    password_hash: str,
+    display_name: str = "",
+    full_name: str = "",
+    school: str = "",
+) -> int:
+    async with get_sessionmaker()() as db:
+        teacher = Teacher(
+            email=email,
+            password_hash=password_hash,
+            display_name=display_name or email.split("@")[0],
+            full_name=full_name,
+            school=school,
         )
+        db.add(teacher)
         await db.commit()
-        return cursor.lastrowid
+        await db.refresh(teacher)
+        return teacher.id
 
 
 async def get_user_by_email(email: str) -> Optional[dict]:
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute("SELECT * FROM users WHERE email = ?", (email,)) as cursor:
-            row = await cursor.fetchone()
-            return dict(row) if row else None
+    async with get_sessionmaker()() as db:
+        res = await db.execute(select(Teacher).where(Teacher.email == email))
+        teacher = res.scalar_one_or_none()
+        return _row(teacher) if teacher else None
 
 
 async def get_user_by_id(user_id: int) -> Optional[dict]:
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute("SELECT * FROM users WHERE id = ?", (user_id,)) as cursor:
-            row = await cursor.fetchone()
-            return dict(row) if row else None
+    async with get_sessionmaker()() as db:
+        teacher = await db.get(Teacher, user_id)
+        return _row(teacher) if teacher else None
 
 
 async def update_last_login(user_id: int):
-    db_path = _get_db_path()
-    now = datetime.utcnow().isoformat()
-    async with aiosqlite.connect(db_path) as db:
-        await db.execute("UPDATE users SET last_login = ? WHERE id = ?", (now, user_id))
+    async with get_sessionmaker()() as db:
+        await db.execute(
+            update(Teacher).where(Teacher.id == user_id).values(last_login=_now())
+        )
+        await db.commit()
+
+
+async def set_user_admin(user_id: int, is_admin: bool):
+    async with get_sessionmaker()() as db:
+        await db.execute(
+            update(Teacher).where(Teacher.id == user_id).values(is_admin=is_admin)
+        )
         await db.commit()
 
 
-# ---- Session CRUD ----
+async def list_all_users() -> list[dict]:
+    """Admin: all teachers (without password hashes), with per-teacher counts."""
+    async with get_sessionmaker()() as db:
+        res = await db.execute(select(Teacher).order_by(Teacher.created_at.desc()))
+        teachers = res.scalars().all()
+        out = []
+        for t in teachers:
+            d = _row(t)
+            d.pop("password_hash", None)
+            d["quiz_count"] = (
+                await db.execute(select(func.count(Quiz.id)).where(Quiz.teacher_id == t.id))
+            ).scalar_one()
+            d["student_count"] = (
+                await db.execute(select(func.count(Student.id)).where(Student.teacher_id == t.id))
+            ).scalar_one()
+            out.append(d)
+        return out
+
+
+async def delete_user(user_id: int):
+    async with get_sessionmaker()() as db:
+        await db.execute(delete(Teacher).where(Teacher.id == user_id))
+        await db.commit()
+
+
+# =============================================================================
+# Quizzes (sessions)
+# =============================================================================
 
 async def create_session(user_id: int, title: str = "Untitled Session") -> int:
-    db_path = _get_db_path()
-    now = datetime.utcnow().isoformat()
-    async with aiosqlite.connect(db_path) as db:
-        cursor = await db.execute(
-            "INSERT INTO exam_sessions (user_id, title, status, created_at, updated_at) VALUES (?, ?, 'draft', ?, ?)",
-            (user_id, title, now, now),
-        )
+    async with get_sessionmaker()() as db:
+        quiz = Quiz(teacher_id=user_id, title=title, status="draft")
+        db.add(quiz)
         await db.commit()
-        return cursor.lastrowid
+        await db.refresh(quiz)
+        return quiz.id
 
 
 async def get_sessions(user_id: int) -> list[dict]:
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute(
-            "SELECT * FROM exam_sessions WHERE user_id = ? ORDER BY created_at DESC", (user_id,)
-        ) as cursor:
-            rows = await cursor.fetchall()
-            return [dict(row) for row in rows]
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(Quiz).where(Quiz.teacher_id == user_id).order_by(Quiz.created_at.desc())
+        )
+        return [_row(q) for q in res.scalars().all()]
 
 
 async def get_session(session_id: int) -> Optional[dict]:
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute("SELECT * FROM exam_sessions WHERE id = ?", (session_id,)) as cursor:
-            row = await cursor.fetchone()
-            return dict(row) if row else None
+    async with get_sessionmaker()() as db:
+        quiz = await db.get(Quiz, session_id)
+        if not quiz:
+            return None
+        d = _row(quiz)
+        d["user_id"] = quiz.teacher_id  # back-compat key for existing callers
+        return d
 
 
 async def update_session_status(session_id: int, status: str):
-    db_path = _get_db_path()
-    now = datetime.utcnow().isoformat()
-    async with aiosqlite.connect(db_path) as db:
+    async with get_sessionmaker()() as db:
         await db.execute(
-            "UPDATE exam_sessions SET status = ?, updated_at = ? WHERE id = ?",
-            (status, now, session_id),
+            update(Quiz).where(Quiz.id == session_id).values(status=status, updated_at=_now())
         )
         await db.commit()
 
 
-# ---- Parsed Data CRUD ----
+async def update_session_meta(session_id: int, title: Optional[str] = None, subject: Optional[str] = None):
+    values: dict = {"updated_at": _now()}
+    if title is not None:
+        values["title"] = title
+    if subject is not None:
+        values["subject"] = subject
+    async with get_sessionmaker()() as db:
+        await db.execute(update(Quiz).where(Quiz.id == session_id).values(**values))
+        await db.commit()
+
+
+async def delete_session(session_id: int):
+    async with get_sessionmaker()() as db:
+        await db.execute(delete(Quiz).where(Quiz.id == session_id))
+        await db.commit()
+
+
+# =============================================================================
+# Parsed data
+# =============================================================================
 
 async def save_parsed_data(
     session_id: int, data_type: str, file_name: str, raw_text: str, structured_data: dict
 ) -> int:
-    db_path = _get_db_path()
-    now = datetime.utcnow().isoformat()
-    async with aiosqlite.connect(db_path) as db:
-        cursor = await db.execute(
-            "INSERT INTO parsed_data (session_id, data_type, file_name, raw_text, structured_data, created_at) VALUES (?, ?, ?, ?, ?, ?)",
-            (session_id, data_type, file_name, raw_text, json.dumps(structured_data, ensure_ascii=False), now),
+    async with get_sessionmaker()() as db:
+        row = ParsedData(
+            quiz_id=session_id,
+            data_type=data_type,
+            file_name=file_name or "",
+            raw_text=raw_text or "",
+            structured_data=structured_data,
         )
+        db.add(row)
         await db.commit()
-        return cursor.lastrowid
+        await db.refresh(row)
+        return row.id
 
 
 async def get_parsed_data(session_id: int) -> list[dict]:
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute(
-            "SELECT * FROM parsed_data WHERE session_id = ? ORDER BY data_type, created_at",
-            (session_id,),
-        ) as cursor:
-            rows = await cursor.fetchall()
-            result = []
-            for row in rows:
-                d = dict(row)
-                d["structured_data"] = json.loads(d["structured_data"])
-                result.append(d)
-            return result
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(ParsedData)
+            .where(ParsedData.quiz_id == session_id)
+            .order_by(ParsedData.data_type, ParsedData.created_at)
+        )
+        return [_row(p) for p in res.scalars().all()]
 
 
 async def delete_parsed_data(session_id: int, data_type: Optional[str] = None):
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
+    async with get_sessionmaker()() as db:
+        stmt = delete(ParsedData).where(ParsedData.quiz_id == session_id)
         if data_type:
-            await db.execute(
-                "DELETE FROM parsed_data WHERE session_id = ? AND data_type = ?",
-                (session_id, data_type),
-            )
-        else:
-            await db.execute("DELETE FROM parsed_data WHERE session_id = ?", (session_id,))
+            stmt = stmt.where(ParsedData.data_type == data_type)
+        await db.execute(stmt)
         await db.commit()
 
 
-# ---- Answer Grid CRUD ----
-
-ANSWER_GRID_DATA_TYPE = "answer_grid"
-
+# ---- Answer grid (stored as a parsed_data row) ----
 
 async def save_answer_grid(session_id: int, grid_dict: dict) -> int:
-    """Save confirmed answer grid, replacing any previous one for the session."""
     await delete_parsed_data(session_id, ANSWER_GRID_DATA_TYPE)
     return await save_parsed_data(
         session_id=session_id,
@@ -237,116 +269,330 @@ async def save_answer_grid(session_id: int, grid_dict: dict) -> int:
 
 
 async def get_answer_grid(session_id: int) -> Optional[dict]:
-    """Return the saved answer grid dict or None if not confirmed."""
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute(
-            "SELECT structured_data FROM parsed_data WHERE session_id = ? AND data_type = ? ORDER BY created_at DESC LIMIT 1",
-            (session_id, ANSWER_GRID_DATA_TYPE),
-        ) as cursor:
-            row = await cursor.fetchone()
-            if not row:
-                return None
-            return json.loads(row["structured_data"])
-
-
-# ---- Prompt CRUD ----
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(ParsedData.structured_data)
+            .where(ParsedData.quiz_id == session_id)
+            .where(ParsedData.data_type == ANSWER_GRID_DATA_TYPE)
+            .order_by(ParsedData.created_at.desc())
+            .limit(1)
+        )
+        row = res.scalar_one_or_none()
+        return row if row else None
 
-async def save_prompt(user_id: int, name: str, content: str, is_default: bool = False) -> int:
-    db_path = _get_db_path()
-    now = datetime.utcnow().isoformat()
-    async with aiosqlite.connect(db_path) as db:
-        cursor = await db.execute(
-            "INSERT INTO prompts (user_id, name, content, is_default, created_at, updated_at) VALUES (?, ?, ?, ?, ?, ?)",
-            (user_id, name, content, is_default, now, now),
+
+# =============================================================================
+# Raw files (metadata; bytes live in Supabase Storage)
+# =============================================================================
+
+async def save_raw_file(
+    session_id: int,
+    data_type: str,
+    file_name: str,
+    content_type: str,
+    size_bytes: int,
+    storage_path: str,
+) -> int:
+    async with get_sessionmaker()() as db:
+        row = RawFile(
+            quiz_id=session_id,
+            data_type=data_type,
+            file_name=file_name,
+            content_type=content_type,
+            size_bytes=size_bytes,
+            storage_path=storage_path,
+        )
+        db.add(row)
+        await db.commit()
+        await db.refresh(row)
+        return row.id
+
+
+async def get_raw_files(session_id: int) -> list[dict]:
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(RawFile).where(RawFile.quiz_id == session_id).order_by(RawFile.created_at)
         )
+        return [_row(f) for f in res.scalars().all()]
+
+
+# =============================================================================
+# Students (first-class, matched across quizzes by normalized name)
+# =============================================================================
+
+async def match_or_create_student(
+    teacher_id: int, name: str, external_id: Optional[str] = None
+) -> int:
+    """Return the student id for this teacher, creating the row if new."""
+    norm = normalize_name(name)
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(Student)
+            .where(Student.teacher_id == teacher_id)
+            .where(Student.normalized_name == norm)
+        )
+        student = res.scalar_one_or_none()
+        if student:
+            if external_id and not student.external_id:
+                student.external_id = external_id
+                await db.commit()
+            return student.id
+        student = Student(
+            teacher_id=teacher_id, name=name, normalized_name=norm, external_id=external_id
+        )
+        db.add(student)
         await db.commit()
-        return cursor.lastrowid
+        await db.refresh(student)
+        return student.id
+
+
+async def get_students(teacher_id: int) -> list[dict]:
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(Student).where(Student.teacher_id == teacher_id).order_by(Student.name)
+        )
+        return [_row(s) for s in res.scalars().all()]
+
+
+async def get_student(student_id: int) -> Optional[dict]:
+    async with get_sessionmaker()() as db:
+        student = await db.get(Student, student_id)
+        return _row(student) if student else None
+
+
+async def save_student_result(
+    quiz_id: int,
+    student_id: int,
+    answers: dict,
+    total_questions: int,
+    correct_count: int,
+    score: float,
+) -> int:
+    """Upsert one student's result for a quiz (unique per quiz+student)."""
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(StudentResult)
+            .where(StudentResult.quiz_id == quiz_id)
+            .where(StudentResult.student_id == student_id)
+        )
+        existing = res.scalar_one_or_none()
+        if existing:
+            existing.answers = answers
+            existing.total_questions = total_questions
+            existing.correct_count = correct_count
+            existing.score = score
+            await db.commit()
+            return existing.id
+        row = StudentResult(
+            quiz_id=quiz_id,
+            student_id=student_id,
+            answers=answers,
+            total_questions=total_questions,
+            correct_count=correct_count,
+            score=score,
+        )
+        db.add(row)
+        await db.commit()
+        await db.refresh(row)
+        return row.id
+
+
+async def get_student_timeline(student_id: int) -> list[dict]:
+    """One student's results across all quizzes, oldest first — the trajectory."""
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(StudentResult, Quiz.title, Quiz.subject, Quiz.created_at)
+            .join(Quiz, StudentResult.quiz_id == Quiz.id)
+            .where(StudentResult.student_id == student_id)
+            .order_by(Quiz.created_at)
+        )
+        out = []
+        for sr, title, subject, created in res.all():
+            d = _row(sr)
+            d["quiz_title"] = title
+            d["quiz_subject"] = subject
+            d["quiz_created_at"] = created
+            out.append(d)
+        return out
+
+
+# =============================================================================
+# Prompts
+# =============================================================================
+
+async def save_prompt(user_id: int, name: str, content: str, is_default: bool = False) -> int:
+    async with get_sessionmaker()() as db:
+        row = Prompt(teacher_id=user_id, name=name, content=content, is_default=is_default)
+        db.add(row)
+        await db.commit()
+        await db.refresh(row)
+        return row.id
 
 
 async def get_prompts(user_id: int) -> list[dict]:
-    """Get user's own prompts plus all default prompts."""
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute(
-            "SELECT * FROM prompts WHERE user_id = ? OR is_default = 1 ORDER BY is_default DESC, updated_at DESC",
-            (user_id,),
-        ) as cursor:
-            rows = await cursor.fetchall()
-            return [dict(row) for row in rows]
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(Prompt)
+            .where((Prompt.teacher_id == user_id) | (Prompt.is_default.is_(True)))
+            .order_by(Prompt.is_default.desc(), Prompt.updated_at.desc())
+        )
+        return [_row(p) for p in res.scalars().all()]
 
 
 async def get_all_prompts() -> list[dict]:
-    """Get all prompts from all users (for the 'read others' dropdown)."""
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute(
-            "SELECT p.*, u.email as author_email FROM prompts p JOIN users u ON p.user_id = u.id ORDER BY p.updated_at DESC"
-        ) as cursor:
-            rows = await cursor.fetchall()
-            return [dict(row) for row in rows]
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(Prompt, Teacher.email)
+            .join(Teacher, Prompt.teacher_id == Teacher.id)
+            .order_by(Prompt.updated_at.desc())
+        )
+        out = []
+        for p, email in res.all():
+            d = _row(p)
+            d["author_email"] = email
+            out.append(d)
+        return out
 
 
 async def get_prompt(prompt_id: int) -> Optional[dict]:
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute("SELECT * FROM prompts WHERE id = ?", (prompt_id,)) as cursor:
-            row = await cursor.fetchone()
-            return dict(row) if row else None
+    async with get_sessionmaker()() as db:
+        p = await db.get(Prompt, prompt_id)
+        if not p:
+            return None
+        d = _row(p)
+        d["user_id"] = p.teacher_id  # back-compat key
+        return d
 
 
 async def update_prompt(prompt_id: int, name: str, content: str):
-    db_path = _get_db_path()
-    now = datetime.utcnow().isoformat()
-    async with aiosqlite.connect(db_path) as db:
+    async with get_sessionmaker()() as db:
         await db.execute(
-            "UPDATE prompts SET name = ?, content = ?, updated_at = ? WHERE id = ?",
-            (name, content, now, prompt_id),
+            update(Prompt)
+            .where(Prompt.id == prompt_id)
+            .values(name=name, content=content, updated_at=_now())
         )
         await db.commit()
 
 
 async def delete_prompt(prompt_id: int):
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        await db.execute("DELETE FROM prompts WHERE id = ?", (prompt_id,))
+    async with get_sessionmaker()() as db:
+        await db.execute(delete(Prompt).where(Prompt.id == prompt_id))
         await db.commit()
 
 
-# ---- Report CRUD ----
+# =============================================================================
+# Reports
+# =============================================================================
 
-async def save_report(session_id: int, prompt_id: Optional[int], html_content: str) -> int:
-    db_path = _get_db_path()
-    now = datetime.utcnow().isoformat()
-    async with aiosqlite.connect(db_path) as db:
-        cursor = await db.execute(
-            "INSERT INTO reports (session_id, prompt_id, html_content, created_at) VALUES (?, ?, ?, ?)",
-            (session_id, prompt_id, html_content, now),
+async def save_report(
+    session_id: int,
+    prompt_id: Optional[int],
+    html_content: str,
+    student_id: Optional[int] = None,
+    model: str = "",
+) -> int:
+    async with get_sessionmaker()() as db:
+        row = Report(
+            quiz_id=session_id,
+            prompt_id=prompt_id,
+            student_id=student_id,
+            html_content=html_content,
+            model=model,
         )
+        db.add(row)
         await db.commit()
-        return cursor.lastrowid
+        await db.refresh(row)
+        return row.id
 
 
 async def get_reports(session_id: int) -> list[dict]:
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute(
-            "SELECT * FROM reports WHERE session_id = ? ORDER BY created_at DESC",
-            (session_id,),
-        ) as cursor:
-            rows = await cursor.fetchall()
-            return [dict(row) for row in rows]
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(Report).where(Report.quiz_id == session_id).order_by(Report.created_at.desc())
+        )
+        return [_row(r) for r in res.scalars().all()]
 
 
 async def get_report(report_id: int) -> Optional[dict]:
-    db_path = _get_db_path()
-    async with aiosqlite.connect(db_path) as db:
-        db.row_factory = aiosqlite.Row
-        async with db.execute("SELECT * FROM reports WHERE id = ?", (report_id,)) as cursor:
-            row = await cursor.fetchone()
-            return dict(row) if row else None
+    async with get_sessionmaker()() as db:
+        r = await db.get(Report, report_id)
+        return _row(r) if r else None
+
+
+# =============================================================================
+# Analytics (admin dashboard)
+# =============================================================================
+
+async def get_stats() -> dict:
+    async with get_sessionmaker()() as db:
+        async def count(model):
+            return (await db.execute(select(func.count(model.id)))).scalar_one()
+
+        return {
+            "teachers": await count(Teacher),
+            "students": await count(Student),
+            "quizzes": await count(Quiz),
+            "reports": await count(Report),
+            "student_results": await count(StudentResult),
+            "prompts": await count(Prompt),
+        }
+
+
+async def admin_list_students(limit: int = 500) -> list[dict]:
+    """All students across teachers, with quiz count + average score (admin)."""
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(Student, Teacher.email).join(Teacher, Student.teacher_id == Teacher.id).order_by(Student.name).limit(limit)
+        )
+        out = []
+        for s, email in res.all():
+            d = _row(s)
+            d["teacher_email"] = email
+            agg = (
+                await db.execute(
+                    select(func.count(StudentResult.id), func.avg(StudentResult.score)).where(
+                        StudentResult.student_id == s.id
+                    )
+                )
+            ).one()
+            d["result_count"] = agg[0]
+            d["avg_score"] = round(float(agg[1]), 1) if agg[1] is not None else None
+            out.append(d)
+        return out
+
+
+async def get_schema_version() -> Optional[str]:
+    """Current Alembic migration revision applied to the DB."""
+    from sqlalchemy import text
+
+    from .db import active_schema
+
+    schema = active_schema()
+    table = f'"{schema}".alembic_version' if schema else "alembic_version"
+    async with get_sessionmaker()() as db:
+        try:
+            res = await db.execute(text(f"SELECT version_num FROM {table} LIMIT 1"))
+            row = res.first()
+            return row[0] if row else None
+        except Exception:
+            return None
+
+
+async def admin_list_sessions(limit: int = 200) -> list[dict]:
+    """All quizzes across teachers, with author email + counts (admin)."""
+    async with get_sessionmaker()() as db:
+        res = await db.execute(
+            select(Quiz, Teacher.email)
+            .join(Teacher, Quiz.teacher_id == Teacher.id)
+            .order_by(Quiz.created_at.desc())
+            .limit(limit)
+        )
+        out = []
+        for q, email in res.all():
+            d = _row(q)
+            d["teacher_email"] = email
+            d["report_count"] = (
+                await db.execute(select(func.count(Report.id)).where(Report.quiz_id == q.id))
+            ).scalar_one()
+            out.append(d)
+        return out

chatkit/backend/app/db.py

@@ -0,0 +1,110 @@
+"""Async SQLAlchemy engine + session management for ClassLens.
+
+Supports two providers (DATABASE_PROVIDER):
+  - "supabase": Postgres via asyncpg, tables isolated in a dedicated schema
+  - "sqlite":   local aiosqlite file (dev/tests; schema flattened to None)
+"""
+
+from __future__ import annotations
+
+import os
+import ssl
+from functools import lru_cache
+from pathlib import Path
+from typing import AsyncIterator, Optional
+
+from sqlalchemy.engine import URL, make_url
+from sqlalchemy.ext.asyncio import (
+    AsyncEngine,
+    AsyncSession,
+    async_sessionmaker,
+    create_async_engine,
+)
+
+from .config import get_settings
+
+
+def _is_supabase() -> bool:
+    s = get_settings()
+    return s.database_provider == "supabase" and bool(s.supabase_db_url)
+
+
+def active_schema() -> Optional[str]:
+    """The Postgres schema for our tables, or None for SQLite (no schemas)."""
+    if _is_supabase():
+        return get_settings().db_schema or None
+    return None
+
+
+def assert_production_db_config() -> None:
+    """Fail loud rather than silently running on ephemeral SQLite.
+
+    The container FS on HF Spaces is ephemeral, so booting on SQLite means all
+    data is lost on every sleep/rebuild. Refuse to start in those cases.
+    """
+    s = get_settings()
+    if s.database_provider == "supabase" and not s.supabase_db_url:
+        raise RuntimeError(
+            "DATABASE_PROVIDER=supabase but SUPABASE_DB_URL is empty — refusing to "
+            "start on ephemeral SQLite. Set the SUPABASE_DB_URL secret."
+        )
+    # On a deployed HF Space (SPACE_ID is injected by the platform), require a
+    # real Postgres DB so a missing/typo'd provider can't silently degrade.
+    if os.getenv("SPACE_ID") and not _is_supabase():
+        raise RuntimeError(
+            "Running on a Hugging Face Space without Supabase configured — refusing "
+            "to start on ephemeral SQLite. Set DATABASE_PROVIDER=supabase and "
+            "SUPABASE_DB_URL."
+        )
+
+
+def _build_url() -> URL:
+    s = get_settings()
+    if _is_supabase():
+        # make_url keeps the raw password (handles '*' etc.); just swap the driver.
+        return make_url(s.supabase_db_url).set(drivername="postgresql+asyncpg")
+    # SQLite fallback
+    path = s.database_path or str(Path(__file__).parent.parent / "classlens.db")
+    return make_url(f"sqlite+aiosqlite:///{path}")
+
+
+def _connect_args() -> dict:
+    if _is_supabase():
+        # Encrypt but don't verify the CA chain (libpq sslmode=require semantics).
+        # Supabase's pooler presents a valid cert in prod, but TLS-intercepting
+        # proxies (corp egress) break chain verification — this works in both.
+        ctx = ssl.create_default_context()
+        ctx.check_hostname = False
+        ctx.verify_mode = ssl.CERT_NONE
+        return {
+            "ssl": ctx,
+            # Supabase runs pgbouncer; disable asyncpg's prepared-statement cache
+            # so the same code works on both session (5432) and txn (6543) poolers.
+            "statement_cache_size": 0,
+        }
+    return {}
+
+
+@lru_cache
+def get_engine() -> AsyncEngine:
+    kwargs: dict = {"pool_pre_ping": True, "future": True}
+    if _is_supabase():
+        kwargs.update(pool_size=5, max_overflow=5, connect_args=_connect_args())
+    return create_async_engine(_build_url(), **kwargs)
+
+
+@lru_cache
+def get_sessionmaker() -> async_sessionmaker[AsyncSession]:
+    return async_sessionmaker(get_engine(), expire_on_commit=False, class_=AsyncSession)
+
+
+async def get_session() -> AsyncIterator[AsyncSession]:
+    """FastAPI dependency: yields a session, commits on success, rolls back on error."""
+    maker = get_sessionmaker()
+    async with maker() as session:
+        try:
+            yield session
+            await session.commit()
+        except Exception:
+            await session.rollback()
+            raise

chatkit/backend/app/main.py

@@ -8,7 +8,7 @@ from typing import Optional
 
 from fastapi import FastAPI, Depends, File, Form, UploadFile, HTTPException
 from fastapi.middleware.cors import CORSMiddleware
-from fastapi.responses import JSONResponse, Response, FileResponse
+from fastapi.responses import Response, FileResponse
 from fastapi.staticfiles import StaticFiles
 from pydantic import BaseModel
 
@@ -18,6 +18,8 @@ from .database import (
     get_sessions,
     get_session,
     update_session_status,
+    update_session_meta,
+    delete_session,
     get_parsed_data,
     save_prompt,
     get_prompts,
@@ -31,11 +33,22 @@ from .database import (
     save_answer_grid,
     get_answer_grid,
     delete_parsed_data,
+    match_or_create_student,
+    save_student_result,
+    save_raw_file,
     ANSWER_GRID_DATA_TYPE,
 )
-from .auth import get_current_user, register_user, login_user
+from .storage import upload_raw_file
+from .auth import get_current_user, register_user, login_user, public_user
+from .admin import router as admin_router
+from .db import assert_production_db_config
 from .file_processor import process_uploaded_files
-from .answer_grid import from_dict as grid_from_dict, seed_from_parsed, to_dict as grid_to_dict
+from .answer_grid import (
+    from_dict as grid_from_dict,
+    seed_from_parsed,
+    to_dict as grid_to_dict,
+    score_student as grid_score_student,
+)
 from .parsers import AUTO, list_parsers
 from .report_generator import generate_student_report, build_student_markdown
 
@@ -45,6 +58,7 @@ STATIC_DIR = Path(__file__).parent.parent / "static"
 @asynccontextmanager
 async def lifespan(app: FastAPI):
     """Initialize database on startup."""
+    assert_production_db_config()
     await init_database()
     yield
 
@@ -64,6 +78,8 @@ app.add_middleware(
     allow_headers=["*"],
 )
 
+app.include_router(admin_router)
+
 
 # =============================================================================
 # Auth Endpoints
@@ -74,12 +90,19 @@ class AuthRequest(BaseModel):
     password: str
 
 
+class RegisterRequest(BaseModel):
+    email: str
+    password: str
+    full_name: str = ""
+    school: str = ""
+
+
 @app.post("/api/auth/register")
-async def api_register(body: AuthRequest):
-    user = await register_user(body.email, body.password)
+async def api_register(body: RegisterRequest):
+    user = await register_user(body.email, body.password, body.full_name, body.school)
     from .auth import create_access_token
     token = create_access_token({"sub": str(user["id"])})
-    return {"token": token, "user": {"id": user["id"], "email": user["email"], "display_name": user["display_name"]}}
+    return {"token": token, "user": public_user(user)}
 
 
 @app.post("/api/auth/login")
@@ -90,7 +113,7 @@ async def api_login(body: AuthRequest):
 
 @app.get("/api/auth/me")
 async def api_me(user=Depends(get_current_user)):
-    return {"id": user["id"], "email": user["email"], "display_name": user["display_name"]}
+    return public_user(user)
 
 
 # =============================================================================
@@ -122,6 +145,27 @@ async def api_get_session(session_id: int, user=Depends(get_current_user)):
     return session
 
 
+class UpdateSessionRequest(BaseModel):
+    title: Optional[str] = None
+    subject: Optional[str] = None
+
+
+@app.patch("/api/sessions/{session_id}")
+async def api_update_session(
+    session_id: int, body: UpdateSessionRequest, user=Depends(get_current_user)
+):
+    await _session_or_404(session_id, user)
+    await update_session_meta(session_id, title=body.title, subject=body.subject)
+    return await get_session(session_id)
+
+
+@app.delete("/api/sessions/{session_id}")
+async def api_delete_session(session_id: int, user=Depends(get_current_user)):
+    await _session_or_404(session_id, user)
+    await delete_session(session_id)
+    return {"status": "deleted"}
+
+
 # =============================================================================
 # File Upload & Processing Endpoints
 # =============================================================================
@@ -153,6 +197,26 @@ async def api_upload_files(
             detail="data_type must be 'questions', 'answers', 'student_answers', or 'teacher_answers'",
         )
 
+    # Persist the raw source files (best-effort) before parsing consumes them.
+    for f in files:
+        try:
+            content = await f.read()
+            storage_path = await upload_raw_file(
+                session_id, data_type, f.filename or "file", content, f.content_type or ""
+            )
+            await save_raw_file(
+                session_id,
+                data_type,
+                f.filename or "file",
+                f.content_type or "",
+                len(content),
+                storage_path or "",
+            )
+            await f.seek(0)
+        except Exception:
+            # Raw-file archival is non-critical; never block parsing on it.
+            await f.seek(0)
+
     try:
         structured = await process_uploaded_files(
             files,
@@ -369,13 +433,62 @@ async def api_generate_student_report(
 
     try:
         html = await generate_student_report(grid, body.student_index, body.model)
-        report_id = await save_report(session_id, None, html)
         student_name = grid.students[body.student_index].name
-        return {"report_id": report_id, "html_content": html, "student_name": student_name}
+        # Link the report to a first-class student + persist this quiz result,
+        # so the data is queryable across quizzes for the dashboard.
+        student_id = await match_or_create_student(user["id"], student_name)
+        total, correct, score = grid_score_student(grid, body.student_index)
+        await save_student_result(
+            quiz_id=session_id,
+            student_id=student_id,
+            answers={"answers": list(grid.students[body.student_index].answers)},
+            total_questions=total,
+            correct_count=correct,
+            score=score,
+        )
+        report_id = await save_report(
+            session_id, None, html, student_id=student_id, model=body.model
+        )
+        return {
+            "report_id": report_id,
+            "html_content": html,
+            "student_name": student_name,
+            "student_id": student_id,
+            "score": score,
+        }
     except Exception as e:
         raise HTTPException(status_code=500, detail=f"Report generation failed: {str(e)}")
 
 
+@app.post("/api/sessions/{session_id}/save-to-db")
+async def api_save_to_db(session_id: int, user=Depends(get_current_user)):
+    """Commit the confirmed grid as durable analytics data.
+
+    Creates/matches a student per row (by name, within this teacher) and stores
+    each student's result for this quiz, then marks the quiz as saved. This is
+    what the "Save to database" button calls.
+    """
+    await _session_or_404(session_id, user)
+    grid = await _require_confirmed_grid(session_id)
+
+    saved = []
+    for idx, student in enumerate(grid.students):
+        student_id = await match_or_create_student(user["id"], student.name)
+        total, correct, score = grid_score_student(grid, idx)
+        await save_student_result(
+            quiz_id=session_id,
+            student_id=student_id,
+            answers={"answers": list(student.answers)},
+            total_questions=total,
+            correct_count=correct,
+            score=score,
+        )
+        saved.append({"student_id": student_id, "name": student.name, "score": score})
+
+    await update_session_status(session_id, "saved")
+    return {"status": "saved", "students_saved": len(saved), "students": saved}
+
+
 @app.get("/api/sessions/{session_id}/reports")
 async def api_list_reports(session_id: int, user=Depends(get_current_user)):
     session = await get_session(session_id)

chatkit/backend/app/models.py

@@ -0,0 +1,153 @@
+"""SQLAlchemy ORM models for ClassLens.
+
+All tables are isolated in a dedicated schema (``classlens`` on Supabase) so they
+never collide with other apps sharing the same Postgres instance.
+"""
+
+from __future__ import annotations
+
+from datetime import datetime
+from typing import Optional
+
+from sqlalchemy import (
+    JSON,
+    Boolean,
+    DateTime,
+    Float,
+    ForeignKey,
+    Integer,
+    MetaData,
+    String,
+    Text,
+    UniqueConstraint,
+    func,
+)
+from sqlalchemy.orm import DeclarativeBase, Mapped, mapped_column
+
+from .db import active_schema
+
+SCHEMA = active_schema()
+
+
+def _fk(target: str) -> str:
+    """Schema-qualified FK target, e.g. 'classlens.teachers.id' or 'teachers.id'."""
+    return f"{SCHEMA}.{target}" if SCHEMA else target
+
+
+class Base(DeclarativeBase):
+    metadata = MetaData(schema=SCHEMA)
+
+
+class Teacher(Base):
+    __tablename__ = "teachers"
+
+    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
+    email: Mapped[str] = mapped_column(String(255), unique=True, nullable=False, index=True)
+    password_hash: Mapped[str] = mapped_column(String(255), nullable=False)
+    display_name: Mapped[str] = mapped_column(String(255), default="")
+    full_name: Mapped[str] = mapped_column(String(255), default="")
+    school: Mapped[str] = mapped_column(String(255), default="")
+    is_admin: Mapped[bool] = mapped_column(Boolean, default=False, nullable=False)
+    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
+    last_login: Mapped[Optional[datetime]] = mapped_column(DateTime(timezone=True), nullable=True)
+
+
+class Student(Base):
+    __tablename__ = "students"
+    __table_args__ = (
+        UniqueConstraint("teacher_id", "normalized_name", name="uq_student_teacher_name"),
+    )
+
+    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
+    teacher_id: Mapped[int] = mapped_column(ForeignKey(_fk("teachers.id"), ondelete="CASCADE"), index=True)
+    name: Mapped[str] = mapped_column(String(255), nullable=False)
+    normalized_name: Mapped[str] = mapped_column(String(255), nullable=False, index=True)
+    external_id: Mapped[Optional[str]] = mapped_column(String(128), nullable=True)
+    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
+
+
+class Quiz(Base):
+    __tablename__ = "quizzes"
+
+    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
+    teacher_id: Mapped[int] = mapped_column(ForeignKey(_fk("teachers.id"), ondelete="CASCADE"), index=True)
+    title: Mapped[str] = mapped_column(String(512), default="Untitled Session")
+    subject: Mapped[Optional[str]] = mapped_column(String(255), nullable=True)
+    status: Mapped[str] = mapped_column(String(32), default="draft")
+    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
+    updated_at: Mapped[datetime] = mapped_column(
+        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
+    )
+
+
+class RawFile(Base):
+    __tablename__ = "raw_files"
+
+    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
+    quiz_id: Mapped[int] = mapped_column(ForeignKey(_fk("quizzes.id"), ondelete="CASCADE"), index=True)
+    data_type: Mapped[str] = mapped_column(String(64), nullable=False)
+    file_name: Mapped[str] = mapped_column(String(512), default="")
+    content_type: Mapped[str] = mapped_column(String(128), default="")
+    size_bytes: Mapped[int] = mapped_column(Integer, default=0)
+    storage_path: Mapped[str] = mapped_column(String(1024), default="")
+    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
+
+
+class ParsedData(Base):
+    __tablename__ = "parsed_data"
+
+    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
+    quiz_id: Mapped[int] = mapped_column(ForeignKey(_fk("quizzes.id"), ondelete="CASCADE"), index=True)
+    data_type: Mapped[str] = mapped_column(String(64), nullable=False)
+    file_name: Mapped[str] = mapped_column(String(512), default="")
+    raw_text: Mapped[str] = mapped_column(Text, default="")
+    structured_data: Mapped[dict] = mapped_column(JSON, nullable=False)
+    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
+
+
+class StudentResult(Base):
+    """One student's outcome on one quiz — the backbone for cross-quiz analytics."""
+
+    __tablename__ = "student_results"
+    __table_args__ = (
+        UniqueConstraint("quiz_id", "student_id", name="uq_result_quiz_student"),
+    )
+
+    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
+    quiz_id: Mapped[int] = mapped_column(ForeignKey(_fk("quizzes.id"), ondelete="CASCADE"), index=True)
+    student_id: Mapped[int] = mapped_column(ForeignKey(_fk("students.id"), ondelete="CASCADE"), index=True)
+    answers: Mapped[dict] = mapped_column(JSON, default=dict)
+    total_questions: Mapped[int] = mapped_column(Integer, default=0)
+    correct_count: Mapped[int] = mapped_column(Integer, default=0)
+    score: Mapped[float] = mapped_column(Float, default=0.0)
+    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
+
+
+class Prompt(Base):
+    __tablename__ = "prompts"
+
+    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
+    teacher_id: Mapped[int] = mapped_column(ForeignKey(_fk("teachers.id"), ondelete="CASCADE"), index=True)
+    name: Mapped[str] = mapped_column(String(255), nullable=False)
+    content: Mapped[str] = mapped_column(Text, nullable=False)
+    is_default: Mapped[bool] = mapped_column(Boolean, default=False)
+    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())
+    updated_at: Mapped[datetime] = mapped_column(
+        DateTime(timezone=True), server_default=func.now(), onupdate=func.now()
+    )
+
+
+class Report(Base):
+    __tablename__ = "reports"
+
+    id: Mapped[int] = mapped_column(Integer, primary_key=True, autoincrement=True)
+    quiz_id: Mapped[int] = mapped_column(ForeignKey(_fk("quizzes.id"), ondelete="CASCADE"), index=True)
+    student_id: Mapped[Optional[int]] = mapped_column(
+        ForeignKey(_fk("students.id"), ondelete="SET NULL"), nullable=True, index=True
+    )
+    prompt_id: Mapped[Optional[int]] = mapped_column(
+        ForeignKey(_fk("prompts.id"), ondelete="SET NULL"), nullable=True
+    )
+    html_content: Mapped[str] = mapped_column(Text, nullable=False)
+    model: Mapped[str] = mapped_column(String(64), default="")
+    created_at: Mapped[datetime] = mapped_column(DateTime(timezone=True), server_default=func.now())

chatkit/backend/app/storage.py

@@ -0,0 +1,64 @@
+"""Raw-file storage on Supabase Storage (object storage).
+
+Bytes live in a Storage bucket; only metadata + the object path are kept in the
+``raw_files`` table. Uploads are best-effort: if storage isn't configured (e.g.
+local dev or SQLite mode), uploads are skipped and an empty path is returned so
+the rest of the flow keeps working.
+"""
+
+from __future__ import annotations
+
+from functools import lru_cache
+from typing import Optional
+
+from starlette.concurrency import run_in_threadpool
+
+from .config import get_settings
+
+
+def storage_enabled() -> bool:
+    s = get_settings()
+    return bool(s.supabase_url and s.supabase_service_role_key)
+
+
+@lru_cache
+def _client():
+    from supabase import create_client
+
+    s = get_settings()
+    return create_client(s.supabase_url, s.supabase_service_role_key)
+
+
+def _ensure_bucket(bucket: str):
+    client = _client()
+    try:
+        existing = {b.name for b in client.storage.list_buckets()}
+        if bucket not in existing:
+            client.storage.create_bucket(bucket, options={"public": False})
+    except Exception:
+        # Bucket may already exist or listing may be restricted; uploads will surface real errors.
+        pass
+
+
+def _upload_sync(path: str, data: bytes, content_type: str) -> str:
+    s = get_settings()
+    bucket = s.supabase_storage_bucket
+    _ensure_bucket(bucket)
+    client = _client()
+    client.storage.from_(bucket).upload(
+        path,
+        data,
+        {"content-type": content_type or "application/octet-stream", "upsert": "true"},
+    )
+    return path
+
+
+async def upload_raw_file(
+    quiz_id: int, data_type: str, file_name: str, data: bytes, content_type: str
+) -> Optional[str]:
+    """Upload bytes and return the storage object path, or None if disabled."""
+    if not storage_enabled():
+        return None
+    safe_name = file_name.replace("/", "_") or "file"
+    path = f"quiz_{quiz_id}/{data_type}/{safe_name}"
+    return await run_in_threadpool(_upload_sync, path, data, content_type)

chatkit/backend/pyproject.toml

@@ -12,6 +12,10 @@ dependencies = [
     "python-multipart>=0.0.6",
     "python-dotenv>=1.0.0",
     "aiosqlite>=0.19.0",
+    "sqlalchemy[asyncio]>=2.0.30",
+    "asyncpg>=0.29.0",
+    "alembic>=1.13.0",
+    "supabase>=2.6.0",
     "cryptography>=41.0.0",
     "pydantic>=2.0.0",
     "httpx>=0.25.0",

chatkit/backend/tests/test_csv_strict.py

@@ -290,17 +290,17 @@ async def test_combined_answers_persists_both_subtypes(tmp_path, monkeypatch):
     from app import database
     from app.file_processor import process_uploaded_files
 
-    # Point the DB at a fresh file
-    db_file = tmp_path / "test.db"
-    monkeypatch.setattr(database, "DATABASE_PATH", db_file)
-    monkeypatch.setattr(
-        database.get_settings.__wrapped__, "__defaults__", None, raising=False
-    )
-    # Ensure config returns no override
+    # Point the DB at a fresh local SQLite file and rebuild the engine.
     from app import config as config_module
+    from app import db as db_module
 
+    db_file = tmp_path / "test.db"
     cached = config_module.get_settings()
-    monkeypatch.setattr(cached, "database_path", "", raising=False)
+    monkeypatch.setattr(cached, "database_provider", "sqlite", raising=False)
+    monkeypatch.setattr(cached, "supabase_db_url", "", raising=False)
+    monkeypatch.setattr(cached, "database_path", str(db_file), raising=False)
+    db_module.get_engine.cache_clear()
+    db_module.get_sessionmaker.cache_clear()
 
     await database.init_database()
     user_id = await database.create_user("t@example.com", "h", "T")

chatkit/backend/tests/test_database.py

@@ -0,0 +1,108 @@
+"""Tests for the SQLAlchemy data-access layer (run against a temp SQLite file)."""
+
+from __future__ import annotations
+
+import pytest
+
+from app import config as config_module
+from app import database
+from app import db as db_module
+from app.answer_grid import from_dict as grid_from_dict, score_student
+
+
+@pytest.fixture
+async def fresh_db(tmp_path, monkeypatch):
+    db_file = tmp_path / "t.db"
+    cached = config_module.get_settings()
+    monkeypatch.setattr(cached, "database_provider", "sqlite", raising=False)
+    monkeypatch.setattr(cached, "supabase_db_url", "", raising=False)
+    monkeypatch.setattr(cached, "database_path", str(db_file), raising=False)
+    monkeypatch.setattr(cached, "admin_emails", "", raising=False)
+    db_module.get_engine.cache_clear()
+    db_module.get_sessionmaker.cache_clear()
+    await database.init_database()
+    yield
+    db_module.get_engine.cache_clear()
+    db_module.get_sessionmaker.cache_clear()
+
+
+@pytest.mark.unit
+async def test_teacher_profile_roundtrip(fresh_db):
+    uid = await database.create_user(
+        "t@example.com", "hash", full_name="Wang", school="First High"
+    )
+    user = await database.get_user_by_email("t@example.com")
+    assert user["id"] == uid
+    assert user["full_name"] == "Wang"
+    assert user["school"] == "First High"
+    assert user["is_admin"] is False
+
+
+@pytest.mark.unit
+async def test_student_matched_by_normalized_name(fresh_db):
+    uid = await database.create_user("t@example.com", "h")
+    a = await database.match_or_create_student(uid, "Alice Chen")
+    b = await database.match_or_create_student(uid, "  alice   chen ")
+    c = await database.match_or_create_student(uid, "Bob")
+    assert a == b  # whitespace/case-insensitive match
+    assert a != c
+    students = await database.get_students(uid)
+    assert len(students) == 2
+
+
+@pytest.mark.unit
+async def test_student_timeline_across_quizzes(fresh_db):
+    uid = await database.create_user("t@example.com", "h")
+    sid = await database.match_or_create_student(uid, "Alice")
+
+    q1 = await database.create_session(uid, "Quiz 1")
+    await database.save_student_result(q1, sid, {"answers": ["A"]}, 5, 3, 60.0)
+    q2 = await database.create_session(uid, "Quiz 2")
+    await database.save_student_result(q2, sid, {"answers": ["B"]}, 5, 5, 100.0)
+
+    timeline = await database.get_student_timeline(sid)
+    assert [t["score"] for t in timeline] == [60.0, 100.0]
+    assert timeline[0]["quiz_title"] == "Quiz 1"
+
+
+@pytest.mark.unit
+async def test_save_student_result_is_upsert(fresh_db):
+    uid = await database.create_user("t@example.com", "h")
+    sid = await database.match_or_create_student(uid, "Alice")
+    q = await database.create_session(uid, "Quiz")
+    await database.save_student_result(q, sid, {"answers": []}, 5, 2, 40.0)
+    await database.save_student_result(q, sid, {"answers": []}, 5, 4, 80.0)
+    timeline = await database.get_student_timeline(sid)
+    assert len(timeline) == 1  # same quiz+student -> updated, not duplicated
+    assert timeline[0]["score"] == 80.0
+
+
+@pytest.mark.unit
+async def test_stats_counts(fresh_db):
+    uid = await database.create_user("t@example.com", "h")
+    sid = await database.match_or_create_student(uid, "Alice")
+    q = await database.create_session(uid, "Quiz")
+    await database.save_student_result(q, sid, {"answers": []}, 3, 3, 100.0)
+    stats = await database.get_stats()
+    assert stats["teachers"] == 1
+    assert stats["students"] == 1
+    assert stats["quizzes"] == 1
+    assert stats["student_results"] == 1
+
+
+@pytest.mark.unit
+def test_score_student_grades_only_concrete_keys():
+    grid = grid_from_dict(
+        {
+            "total_questions": 4,
+            # Q4 official is blank -> not graded
+            "official_answers": ["A", "B", "C", ""],
+            "students": [{"name": "Alice", "answers": ["A", "X", "=", ""]}],
+            "questions": [],
+        }
+    )
+    graded, correct, score = score_student(grid, 0)
+    # Graded = Q1..Q3 (3). Correct: Q1 (A), Q3 ("=") -> 2 correct, Q2 wrong.
+    assert graded == 3
+    assert correct == 2
+    assert score == pytest.approx(66.7, abs=0.1)

chatkit/env.example

@@ -1,50 +1,44 @@
-# ExamInsight Environment Variables
-# Copy to .env for local development
-# For HF Spaces, add these as Secrets in the Space settings
+# ClassLens Environment Variables
+# Copy to .env (project root or chatkit/) for local development.
+# For HF Spaces, add these as Secrets in the Space settings. NEVER commit real secrets.
 
 # =============================================================================
-# REQUIRED
+# OpenAI (vision parsing + report generation)
 # =============================================================================
-
-# OpenAI API Key (required for ChatKit)
 OPENAI_API_KEY=sk-your-openai-api-key
 
 # =============================================================================
-# GOOGLE OAUTH (optional - for private Google Sheets)
+# Database
 # =============================================================================
+# Provider: "supabase" (Postgres, durable) or "sqlite" (local dev/tests)
+DATABASE_PROVIDER=supabase
 
-# Get these from Google Cloud Console > APIs & Services > Credentials
-GOOGLE_CLIENT_ID=your-client-id.apps.googleusercontent.com
-GOOGLE_CLIENT_SECRET=GOCSPX-your-client-secret
+# Postgres schema that isolates ClassLens tables from anything else in the DB.
+DB_SCHEMA=classlens
 
-# Redirect URI - update for production
-# Local: http://localhost:8000/auth/callback
-# HF Spaces: https://taboola-cz-examinsight.hf.space/auth/callback
-GOOGLE_REDIRECT_URI=http://localhost:8000/auth/callback
+# Supabase connection (Project Settings > Database > Connection string).
+# Use the session pooler (port 5432). Keep the password URL-safe.
+SUPABASE_DB_URL=postgresql://postgres.<ref>:<password>@<region>.pooler.supabase.com:5432/postgres
 
-# =============================================================================
-# EMAIL (optional - for sending reports)
-# =============================================================================
+# Supabase project URL + service-role key (only used for raw-file Storage).
+SUPABASE_URL=https://<ref>.supabase.co
+SUPABASE_SERVICE_ROLE_KEY=your-service-role-key
+SUPABASE_STORAGE_BUCKET=classlens-raw-files
 
-# Option 1: Gmail SMTP (easier setup)
-GMAIL_USER=your-email@gmail.com
-GMAIL_APP_PASSWORD=your-16-char-app-password
-
-# Option 2: SendGrid API
-SENDGRID_API_KEY=SG.your-sendgrid-api-key
-SENDGRID_FROM_EMAIL=examinsight@yourdomain.com
+# Local SQLite path (only used when DATABASE_PROVIDER=sqlite; blank = auto).
+DATABASE_PATH=
 
 # =============================================================================
-# SECURITY
+# Auth
 # =============================================================================
+JWT_SECRET_KEY=change-me-in-prod
+JWT_EXPIRE_MINUTES=1440
 
-# Generate with: python -c "from cryptography.fernet import Fernet; print(Fernet.generate_key().decode())"
-ENCRYPTION_KEY=your-fernet-encryption-key
+# Comma-separated emails that are promoted to admin on startup.
+ADMIN_EMAILS=you@example.com
 
 # =============================================================================
-# FRONTEND (for HF Spaces domain key)
+# Misc
 # =============================================================================
-
-# Register your HF Space domain at:
-# https://platform.openai.com/settings/organization/security/domain-allowlist
-VITE_CHATKIT_API_DOMAIN_KEY=domain_pk_your-production-key
+ENCRYPTION_KEY=
+FRONTEND_URL=http://localhost:3000

chatkit/frontend/src/App.tsx

@@ -8,16 +8,12 @@ import { ParsedDataSummary } from "./components/step2/ParsedDataSummary";
 import { StudentSelector } from "./components/step2/StudentSelector";
 import { PromptEditor } from "./components/step2/PromptEditor";
 import { ReportViewer } from "./components/step3/ReportViewer";
+import { AdminDashboard } from "./components/admin/AdminDashboard";
 import { apiGet, apiPost, getToken, clearToken } from "./lib/api";
+import type { User } from "./types";
 
 const INVITE_CODE = "taboola-npo-cz";
 
-interface User {
-  id: number;
-  email: string;
-  display_name: string;
-}
-
 interface StudentInfo {
   index: number;
   name: string;
@@ -43,6 +39,7 @@ export default function App() {
   const [selectedIndices, setSelectedIndices] = useState<number[]>([]);
   const [reports, setReports] = useState<StudentReport[]>([]);
   const [isGenerating, setIsGenerating] = useState(false);
+  const [showAdmin, setShowAdmin] = useState(false);
 
   // Check for saved JWT on mount
   useEffect(() => {
@@ -205,9 +202,32 @@ ${doneReports
     return <LoginForm onLogin={setUser} />;
   }
 
+  if (showAdmin && user.is_admin) {
+    return (
+      <div className="min-h-screen">
+        <Header
+          userEmail={user.email}
+          isAdmin={user.is_admin}
+          showAdmin={showAdmin}
+          onToggleAdmin={() => setShowAdmin((v) => !v)}
+          onLogout={handleLogout}
+        />
+        <div className="pt-20">
+          <AdminDashboard />
+        </div>
+      </div>
+    );
+  }
+
   return (
     <div className="min-h-screen">
-      <Header userEmail={user.email} onLogout={handleLogout} />
+      <Header
+        userEmail={user.email}
+        isAdmin={user.is_admin}
+        showAdmin={showAdmin}
+        onToggleAdmin={() => setShowAdmin((v) => !v)}
+        onLogout={handleLogout}
+      />
 
       <div className="pt-20">
         <StepIndicator currentStep={currentStep} onStepClick={handleStepClick} />
@@ -285,6 +305,14 @@ ${doneReports
                 isGenerating={isGenerating}
                 onBack={() => setCurrentStep(2)}
                 onExportAllHtml={handleExportAllHtml}
+                onSaveToDb={
+                  sessionId
+                    ? () =>
+                        apiPost<{ students_saved: number }>(
+                          `/api/sessions/${sessionId}/save-to-db`
+                        )
+                    : undefined
+                }
               />
             </div>
           )}

chatkit/frontend/src/components/Header.tsx

@@ -3,9 +3,12 @@ import { clearToken } from "../lib/api";
 interface HeaderProps {
   userEmail: string;
   onLogout: () => void;
+  isAdmin?: boolean;
+  showAdmin?: boolean;
+  onToggleAdmin?: () => void;
 }
 
-export function Header({ userEmail, onLogout }: HeaderProps) {
+export function Header({ userEmail, onLogout, isAdmin, showAdmin, onToggleAdmin }: HeaderProps) {
   const handleLogout = () => {
     clearToken();
     onLogout();
@@ -29,6 +32,18 @@ export function Header({ userEmail, onLogout }: HeaderProps) {
         </div>
 
         <div className="flex items-center gap-4">
+          {isAdmin && onToggleAdmin && (
+            <button
+              onClick={onToggleAdmin}
+              className={`text-sm font-medium px-4 py-2 rounded-full border transition-colors ${
+                showAdmin
+                  ? "bg-[var(--color-primary)] text-white border-transparent"
+                  : "text-[var(--color-text-muted)] border-[var(--color-border)] hover:text-[var(--color-text)]"
+              }`}
+            >
+              {showAdmin ? "← 返回應用" : "管理後台"}
+            </button>
+          )}
           <div className="flex items-center gap-2 px-4 py-2 rounded-full bg-[var(--color-success)]/10 border border-[var(--color-success)]/20">
             <span className="w-2 h-2 rounded-full bg-[var(--color-success)]"></span>
             <span className="text-sm font-medium text-[var(--color-success)]">

chatkit/frontend/src/components/LoginForm.tsx

@@ -2,11 +2,7 @@ import { useState } from "react";
 import { apiPost, setToken } from "../lib/api";
 import { CLASSLENS_ICON } from "../lib/icon";
 
-interface User {
-  id: number;
-  email: string;
-  display_name: string;
-}
+import type { User } from "../types";
 
 interface AuthResponse {
   token: string;
@@ -21,6 +17,8 @@ export function LoginForm({ onLogin }: LoginFormProps) {
   const [mode, setMode] = useState<"login" | "register">("login");
   const [email, setEmail] = useState("");
   const [password, setPassword] = useState("");
+  const [fullName, setFullName] = useState("");
+  const [school, setSchool] = useState("");
   const [error, setError] = useState("");
   const [loading, setLoading] = useState(false);
 
@@ -31,7 +29,11 @@ export function LoginForm({ onLogin }: LoginFormProps) {
 
     try {
       const endpoint = mode === "login" ? "/api/auth/login" : "/api/auth/register";
-      const result = await apiPost<AuthResponse>(endpoint, { email, password });
+      const body =
+        mode === "login"
+          ? { email, password }
+          : { email, password, full_name: fullName, school };
+      const result = await apiPost<AuthResponse>(endpoint, body);
       setToken(result.token);
       onLogin(result.user);
     } catch (err: unknown) {
@@ -103,6 +105,36 @@ export function LoginForm({ onLogin }: LoginFormProps) {
                 autoFocus
               />
             </div>
+            {mode === "register" && (
+              <>
+                <div>
+                  <label className="block text-sm font-medium text-[var(--color-text-muted)] mb-2">
+                    姓名
+                  </label>
+                  <input
+                    type="text"
+                    value={fullName}
+                    onChange={(e) => setFullName(e.target.value)}
+                    placeholder="王小明"
+                    required
+                    className="input"
+                  />
+                </div>
+                <div>
+                  <label className="block text-sm font-medium text-[var(--color-text-muted)] mb-2">
+                    學校
+                  </label>
+                  <input
+                    type="text"
+                    value={school}
+                    onChange={(e) => setSchool(e.target.value)}
+                    placeholder="台北市立第一高級中學"
+                    required
+                    className="input"
+                  />
+                </div>
+              </>
+            )}
             <div>
               <label className="block text-sm font-medium text-[var(--color-text-muted)] mb-2">
                 密碼

chatkit/frontend/src/components/admin/AdminDashboard.tsx

@@ -0,0 +1,322 @@
+import { useEffect, useState } from "react";
+import { apiGet, apiPut, apiDelete } from "../../lib/api";
+
+type Tab = "overview" | "teachers" | "sessions" | "students";
+
+interface Stats {
+  teachers: number;
+  students: number;
+  quizzes: number;
+  reports: number;
+  student_results: number;
+  prompts: number;
+  schema_version: string | null;
+}
+
+interface TeacherRow {
+  id: number;
+  email: string;
+  full_name: string;
+  school: string;
+  is_admin: boolean;
+  created_at: string;
+  quiz_count: number;
+  student_count: number;
+}
+
+interface SessionRow {
+  id: number;
+  title: string;
+  subject: string | null;
+  status: string;
+  teacher_email: string;
+  report_count: number;
+  created_at: string;
+}
+
+interface StudentRow {
+  id: number;
+  name: string;
+  teacher_email: string;
+  result_count: number;
+  avg_score: number | null;
+}
+
+interface TimelineEntry {
+  quiz_title: string;
+  quiz_subject: string | null;
+  quiz_created_at: string;
+  total_questions: number;
+  correct_count: number;
+  score: number;
+}
+
+const TABS: { key: Tab; label: string }[] = [
+  { key: "overview", label: "總覽" },
+  { key: "teachers", label: "教師" },
+  { key: "sessions", label: "測驗" },
+  { key: "students", label: "學生" },
+];
+
+export function AdminDashboard() {
+  const [tab, setTab] = useState<Tab>("overview");
+
+  return (
+    <main className="max-w-6xl mx-auto px-4 pb-12">
+      <div className="text-center mb-6">
+        <h2 className="font-display text-2xl font-bold text-[var(--color-text)]">管理後台</h2>
+        <p className="text-[var(--color-text-muted)] mt-1">資料庫管理與使用分析</p>
+      </div>
+
+      <div className="flex justify-center gap-2 mb-6">
+        {TABS.map((t) => (
+          <button
+            key={t.key}
+            onClick={() => setTab(t.key)}
+            className={`px-4 py-2 text-sm font-medium rounded-lg transition-colors ${
+              tab === t.key
+                ? "bg-[var(--color-primary)] text-white"
+                : "text-[var(--color-text-muted)] hover:text-[var(--color-text)] border border-[var(--color-border)]"
+            }`}
+          >
+            {t.label}
+          </button>
+        ))}
+      </div>
+
+      {tab === "overview" && <Overview />}
+      {tab === "teachers" && <Teachers />}
+      {tab === "sessions" && <Sessions />}
+      {tab === "students" && <Students />}
+    </main>
+  );
+}
+
+function useFetch<T>(path: string, deps: unknown[] = []) {
+  const [data, setData] = useState<T | null>(null);
+  const [error, setError] = useState("");
+  const [loading, setLoading] = useState(true);
+  const [nonce, setNonce] = useState(0);
+  const reload = () => setNonce((n) => n + 1);
+
+  useEffect(() => {
+    let active = true;
+    setLoading(true);
+    apiGet<T>(path)
+      .then((d) => active && setData(d))
+      .catch((e) => active && setError(e instanceof Error ? e.message : "Error"))
+      .finally(() => active && setLoading(false));
+    return () => {
+      active = false;
+    };
+    // eslint-disable-next-line react-hooks/exhaustive-deps
+  }, [path, nonce, ...deps]);
+
+  return { data, error, loading, reload };
+}
+
+function StatCard({ label, value }: { label: string; value: number | string }) {
+  return (
+    <div className="card p-6 text-center">
+      <div className="text-3xl font-bold text-[var(--color-primary)]">{value}</div>
+      <div className="text-sm text-[var(--color-text-muted)] mt-1">{label}</div>
+    </div>
+  );
+}
+
+function Overview() {
+  const { data, loading, error } = useFetch<Stats>("/api/admin/stats");
+  if (loading) return <Spinner />;
+  if (error || !data) return <ErrorBox msg={error} />;
+  return (
+    <div>
+      <div className="grid grid-cols-2 md:grid-cols-3 gap-4">
+        <StatCard label="教師" value={data.teachers} />
+        <StatCard label="學生" value={data.students} />
+        <StatCard label="測驗" value={data.quizzes} />
+        <StatCard label="已生成報告" value={data.reports} />
+        <StatCard label="學生成績紀錄" value={data.student_results} />
+        <StatCard label="提示詞" value={data.prompts} />
+      </div>
+      <p className="text-center text-xs text-[var(--color-text-muted)] mt-6">
+        資料庫結構版本：{data.schema_version ?? "未知"}
+      </p>
+    </div>
+  );
+}
+
+function Teachers() {
+  const { data, loading, error, reload } = useFetch<{ users: TeacherRow[] }>("/api/admin/users");
+  if (loading) return <Spinner />;
+  if (error || !data) return <ErrorBox msg={error} />;
+
+  const toggleAdmin = async (u: TeacherRow) => {
+    await apiPut(`/api/admin/users/${u.id}/admin`, { is_admin: !u.is_admin });
+    reload();
+  };
+  const remove = async (u: TeacherRow) => {
+    if (!confirm(`刪除教師 ${u.email}？此操作會一併刪除其所有測驗與報告。`)) return;
+    await apiDelete(`/api/admin/users/${u.id}`);
+    reload();
+  };
+
+  return (
+    <Table headers={["Email", "姓名", "學校", "測驗", "學生", "管理員", ""]}>
+      {data.users.map((u) => (
+        <tr key={u.id} className="border-t border-[var(--color-border)]">
+          <Td>{u.email}</Td>
+          <Td>{u.full_name || "—"}</Td>
+          <Td>{u.school || "—"}</Td>
+          <Td>{u.quiz_count}</Td>
+          <Td>{u.student_count}</Td>
+          <Td>
+            <button onClick={() => toggleAdmin(u)} className="text-sm underline text-[var(--color-primary)]">
+              {u.is_admin ? "是" : "否"}
+            </button>
+          </Td>
+          <Td>
+            <button onClick={() => remove(u)} className="text-sm text-red-400 hover:underline">
+              刪除
+            </button>
+          </Td>
+        </tr>
+      ))}
+    </Table>
+  );
+}
+
+function Sessions() {
+  const { data, loading, error, reload } = useFetch<{ sessions: SessionRow[] }>("/api/admin/sessions");
+  if (loading) return <Spinner />;
+  if (error || !data) return <ErrorBox msg={error} />;
+
+  const remove = async (s: SessionRow) => {
+    if (!confirm(`刪除測驗「${s.title}」？`)) return;
+    await apiDelete(`/api/admin/sessions/${s.id}`);
+    reload();
+  };
+
+  return (
+    <Table headers={["標題", "科目", "教師", "狀態", "報告", "建立時間", ""]}>
+      {data.sessions.map((s) => (
+        <tr key={s.id} className="border-t border-[var(--color-border)]">
+          <Td>{s.title}</Td>
+          <Td>{s.subject || "—"}</Td>
+          <Td>{s.teacher_email}</Td>
+          <Td>{s.status}</Td>
+          <Td>{s.report_count}</Td>
+          <Td>{new Date(s.created_at).toLocaleDateString()}</Td>
+          <Td>
+            <button onClick={() => remove(s)} className="text-sm text-red-400 hover:underline">
+              刪除
+            </button>
+          </Td>
+        </tr>
+      ))}
+    </Table>
+  );
+}
+
+function Students() {
+  const { data, loading, error } = useFetch<{ students: StudentRow[] }>("/api/admin/students");
+  const [selected, setSelected] = useState<number | null>(null);
+
+  if (loading) return <Spinner />;
+  if (error || !data) return <ErrorBox msg={error} />;
+
+  if (selected !== null) {
+    return <StudentDetail studentId={selected} onBack={() => setSelected(null)} />;
+  }
+
+  return (
+    <Table headers={["姓名", "教師", "測驗次數", "平均分數", ""]}>
+      {data.students.map((s) => (
+        <tr key={s.id} className="border-t border-[var(--color-border)]">
+          <Td>{s.name}</Td>
+          <Td>{s.teacher_email}</Td>
+          <Td>{s.result_count}</Td>
+          <Td>{s.avg_score ?? "—"}</Td>
+          <Td>
+            <button onClick={() => setSelected(s.id)} className="text-sm underline text-[var(--color-primary)]">
+              查看歷程
+            </button>
+          </Td>
+        </tr>
+      ))}
+    </Table>
+  );
+}
+
+function StudentDetail({ studentId, onBack }: { studentId: number; onBack: () => void }) {
+  const { data, loading, error } = useFetch<{
+    student: { name: string };
+    timeline: TimelineEntry[];
+  }>(`/api/admin/students/${studentId}`);
+
+  if (loading) return <Spinner />;
+  if (error || !data) return <ErrorBox msg={error} />;
+
+  return (
+    <div>
+      <button onClick={onBack} className="text-sm text-[var(--color-text-muted)] hover:underline mb-4">
+        ← 返回學生列表
+      </button>
+      <h3 className="font-display text-xl font-semibold mb-4 text-[var(--color-text)]">
+        {data.student.name} 的跨測驗歷程
+      </h3>
+      {data.timeline.length === 0 ? (
+        <p className="text-[var(--color-text-muted)]">尚無成績紀錄</p>
+      ) : (
+        <Table headers={["測驗", "科目", "日期", "答對", "總題數", "分數"]}>
+          {data.timeline.map((t, i) => (
+            <tr key={i} className="border-t border-[var(--color-border)]">
+              <Td>{t.quiz_title}</Td>
+              <Td>{t.quiz_subject || "—"}</Td>
+              <Td>{new Date(t.quiz_created_at).toLocaleDateString()}</Td>
+              <Td>{t.correct_count}</Td>
+              <Td>{t.total_questions}</Td>
+              <Td>
+                <span className="font-semibold text-[var(--color-primary)]">{t.score}</span>
+              </Td>
+            </tr>
+          ))}
+        </Table>
+      )}
+    </div>
+  );
+}
+
+function Table({ headers, children }: { headers: string[]; children: React.ReactNode }) {
+  return (
+    <div className="card overflow-x-auto">
+      <table className="w-full text-sm">
+        <thead>
+          <tr className="text-left text-[var(--color-text-muted)]">
+            {headers.map((h, i) => (
+              <th key={i} className="px-4 py-3 font-medium">
+                {h}
+              </th>
+            ))}
+          </tr>
+        </thead>
+        <tbody className="text-[var(--color-text)]">{children}</tbody>
+      </table>
+    </div>
+  );
+}
+
+function Td({ children }: { children: React.ReactNode }) {
+  return <td className="px-4 py-3">{children}</td>;
+}
+
+function Spinner() {
+  return (
+    <div className="flex justify-center py-12">
+      <div className="w-6 h-6 border-2 border-[var(--color-primary)] border-t-transparent rounded-full animate-spin" />
+    </div>
+  );
+}
+
+function ErrorBox({ msg }: { msg: string }) {
+  return <div className="card p-6 text-center text-red-400">{msg || "載入失敗"}</div>;
+}

chatkit/frontend/src/components/step3/ReportViewer.tsx

@@ -6,6 +6,7 @@ interface ReportViewerProps {
   isGenerating: boolean;
   onBack: () => void;
   onExportAllHtml: () => void;
+  onSaveToDb?: () => Promise<{ students_saved: number }>;
 }
 
 export function ReportViewer({
@@ -13,10 +14,27 @@ export function ReportViewer({
   isGenerating,
   onBack,
   onExportAllHtml,
+  onSaveToDb,
 }: ReportViewerProps) {
   const [activeIndex, setActiveIndex] = useState(0);
+  const [saveState, setSaveState] = useState<"idle" | "saving" | "saved" | "error">("idle");
+  const [saveMsg, setSaveMsg] = useState("");
   const iframeRef = useRef<HTMLIFrameElement>(null);
 
+  const handleSaveToDb = async () => {
+    if (!onSaveToDb) return;
+    setSaveState("saving");
+    setSaveMsg("");
+    try {
+      const res = await onSaveToDb();
+      setSaveState("saved");
+      setSaveMsg(`已儲存 ${res.students_saved} 位學生成績`);
+    } catch (e) {
+      setSaveState("error");
+      setSaveMsg(e instanceof Error ? e.message : "儲存失敗");
+    }
+  };
+
   // Auto-switch to the report currently being generated
   useEffect(() => {
     const generatingIdx = reports.findIndex((r) => r.status === "generating");
@@ -83,11 +101,31 @@ export function ReportViewer({
           </button>
         )}
         {doneCount > 1 && (
-          <button onClick={onExportAllHtml} className="btn btn-primary text-sm">
+          <button onClick={onExportAllHtml} className="btn btn-outline text-sm">
             下載全部報告
           </button>
         )}
+        {onSaveToDb && !isGenerating && doneCount > 0 && (
+          <button
+            onClick={handleSaveToDb}
+            disabled={saveState === "saving" || saveState === "saved"}
+            className="btn btn-primary text-sm disabled:opacity-60"
+          >
+            {saveState === "saving" && "儲存中..."}
+            {saveState === "saved" && "✅ 已儲存"}
+            {(saveState === "idle" || saveState === "error") && "儲存至資料庫"}
+          </button>
+        )}
       </div>
+      {saveMsg && (
+        <p
+          className={`text-sm text-right ${
+            saveState === "error" ? "text-red-400" : "text-[var(--color-success)]"
+          }`}
+        >
+          {saveMsg}
+        </p>
+      )}
 
       {/* Student tabs */}
       <div className="flex gap-2 overflow-x-auto pb-2">

chatkit/frontend/src/types/index.ts

@@ -0,0 +1,8 @@
+export interface User {
+  id: number;
+  email: string;
+  display_name: string;
+  full_name?: string;
+  school?: string;
+  is_admin?: boolean;
+}