minglei/sql/banking_payment_tracking.sql

CREATE TABLE `banking_payment_tracking` (
  `id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键',
  `business_trans_no` VARCHAR(128) NOT NULL COMMENT '银企支付单号，制单请求的businessTransNo',
  `pay_amount` DECIMAL(18,2) NOT NULL COMMENT '支付金额',
  `payment_account_no` VARCHAR(128) DEFAULT NULL COMMENT '付款账号',
  `customerAccountNo` VARCHAR(255) DEFAULT NULL COMMENT '收款账号',
  `customerName` VARCHAR(255) DEFAULT NULL COMMENT '收款方名称',
  `customerBank` VARCHAR(255) DEFAULT NULL COMMENT '收款方银行名称',
  `remark` VARCHAR(500) DEFAULT NULL COMMENT '付款说明',
  `payment_status` VARCHAR(64) DEFAULT NULL COMMENT '银企返回的支付状态，例如X0000',
  `tracking_status` VARCHAR(32) NOT NULL DEFAULT 'PENDING' COMMENT '补偿状态：PENDING、RECEIPT_PENDING、COMPLETED',
  `form_id` VARCHAR(128) DEFAULT NULL COMMENT '宜搭表单ID，来源于formID',
  `form_instance_id` VARCHAR(128) DEFAULT NULL COMMENT '宜搭表单实例ID',
  `receipt_preview_url` VARCHAR(1024) DEFAULT NULL COMMENT '回单预览地址',
  `receipt_download_url` VARCHAR(1024) DEFAULT NULL COMMENT '回单下载地址',
  `created_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
  `updated_at` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_banking_payment_tracking_business_trans_no` (`business_trans_no`),
  KEY `idx_banking_payment_tracking_status` (`tracking_status`, `id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='银企支付状态与电子回单补偿记录';