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()"]
| Module | Function Signature | Behavior |
|---|---|---|
text_normalizer.py / .ts | normalize_text(text, casefold=True, strip_punct=True) | Unicode NFC + Polish/Vietnamese/Arabic diacritic stripping & multi-script tokenizers |
grader.py / .ts | grade_exercise(exercise, submitted, gameplay_mode="playful") | Strategy dispatch across all 11 types + returns explanation & evidence_quotes |
placement_engine.py / .ts | evaluate_placement(db, profile, course_id, answers) | Weights tier accuracy (A1–C1 / N5–N1) & unlocks foundational units |
score_estimator.py / .ts | recompute_course_estimate(db, profile_id, course_id) | Maps weighted skill accuracy → IELTS Band, TOEIC 300–990, JLPT N5–N1 %, TESOL Tier |
srs_service.py / .ts | record_word_outcome(db, profile_id, vocab_id, is_correct, ms) | SM-2 lite updates ease_factor, interval_days & next_review_at |
pack_service.py / .ts | validate_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| Type | Core config_json | Submitted Payload |
|---|---|---|
picture_match | pairs[{pair_id, text_native, illustration_key}] | {"matches": {"p1": "ねこ"}} |
sentence_builder | direction, target_tokens[], accepted_sequences[] | {"ordered_tokens": ["صباح","الخير"]} |
category_sort | buckets[], items[{item_id, correct_bucket_id}] | {"placements": {"i1": "fruits"}} |
match_pairs | pairs[{pair_id, left_text, right_text}] | {"matches": {"m1": "Water"}} |
fill_blank_drag | segments[{kind, blank_id, accepted_answers[]}] | {"blanks": {"b1": "mange"}} |
listen_and_arrange | audio_text, ordered_tiles[], distractor_tiles[] | {"ordered_tokens": ["さ","く","ら"]} |
tap_hear_explore | cards[{text_native, romanization, ipa, metadata}] | {"explored_count": 4} |
reading_passage_drag | paragraphs[{text, evidence_quote}], slots[], tiles[] | {"slot_matches": {"A": "h2"}} |
essay_architect | framework_badge, slots[], blocks[], expected_order[] | {"ordered_block_ids": ["b_lead","b_pres"]} |
error_spotter | segments[{seg_id, is_error}], fix_choices[] | {"selected_seg_id": "seg1", "applied_fix_id": "f1"} |
listening_lab | accent_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
| Version | Date | Summary of Changes |
|---|---|---|
| v1.2.0 | 2026-11-22 | Added TypeScript Worker (.ts) parity notes for D1 execution, Polish diacritic stripping, and cascade-safe ON CONFLICT DO UPDATE migrations (worker/migrations/). |
| v1.1.0 | 2026-11-15 | Synchronized 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.0 | 2026-11-15 | Initial Overview + Domain-Split ERD, 11 Schemas & Python Functions HTML portal. |