You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
130 lines
4.8 KiB
130 lines
4.8 KiB
-- CreateTable
|
|
CREATE TABLE `sys_user` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`user_id` VARCHAR(32) NOT NULL,
|
|
`customer_id` VARCHAR(32) NULL,
|
|
`username` VARCHAR(64) NOT NULL,
|
|
`password_hash` VARCHAR(255) NOT NULL,
|
|
`role` VARCHAR(16) NOT NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT false,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `sys_user_user_id_key`(`user_id`),
|
|
UNIQUE INDEX `sys_user_customer_id_key`(`customer_id`),
|
|
UNIQUE INDEX `sys_user_username_key`(`username`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `quote_record` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`quote_id` VARCHAR(32) NOT NULL,
|
|
`request_id` VARCHAR(36) NOT NULL,
|
|
`customer_id` VARCHAR(32) NOT NULL,
|
|
`cargo_hash` CHAR(32) NOT NULL,
|
|
`status` VARCHAR(16) NOT NULL DEFAULT 'processing',
|
|
`source_type` VARCHAR(16) NULL,
|
|
`is_realtime` BOOLEAN NOT NULL DEFAULT true,
|
|
`confidence_score` DECIMAL(3, 2) NULL,
|
|
`currency` VARCHAR(8) NOT NULL DEFAULT 'USD',
|
|
`pickup_json` JSON NOT NULL,
|
|
`delivery_json` JSON NOT NULL,
|
|
`weight_lb` DECIMAL(12, 2) NOT NULL,
|
|
`dim_l_in` DECIMAL(8, 2) NOT NULL,
|
|
`dim_w_in` DECIMAL(8, 2) NOT NULL,
|
|
`dim_h_in` DECIMAL(8, 2) NOT NULL,
|
|
`pallet_count` INTEGER NOT NULL,
|
|
`cargo_type` VARCHAR(32) NOT NULL,
|
|
`quotes_json` JSON NULL,
|
|
`markup_percent` DECIMAL(5, 1) NOT NULL DEFAULT 0.0,
|
|
`valid_until` DATETIME(3) NULL,
|
|
`error_code` VARCHAR(32) NULL,
|
|
`cc_customer_id` VARCHAR(32) NULL,
|
|
`forecast_id` VARCHAR(32) NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT false,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `quote_record_quote_id_key`(`quote_id`),
|
|
INDEX `idx_customer_created`(`customer_id`, `created_at`),
|
|
INDEX `idx_cargo_hash`(`cargo_hash`),
|
|
INDEX `idx_request`(`request_id`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `idempotency_record` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`request_id` VARCHAR(36) NOT NULL,
|
|
`customer_id` VARCHAR(32) NOT NULL,
|
|
`quote_id` VARCHAR(32) NOT NULL,
|
|
`expire_at` DATETIME(3) NOT NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
UNIQUE INDEX `idempotency_record_request_id_key`(`request_id`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `quote_cache_meta` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`cargo_hash` CHAR(32) NOT NULL,
|
|
`raw_total_standard` DECIMAL(12, 2) NOT NULL,
|
|
`raw_total_guaranteed` DECIMAL(12, 2) NOT NULL,
|
|
`last_quotes_json` JSON NOT NULL,
|
|
`refreshed_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
UNIQUE INDEX `quote_cache_meta_cargo_hash_key`(`cargo_hash`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `markup_config` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`customer_id` VARCHAR(32) NOT NULL,
|
|
`markup_percent` DECIMAL(5, 1) NOT NULL,
|
|
`operator_id` VARCHAR(32) NOT NULL,
|
|
`remark` VARCHAR(255) NULL,
|
|
`is_deleted` BOOLEAN NOT NULL DEFAULT false,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
|
UNIQUE INDEX `markup_config_customer_id_key`(`customer_id`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `alert_log` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`alert_type` VARCHAR(32) NOT NULL,
|
|
`quote_id` VARCHAR(32) NULL,
|
|
`cargo_hash` CHAR(32) NULL,
|
|
`detail_json` JSON NULL,
|
|
`status` VARCHAR(16) NOT NULL DEFAULT 'open',
|
|
`resolver_id` VARCHAR(32) NULL,
|
|
`resolved_at` DATETIME(3) NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
INDEX `idx_type_status`(`alert_type`, `status`),
|
|
INDEX `idx_created`(`created_at`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- CreateTable
|
|
CREATE TABLE `audit_log` (
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
`action` VARCHAR(64) NOT NULL,
|
|
`actor_id` VARCHAR(32) NOT NULL,
|
|
`resource` VARCHAR(128) NULL,
|
|
`detail_json` JSON NULL,
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
|
INDEX `idx_actor_created`(`actor_id`, `created_at`),
|
|
INDEX `idx_action_created`(`action`, `created_at`),
|
|
PRIMARY KEY (`id`)
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
-- PRD §9.4：加价比例 0~30% 硬约束
|
|
ALTER TABLE `markup_config` ADD CONSTRAINT `chk_markup` CHECK (`markup_percent` >= 0 AND `markup_percent` <= 30.0);
|