市场应用PayPal Payouts API日结打款逻辑与数据设计问询

1. 待结算余额方案评估 #

完全靠实时聚合Transaction表算余额的方案只适合开发阶段凑合用，上线后数据量上来了必出问题：一是查询性能会随着交易增长线性下降，二是聚合逻辑一旦漏了状态筛选、或者并发下有交易写入，算出来的金额就会错。

• 最稳妥的方案是直接在User表加两个余额字段：available_settlement_balance（可结算余额，整数，存货币最小单位）、frozen_balance（冻结中余额，比如处于退款争议期的资金）。所有资金变动都在数据库事务内同步更新余额，同时写Transaction流水，流水作为对账凭证，余额字段作为业务逻辑直接读取的可信源，每天跑批核对流水总和和余额是否一致即可。
• 要是实在不想冗余存余额字段，至少给Transaction表加上seller_user_id、is_settled两个索引，不要每次全表扫了求和，只查未结算的交易做聚合。

2. 表结构调整建议 #

你现在的表结构核心问题是把打款批次和交易流水的耦合放错了位置，调整后可以彻底解决payout_id关联的问题：

• Transaction表改动
  • 删掉现在设计的payout_id字段，不要在购买交易上直接绑定打款ID，不然打款失败重试、部分交易打款失败退回的时候，你要批量改一堆交易记录，很容易出脏数据
  • 新增seller_user_id字段，不管是购买还是打款类型的交易，都直接绑定所属卖家ID，不用每次关联商品表查卖家，聚合计算效率至少提一个量级
  • 新增is_settled布尔字段，默认值0，标记这笔购买交易是不是已经完成打款结算
  • 新增reverse_transaction_id字段，用来关联退款、打款失败冲正这类反向操作对应的原交易
• Payout表改动
  • Payout表本身不冗余，它对应的是你调用PayPal Payouts API生成的打款批次，和payout类型的Transaction是一对一关系，但职责不一样：Transaction记用户维度的资金流水，Payout记打款批次的外部交互信息。需要给Payout表补几个字段：seller_user_id（打款收款人）、total_amount（批次总打款金额）、paypal_batch_id（PayPal返回的官方批次ID，用来匹配webhook回调）、platform_fee（平台手续费）、paypal_fee（PayPal收的打款手续费）
  • 新增中间关联表payout_item，解决打款批次和购买交易的多对多映射问题，字段就三个：payout_id（关联Payout表主键）、transaction_id（关联purchase类型的Transaction主键）、amount（这笔交易在本批次打款的金额）。要查某笔打款包含哪些交易、某笔交易属于哪个打款批次，查这张表就行，完全不需要在Transaction表上加payout_id字段。

  原始交易记录一旦写入尽量不要更新核心关联字段，这是账务系统的基本要求。为什么非要加中间表？打款是可能失败的，失败后这笔交易要归到下一个批次重打，用中间表的话你只需要删掉失败批次对应的payout_item记录，把对应交易的is_settled改回0就行，不用动原始交易记录。

3. 打款批次归属逻辑优化 #

你想的“按CRON执行时点前24小时的交易划分批次”方案稳健性很差：服务器时区不对、CRON任务延迟跑、PayPal接口超时重试、某笔交易刚好卡在时间临界点，都会导致漏打或者重复打，后期对账会非常麻烦。
不要卡时间窗口切批次，用状态标记来圈定待打款交易：

• 先给购买交易加一个冻结期，比如用户付款完成后72小时再进入可结算状态，等过了默认退款/争议期再打款，避免刚打完款用户就申请退款，你还要找卖家追钱。
• CRON任务跑的时候，不要按创建时间筛24小时内的交易，直接查所有满足以下条件的交易：type = 'purchase' AND status = 'COMPLETED' AND is_settled = 0 AND created_at < (当前时间 - 冻结时长)，按卖家ID分组汇总金额。
• 查出来待结算交易后，所有操作放到一个数据库事务里执行：
  • 给查出来的待结算交易加行锁，防止并发启动多个CRON任务重复处理同一批交易
  • 往Payout表插一条状态为PENDING的批次记录
  • 批量往payout_item表插记录，把当前批次ID和所有待结算交易关联起来
  • 把这批待结算交易的is_settled字段更新为1
  • 往Transaction表插一条type为payout的负金额记录，对应卖家ID
  • 扣减卖家的available_settlement_balance对应金额
• 事务提交成功后，再调用PayPal Payouts API发起打款，后续接收到PayPal的webhook回调再更新Payout批次状态：
  • 打款成功：把对应的payout类型Transaction状态改成COMPLETED即可
  • 打款失败：把该批次下所有payout_item对应的交易is_settled改回0，删掉对应的payout_item记录，把payout类型Transaction状态标记为FAILED，同时把扣减的金额加回卖家的可结算余额，等下一次CRON任务自动重试就行。

4. 原有CRON流程的问题修正 #

你原来设计的三步流程有两个高危漏洞：

• 先插payout流水扣余额，再插Payout批次记录，中间任何一步出错都会导致用户余额扣了但实际没发起打款，所有数据库写操作必须放在同一个事务里，要么全成要么全回滚。
• 不要直接把用户的待结算余额全部扣成0，要以你实际加锁拿到的待结算交易总金额为准，防止CRON执行过程中有新的交易完成结算，被误算到本次打款金额里。

内容的提问来源于stack exchange，提问作者Jim Stevens