trans(交易表)trans(交易表)
transidtransidPKPK
transno(UUID)transno(UUID)
translog(交易日志表)translog(交易日志表)
translogidtranslogidPKPK
transidtransid
pay_channel 支付渠道pay_channel 支付渠道
pay_channelidpay_channelidPKPK
namename
notify_urlnotify_url
account(钱包账户)account(钱包账户)
accountidaccountidPKPK
balancebalance
freeze_balance(冻结金额)freeze_balance(冻结金额)
accountlog(账户变化流水表)accountlog(账户变化流水表)
accountlogidaccountlogidPKPK
amountamount
type(收入/支出/冻结/解冻)type(收入/支出/冻结/解冻)
out_trade_noout_trade_no
state
(待支付/已完成/已关闭)
state
(待支付/已完成/已关闭)
creat_timecreat_time
pay_timepay_time
update_timeupdate_time
titletitle
amountamount
transflowidtransflowid
amountamount
payments(付款表)payments(付款表)
paymentidpaymentidPKPK
transidtransid
statestate
creat_timecreat_time
pay_timepay_time
update_timeupdate_time
amountamount
receivables(收款表)receivables(收款表)
receivableidreceivableidPKPK
transidtransid
statestate
creat_timecreat_time
rec_timerec_time
update_timeupdate_time
amountamount
state(处理中/已完成/失败/已关闭)state(处理中/已完成/失败/已关闭)
creat_timecreat_time
refund 退款申请refund 退款申请
refundidrefundidPKPK
accountidaccountid
creat_timecreat_time
update_timeupdate_time
statestate
amountamount
tranidtranid
pay_channelidpay_channelid
success_timesuccess_time
update_timeupdate_time
ssoidssoid
statestate
statestate
creat_timecreat_time
update_timeupdate_time
titletitle
statestate
creat_timecreat_time
update_timeupdate_time
creat_timecreat_time
update_timeupdate_time
freeze 冻结申请freeze 冻结申请
freezeidfreezeidPKPK
userssoiduserssoid
creat_timecreat_time
update_timeupdate_time
state 待处理/已冻结/已解冻state 待处理/已冻结/已解冻
amountamount
accountidaccountid
cancel_timecancel_time
done_timedone_time
scertkeyscertkey
encryptionencryption
client 接入客户端配置client 接入客户端配置
clientidclientidPKPK
bussinessidbussinessid
appidappid
statestate
creat_timecreat_time
update_timeupdate_time
notify_urlnotify_url
expiry_time 支付有效期expiry_time 支付有效期
payaccountidpayaccountid
recaccountidrecaccountid
namename
bankaccountbankaccount
bankbank
transidtransid
pay_state (0应付/1实付)pay_state (0应付/1实付)
rec_state (0应收/1实收)rec_state (0应收/1实收)
transflow(交易流水表)transflow(交易流水表)
transflowidtransflowidPKPK
transidtransid
amountamount
state(处理中/已完成/失败/已关闭)state(处理中/已完成/失败/已关闭)
creat_timecreat_time
success_timesuccess_time
update_timeupdate_time
payaccountidpayaccountid
recaccountidrecaccountid
flow_type
(外部支付/余额支付/余额收入/转出到外部)
flow_type
(外部支付/余额支付/余额收入/转出到外部)
paymentidpaymentid
receivableidreceivableid
trans_type(支付/充值/提现/退款支付)trans_type(支付/充值/提现/退款支付)
settle(未结算/已结算)settle(未结算/已结算)
结算 单次交易(transid相同):
sum(应付) = sum(应收) = sum(交易流水实付) = sum(交易流水实收) = 交易金额
trans.amount
============应付
SUM(payments.amount)
============实付
SUM(transflow.amount) WHERE transflow.flow_type IN (外部支付 or 余额支付) AND state = 已完成 AND
============应收
SUM(receivables.amount)
============实收
SUM(transflow.amount WHERE transflow.flow_type IN (收入) AND state = 已完成)
交易流水实付状态 与 交易日支付状态一致
transflowid相同
transflow.state = 已处理
translog.state = 已处理
充值 单次交易(transid相同)
sum(应收) = sum(交易流水实收) = 钱包流水一致
============应收
SUM(receivables.amount)
============实收
SUM(transflow.amount WHERE transflow.flow_type IN (外部支付) AND state = 已完成)
==========钱包余额流水
translog.amount
提现 单次交易(transid相同)
sum(应付) = sum(交易流水实付) = 钱包流水一致
============应付
SUM(payments.amount)
============实付
SUM(transflow.amount WHERE transflow.flow_type IN (转出到外部) AND state = 已完成)
==========钱包余额流水
translog.amount
数据加密摘要一致:用skey生成当前数据的加密摘要 与 当前数据的encryption字段内容一致
skey:非对称秘钥
Apollo中配置skey_key_use:配置配置中心中正在被使用秘钥值的key
skey_key_use的配置值当做key去Apollo中用获取skey值
encryption:用skey生成的当前数据表对应的对称加密字段,每次应用更新数据时同步更新
trans表:transid + out_trade_no + trans_type + amount + payaccountid + recaccountid + state
transflow表:transflowid + transid + amount + flow_type + paymentid +receivableid + state
translog表:translogid + transid + transflowid + amount + + be_amount + af_amount + state
payments表:paymentid + transid + payaccountid + amount + pay_state
receivables表:receivableid + transid + recaccountid + amount + rec_state
account表: amount + freeze_amount + state
每天凌晨2点对于当天0点之前发生的已结算的交易流水与支付渠道提供的日志进行对账
scertkeyscertkey
encryptionencryption
scertkeyscertkey
encryptionencryption
in_out (0转入/1转出/2平台内划转)in_out (0转入/1转出/2平台内划转)
scertkeyscertkey
encryptionencryption
scertkeyscertkey
encryptionencryption
scertkeyscertkey
encryptionencryption
scertkeyscertkey
encryptionencryption
deposit 充值申请deposit 充值申请
depositiddepositidPKPK
accountidaccountid
creat_timecreat_time
update_timeupdate_time
statestate
amountamount
tranidtranid
cancel_timecancel_time
done_timedone_time
namename
withdraw 提现申请withdraw 提现申请
withdrawidwithdrawidPKPK
accountidaccountid
creat_timecreat_time
update_timeupdate_time
statestate
amountamount
tranidtranid
cancel_timecancel_time
done_timedone_time
namename
bankaccountbankaccount
bankbank
depositnodepositno
withdrawnowithdrawno
refundnorefundno
freezenofreezeno
snapshot(修改前的数据快照)snapshot(修改前的数据快照)
执行update sql做幂等校验
update account set amount=amount-${cost}
where ssoid =xxx AND (amount -freeze_amount -${cost}) >=0
有一条余额支付渠道的初
始化数据