www
490 lines
6.8 KiB
Markdown
Executable File
490 lines
6.8 KiB
Markdown
Executable File
# 05_数据库表结构设计
|
||
|
||
## 1. 核心表清单
|
||
|
||
```text
|
||
users
|
||
projects
|
||
novel_sources
|
||
novel_chapters
|
||
copyright_records
|
||
story_bibles
|
||
world_bibles
|
||
characters
|
||
character_images
|
||
character_memories
|
||
episodes
|
||
episode_scripts
|
||
storyboard_shots
|
||
shot_images
|
||
plot_memories
|
||
plot_threads
|
||
continuity_checks
|
||
assets
|
||
render_tasks
|
||
provider_configs
|
||
provider_logs
|
||
orders
|
||
quota_accounts
|
||
quota_logs
|
||
revision_requests
|
||
content_reviews
|
||
case_showcases
|
||
analytics_events
|
||
system_configs
|
||
operation_logs
|
||
```
|
||
|
||
---
|
||
|
||
## 2. projects
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
user_id BIGINT NOT NULL
|
||
title VARCHAR(255)
|
||
input_mode VARCHAR(50) -- ai_original/upload/admin_import
|
||
genre VARCHAR(100)
|
||
style_code VARCHAR(100)
|
||
output_type VARCHAR(50)
|
||
target_episode_count INT
|
||
episode_duration INT
|
||
status VARCHAR(80)
|
||
copyright_status VARCHAR(80)
|
||
payment_status VARCHAR(80)
|
||
quality_level VARCHAR(50)
|
||
is_long_series BOOLEAN DEFAULT FALSE
|
||
created_at DATETIME
|
||
updated_at DATETIME
|
||
completed_at DATETIME
|
||
```
|
||
|
||
索引：
|
||
|
||
```text
|
||
user_id,status
|
||
genre,status
|
||
created_at
|
||
```
|
||
|
||
---
|
||
|
||
## 3. novel_sources
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT NOT NULL
|
||
source_type VARCHAR(50)
|
||
title VARCHAR(255)
|
||
author_name VARCHAR(100)
|
||
raw_asset_id BIGINT
|
||
raw_text LONGTEXT
|
||
clean_text LONGTEXT
|
||
word_count INT
|
||
chapter_count INT
|
||
parse_status VARCHAR(50)
|
||
parse_report JSON
|
||
created_at DATETIME
|
||
```
|
||
|
||
---
|
||
|
||
## 4. novel_chapters
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT NOT NULL
|
||
novel_source_id BIGINT
|
||
chapter_no INT
|
||
title VARCHAR(255)
|
||
content LONGTEXT
|
||
summary TEXT
|
||
visual_summary TEXT
|
||
word_count INT
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
```
|
||
|
||
`visual_summary` 用于记录该章节可视化场景摘要。
|
||
|
||
---
|
||
|
||
## 5. copyright_records
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
user_id BIGINT
|
||
authorization_type VARCHAR(50)
|
||
statement_text TEXT
|
||
ip VARCHAR(80)
|
||
user_agent TEXT
|
||
confirmed_at DATETIME
|
||
```
|
||
|
||
authorization_type：
|
||
|
||
```text
|
||
author_self
|
||
licensed
|
||
public_domain
|
||
internal_test
|
||
ai_original
|
||
```
|
||
|
||
---
|
||
|
||
## 6. story_bibles
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
title VARCHAR(255)
|
||
logline TEXT
|
||
main_plot TEXT
|
||
core_conflict TEXT
|
||
selling_points TEXT
|
||
tone VARCHAR(100)
|
||
world_summary TEXT
|
||
ending_direction TEXT
|
||
taboo_rules TEXT
|
||
version INT
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
updated_at DATETIME
|
||
```
|
||
|
||
---
|
||
|
||
## 7. world_bibles
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
world_type VARCHAR(100)
|
||
setting_text TEXT
|
||
rules_text TEXT
|
||
power_system TEXT
|
||
social_structure TEXT
|
||
time_period TEXT
|
||
visual_rules TEXT
|
||
forbidden_rules TEXT
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
```
|
||
|
||
---
|
||
|
||
## 8. characters
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
name VARCHAR(100)
|
||
role_type VARCHAR(50)
|
||
gender_label VARCHAR(50)
|
||
age_group VARCHAR(50)
|
||
identity_desc TEXT
|
||
appearance_desc TEXT
|
||
hair_desc TEXT
|
||
costume_rules TEXT
|
||
personality_desc TEXT
|
||
speech_style TEXT
|
||
relationship_desc TEXT
|
||
character_arc TEXT
|
||
negative_rules TEXT
|
||
anchor_asset_id BIGINT
|
||
importance_level INT
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
```
|
||
|
||
role_type：
|
||
|
||
```text
|
||
lead_male
|
||
lead_female
|
||
villain
|
||
supporting
|
||
extra
|
||
```
|
||
|
||
---
|
||
|
||
## 9. character_images
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
character_id BIGINT
|
||
asset_id BIGINT
|
||
image_type VARCHAR(50)
|
||
prompt_text TEXT
|
||
negative_prompt TEXT
|
||
is_anchor BOOLEAN
|
||
quality_score DECIMAL(5,2)
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
```
|
||
|
||
image_type：
|
||
|
||
```text
|
||
front_reference
|
||
side_reference
|
||
expression
|
||
costume
|
||
anchor
|
||
episode_variant
|
||
```
|
||
|
||
---
|
||
|
||
## 10. character_memories
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
character_id BIGINT
|
||
episode_id BIGINT
|
||
memory_type VARCHAR(50)
|
||
content TEXT
|
||
created_at DATETIME
|
||
```
|
||
|
||
memory_type：
|
||
|
||
```text
|
||
appearance_state
|
||
relationship_state
|
||
emotion_state
|
||
goal_state
|
||
injury_state
|
||
costume_state
|
||
```
|
||
|
||
---
|
||
|
||
## 11. episodes
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
episode_no INT
|
||
source_chapter_ids JSON
|
||
title VARCHAR(255)
|
||
summary TEXT
|
||
opening_hook TEXT
|
||
middle_conflict TEXT
|
||
ending_hook TEXT
|
||
target_duration INT
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
```
|
||
|
||
---
|
||
|
||
## 12. episode_scripts
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
episode_id BIGINT
|
||
script_text LONGTEXT
|
||
narration_text LONGTEXT
|
||
dialogue_json JSON
|
||
version INT
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
```
|
||
|
||
---
|
||
|
||
## 13. storyboard_shots
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
episode_id BIGINT
|
||
shot_no INT
|
||
scene_name VARCHAR(255)
|
||
location_desc TEXT
|
||
characters_json JSON
|
||
visual_desc TEXT
|
||
action_desc TEXT
|
||
dialogue_text TEXT
|
||
narration_text TEXT
|
||
camera_motion VARCHAR(100)
|
||
effect_type VARCHAR(100)
|
||
duration DECIMAL(6,2)
|
||
prompt_text TEXT
|
||
negative_prompt TEXT
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
```
|
||
|
||
---
|
||
|
||
## 14. plot_memories
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
episode_id BIGINT
|
||
chapter_id BIGINT
|
||
memory_type VARCHAR(50)
|
||
content TEXT
|
||
importance_level INT
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
```
|
||
|
||
memory_type：
|
||
|
||
```text
|
||
event
|
||
clue
|
||
foreshadowing
|
||
resolved_conflict
|
||
unresolved_conflict
|
||
relationship_change
|
||
world_rule
|
||
item_state
|
||
```
|
||
|
||
---
|
||
|
||
## 15. plot_threads
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
thread_name VARCHAR(255)
|
||
thread_type VARCHAR(80)
|
||
description TEXT
|
||
start_episode_no INT
|
||
expected_resolve_episode_no INT
|
||
resolved_episode_no INT
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
```
|
||
|
||
thread_type：
|
||
|
||
```text
|
||
main_plot
|
||
romance
|
||
revenge
|
||
mystery
|
||
villain_plan
|
||
character_growth
|
||
```
|
||
|
||
---
|
||
|
||
## 16. continuity_checks
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
episode_id BIGINT
|
||
check_type VARCHAR(80)
|
||
result_status VARCHAR(50)
|
||
issue_text TEXT
|
||
suggestion_text TEXT
|
||
created_at DATETIME
|
||
```
|
||
|
||
check_type：
|
||
|
||
```text
|
||
character_name
|
||
relationship
|
||
timeline
|
||
appearance
|
||
plot_thread
|
||
world_rule
|
||
```
|
||
|
||
---
|
||
|
||
## 17. assets
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
user_id BIGINT
|
||
project_id BIGINT
|
||
asset_type VARCHAR(50)
|
||
file_path VARCHAR(500)
|
||
file_url VARCHAR(500)
|
||
mime_type VARCHAR(100)
|
||
width INT
|
||
height INT
|
||
duration DECIMAL(10,2)
|
||
size BIGINT
|
||
hash VARCHAR(128)
|
||
visibility VARCHAR(30)
|
||
status VARCHAR(50)
|
||
created_at DATETIME
|
||
```
|
||
|
||
---
|
||
|
||
## 18. render_tasks
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
episode_id BIGINT
|
||
shot_id BIGINT
|
||
task_type VARCHAR(80)
|
||
provider_id BIGINT
|
||
status VARCHAR(50)
|
||
input_json JSON
|
||
input_hash VARCHAR(128)
|
||
output_asset_id BIGINT
|
||
provider_request_id VARCHAR(255)
|
||
retry_count INT
|
||
max_retry INT
|
||
cost_estimate DECIMAL(12,4)
|
||
cost_actual DECIMAL(12,4)
|
||
error_code VARCHAR(100)
|
||
error_message TEXT
|
||
created_at DATETIME
|
||
started_at DATETIME
|
||
finished_at DATETIME
|
||
```
|
||
|
||
---
|
||
|
||
## 19. analytics_events
|
||
|
||
```sql
|
||
id BIGINT PRIMARY KEY
|
||
project_id BIGINT
|
||
episode_id BIGINT
|
||
event_type VARCHAR(80)
|
||
platform VARCHAR(80)
|
||
metric_json JSON
|
||
created_at DATETIME
|
||
```
|
||
|
||
用于后续记录播放量、完播率、点赞率、评论反馈等。
|
||
|
||
---
|
||
|
||
## 20. 索引建议
|
||
|
||
```text
|
||
projects(user_id,status)
|
||
novel_chapters(project_id,chapter_no)
|
||
characters(project_id,role_type)
|
||
episodes(project_id,episode_no)
|
||
storyboard_shots(project_id,episode_id,shot_no)
|
||
plot_memories(project_id,episode_id)
|
||
plot_threads(project_id,status)
|
||
render_tasks(project_id,status)
|
||
render_tasks(task_type,status)
|
||
assets(project_id,asset_type)
|
||
```
|