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.

{
  "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.

{
  "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.

{
  "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.

{
  "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

{
  "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.

# 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:

   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:

   # 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())
   "