# JSON 盘点与表结构设计 结论:现有存储是 `docs/data/` JSON(+ `251228-words` 词库)由 FastAPI 读写、Nginx 直出二进制;无账号表、无资源哈希。表按 **catalog / identity / usage / profile / assets** 分层,业务键用稳定字符串(`book_id` / `word_key` / `child_id`),代理主键 `INTEGER`(MySQL `BIGINT AUTO_INCREMENT`)。SQLite 脚本已在内存库执行通过(99 表 + 2 视图)。不要把整份 JSON 塞进一个 TEXT。 配套文件: - [schema/sqlite.sql](schema/sqlite.sql) - [schema/mysql.sql](schema/mysql.sql) - [schema/json-to-table-map.md](schema/json-to-table-map.md) --- ## 1. 路径与配置(`app/config.py`) | 配置 | 默认(相对仓库 / workhome) | 用途 | |------|-----------------------------|------| | `DATA_DIR` | `$workhome/data`(仓库侧对应 `docs/data/`) | 结构化数据根 | | `BOOK_INDEX_FILE` | `data/book-index.json` | 绘本索引 | | `STRUCTURED_DIR` | `data/book-structured/{stage}/{folder}.json` | 书页/句/词 | | `OCR_DIR` | `data/book-ocr/` | OCR 底稿 | | `STRUCTURED_REVIEW_DIR` | `data/book-structured-review/` | 文本校对报告 | | `KEY_WORDS_DIR` | `data/book-key-words/` | 重点词句 | | `RECORDS_DIR` | `data/reading-records/{child_id}.json` | 阅读会话 | | `LEARNING_DIR` | `data/learning/{child_id}/` | profile/events/srs/plans/days/reports | | `CURRICULUM_DIR` | `data/curriculum/` | 课程包 | | `PHONICS_FILE` | `251228-words/phonics-data.json` | 拼读词库 | | `WORDS_DATA_FILE` | `251228-words/data.json` | 中文释义 / Starters 源 | | `PHONEME_MAP_FILE` | `251228-words/phoneme.json` | IPA→mp3 文件名 | | `PRONUNCIATION_REFERENCE_FILE` | `data/pronunciation-reference.json` | 双口音 | | `IMAGES_DIR` | `$workhome/images/{stage}/{folder}/` | 页图(Nginx `/images/`) | | `TTS_OUTPUT_DIR` | `$workhome/audio/tts-words/` | TTS | | `AUDIO_BOOKS_DIR` | `$workhome/audio/books/{stage}/` | 整本 MP3 + `audit-metadata/` | | `PHONEMES_AUDIO_DIR` | `251228-words/audio/pho-v2/` | 音素音频 | | `EASYSTUDY_TOKEN` | 环境变量 | Hermes 头 `X-Easy-Token`,**不是**用户表 | `learning_path(child_id, *parts)` = `LEARNING_DIR / {child_id} / *parts`。当前「用户维度」仅此目录名 / query `child_id`,默认 `"default"`。 静态资源 Nginx:`/estudy/images/`、`/estudy/audio/`、`/estudy/flashcards/`、`/estudy/data/`。表结构只建 **URI/哈希/字节数** 索引,不入库二进制。 --- ## 2. API 清单(相对 `/api`,对外经 Nginx 为 `/estudy/api`) 读写:R=读 JSON,W=写 JSON,C=计算后可选落盘,S=写静态文件。 | 方法 | 路径 | 源 | R/W | 用户维 | |------|------|----|-----|--------| | GET | `/health` | 无 | — | 否 | | GET | `/books/` | book-index + 扫 structured/images | R | 否 | | GET | `/books/{stage}/{folder}` | structured(无则扫页图) | R | 否 | | GET | `/books/{stage}/{folder}/page/{n}` | 同上 | R | 否 | | GET | `/books/{stage}/{folder}/words` | structured 汇总字段 | R | 否 | | GET | `/books/{stage}/{folder}/correction-report` | book-structured-review | R | 否 | | GET | `/books/{stage}/{folder}/audit-metadata` | `audio/books/.../audit-metadata/` | R | 否 | | PUT | `.../sentence/{i}/word/{j}` | structured | W | 否 | | DELETE | 同上 | structured | W | 否 | | POST | `.../word/{j}/insert` | structured | W | 否 | | PUT | `.../page/{n}/audio-time` | structured `audio_start/end` | W | 否 | | GET/PUT | `/books/{stage}/{folder}/key-words` | book-key-words | R/W | 否(书维) | | GET | `/words/phonemes` | phonics-data.phonemes | R | 否 | | GET | `/words/phoneme-audio-map` | phoneme.json + 磁盘 | R | 否 | | GET | `/words/lookup/{word}` | phonics-data + reference + data.json | R | 否 | | GET | `/words/search` | phonics-data.words | R | 否 | | GET | `/words/references/{word}` | 全量扫 structured | R | 否 | | POST | `/words/tts/{word}` | 生成 mp3 | S | 否 | | GET | `/curriculum/week` | curriculum + profile.cursors | R | **是** `child_id` | | POST/GET | `/records` | reading-records | R/W | **是** | | GET | `/records/summary` | 聚合 records | R | **是** | | GET | `/records/{id}` | records | R | **是** | | POST/GET | `/events` | `learning/{id}/events/*.jsonl` | R/W | **是** | | GET | `/srs/due` `/srs/items` | `srs.json` | R | **是** | | POST | `/srs/items/{id}/reset` `/srs/sync` | srs.json + events | W | **是** | | GET/POST | `/plan/today` `/plan/generate` | `plans/{date}.json` | R/W | **是** | | POST | `/plan/{date}/cards/{id}/skip\|complete` | plans + events | W | **是** | | GET | `/plan/today/wechat-text` | 现算 | R | **是** | | GET | `/reports/daily\|weekly` | reports 或现算后落盘 | C | **是** | | POST | `/days/{date}/off` | days.json + plan | W | **是** | | GET | `/days/streak` | days + plans | R | **是** | **没有** profile CRUD、账号登录、资源包、同步水位 API(留给 02 路)。`child_id` 现为 query/body 字符串,不是鉴权主体。Hermes 仅校验环境 token。 --- ## 3. JSON 源盘点 量级为 2026-09-19 仓库快照。主键/自然键指导入后应对齐的业务键。 ### 3.1 词库与发音(无用户维) | 路径 | 主键/自然键 | 字段形状 | 量级 | 读/写 | 用户维 | |------|-------------|----------|------|-------|--------| | `251228-words/phonics-data.json` | `words.{word}`;`phonemes.{grapheme}` | `{phonemes, words:{phonemes[], highlight, exampleWord, exampleMeaning, letterPhonemes[{letter,phoneme}]}}` | 1.05MB;31 grapheme;1923 word | API 读;tools 写 | 否 | | `251228-words/phoneme.json` | IPA 字符串 | `{ipa: filename}` | 1.2KB;48 键 | API 读 | 否 | | `251228-words/data.json` | 页 `page` + 词 `en` | `[{page, titleEn, titleCn, words[{no,cn,en,pic?}]}]` | 26KB;19 页;407 条 | lookup 释义 | 否 | | `251228-words/user-cfg.json` | 页号+词号 | 第一代熟练度/语速 | 小 | **无 API** | 本机遗留 | | `docs/data/pronunciation-reference.json` | `words.{word}` | `{meta, words:{defaultAccent, pronunciations.{br,us}, verification}}` | 157KB;417 word | lookup 读 | 否 | | `docs/data/pronunciation-manual.json` | `words.{word}` | 人工 br/us ipa | 1.2KB | 工具输入 | 否 | | `docs/data/pronunciation-review.json` | `words.{word}` | 待校 + occurrences | 489KB;417 word | 无运行时 API | 否 | | `docs/data/pronunciation-audit-report.json` | 报告 | 工具输出 | 290KB | 无 API | 否 | | `docs/data/phonics-missing.json` | 词小写 | `{letter,phoneme}[]` | 22KB;103 词 | tools 补丁 | 否 | | `docs/data/words-not-in-cmu.json` | 数组元素 | `string[]` | 7KB;596 | 工具清单 | 否 | ### 3.2 书目录 / 结构化 / OCR / 重点词(无用户维) | 路径 | 主键/自然键 | 字段形状 | 量级 | 读/写 | 用户维 | |------|-------------|----------|------|-------|--------| | `docs/data/book-index.json` | `stage` + `img_folder`;派生 `book_id={stage}__{folder}` | `{stage-NN: [{book_title, img_folder, pdf_path, img_path, page_count, has_images, has_structured, quality_score?, regular_phonics_coverage?, content_page_count?, readable?}]}` | 112KB;14 stage;364 书;readable=10(皆 stage-03) | 列表读 | 否 | | `docs/data/book-structured/{stage}/{folder}.json` | `stage+book_folder`;页 `page`;句数组下标;词下标 | 顶层计数 + `content_pages[{page,file,sentences[{text,words[{word,phonics?}]}], ocr_words[{text,bbox[4],confidence}], audio_start?, audio_end?}]` + `all_words` + `quality` | 80 文件 / 10.8MB;1364 页;2987 句;20294 词例。仅 stage-03/04 | GET 读;编辑/插删/音频时间写 | 否 | | `docs/data/book-ocr/{stage}/{folder}.json` | 同上 + `pages[].page` + words 序 | `{stage, book_folder, page_count, pages[{page,file,full_text,words[{text,bbox,confidence}]}]}` | 80 / 3.1MB | 无直接 API(校对工具) | 否 | | `docs/data/book-key-words/{stage}/{folder}.json` | `book_id`;`words[].id`;`sentences[].id` | `{book_id, version, words[], sentences[], updated_at}` | 1 文件 | GET/PUT | 否 | | `docs/data/book-structured-review/...correction-report.json` | `stage+folder` + `reviews[page,sentence_index]` | summary + reviews(ocr/asr/suggestion) | 4 文件 / 36KB | GET correction-report | 否 | | `docs/data/book-audio-reports/stage-03/*.json` | 书 + 句 | timeline 报告/预览/batch | 56 / 963KB | 无 API | 否 | | `$AUDIO_BOOKS_DIR/{stage}/audit-metadata/{folder}.json` | 书 | recognition.segments + alignment.pages | 不在 git | GET audit-metadata | 否 | `book_id` 已在课程、events、key-words 使用:`stage-03__AtTheSeaside`。 ### 3.3 课程包(无用户维,只读) | 路径 | 主键/自然键 | 字段形状 | 量级 | 读/写 | 用户维 | |------|-------------|----------|------|-------|--------| | `curriculum/phonics-units.json` | `units[].id`(W1–W26) | version/inputs/monthPlan/summary + unit:rule, graphemes, rimes, phonemes, words[], confusions, letterConfusions, distractorGraphemes, reviewOf | 336KB;26 单元;329 词次 | plan/curriculum 读 | 否 | | `curriculum/hf-words.json` | `groups[].id`(A–F);`templates[].id` | group.words[](confusionSet, patternIds, occurrences)+ patterns + sentenceTemplates | 258KB;6 组;115 词;165 模板 | 读 | 否 | | `curriculum/ort-sequence.json` | `books[].bookId`;`order` | 排序 + readable/quality + activityPages[] | 36KB;80 书 | 读 | 否 | | `curriculum/letters.json` | `letters[].letter`(a–z) | sound/card/pictureCards + confusionPairs + letterCardFiles | 73KB;26 字母 | 读 | 否 | | `curriculum/qa-bank.json` | `questions[].id` | topic/kind/q/zh/accept[]/imageUrl | 26KB;53 题 | 课程生成(说话卡预留) | 否 | | `curriculum/starters-words.json` | `words[].id`(p1-01) | topic/zh/pic/variants/bookFrequency | 193KB;407 词 | 构建物;runtime 弱引用 | 否 | `curriculum.py` 强制:phonics-units、hf-words、ort-sequence、letters;**不**强制 qa-bank / starters-words。 ### 3.4 学习运行时(有用户维) `docs/data/learning/{child_id}/`,快照 14 个 child_id(`default` 为真实孩子;其余多为测试)。 | 路径 | 主键/自然键 | 字段形状 | 量级 | 读/写 | 用户维 | |------|-------------|----------|------|-------|--------| | `profile.json` | `childId` | nick, dailyMinutes, schedule, limits, thresholds, windows, streak 配置, cursors, level, features, publicBaseUrl, updatedAt | 每孩 1 | 内部 ensure/save;**无 HTTP** | 是 | | `days.json` | child | `off.{YYYY-MM-DD:{reason,source}}`, `studyDays[]`, `starsTotal`, `graceByWeek.{ISO周}`, `computedStreak` | 每孩 1 | days/plan | 是 | | `srs.json` | `items.{itemId}` 如 `word:cat` | status/dueAt/计数/correctDays/sources/history[-20] | default 49 items | srs + events 驱动 | 是 | | `plans/{YYYY-MM-DD}.json` | `(childId, date)`;卡 `c1`–`c6` | dayType, week, cards[], summary, wechat | 若干日 | plan 生成/skip/complete | 是 | | `events/{YYYY-MM}.jsonl` | `id` 须 `evt_` 前缀 | `{id, childId, type, at, planItemId, itemId, payload, migrated}` | 全库 ~321 行 | 追加;幂等按 id | 是 | | `reports/daily/{date}.json` | `(child, date)` | 星、卡列表、highlights、streak | 少 | 过日落后盘 | 是 | | `reports/weekly/{YYYY-Www}.json` | `(child, week)` | 完成率、弱项、skippedCards、booksRead | 少 | 周末落盘 | 是 | | `docs/data/reading-records/{child_id}.json` | `id`(时间戳-hex) | 数组:stage/folder/title/时长/页/completed/unknown_words/plan_item_id/mode/event_id | 8 文件;41 条;default 29 | records API | 是 | 事件 `type`:客户端 `phonics|quiz|quiz_summary|spelling|reading|listening|writing_checkin`;服务端 `card_complete|day_complete|card_skip|sync|day_off`。payload 必填见 `events_service.PAYLOAD_FIELDS`。 SRS `itemId`:`word:{lower}` / `phoneme:{ipa}` / `letter:{a}` / `pattern:{id}`。状态:`new|learning|weekly|monthly|mastered`。 计划卡:`c1` items[],`c2` clipId/audioUrl,`c3` bookId/mode,`c5` letters/words[],`c6` questions[]。 --- ## 4. 逻辑分层 | 层 | 含义 | 是否用户维 | 同步方向(给 02) | |----|------|------------|-------------------| | **catalog** | 书、页、句、词、音素、课程单元、QA | 否 | 服务端权威 → 课程包下发本地 SQLite | | **identity** | 家长账号、孩子、设备 | 是 | 服务端权威 | | **usage** | 阅读记录、计划、events、SRS、请假、报表快照 | 是 | 本地可写;联网按 event_id/record_id 幂等上传 | | **profile** | 掌握度/弱项/画像标签 | 是 | 可由 usage 物化;服务端可覆盖 | | **assets** | URI、sha1、byte_size、package_id | 否 | 包清单;文件走 03 路 | 禁止:一本 structured JSON 塞进 `TEXT`;一份 srs.json 塞进 `TEXT`;events 整月 JSONL 当 BLOB。 可变结构处理: - 计划卡异质字段 → 公共列 + `usage_plan_card_items/questions/words` - 事件 payload → 高频列打平 + `payload_json`(SQLite TEXT / MySQL JSON)+ `usage_event_unknown_words` - ASR alignment 仍多变 → `pipeline_audio_align_pages.align_json`(注明 JSON 对齐),segments 已拆表 - 校对 reviews 主字段拆表,其余进 `review_json` OCR:`book-ocr` 为权威页词框;structured 内 `ocr_words` 同源副本,导入时 `source` 区分 `book-ocr` / `structured`。 --- ## 5. MySQL 兼容约束(已落在 DDL) | 规则 | 落地 | |------|------| | 类型子集 | SQLite:INTEGER/TEXT/REAL/BLOB;MySQL:INT/BIGINT、VARCHAR/TEXT、DOUBLE、BLOB、JSON | | 布尔 | `INTEGER`/`INT` 0/1 + CHECK | | 时间 | ISO-8601 TEXT/VARCHAR(40),不用 `datetime()` | | PK | SQLite `INTEGER PRIMARY KEY`(无 AUTOINCREMENT);MySQL `BIGINT AUTO_INCREMENT` | | 业务键 | UNIQUE 字符串:`book_id`、`word_key`、`child_id`、`event_id`、`item_id` | | 不用 JSON1 当唯一存储 | 拆表;仅 `payload_json` / `review_json` / `align_json` | | 约束名稳定 | `uk_*` / `idx_*` / `fk_*` 两侧同名 | | FK | 引用业务字符串 ID,便于 dump/同步 | | 资源 | `assets` + `asset_links`,无 BLOB 大文件 | --- ## 6. 表清单(99 表 + 2 视图) ### catalog(54) 包/阶段:`catalog_packages`, `catalog_stages` 词:`catalog_phonemes`, `catalog_grapheme_map`, `catalog_words`, `catalog_word_ipa_seq`, `catalog_word_letter_phonemes`, `catalog_word_definitions`, `catalog_word_accents`, `catalog_word_verification`, `catalog_word_flags` 书:`catalog_books`, `catalog_book_pages`, `catalog_book_sentences`, `catalog_sentence_words`, `catalog_sentence_word_phonemes`, `catalog_book_words`, `catalog_page_ocr_words`, `catalog_book_key_docs`, `catalog_book_key_words`, `catalog_book_key_sentences` 课程:`catalog_month_plans`, `catalog_month_plan_weeks`, `catalog_phonics_units` + graphemes/rimes/phonemes/words/confusions/distractors/reviews,`catalog_hf_*`,`catalog_ort_items`, `catalog_ort_activity_pages`,`catalog_letters*`,`catalog_confusion_pairs`,`catalog_qa_*`,`catalog_starters_*`,`catalog_word_citations` ### assets(2) `assets`, `asset_links` ### pipeline(6,有 API 的校对/ASR) `pipeline_correction_reports/reviews`, `pipeline_audio_runs/segments/align_pages`, `pipeline_timeline_reviews` ### identity(5) `ident_accounts`, `ident_children`, `ident_devices`, `ident_child_devices`, `ident_child_settings` 现网无家长表:导入时 `ident_children.child_id='default'`,`account_id` 空。设备表为后续本地 SQLite 占位。 ### usage(29) 进度指针:`usage_child_cursors`, `usage_written_words`, `usage_reread_queue`, `usage_child_counters` 阅读:`usage_reading_records`, `usage_reading_record_unknown_words` 事件:`usage_events`, `usage_event_unknown_words` 计划:`usage_plans`, `usage_plan_cards`, `usage_plan_card_items/questions/words` SRS:`usage_srs_state/items/item_history/item_sources/correct_days` 日历:`usage_day_offs`, `usage_study_days`, `usage_week_grace` 报表快照:`usage_report_daily*`,`usage_report_weekly*` ### profile(2 + 视图) `profile_tags`(画像标签,现网无数据) `profile_weak_current`(周报弱项物化) 视图 `v_profile_mastery` = SRS 状态(掌握度不另存一份权威) 视图 `v_catalog_book_list` = 列表 API 投影 --- ## 7. 关键设计决策 1. **`book_id = {stage}__{img_folder}`** 已在 events/ort/key-words 使用,不改。 2. **词主键 `word_key` 小写**;句中 `surface` 保留大小写。OCR 垃圾词不强制 FK 到 `catalog_words`。 3. **句/词仍用数组下标**(`sentence_idx`/`word_idx`),与现编辑 API 一致;另留 `sentence_uid` 给重点句。 4. **profile.json 拆开**:身份设置 → `ident_child_settings`;课程游标 → `usage_child_cursors`;level 在 settings(产品调档)同时写入当日 `usage_plans.level_code`。 5. **阅读双写**:`reading-records` 与 `events type=reading` 并存,分表导入,不在第一期合并(02 再定单一事实源)。 6. **报表可重建**:daily/weekly 可从 events+plans+srs 重算;表仅缓存。弱项同时写入 `profile_weak_current` 供画像。 7. **整数 PK + 字符串 UK**:本地自增不必跨库相等;同步用业务键。 8. **课程 occurrences** 进 `catalog_word_citations`,不替代 `catalog_book_sentences`(后者是正文)。 9. **第一代 `user-cfg.json`** 不进主 schema(无 API);若要迁,另开 `profile_legacy_page_scores`。 10. **测试 child_id**(apitest 等)与 `default` 同等导入,便于回放;生产 MySQL 可过滤。 --- ## 8. 与现有 API 的差距 | 现网 | 库后仍缺 / 需组装 | |------|-------------------| | 整本 GET 返回嵌套 JSON | 需 JOIN 页/句/词/phoneme 再拼 `content_pages`;或只读包一层 | | `image_url` / `audio_url` | 由 `asset_links` + `config.resource_url` 现算,不入库绝对 URL | | `words/references` 扫全库 JSON | 改为 `catalog_sentence_words WHERE word_key=?` | | `search?tag=` 未实现 | 可用 `catalog_hf_words` / unit 引用当 tag | | 无 profile API | 设置在表里,但 HTTP 仍没有 | | 无账号;`child_id` 可伪造 | `ident_accounts` 空;鉴权不在本路 | | TTS POST 写文件 | 成功后插 `assets`;仍不存 mp3 字节 | | plan.wechat 可现算 | 列可空,不必持久化 | | `has_images` 现扫磁盘 | 应用 `assets` 是否有 `page_image`,03 路补哈希 | | structured 与 OCR 双份 ocr_words | 导入去重;API 仍可从 `catalog_page_ocr_words` 填 | | 校对/ASR 文件在 Nginx 音频目录 | pipeline 表;文件仍走静态 | | 课程 qa-bank 无专用 GET | 表已建,API 未暴露 | | 同步/包版本 | `catalog_packages` 占位,协议见 02 | 孩子端交互无需为本路改动:先 SQLite 替换 store 层即可保持 JSON 形状的响应。 --- ## 9. 迁移顺序(逻辑;本路不跑全量) 详见 [schema/json-to-table-map.md](schema/json-to-table-map.md)。摘要: 1. `schema_migrations` + `catalog_packages` 2. 音素 → 词库 → 口音 → flags 3. stages → books(index)→ pages/sentences/words(structured)→ OCR → key-words 4. 课程六文件 → citations 5. assets 索引(仅 URI;哈希/字节 03 补) 6. pipeline 校对/ASR(可选) 7. `ident_children` 从 learning 目录名种子 8. settings + cursors + days 9. srs → events → plans → reading-records → reports 10. 物化 `profile_weak_current` 依赖:events/plans 需要 child 行;sentence_words 需要 books;unit words 建议先有 `catalog_words`(缺失词先 INSERT 再挂)。 示例 INSERT 见 `schema/sqlite.sql` 文末(W1/`cat`/`stage-03__AtTheSeaside`/`default`)。