用户钱包(账户)设计

前言

 在涉及关于金钱的业务系统时,一定要注意数据安全。通常可以把财务相关的数据库,独立于其他的业务系统数据库。从表的设计角度来看。应该纪录每一步资金的流向,方便数据追踪下面来开始设计数据表。

1,表设计

    这里主要用到的表有。用户钱包表,流水纪录表,充值订单表,支付纪录表,提现表。
DROP TABLE IF EXISTS `user_wallet`;
CREATE TABLE `user_wallet` (
  `user_uuid` char(32)  NOT NULL COMMENT '用户user_uuid',
  `wallet_income` decimal(10,2) unsigned DEFAULT '0.00' COMMENT '钱包总收入额',
  `wallet_outcome` decimal(10,2) unsigned DEFAULT '0.00' COMMENT '钱包总支出额',
  `balance_fee` decimal(10,2) unsigned DEFAULT '0.00' COMMENT '钱包总可用余额',
  `check_sign`  varchar(100) DEFAULT '' COMMENT '用于安全检查,检查不通过为异常。',
  `update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`user_uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户钱包';
    上面的钱包表中,check_sign字段,是有效保证数据安全,不被轻易篡改的方法。sign字段的值是当前一条纪录的数据加密计算值。在每次对这张表进行操作时。都要进行check_sign字段签名验证。通过则继续。不通过,则可以根据具体情况采取措施。比如发送通知到相关人员。

DROP TABLE IF EXISTS `user_wallet_log`;
CREATE TABLE `user_wallet_log` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto id',
  `user_uuid` char(32) DEFAULT '' COMMENT '用uuid',
  `number` varchar(32) NOT NULL DEFAULT '' COMMENT '流水号',
  `target_type` smallint(5) unsigned DEFAULT '0' COMMENT '业务类型,1:充值,2:提现  3:下单',
  `target_uuid` char(32) DEFAULT '' COMMENT '来源uuid(如提现uuid)',
  `action_type` smallint(5) unsigned DEFAULT '0' COMMENT '操作类型,1:充值,2:提现,3:订单',
  `fee` decimal(10,2) DEFAULT '0.00' COMMENT '变动的金额,正负数。',
  `original_account_json`  varchar(1000) DEFAULT '账户变更前的数据 json存储',
 `dispose_account_json`  varchar(1000) DEFAULT '账户变更后的数据 json存储',
  `status` smallint(5) unsigned DEFAULT '0' COMMENT '处理状态1,处理完成,0未完成',
  `result_type`  smallint(5) unsigned DEFAULT '0' COMMENT '处理结果,0:没有变更,1:有变更。',
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户钱包流水记录表';
    流水纪录表中的original_account_json和dispose_account_json字段,可以用来排查账户异常,方便梳理对帐。
DROP TABLE IF EXISTS `user_top_up_order`;
CREATE TABLE `user_top_up_order` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto id',
  `user_uuid` char(32) NOT NULL DEFAULT '' COMMENT '用户ID',
  `order_num` char(32) NOT NULL COMMENT '订单号',
  `amounts` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '订单总金额',
  `currency` varchar(8) NOT NULL DEFAULT 'CNY' COMMENT '货币类型',
  `pay_fee` decimal(10,2) NOT NULL DEFAULT '0.00' COMMENT '实际支付金额',
  `exchange` decimal(10,4) NOT NULL DEFAULT '0.00' COMMENT '汇率',
  `status` tinyint(5) NOT NULL DEFAULT '0' COMMENT '支付状态:0待支付,1已支付,100已取消',
  `update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `deleted` tinyint(5) NOT NULL DEFAULT '0' COMMENT '0未删除1已删除',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='充值订单';
    充值订单没什么好说的
DROP TABLE IF EXISTS `user_withdraw_cash_list`;
CREATE TABLE `user_withdraw_cash_list` (
 `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto id',
  `user_uuid` varchar(32) NOT NULL COMMENT '申请用户uuid',
  `withdraw_way` tinyint(1) NOT NULL DEFAULT '1' COMMENT '提现(渠道)方式 1银行转账',
  `withdraw_status` smallint(5) unsigned DEFAULT '0' COMMENT '处理状态。 1发起申请(待审核理)前台显示处理中,2提现成功,3审核不通过',
  `number` char(32) DEFAULT '' COMMENT '提现单号',
  `receivable_account` varchar(32) DEFAULT '' COMMENT '收款账户',
  `name` varchar(30) DEFAULT '' COMMENT '收款人姓名',
  `address` varchar(100) DEFAULT '' COMMENT '开户行地址',
  `withdraw_fee` decimal(10,2) DEFAULT '0.00' COMMENT '提现金额',
  `content` varchar(500) DEFAULT '' COMMENT '审核不通过原因',
  `verify_user` varchar(32) DEFAULT '' COMMENT '审核人',
  `action_user` varchar(32) DEFAULT '' COMMENT '操作人',
  `action_at` datetime DEFAULT NULL COMMENT '审核时间',
  `sent_notice_at` datetime DEFAULT NULL COMMENT '发送通知时间',
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `deleted` tinyint(1) NOT NULL DEFAULT '0' COMMENT '是否删除:0未删除,1已删除',
  PRIMARY KEY (`uuid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='提现记录表';
    一般来说,有充值,就有提现。很多业务系统中没有充值,也会有提现场景。最后一张就是支付纪录表了。
DROP TABLE IF EXISTS `payment_list`;
CREATE TABLE `payment_list` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT COMMENT 'auto id',
  `user_uuid` varchar(32) NOT NULL DEFAULT '' COMMENT '用户uuid',
  `pay_num` bigint(20) unsigned DEFAULT '0' COMMENT '支付号',
  `trade_no` varchar(64) COLLATE utf8mb4_general_ci NOT NULL DEFAULT '' COMMENT '第三方付款成功交易号',
  `order_type` smallint(5) unsigned DEFAULT '0' COMMENT '订单类型 1充值 ',
  `order_uuid` char(32) DEFAULT '' COMMENT '充值订单表uuid',
  `pay_way` smallint(5) unsigned DEFAULT '0' COMMENT '付款方式 1微信 2支付宝 ',
  `pay_status` smallint(5) unsigned DEFAULT '0' COMMENT '支付状态 1 支付成功,2 支付失败 ',
  `step` smallint(5) unsigned DEFAULT '0' COMMENT '步骤 1 创建支付,2 支付回调通知',
  `post_status` smallint(5) unsigned DEFAULT '0' COMMENT '请求支付状态,1:成功,2:失败',
  `return_status` smallint(5) unsigned DEFAULT '0' COMMENT '回调状态,1:成功,2:失败',
  `total_fee` decimal(10,2) unsigned DEFAULT '0.00' COMMENT '支付价格',
  `result_code`  varchar(30) DEFAULT '' COMMENT '第三方返回错误码',
  `err_code_str`  varchar(255) DEFAULT '' COMMENT '第三方返回的错误记录',
  `currency` varchar(8) NOT NULL DEFAULT 'HKD' COMMENT '货币类型',
  `ip2long` int(10) unsigned DEFAULT '0' COMMENT 'ip2long',
  `post_json` varchar(2000) DEFAULT '' COMMENT '提交post json 数据',
  `return_json` varchar(2000) DEFAULT '' COMMENT '回调post return data json',
  `create_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `update_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  `success_at` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '支付成功时间',
`procedure_kb mediumint(8) unsigned DEFAULT '0' COMMENT '手续费比例,千位比。6/1000',

`procedure_fee`  decimal(10,2) unsigned DEFAULT '0.00' COMMENT '支付渠道收取手续费金额',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付记录列表';
以上,用户钱包功能所需表就设计好了。之前一篇提到的支付纪录表也面世了。因为很多公司并没有这张表,或者说只有一张简单的表纪录第三方交易单号的信息和订单信息。而且后台管理系统没有页面展示。导致客服运营等人员,经常反馈支付失败的问题。很多时候只是网络延时导致订单回调不及时,而没有修改订单状态。而我们开发则不得不停下手中的活,去排查相关的问题。

2 简要流程

这里就不画流程图和时序图了。简单说下步骤
1.何时创建用户钱包纪录?。新用户在注册时可创建。老用户写脚本创建。
2.创建用户钱包纪录根据用的钱包纪录信息生产一个sign。并同时创建一条流水纪录。
3.操作钱包表时,要先获取一条纪录。生产一个sign值,然后根据纪录的sign值进行对比。相同则继续走正常的步骤。不同时,则应该终止后续操作,并发通知给相关人员进行排查异常。
4.重新生成sign值,并正常更新数据,纪录流水纪录。
5.支付纪录表,应该是每发起一次支付,就要生成一条支付纪录。同一个订单对应多个支付纪录。

3,check_sign字段生成

  /**
     * @param $walletInfo
     */
    public function updateSign($walletInfo)
    {
        unset($walletInfo['check_sign']);
        unset($walletInfo['update_at']);
        $walletCheckKey = config('bill.walletCheckKey');
        $checkSign = $this->encode_HMAC($walletInfo, $walletCheckKey);

        $update['check_sign'] = $checkSign;
        $this->walletRepository->saveTransaction($walletInfo['id'], $update);
    }

    /**
     * 检查sign
     *
     * @param $walletInfo
     *
     * @return bool
     */
    public function walletSignCheck($walletInfo)
    {
        $walletCheckKey = config('bill.walletCheckKey');
        $check = false;
        $checkSignDB = $walletInfo['check_sign'];

        unset($walletInfo['check_sign']);
        unset($walletInfo['update_at']);

        $checkSign = $this->encode_HMAC($walletInfo, $walletCheckKey);
        if ($checkSignDB == $checkSign) {
            $check = true;
        }
        return $check;
    }

    /** * sign生成
    * * @param $walletInfo
    * * @return bool 
    */
    protected function encode_HMAC($data, $walletCheckKey)
    {
        if (empty($data) || !is_array($data)) {
            _writeBizException(json_encode([$data]), __FUNCTION__, class_basename(__CLASS__));
        }
        ksort($data);
        $str = "";
        foreach ($data as $k => $v) {
            $str = $str . "&" . $k . "=" . $v;
        }
        $str = trim($str, "&");
        $str = $str . "&key=" . $walletCheckKey;
        $re = strtoupper(hash_hmac("sha256", $str, $walletCheckKey));
        return $re;
    }
本作品采用《CC 协议》,转载必须注明作者和本文链接
本帖由系统于 4个月前 自动加精
《L03 构架 API 服务器》
你将学到如 RESTFul 设计风格、PostMan 的使用、OAuth 流程,JWT 概念及使用 和 API 开发相关的进阶知识。
《L04 微信小程序从零到发布》
从小程序个人账户申请开始,带你一步步进行开发一个微信小程序,直到提交微信控制台上线发布。
讨论数量: 19
bing8u

就希望多几个这样把简单事情复杂化的人。

这样才能突出我们做应用的速度是多么快捷。

4个月前 评论
rc1021 4个月前
qufo 4个月前
rc1021 4个月前
稻草人AQA 1个月前
朕略显ぼうっと萌 1个月前
稻草人AQA 1个月前
稻草人AQA 1个月前
稻草人AQA 1个月前
bing8u

就希望多几个这样把简单事情复杂化的人。

这样才能突出我们做应用的速度是多么快捷。

4个月前 评论
rc1021 4个月前
qufo 4个月前
rc1021 4个月前
稻草人AQA 1个月前
朕略显ぼうっと萌 1个月前
稻草人AQA 1个月前
稻草人AQA 1个月前
稻草人AQA 1个月前

体会不到 check_sign的作用是啥

大胆猜测一下是每次更新 balance_fee 后重新计算并且更新这个 check_sign

那么约等于余额的修改都需要队列并不支持并发么..

4个月前 评论
Euan (楼主) 4个月前
KingOfTheWorld 3个月前
Euan (楼主) 3个月前
KingOfTheWorld 3个月前
liaosp 3个月前
kingjian 2个月前
xujinhuan 2个月前
稻草人AQA 1个月前

不错 sign的签名鉴定很好的思路

4个月前 评论

学习到了!赞!

3个月前 评论
Marrigan

赞!

3个月前 评论

看到这种干货文章,反手就是一个赞 :kiss:

3个月前 评论

wallet_income、wallet_outcome只是单纯的用来统计吗

2个月前 评论

金额为什么不是最小单位,应该是分(整数)

2个月前 评论
Marrigan 2个月前

file varcha 可以这么高?

2个月前 评论
huangxu 2个月前

上一家公司也是这么设计的,其实最大的一个疑问在于。 在一个事务中多表操作,对mysql数据库的压力。

2个月前 评论

我想问一下, user_wallet_log表里面的 字段 target_type 和action_type 存储的东西都是一样的吗?

2个月前 评论

为什么json数据要用varchar存储,直接用json格式不行吗?mysql5.7就支持了。laravel Model里面用cats转化成为array还是很便捷的。

2个月前 评论

有个问题想请教 用uuid作为用户唯一标识 是因为用到了集群吗

2个月前 评论
Euan (楼主) 2个月前

没get到checksign的用处,是防止直接修改数据库的操作吗? 有能直接改库的权限了,代码也是有的吧?改库后重新sign就行了?

2个月前 评论

我好奇就是,varchar(1000)可不可以用json字段代替,status这些字段如果用字符串存储,例如:paid这样存会不会利于阅读呢

1个月前 评论

请勿发布不友善或者负能量的内容。与人为善,比聪明更重要!