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

```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
```