227 lines
9.6 KiB
Markdown
227 lines
9.6 KiB
Markdown
# 数据库设计文档（订单 / 支付 / 邀请模块）
|
||
|
||
## 一、订单模块
|
||
|
||
### 1.1 orders 表 - 订单主表
|
||
|
||
```sql
|
||
CREATE TABLE orders (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
order_no VARCHAR(50) NOT NULL UNIQUE COMMENT '订单号(全局唯一)',
|
||
user_id BIGINT NOT NULL COMMENT '用户ID',
|
||
total_amount DECIMAL(10, 2) NOT NULL COMMENT '应付总金额(元)',
|
||
cash_amount DECIMAL(10, 2) DEFAULT 0.00 COMMENT '现金支付部分',
|
||
points_used INT DEFAULT 0 COMMENT '使用积分数',
|
||
points_deduct_amount DECIMAL(10, 2) DEFAULT 0.00 COMMENT '积分抵扣金额',
|
||
status ENUM('pending','paid','completed','cancelled','refunding','refunded') DEFAULT 'pending',
|
||
payment_method ENUM('wechat','alipay','points','mixed') COMMENT '支付方式',
|
||
remark VARCHAR(500) COMMENT '备注',
|
||
cancel_reason VARCHAR(255) COMMENT '取消原因',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
paid_at TIMESTAMP NULL COMMENT '支付时间',
|
||
expired_at TIMESTAMP NULL COMMENT '超时取消时间',
|
||
FOREIGN KEY (user_id) REFERENCES users(id),
|
||
INDEX idx_user_id (user_id),
|
||
INDEX idx_status (status),
|
||
INDEX idx_order_no (order_no),
|
||
INDEX idx_created_at (created_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单表';
|
||
```
|
||
|
||
**订单号生成规则**：`ORD + 年月日时分秒 + 6位序列号`，例如 `ORD20260316143022000001`
|
||
|
||
**状态流转**：
|
||
```
|
||
pending → paid → completed
|
||
pending → cancelled（超时/主动取消）
|
||
paid → refunding → refunded
|
||
```
|
||
|
||
### 1.2 order_items 表 - 订单项
|
||
|
||
```sql
|
||
CREATE TABLE order_items (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
order_id BIGINT NOT NULL COMMENT '订单ID',
|
||
skill_id BIGINT NOT NULL COMMENT 'SkillID',
|
||
skill_name VARCHAR(200) NOT NULL COMMENT 'Skill名称快照',
|
||
skill_cover VARCHAR(500) COMMENT 'Skill封面快照(腾讯云COS)',
|
||
unit_price DECIMAL(10, 2) NOT NULL COMMENT '下单时单价快照',
|
||
quantity INT DEFAULT 1,
|
||
total_price DECIMAL(10, 2) NOT NULL COMMENT '小计',
|
||
FOREIGN KEY (order_id) REFERENCES orders(id),
|
||
INDEX idx_order_id (order_id),
|
||
INDEX idx_skill_id (skill_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='订单项表';
|
||
```
|
||
|
||
### 1.3 order_refunds 表 - 退款记录
|
||
|
||
```sql
|
||
CREATE TABLE order_refunds (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
order_id BIGINT NOT NULL COMMENT '订单ID',
|
||
refund_no VARCHAR(50) NOT NULL UNIQUE COMMENT '退款单号',
|
||
refund_amount DECIMAL(10, 2) NOT NULL COMMENT '退款金额',
|
||
refund_points INT DEFAULT 0 COMMENT '退回积分',
|
||
reason VARCHAR(255) COMMENT '退款原因',
|
||
images JSON COMMENT '凭证图片(腾讯云COS URL数组)',
|
||
status ENUM('pending','approved','rejected','completed') DEFAULT 'pending',
|
||
reject_reason VARCHAR(255) COMMENT '拒绝原因',
|
||
operator_id BIGINT COMMENT '处理人(管理员ID)',
|
||
processed_at TIMESTAMP NULL COMMENT '处理时间',
|
||
remark VARCHAR(255) COMMENT '处理备注',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
completed_at TIMESTAMP NULL COMMENT '退款完成时间',
|
||
FOREIGN KEY (order_id) REFERENCES orders(id),
|
||
INDEX idx_order_id (order_id),
|
||
INDEX idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='退款表';
|
||
```
|
||
|
||
> **变更说明**：原 `handled_by` 重命名为 `operator_id`；新增 `processed_at`（处理时间）和 `remark`（处理备注），与 `AdminServiceImpl.processRefund()` 对齐。
|
||
|
||
## 二、支付模块
|
||
|
||
### 2.1 recharge_orders 表 - 充值订单
|
||
|
||
```sql
|
||
CREATE TABLE recharge_orders (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
recharge_no VARCHAR(50) NOT NULL UNIQUE COMMENT '充值单号',
|
||
user_id BIGINT NOT NULL,
|
||
amount DECIMAL(10, 2) NOT NULL COMMENT '充值金额(元)',
|
||
bonus_points INT DEFAULT 0 COMMENT '赠送积分',
|
||
total_points INT NOT NULL COMMENT '到账总积分(按金额换算+赠送)',
|
||
payment_method ENUM('wechat','alipay') NOT NULL,
|
||
status ENUM('pending','paid','failed','cancelled') DEFAULT 'pending',
|
||
transaction_id VARCHAR(100) COMMENT '微信/支付宝交易流水号',
|
||
notify_data TEXT COMMENT '支付回调原始数据',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
paid_at TIMESTAMP NULL COMMENT '支付完成时间',
|
||
FOREIGN KEY (user_id) REFERENCES users(id),
|
||
INDEX idx_user_id (user_id),
|
||
INDEX idx_status (status),
|
||
INDEX idx_created_at (created_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='充值订单表';
|
||
```
|
||
|
||
**充值赠送规则**（在 `application.yml` 中配置）：
|
||
|
||
| 充值金额 | 赠送积分 | 到账总积分 |
|
||
|---------|---------|----------|
|
||
| ¥10 | 10 | 1010 |
|
||
| ¥50 | 60 | 5060 |
|
||
| ¥100 | 150 | 10150 |
|
||
| ¥500 | 800 | 50800 |
|
||
| ¥1000 | 2000 | 102000 |
|
||
|
||
> 到账总积分 = 充值金额 × 100（1元=100积分）+ 赠送积分
|
||
|
||
### 2.2 payment_records 表 - 支付流水
|
||
|
||
```sql
|
||
CREATE TABLE payment_records (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
user_id BIGINT NOT NULL,
|
||
biz_type ENUM('order','recharge') NOT NULL COMMENT '业务类型',
|
||
biz_id BIGINT NOT NULL COMMENT '业务ID(order_id 或 recharge_id)',
|
||
biz_no VARCHAR(50) NOT NULL COMMENT '业务单号',
|
||
amount DECIMAL(10, 2) NOT NULL COMMENT '支付金额',
|
||
payment_method ENUM('wechat','alipay','points') NOT NULL,
|
||
transaction_id VARCHAR(100) COMMENT '三方交易号',
|
||
status ENUM('pending','success','failed') DEFAULT 'pending',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id),
|
||
INDEX idx_user_id (user_id),
|
||
INDEX idx_biz_no (biz_no),
|
||
INDEX idx_transaction_id (transaction_id),
|
||
INDEX idx_created_at (created_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='支付流水表';
|
||
```
|
||
|
||
## 三、邀请模块
|
||
|
||
### 3.1 invite_codes 表 - 邀请码
|
||
|
||
```sql
|
||
CREATE TABLE invite_codes (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
user_id BIGINT NOT NULL COMMENT '邀请人ID',
|
||
code VARCHAR(50) NOT NULL UNIQUE COMMENT '邀请码(大写字母+数字)',
|
||
invite_url VARCHAR(500) COMMENT '邀请链接',
|
||
is_active BOOLEAN DEFAULT TRUE COMMENT '是否启用',
|
||
use_count INT DEFAULT 0 COMMENT '已使用次数',
|
||
max_use_count INT DEFAULT -1 COMMENT '最大使用次数(-1为不限)',
|
||
expired_at TIMESTAMP NULL COMMENT '过期时间(NULL为永不过期)',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (user_id) REFERENCES users(id),
|
||
UNIQUE KEY uk_user_code (user_id),
|
||
INDEX idx_code (code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请码表';
|
||
```
|
||
|
||
> **变更说明**：原 `status ENUM('active','inactive')` 改为 `is_active BOOLEAN`，`used_count` 重命名为 `use_count`；新增 `max_use_count`（最大使用次数）、`expired_at`（过期时间）、`updated_at`，与 `InviteCode.java` 实体对齐。
|
||
|
||
### 3.2 invite_records 表 - 邀请记录
|
||
|
||
```sql
|
||
CREATE TABLE invite_records (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT,
|
||
inviter_id BIGINT NOT NULL COMMENT '邀请人ID',
|
||
invitee_id BIGINT NOT NULL COMMENT '被邀请人ID',
|
||
invite_code VARCHAR(50) COMMENT '使用的邀请码',
|
||
status ENUM('registered','first_paid') DEFAULT 'registered' COMMENT '状态',
|
||
inviter_reward_points INT DEFAULT 0 COMMENT '邀请人获得积分',
|
||
invitee_reward_points INT DEFAULT 0 COMMENT '被邀请人获得积分',
|
||
reward_given BOOLEAN DEFAULT FALSE COMMENT '奖励是否已发放',
|
||
rewarded_at TIMESTAMP NULL COMMENT '奖励发放时间',
|
||
created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
|
||
updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
|
||
FOREIGN KEY (inviter_id) REFERENCES users(id),
|
||
FOREIGN KEY (invitee_id) REFERENCES users(id),
|
||
UNIQUE KEY uk_invitee (invitee_id),
|
||
INDEX idx_inviter_id (inviter_id),
|
||
INDEX idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='邀请记录表';
|
||
```
|
||
|
||
> **变更说明**：字段 `inviter_reward_points` / `invitee_reward_points` 与 `InviteRecord.java` 实体对齐；新增 `rewarded_at` 字段。
|
||
|
||
## 四、Redis 缓存 Key 设计
|
||
|
||
| Key | 说明 | TTL |
|
||
|-----|------|-----|
|
||
| `user:info:{userId}` | 用户基本信息缓存 | 30分钟 |
|
||
| `user:token:{token}` | JWT Token黑名单(登出时写入) | 与token同期 |
|
||
| `user:points:{userId}` | 用户积分余额缓存 | 5分钟 |
|
||
| `skill:detail:{skillId}` | Skill详情缓存 | 10分钟 |
|
||
| `skill:hot:list` | 热门Skill列表 | 1小时 |
|
||
| `skill:category:list` | 分类列表 | 24小时 |
|
||
| `sign_in:lock:{userId}:{date}` | 签到幂等锁 | 24小时 |
|
||
| `invite:code:{code}` | 邀请码验证缓存 | 1小时 |
|
||
| `order:lock:{orderNo}` | 订单支付分布式锁 | 30秒 |
|
||
| `captcha:sms:{phone}` | 短信验证码 | 5分钟 |
|
||
|
||
## 五、数据库初始化 SQL 执行顺序
|
||
|
||
```
|
||
1. V1__init_users.sql -- users, user_profiles, user_auth
|
||
2. V2__init_skills.sql -- skill_categories, skills, skill_reviews, skill_downloads
|
||
3. V3__init_points.sql -- user_points, points_records, points_rules
|
||
4. V4__init_orders.sql -- orders, order_items, order_refunds
|
||
5. V5__init_payments.sql -- recharge_orders, payment_records
|
||
6. V6__init_invites.sql -- invite_codes, invite_records
|
||
```
|
||
|
||
---
|
||
|
||
**文档版本**：v1.1
|
||
**创建日期**：2026-03-16
|
||
**最后更新**：2026-03-16（修复字段缺失问题）
|