chatkit/backend/DATA_ACCESS.md

# ClassLens — Data Access Reference

> Last verified: 2026-07-03 against local SQLite dev DB (`classlens.db`).
> All curl examples use `http://127.0.0.1:8000` (local dev server).
> In production the same paths work against the Supabase-backed server.

---

## 1. Database schema

All tables live in the **`classlens`** Postgres schema on Supabase (no schema prefix in SQLite dev). Run migrations with `alembic upgrade head` before starting the server for the first time.

### teachers
| Column | Type | Notes |
|---|---|---|
| `id` | int PK | |
| `email` | varchar | unique, used as login |
| `password_hash` | varchar | bcrypt; never exposed to client |
| `full_name` | varchar | |
| `display_name` | varchar | shown in UI header |
| `school` | varchar | |
| `is_admin` | bool | promoted via `ADMIN_EMAILS` env var |

### students
| Column | Type | Notes |
|---|---|---|
| `id` | int PK | |
| `teacher_id` | int FK → teachers | every query is teacher-scoped |
| `name` | varchar | display name |
| `normalized_name` | varchar | lower-cased, whitespace-collapsed — used for cross-quiz dedup |

A student is created automatically the first time their name appears in a report. The same student name across multiple quiz uploads maps to the same `id` via `normalized_name`.

### quizzes
| Column | Type | Notes |
|---|---|---|
| `id` | int PK | also called `session_id` in endpoints |
| `teacher_id` | int FK → teachers | |
| `title` | varchar | auto-numbered "Quiz N" if omitted |
| `status` | varchar | `draft` → `processed` → `saved` |
| `created_at` | timestamptz | used as x-axis in trend chart |

### student_results
One row per student per quiz — the central analytics table.

| Column | Type | Notes |
|---|---|---|
| `id` | int PK | |
| `quiz_id` | int FK → quizzes | |
| `student_id` | int FK → students | |
| `score` | float | 0–100 |
| `weaknesses` | text | plain-text paragraph from the LLM analytics call |
| `study_recommendations` | text | plain-text paragraph from the LLM analytics call |
| `created_at` | timestamptz | |

> `weaknesses` and `study_recommendations` are plain text strings, **not** JSON.
> They are written by `extract_student_analytics()` in `report_generator.py`.

### parsed_data
One row per question per quiz — populated when a questions PDF is uploaded.

| Column | Type | Notes |
|---|---|---|
| `id` | int PK | |
| `quiz_id` | int FK → quizzes | |
| `question_num` | int | 1-indexed |
| `question_str` | text | full question text extracted from PDF |
| `answer` | varchar(10) | correct answer letter (A/B/C/D); empty until answer PDF uploaded |
| `main_category` | varchar(64) | one of the 6 taxonomy categories; set by background categorization |
| `tags` | JSON array | sub-tags from that category; set by background categorization |

### question_bank
Shared pool accumulated from all teacher uploads (added in migration 0003).

| Column | Type | Notes |
|---|---|---|
| `id` | int PK | |
| `quiz_id` | int, nullable | source quiz (SET NULL on quiz delete) |
| `teacher_id` | int, nullable | uploading teacher (SET NULL on teacher delete) |
| `question_text` | text | |
| `answer` | varchar(10) | may be empty |
| `main_category` | varchar(64), indexed | |
| `tags` | JSON array | |

---

## 2. Category taxonomy

Six main categories. Two have sub-tags; four do not.

| Category | Has sub-tags? | Sample tags |
|---|---|---|
| 字詞理解 | Yes | 形容詞用法, 動詞與動詞片語, 情境字彙推斷 |
| 語法結構 | Yes | 現在簡單式, 過去簡單式, 被動語態, 關係代名詞 |
| 文意推論 | No | — |
| 篇章大意 | No | — |
| 篇章細節 | No | — |
| 篇章結構 | No | — |

Full taxonomy is the single source of truth in `app/taxonomy.py` (backend) and the `TAXONOMY` constant in `TeacherDashboard.tsx` (frontend).

---

## 3. API endpoints — dashboard components

All endpoints require `Authorization: Bearer <token>`. Teacher scope is enforced server-side — never pass `teacher_id` as a query param.

### Authentication

```
POST /api/auth/login
Body: { "email": "...", "password": "..." }
→ { "token": "<jwt>", "user": { "id", "email", "display_name", "full_name", "school", "is_admin" } }
```

The JWT is stored in `localStorage` under the key `classlens_token`.

---

### Student list panel → `GET /api/students`

Returns all students belonging to the logged-in teacher, with aggregate stats.

```json
{
  "students": [
    { "id": 1, "name": "Alice Chen", "avg_score": 90.0, "result_count": 2 },
    { "id": 2, "name": "Bob Lee",   "avg_score": 50.0, "result_count": 2 }
  ]
}
```

Key fields: `name` (display), `result_count` (number of quizzes taken), `avg_score` (overall average, 0–100 or null if no results).

---

### Student detail + assessment timeline → `GET /api/students/{student_id}`

Returns the student profile plus a chronological list of all quiz results.

```json
{
  "student": { "id": 1, "name": "Alice Chen" },
  "timeline": [
    {
      "quiz_id": 1,
      "quiz_title": "Quiz 1",
      "quiz_created_at": "2026-07-03T03:50:08",
      "score": 100.0,
      "weaknesses": "語法結構中的現在簡單式使用正確，無明顯弱點。",
      "study_recommendations": "建議延伸學習現在完成式與過去進行式的區別。"
    },
    {
      "quiz_id": 2,
      "quiz_title": "Quiz 2",
      "quiz_created_at": "2026-07-03T03:50:08",
      "score": 80.0,
      "weaknesses": "過去簡單式偶有混淆，被動語態理解待加強。",
      "study_recommendations": "建議針對過去簡單式與被動語態進行專項練習。"
    }
  ],
  "weaknesses": "過去簡單式偶有混淆，被動語態理解待加強。",
  "study_recommendations": "建議針對過去簡單式與被動語態進行專項練習。"
}
```

**How `timeline` maps to dashboard components:**

| Dashboard element | Source field |
|---|---|
| Assessment trend chart (y-axis) | `timeline[].score` |
| Chart x-axis labels | `"Quiz " + (index+1)` — position-based, not `quiz_title` |
| Chart tooltip date | `timeline[].quiz_created_at` |
| Focus Areas text (most recent) | top-level `weaknesses` |
| Study Plan text (most recent) | top-level `study_recommendations` |

The top-level `weaknesses` / `study_recommendations` are the most recent non-null values from the timeline — not a synthesis.

---

### AI synthesis (Summary / Focus Areas / Study Recommendations) → `GET /api/students/{student_id}/ai-summary`

Calls the LLM to synthesize insights across **all** quizzes into three distinct paragraphs.

```json
{
  "summary": "Alice has maintained strong performance across both quizzes...",
  "focus_areas": "Alice's recurring challenge is passive voice construction...",
  "study_recommendations": "Review 被動語態 by practising sentence transformations..."
}
```

The dashboard fetches this in parallel with the student detail call. The three fields map directly to the **Summary**, **Focus Areas**, and **Study Recommendations** cards.

---

### Questions with categories → `GET /api/sessions/{session_id}/parsed-data`

Returns every question for a quiz with its category and tags. Used by the question generation panel.

```json
{
  "parsed_data": {
    "questions": [
      {
        "id": 1,
        "quiz_id": 1,
        "question_num": 1,
        "question_str": "She _____ to school every day.",
        "answer": "A",
        "main_category": "語法結構",
        "tags": ["時態", "現在簡單式"],
        "created_at": "2026-07-03T03:50:08"
      }
    ]
  }
}
```

`main_category` and `tags` are populated by the background categorization job that runs after each questions PDF upload. They may be empty strings/arrays until that job completes.

---

### Quiz list → `GET /api/sessions`

```json
{
  "sessions": [
    { "id": 1, "teacher_id": 1, "title": "Quiz 1", "status": "draft", "created_at": "..." },
    { "id": 2, "teacher_id": 1, "title": "Quiz 2", "status": "draft", "created_at": "..." }
  ]
}
```

---

## 4. Test results — confirmed working (2026-07-03)

All tests run against local SQLite DB, server at `http://127.0.0.1:8000`.

```bash
# Start server
cd chatkit/backend
DATABASE_PROVIDER=sqlite uvicorn app.main:app --port 8000

# Get token
TOKEN=$(curl -s -X POST http://127.0.0.1:8000/api/auth/login \
  -H "Content-Type: application/json" \
  -d '{"email":"test@school.edu","password":"testpass123"}' \
  | python3 -c "import sys,json; print(json.load(sys.stdin)['token'])")

# 1. Health check ✓ → 200
curl http://127.0.0.1:8000/api/health
# { "status": "healthy", "service": "ClassLens", "version": "2.0.0" }

# 2. Student list ✓ → 200
curl http://127.0.0.1:8000/api/students \
  -H "Authorization: Bearer $TOKEN"
# { "students": [{ "id":1, "name":"Alice Chen", "avg_score":90.0, "result_count":2 }, ...] }

# 3. Student detail + timeline ✓ → 200
curl http://127.0.0.1:8000/api/students/1 \
  -H "Authorization: Bearer $TOKEN"
# { "student": {...}, "timeline": [{ "score":100.0, ... }, { "score":80.0, ... }], ... }

# 4. Quiz list ✓ → 200
curl http://127.0.0.1:8000/api/sessions \
  -H "Authorization: Bearer $TOKEN"
# { "sessions": [{ "id":1, "title":"Quiz 1" }, { "id":2, "title":"Quiz 2" }] }

# 5. Questions with categories ✓ → 200
curl http://127.0.0.1:8000/api/sessions/1/parsed-data \
  -H "Authorization: Bearer $TOKEN"
# { "parsed_data": { "questions": [{ "question_num":1, "main_category":"語法結構", "tags":["時態","現在簡單式"] }, ...] } }

# 6. AI summary (requires OPENAI_API_KEY set)
curl http://127.0.0.1:8000/api/students/1/ai-summary \
  -H "Authorization: Bearer $TOKEN"
# { "summary": "...", "focus_areas": "...", "study_recommendations": "..." }
```

---

## 5. Notes for the data layer

1. **Teacher scope is automatic.** The server joins through `teacher_id` — never pass it as a query param.

2. **Student identity is name-matched.** The `normalized_name` column (lower-case, single-space) deduplicates the same student across multiple quiz uploads. `match_or_create_student()` is called during report generation.

3. **`weaknesses` is plain text.** It is a paragraph written by the analytics LLM, not a structured JSON array. The dashboard renders it as a text block under the Focus Areas heading.

4. **Category data lags the upload by seconds.** `main_category` and `tags` in `parsed_data` are filled by a background `asyncio.create_task` that runs after the upload endpoint returns. They will be empty immediately after upload and populated within a few seconds.

5. **The 4 reading categories have no sub-tags.** `文意推論`, `篇章大意`, `篇章細節`, `篇章結構` — `tags` will always be `[]` for questions in these categories.

6. **Local dev — no Supabase needed:**
   ```bash
   cd chatkit/backend
   DATABASE_PROVIDER=sqlite uvicorn app.main:app --port 8000
   ```
   The SQLite file is `chatkit/backend/classlens.db`. If it's missing or stale, `init_database()` creates it fresh on startup using `Base.metadata.create_all`.

7. **Seed test data** after recreating the DB:
   ```bash
   # See chatkit/backend/tests/test_database.py for fixture pattern
   DATABASE_PROVIDER=sqlite python3 -c "
   import asyncio
   from app.database import init_database, create_user, ...
   asyncio.run(seed())
   "
   ```