🦉 LingoBright Docs

04 · Data Model, 11 Schemas & Python Functions

Overview ERD first, followed by 4 small domain ERDs, 11 polymorphic exercise schemas, Python function specs & REST APIs.

1. Database Overview ERD (High-Level Map)

Open erd-and-tables.md
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
        

Detail A · Identity (2 Tables)

erDiagram
    USERS ||--o{ LEARNER_PROFILES : owns
    USERS {
        int id PK
        string email UK
        string role
        string parent_pin_hash
    }
    LEARNER_PROFILES {
        int id PK
        int user_id FK
        string guest_token UK
        string gameplay_mode
        string script_mode
        string preferred_accent
        int total_xp
    }
          

Detail B · Curriculum Ladder (4 Tables)

erDiagram
    LANGUAGES ||--o{ COURSES : defines
    COURSES ||--o{ UNITS : contains
    UNITS ||--o{ LESSONS : contains
    LANGUAGES {
        string code PK
        string bcp47_tts_tag
        string direction
        string script_family
    }
    COURSES {
        int id PK
        string slug UK
        string exam_framework
        string min_tier
        string max_tier
    }
    UNITS {
        int id PK
        int course_id FK
        string cefr_level
        string exam_tier
    }
    LESSONS {
        int id PK
        int unit_id FK
        string skill_focus
        string cefr_level
    }
          

Detail C · Content & Audio Bank (4 Tables)

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 text_native
        string romanization
        string cefr_level
        string register
    }
    GRAMMAR_CONCEPTS {
        int id PK
        string lang_code FK
        string pattern_formula
        string cefr_level
    }
    EXERCISES {
        int id PK
        int lesson_id FK
        string exercise_type
        string skill_focus
        json config_json
    }
    AUDIO_ASSETS {
        int id PK
        string lang_code FK
        string accent_tag
        string text_hash
    }
          

Detail D · Progress, SRS & Stickers (6 Tables)

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
    STICKERS ||--o{ USER_STICKERS : unlocked_as
    USER_LESSON_PROGRESS {
        int id PK
        string status
        int best_stars
        float best_accuracy
    }
    EXERCISE_ATTEMPTS {
        int id PK
        boolean is_correct
        int duration_ms
    }
    WORD_MASTERY_SRS {
        int id PK
        float ease_factor
        int interval_days
        datetime next_review_at
    }
    USER_SKILL_ESTIMATES {
        int id PK
        string overall_tier_label
        float numeric_estimate
    }
    STICKERS {
        string code PK
        string rule_type
    }
    USER_STICKERS {
        int id PK
        datetime unlocked_at
    }
          

Backend Attempt-to-Estimate Flow & Python Functions

Open functions-and-api.md
flowchart TD
    Req["POST /api/v1/exercises/{id}/attempt"] --> Norm["text_normalizer.normalize_text()"]
    Norm --> Grade["grader.grade_exercise() - 11 handlers"]
    Grade --> Attempt["Insert ExerciseAttempt"]
    Grade --> SRS["srs_service.record_word_outcome()"]
    Grade --> XP["gamification_service.award_exercise_xp()"]
    Done["POST /api/v1/lessons/{id}/complete"] --> Stars["complete_lesson_rewards() - 1-3 Stars + Stickers"]
    Done --> Est["score_estimator.recompute_course_estimate()"]
        
ModuleFunction SignatureBehavior
text_normalizer.py / .tsnormalize_text(text, casefold=True, strip_punct=True)Unicode NFC + Polish/Vietnamese/Arabic diacritic stripping & multi-script tokenizers
grader.py / .tsgrade_exercise(exercise, submitted, gameplay_mode="playful")Strategy dispatch across all 11 types + returns explanation & evidence_quotes
placement_engine.py / .tsevaluate_placement(db, profile, course_id, answers)Weights tier accuracy (A1–C1 / N5–N1) & unlocks foundational units
score_estimator.py / .tsrecompute_course_estimate(db, profile_id, course_id)Maps weighted skill accuracy → IELTS Band, TOEIC 300–990, JLPT N5–N1 %, TESOL Tier
srs_service.py / .tsrecord_word_outcome(db, profile_id, vocab_id, is_correct, ms)SM-2 lite updates ease_factor, interval_days & next_review_at
pack_service.py / .tsvalidate_pack_data(raw_data) / import_pack_to_db(...)Validates all 11 contracts (incl. speech_register), Bidi safety & upserts atomically (SQLite & D1)

11 Polymorphic Exercise Schemas

Open exercise-schemas.md
TypeCore config_jsonSubmitted Payload
picture_matchpairs[{pair_id, text_native, illustration_key}]{"matches": {"p1": "ねこ"}}
sentence_builderdirection, target_tokens[], accepted_sequences[]{"ordered_tokens": ["صباح","الخير"]}
category_sortbuckets[], items[{item_id, correct_bucket_id}]{"placements": {"i1": "fruits"}}
match_pairspairs[{pair_id, left_text, right_text}]{"matches": {"m1": "Water"}}
fill_blank_dragsegments[{kind, blank_id, accepted_answers[]}]{"blanks": {"b1": "mange"}}
listen_and_arrangeaudio_text, ordered_tiles[], distractor_tiles[]{"ordered_tokens": ["さ","く","ら"]}
tap_hear_explorecards[{text_native, romanization, ipa, metadata}]{"explored_count": 4}
reading_passage_dragparagraphs[{text, evidence_quote}], slots[], tiles[]{"slot_matches": {"A": "h2"}}
essay_architectframework_badge, slots[], blocks[], expected_order[]{"ordered_block_ids": ["b_lead","b_pres"]}
error_spottersegments[{seg_id, is_error}], fix_choices[]{"selected_seg_id": "seg1", "applied_fix_id": "f1"}
listening_labaccent_tag, audio_script, fields[], choices[]{"field_values": {"venue": "Greenhouse Pavilion"}}
Inspect Sample IELTS / TESOL / TOEIC JSON Payloads
{
  "exercise_type": "reading_passage_drag",
  "passage_title": "Urban Rewilding and Microclimate Cooling",
  "paragraphs": [{"para_id": "A", "evidence_quote": "elevating evening city temperatures by up to 4°C"}],
  "slots": [{"slot_id": "A", "expected_tile_id": "h2"}],
  "tiles": [{"tile_id": "h2", "text": "How built surfaces intensify urban heat retention"}]
}
{
  "exercise_type": "essay_architect",
  "framework_badge": "TESOL PPP",
  "slots": [{"slot_id": "s1", "stage_label": "1. Lead-in"}, {"slot_id": "s2", "stage_label": "2. Presentation"}],
  "expected_order": ["b_lead", "b_pres"]
}

Changelog

VersionDateSummary of Changes
v1.2.02026-11-22Added TypeScript Worker (.ts) parity notes for D1 execution, Polish diacritic stripping, and cascade-safe ON CONFLICT DO UPDATE migrations (worker/migrations/).
v1.1.02026-11-15Synchronized function signatures (grade_exercise, complete_lesson_rewards), 16 ORM columns, speech_register YAML alias, and full REST route inventory across erd-and-tables.md, exercise-schemas.md, functions-and-api.md, and HTML portal.
v1.0.02026-11-15Initial Overview + Domain-Split ERD, 11 Schemas & Python Functions HTML portal.