# Data Model — Database Overview & Domain ERDs

> [Docs Home](../README.md) | [Data & Functions HTML View](./index.html) | Next: [11 Exercise Schemas →](./exercise-schemas.md)

## 1. High-Level Database Overview (No Column Clutter)

```mermaid
erDiagram
    USERS ||--o{ LEARNER_PROFILES : owns
    LANGUAGES ||--o{ COURSES : defines
    LANGUAGES ||--o{ VOCABULARY_ITEMS : defines
    LANGUAGES ||--o{ GRAMMAR_CONCEPTS : defines
    LANGUAGES ||--o{ AUDIO_ASSETS : voices
    COURSES ||--o{ UNITS : contains
    UNITS ||--o{ LESSONS : contains
    LESSONS ||--o{ EXERCISES : contains
    LEARNER_PROFILES ||--o{ USER_LESSON_PROGRESS : tracks
    LEARNER_PROFILES ||--o{ EXERCISE_ATTEMPTS : submits
    LEARNER_PROFILES ||--o{ WORD_MASTERY_SRS : practices
    LEARNER_PROFILES ||--o{ USER_SKILL_ESTIMATES : scores
    LEARNER_PROFILES ||--o{ USER_STICKERS : earns
```

---

## 2. Detail A — Identity & Learner Profiles (2 Tables)

```mermaid
erDiagram
    USERS ||--o{ LEARNER_PROFILES : owns
    USERS {
        int id PK
        string email UK
        string username
        string password_hash
        string role
        string parent_pin_hash
        datetime created_at
    }
    LEARNER_PROFILES {
        int id PK
        int user_id FK
        string guest_token UK
        string nickname
        string avatar_id
        int active_course_id FK
        string gameplay_mode
        string script_mode
        string preferred_accent
        boolean open_access
        boolean sound_enabled
        int total_xp
        int current_streak
        int daily_xp_goal
        int today_xp
        date last_active_date
        datetime created_at
    }
```

| Table | Key Constraints & Role |
| :--- | :--- |
| `users` | `UQ(email)`; optional parent/learner login + 4-digit PIN gate |
| `learner_profiles` | `UQ(guest_token)`; zero-PII starter + Playful/Exam & accent prefs |

---

## 3. Detail B — Curriculum Ladder (4 Tables)

```mermaid
erDiagram
    LANGUAGES ||--o{ COURSES : defines
    COURSES ||--o{ UNITS : contains
    UNITS ||--o{ LESSONS : contains

    LANGUAGES {
        string code PK
        string name_en
        string native_name
        string bcp47_tts_tag
        json supported_accents_json
        string direction
        string script_family
        string tokenization_mode
    }
    COURSES {
        int id PK
        string slug UK
        string source_lang_code FK
        string target_lang_code FK
        string exam_framework
        string title
        text description
        string min_tier
        string max_tier
        boolean is_published
    }
    UNITS {
        int id PK
        int course_id FK
        string slug
        string title
        string cefr_level
        string exam_tier
        int sort_order
    }
    LESSONS {
        int id PK
        int unit_id FK
        string slug
        string title
        string lesson_type
        string skill_focus
        string cefr_level
        string exam_tier
        int target_time_sec
        int xp_reward
        int sort_order
    }
```

| Table | Key Constraints & Role |
| :--- | :--- |
| `languages` | Drives RTL `<bdi>`, CJK tokenization & multi-accent voices |
| `courses` | `UQ(slug)`; supports `CEFR`, `IELTS`, `TOEIC`, `TESOL`, `JLPT`, `HSK`, `TOPIK` |
| `units` & `lessons` | `UQ(course_id, slug)`, `UQ(unit_id, slug)`; tier & `skill_focus` tags |

---

## 4. Detail C — Content, Exercises & Audio Bank (4 Tables)

```mermaid
erDiagram
    LANGUAGES ||--o{ VOCABULARY_ITEMS : defines
    LANGUAGES ||--o{ GRAMMAR_CONCEPTS : defines
    LANGUAGES ||--o{ AUDIO_ASSETS : voices
    LESSONS ||--o{ EXERCISES : contains

    VOCABULARY_ITEMS {
        int id PK
        string lang_code FK
        string slug
        string text_native
        string romanization
        string ipa
        string cefr_level
        string exam_tag
        string register
        json translations_json
        json metadata_json
    }
    GRAMMAR_CONCEPTS {
        int id PK
        string lang_code FK
        string slug
        string title
        string pattern_formula
        string cefr_level
        string exam_tag
        text explanation_md
        json common_traps_json
    }
    EXERCISES {
        int id PK
        int lesson_id FK
        string exercise_type
        string skill_focus
        string cefr_level
        string exam_tier
        text prompt_text
        text hint_text
        json config_json
        json explanation_json
        json vocab_ids_json
        json grammar_ids_json
        int difficulty
        int sort_order
    }
    AUDIO_ASSETS {
        int id PK
        string lang_code FK
        string accent_tag
        string text_hash
        text text_content
        string file_path
        string provider
    }
```

| Table | Key Constraints & Role |
| :--- | :--- |
| `vocabulary_items` | `metadata_json` holds Kanji radicals/Onyomi/collocations/Band-9 upgrades |
| `grammar_concepts` | Reusable rule bank (`JLPT N1`, `IELTS Inversion`, `TESOL PPP`) |
| `exercises` | 11 polymorphic types validated by Pydantic `ExerciseConfigUnion` |
| `audio_assets` | `UQ(lang_code, accent_tag, text_hash)` cached MP3 lookup |

---

## 5. Detail D — Progress, SRS, Exam Estimator & Stickers (6 Tables)

```mermaid
erDiagram
    LEARNER_PROFILES ||--o{ USER_LESSON_PROGRESS : tracks
    LEARNER_PROFILES ||--o{ EXERCISE_ATTEMPTS : submits
    LEARNER_PROFILES ||--o{ WORD_MASTERY_SRS : practices
    LEARNER_PROFILES ||--o{ USER_SKILL_ESTIMATES : scores
    LEARNER_PROFILES ||--o{ USER_STICKERS : earns
    STICKERS ||--o{ USER_STICKERS : unlocked_as

    USER_LESSON_PROGRESS {
        int id PK
        int profile_id FK
        int lesson_id FK
        string status
        int best_stars
        float best_accuracy
        int attempts_count
        datetime completed_at
    }
    EXERCISE_ATTEMPTS {
        int id PK
        int profile_id FK
        int exercise_id FK
        boolean is_correct
        string gameplay_mode
        int duration_ms
        int xp_awarded
        datetime created_at
    }
    WORD_MASTERY_SRS {
        int id PK
        int profile_id FK
        int vocab_id FK
        float ease_factor
        int interval_days
        int repetitions
        int correct_count
        int wrong_count
        datetime next_review_at
    }
    USER_SKILL_ESTIMATES {
        int id PK
        int profile_id FK
        int course_id FK
        string overall_tier_label
        float numeric_estimate
        json skill_breakdown_json
        datetime updated_at
    }
    STICKERS {
        string code PK
        string title
        string svg_key
        string rule_type
    }
    USER_STICKERS {
        int id PK
        int profile_id FK
        string sticker_code FK
        datetime unlocked_at
    }
```

| Table | Key Constraints & Role |
| :--- | :--- |
| `user_lesson_progress` | `UQ(profile_id, lesson_id)`; tracks `unlocked`/`tested_out`/`completed` & `0-3★` |
| `exercise_attempts` | Telemetry for accuracy trends & Parent Analytics |
| `word_mastery_srs` | `IDX(profile_id, next_review_at)`; SM-2 lite scheduling for Brain Boost |
| `user_skill_estimates` | Live IELTS Band, TOEIC 300–990, JLPT N5–N1 % & TESOL Tier |
| `stickers` / `user_stickers` | `UQ(profile_id, sticker_code)`; collectible reward badges |

> **Cloudflare D1 Edge Replication:** `scripts/export_d1_sql.py` extracts all 16 SQLAlchemy table DDLs into `worker/migrations/0001_schema.sql` (`CREATE TABLE IF NOT EXISTS`, no explicit transactions) and emits cascade-safe `INSERT ... ON CONFLICT DO UPDATE` seed rows into `worker/migrations/0002_seed.sql` for Cloudflare D1 (`lingobright-db`), preserving `user_lesson_progress`, `word_mastery_srs`, and `exercise_attempts` across re-seeds.

## Changelog

| Version | Date | Summary of Changes |
| :--- | :--- | :--- |
| **v1.2.0** | 2026-11-22 | Documented single-source Cloudflare D1 DDL/upsert generation (`export_d1_sql.py` → `worker/migrations/0001_schema.sql` & `0002_seed.sql`) for `lingobright-db` (`10` languages, `17` courses, `68` exercises). |
| **v1.1.0** | 2026-11-15 | Synchronized all 4 domain ERDs with exact SQLAlchemy columns in `app/models/` (`username`, `sound_enabled`, `today_xp`, `daily_xp_goal`, `lesson_type`, `prompt_text`, `hint_text`, `grammar_ids_json`, `text_content`, `attempts_count`, `correct_count`, `wrong_count`, timestamps). |
| **v1.0.0** | 2026-11-15 | Initial overview + 4 domain-split ERD architecture. |
