break
9959168e22
Some checks failed
构建并部署到测试环境（无 SSH） / build-and-deploy (push) Failing after 5h7m21s
45 lines
2.7 KiB
SQL
45 lines
2.7 KiB
SQL
-- 支付创建提交时的路由尝试事实，以及明确失败自动禁用的安全留痕。
|
||
-- 关联只保存 ID，不保存凭证、原始响应或敏感渠道载荷。
|
||
CREATE TABLE tb_payment_merchant_routing_attempt (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
payment_id BIGINT NOT NULL,
|
||
merchant_id BIGINT NOT NULL,
|
||
pool_id BIGINT NOT NULL,
|
||
routing_epoch BIGINT NOT NULL,
|
||
amount BIGINT NOT NULL,
|
||
result_class VARCHAR(32) NOT NULL DEFAULT 'pending',
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
CONSTRAINT uk_payment_merchant_routing_attempt_payment UNIQUE (payment_id),
|
||
CONSTRAINT chk_payment_merchant_routing_attempt_epoch CHECK (routing_epoch >= 1),
|
||
CONSTRAINT chk_payment_merchant_routing_attempt_amount CHECK (amount > 0),
|
||
CONSTRAINT chk_payment_merchant_routing_attempt_result CHECK (result_class IN ('pending', 'success', 'explicit_failed', 'unknown', 'not_sent', 'cancelled'))
|
||
);
|
||
CREATE INDEX idx_payment_merchant_routing_attempt_select
|
||
ON tb_payment_merchant_routing_attempt (pool_id, routing_epoch, created_at, merchant_id, id);
|
||
CREATE INDEX idx_payment_merchant_routing_attempt_merchant
|
||
ON tb_payment_merchant_routing_attempt (merchant_id, created_at, id);
|
||
|
||
CREATE TABLE tb_payment_merchant_failure (
|
||
id BIGSERIAL PRIMARY KEY,
|
||
payment_id BIGINT NOT NULL,
|
||
payment_no VARCHAR(40) NOT NULL,
|
||
merchant_id BIGINT NOT NULL,
|
||
pool_id BIGINT NOT NULL,
|
||
result_class VARCHAR(32) NOT NULL,
|
||
failure_reason VARCHAR(128) NOT NULL DEFAULT '',
|
||
merchant_status_before SMALLINT NOT NULL,
|
||
merchant_status_after SMALLINT NOT NULL,
|
||
occurred_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
created_at TIMESTAMPTZ NOT NULL DEFAULT NOW(),
|
||
CONSTRAINT chk_payment_merchant_failure_result CHECK (result_class = 'explicit_failed')
|
||
);
|
||
CREATE INDEX idx_payment_merchant_failure_merchant_time
|
||
ON tb_payment_merchant_failure (merchant_id, occurred_at DESC, id DESC);
|
||
CREATE INDEX idx_payment_merchant_failure_payment
|
||
ON tb_payment_merchant_failure (payment_id, occurred_at DESC, id DESC);
|
||
|
||
COMMENT ON TABLE tb_payment_merchant_routing_attempt IS '新支付创建提交时冻结路由的一次不可变尝试事实，不依赖最终支付结果';
|
||
COMMENT ON COLUMN tb_payment_merchant_routing_attempt.result_class IS '尝试结果分类：pending/success/explicit_failed/unknown/not_sent/cancelled';
|
||
COMMENT ON TABLE tb_payment_merchant_failure IS '支付商户明确预下单失败及自动禁用的安全调查留痕，不保存凭证或原始响应';
|
||
COMMENT ON COLUMN tb_payment_merchant_failure.failure_reason IS '脱敏稳定失败原因编码或摘要';
|