chatkit/backend/DATABASE.md

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

# 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