# JSON → 表.列 映射与迁移顺序 约定:`book_id = {stage}__{img_folder}`(**不是** fs_slug;撇号书如 `stage-03__Kipper'sIdea`);`fs_slug` 仅物理目录;`word_key = lower(word)`;时间保持原 ISO-8601 字符串;布尔 `true/false` → `0/1`。下载包 `package_id`:`core-runtime` / `book:{book_id}` / `course:{course_id}`。SQLite 可执行示例见 `sqlite.sql` 文末。本文件不跑全量导入。 `payload_json` / `review_json` / `align_json`:SQLite `TEXT`,MySQL `JSON`。 --- ## 0. 迁移顺序 | 步 | 源 | 目标表 | 依赖 | |----|----|--------|------| | 0 | — | `schema_migrations`, `catalog_packages` | — | | 1 | `phoneme.json` | `catalog_phonemes` | — | | 2 | `phonics-data.json` `.phonemes` | `catalog_grapheme_map` | 1(缺 IPA 先插 phonemes) | | 3 | `phonics-data.json` `.words` | `catalog_words` + `catalog_word_ipa_seq(accent=legacy)` + `catalog_word_letter_phonemes` | 2 | | 4 | `data.json` | `catalog_word_definitions`;必要时 INSERT words | 3 | | 5 | `pronunciation-reference.json` | `catalog_word_accents`, ipa/letter 行 `accent=br\|us`, `catalog_word_verification` | 3 | | 6 | `pronunciation-manual.json` | 覆盖 5 的 br/us | 5 | | 7 | `phonics-missing.json` | letter_phonemes `source=manual` | 3 | | 8 | `words-not-in-cmu.json` | `catalog_word_flags(flag=not_in_cmu)` | 可无 word 行 | | 9 | `book-index.json` | `catalog_stages`, `catalog_books` | — | | 10 | `book-structured/*.json` | pages, sentences, sentence_words, phonemes, book_words, quality 列 | 9 | | 11 | `book-ocr/*.json` | `catalog_page_ocr_words source=book-ocr` | 9 | | 12 | structured 内 `ocr_words` | 同上 `source=structured`(若与 11 重复可跳过) | 10 | | 13 | `book-key-words` | `catalog_book_key_*` | 9 | | 14 | curriculum 六文件 | 课程表 + `catalog_word_citations` | 3、9 | | 15 | 路径约定生成 URI | `assets`, `asset_links`(sha256/bytes 空,留给 03;`package_id=book:{book_id}`) | 10、1 | | 16 | correction-report / audit-metadata / timeline-report | `pipeline_*` | 9 | | 17 | `learning/*/ ` 目录名 | `ident_children` | — | | 18 | `profile.json` | `ident_child_settings`, `usage_child_cursors`, `usage_written_words`, `usage_reread_queue` | 17 | | 19 | `days.json` | `usage_day_offs`, `usage_study_days`, `usage_week_grace`, `usage_child_counters` | 17 | | 20 | `srs.json` | `usage_srs_*` | 17 | | 21 | `events/*.jsonl` | `usage_events`, `usage_event_unknown_words` | 17 | | 22 | `plans/*.json` | `usage_plans`, `usage_plan_cards*` | 17 | | 23 | `reading-records/{id}.json` | `usage_reading_records*` | 17 | | 24 | `reports/**` | `usage_report_*`;弱项 → `profile_weak_current` | 17 | | 25 | — | 校验计数;重算报表可选覆盖 24 | 21–23 | `pronunciation-review.json` / `pronunciation-audit-report.json` / `user-cfg.json`:**不导入主库**(工具清单 / 第一代前端)。citations 若需要校对上下文可从 review.occurrences 补 `catalog_word_citations source_kind=pron_review`。 --- ## 1. 词库 ### `251228-words/phoneme.json` | JSON | 表.列 | |------|--------| | 键 IPA | `catalog_phonemes.ipa` | | 值 filename | `catalog_phonemes.audio_filename` | | (序) | `sort_order` | 资源:`rel_uri=/audio/phonemes/{filename}` → `assets` + `asset_links(object_type=phoneme, object_id=ipa, role=audio)`。 ### `251228-words/phonics-data.json` | JSON | 表.列 | |------|--------| | `phonemes.{g}` | `catalog_grapheme_map.grapheme`, `.ipa` | | `words` 的 key | `catalog_words.word_key` / `display_text` | | `.highlight` | `catalog_words.highlight` | | `.exampleWord` | `example_word` | | `.exampleMeaning` | `example_meaning` | | `.in` 存在 | `in_phonics_data=1` | | `.phonemes[i]` | `catalog_word_ipa_seq(accent='legacy', seq=i, ipa)` | | `.letterPhonemes[i].letter/phoneme` | `catalog_word_letter_phonemes(accent='legacy', source='phonics-data')` | ### `251228-words/data.json` | JSON | 表.列 | |------|--------| | `words[].en` | 确保 `catalog_words`;`catalog_starters_words` 由 curriculum 构建物导入更完整 | | `words[].cn` | `catalog_word_definitions.meaning`(按逗号拆 `seq`);pos 规则同 `words.py` | | `words[].pic` | 03 路图片;此处可 `asset_links role=word_pic` | | 顶层 `page/titleEn/titleCn` | `catalog_starters_topics`(若未走 starters-words.json) | 优先导入 `curriculum/starters-words.json`,`data.json` 只补定义。 ### `docs/data/pronunciation-reference.json` | JSON | 表.列 | |------|--------| | `words.{w}.defaultAccent` | `catalog_words.default_accent` | | `.pronunciations.br\|us.ipa[]` | `catalog_word_ipa_seq(accent)` | | `.display` | `catalog_word_accents.display` | | `.letterPhonemes[]` | `catalog_word_letter_phonemes(accent)` | | `.verification.*` | `catalog_word_verification`(camelCase→snake) | `br/us` 为 null:不插 ipa 行。 ### `docs/data/pronunciation-manual.json` 同 reference 的 br/us 对象(无 `pronunciations` 包一层):覆盖对应 accent 行。 ### `docs/data/phonics-missing.json` | JSON | 表.列 | |------|--------| | 键 | `word_key`(跳过 `_comment`) | | 值 `[i]` | `catalog_word_letter_phonemes(accent='legacy', source='manual')` | ### `docs/data/words-not-in-cmu.json` 数组元素 → `catalog_word_flags(word_key, flag='not_in_cmu')`。 --- ## 2. 书 ### `docs/data/book-index.json` | JSON | 表.列 | |------|--------| | 对象键 `stage-03` | `catalog_stages.stage_id`;`catalog_books.stage_id` | | `.img_folder` | `folder`;`book_id=stage+'__'+folder`(禁止用 fs_slug 替换) | | 03 mapping `fs_slug` | `catalog_books.fs_slug`;物理树 `images/books/{stage}/{fs_slug}/` | | `.book_title` | `title` | | `.pdf_path` | `pdf_path`(路径索引,PDF 二进制不入库) | | `.img_path` | `img_path` | | `.page_count` | `page_count` | | `.has_images` / `.has_structured` / `.readable` | 同名 0/1;缺省 0 | | `.quality_score` `.regular_phonics_coverage` `.content_page_count` | 同名;structured 导入时覆盖 | ### `docs/data/book-structured/{stage}/{folder}.json` | JSON | 表.列 | |------|--------| | `stage` `book_folder` | 校验 book_id | | `total_pages` | 可忽略(用 index/page 行) | | `content_page_count` `unique_word_count` `words_with_phonics_count` | `catalog_books.*` | | `quality.*` | `garbage_word_count` 等 6 列 | | `content_pages[].page` `file` | `catalog_book_pages.page_num` `file_name` | | `.audio_start` `.audio_end` | 同名 REAL(现网文件多缺,API 可写) | | `.sentences[]` 下标 | `sentence_idx` | | `.sentences[].text` | `catalog_book_sentences.text` | | `.sentences[].id` `start` `end` | `sentence_uid` `audio_start` `audio_end` | | `.sentences[].words[].word` | `surface`;`word_key=lower` | | `.phonics` 有则 | `has_phonics=1` | | `.phonics[k].letters/phoneme` | `catalog_sentence_word_phonemes` | | `all_words[]` | `catalog_book_words.word_key` | | `words_with_phonics[]` | 同行 `with_phonics=1` | | `words_without_phonics[]` | `with_phonics=0`(若已在 with 中则 1 优先) | | `ocr_words[]` | `catalog_page_ocr_words` bbox→x,y,w,h | 响应派生:`image_url`/`audio_url`/`book_title` **不入库**。 ### `docs/data/book-ocr/{stage}/{folder}.json` | JSON | 表.列 | |------|--------| | `pages[].page` `file` | 若 pages 表无此页则补 `catalog_book_pages` | | `pages[].full_text` | 不建列(可由 ocr words 拼接);需要可加 `catalog_book_pages` 扩展,本版省略 | | `pages[].words[]` | `catalog_page_ocr_words(source='book-ocr')` | ### `docs/data/book-key-words/{stage}/{folder}.json` | JSON | 表.列 | |------|--------| | `book_id` `version` `updated_at` | `catalog_book_key_docs` | | `sentences[].id/text/created_at/updated_at` | `catalog_book_key_sentences` | | `words[].id` | `key_word_id` | | `text/normalized/enabled/sentence_id/timestamps` | `catalog_book_key_words` | ### 校对 / 时间轴 / ASR `*.correction-report.json` → `pipeline_correction_reports`(summary 计数)+ `pipeline_correction_reviews`(主字段)+ `review_json` 存整条 review。 `audit-metadata` → `pipeline_audio_runs`;`recognition.segments[]` → `pipeline_audio_segments`(`id/start/end/text`);`alignment.pages[]` → `pipeline_audio_align_pages.align_json`。 `*.timeline-report.json` `manual_review[]` → `pipeline_timeline_reviews`。 `timeline-preview` / `batch-run-log` / `audio-scan-summary`:工具日志,**不导入**。 --- ## 3. 课程 共用:`version/generatedAt/generator/inputs` → 一行 `catalog_packages(kind=curriculum, package_id=文件级快照如 curriculum-2026-09-19)`。下载包另有 `core-runtime` / `book:{book_id}` / `course:{course_id}` / `phonics-W1`(逻辑周,文件在 core)。不要再用 `books-stage-03` 这种按阶段包 id。 ### `phonics-units.json` | JSON | 表.列 | |------|--------| | `monthPlan[]` | `catalog_month_plans` + `catalog_month_plan_weeks` | | `units[].id` | `catalog_phonics_units.unit_id` | | `month/week/rule/ruleKey/level/opwRef/wordCount/autoFilledCount` | 对应列(camel→snake) | | `graphemes[]` `rimes[]` `phonemes[]` | 三个 seq 表 | | `words[]` | `catalog_phonics_unit_words`;letter/ipa 与词库重复则不拷,引用 `word_key` | | `words[].occurrences[]` | `catalog_word_citations(source_kind='phonics_unit', source_ref=unit_id)` | | `confusions[][2]` | `catalog_phonics_unit_confusions.kind='word'` | | `letterConfusions[][2]` | `kind='letter'` | | `distractorGraphemes[]` | `catalog_phonics_unit_distractors` | | `reviewOf[]` | `catalog_phonics_unit_reviews` | ### `hf-words.json` | JSON | 表.列 | |------|--------| | `groups[].id/title/theme/month/wordCount` | `catalog_hf_groups` | | `words[].text` | `catalog_hf_words.display_text`;`word_key=lower` | | `inPhonicsData` `bookFrequency` | 列 | | `confusionSet[]` | `catalog_hf_word_confusions` | | `patternIds[]` | `catalog_hf_word_pattern_ids` | | `occurrences[]` | `catalog_word_citations(source_kind='hf', source_ref=group_id)` | | `patterns[]` | `catalog_hf_patterns` + `catalog_hf_pattern_words` | | `sentenceTemplates[]` | `catalog_hf_templates` + `catalog_hf_template_options` | | `templates[].source.*` | `source_type/source_book_id/source_page/source_original` | ### `ort-sequence.json` | JSON | 表.列 | |------|--------| | `books[].bookId` | `catalog_ort_items.book_id` | | `order` | `sort_order` | | `stageOrder/stage/folder/title/...` | 对应列 | | `activityPages[]` | `catalog_ort_activity_pages` | | `rules` / `summary` | 不入库(生成器元数据)或写入 `catalog_packages.note` | ### `letters.json` | JSON | 表.列 | |------|--------| | `letterCardFiles[]` | `catalog_letter_card_files` + assets | | `confusionPairs[][2]` | `catalog_confusion_pairs source='letters.json'` | | `letters[]` | `catalog_letters`(card/lowerCard 压成列) | | `exampleWords[]` | `catalog_letter_examples` | | `confusions[]` | `catalog_letter_confusions` | | `pictureCards[]` | `catalog_letter_picture_cards`;`exists`→`exists_flag` | | `flashcardsBaseUrl` | 不入库(部署配置) | 代码里写死的额外混淆对(they/them 等)导入时 `source='curriculum.py'`。 ### `qa-bank.json` | JSON | 表.列 | |------|--------| | `topics` map | `catalog_qa_topics` | | `questions[]` | `catalog_qa_questions`(`q`→`prompt`,`zh`→`prompt_zh`) | | `accept[]` | `catalog_qa_accept` | | `image` `imageUrl` `imageExists` | `image_rel` `image_uri` `image_exists` | ### `starters-words.json` | JSON | 表.列 | |------|--------| | `topics[]` | `catalog_starters_topics` | | `words[]` | `catalog_starters_words`(`id`→`starters_id`) | | `variants[]` | `catalog_starters_word_variants` | --- ## 4. Identity / settings 无 JSON 的家长账号:不插 `ident_accounts`。 每个 `docs/data/learning/{child_id}/` → `ident_children(child_id, nick 稍后从 profile 填, created_at=profile.updatedAt)`。 ### `profile.json` | JSON | 表.列 | |------|--------| | `childId` `nick` | `ident_children` | | `dailyMinutes` | `ident_child_settings.daily_minutes` | | `schedule.weekday` 等 | `schedule_*` | | `limits.*` | `new_per_day` 等 | | `thresholds.*` | `word_score` `sentence_score` `hasty_ms` `book_unknown_max` | | `windows.*` | `learning_days` 等 | | `streak.minCardsForDay` | `min_cards_for_day` | | `streak.graceDaysPerWeek` | `grace_days_per_week` | | `level` | `level_code` | | `features.speaking` | `speaking_enabled` | | `publicBaseUrl` `updatedAt` | 同名 | | `cursors.phonicsWeek` 等 | `usage_child_cursors` | | `cursors.writtenWords[]` | `usage_written_words` | | `cursors.rereadQueue[]` | `usage_reread_queue.book_id` | | `cursors.ortRetried` | `ort_retried` | ### `days.json` | JSON | 表.列 | |------|--------| | `off.{date}` | `usage_day_offs.off_date, reason, source` | | `studyDays[]` | `usage_study_days.study_date` | | `graceByWeek.{week}` | `usage_week_grace.week_id, used_count` | | `starsTotal` `computedStreak` `updatedAt` | `usage_child_counters` | --- ## 5. Usage ### `srs.json` | JSON | 表.列 | |------|--------| | `version` `updatedAt` | `usage_srs_state` | | `items` 的 key | `usage_srs_items.item_id` | | `kind/subkind/text/status` | 同名 | | `enteredAt` `dueAt` `lastAt` | snake | | `extendCount` 等计数 | snake | | `lastResult` | `last_result` | | `correctDays[]` | `usage_srs_correct_days.day` | | `sources[].type/ref` | `usage_srs_item_sources` | | `history[]` 按数组序 | `usage_srs_item_history.seq`(JSON 已截断 20 条) | ### `events/{YYYY-MM}.jsonl` 每行 | JSON | 表.列 | |------|--------| | `id` | `event_id` | | `childId` `type` `at` `planItemId` `itemId` `migrated` | `child_id` `event_type` `at` `plan_item_id` `item_id` `migrated` | | `payload.stage` | `payload_stage` | | `qtype` | `payload_qtype` | | `correct` `firstAttemptCorrect` `hasty` `completed` `free` | 对应 INTEGER | | `attempts` `responseMs` | `payload_attempts` `payload_response_ms` | | `bookId` `mode` `pagesRead` `totalPages` `durationSec` | `payload_*` | | `clipId` `cardId` `reason` `source` | `payload_*` | | `total` `playCount` `sentencePlayCount` | `payload_total` 等 | | `unknownWords[]` | `usage_event_unknown_words` | | 其余键(chosen/answer/photo/letters/words/migratedItems…) | 整份 payload 进 `payload_json` | ### `plans/{date}.json` | JSON | 表.列 | |------|--------| | `date` | `plan_date` | | `dayType` `generatedAt` `version` | 列 | | `week.id/rule/episode` | `week_id` `week_rule` `week_episode` | | `summary.*` | `total_stars` `earned_stars` `completed_cards` `streak` `level_code` `started_at` | | `wechat.morning/nudge/saturday` | `wechat_*` | | `cards[]` 公共字段 | `usage_plan_cards` | | `cards[].items[]` | `usage_plan_card_items` | | `cards[].questions[]` | `usage_plan_card_questions` | | `cards[].words[]` | `usage_plan_card_words` | | `clipId/bookId/audioUrl/targetSec/mode/readable/placeholder/reason/letters/deeplink` | 卡表列 | ### `reading-records/{child_id}.json` 数组元素 | JSON | 表.列 | |------|--------| | `id` | `record_id` | | `child_id` `stage` `book_folder` `book_title` | `child_id` `stage_id` `book_folder` `book_title`;`book_id` 派生 | | `started_at` `finished_at` `duration_sec` `total_pages` `pages_read` | 同名 | | `completed` | 0/1 | | `unknown_words[]` | `usage_reading_record_unknown_words` | | `plan_item_id` `mode` `event_id` | 列 | | `created_at` | `created_at` | ### 报表 `reports/daily/{date}.json`: - 标量 → `usage_report_daily` - `completedCards/skippedCards/deferredCards` → `usage_report_daily_cards.bucket` - `highlights[]` → `usage_report_daily_highlights` `reports/weekly/{week}.json`: - 标量 → `usage_report_weekly`(`unknownWordsChange.from/to` → `unknown_from/to`;`nextWeek.rule/reread` → `next_rule/next_reread`) - `weakWords/weakPhonemes/weakHfWords` → `usage_report_weekly_words.bucket` 且 upsert `profile_weak_current` - `confusionPairs[]` → `bucket=confusion`,`extra=weight` - `skippedCards` / `expiredCarryovers` → `usage_report_weekly_skipped`(`expired=1`) - `booksRead[]` → `usage_report_weekly_books` - `nextWeek.words[]` → `usage_report_weekly_next_words` - `hasData=false` 的周:只插 `usage_report_weekly.has_data=0` --- ## 6. 资源索引(无独立 JSON) 由路径规则生成行,**不读文件体**。`rel_uri` 无 `/estudy` 前缀,切流后用 **fs_slug**(规范公开路径);磁盘多出来的 `images/books/` 由 Nginx alias 消化,不入库。`package_id` 对齐 02/03 下载包,不是按 stage。 | 对象 | `assets.kind` | `rel_uri` 例 | `asset_links` | `package_id` | |------|---------------|--------------|---------------|--------------| | 页图 | `book_page_image` | `/images/{stage}/{fs_slug}/{file}` | `book_page` / `{book_id}:{page}` / `page_image` | `book:{book_id}` | | 整本音频 | `book_audio` | `/audio/books/{stage}/{fs_slug}/book.mp3` | `book` / book_id / `read_aloud` | `book:{book_id}` | | 音素 | `phoneme_audio` | `/audio/phonemes/{file}` | `phoneme` / ipa / `audio` | `core-runtime` | | 单词 TTS | `word_tts` | `/audio/tts-words/{accent}/{voice}/{word}.mp3` | `word` + accent/voice / `tts` | `core-runtime` | | 单词本地音 | `word_audio` | `/audio/words/...` | `word` / `local` | `dict-words` | | 字母闪卡图 | `flashcard` | `/flashcards/...` | `letter` 或 `qa` | `course:opw-flashcards`(亦在 core) | | 字母 PDF | `letter_card_pdf` | `/flashcards/letters/*.pdf` | `letter_card_file` | `course:opw-flashcards` | `sha256` `byte_size` 留空直到 03 路扫描磁盘。`sha1` 可空(课程生成器用 `catalog_packages.source_sha1`)。 --- ## 7. 示例(已写入 sqlite.sql) `phonics-data.words.cat` → `catalog_words` + legacy ipa/letter 三行。 `data.json` 猫 → `catalog_word_definitions('cat','n.','猫')`。 `book-index` At the Seaside + structured 第 2 页首词 `The` → `catalog_books` / `pages` / `sentences` / `sentence_words`。 `profile.json` default → `ident_children` + settings + cursors。 `srs.json items['word:cat']` → `usage_srs_items` + source `unit/W1`。 events 一行 phonics listen_pick → `usage_events` 打平列。 `reading-records` 一条 → `usage_reading_records`。