xiaoquan/rent

Fork 0

Files

T

xiaoquan d1147713f8 feat: 迭代

2026-05-26 21:27:48 +08:00

21 KiB

Raw Permalink Blame History

财务系统数据库设计文档

最后更新: 2024-01-XX - 账户体系重构完成
版本: v2.0

概述

本文档描述了租房平台财务系统的数据库设计，包括账户体系、资金流转和数据一致性保障机制。

数据库表与 Entity 映射关系

数据库表	Entity 文件	说明	状态
`system_accounts`	`system-account.entity.ts`	系统总账户表	✅ v2.0 新增
`system_transactions`	`system-transaction.entity.ts`	系统总账户交易流水表	✅ v2.0 新增
`platform_accounts`	`platform-account.entity.ts`	平台账户表	✅ v2.0 重构
`platform_transactions`	`platform-transaction.entity.ts`	平台交易流水表	✅ 已完成
`merchant_accounts`	`merchant-account.entity.ts`	商家账户表	✅ 已完成
`merchant_transactions`	`merchant-transaction.entity.ts`	商家交易流水表	✅ 已完成
`user_accounts`	`user-account.entity.ts`	用户账户表	✅ 已完成
`user_transactions`	`user-transaction.entity.ts`	用户交易流水表	✅ 已完成
`user_withdrawals`	`user-withdrawal.entity.ts`	用户提现表	✅ 已完成
`merchant_withdrawals`	`merchant-withdrawal.entity.ts`	商家提现表	✅ 已完成
`settlements`	`settlement.entity.ts`	结算单表	✅ 已完成
`settlement_items`	`settlement-item.entity.ts`	结算明细表	✅ 已完成

账户体系设计

四层账户结构

系统采用四层账户结构，确保资金流转清晰、职责明确：

1. 系统总账户 (System Account) - v2.0 新增

职责: 记录平台内所有未提现的资金总额

计算公式:

系统总账户余额 = 用户实付总额 - 累计退款 - 累计提现

字段说明:

balance: 当前余额
total_income: 累计收入（用户实付总额）
total_refund: 累计退款
total_withdrawn: 累计提现

2. 平台账户 (Platform Account) - v2.0 重构

职责: 记录平台净收益（服务费收入 - 邀请返现支出）

计算公式:

平台账户余额 = 累计服务费收入 - 累计邀请返现支出

字段说明:

balance: 当前余额
total_income: 累计收入（服务费收入）
total_expense: 累计支出（邀请返现支出）

v2.0 变更:

❌ 删除字段: total_order_income, total_service_fee, total_cashback, total_settlement
✅ 字段含义变更: total_income 从"订单收入"改为"服务费收入"
✅ 字段含义变更: total_expense 从"所有支出"改为"邀请返现支出"

3. 商家账户 (Merchant Account)

职责: 记录商家的订单收入和提现

计算公式:

商家账户余额 = 累计订单收入 - 累计提现
订单收入 = 订单金额 - 服务费

4. 用户账户 (User Account)

职责: 记录用户的邀请返现收入和提现

计算公式:

用户账户余额 = 累计邀请返现 - 累计提现

资金守恒验证

系统通过以下公式验证资金守恒：

系统总账户余额 = 商家账户余额总和 + 用户账户余额总和 + 平台账户余额

这个公式确保平台内所有资金都有明确的归属，不会出现资金丢失或凭空产生的情况。

数据表结构详解

system_accounts（系统总账户表）- v2.0 新增

CREATE TABLE IF NOT EXISTS `system_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_name` VARCHAR(50) NOT NULL COMMENT '账户名称',
  `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '可用余额',
  `total_income` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '累计收入（用户实付总额）',
  `total_refund` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '累计退款',
  `total_withdrawn` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '累计提现',
  `version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_account_name` (`account_name`)
) COMMENT='系统总账户表';

关键点:

记录平台内所有未提现的资金
balance = total_income - total_refund - total_withdrawn
用于验证资金守恒

system_transactions（系统总账户交易流水表）- v2.0 新增

CREATE TABLE IF NOT EXISTS `system_transactions` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `transaction_no` VARCHAR(64) NOT NULL COMMENT '交易流水号',
  `account_id` BIGINT UNSIGNED NOT NULL COMMENT '账户ID',
  `direction` ENUM('income','expense') NOT NULL COMMENT '交易方向',
  `amount` DECIMAL(12,2) NOT NULL COMMENT '交易金额',
  `balance_before` DECIMAL(12,2) NOT NULL COMMENT '交易前余额',
  `balance_after` DECIMAL(12,2) NOT NULL COMMENT '交易后余额',
  `transaction_type` VARCHAR(50) NOT NULL COMMENT '交易类型',
  `business_type` VARCHAR(50) NOT NULL COMMENT '业务类型',
  `business_id` BIGINT UNSIGNED NOT NULL COMMENT '业务ID',
  `business_no` VARCHAR(64) NOT NULL COMMENT '业务单号',
  `remark` VARCHAR(500) DEFAULT NULL COMMENT '备注',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_transaction_no` (`transaction_no`),
  KEY `idx_account_id` (`account_id`),
  KEY `idx_business` (`business_type`, `business_id`),
  KEY `idx_created_at` (`created_at`)
) COMMENT='系统总账户交易流水表';

交易类型:

用户支付: 用户支付订单（income）
订单退款: 订单退款（expense）
用户提现: 用户提现（expense）
商家提现: 商家提现（expense）

platform_accounts（平台账户表）- v2.0 重构

CREATE TABLE IF NOT EXISTS `platform_accounts` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
  `account_name` VARCHAR(50) NOT NULL COMMENT '账户名称',
  `balance` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '可用余额',
  `total_income` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '累计收入（服务费收入）',
  `total_expense` DECIMAL(12,2) NOT NULL DEFAULT 0.00 COMMENT '累计支出（邀请返现支出）',
  `version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_account_name` (`account_name`)
) COMMENT='平台账户表';

v2.0 变更:

total_income 含义变更: 从"订单收入"改为"服务费收入"
total_expense 含义变更: 从"所有支出"改为"邀请返现支出"
删除冗余字段: total_order_income, total_service_fee, total_cashback, total_settlement

user_accounts（用户账户表）

CREATE TABLE `user_accounts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT,
  `user_id` BIGINT UNSIGNED,                          -- 用户ID
  `balance` DECIMAL(12,2) DEFAULT 0.00,               -- 可用余额
  `frozen_balance` DECIMAL(12,2) DEFAULT 0.00,        -- 冻结余额（提现中）
  `total_income` DECIMAL(12,2) DEFAULT 0.00,          -- 累计收入（邀请返现）
  `total_expense` DECIMAL(12,2) DEFAULT 0.00,         -- 累计支出（提现）
  `version` INT UNSIGNED DEFAULT 0,                   -- 乐观锁版本号
  `status` ENUM('active','frozen','closed'),          -- 状态
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_user_id` (`user_id`)
);

关键点:

用户账户主要用于邀请返现
balance = total_income - total_expense
提现时先冻结余额，打款后扣减

merchant_accounts（商家账户表）

CREATE TABLE `merchant_accounts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT,
  `merchant_id` BIGINT UNSIGNED,                      -- 商家ID
  `balance` DECIMAL(12,2) DEFAULT 0.00,               -- 可用余额
  `frozen_balance` DECIMAL(12,2) DEFAULT 0.00,        -- 冻结余额（提现中）
  `debt_amount` DECIMAL(12,2) DEFAULT 0.00,           -- 欠款金额（退款扣回）
  `total_income` DECIMAL(12,2) DEFAULT 0.00,          -- 累计收入（订单结算）
  `total_expense` DECIMAL(12,2) DEFAULT 0.00,         -- 累计支出（提现+退款扣回）
  `total_settlement` DECIMAL(12,2) DEFAULT 0.00,      -- 累计结算金额
  `total_withdraw` DECIMAL(12,2) DEFAULT 0.00,        -- 累计提现金额
  `last_settlement_at` DATETIME,                      -- 最后结算时间
  `pending_settlement` DECIMAL(12,2) DEFAULT 0.00,    -- 待结算金额
  `version` INT UNSIGNED DEFAULT 0,                   -- 乐观锁版本号
  `status` ENUM('active','frozen','closed'),          -- 状态
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_merchant_id` (`merchant_id`)
);

关键点:

商家账户记录订单结算收入
结算金额 = 订单金额 - 服务费
每周一凌晨2点自动执行周结算

资金流转流程

1. 订单支付流程 - v2.0 更新

用户支付订单
  ↓
系统总账户 +实付金额 (income)
  ↓
订单状态：pending_pay → pending_confirm

涉及表:

system_accounts: balance += 实付金额, total_income += 实付金额
system_transactions: 插入交易流水（income）

代码位置: apps/server/src/modules/app/order/order.service.ts

2. 订单结算流程 - v2.0 更新

订单完成（用户离店）
  ↓
商家账户 +（实付金额 - 服务费）
  ↓
平台账户 +服务费
  ↓
如果有邀请关系：
  用户账户（邀请人）+返现金额
  平台账户 -返现金额

涉及表:

merchant_accounts: balance += 结算金额
merchant_transactions: 插入交易流水
platform_accounts: balance += 服务费, total_income += 服务费
platform_transactions: 插入交易流水
user_accounts: balance += 返现金额（如有邀请）
user_transactions: 插入交易流水（如有邀请）
platform_accounts: balance -= 返现金额, total_expense += 返现金额（如有邀请）
settlements: 插入结算单记录
settlement_items: 插入结算明细

代码位置:

apps/server/src/modules/shared/finance/settlement.service.ts
apps/server/src/modules/app/activity/activity.service.ts

3. 订单退款流程 - v2.0 更新

用户申请退款
  ↓
调用第三方支付退款API
  ↓
系统总账户 +退款金额 (expense)
  ↓
订单状态：pending_confirm/pending_checkin → refunded

涉及表:

system_accounts: balance -= 退款金额, total_refund += 退款金额
system_transactions: 插入交易流水（expense）

代码位置: apps/server/src/modules/shared/finance/refund.service.ts

4. 提现流程 - v2.0 更新

用户提现

用户申请提现
  ↓
用户账户：balance → frozen_balance
  ↓
管理员审核通过
  ↓
管理员确认打款
  ↓
用户账户 -提现金额（从frozen_balance扣减）
系统总账户 +提现金额 (expense)
  ↓
提现状态：pending → approved → paid

商家提现

商家申请提现
  ↓
商家账户：balance → frozen_balance
  ↓
管理员审核通过
  ↓
管理员确认打款
  ↓
商家账户 -提现金额（从frozen_balance扣减）
系统总账户 +提现金额 (expense)
  ↓
提现状态：pending → approved → paid

涉及表:

user_accounts / merchant_accounts: frozen_balance → balance扣减
user_transactions / merchant_transactions: 插入交易流水
system_accounts: balance -= 提现金额, total_withdrawn += 提现金额
system_transactions: 插入交易流水
user_withdrawals / merchant_withdrawals: 更新提现状态

代码位置: apps/server/src/modules/shared/finance/withdrawal.service.ts

platform_accounts（平台账户表）

CREATE TABLE `platform_accounts` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT,
  `account_name` VARCHAR(50),                         -- 账户名称
  `balance` DECIMAL(12,2) DEFAULT 0.00,               -- 可用余额
  `frozen_balance` DECIMAL(12,2) DEFAULT 0.00,        -- 冻结余额
  `total_income` DECIMAL(12,2) DEFAULT 0.00,          -- 累计收入（订单收入）
  `total_expense` DECIMAL(12,2) DEFAULT 0.00,         -- 累计支出（商家结算+退款）
  `total_service_fee` DECIMAL(12,2) DEFAULT 0.00,     -- 累计服务费收入
  `version` INT UNSIGNED DEFAULT 0,                   -- 乐观锁版本号
  `status` ENUM('active','frozen','closed'),          -- 状态
  PRIMARY KEY (`id`)
);

关键点:

账户按角色分离：用户、商家、平台各有独立的账户表
每个账户表都有 version 字段用于乐观锁，防止并发问题
balance 和 frozen_balance 有 CHECK 约束，不能为负数

user_transactions / merchant_transactions / platform_transactions（交易流水表）

交易流水表也按角色分离，结构类似：

CREATE TABLE `user_transactions` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT,
  `transaction_no` VARCHAR(32) UNIQUE,                -- 交易流水号（全局唯一）
  `user_id` BIGINT UNSIGNED,                          -- 用户ID
  `account_id` BIGINT UNSIGNED,                       -- 用户账户ID
  `direction` ENUM('income','expense'),               -- 方向
  `amount` DECIMAL(12,2),                             -- 金额
  `balance_before` DECIMAL(12,2),                     -- 交易前余额
  `balance_after` DECIMAL(12,2),                      -- 交易后余额
  `transaction_type` VARCHAR(50),                     -- 交易类型
  `business_type` VARCHAR(50),                        -- 业务类型
  `business_id` BIGINT UNSIGNED,                      -- 业务ID
  `business_no` VARCHAR(32),                          -- 业务单号
  `related_account_type` ENUM('platform','merchant'), -- 对方账户类型
  `related_account_id` BIGINT UNSIGNED,               -- 对方账户ID
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_transaction_no` (`transaction_no`)
);

关键点:

所有账户操作都记录详细的交易流水
transaction_no 全局唯一，用于追踪资金流向
记录交易前后余额，便于对账和审计

settlements（结算单表）

CREATE TABLE `settlements` (
  `id` BIGINT UNSIGNED AUTO_INCREMENT,
  `settlement_no` VARCHAR(32) UNIQUE,                 -- 结算单号
  `merchant_id` BIGINT UNSIGNED,                      -- 商家ID
  `period_start` DATE,                                -- 周期开始
  `period_end` DATE,                                  -- 周期结束
  `order_count` INT UNSIGNED DEFAULT 0,               -- 订单数量
  `order_amount` DECIMAL(12,2) DEFAULT 0.00,          -- 订单总额
  `service_fee` DECIMAL(12,2) DEFAULT 0.00,           -- 服务费
  `settlement_amount` DECIMAL(12,2) DEFAULT 0.00,     -- 结算金额
  `status` ENUM('pending','settled','failed'),        -- 状态
  `settled_at` DATETIME,                              -- 结算时间
  PRIMARY KEY (`id`)
);

关键点:

每周一凌晨2点自动生成上周的结算单
settlement_amount = order_amount - service_fee
结算时执行转账：给商家账户增加余额，给平台账户增加服务费

数据一致性保证

1. 事务保证

所有涉及金额变动的操作都在事务中执行：

const queryRunner = this.dataSource.createQueryRunner();
await queryRunner.connect();
await queryRunner.startTransaction();

try {
  // 账户操作
  await queryRunner.commitTransaction();
} catch (error) {
  await queryRunner.rollbackTransaction();
  throw error;
} finally {
  await queryRunner.release();
}

2. 悲观锁

在查询账户时使用悲观锁，防止并发修改：

const account = await queryRunner.manager.findOne(SystemAccount, {
  where: { account_name: 'SYSTEM_MAIN' },
  lock: { mode: 'pessimistic_write' },
});

3. 乐观锁

使用 version 字段实现乐观锁，检测并发冲突：

account.version += 1;
await queryRunner.manager.save(account);

4. 金额精度控制

所有金额计算都使用 Number() 转换和 toFixed(2) 保留两位小数：

const amountNum = Number(amount);
const balanceBefore = Number(account.balance);
const balanceAfter = parseFloat((balanceBefore + amountNum).toFixed(2));

5. 余额校验

在扣减余额前检查余额是否足够：

if (balanceBefore < amount) {
  throw new BadRequestException('账户余额不足');
}

6. CHECK 约束

CONSTRAINT `chk_balance` CHECK (`balance` >= 0)
CONSTRAINT `chk_frozen_balance` CHECK (`frozen_balance` >= 0)

确保余额不会为负数。

7. 触发器

用户注册时自动创建账户
商家审核通过时自动创建账户

数据库初始化

1. 迁移脚本位置

database/migrations/001_init_schema.sql 包含完整的财务系统表结构

2. 初始化步骤

# 1. 执行数据库迁移脚本（会清空所有数据）
mysql -u root -p < database/migrations/001_init_schema.sql

# 2. 初始化系统总账户
mysql -u root -p
USE your_database_name;
INSERT INTO system_accounts (account_name, balance, total_income, total_refund, total_withdrawn, status)
VALUES ('SYSTEM_MAIN', 0.00, 0.00, 0.00, 0.00, 'active');

# 3. 初始化平台账户
INSERT INTO platform_accounts (account_name, balance, frozen_balance, total_income, total_expense, status)
VALUES ('PLATFORM_MAIN', 0.00, 0.00, 0.00, 0.00, 'active');

3. 自动创建的数据

执行迁移脚本后会自动创建：

触发器
- trg_user_create_account: 用户注册时自动创建用户账户
- trg_merchant_create_account: 商家审核通过时自动创建商家账户

索引说明

关键索引

system_accounts 表
- uk_account_name: 唯一索引，确保账户名称唯一
system_transactions 表
- uk_transaction_no: 唯一索引，防止重复交易
- idx_account_id: 按账户查询流水
- idx_business: 按业务类型和业务ID查询
- idx_created_at: 按时间范围查询
platform_accounts 表
- uk_account_name: 唯一索引，确保账户名称唯一
user_accounts / merchant_accounts 表
- uk_user_id / uk_merchant_id: 唯一索引，确保每个用户/商家只有一个账户
- idx_status: 按状态查询
transactions 表
- uk_transaction_no: 唯一索引，防止重复交易
- idx_account_id: 按账户查询流水
- idx_business: 按业务类型和业务ID查询
- idx_created_at: 按时间范围查询
settlements 表
- uk_settlement_no: 唯一索引
- idx_merchant_id: 按商家查询
- idx_period: 按周期查询
withdrawals 表
- uk_withdraw_no: 唯一索引
- idx_status: 按状态查询（待审核、已打款等）

版本更新记录

v2.0 (2024-01-XX) - 账户体系重构

重大变更:

✅ 新增 system_accounts 表 - 记录系统总资金
✅ 新增 system_transactions 表 - 系统总账户交易流水
✅ 重构 platform_accounts 表 - 只记录平台净收益
- 字段含义变更: total_income 改为"服务费收入"
- 字段含义变更: total_expense 改为"邀请返现支出"
- 删除冗余字段: total_order_income, total_service_fee, total_cashback, total_settlement
✅ 修改订单支付逻辑 - 更新系统总账户
✅ 修改订单结算逻辑 - 同时更新商家账户和平台账户
✅ 修改邀请返现逻辑 - 从平台账户扣减
✅ 修改退款逻辑 - 更新系统总账户
✅ 修改提现逻辑 - 更新系统总账户

资金守恒验证:

系统总账户余额 = 商家账户余额总和 + 用户账户余额总和 + 平台账户余额

升级步骤:

备份现有数据库
执行新的数据库迁移脚本
初始化系统总账户和平台账户
更新后端代码
重启服务
验证资金守恒

v1.0 (2024-04-24) - 初始版本

功能:

用户账户、商家账户、平台账户
交易流水记录
周结算功能
提现功能
日对账功能

注意事项

执行迁移脚本: 确保按顺序执行 001_init_schema.sql，会自动创建所有表和触发器。
初始化账户: 迁移脚本执行后需要手动初始化系统总账户和平台账户。
定时任务: 需要在 NestJS 中启用 @nestjs/schedule 模块，定时任务才会执行。
资金安全: 所有账户操作都使用事务、悲观锁和乐观锁保证数据一致性。
金额精度: 所有金额计算使用 Number() 和 toFixed(2) 确保精度。

维护团队: 开发团队
最后更新: 2024-01-XX

21 KiB Raw Permalink Blame History Unescape Escape

财务系统数据库设计文档

概述

数据库表与 Entity 映射关系

账户体系设计

四层账户结构

1. 系统总账户 (System Account) - v2.0 新增

2. 平台账户 (Platform Account) - v2.0 重构

3. 商家账户 (Merchant Account)

4. 用户账户 (User Account)

资金守恒验证

数据表结构详解

system_accounts（系统总账户表）- v2.0 新增

system_transactions（系统总账户交易流水表）- v2.0 新增

platform_accounts（平台账户表）- v2.0 重构

user_accounts（用户账户表）

merchant_accounts（商家账户表）

资金流转流程

1. 订单支付流程 - v2.0 更新

2. 订单结算流程 - v2.0 更新

3. 订单退款流程 - v2.0 更新

4. 提现流程 - v2.0 更新

用户提现

商家提现

platform_accounts（平台账户表）

user_transactions / merchant_transactions / platform_transactions（交易流水表）

settlements（结算单表）

数据一致性保证

1. 事务保证

2. 悲观锁

3. 乐观锁

4. 金额精度控制

5. 余额校验

6. CHECK 约束

7. 触发器

数据库初始化

1. 迁移脚本位置

2. 初始化步骤

3. 自动创建的数据

索引说明

关键索引

版本更新记录

v2.0 (2024-01-XX) - 账户体系重构

v1.0 (2024-04-24) - 初始版本

相关文档

注意事项

21 KiB

Raw Permalink Blame History