mixvideo-v2/.promptx/database-design-standards.md

MixVideo 数据库设计规范

数据库技术栈

核心技术

• SQLite 3.40+: 嵌入式数据库
• WAL模式: 提高并发性能
• 外键约束: 保证数据一致性
• 连接池: 支持高并发访问
• 读写分离: 优化查询性能

数据库配置

-- 启用WAL模式
PRAGMA journal_mode = WAL;

-- 启用外键约束
PRAGMA foreign_keys = ON;

-- 设置同步模式
PRAGMA synchronous = NORMAL;

-- 设置缓存大小
PRAGMA cache_size = 10000;

-- 设置超时时间
PRAGMA busy_timeout = 5000;

-- 启用安全删除
PRAGMA secure_delete = ON;

-- 设置临时存储
PRAGMA temp_store = MEMORY;

表设计规范

命名规范

• 表名: snake_case复数形式 (如 materials, projects)
• 字段名: snake_case (如 created_at, project_id)
• 索引名: idx_表名_字段名 (如 idx_materials_project_id)
• 外键名: fk_表名_引用表名 (如 fk_materials_projects)

字段设计规范

-- 主键设计
id TEXT PRIMARY KEY NOT NULL,  -- 使用UUID字符串

-- 时间戳字段
created_at TEXT NOT NULL DEFAULT (datetime('now')),
updated_at TEXT NOT NULL DEFAULT (datetime('now')),

-- 软删除字段
is_active INTEGER NOT NULL DEFAULT 1,

-- 外键字段
project_id TEXT NOT NULL,
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE,

-- 枚举字段（使用TEXT存储JSON）
material_type TEXT NOT NULL CHECK (material_type IN ('video', 'audio', 'image')),

-- 可选字段
description TEXT,
duration INTEGER,  -- 可为NULL

-- 文件路径字段
path TEXT NOT NULL,
size INTEGER NOT NULL CHECK (size > 0),

核心表结构

1. 项目表 (projects)

CREATE TABLE projects (
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    path TEXT NOT NULL UNIQUE,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    is_active INTEGER NOT NULL DEFAULT 1
);

-- 索引
CREATE INDEX idx_projects_name ON projects(name);
CREATE INDEX idx_projects_path ON projects(path);
CREATE INDEX idx_projects_created_at ON projects(created_at);

2. 素材表 (materials)

CREATE TABLE materials (
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    path TEXT NOT NULL,
    material_type TEXT NOT NULL CHECK (material_type IN ('video', 'audio', 'image')),
    size INTEGER NOT NULL CHECK (size > 0),
    duration INTEGER,  -- 视频/音频时长(秒)
    width INTEGER,     -- 图片/视频宽度
    height INTEGER,    -- 图片/视频高度
    fps REAL,          -- 视频帧率
    project_id TEXT NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    is_active INTEGER NOT NULL DEFAULT 1,
    
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE
);

-- 索引
CREATE INDEX idx_materials_project_id ON materials(project_id);
CREATE INDEX idx_materials_type ON materials(material_type);
CREATE INDEX idx_materials_name ON materials(name);
CREATE INDEX idx_materials_created_at ON materials(created_at);
CREATE UNIQUE INDEX idx_materials_path_project ON materials(path, project_id);

3. 模特表 (models)

CREATE TABLE models (
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    stage_name TEXT,
    gender TEXT NOT NULL CHECK (gender IN ('male', 'female', 'other')),
    age INTEGER CHECK (age > 0 AND age < 150),
    height INTEGER CHECK (height > 0 AND height < 300),  -- 厘米
    weight INTEGER CHECK (weight > 0 AND weight < 500),  -- 公斤
    measurements TEXT,  -- JSON格式存储三围
    description TEXT,
    tags TEXT,  -- JSON数组格式存储标签
    avatar_path TEXT,
    contact_info TEXT,  -- JSON格式存储联系信息
    social_media TEXT,  -- JSON格式存储社交媒体
    status TEXT NOT NULL DEFAULT 'active' CHECK (status IN ('active', 'inactive', 'archived')),
    rating REAL CHECK (rating >= 1.0 AND rating <= 5.0),
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    is_active INTEGER NOT NULL DEFAULT 1
);

-- 索引
CREATE INDEX idx_models_name ON models(name);
CREATE INDEX idx_models_gender ON models(gender);
CREATE INDEX idx_models_status ON models(status);
CREATE INDEX idx_models_rating ON models(rating);

4. 模特照片表 (model_photos)

CREATE TABLE model_photos (
    id TEXT PRIMARY KEY NOT NULL,
    model_id TEXT NOT NULL,
    photo_type TEXT NOT NULL CHECK (photo_type IN ('portrait', 'full_body', 'casual', 'formal', 'swimwear', 'other')),
    path TEXT NOT NULL,
    description TEXT,
    is_primary INTEGER NOT NULL DEFAULT 0,  -- 是否为主要照片
    sort_order INTEGER NOT NULL DEFAULT 0,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    is_active INTEGER NOT NULL DEFAULT 1,
    
    FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE
);

-- 索引
CREATE INDEX idx_model_photos_model_id ON model_photos(model_id);
CREATE INDEX idx_model_photos_type ON model_photos(photo_type);
CREATE INDEX idx_model_photos_primary ON model_photos(is_primary);
CREATE UNIQUE INDEX idx_model_photos_path ON model_photos(path);

5. 模板表 (templates)

CREATE TABLE templates (
    id TEXT PRIMARY KEY NOT NULL,
    name TEXT NOT NULL,
    description TEXT,
    canvas_config TEXT NOT NULL,  -- JSON格式存储画布配置
    duration INTEGER NOT NULL,    -- 模板总时长(微秒)
    fps REAL NOT NULL,
    materials TEXT,  -- JSON格式存储模板素材
    tracks TEXT,     -- JSON格式存储轨道信息
    import_status TEXT NOT NULL DEFAULT 'pending' CHECK (import_status IN ('pending', 'processing', 'completed', 'failed')),
    source_file_path TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    is_active INTEGER NOT NULL DEFAULT 1
);

-- 索引
CREATE INDEX idx_templates_name ON templates(name);
CREATE INDEX idx_templates_status ON templates(import_status);
CREATE INDEX idx_templates_created_at ON templates(created_at);

6. AI分类记录表 (video_classifications)

CREATE TABLE video_classifications (
    id TEXT PRIMARY KEY NOT NULL,
    material_id TEXT NOT NULL,
    classification_result TEXT NOT NULL,  -- JSON格式存储分类结果
    confidence_score REAL,
    processing_time INTEGER,  -- 处理时间(毫秒)
    ai_model_version TEXT,
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending', 'processing', 'completed', 'failed')),
    error_message TEXT,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    
    FOREIGN KEY (material_id) REFERENCES materials(id) ON DELETE CASCADE
);

-- 索引
CREATE INDEX idx_video_classifications_material_id ON video_classifications(material_id);
CREATE INDEX idx_video_classifications_status ON video_classifications(status);
CREATE INDEX idx_video_classifications_created_at ON video_classifications(created_at);

7. 穿搭图片表 (outfit_images)

CREATE TABLE outfit_images (
    id TEXT PRIMARY KEY NOT NULL,
    model_id TEXT NOT NULL,
    image_path TEXT NOT NULL,
    prompt TEXT,
    negative_prompt TEXT,
    generation_params TEXT,  -- JSON格式存储生成参数
    is_favorite INTEGER NOT NULL DEFAULT 0,
    tags TEXT,  -- JSON数组格式存储标签
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    updated_at TEXT NOT NULL DEFAULT (datetime('now')),
    is_active INTEGER NOT NULL DEFAULT 1,
    
    FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE CASCADE
);

-- 索引
CREATE INDEX idx_outfit_images_model_id ON outfit_images(model_id);
CREATE INDEX idx_outfit_images_favorite ON outfit_images(is_favorite);
CREATE INDEX idx_outfit_images_created_at ON outfit_images(created_at);
CREATE UNIQUE INDEX idx_outfit_images_path ON outfit_images(image_path);

8. 对话记录表 (conversations)

CREATE TABLE conversations (
    id TEXT PRIMARY KEY NOT NULL,
    session_id TEXT NOT NULL,
    role TEXT NOT NULL CHECK (role IN ('user', 'assistant', 'system')),
    content TEXT NOT NULL,
    metadata TEXT,  -- JSON格式存储元数据
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    is_active INTEGER NOT NULL DEFAULT 1
);

-- 索引
CREATE INDEX idx_conversations_session_id ON conversations(session_id);
CREATE INDEX idx_conversations_role ON conversations(role);
CREATE INDEX idx_conversations_created_at ON conversations(created_at);

数据库迁移规范

迁移文件结构

migrations/
├── 001_initial_schema.sql
├── 002_add_model_photos.sql
├── 003_add_video_classifications.sql
├── 004_add_outfit_images.sql
└── 005_add_conversations.sql

迁移脚本模板

-- Migration: 001_initial_schema.sql
-- Description: 创建初始数据库结构
-- Date: 2024-01-01

BEGIN TRANSACTION;

-- 创建项目表
CREATE TABLE projects (
    -- 表结构定义
);

-- 创建索引
CREATE INDEX idx_projects_name ON projects(name);

-- 插入初始数据（如果需要）
INSERT INTO projects (id, name, path) VALUES 
('default', 'Default Project', '/default/path');

COMMIT;

迁移管理

pub struct DatabaseMigration {
    version: u32,
    description: String,
    sql: String,
}

impl Database {
    pub async fn migrate(&self) -> Result<()> {
        // 创建迁移记录表
        self.create_migration_table().await?;
        
        // 获取当前版本
        let current_version = self.get_current_version().await?;
        
        // 执行待处理的迁移
        let migrations = self.get_pending_migrations(current_version)?;
        
        for migration in migrations {
            self.execute_migration(&migration).await?;
        }
        
        Ok(())
    }
}

查询优化规范

索引策略

-- 单列索引
CREATE INDEX idx_materials_project_id ON materials(project_id);

-- 复合索引
CREATE INDEX idx_materials_project_type ON materials(project_id, material_type);

-- 唯一索引
CREATE UNIQUE INDEX idx_projects_path ON projects(path);

-- 部分索引
CREATE INDEX idx_active_materials ON materials(project_id) WHERE is_active = 1;

查询优化

-- ✅ 使用索引的查询
SELECT * FROM materials 
WHERE project_id = ? AND material_type = 'video'
ORDER BY created_at DESC
LIMIT 20;

-- ✅ 使用EXISTS而不是IN
SELECT * FROM projects p
WHERE EXISTS (
    SELECT 1 FROM materials m 
    WHERE m.project_id = p.id AND m.is_active = 1
);

-- ✅ 使用适当的JOIN
SELECT p.name, COUNT(m.id) as material_count
FROM projects p
LEFT JOIN materials m ON p.id = m.project_id AND m.is_active = 1
WHERE p.is_active = 1
GROUP BY p.id, p.name;

分页查询

-- 基于OFFSET的分页（适用于小数据集）
SELECT * FROM materials
WHERE project_id = ?
ORDER BY created_at DESC
LIMIT ? OFFSET ?;

-- 基于游标的分页（适用于大数据集）
SELECT * FROM materials
WHERE project_id = ? AND created_at < ?
ORDER BY created_at DESC
LIMIT ?;

数据完整性规范

外键约束

-- 级联删除
FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE CASCADE

-- 限制删除
FOREIGN KEY (model_id) REFERENCES models(id) ON DELETE RESTRICT

-- 设置NULL
FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL

检查约束

-- 数值范围检查
CHECK (age > 0 AND age < 150)

-- 枚举值检查
CHECK (status IN ('active', 'inactive', 'archived'))

-- 字符串长度检查
CHECK (length(name) > 0 AND length(name) <= 255)

触发器

-- 自动更新时间戳
CREATE TRIGGER update_materials_timestamp
AFTER UPDATE ON materials
FOR EACH ROW
BEGIN
    UPDATE materials SET updated_at = datetime('now') WHERE id = NEW.id;
END;

-- 软删除触发器
CREATE TRIGGER soft_delete_materials
INSTEAD OF DELETE ON materials
FOR EACH ROW
BEGIN
    UPDATE materials SET is_active = 0, updated_at = datetime('now') WHERE id = OLD.id;
END;

备份和恢复规范

备份策略

pub struct DatabaseBackup {
    source_path: PathBuf,
    backup_dir: PathBuf,
}

impl DatabaseBackup {
    pub async fn create_backup(&self) -> Result<PathBuf> {
        let timestamp = Utc::now().format("%Y%m%d_%H%M%S");
        let backup_name = format!("mixvideo_backup_{}.db", timestamp);
        let backup_path = self.backup_dir.join(backup_name);
        
        // 使用SQLite的备份API
        let source = Connection::open(&self.source_path)?;
        let backup = Connection::open(&backup_path)?;
        
        let backup_handle = rusqlite::backup::Backup::new(&source, &backup)?;
        backup_handle.run_to_completion(5, Duration::from_millis(250), None)?;
        
        Ok(backup_path)
    }
    
    pub async fn restore_backup(&self, backup_path: &Path) -> Result<()> {
        // 验证备份文件
        self.validate_backup(backup_path)?;
        
        // 创建当前数据库的备份
        let current_backup = self.create_backup().await?;
        
        // 恢复数据库
        std::fs::copy(backup_path, &self.source_path)?;
        
        // 验证恢复的数据库
        self.validate_database().await?;
        
        Ok(())
    }
}

数据验证

-- 检查数据完整性
PRAGMA integrity_check;

-- 检查外键约束
PRAGMA foreign_key_check;

-- 分析数据库统计信息
ANALYZE;

性能监控规范

查询性能分析

-- 启用查询计划分析
EXPLAIN QUERY PLAN SELECT * FROM materials WHERE project_id = ?;

-- 查看表统计信息
SELECT name, tbl_name, sql FROM sqlite_master WHERE type = 'index';

-- 检查表大小
SELECT 
    name,
    COUNT(*) as row_count,
    SUM(length(sql)) as size_estimate
FROM sqlite_master 
WHERE type = 'table'
GROUP BY name;

性能监控

pub struct DatabaseMetrics {
    query_times: HashMap<String, Vec<Duration>>,
    connection_pool_stats: ConnectionPoolStats,
}

impl DatabaseMetrics {
    pub fn record_query_time(&mut self, query: &str, duration: Duration) {
        self.query_times
            .entry(query.to_string())
            .or_insert_with(Vec::new)
            .push(duration);
    }
    
    pub fn get_slow_queries(&self, threshold: Duration) -> Vec<(String, Duration)> {
        self.query_times
            .iter()
            .filter_map(|(query, times)| {
                let avg_time = times.iter().sum::<Duration>() / times.len() as u32;
                if avg_time > threshold {
                    Some((query.clone(), avg_time))
                } else {
                    None
                }
            })
            .collect()
    }
}

安全规范

数据加密

// 敏感数据加密存储
pub fn encrypt_sensitive_data(data: &str, key: &[u8]) -> Result<String> {
    // 使用AES加密
    let cipher = Aes256Gcm::new(key.into());
    let nonce = Aes256Gcm::generate_nonce(&mut OsRng);
    let ciphertext = cipher.encrypt(&nonce, data.as_bytes())?;
    
    // 组合nonce和密文
    let mut result = nonce.to_vec();
    result.extend_from_slice(&ciphertext);
    
    Ok(base64::encode(result))
}

访问控制

-- 创建只读视图
CREATE VIEW materials_readonly AS
SELECT id, name, material_type, size, created_at
FROM materials
WHERE is_active = 1;

-- 限制敏感字段访问
CREATE VIEW models_public AS
SELECT id, name, stage_name, gender, description, tags, avatar_path, rating
FROM models
WHERE is_active = 1;

审计日志

CREATE TABLE audit_logs (
    id TEXT PRIMARY KEY NOT NULL,
    table_name TEXT NOT NULL,
    operation TEXT NOT NULL CHECK (operation IN ('INSERT', 'UPDATE', 'DELETE')),
    record_id TEXT NOT NULL,
    old_values TEXT,  -- JSON格式
    new_values TEXT,  -- JSON格式
    user_id TEXT,
    timestamp TEXT NOT NULL DEFAULT (datetime('now'))
);