# 技术侧数据库表设计

## 目标

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

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

当前项目已有：

- `user`
- `user_progress`
- `user_config`

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

## 1. 用户与认证

### 1.1 user

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

建议字段：

```sql
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

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

```sql
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

定义平台套餐。

```sql
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

记录用户当前套餐。

```sql
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

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

```sql
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

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

```sql
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

最关键的业务日志表。

```sql
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

记录长文本与后台任务。

```sql
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

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

```sql
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

记录重要管理操作。

```sql
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

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

```sql
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

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

```sql
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. 数据库迁移建议

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

推荐步骤：

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

## 总结

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