# Design: API Contracts, Database, Seeds, UI

Base URL: `/api` · Auth: `Authorization: Bearer <JWT>` · Errors: `{ "error": { "code": "STRING", "message": "..." } }`

## 1. Database Schema (MySQL 8, schema `revision`)

```sql
CREATE TABLE users (
  id BIGINT AUTO_INCREMENT PRIMARY KEY,
  email VARCHAR(190) NOT NULL UNIQUE,
  password_hash VARCHAR(255) NOT NULL,
  full_name VARCHAR(50) NOT NULL,
  role ENUM('admin','student','parent') NOT NULL,
  board_id INT NULL, class_id INT NULL,
  onboarded TINYINT(1) NOT NULL DEFAULT 0,
  is_active TINYINT(1) NOT NULL DEFAULT 1,
  subjects_json JSON NULL, chapters_json JSON NULL, topics_json JSON NULL,
  quiz_sessions_json JSON NULL, attempts_json JSON NULL,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (board_id) REFERENCES boards(id),
  FOREIGN KEY (class_id) REFERENCES classes(id)
);
CREATE TABLE boards  (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(20) UNIQUE, name VARCHAR(80), is_active TINYINT(1) DEFAULT 1);
CREATE TABLE classes (id INT AUTO_INCREMENT PRIMARY KEY, board_id INT NOT NULL, grade TINYINT NOT NULL, label VARCHAR(30), is_active TINYINT(1) DEFAULT 1,
  UNIQUE (board_id, grade), FOREIGN KEY (board_id) REFERENCES boards(id));
-- NEW in v2.0: admin-managed subjects
CREATE TABLE subjects (id INT AUTO_INCREMENT PRIMARY KEY, board_id INT NOT NULL, class_id INT NOT NULL,
  name VARCHAR(80) NOT NULL, is_core TINYINT(1) DEFAULT 1, is_active TINYINT(1) DEFAULT 1,
  UNIQUE (class_id, name), FOREIGN KEY (board_id) REFERENCES boards(id), FOREIGN KEY (class_id) REFERENCES classes(id));
-- NEW: books viewable by students
CREATE TABLE books (id INT AUTO_INCREMENT PRIMARY KEY, subject_id INT NOT NULL, title VARCHAR(160) NOT NULL,
  file_path VARCHAR(255) NOT NULL, page_count INT NULL, allow_download TINYINT(1) DEFAULT 0,
  is_active TINYINT(1) DEFAULT 1, uploaded_by BIGINT, created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  FOREIGN KEY (subject_id) REFERENCES subjects(id));
CREATE TABLE book_progress (user_id BIGINT, book_id INT, last_page INT DEFAULT 1, updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, PRIMARY KEY (user_id, book_id));
-- NEW: parent monitoring
CREATE TABLE parent_student_links (id BIGINT AUTO_INCREMENT PRIMARY KEY, parent_id BIGINT NOT NULL, student_id BIGINT NOT NULL,
  status ENUM('active','revoked') DEFAULT 'active', created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  UNIQUE (parent_id, student_id), FOREIGN KEY (parent_id) REFERENCES users(id), FOREIGN KEY (student_id) REFERENCES users(id));
CREATE TABLE link_codes (code_hash CHAR(64) PRIMARY KEY, student_id BIGINT NOT NULL, expires_at DATETIME NOT NULL, used_at DATETIME NULL);
CREATE TABLE signup_codes (id INT AUTO_INCREMENT PRIMARY KEY, code VARCHAR(32) UNIQUE, allowed_role ENUM('student','parent') NOT NULL,
  used_by BIGINT NULL, used_at DATETIME NULL, expires_at DATETIME NULL, created_by BIGINT);
CREATE TABLE student_score (user_id BIGINT PRIMARY KEY, total_points INT DEFAULT 0, attempts INT DEFAULT 0,
  current_streak INT DEFAULT 0, best_streak INT DEFAULT 0, last_active DATE NULL, tier_id INT NULL);
CREATE TABLE reward_tiers (id INT AUTO_INCREMENT PRIMARY KEY, name VARCHAR(30), min_points INT);
CREATE TABLE feedback_phrases (id INT AUTO_INCREMENT PRIMARY KEY, kind ENUM('correct','incorrect'), text VARCHAR(160), is_active TINYINT(1) DEFAULT 1);
```
**Triggers:** `trg_protect_system_admin` (block delete/demote of root admin) · `trg_score_after_attempt` (recompute points, streak, tier).
**Views:** `v_topic_mastery(user_id, subject, chapter, topic, accuracy, status)` with status `mastered|learning|needs_revision`; `v_weak_topics(user_id, subject, topic, accuracy)`; `v_child_summary` (used by parents).

## 2. Seed Query Structure
```sql
INSERT INTO boards (code,name) VALUES ('CBSE','CBSE'),('CISCE','CISCE / ICSE'),('STATE-TN','Tamil Nadu State Board'),('IB','IB'),('CAMBRIDGE','Cambridge');
INSERT INTO classes (board_id,grade,label) SELECT b.id,g.n,CONCAT('Class ',g.n) FROM boards b
  JOIN (SELECT 1 n UNION SELECT 2 UNION SELECT 3 UNION SELECT 4 UNION SELECT 5 UNION SELECT 6 UNION SELECT 7 UNION SELECT 8 UNION SELECT 9 UNION SELECT 10 UNION SELECT 11 UNION SELECT 12) g;
INSERT INTO subjects (board_id,class_id,name) SELECT c.board_id,c.id,s.name FROM classes c
  JOIN (SELECT 'Mathematics' name UNION SELECT 'Science' UNION SELECT 'English' UNION SELECT 'Social Science') s
  WHERE c.grade BETWEEN 6 AND 10;
INSERT INTO reward_tiers (name,min_points) VALUES ('Bronze',0),('Silver',500),('Gold',1500),('Platinum',3500),('Diamond',7500);
INSERT INTO feedback_phrases (kind,text) VALUES ('correct','Brilliant! Keep it up!'),('incorrect','Good try! Check the explanation and go again.');
INSERT INTO users (email,password_hash,full_name,role,onboarded) VALUES ('team@zettaisystems.com','<argon2>','System Admin','admin',1);
INSERT INTO signup_codes (code,allowed_role) VALUES ('DEMO-STUDENT-01','student'),('DEMO-PARENT-01','parent');
```

## 3. API Contracts

### Auth
| Method | Path | Roles |
|---|---|---|
| POST | `/auth/register` | public |
| POST | `/auth/login` | public |
| POST | `/auth/refresh` | any |
| GET | `/users/me` | any |

```jsonc
// POST /auth/register
{ "full_name":"Aarav Kumar","email":"a@x.com","password":"********","role":"student","access_code":"DEMO-STUDENT-01" }
// 201
{ "user":{ "id":12,"role":"student","onboarded":false }, "access_token":"jwt","refresh_token":"jwt" }
// POST /auth/login  { "email":"...","password":"..." }  -> same shape, 401 INVALID_CREDENTIALS
```

### Catalog (read: any auth; write: admin)
| Method | Path |
|---|---|
| GET | `/catalog/boards` |
| GET | `/catalog/classes?board_id=` |
| GET | `/catalog/subjects?board_id=&class_id=` |
| POST / PATCH / DELETE | `/admin/boards`, `/admin/classes`, `/admin/subjects` (soft delete via `is_active`) |

```jsonc
// GET /catalog/subjects?board_id=1&class_id=9
{ "items":[ { "id":31,"name":"Mathematics","is_core":true } ] }
// POST /admin/subjects
{ "board_id":1,"class_id":9,"name":"Mathematics","is_core":true }
```

### Onboarding (student)
```jsonc
// POST /users/me/onboarding
{ "full_name":"Aarav Kumar","board_id":1,"class_id":9,"subject_ids":[31,32,33] }
// 200 { "onboarded":true,"subjects":[{"id":31,"name":"Mathematics","is_initial":true}] }
// 422 SUBJECT_NOT_IN_CLASS
```

### Practice
| Method | Path | Notes |
|---|---|---|
| GET | `/subjects/{id}/chapters` | chapter/topic tree (AI-assisted, cached in `chapters_json`) |
| POST | `/quiz/sessions` | start MCQ or timed |
| POST | `/quiz/sessions/{id}/attempt` | submit |
| POST | `/revision/sessions` | revision mix |

```jsonc
// POST /quiz/sessions
{ "subject_id":31,"chapter":"Polynomials","topic":"Zeros of a polynomial","mode":"mcq" }   // mode: mcq | timed
// 201
{ "session_id":"s_8f2","mode":"mcq","ai_generated":true,"time_limit_sec":null,
  "questions":[ { "id":"q1","text":"...","options":["A","B","C","D"] } ] }
// POST /quiz/sessions/s_8f2/attempt
{ "answers":[ { "question_id":"q1","selected":2 } ], "time_taken_sec":212 }
// 200
{ "score":8,"total":10,"points_awarded":80,"total_points":1240,"tier":"Silver","streak":4,
  "feedback":"Brilliant!","review":[ { "question_id":"q1","correct":2,"is_correct":true,"explanation":"..." } ],
  "next_recommendation":{ "topic":"Quadratic equations" } }
// POST /revision/sessions { "subject_id":31 } -> same shape, ai_generated=false, source="stored", ordered wrong-first
// 409 NO_COMPLETED_SETS if the student has no 10/10 set yet
```
**Gemini output contract (Pydantic):** `{ "questions":[ { "text":str, "options":[str x4], "correct_index":0..3, "explanation":str } x10 ] }`

### Books
| Method | Path | Roles |
|---|---|---|
| GET | `/books` | student (own subjects), admin (all) |
| GET | `/books/{id}/file` | student/admin, streamed inline |
| PUT | `/books/{id}/progress` `{ "last_page":42 }` | student |
| POST | `/admin/books` (multipart) | admin |

### Analytics (student)
`GET /analytics/overview?range=week|month|year` > `{ "points_by_day":[...], "subjects":[{ "id":31,"mastery":0.72,"mastered":6,"learning":4,"needs_revision":2 }] }` · `GET /analytics/weak-topics?subject_id=` · `GET /analytics/next` (what to study next)

### Parent
| Method | Path |
|---|---|
| POST | `/users/me/link-code` (student) |
| POST | `/parent/links` `{ "code":"K7M2QX" }` |
| GET | `/parent/children` |
| GET | `/parent/children/{student_id}/summary` |
| GET | `/parent/children/{student_id}/weak-topics` |
| GET | `/parent/children/{student_id}/activity?range=week` |
| DELETE | `/parent/links/{student_id}` |

```jsonc
// GET /parent/children/12/summary
{ "student":{ "id":12,"name":"Aarav Kumar","board":"CBSE","class":"Class 9" },
  "total_points":1240,"tier":"Silver","streak":4,"last_active":"2026-10-26",
  "subjects":[ { "name":"Mathematics","mastery":0.72 } ],
  "weak_topics":[ { "subject":"Science","topic":"Force and laws of motion","accuracy":0.38 } ] }
// 403 NOT_LINKED
```

### Admin
`GET/POST/DELETE /admin/signup-codes` · `GET/PATCH /admin/users` · `POST /admin/parent-links` · CRUD `/admin/feedback-phrases`, `/admin/reward-tiers`. Errors: `403 SYSTEM_ADMIN_PROTECTED`.

## 4. Standard Error Codes
`INVALID_CREDENTIALS` 401 · `FORBIDDEN_ROLE` 403 · `NOT_LINKED` 403 · `SYSTEM_ADMIN_PROTECTED` 403 · `SUBJECT_NOT_IN_CLASS` 422 · `INVALID_ACCESS_CODE` 400 · `AI_INVALID_OUTPUT` 502 · `NO_COMPLETED_SETS` 409 · `RATE_LIMITED` 429

## 5. UI Design Spec (redesign)
**Tokens:** primary indigo `#4F46E5`, accent teal `#14B8A6`, surfaces slate (light `#F8FAFC` / dark `#0F172A`), radius 16, spacing 4-pt grid, font Inter / system.
**Floating bottom bar:** pill, 64px high, 16px side and bottom margin plus safe-area inset, elevation shadow, translucent blur, active tab shows icon + label, inactive icon only. Hides on quiz/timed screens to avoid mis-taps.
| Role | Tabs |
|---|---|
| Student | Home, Practice, Books, Progress, Profile |
| Parent | Children, Progress, Alerts, Profile |
| Admin (APK) | Catalog, Books, Users, Codes, Profile |

**Key screens:** Sign In · Create Account (role picker) · Onboarding (4-step progress) · Student Home (continue, next topic, streak) · Practice picker · Quiz (AI badge, progress, option cards) · Result · Books library and reader · Progress rings · Parent child card and detail · Admin tables and forms.
**Responsive:** compact = single column + floating bar; medium = two columns; expanded = rail/sidebar + master-detail.