技术侧数据库表设计

目标

本文档用于补齐产品化和商业化所需的数据结构，覆盖以下能力：

用户与认证
套餐与配额
用量统计
TTS 请求日志
异步任务
缓存索引
管理员审计日志

当前项目已有：

user
user_progress
user_config

本文以“尽量兼容现有表结构、逐步扩展”为原则。

1. 用户与认证

1.1 user

已有表可继续沿用，但建议补充字段。

建议字段：

CREATE TABLE user (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(64) NOT NULL UNIQUE,
    password_hash VARCHAR(255) NOT NULL,
    email VARCHAR(128) NULL,
    phone VARCHAR(32) NULL,
    is_admin TINYINT(1) NOT NULL DEFAULT 0,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    session_token VARCHAR(128) NULL,
    session_expires_at DATETIME NULL,
    last_file VARCHAR(1024) NULL,
    last_page INT NULL,
    last_login_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

新增字段建议：

email：后续找回密码、订单联系
phone：机构客户联系，可选
last_login_at：活跃度统计

1.2 user_session_log

记录登录行为，用于安全和审计。

CREATE TABLE user_session_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    session_token VARCHAR(128) NOT NULL,
    login_ip VARCHAR(64) NULL,
    user_agent VARCHAR(512) NULL,
    login_at DATETIME NOT NULL,
    logout_at DATETIME NULL,
    is_valid TINYINT(1) NOT NULL DEFAULT 1,
    KEY idx_user_login (user_id, login_at),
    CONSTRAINT fk_session_user
        FOREIGN KEY (user_id) REFERENCES user(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2. 套餐与配额

2.1 plan

定义平台套餐。

CREATE TABLE plan (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    code VARCHAR(32) NOT NULL UNIQUE,
    name VARCHAR(64) NOT NULL,
    description VARCHAR(255) NULL,
    price_month DECIMAL(10,2) NULL,
    price_year DECIMAL(10,2) NULL,
    daily_tts_chars INT NOT NULL DEFAULT 0,
    monthly_tts_chars INT NOT NULL DEFAULT 0,
    daily_tts_requests INT NOT NULL DEFAULT 0,
    max_text_length INT NOT NULL DEFAULT 0,
    max_parallel_tasks INT NOT NULL DEFAULT 1,
    is_public TINYINT(1) NOT NULL DEFAULT 1,
    is_active TINYINT(1) NOT NULL DEFAULT 1,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

推荐套餐初始值：

free
pro_monthly
pro_yearly
team
private_deploy

2.2 user_plan

记录用户当前套餐。

CREATE TABLE user_plan (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    plan_id BIGINT NOT NULL,
    start_at DATETIME NOT NULL,
    end_at DATETIME NULL,
    status VARCHAR(32) NOT NULL,
    source VARCHAR(32) NOT NULL DEFAULT 'admin',
    auto_renew TINYINT(1) NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_user_status (user_id, status),
    CONSTRAINT fk_user_plan_user
        FOREIGN KEY (user_id) REFERENCES user(id)
        ON DELETE CASCADE,
    CONSTRAINT fk_user_plan_plan
        FOREIGN KEY (plan_id) REFERENCES plan(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

status 建议值：

active
expired
canceled
trial

2.3 usage_daily

日用量统计，支撑配额判断。

CREATE TABLE usage_daily (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    usage_date DATE NOT NULL,
    tts_chars INT NOT NULL DEFAULT 0,
    tts_requests INT NOT NULL DEFAULT 0,
    audio_seconds INT NOT NULL DEFAULT 0,
    cache_hits INT NOT NULL DEFAULT 0,
    cache_misses INT NOT NULL DEFAULT 0,
    failed_requests INT NOT NULL DEFAULT 0,
    UNIQUE KEY uniq_user_date (user_id, usage_date),
    KEY idx_usage_date (usage_date),
    CONSTRAINT fk_usage_daily_user
        FOREIGN KEY (user_id) REFERENCES user(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

2.4 usage_monthly

月汇总统计，便于后台展示和计费判断。

CREATE TABLE usage_monthly (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    usage_month CHAR(7) NOT NULL,
    tts_chars INT NOT NULL DEFAULT 0,
    tts_requests INT NOT NULL DEFAULT 0,
    audio_seconds INT NOT NULL DEFAULT 0,
    amount_estimated DECIMAL(10,2) NOT NULL DEFAULT 0.00,
    UNIQUE KEY uniq_user_month (user_id, usage_month),
    KEY idx_usage_month (usage_month),
    CONSTRAINT fk_usage_monthly_user
        FOREIGN KEY (user_id) REFERENCES user(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

3. TTS 请求日志

3.1 tts_request_log

最关键的业务日志表。

CREATE TABLE tts_request_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    request_id VARCHAR(64) NOT NULL UNIQUE,
    user_id BIGINT NULL,
    file_path VARCHAR(512) NULL,
    page INT NULL,
    sentence_index INT NULL,
    text_hash VARCHAR(64) NOT NULL,
    text_length INT NOT NULL,
    voice VARCHAR(64) NOT NULL,
    speed DECIMAL(4,2) NOT NULL DEFAULT 1.00,
    cache_key VARCHAR(128) NULL,
    cache_hit TINYINT(1) NOT NULL DEFAULT 0,
    task_id VARCHAR(64) NULL,
    status VARCHAR(32) NOT NULL,
    audio_seconds INT NOT NULL DEFAULT 0,
    latency_ms INT NOT NULL DEFAULT 0,
    error_message VARCHAR(512) NULL,
    created_at DATETIME NOT NULL,
    finished_at DATETIME NULL,
    KEY idx_user_created (user_id, created_at),
    KEY idx_status_created (status, created_at),
    KEY idx_text_hash (text_hash),
    CONSTRAINT fk_tts_log_user
        FOREIGN KEY (user_id) REFERENCES user(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

status 建议值：

queued
processing
success
failed
canceled

4. 异步任务

4.1 async_task

记录长文本与后台任务。

CREATE TABLE async_task (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    task_id VARCHAR(64) NOT NULL UNIQUE,
    user_id BIGINT NULL,
    task_type VARCHAR(32) NOT NULL,
    priority INT NOT NULL DEFAULT 0,
    status VARCHAR(32) NOT NULL,
    payload_json JSON NULL,
    result_json JSON NULL,
    progress INT NOT NULL DEFAULT 0,
    error_message VARCHAR(512) NULL,
    retry_count INT NOT NULL DEFAULT 0,
    started_at DATETIME NULL,
    finished_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_user_status (user_id, status),
    KEY idx_type_status (task_type, status),
    CONSTRAINT fk_async_task_user
        FOREIGN KEY (user_id) REFERENCES user(id)
        ON DELETE SET NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

task_type 建议值：

tts_long_text
cache_warmup
cache_cleanup
usage_rollup

5. 缓存索引

5.1 audio_cache_index

避免只依赖磁盘文件名，便于统计和清理。

CREATE TABLE audio_cache_index (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    cache_key VARCHAR(128) NOT NULL UNIQUE,
    text_hash VARCHAR(64) NOT NULL,
    text_length INT NOT NULL,
    voice VARCHAR(64) NOT NULL,
    speed DECIMAL(4,2) NOT NULL DEFAULT 1.00,
    model_version VARCHAR(64) NULL,
    file_path VARCHAR(512) NOT NULL,
    file_size BIGINT NOT NULL DEFAULT 0,
    audio_seconds INT NOT NULL DEFAULT 0,
    hit_count INT NOT NULL DEFAULT 0,
    created_at DATETIME NOT NULL,
    last_hit_at DATETIME NULL,
    expired_at DATETIME NULL,
    KEY idx_last_hit (last_hit_at),
    KEY idx_text_voice (text_hash, voice)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

6. 管理员审计日志

6.1 admin_audit_log

记录重要管理操作。

CREATE TABLE admin_audit_log (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    admin_user_id BIGINT NOT NULL,
    action VARCHAR(64) NOT NULL,
    target_type VARCHAR(64) NOT NULL,
    target_id VARCHAR(128) NULL,
    detail_json JSON NULL,
    ip VARCHAR(64) NULL,
    created_at DATETIME NOT NULL,
    KEY idx_admin_created (admin_user_id, created_at),
    KEY idx_action_created (action, created_at),
    CONSTRAINT fk_admin_audit_user
        FOREIGN KEY (admin_user_id) REFERENCES user(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

action 示例：

create_user
reset_password
disable_user
assign_plan
clear_cache
retry_task

7. 可选扩展表

7.1 payment_order

如果后续接支付，可增加订单表。

CREATE TABLE payment_order (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    order_no VARCHAR(64) NOT NULL UNIQUE,
    user_id BIGINT NOT NULL,
    plan_id BIGINT NOT NULL,
    amount DECIMAL(10,2) NOT NULL,
    currency VARCHAR(16) NOT NULL DEFAULT 'CNY',
    status VARCHAR(32) NOT NULL,
    paid_at DATETIME NULL,
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_user_created (user_id, created_at),
    CONSTRAINT fk_payment_order_user
        FOREIGN KEY (user_id) REFERENCES user(id),
    CONSTRAINT fk_payment_order_plan
        FOREIGN KEY (plan_id) REFERENCES plan(id)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

7.2 user_file

如果后续需要更完整的文件管理，建议从纯目录管理升级到表管理。

CREATE TABLE user_file (
    id BIGINT PRIMARY KEY AUTO_INCREMENT,
    user_id BIGINT NOT NULL,
    file_name VARCHAR(255) NOT NULL,
    file_path VARCHAR(512) NOT NULL,
    file_size BIGINT NOT NULL DEFAULT 0,
    page_count INT NOT NULL DEFAULT 0,
    status VARCHAR(32) NOT NULL DEFAULT 'active',
    created_at DATETIME NOT NULL,
    updated_at DATETIME NOT NULL,
    KEY idx_user_created (user_id, created_at),
    CONSTRAINT fk_user_file_user
        FOREIGN KEY (user_id) REFERENCES user(id)
        ON DELETE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

8. 实施顺序建议

第一批必须落地

user 字段补充
plan
user_plan
usage_daily
tts_request_log
audio_cache_index

第二批建议落地

async_task
admin_audit_log
usage_monthly

第三批按商业进度落地

payment_order
user_file
user_session_log

9. 数据库迁移建议

建议不要一次重构全部表，而是用迁移脚本逐步上线。

推荐步骤：

新增新表，不动旧逻辑
在核心接口中逐步开始写入新表
后台逐步读取新表
稳定后再清理旧逻辑或补齐回填脚本

总结

数据库设计的核心目标不是“复杂”，而是为配额、统计、缓存、后台和收费预留结构。对你当前项目来说，最优先的是套餐、用量、TTS 请求日志和缓存索引，这四块直接决定系统是否可运营。

技术侧-数据库表设计.md 11 KB Előzmények Nyers