140 lines
7.3 KiB
SQL
140 lines
7.3 KiB
SQL
SET NAMES utf8mb4 COLLATE utf8mb4_unicode_ci;
|
||
|
||
-- 使用 utf8mb4 连接字符集，保证中文注释、中文名称和 emoji 在导入时不被降级。
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_users (
|
||
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
|
||
username VARCHAR(64) NOT NULL UNIQUE COMMENT '用户名',
|
||
name VARCHAR(80) NOT NULL COMMENT '名称',
|
||
password_hash VARCHAR(255) NOT NULL COMMENT '密码哈希值',
|
||
team VARCHAR(80) COMMENT '所属团队',
|
||
status VARCHAR(24) NOT NULL DEFAULT 'active' COMMENT '业务状态',
|
||
mfa_enabled BOOLEAN NOT NULL DEFAULT FALSE COMMENT '是否启用多因素认证',
|
||
last_login_at_ms BIGINT NULL COMMENT '最后登录时间，UTC epoch ms',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
INDEX idx_admin_users_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台用户表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_roles (
|
||
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
|
||
name VARCHAR(80) NOT NULL UNIQUE COMMENT '名称',
|
||
code VARCHAR(80) NOT NULL UNIQUE COMMENT '编码',
|
||
description VARCHAR(255) COMMENT '描述信息',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms'
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台角色表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_permissions (
|
||
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
|
||
name VARCHAR(100) NOT NULL COMMENT '名称',
|
||
code VARCHAR(100) NOT NULL UNIQUE COMMENT '编码',
|
||
kind VARCHAR(32) NOT NULL COMMENT '分类',
|
||
description VARCHAR(255) COMMENT '描述信息',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
INDEX idx_admin_permissions_kind (kind)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台权限表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_user_roles (
|
||
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户 ID',
|
||
role_id BIGINT UNSIGNED NOT NULL COMMENT '角色 ID',
|
||
PRIMARY KEY (user_id, role_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台用户角色表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_role_permissions (
|
||
role_id BIGINT UNSIGNED NOT NULL COMMENT '角色 ID',
|
||
permission_id BIGINT UNSIGNED NOT NULL COMMENT '权限 ID',
|
||
PRIMARY KEY (role_id, permission_id)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台角色权限表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_menus (
|
||
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
|
||
parent_id BIGINT UNSIGNED NULL COMMENT '父级 ID',
|
||
title VARCHAR(80) NOT NULL COMMENT '标题',
|
||
code VARCHAR(100) NOT NULL UNIQUE COMMENT '编码',
|
||
path VARCHAR(160) COMMENT '路由路径',
|
||
icon VARCHAR(64) COMMENT '图标名称',
|
||
permission_code VARCHAR(100) COMMENT '权限编码',
|
||
sort INT NOT NULL DEFAULT 0 COMMENT '排序值',
|
||
visible BOOLEAN NOT NULL DEFAULT TRUE COMMENT '是否可见',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
INDEX idx_admin_menus_parent_id (parent_id),
|
||
INDEX idx_admin_menus_permission_code (permission_code)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台菜单表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_refresh_tokens (
|
||
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
|
||
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户 ID',
|
||
token_hash VARCHAR(128) NOT NULL UNIQUE COMMENT '令牌哈希值',
|
||
user_agent VARCHAR(255) COMMENT '用户代理',
|
||
ip VARCHAR(64) COMMENT 'IP',
|
||
expires_at_ms BIGINT NOT NULL COMMENT '过期时间，UTC epoch ms',
|
||
revoked_at_ms BIGINT NULL COMMENT '吊销时间，UTC epoch ms',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
INDEX idx_admin_refresh_tokens_user_id (user_id),
|
||
INDEX idx_admin_refresh_tokens_expires_at (expires_at_ms)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台刷新令牌表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_login_logs (
|
||
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
|
||
username VARCHAR(64) COMMENT '用户名',
|
||
user_id BIGINT UNSIGNED NULL COMMENT '用户 ID',
|
||
ip VARCHAR(64) COMMENT 'IP',
|
||
user_agent VARCHAR(255) COMMENT '用户代理',
|
||
status VARCHAR(32) NOT NULL COMMENT '业务状态',
|
||
message VARCHAR(255) COMMENT '消息',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
INDEX idx_admin_login_logs_username (username),
|
||
INDEX idx_admin_login_logs_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台登录日志表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_operation_logs (
|
||
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
|
||
user_id BIGINT UNSIGNED NOT NULL COMMENT '用户 ID',
|
||
username VARCHAR(64) NOT NULL COMMENT '用户名',
|
||
action VARCHAR(80) NOT NULL COMMENT '操作',
|
||
resource VARCHAR(120) COMMENT '资源',
|
||
method VARCHAR(12) COMMENT '方法',
|
||
path VARCHAR(180) COMMENT '路由路径',
|
||
ip VARCHAR(64) COMMENT 'IP',
|
||
status VARCHAR(32) NOT NULL COMMENT '业务状态',
|
||
detail TEXT COMMENT '详情',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
INDEX idx_admin_operation_logs_user_id (user_id),
|
||
INDEX idx_admin_operation_logs_action (action)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台操作日志表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_notifications (
|
||
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
|
||
title VARCHAR(120) NOT NULL COMMENT '标题',
|
||
content VARCHAR(500) COMMENT '内容',
|
||
level VARCHAR(24) NOT NULL DEFAULT 'info' COMMENT '等级',
|
||
read_at_ms BIGINT NULL COMMENT '读取时间，UTC epoch ms',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
INDEX idx_admin_notifications_level (level)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台通知表';
|
||
|
||
CREATE TABLE IF NOT EXISTS admin_service_assets (
|
||
id BIGINT UNSIGNED PRIMARY KEY AUTO_INCREMENT COMMENT '主键 ID',
|
||
code VARCHAR(80) NOT NULL UNIQUE COMMENT '编码',
|
||
name VARCHAR(120) NOT NULL COMMENT '名称',
|
||
description VARCHAR(255) COMMENT '描述信息',
|
||
status VARCHAR(24) NOT NULL COMMENT '业务状态',
|
||
deploy VARCHAR(80) COMMENT '部署',
|
||
version VARCHAR(40) COMMENT '版本号',
|
||
port VARCHAR(32) COMMENT '端口',
|
||
replicas VARCHAR(32) COMMENT '副本数',
|
||
response VARCHAR(32) COMMENT '响应',
|
||
cpu INT NOT NULL DEFAULT 0 COMMENT 'CPU',
|
||
memory INT NOT NULL DEFAULT 0 COMMENT '内存',
|
||
trend_json TEXT COMMENT '趋势 JSON 配置或快照',
|
||
logs_json TEXT COMMENT '日志 JSON 配置或快照',
|
||
created_at_ms BIGINT NOT NULL COMMENT '创建时间，UTC epoch ms',
|
||
updated_at_ms BIGINT NOT NULL COMMENT '更新时间，UTC epoch ms',
|
||
INDEX idx_admin_service_assets_status (status)
|
||
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='后台服务资产表';
|