119 lines
6.7 KiB
SQL
119 lines
6.7 KiB
SQL
-- pay-bridge 初始化数据库脚本
|
||
-- MySQL 8.0, utf8mb4, InnoDB
|
||
-- 金额字段统一使用 BIGINT（单位：分）
|
||
|
||
CREATE DATABASE IF NOT EXISTS pay_bridge DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
USE pay_bridge;
|
||
|
||
-- 接入应用
|
||
CREATE TABLE IF NOT EXISTS `app` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`app_id` VARCHAR(32) NOT NULL COMMENT '应用ID，下游系统鉴权凭证',
|
||
`app_secret` VARCHAR(128) NOT NULL COMMENT '应用密钥（AES加密存储）',
|
||
`app_name` VARCHAR(64) NOT NULL COMMENT '应用名称',
|
||
`status` TINYINT NOT NULL DEFAULT 1 COMMENT '1=启用 0=禁用',
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_app_id` (`app_id`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='接入应用';
|
||
|
||
-- 交易订单
|
||
CREATE TABLE IF NOT EXISTS `trade_order` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`trade_no` VARCHAR(32) NOT NULL COMMENT 'pay-bridge生成的交易号',
|
||
`merchant_order_no` VARCHAR(64) NOT NULL COMMENT '下游系统的商户订单号',
|
||
`app_id` VARCHAR(32) NOT NULL COMMENT '所属应用ID',
|
||
`channel_code` VARCHAR(32) NOT NULL COMMENT '支付渠道编码',
|
||
`channel_trade_no` VARCHAR(64) DEFAULT NULL COMMENT '上游渠道交易号',
|
||
`pay_method` VARCHAR(32) NOT NULL COMMENT '支付方式',
|
||
`amount` BIGINT NOT NULL COMMENT '订单金额（分）',
|
||
`profit_sharing_amount` BIGINT NOT NULL DEFAULT 0 COMMENT '分润金额（分）',
|
||
`service_fee_amount` BIGINT NOT NULL DEFAULT 0 COMMENT '服务费金额（分）',
|
||
`subject` VARCHAR(256) NOT NULL COMMENT '商品描述',
|
||
`notify_url` VARCHAR(512) NOT NULL COMMENT '下游通知地址',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'CREATING' COMMENT '交易状态',
|
||
`extra` JSON DEFAULT NULL COMMENT '支付方式扩展参数',
|
||
`channel_extra` JSON DEFAULT NULL COMMENT '渠道返回的支付凭证',
|
||
`expire_time` DATETIME(3) NOT NULL COMMENT '订单过期时间',
|
||
`pay_time` DATETIME(3) DEFAULT NULL COMMENT '支付成功时间',
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_trade_no` (`trade_no`),
|
||
UNIQUE KEY `uk_app_merchant_order` (`app_id`, `merchant_order_no`),
|
||
KEY `idx_channel_trade_no` (`channel_trade_no`),
|
||
KEY `idx_app_status_created` (`app_id`, `status`, `created_at`),
|
||
KEY `idx_expire_time` (`expire_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='交易订单';
|
||
|
||
-- 退款记录
|
||
CREATE TABLE IF NOT EXISTS `refund_order` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`refund_no` VARCHAR(32) NOT NULL COMMENT 'pay-bridge退款单号',
|
||
`trade_no` VARCHAR(32) NOT NULL COMMENT '关联交易号',
|
||
`app_id` VARCHAR(32) NOT NULL,
|
||
`channel_code` VARCHAR(32) NOT NULL,
|
||
`channel_refund_no` VARCHAR(64) DEFAULT NULL COMMENT '上游渠道退款单号',
|
||
`refund_amount` BIGINT NOT NULL COMMENT '退款金额（分）',
|
||
`reason` VARCHAR(256) DEFAULT NULL COMMENT '退款原因',
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
`notify_url` VARCHAR(512) DEFAULT NULL,
|
||
`refund_time` DATETIME(3) DEFAULT NULL COMMENT '退款完成时间',
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_refund_no` (`refund_no`),
|
||
KEY `idx_trade_no` (`trade_no`),
|
||
KEY `idx_app_status` (`app_id`, `status`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='退款记录';
|
||
|
||
-- 渠道配置
|
||
CREATE TABLE IF NOT EXISTS `channel_config` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`app_id` VARCHAR(32) NOT NULL COMMENT '关联应用ID',
|
||
`channel_code` VARCHAR(32) NOT NULL COMMENT '渠道编码',
|
||
`merchant_id` VARCHAR(64) NOT NULL COMMENT '渠道商户ID',
|
||
`api_key` TEXT DEFAULT NULL COMMENT 'API密钥（AES加密）',
|
||
`private_key` TEXT DEFAULT NULL COMMENT 'RSA私钥（AES加密）',
|
||
`public_key` TEXT DEFAULT NULL COMMENT '渠道公钥（明文）',
|
||
`notify_url` VARCHAR(512) NOT NULL COMMENT '上游回调接收地址',
|
||
`sandbox` TINYINT NOT NULL DEFAULT 0 COMMENT '1=沙箱 0=生产',
|
||
`extra_config` JSON DEFAULT NULL COMMENT '渠道特有扩展配置',
|
||
`status` TINYINT NOT NULL DEFAULT 1,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_app_channel` (`app_id`, `channel_code`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='渠道配置';
|
||
|
||
-- 下游通知记录
|
||
CREATE TABLE IF NOT EXISTS `notify_log` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`trade_no` VARCHAR(32) NOT NULL COMMENT '关联交易号',
|
||
`notify_type` VARCHAR(20) NOT NULL COMMENT 'PAYMENT / REFUND',
|
||
`notify_url` VARCHAR(512) NOT NULL,
|
||
`status` VARCHAR(20) NOT NULL DEFAULT 'PENDING',
|
||
`retry_count` INT NOT NULL DEFAULT 0,
|
||
`next_retry_time` DATETIME(3) DEFAULT NULL,
|
||
`last_response` TEXT DEFAULT NULL,
|
||
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_trade_notify_type` (`trade_no`, `notify_type`),
|
||
KEY `idx_status_next_retry` (`status`, `next_retry_time`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='下游通知记录';
|
||
|
||
-- 订单编码序列
|
||
CREATE TABLE IF NOT EXISTS `order_sequence` (
|
||
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
||
`app_id` VARCHAR(32) NOT NULL,
|
||
`seq_type` VARCHAR(20) NOT NULL COMMENT 'TRADE / REFUND / SHARING',
|
||
`prefix` VARCHAR(8) NOT NULL COMMENT '序号前缀',
|
||
`current_value` BIGINT UNSIGNED NOT NULL DEFAULT 0,
|
||
`step` INT NOT NULL DEFAULT 1,
|
||
`updated_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3) ON UPDATE CURRENT_TIMESTAMP(3),
|
||
PRIMARY KEY (`id`),
|
||
UNIQUE KEY `uk_app_type` (`app_id`, `seq_type`)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='订单编码序列';
|