-- 创建数据库
|
CREATE DATABASE IF NOT EXISTS `dryad_payment` DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
|
USE `dryad_payment`;
|
|
-- 删除已存在的表
|
DROP TABLE IF EXISTS `pay_reconciliation_result`;
|
DROP TABLE IF EXISTS `pay_reconciliation_task`;
|
DROP TABLE IF EXISTS `pay_operation_audit`;
|
DROP TABLE IF EXISTS `pay_biz_callback_log`;
|
DROP TABLE IF EXISTS `pay_notify_log`;
|
DROP TABLE IF EXISTS `pay_transaction`;
|
DROP TABLE IF EXISTS `pay_order`;
|
|
-- 支付订单表
|
CREATE TABLE `pay_order` (
|
`id` BIGINT NOT NULL PRIMARY KEY COMMENT '订单ID',
|
`biz_order_id` VARCHAR(64) NOT NULL COMMENT '业务订单号',
|
`amount` INT NOT NULL COMMENT '金额（分）',
|
`currency` VARCHAR(8) NOT NULL DEFAULT 'CNY' COMMENT '币种',
|
`channel` VARCHAR(16) NOT NULL COMMENT '支付渠道',
|
`status` VARCHAR(16) NOT NULL COMMENT '订单状态',
|
`subject` VARCHAR(128) NOT NULL COMMENT '订单标题',
|
`description` VARCHAR(512) COMMENT '订单描述',
|
`callback_url` VARCHAR(512) NOT NULL COMMENT '业务回调地址',
|
`expire_at` DATETIME NOT NULL COMMENT '过期时间',
|
`latest_transaction_id` BIGINT COMMENT '最新交易ID',
|
`channel_trade_no` VARCHAR(64) COMMENT '渠道交易号',
|
`paid_at` DATETIME COMMENT '支付成功时间',
|
`version` INT NOT NULL DEFAULT 0 COMMENT '乐观锁版本号',
|
`created_at` DATETIME NOT NULL COMMENT '创建时间',
|
`updated_at` DATETIME NOT NULL COMMENT '更新时间',
|
INDEX `idx_biz_order_id` (`biz_order_id`),
|
INDEX `idx_channel_trade_no` (`channel_trade_no`),
|
INDEX `idx_status` (`status`),
|
INDEX `idx_expire_at` (`expire_at`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付订单表';
|
|
-- 支付交易流水表
|
CREATE TABLE `pay_transaction` (
|
`id` BIGINT NOT NULL PRIMARY KEY COMMENT '交易ID',
|
`order_id` BIGINT NOT NULL COMMENT '订单ID',
|
`channel` VARCHAR(16) NOT NULL COMMENT '支付渠道',
|
`client_type` VARCHAR(32) NOT NULL COMMENT '客户端类型',
|
`status` VARCHAR(16) NOT NULL COMMENT '交易状态',
|
`code_or_qr` VARCHAR(512) COMMENT '二维码内容',
|
`qr_base64` TEXT COMMENT 'Base64二维码图片',
|
`request_params` TEXT COMMENT '请求参数快照',
|
`response_snapshot` TEXT COMMENT '响应快照',
|
`channel_trade_no` VARCHAR(64) COMMENT '渠道交易号',
|
`created_at` DATETIME NOT NULL COMMENT '创建时间',
|
`paid_at` DATETIME COMMENT '支付完成时间',
|
INDEX `idx_order_id` (`order_id`),
|
INDEX `idx_channel_trade_no` (`channel_trade_no`),
|
INDEX `idx_status` (`status`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='支付交易流水表';
|
|
-- 渠道回调日志表
|
CREATE TABLE `pay_notify_log` (
|
`id` BIGINT NOT NULL PRIMARY KEY COMMENT '日志ID',
|
`channel` VARCHAR(16) NOT NULL COMMENT '支付渠道',
|
`notify_id_or_serial` VARCHAR(64) NOT NULL COMMENT '渠道通知唯一标识',
|
`order_id` BIGINT COMMENT '订单ID',
|
`transaction_id` BIGINT COMMENT '交易ID',
|
`payload` TEXT NOT NULL COMMENT '回调原始报文',
|
`verified` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '签名验证是否通过',
|
`processed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否已处理',
|
`result` VARCHAR(128) COMMENT '处理结果',
|
`created_at` DATETIME NOT NULL COMMENT '接收时间',
|
UNIQUE KEY `uk_channel_notify` (`channel`, `notify_id_or_serial`),
|
INDEX `idx_order_id` (`order_id`),
|
INDEX `idx_transaction_id` (`transaction_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='渠道回调日志表';
|
|
-- 业务回调日志表
|
CREATE TABLE `pay_biz_callback_log` (
|
`id` BIGINT NOT NULL PRIMARY KEY COMMENT '日志ID',
|
`order_id` BIGINT NOT NULL COMMENT '订单ID',
|
`transaction_id` BIGINT NOT NULL COMMENT '交易ID',
|
`callback_url` VARCHAR(512) NOT NULL COMMENT '回调地址',
|
`payload` TEXT NOT NULL COMMENT '回调请求体',
|
`http_status` INT COMMENT 'HTTP状态码',
|
`response` TEXT COMMENT '响应内容',
|
`success` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否成功',
|
`retry_count` INT NOT NULL DEFAULT 0 COMMENT '重试次数',
|
`last_retry_at` DATETIME COMMENT '最后重试时间',
|
`created_at` DATETIME NOT NULL COMMENT '创建时间',
|
INDEX `idx_order_id` (`order_id`),
|
INDEX `idx_success` (`success`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='业务回调日志表';
|
|
-- 操作审计表
|
CREATE TABLE `pay_operation_audit` (
|
`id` BIGINT NOT NULL PRIMARY KEY COMMENT '审计ID',
|
`operator` VARCHAR(64) NOT NULL COMMENT '操作人',
|
`operation_type` VARCHAR(32) NOT NULL COMMENT '操作类型',
|
`order_id` BIGINT COMMENT '订单ID',
|
`transaction_id` BIGINT COMMENT '交易ID',
|
`params` TEXT COMMENT '操作参数',
|
`approved` TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否通过',
|
`created_at` DATETIME NOT NULL COMMENT '操作时间',
|
INDEX `idx_operator` (`operator`),
|
INDEX `idx_operation_type` (`operation_type`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作审计表';
|
|
-- 对账任务表
|
CREATE TABLE `pay_reconciliation_task` (
|
`id` BIGINT NOT NULL PRIMARY KEY COMMENT '任务ID',
|
`task_date` DATE NOT NULL COMMENT '对账日期',
|
`status` VARCHAR(16) NOT NULL COMMENT '任务状态',
|
`total_count` INT NOT NULL DEFAULT 0 COMMENT '总订单数',
|
`success_count` INT NOT NULL DEFAULT 0 COMMENT '成功数',
|
`failed_count` INT NOT NULL DEFAULT 0 COMMENT '失败数',
|
`diff_count` INT NOT NULL DEFAULT 0 COMMENT '差异数',
|
`fixed_count` INT NOT NULL DEFAULT 0 COMMENT '自动修复数',
|
`created_at` DATETIME NOT NULL COMMENT '创建时间',
|
`finished_at` DATETIME COMMENT '完成时间',
|
UNIQUE KEY `uk_task_date` (`task_date`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='对账任务表';
|
|
-- 对账差异明细表
|
CREATE TABLE `pay_reconciliation_result` (
|
`id` BIGINT NOT NULL PRIMARY KEY COMMENT '明细ID',
|
`task_id` BIGINT NOT NULL COMMENT '任务ID',
|
`order_id` BIGINT NOT NULL COMMENT '订单ID',
|
`transaction_id` BIGINT COMMENT '交易ID',
|
`local_status` VARCHAR(16) COMMENT '本地状态',
|
`channel_status` VARCHAR(16) COMMENT '渠道状态',
|
`diff_type` VARCHAR(32) NOT NULL COMMENT '差异类型',
|
`fixed` TINYINT(1) NOT NULL DEFAULT 0 COMMENT '是否已修复',
|
`note` VARCHAR(512) COMMENT '备注',
|
`created_at` DATETIME NOT NULL COMMENT '创建时间',
|
INDEX `idx_task_id` (`task_id`),
|
INDEX `idx_order_id` (`order_id`)
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='对账差异明细表';
|
