forked from lunanting/cangku
420 lines
21 KiB
SQL
420 lines
21 KiB
SQL
-- =====================================================
|
||
-- 数据库初始化脚本
|
||
-- 数据库: shujuku2_system
|
||
-- =====================================================
|
||
|
||
SET NAMES utf8mb4;
|
||
|
||
-- 创建数据库
|
||
CREATE DATABASE IF NOT EXISTS shujuku2_system DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
USE shujuku2_system;
|
||
|
||
-- =====================================================
|
||
-- 1. 用户表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS users;
|
||
CREATE TABLE users (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '用户ID',
|
||
username VARCHAR(50) NOT NULL COMMENT '用户名',
|
||
password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希',
|
||
name VARCHAR(50) NOT NULL COMMENT '姓名',
|
||
email VARCHAR(100) NOT NULL COMMENT '邮箱',
|
||
department_id BIGINT COMMENT '部门ID',
|
||
role ENUM('admin', 'analyst', 'user', 'guest') DEFAULT 'user' COMMENT '角色',
|
||
status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
|
||
auth_source VARCHAR(20) NOT NULL DEFAULT 'local' COMMENT '最近登录来源',
|
||
avatar_url VARCHAR(500) COMMENT '头像地址',
|
||
mobile VARCHAR(30) COMMENT '手机号',
|
||
job_title VARCHAR(100) COMMENT '职位',
|
||
dingtalk_user_id VARCHAR(128) COMMENT '钉钉用户ID',
|
||
dingtalk_union_id VARCHAR(128) COMMENT '钉钉UnionID',
|
||
google_sub VARCHAR(128) COMMENT 'Google账号唯一标识',
|
||
department_names TEXT COMMENT '外部部门信息',
|
||
dingtalk_department_ids TEXT COMMENT '钉钉部门ID列表(JSON)',
|
||
last_login_at DATETIME COMMENT '最后登录时间',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_username (username),
|
||
UNIQUE KEY uk_email (email),
|
||
INDEX idx_department (department_id),
|
||
INDEX idx_role (role),
|
||
INDEX idx_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
|
||
|
||
-- 插入默认管理员用户 (密码: admin123)
|
||
INSERT INTO users (username, password_hash, name, email, role, status) VALUES
|
||
('admin', '$2a$10$N.zmdr9k7uOCQb376NoUnuTJ8iAt6Z5EHsM8lE9lBOsl7iKTVKIUi', '系统管理员', 'admin@company.com', 'admin', 'active');
|
||
|
||
-- =====================================================
|
||
-- 2. 部门表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS departments;
|
||
CREATE TABLE departments (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '部门ID',
|
||
parent_id BIGINT DEFAULT 0 COMMENT '父部门ID',
|
||
name VARCHAR(100) NOT NULL COMMENT '部门名称',
|
||
code VARCHAR(50) NOT NULL COMMENT '部门编码',
|
||
sort INT DEFAULT 0 COMMENT '排序',
|
||
status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
|
||
source VARCHAR(20) NOT NULL DEFAULT 'local' COMMENT '来源',
|
||
external_id VARCHAR(64) COMMENT '外部部门ID',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
INDEX idx_parent (parent_id),
|
||
INDEX idx_code (code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门表';
|
||
|
||
-- 插入示例部门
|
||
INSERT INTO departments (name, code, sort) VALUES
|
||
('总公司', 'ROOT', 1),
|
||
('技术部', 'TECH', 2),
|
||
('市场部', 'SALES', 3),
|
||
('财务部', 'FINANCE', 4),
|
||
('运营部', 'OPS', 5);
|
||
|
||
-- =====================================================
|
||
-- 3. 权限表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS permissions;
|
||
CREATE TABLE permissions (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '权限ID',
|
||
page_key VARCHAR(50) NOT NULL COMMENT '页面标识',
|
||
page_name VARCHAR(100) NOT NULL COMMENT '页面名称',
|
||
role VARCHAR(20) NOT NULL COMMENT '角色',
|
||
allowed BOOLEAN DEFAULT TRUE COMMENT '是否允许',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
UNIQUE KEY uk_page_role (page_key, role)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='权限表';
|
||
|
||
-- 插入默认权限配置
|
||
INSERT INTO permissions (page_key, page_name, role, allowed) VALUES
|
||
('dashboard', '仪表盘', 'admin', TRUE),
|
||
('dashboard', '仪表盘', 'analyst', TRUE),
|
||
('dashboard', '仪表盘', 'user', TRUE),
|
||
('dashboard', '仪表盘', 'guest', TRUE),
|
||
('analysis_summary', '监控', 'admin', TRUE),
|
||
('analysis_summary', '监控', 'analyst', TRUE),
|
||
('analysis_summary', '监控', 'user', TRUE),
|
||
('analysis_summary', '监控', 'guest', TRUE),
|
||
('analysis_customer_service', '客服', 'admin', TRUE),
|
||
('analysis_customer_service', '客服', 'analyst', TRUE),
|
||
('analysis_customer_service', '客服', 'user', TRUE),
|
||
('analysis_customer_service', '客服', 'guest', TRUE),
|
||
('analysis_product_design', '开发', 'admin', TRUE),
|
||
('analysis_product_design', '开发', 'analyst', TRUE),
|
||
('analysis_product_design', '开发', 'user', TRUE),
|
||
('analysis_product_design', '开发', 'guest', TRUE),
|
||
('analysis_logistics', '快递', 'admin', TRUE),
|
||
('analysis_logistics', '快递', 'analyst', TRUE),
|
||
('analysis_logistics', '快递', 'user', TRUE),
|
||
('analysis_logistics', '快递', 'guest', TRUE),
|
||
('analysis_warehouse', '仓库', 'admin', TRUE),
|
||
('analysis_warehouse', '仓库', 'analyst', TRUE),
|
||
('analysis_warehouse', '仓库', 'user', TRUE),
|
||
('analysis_warehouse', '仓库', 'guest', TRUE),
|
||
('analysis_supplier', '供应', 'admin', TRUE),
|
||
('analysis_supplier', '供应', 'analyst', TRUE),
|
||
('analysis_supplier', '供应', 'user', TRUE),
|
||
('analysis_supplier', '供应', 'guest', TRUE),
|
||
('analysis_procurement', '采购', 'admin', TRUE),
|
||
('analysis_procurement', '采购', 'analyst', TRUE),
|
||
('analysis_procurement', '采购', 'user', TRUE),
|
||
('analysis_procurement', '采购', 'guest', TRUE),
|
||
('analysis_region', '地区', 'admin', TRUE),
|
||
('analysis_region', '地区', 'analyst', TRUE),
|
||
('analysis_region', '地区', 'user', TRUE),
|
||
('analysis_region', '地区', 'guest', TRUE),
|
||
('reports', '报表管理', 'admin', TRUE),
|
||
('reports', '报表管理', 'analyst', TRUE),
|
||
('reports', '报表管理', 'user', FALSE),
|
||
('reports', '报表管理', 'guest', FALSE),
|
||
('export', '数据导出', 'admin', TRUE),
|
||
('export', '数据导出', 'analyst', TRUE),
|
||
('export', '数据导出', 'user', FALSE),
|
||
('export', '数据导出', 'guest', FALSE),
|
||
('alerts', '预警配置', 'admin', TRUE),
|
||
('alerts', '预警配置', 'analyst', FALSE),
|
||
('alerts', '预警配置', 'user', FALSE),
|
||
('alerts', '预警配置', 'guest', FALSE),
|
||
('users', '用户管理', 'admin', TRUE),
|
||
('users', '用户管理', 'analyst', FALSE),
|
||
('users', '用户管理', 'user', FALSE),
|
||
('users', '用户管理', 'guest', FALSE),
|
||
('settings', '系统设置', 'admin', TRUE),
|
||
('settings', '系统设置', 'analyst', FALSE),
|
||
('settings', '系统设置', 'user', FALSE),
|
||
('settings', '系统设置', 'guest', FALSE);
|
||
|
||
-- =====================================================
|
||
-- 4. 系统配置表
|
||
-- =====================================================
|
||
CREATE TABLE IF NOT EXISTS system_config (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
|
||
config_key VARCHAR(100) NOT NULL COMMENT '配置键',
|
||
config_value TEXT COMMENT '配置值',
|
||
config_type VARCHAR(20) DEFAULT 'string' COMMENT '配置类型',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_config_key (config_key)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统配置表';
|
||
|
||
-- 插入默认配置
|
||
INSERT IGNORE INTO system_config (config_key, config_value, config_type) VALUES
|
||
('site_name', '拼多多自动化运营平台', 'string'),
|
||
('site_description', '数据分析平台', 'string'),
|
||
('logo', '', 'string'),
|
||
('favicon', '', 'string'),
|
||
('copyright', '© 2024 公司名称. All rights reserved.', 'string'),
|
||
('enable_alert', 'true', 'boolean'),
|
||
('enable_export', 'true', 'boolean'),
|
||
('enable_sso', 'true', 'boolean'),
|
||
('default_theme', 'light', 'string'),
|
||
('setup_completed', 'false', 'boolean'),
|
||
('setup_completed_at', '', 'string');
|
||
|
||
-- 专用服务密钥与普通配置隔离，升级时不得删除。
|
||
CREATE TABLE IF NOT EXISTS system_secrets (
|
||
secret_key VARCHAR(120) PRIMARY KEY COMMENT '密钥用途',
|
||
secret_value TEXT NOT NULL COMMENT '仅服务端读取的密钥值',
|
||
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='系统持久密钥';
|
||
|
||
-- =====================================================
|
||
-- 5. 部门功能页面权限表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS department_page_permissions;
|
||
CREATE TABLE department_page_permissions (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '部门页面权限ID',
|
||
department_id BIGINT NOT NULL COMMENT '部门ID',
|
||
page_key VARCHAR(100) NOT NULL COMMENT '页面标识',
|
||
page_name VARCHAR(100) NOT NULL COMMENT '页面名称',
|
||
allowed BOOLEAN DEFAULT TRUE COMMENT '是否允许访问',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_department_page (department_id, page_key),
|
||
INDEX idx_department_id (department_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='部门功能页面权限表';
|
||
|
||
-- =====================================================
|
||
-- 6. 认证配置表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS auth_configs;
|
||
CREATE TABLE auth_configs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
|
||
auth_base_url VARCHAR(255) COMMENT '认证回调基础地址',
|
||
dingtalk_enabled BOOLEAN DEFAULT FALSE COMMENT '是否启用钉钉登录',
|
||
dingtalk_app_key VARCHAR(255) COMMENT '钉钉AppKey',
|
||
dingtalk_app_secret VARCHAR(255) COMMENT '钉钉AppSecret',
|
||
dingtalk_sync_root_dept_id BIGINT DEFAULT 1 COMMENT '钉钉同步根部门ID',
|
||
google_enabled BOOLEAN DEFAULT FALSE COMMENT '是否启用Google登录',
|
||
google_client_id VARCHAR(255) COMMENT 'Google Client ID',
|
||
google_client_secret VARCHAR(255) COMMENT 'Google Client Secret',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='认证配置表';
|
||
|
||
-- =====================================================
|
||
-- 6.1 Redis 配置表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS redis_configs;
|
||
CREATE TABLE redis_configs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
|
||
profile_key VARCHAR(64) NOT NULL DEFAULT 'default' COMMENT 'Redis配置标识：default/changzhou',
|
||
profile_name VARCHAR(100) COMMENT 'Redis配置名称',
|
||
host VARCHAR(255) COMMENT 'Redis主机地址',
|
||
port INT NOT NULL DEFAULT 6379 COMMENT 'Redis端口',
|
||
password VARCHAR(255) COMMENT 'Redis密码',
|
||
db INT NOT NULL DEFAULT 0 COMMENT 'Redis数据库编号',
|
||
enabled TINYINT(1) NOT NULL DEFAULT 1 COMMENT '是否启用',
|
||
is_default BOOLEAN DEFAULT TRUE COMMENT '是否默认',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
INDEX idx_redis_configs_default (is_default),
|
||
INDEX idx_redis_configs_profile_key (profile_key)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='Redis配置表';
|
||
|
||
-- =====================================================
|
||
-- 7. 预警规则表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS alert_rules;
|
||
CREATE TABLE alert_rules (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '规则ID',
|
||
name VARCHAR(100) NOT NULL COMMENT '规则名称',
|
||
cube_name VARCHAR(50) NOT NULL COMMENT 'Cube模型名',
|
||
measure_name VARCHAR(100) NOT NULL COMMENT '监控指标',
|
||
dimension_filter JSON COMMENT '维度过滤条件',
|
||
`condition` VARCHAR(20) NOT NULL COMMENT '条件: gt/lt/eq/gte/lte',
|
||
threshold DECIMAL(18,4) NOT NULL COMMENT '阈值',
|
||
time_granularity VARCHAR(20) COMMENT '时间粒度',
|
||
time_range VARCHAR(50) COMMENT '时间范围',
|
||
notify_channels JSON COMMENT '通知渠道',
|
||
severity ENUM('low', 'medium', 'high', 'critical') DEFAULT 'medium' COMMENT '严重程度',
|
||
status ENUM('active', 'paused') DEFAULT 'active' COMMENT '状态',
|
||
creator_id BIGINT COMMENT '创建者ID',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
INDEX idx_status (status),
|
||
INDEX idx_cube (cube_name)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预警规则表';
|
||
|
||
-- =====================================================
|
||
-- 8. 预警记录表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS alert_records;
|
||
CREATE TABLE alert_records (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '记录ID',
|
||
rule_id BIGINT NOT NULL COMMENT '规则ID',
|
||
triggered_at DATETIME NOT NULL COMMENT '触发时间',
|
||
actual_value DECIMAL(18,4) NOT NULL COMMENT '实际值',
|
||
threshold DECIMAL(18,4) NOT NULL COMMENT '阈值',
|
||
status ENUM('triggered', 'acknowledged', 'resolved') DEFAULT 'triggered' COMMENT '状态',
|
||
notified_channels JSON COMMENT '已通知渠道',
|
||
notification_results JSON COMMENT '通知结果',
|
||
acknowledged_by BIGINT COMMENT '确认人ID',
|
||
acknowledged_at DATETIME COMMENT '确认时间',
|
||
resolved_at DATETIME COMMENT '解决时间',
|
||
INDEX idx_rule_triggered (rule_id, triggered_at),
|
||
INDEX idx_status (status),
|
||
INDEX idx_triggered (triggered_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='预警记录表';
|
||
|
||
-- =====================================================
|
||
-- 9. 通知渠道表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS notification_channels;
|
||
CREATE TABLE notification_channels (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '渠道ID',
|
||
user_id BIGINT NOT NULL COMMENT '用户ID',
|
||
channel_type ENUM('wechat', 'email', 'sms', 'dingtalk') NOT NULL COMMENT '渠道类型',
|
||
channel_value VARCHAR(255) NOT NULL COMMENT '渠道值(邮箱/手机/企业微信ID)',
|
||
is_enabled BOOLEAN DEFAULT TRUE COMMENT '是否启用',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
INDEX idx_user (user_id),
|
||
INDEX idx_type (channel_type)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='通知渠道表';
|
||
|
||
-- =====================================================
|
||
-- 10. S3 配置表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS s3_configs;
|
||
CREATE TABLE s3_configs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
|
||
provider VARCHAR(20) NOT NULL COMMENT '提供商: aws/aliyun/tencent/qiniu/minio/custom',
|
||
endpoint VARCHAR(255) COMMENT 'S3 Endpoint',
|
||
region VARCHAR(50) COMMENT 'S3区域',
|
||
zone VARCHAR(32) COMMENT '厂商区域短码，例如 z0',
|
||
protocol VARCHAR(10) NOT NULL DEFAULT 'https' COMMENT '访问协议',
|
||
storage_class VARCHAR(32) NOT NULL DEFAULT 'STANDARD' COMMENT '默认存储类型',
|
||
bucket VARCHAR(100) NOT NULL COMMENT '存储桶名称',
|
||
access_key_id VARCHAR(255) NOT NULL COMMENT 'Access Key ID',
|
||
secret_access_key VARCHAR(255) NOT NULL COMMENT 'Secret Access Key',
|
||
custom_domain VARCHAR(255) COMMENT '自定义域名',
|
||
cdn_url VARCHAR(255) COMMENT 'CDN地址',
|
||
is_default BOOLEAN DEFAULT FALSE COMMENT '是否默认',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='S3配置表';
|
||
|
||
-- =====================================================
|
||
-- 11. 数据库配置表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS database_configs;
|
||
CREATE TABLE database_configs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
|
||
name VARCHAR(100) NOT NULL COMMENT '配置名称',
|
||
driver VARCHAR(20) NOT NULL COMMENT '驱动: mysql/postgresql',
|
||
host VARCHAR(255) NOT NULL COMMENT '主机地址',
|
||
port INT NOT NULL DEFAULT 3306 COMMENT '端口',
|
||
database_name VARCHAR(100) NOT NULL COMMENT '数据库名',
|
||
username VARCHAR(100) NOT NULL COMMENT '用户名',
|
||
password VARCHAR(255) COMMENT '密码',
|
||
`ssl` BOOLEAN DEFAULT TRUE COMMENT '是否SSL',
|
||
ssl_ca TEXT COMMENT 'SSL CA证书',
|
||
max_pool_size INT DEFAULT 10 COMMENT '最大连接池',
|
||
query_timeout INT DEFAULT 300 COMMENT '查询超时(秒)',
|
||
use_env_config BOOLEAN DEFAULT FALSE COMMENT '是否优先使用 .env 业务数据库配置',
|
||
is_default BOOLEAN DEFAULT FALSE COMMENT '是否默认',
|
||
status ENUM('active', 'inactive') DEFAULT 'active' COMMENT '状态',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_at DATETIME DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='数据库配置表';
|
||
|
||
-- =====================================================
|
||
-- 12. 操作日志表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS operation_logs;
|
||
CREATE TABLE operation_logs (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '日志ID',
|
||
user_id BIGINT COMMENT '用户ID',
|
||
action VARCHAR(50) NOT NULL COMMENT '操作类型',
|
||
module VARCHAR(50) NOT NULL COMMENT '模块',
|
||
description TEXT COMMENT '操作描述',
|
||
ip_address VARCHAR(45) COMMENT 'IP地址',
|
||
user_agent TEXT COMMENT 'User Agent',
|
||
request_params TEXT COMMENT '请求参数',
|
||
response_status INT COMMENT '响应状态',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
INDEX idx_user (user_id),
|
||
INDEX idx_action (action),
|
||
INDEX idx_module (module),
|
||
INDEX idx_created (created_at)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='操作日志表';
|
||
|
||
-- =====================================================
|
||
-- 11. KPI目标值配置表
|
||
-- =====================================================
|
||
DROP TABLE IF EXISTS sys_kpi_targets;
|
||
CREATE TABLE sys_kpi_targets (
|
||
id BIGINT PRIMARY KEY AUTO_INCREMENT COMMENT '配置ID',
|
||
page_code VARCHAR(50) NOT NULL COMMENT '页面编码',
|
||
kpi_code VARCHAR(50) NOT NULL COMMENT '指标编码',
|
||
kpi_name VARCHAR(100) NOT NULL COMMENT '指标名称',
|
||
unit VARCHAR(20) DEFAULT '' COMMENT '单位',
|
||
direction VARCHAR(10) DEFAULT 'lower' COMMENT '越低越好lower/越高越好higher',
|
||
target_good DECIMAL(18, 4) COMMENT '优秀线',
|
||
target_pass DECIMAL(18, 4) COMMENT '达标线',
|
||
target_warn DECIMAL(18, 4) COMMENT '预警线',
|
||
is_enabled TINYINT(1) DEFAULT 1 COMMENT '是否启用',
|
||
sort_order INT DEFAULT 0 COMMENT '排序',
|
||
remark VARCHAR(200) DEFAULT '' COMMENT '备注',
|
||
created_by BIGINT COMMENT '创建人',
|
||
created_at DATETIME DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
|
||
updated_by BIGINT COMMENT '更新人',
|
||
updated_at DATETIME DEFAULT NULL ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间',
|
||
UNIQUE KEY uk_page_kpi (page_code, kpi_code),
|
||
INDEX idx_page (page_code),
|
||
INDEX idx_enabled (is_enabled)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='KPI目标值配置表';
|
||
|
||
-- 插入默认KPI目标值配置
|
||
INSERT INTO sys_kpi_targets (page_code, kpi_code, kpi_name, unit, direction, target_good, target_pass, target_warn, sort_order) VALUES
|
||
-- 客服分析
|
||
('customerService', 'refundProcessTime', '退款处理时长', 'h', 'lower', 12, 24, 48, 1),
|
||
('customerService', 'disputeRate', '纠纷判责率', '%', 'lower', 10, 20, 30, 2),
|
||
('customerService', 'compensationRate', '赔付率', '%', 'lower', 0.3, 0.5, 1, 3),
|
||
('customerService', 'avgCompensation', '单笔平均赔付', '元', 'lower', 30, 50, 80, 4),
|
||
('customerService', 'labelRate', '打标完成率', '%', 'higher', 98, 90, 80, 5),
|
||
-- 仓库分析
|
||
('warehouse', 'sameDayShipRate', '当日发货率', '%', 'higher', 98, 95, 90, 1),
|
||
('warehouse', 'wrongShipRate', '错发漏发率', '%', 'lower', 0.2, 0.5, 1, 2),
|
||
('warehouse', 'nextDayRate', '次日达占比', '%', 'higher', 50, 30, 20, 3),
|
||
('warehouse', 'inventoryTurnover', '库存周转率', '次/月', 'higher', 10, 6, 4, 4),
|
||
-- 快递分析
|
||
('logistics', 'pickupRate24h', '24h揽收及时率', '%', 'higher', 98, 95, 90, 1),
|
||
('logistics', 'avgDeliveryTime', '平均物流时长', 'h', 'lower', 48, 72, 96, 2),
|
||
('logistics', 'abnormalRate', '物流异常率', '%', 'lower', 1, 2, 5, 3),
|
||
('logistics', 'lostDamageRate', '丢件破损率', '%', 'lower', 0.2, 0.5, 1, 4),
|
||
-- 供应商分析
|
||
('supplier', 'qualityRefundRate', '品质退款率', '%', 'lower', 1.5, 3, 5, 1),
|
||
('supplier', 'defectRate', '次品率', '%', 'lower', 1, 2, 3, 2),
|
||
('supplier', 'shipTimelyRate', '发货及时率', '%', 'higher', 98, 95, 90, 3),
|
||
-- 采购分析
|
||
('procurement', 'purchaseRefundRate', '采购SKU退款率', '%', 'lower', 1.5, 3, 5, 1),
|
||
('procurement', 'dropshipTimelyRate', '代发准时率', '%', 'higher', 98, 95, 90, 2),
|
||
('procurement', 'stockoutImpactRate', '缺货影响订单率', '%', 'lower', 0.5, 1, 2, 3),
|
||
-- 开发分析
|
||
('productDesign', 'designAttributionRate', '开发归因退款率', '%', 'lower', 5, 8, 12, 1),
|
||
('productDesign', 'firstQualityReturnRate', '首次品退率', '%', 'lower', 1, 2, 3, 2),
|
||
('productDesign', 'visualIssueRate', '视觉问题率', '%', 'lower', 2, 5, 8, 3);
|