break
cbf909b878
All checks were successful
构建并部署到测试环境（无 SSH） / build-and-deploy (push) Successful in 8m6s
43 lines
1.7 KiB
SQL
43 lines
1.7 KiB
SQL
-- 代理在线扫码充值：请求幂等、付款内容与待处理查询索引。
|
|
DO $$
|
|
BEGIN
|
|
IF EXISTS (
|
|
SELECT 1
|
|
FROM tb_payment
|
|
WHERE third_party_trade_no IS NOT NULL
|
|
AND third_party_trade_no <> ''
|
|
GROUP BY payment_method, third_party_trade_no
|
|
HAVING COUNT(*) > 1
|
|
) THEN
|
|
RAISE EXCEPTION '检测到重复的支付方式与第三方交易号，请先人工核对资金事实';
|
|
END IF;
|
|
END
|
|
$$;
|
|
|
|
ALTER TABLE tb_agent_recharge_record
|
|
ADD COLUMN request_id varchar(64),
|
|
ADD COLUMN request_fingerprint varchar(64);
|
|
|
|
ALTER TABLE tb_payment
|
|
ADD COLUMN qr_content text;
|
|
|
|
CREATE UNIQUE INDEX uk_agent_recharge_user_request
|
|
ON tb_agent_recharge_record (user_id, request_id)
|
|
WHERE request_id IS NOT NULL AND request_id <> '';
|
|
|
|
CREATE UNIQUE INDEX uk_payment_method_trade_no
|
|
ON tb_payment (payment_method, third_party_trade_no)
|
|
WHERE third_party_trade_no IS NOT NULL AND third_party_trade_no <> '';
|
|
|
|
CREATE INDEX idx_agent_recharge_online_pending
|
|
ON tb_agent_recharge_record (status, created_at, id)
|
|
WHERE deleted_at IS NULL AND payment_method IN ('wechat', 'alipay') AND status IN (1, 2, 4);
|
|
|
|
CREATE INDEX idx_payment_agent_recharge_pending
|
|
ON tb_payment (status, created_at, id)
|
|
WHERE deleted_at IS NULL AND order_type = 'agent_recharge' AND status = 0;
|
|
|
|
COMMENT ON COLUMN tb_agent_recharge_record.request_id IS '提交账号提供的在线充值幂等请求标识';
|
|
COMMENT ON COLUMN tb_agent_recharge_record.request_fingerprint IS '在线充值请求业务字段指纹';
|
|
COMMENT ON COLUMN tb_payment.qr_content IS '支付渠道返回的原始扫码付款内容，禁止写入日志或普通查询响应';
|