WangDL 00b7b3613c restructure doc repo: new directory layout with status prefixes, archive completed docs

Co-Authored-By: Claude Opus 4.7 <noreply@anthropic.com>

2026-05-15 17:29:57 +08:00

17 KiB

Raw Permalink Blame History

source, updated

source	updated
AI回答.md	2026-05-09

知习 MySQL 数据库表结构设计

共 27 张表，v0.1 先建 24 张核心表。

通用字段规范

每张核心表统一使用：

id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
deleted_at DATETIME NULL

id：内部主键
created_at：创建时间
updated_at：更新时间
deleted_at：软删除

状态字段统一用 VARCHAR(32)，不用 MySQL ENUM。

一、用户与认证表（5 张）

1. users 用户表

users
- id BIGINT UNSIGNED PK
- email VARCHAR(255) NULL
- nickname VARCHAR(100) NULL
- avatar_url VARCHAR(500) NULL
- status VARCHAR(32) NOT NULL DEFAULT 'active'
- onboarding_completed TINYINT(1) NOT NULL DEFAULT 0
- last_login_at DATETIME NULL
- created_at DATETIME
- updated_at DATETIME
- deleted_at DATETIME NULL

索引：

INDEX idx_users_email (email)
INDEX idx_users_status (status)

2. auth_accounts 第三方登录账号表

auth_accounts
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- provider VARCHAR(32) NOT NULL               -- apple
- provider_user_id VARCHAR(255) NOT NULL       -- Apple userIdentifier / sub
- email VARCHAR(255) NULL
- raw_profile_json JSON NULL
- created_at DATETIME
- updated_at DATETIME

索引：

UNIQUE KEY uk_provider_user (provider, provider_user_id)
INDEX idx_auth_accounts_user_id (user_id)

3. refresh_tokens 刷新 Token 表

refresh_tokens
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- token_hash VARCHAR(255) NOT NULL
- device_id VARCHAR(255) NULL
- device_name VARCHAR(255) NULL
- expires_at DATETIME NOT NULL
- revoked_at DATETIME NULL
- created_at DATETIME
- updated_at DATETIME

索引：

INDEX idx_refresh_tokens_user_id (user_id)
INDEX idx_refresh_tokens_token_hash (token_hash)

4. user_profiles 用户资料扩展表

user_profiles
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- learning_identity VARCHAR(100) NULL      -- 系统学习者 / 备考用户 / 知识工作者
- learning_direction VARCHAR(255) NULL     -- 认知科学 / AIGC / 产品设计
- bio TEXT NULL
- current_goal VARCHAR(255) NULL
- created_at DATETIME
- updated_at DATETIME

索引：

UNIQUE KEY uk_user_profiles_user_id (user_id)

5. user_preferences 用户学习偏好表

user_preferences
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- preferred_methods JSON NULL
  -- ["active_recall", "spaced_repetition", "feynman", "retrieval_practice"]
- default_focus_minutes INT NOT NULL DEFAULT 25
- ai_suggestion_level VARCHAR(32) NOT NULL DEFAULT 'normal'
  -- low / normal / high
- language VARCHAR(32) NOT NULL DEFAULT 'zh-CN'
- appearance VARCHAR(32) NOT NULL DEFAULT 'system'
- notification_enabled TINYINT(1) NOT NULL DEFAULT 1
- created_at DATETIME
- updated_at DATETIME

索引：

UNIQUE KEY uk_user_preferences_user_id (user_id)

二、知识库相关表（5 张）

6. knowledge_bases 知识库表

knowledge_bases
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- title VARCHAR(255) NOT NULL
- description TEXT NULL
- cover_key VARCHAR(100) NULL
- status VARCHAR(32) NOT NULL DEFAULT 'active'
  -- active / archived / deleted
- item_count INT NOT NULL DEFAULT 0
- last_studied_at DATETIME NULL
- created_at DATETIME
- updated_at DATETIME
- deleted_at DATETIME NULL

索引：

INDEX idx_knowledge_bases_user_id (user_id)
INDEX idx_knowledge_bases_status (status)

7. knowledge_items 知识点/内容表

knowledge_items
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- knowledge_base_id BIGINT UNSIGNED NOT NULL
- parent_id BIGINT UNSIGNED NULL
- item_type VARCHAR(32) NOT NULL
  -- chapter / lesson / concept / note / imported_doc
- title VARCHAR(255) NOT NULL
- content LONGTEXT NULL
- summary TEXT NULL
- source_type VARCHAR(32) NULL
  -- manual / file / url / ai_generated
- source_ref VARCHAR(500) NULL
- order_index INT NOT NULL DEFAULT 0
- status VARCHAR(32) NOT NULL DEFAULT 'active'
- created_at DATETIME
- updated_at DATETIME
- deleted_at DATETIME NULL

索引：

INDEX idx_knowledge_items_user_id (user_id)
INDEX idx_knowledge_items_kb_id (knowledge_base_id)
INDEX idx_knowledge_items_parent_id (parent_id)
INDEX idx_knowledge_items_type (item_type)

8. knowledge_item_relations 知识点关联表

knowledge_item_relations
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- source_item_id BIGINT UNSIGNED NOT NULL
- target_item_id BIGINT UNSIGNED NOT NULL
- relation_type VARCHAR(32) NOT NULL
  -- related / prerequisite / similar / conflict / extension
- confidence DECIMAL(5,2) NULL
- reason TEXT NULL
- created_at DATETIME
- updated_at DATETIME

索引：

INDEX idx_relations_source (source_item_id)
INDEX idx_relations_target (target_item_id)

9. tags 标签表

tags
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- name VARCHAR(100) NOT NULL
- color VARCHAR(32) NULL
- created_at DATETIME
- updated_at DATETIME

索引：

UNIQUE KEY uk_user_tag_name (user_id, name)

10. knowledge_item_tags 知识点标签关联表

knowledge_item_tags
- id BIGINT UNSIGNED PK
- knowledge_item_id BIGINT UNSIGNED NOT NULL
- tag_id BIGINT UNSIGNED NOT NULL
- created_at DATETIME

索引：

UNIQUE KEY uk_item_tag (knowledge_item_id, tag_id)

三、资料导入相关表（2 张）

11. uploaded_files 上传文件表

uploaded_files
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- filename VARCHAR(255) NOT NULL
- mime_type VARCHAR(100) NULL
- storage_path VARCHAR(500) NOT NULL
- size_bytes BIGINT UNSIGNED NOT NULL DEFAULT 0
- checksum VARCHAR(255) NULL
- created_at DATETIME

索引：

INDEX idx_uploaded_files_user_id (user_id)

12. document_imports 资料导入任务表

document_imports
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- knowledge_base_id BIGINT UNSIGNED NULL
- file_id BIGINT UNSIGNED NULL
- source_type VARCHAR(32) NOT NULL
  -- file / text / url
- source_name VARCHAR(255) NULL
- source_url VARCHAR(500) NULL
- raw_text LONGTEXT NULL
- status VARCHAR(32) NOT NULL DEFAULT 'pending'
  -- pending / processing / success / failed
- progress INT NOT NULL DEFAULT 0
- error_message TEXT NULL
- result_json JSON NULL
- started_at DATETIME NULL
- completed_at DATETIME NULL
- created_at DATETIME
- updated_at DATETIME

索引：

INDEX idx_document_imports_user_id (user_id)
INDEX idx_document_imports_status (status)

四、学习过程相关表（2 张）

13. learning_sessions 学习会话表

learning_sessions
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- knowledge_base_id BIGINT UNSIGNED NULL
- knowledge_item_id BIGINT UNSIGNED NULL
- mode VARCHAR(32) NOT NULL
  -- reading / active_recall / review / feynman / free_learning
- status VARCHAR(32) NOT NULL DEFAULT 'active'
  -- active / completed / cancelled
- started_at DATETIME NOT NULL
- ended_at DATETIME NULL
- duration_seconds INT NOT NULL DEFAULT 0
- focus_minutes INT NULL
- metadata JSON NULL
- created_at DATETIME
- updated_at DATETIME

索引：

INDEX idx_learning_sessions_user_id (user_id)
INDEX idx_learning_sessions_item_id (knowledge_item_id)
INDEX idx_learning_sessions_started_at (started_at)

14. learning_records 学习记录表

类似 GitHub commit log，语义是学习记录。

learning_records
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- session_id BIGINT UNSIGNED NULL
- record_type VARCHAR(32) NOT NULL
  -- read / active_recall / review / ai_analysis / focus_item_completed
- title VARCHAR(255) NOT NULL
- description TEXT NULL
- duration_seconds INT NOT NULL DEFAULT 0
- occurred_at DATETIME NOT NULL
- metadata JSON NULL
- created_at DATETIME

索引：

INDEX idx_learning_records_user_id (user_id)
INDEX idx_learning_records_occurred_at (occurred_at)

五、主动回忆相关表（2 张）

15. active_recall_questions 主动回忆问题表

active_recall_questions
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- knowledge_item_id BIGINT UNSIGNED NULL
- question_text TEXT NOT NULL
- difficulty VARCHAR(32) NULL
  -- easy / normal / hard
- created_by VARCHAR(32) NOT NULL DEFAULT 'ai'
  -- ai / user / system
- created_at DATETIME
- updated_at DATETIME

索引：

INDEX idx_recall_questions_user_id (user_id)
INDEX idx_recall_questions_item_id (knowledge_item_id)

16. active_recall_answers 主动回忆回答表

active_recall_answers
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- question_id BIGINT UNSIGNED NULL
- session_id BIGINT UNSIGNED NULL
- answer_type VARCHAR(32) NOT NULL DEFAULT 'text'
  -- text / voice
- answer_text LONGTEXT NULL
- audio_file_id BIGINT UNSIGNED NULL
- submitted_at DATETIME NOT NULL
- created_at DATETIME

索引：

INDEX idx_recall_answers_user_id (user_id)
INDEX idx_recall_answers_question_id (question_id)
INDEX idx_recall_answers_session_id (session_id)

六、AI 分析相关表（2 张）

17. ai_analysis_jobs AI 分析任务表

ai_analysis_jobs
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- session_id BIGINT UNSIGNED NULL
- answer_id BIGINT UNSIGNED NULL
- job_type VARCHAR(32) NOT NULL
  -- active_recall_analysis / weak_point_detection / review_generation
- status VARCHAR(32) NOT NULL DEFAULT 'pending'
  -- pending / processing / success / failed
- progress INT NOT NULL DEFAULT 0
- error_message TEXT NULL
- queued_at DATETIME NULL
- started_at DATETIME NULL
- completed_at DATETIME NULL
- created_at DATETIME
- updated_at DATETIME

索引：

INDEX idx_ai_jobs_user_id (user_id)
INDEX idx_ai_jobs_status (status)
INDEX idx_ai_jobs_session_id (session_id)

18. ai_analysis_results AI 分析结果表

ai_analysis_results
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- job_id BIGINT UNSIGNED NOT NULL
- session_id BIGINT UNSIGNED NULL
- answer_id BIGINT UNSIGNED NULL
- summary TEXT NULL
- mastery_score INT NULL          -- 0-100
- strengths JSON NULL
- weaknesses JSON NULL
- suggestions JSON NULL
- next_actions JSON NULL
- raw_result JSON NULL
- created_at DATETIME
- updated_at DATETIME

索引：

INDEX idx_ai_results_user_id (user_id)
INDEX idx_ai_results_job_id (job_id)
INDEX idx_ai_results_session_id (session_id)

七、待巩固项表（1 张）

19. focus_items 待巩固项表

类似 GitHub issue，学习语义叫「待巩固项」。

focus_items
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- knowledge_base_id BIGINT UNSIGNED NULL
- knowledge_item_id BIGINT UNSIGNED NULL
- analysis_result_id BIGINT UNSIGNED NULL
- title VARCHAR(255) NOT NULL
- reason TEXT NULL
- suggestion TEXT NULL
- priority VARCHAR(32) NOT NULL DEFAULT 'normal'
  -- low / normal / high
- status VARCHAR(32) NOT NULL DEFAULT 'open'
  -- open / in_review / completed / ignored
- mastery_score INT NULL
- due_at DATETIME NULL
- completed_at DATETIME NULL
- created_at DATETIME
- updated_at DATETIME
- deleted_at DATETIME NULL

索引：

INDEX idx_focus_items_user_id (user_id)
INDEX idx_focus_items_status (status)
INDEX idx_focus_items_due_at (due_at)

八、复习相关表（3 张）

20. review_cards 复习卡片表

review_cards
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- knowledge_item_id BIGINT UNSIGNED NULL
- focus_item_id BIGINT UNSIGNED NULL
- front_text TEXT NOT NULL
- back_text TEXT NULL
- difficulty VARCHAR(32) NULL
- status VARCHAR(32) NOT NULL DEFAULT 'active'
  -- active / suspended / completed
- next_review_at DATETIME NULL
- interval_days INT NOT NULL DEFAULT 1
- ease_factor DECIMAL(4,2) NOT NULL DEFAULT 2.50
- repetition_count INT NOT NULL DEFAULT 0
- lapse_count INT NOT NULL DEFAULT 0
- created_at DATETIME
- updated_at DATETIME
- deleted_at DATETIME NULL

索引：

INDEX idx_review_cards_user_id (user_id)
INDEX idx_review_cards_next_review_at (next_review_at)
INDEX idx_review_cards_focus_item_id (focus_item_id)

21. review_logs 复习记录表

review_logs
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- review_card_id BIGINT UNSIGNED NOT NULL
- session_id BIGINT UNSIGNED NULL
- rating VARCHAR(32) NOT NULL
  -- again / hard / good / easy
- response_text TEXT NULL
- reviewed_at DATETIME NOT NULL
- next_review_at DATETIME NULL
- created_at DATETIME

索引：

INDEX idx_review_logs_user_id (user_id)
INDEX idx_review_logs_card_id (review_card_id)
INDEX idx_review_logs_reviewed_at (reviewed_at)

22. review_plans 复习计划表

review_plans
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- title VARCHAR(255) NOT NULL
- status VARCHAR(32) NOT NULL DEFAULT 'active'
  -- active / completed / cancelled
- scheduled_at DATETIME NULL
- completed_at DATETIME NULL
- card_count INT NOT NULL DEFAULT 0
- created_at DATETIME
- updated_at DATETIME

索引：

INDEX idx_review_plans_user_id (user_id)
INDEX idx_review_plans_scheduled_at (scheduled_at)

九、学习活跃记录表（1 张）

23. daily_learning_activities 每日学习活跃表

用于个人中心的蓝色学习活跃图。

daily_learning_activities
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- activity_date DATE NOT NULL
- duration_seconds INT NOT NULL DEFAULT 0
- sessions_count INT NOT NULL DEFAULT 0
- active_recall_count INT NOT NULL DEFAULT 0
- review_count INT NOT NULL DEFAULT 0
- ai_analysis_count INT NOT NULL DEFAULT 0
- completed_loop_count INT NOT NULL DEFAULT 0
- activity_level INT NOT NULL DEFAULT 0       -- 0-4，颜色深浅
- created_at DATETIME
- updated_at DATETIME

索引：

UNIQUE KEY uk_user_activity_date (user_id, activity_date)
INDEX idx_daily_activity_user_id (user_id)

十、通知与反馈表（2 张）

24. notifications 消息通知表

notifications
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- type VARCHAR(32) NOT NULL
  -- review_due / ai_analysis_done / learning_suggestion / system
- title VARCHAR(255) NOT NULL
- content TEXT NULL
- data JSON NULL
- read_at DATETIME NULL
- created_at DATETIME

索引：

INDEX idx_notifications_user_id (user_id)
INDEX idx_notifications_read_at (read_at)
INDEX idx_notifications_type (type)

25. feedbacks 用户反馈表

feedbacks
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NULL
- email VARCHAR(255) NULL
- category VARCHAR(64) NOT NULL
  -- feature / bug / experience / privacy / other
- content TEXT NOT NULL
- device_info JSON NULL
- status VARCHAR(32) NOT NULL DEFAULT 'open'
  -- open / processing / resolved / ignored
- created_at DATETIME
- updated_at DATETIME

索引：

INDEX idx_feedbacks_user_id (user_id)
INDEX idx_feedbacks_status (status)

十一、合规与系统表（2 张）

26. user_consents 用户协议同意记录表

user_consents
- id BIGINT UNSIGNED PK
- user_id BIGINT UNSIGNED NOT NULL
- consent_type VARCHAR(32) NOT NULL
  -- privacy_policy / terms_of_service
- version VARCHAR(50) NOT NULL
- accepted_at DATETIME NOT NULL
- ip_address VARCHAR(100) NULL
- user_agent VARCHAR(500) NULL
- created_at DATETIME

索引：

INDEX idx_user_consents_user_id (user_id)
INDEX idx_user_consents_type (consent_type)

27. app_changelogs 更新记录表（可选）

app_changelogs
- id BIGINT UNSIGNED PK
- version VARCHAR(50) NOT NULL
- title VARCHAR(255) NOT NULL
- content TEXT NOT NULL
- platform VARCHAR(32) NOT NULL DEFAULT 'ios'
- published_at DATETIME NULL
- created_at DATETIME
- updated_at DATETIME

v0.1 建表优先级

第一批（24 张，必须）

users
auth_accounts
refresh_tokens
user_profiles
user_preferences

knowledge_bases
knowledge_items
tags
knowledge_item_tags
document_imports
uploaded_files

learning_sessions
learning_records
active_recall_questions
active_recall_answers

ai_analysis_jobs
ai_analysis_results
focus_items

review_cards
review_logs

daily_learning_activities

notifications
feedbacks
user_consents

第二批（3 张，可稍后）

knowledge_item_relations
review_plans
app_changelogs

模块与表对应关系

auth              → users, auth_accounts, refresh_tokens
users             → user_profiles, user_preferences, user_consents
knowledge-base    → knowledge_bases
knowledge-items   → knowledge_items, knowledge_item_relations, tags, knowledge_item_tags
document-import   → uploaded_files, document_imports
learning-session  → learning_sessions, learning_records
active-recall     → active_recall_questions, active_recall_answers
ai-analysis       → ai_analysis_jobs, ai_analysis_results
focus-items       → focus_items
review            → review_cards, review_logs, review_plans
learning-activity → daily_learning_activities
notifications     → notifications
feedback          → feedbacks
system            → app_changelogs

Prisma 生成规范

所有表使用 BIGINT UNSIGNED AUTO_INCREMENT 主键
状态字段使用 VARCHAR，不使用 ENUM
JSON 字段用于存储 AI 分析结构化结果、用户偏好、元数据
核心表添加 created_at、updated_at、deleted_at
为 user_id、status、created_at、外键字段添加合理索引

17 KiB Raw Permalink Blame History Unescape Escape