Codex
39 lines
1.7 KiB
SQL
39 lines
1.7 KiB
SQL
ALTER TABLE qipai_orders
|
|
ADD COLUMN status_version INT UNSIGNED NOT NULL DEFAULT 1 AFTER status,
|
|
ADD COLUMN status_updated_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3)
|
|
AFTER status_version;
|
|
|
|
CREATE TABLE IF NOT EXISTS qipai_order_status_history (
|
|
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
|
|
tenant_id BIGINT UNSIGNED NOT NULL,
|
|
order_id BIGINT UNSIGNED NOT NULL,
|
|
from_status VARCHAR(32) NULL,
|
|
to_status VARCHAR(32) NOT NULL,
|
|
action VARCHAR(32) NOT NULL,
|
|
actor_type VARCHAR(32) NOT NULL,
|
|
actor_id BIGINT UNSIGNED NULL,
|
|
source VARCHAR(32) NOT NULL,
|
|
reason VARCHAR(512) NOT NULL DEFAULT '',
|
|
trace_id VARCHAR(128) NOT NULL,
|
|
metadata JSON NOT NULL,
|
|
created_at DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
CONSTRAINT fk_qipai_order_history_tenant
|
|
FOREIGN KEY (tenant_id) REFERENCES qipai_tenants(id),
|
|
CONSTRAINT fk_qipai_order_history_order
|
|
FOREIGN KEY (order_id) REFERENCES qipai_orders(id),
|
|
UNIQUE KEY uq_qipai_order_history_trace (tenant_id, order_id, trace_id),
|
|
KEY idx_qipai_order_history_order (tenant_id, order_id, id),
|
|
KEY idx_qipai_order_history_status (tenant_id, to_status, created_at)
|
|
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
|
|
|
|
INSERT IGNORE INTO qipai_order_status_history
|
|
(tenant_id, order_id, from_status, to_status, action, actor_type,
|
|
actor_id, source, reason, trace_id, metadata, created_at)
|
|
SELECT tenant_id, id, NULL, status, 'MIGRATED', 'SYSTEM',
|
|
NULL, 'MIGRATION', 'M04-B baseline history',
|
|
CONCAT('m04b-migration-', id), JSON_OBJECT('statusVersion', 1), created_at
|
|
FROM qipai_orders;
|
|
|
|
INSERT IGNORE INTO qipai_schema_migrations (version, name)
|
|
VALUES ('2026062012', 'm04b_order_state_machine');
|