|
|
-- =============================================================================
|
|
|
-- 查价中台 chajia 数据库 — 宝塔 / phpMyAdmin 导入用
|
|
|
-- 字符集：utf8mb4 / utf8mb4_unicode_ci
|
|
|
-- 用法：在宝塔已创建空库 chajia（utf8mb4）后，导入本文件
|
|
|
-- 默认管理员：admin_demo / kj123456（上线后请立即改密）
|
|
|
-- 生成时间：2026-06-29
|
|
|
-- =============================================================================
|
|
|
|
|
|
SET NAMES utf8mb4;
|
|
|
SET CHARACTER SET utf8mb4;
|
|
|
SET FOREIGN_KEY_CHECKS = 0;
|
|
|
|
|
|
DROP TABLE IF EXISTS `audit_log`;
|
|
|
DROP TABLE IF EXISTS `alert_log`;
|
|
|
DROP TABLE IF EXISTS `markup_config`;
|
|
|
DROP TABLE IF EXISTS `quote_cache_meta`;
|
|
|
DROP TABLE IF EXISTS `idempotency_record`;
|
|
|
DROP TABLE IF EXISTS `quote_record`;
|
|
|
DROP TABLE IF EXISTS `sys_user`;
|
|
|
DROP TABLE IF EXISTS `_prisma_migrations`;
|
|
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
|
-- 表结构（与 prisma/schema.prisma 最新迁移一致）
|
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
|
|
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;
|
|
|
|
|
|
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, 2) NOT NULL DEFAULT 0.00,
|
|
|
`valid_until` DATETIME(3) NULL,
|
|
|
`error_code` VARCHAR(32) NULL,
|
|
|
`error_message` VARCHAR(512) 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;
|
|
|
|
|
|
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;
|
|
|
|
|
|
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;
|
|
|
|
|
|
CREATE TABLE `markup_config` (
|
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
`customer_id` VARCHAR(32) NOT NULL,
|
|
|
`markup_type` VARCHAR(16) NOT NULL DEFAULT 'percent',
|
|
|
`markup_percent` DECIMAL(5, 2) NOT NULL,
|
|
|
`markup_fixed_amount` DECIMAL(12, 2) 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`),
|
|
|
CONSTRAINT `chk_markup_percent` CHECK (
|
|
|
`markup_type` <> 'percent'
|
|
|
OR (`markup_percent` >= 0 AND `markup_percent` <= 30.0)
|
|
|
),
|
|
|
CONSTRAINT `chk_markup_fixed` CHECK (
|
|
|
`markup_type` <> 'fixed'
|
|
|
OR (
|
|
|
`markup_fixed_amount` IS NOT NULL
|
|
|
AND `markup_fixed_amount` >= 0
|
|
|
AND `markup_fixed_amount` <= 99999.99
|
|
|
)
|
|
|
)
|
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
|
|
CREATE TABLE `host_customer` (
|
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
`customer_id` VARCHAR(32) NOT NULL,
|
|
|
`name` VARCHAR(128) NOT NULL,
|
|
|
`status` VARCHAR(16) NOT NULL DEFAULT 'active',
|
|
|
`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 `host_customer_customer_id_key`(`customer_id`),
|
|
|
INDEX `idx_host_customer_status`(`status`, `is_deleted`),
|
|
|
PRIMARY KEY (`id`)
|
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
|
|
CREATE TABLE `service_api_key` (
|
|
|
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
|
|
|
`key_id` VARCHAR(32) NOT NULL,
|
|
|
`customer_id` VARCHAR(32) NOT NULL,
|
|
|
`token_hash` CHAR(64) NOT NULL,
|
|
|
`key_prefix` VARCHAR(16) NOT NULL,
|
|
|
`permissions` JSON NOT NULL,
|
|
|
`is_active` BOOLEAN NOT NULL DEFAULT true,
|
|
|
`created_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
`updated_at` DATETIME(3) NOT NULL,
|
|
|
UNIQUE INDEX `service_api_key_key_id_key`(`key_id`),
|
|
|
UNIQUE INDEX `service_api_key_token_hash_key`(`token_hash`),
|
|
|
INDEX `idx_service_api_key_customer`(`customer_id`, `is_active`),
|
|
|
PRIMARY KEY (`id`)
|
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
|
|
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;
|
|
|
|
|
|
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;
|
|
|
|
|
|
CREATE TABLE `_prisma_migrations` (
|
|
|
`id` VARCHAR(36) NOT NULL,
|
|
|
`checksum` VARCHAR(64) NOT NULL,
|
|
|
`finished_at` DATETIME(3) NULL,
|
|
|
`migration_name` VARCHAR(255) NOT NULL,
|
|
|
`logs` TEXT NULL,
|
|
|
`rolled_back_at` DATETIME(3) NULL,
|
|
|
`started_at` DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
|
|
|
`applied_steps_count` INTEGER UNSIGNED NOT NULL DEFAULT 0,
|
|
|
PRIMARY KEY (`id`)
|
|
|
) DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
|
|
|
|
|
-- ---------------------------------------------------------------------------
|
|
|
-- 初始数据
|
|
|
-- ---------------------------------------------------------------------------
|
|
|
|
|
|
INSERT INTO `sys_user` (
|
|
|
`user_id`, `username`, `password_hash`, `role`, `is_deleted`, `created_at`, `updated_at`
|
|
|
) VALUES (
|
|
|
'ADMIN_001',
|
|
|
'admin_demo',
|
|
|
'$2b$10$IlEahtFlDYHlbYv9/n6ETuy9BmmRiNWVAk4DYB5dlH787E7wxToAS',
|
|
|
'admin',
|
|
|
false,
|
|
|
NOW(3),
|
|
|
NOW(3)
|
|
|
);
|
|
|
|
|
|
INSERT INTO `_prisma_migrations` (
|
|
|
`id`, `checksum`, `finished_at`, `migration_name`, `logs`, `rolled_back_at`, `started_at`, `applied_steps_count`
|
|
|
) VALUES
|
|
|
(
|
|
|
'a1000001-0000-4000-8000-000000000001',
|
|
|
'd3b5b3138075270c923d3089496fb61727e1dc0b165e53d7811c7b5c350fff88',
|
|
|
NOW(3),
|
|
|
'20260616000000_init',
|
|
|
NULL,
|
|
|
NULL,
|
|
|
NOW(3),
|
|
|
1
|
|
|
),
|
|
|
(
|
|
|
'a1000002-0000-4000-8000-000000000002',
|
|
|
'692c72fc58455647451d7a6b623deeb9ca015a040b14bb75016ba5657f8cb018',
|
|
|
NOW(3),
|
|
|
'20260617120000_add_quote_error_message',
|
|
|
NULL,
|
|
|
NULL,
|
|
|
NOW(3),
|
|
|
1
|
|
|
),
|
|
|
(
|
|
|
'a1000003-0000-4000-8000-000000000003',
|
|
|
'2002cabd9c321cf2fd385ce756fe6f3c8e26ca63274ad8fb2938b8a0ab50f736',
|
|
|
NOW(3),
|
|
|
'20260624000000_markup_type_fixed',
|
|
|
NULL,
|
|
|
NULL,
|
|
|
NOW(3),
|
|
|
1
|
|
|
);
|
|
|
|
|
|
SET FOREIGN_KEY_CHECKS = 1;
|
|
|
|
|
|
-- 导入完成
|
