-- ============================================================================ -- 用户权限体系迁移脚本(RuoYi-Vue-Plus 核心 RBAC → perm_* 独立表) -- 来源:E:\workspace\ai\biz-buddy\code\back-end\script\sql\postgres\postgres_ry_vue.sql -- 目标库:PostgreSQL master(application-dev.yaml 中的 spring.datasource.dynamic.datasource.master) -- 执行方式:手工执行(项目无 Flyway / Liquibase) -- 幂等性:本脚本可重复执行 -- -- 范围(已确认的迁移决定): -- * 9 张核心 RBAC 表,全部 perm_ 前缀,与既有 sys_user 完全隔离; -- * 既有 sys_user 表保留不动(回滚兜底),登录链路改读本脚本的 perm_user; -- * 内置超管 admin(id=1) 由本脚本自包含种子写入(case_info.owner_id 挂 perm_user.user_id); -- * 融合阶段:业务菜单(1000 段)/工作台菜单(20000 段)/操作码(F) 全部入库, -- 导航改 getRouters 动态下发;common 角色默认授全部业务码(不回归),perm 管理子树除外; -- * 不含字典/参数/客户端/日志/OSS/通知(范围外); -- * 超管约定沿用 RuoYi:user_id=1 或持有 superadmin 角色 → 权限 *:*:*。 -- -- 注意:这是**平台库**表,不需要登记到 table_info / table_field。 -- ============================================================================ -- --------------------------------------------------------------------------- -- 1. 部门表 -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS perm_dept ( dept_id BIGINT PRIMARY KEY, parent_id BIGINT DEFAULT 0, ancestors VARCHAR(500) DEFAULT '', dept_name VARCHAR(30) DEFAULT '', dept_category VARCHAR(100) DEFAULT NULL, order_num INT DEFAULT 0, leader BIGINT DEFAULT NULL, phone VARCHAR(11) DEFAULT NULL, email VARCHAR(50) DEFAULT NULL, status CHAR(1) DEFAULT '0', del_flag CHAR(1) DEFAULT '0', create_dept BIGINT, create_by BIGINT, create_time TIMESTAMP, update_by BIGINT, update_time TIMESTAMP ); CREATE INDEX IF NOT EXISTS idx_perm_dept_parent_id ON perm_dept (parent_id); COMMENT ON TABLE perm_dept IS '部门表'; COMMENT ON COLUMN perm_dept.dept_id IS '部门ID'; COMMENT ON COLUMN perm_dept.parent_id IS '父部门ID'; COMMENT ON COLUMN perm_dept.ancestors IS '祖级列表'; COMMENT ON COLUMN perm_dept.dept_name IS '部门名称'; COMMENT ON COLUMN perm_dept.dept_category IS '部门类别编码'; COMMENT ON COLUMN perm_dept.order_num IS '显示顺序'; COMMENT ON COLUMN perm_dept.leader IS '负责人(perm_user.user_id)'; COMMENT ON COLUMN perm_dept.status IS '部门状态(0正常 1停用)'; COMMENT ON COLUMN perm_dept.del_flag IS '删除标志(0存在 1删除)'; -- --------------------------------------------------------------------------- -- 2. 用户信息表 -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS perm_user ( user_id BIGINT PRIMARY KEY, dept_id BIGINT, user_name VARCHAR(30) NOT NULL, nick_name VARCHAR(30) NOT NULL, user_type VARCHAR(10) DEFAULT 'sys_user', email VARCHAR(50) DEFAULT '', phone_number VARCHAR(11) DEFAULT '', gender CHAR(1) DEFAULT '0', avatar BIGINT, password VARCHAR(100) DEFAULT '', status CHAR(1) DEFAULT '0', del_flag CHAR(1) DEFAULT '0', login_ip VARCHAR(128) DEFAULT '', login_date TIMESTAMP, create_dept BIGINT, create_by BIGINT, create_time TIMESTAMP, update_by BIGINT, update_time TIMESTAMP, remark VARCHAR(500) DEFAULT NULL ); CREATE INDEX IF NOT EXISTS idx_perm_user_dept_id ON perm_user (dept_id); CREATE INDEX IF NOT EXISTS idx_perm_user_user_name ON perm_user (user_name); -- 与既有 sys_user 一致:用户名不区分大小写唯一 CREATE UNIQUE INDEX IF NOT EXISTS uk_perm_user_username ON perm_user (lower(user_name)) WHERE del_flag = '0'; COMMENT ON TABLE perm_user IS '用户信息表(权限体系,登录链路读本表)'; COMMENT ON COLUMN perm_user.user_id IS '用户ID'; COMMENT ON COLUMN perm_user.dept_id IS '部门ID'; COMMENT ON COLUMN perm_user.user_name IS '用户账号'; COMMENT ON COLUMN perm_user.nick_name IS '用户昵称'; COMMENT ON COLUMN perm_user.gender IS '用户性别(0男 1女 2未知)'; COMMENT ON COLUMN perm_user.avatar IS '头像地址'; COMMENT ON COLUMN perm_user.password IS 'BCrypt 密码哈希'; COMMENT ON COLUMN perm_user.status IS '账号状态(0正常 1停用)'; COMMENT ON COLUMN perm_user.del_flag IS '删除标志(0存在 1删除)'; COMMENT ON COLUMN perm_user.login_ip IS '最后登录IP'; COMMENT ON COLUMN perm_user.login_date IS '最后登录时间'; -- --------------------------------------------------------------------------- -- 3. 岗位信息表 -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS perm_post ( post_id BIGINT PRIMARY KEY, dept_id BIGINT, post_code VARCHAR(64) NOT NULL, post_category VARCHAR(100) DEFAULT NULL, post_name VARCHAR(50) NOT NULL, post_sort INT NOT NULL, status CHAR(1) NOT NULL, del_flag CHAR(1) DEFAULT '0', create_dept BIGINT, create_by BIGINT, create_time TIMESTAMP, update_by BIGINT, update_time TIMESTAMP, remark VARCHAR(500) DEFAULT NULL ); CREATE INDEX IF NOT EXISTS idx_perm_post_dept_id ON perm_post (dept_id); COMMENT ON TABLE perm_post IS '岗位信息表'; COMMENT ON COLUMN perm_post.post_id IS '岗位ID'; COMMENT ON COLUMN perm_post.dept_id IS '部门ID'; COMMENT ON COLUMN perm_post.post_code IS '岗位编码'; COMMENT ON COLUMN perm_post.post_category IS '岗位类别编码'; COMMENT ON COLUMN perm_post.post_name IS '岗位名称'; COMMENT ON COLUMN perm_post.post_sort IS '显示顺序'; COMMENT ON COLUMN perm_post.status IS '状态(0正常 1停用)'; COMMENT ON COLUMN perm_post.del_flag IS '删除标志(0存在 1删除)'; -- --------------------------------------------------------------------------- -- 4. 角色信息表 -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS perm_role ( role_id BIGINT PRIMARY KEY, role_name VARCHAR(30) NOT NULL, role_key VARCHAR(100) NOT NULL, role_sort INT NOT NULL, data_scope CHAR(1) DEFAULT '1', menu_check_strictly BOOLEAN DEFAULT TRUE, dept_check_strictly BOOLEAN DEFAULT TRUE, status CHAR(1) NOT NULL, del_flag CHAR(1) DEFAULT '0', create_dept BIGINT, create_by BIGINT, create_time TIMESTAMP, update_by BIGINT, update_time TIMESTAMP, remark VARCHAR(500) DEFAULT NULL ); COMMENT ON TABLE perm_role IS '角色信息表'; COMMENT ON COLUMN perm_role.role_id IS '角色ID'; COMMENT ON COLUMN perm_role.role_name IS '角色名称'; COMMENT ON COLUMN perm_role.role_key IS '角色权限字符串'; COMMENT ON COLUMN perm_role.role_sort IS '显示顺序'; COMMENT ON COLUMN perm_role.data_scope IS '数据范围(1全部 2自定 3本部门 4本部门及以下 5仅本人 6部门及以下或本人);本期仅存储与配置,SQL 过滤融合时启用'; COMMENT ON COLUMN perm_role.menu_check_strictly IS '菜单树选择项是否关联显示'; COMMENT ON COLUMN perm_role.dept_check_strictly IS '部门树选择项是否关联显示'; COMMENT ON COLUMN perm_role.status IS '角色状态(0正常 1停用)'; COMMENT ON COLUMN perm_role.del_flag IS '删除标志(0存在 1删除)'; -- --------------------------------------------------------------------------- -- 5. 菜单权限表 -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS perm_menu ( menu_id BIGINT PRIMARY KEY, menu_name VARCHAR(50) NOT NULL, parent_id BIGINT DEFAULT 0, order_num INT DEFAULT 0, path VARCHAR(200) DEFAULT '', component VARCHAR(255) DEFAULT NULL, query_param VARCHAR(255) DEFAULT NULL, is_frame CHAR(1) DEFAULT 'N', is_cache CHAR(1) DEFAULT 'Y', menu_type CHAR(1) DEFAULT '', visible CHAR(1) DEFAULT '0', status CHAR(1) DEFAULT '0', perms VARCHAR(100) DEFAULT NULL, icon VARCHAR(100) DEFAULT '#', active_menu VARCHAR(255) DEFAULT '', ext VARCHAR(2000) DEFAULT '', create_dept BIGINT, create_by BIGINT, create_time TIMESTAMP, update_by BIGINT, update_time TIMESTAMP, remark VARCHAR(500) DEFAULT '' ); COMMENT ON TABLE perm_menu IS '菜单权限表'; COMMENT ON COLUMN perm_menu.menu_id IS '菜单ID'; COMMENT ON COLUMN perm_menu.menu_name IS '菜单名称'; COMMENT ON COLUMN perm_menu.parent_id IS '父菜单ID'; COMMENT ON COLUMN perm_menu.order_num IS '显示顺序'; COMMENT ON COLUMN perm_menu.path IS '路由地址'; COMMENT ON COLUMN perm_menu.component IS '组件路径'; COMMENT ON COLUMN perm_menu.query_param IS '路由参数'; COMMENT ON COLUMN perm_menu.is_frame IS '是否为外链(Y是 N否)'; COMMENT ON COLUMN perm_menu.is_cache IS '是否缓存(Y缓存 N不缓存)'; COMMENT ON COLUMN perm_menu.menu_type IS '菜单类型(M目录 C菜单 F按钮)'; COMMENT ON COLUMN perm_menu.visible IS '显示状态(0显示 1隐藏)'; COMMENT ON COLUMN perm_menu.status IS '菜单状态(0正常 1停用)'; COMMENT ON COLUMN perm_menu.perms IS '权限标识'; COMMENT ON COLUMN perm_menu.icon IS '菜单图标'; COMMENT ON COLUMN perm_menu.active_menu IS '激活菜单路径'; COMMENT ON COLUMN perm_menu.ext IS '扩展字段'; -- --------------------------------------------------------------------------- -- 6. 用户和角色关联表 -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS perm_user_role ( user_id BIGINT NOT NULL, role_id BIGINT NOT NULL, CONSTRAINT perm_user_role_pk PRIMARY KEY (user_id, role_id) ); CREATE INDEX IF NOT EXISTS idx_perm_user_role_rid ON perm_user_role (role_id); COMMENT ON TABLE perm_user_role IS '用户和角色关联表'; -- --------------------------------------------------------------------------- -- 7. 角色和菜单关联表 -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS perm_role_menu ( role_id BIGINT NOT NULL, menu_id BIGINT NOT NULL, CONSTRAINT perm_role_menu_pk PRIMARY KEY (role_id, menu_id) ); COMMENT ON TABLE perm_role_menu IS '角色和菜单关联表'; -- --------------------------------------------------------------------------- -- 8. 角色和部门关联表(自定数据权限) -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS perm_role_dept ( role_id BIGINT NOT NULL, dept_id BIGINT NOT NULL, CONSTRAINT perm_role_dept_pk PRIMARY KEY (role_id, dept_id) ); COMMENT ON TABLE perm_role_dept IS '角色和部门关联表'; -- --------------------------------------------------------------------------- -- 9. 用户与岗位关联表 -- --------------------------------------------------------------------------- CREATE TABLE IF NOT EXISTS perm_user_post ( user_id BIGINT NOT NULL, post_id BIGINT NOT NULL, CONSTRAINT perm_user_post_pk PRIMARY KEY (user_id, post_id) ); COMMENT ON TABLE perm_user_post IS '用户与岗位关联表'; -- ============================================================================ -- 种子数据(固定 ID,全部 NOT EXISTS 保护,可重复执行) -- 部门 100+、岗位 200+、角色 1/2、菜单 10000+(避开 menu.json 的 1000 段) -- ============================================================================ -- 部门:根 + 默认部门(存量账号统一挂默认部门) INSERT INTO perm_dept (dept_id, parent_id, ancestors, dept_name, order_num, status, del_flag, create_by, create_time) SELECT 100, 0, '0', '总部', 0, '0', '0', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_dept WHERE dept_id = 100); INSERT INTO perm_dept (dept_id, parent_id, ancestors, dept_name, order_num, status, del_flag, create_by, create_time) SELECT 101, 100, '0,100', '默认部门', 1, '0', '0', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_dept WHERE dept_id = 101); -- 岗位 INSERT INTO perm_post (post_id, dept_id, post_code, post_name, post_sort, status, del_flag, create_by, create_time) SELECT 201, 100, 'manager', '管理岗', 1, '0', '0', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_post WHERE post_id = 201); INSERT INTO perm_post (post_id, dept_id, post_code, post_name, post_sort, status, del_flag, create_by, create_time) SELECT 202, 101, 'staff', '普通岗', 2, '0', '0', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_post WHERE post_id = 202); -- 角色:超级管理员(role_key=superadmin,代码侧超管判定依据之一)+ 普通角色 INSERT INTO perm_role (role_id, role_name, role_key, role_sort, data_scope, status, del_flag, create_by, create_time, remark) SELECT 1, '超级管理员', 'superadmin', 1, '1', '0', '0', 1, now(), '超级管理员(拥有全部权限)' WHERE NOT EXISTS (SELECT 1 FROM perm_role WHERE role_id = 1); INSERT INTO perm_role (role_id, role_name, role_key, role_sort, data_scope, status, del_flag, create_by, create_time, remark) SELECT 2, '普通角色', 'common', 2, '5', '0', '0', 1, now(), '普通角色(默认仅本人数据范围,菜单按需分配)' WHERE NOT EXISTS (SELECT 1 FROM perm_role WHERE role_id = 2); -- 菜单:权限管理目录 + 5 个页面 + 按钮级权限 -- 目录 INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT 10000, '权限管理', 0, 90, 'perm', NULL, 'N', 'Y', 'M', '0', '0', '', 'i-mdi:shield-account-outline', 1, now(), '权限管理目录' WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10000); -- 页面菜单(component 与前端 src/perm/views 对应) INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT 10001, '用户管理', 10000, 1, 'user', 'perm/user/index', 'N', 'Y', 'C', '0', '0', 'perm:user:list', 'i-mdi:account-group-outline', 1, now(), '用户管理菜单' WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10001); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT 10002, '角色管理', 10000, 2, 'role', 'perm/role/index', 'N', 'Y', 'C', '0', '0', 'perm:role:list', 'i-mdi:shield-check-outline', 1, now(), '角色管理菜单' WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10002); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT 10003, '菜单管理', 10000, 3, 'menu', 'perm/menu/index', 'N', 'Y', 'C', '0', '0', 'perm:menu:list', 'i-mdi:format-list-bulleted', 1, now(), '菜单管理菜单' WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10003); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT 10004, '部门管理', 10000, 4, 'dept', 'perm/dept/index', 'N', 'Y', 'C', '0', '0', 'perm:dept:list', 'i-mdi:file-tree-outline', 1, now(), '部门管理菜单' WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10004); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, is_frame, is_cache, menu_type, visible, status, perms, icon, create_by, create_time, remark) SELECT 10005, '岗位管理', 10000, 5, 'post', 'perm/post/index', 'N', 'Y', 'C', '0', '0', 'perm:post:list', 'i-mdi:badge-account-outline', 1, now(), '岗位管理菜单' WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10005); -- 用户管理按钮 INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10011, '用户查询', 10001, 1, 'F', '0', '0', 'perm:user:query', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10011); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10012, '用户新增', 10001, 2, 'F', '0', '0', 'perm:user:add', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10012); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10013, '用户修改', 10001, 3, 'F', '0', '0', 'perm:user:edit', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10013); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10014, '用户删除', 10001, 4, 'F', '0', '0', 'perm:user:remove', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10014); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10015, '重置密码', 10001, 5, 'F', '0', '0', 'perm:user:resetPwd', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10015); -- 角色管理按钮 INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10021, '角色查询', 10002, 1, 'F', '0', '0', 'perm:role:query', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10021); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10022, '角色新增', 10002, 2, 'F', '0', '0', 'perm:role:add', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10022); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10023, '角色修改', 10002, 3, 'F', '0', '0', 'perm:role:edit', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10023); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10024, '角色删除', 10002, 4, 'F', '0', '0', 'perm:role:remove', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10024); -- 菜单管理按钮 INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10031, '菜单查询', 10003, 1, 'F', '0', '0', 'perm:menu:query', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10031); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10032, '菜单新增', 10003, 2, 'F', '0', '0', 'perm:menu:add', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10032); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10033, '菜单修改', 10003, 3, 'F', '0', '0', 'perm:menu:edit', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10033); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10034, '菜单删除', 10003, 4, 'F', '0', '0', 'perm:menu:remove', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10034); -- 部门管理按钮 INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10041, '部门查询', 10004, 1, 'F', '0', '0', 'perm:dept:query', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10041); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10042, '部门新增', 10004, 2, 'F', '0', '0', 'perm:dept:add', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10042); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10043, '部门修改', 10004, 3, 'F', '0', '0', 'perm:dept:edit', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10043); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10044, '部门删除', 10004, 4, 'F', '0', '0', 'perm:dept:remove', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10044); -- 岗位管理按钮 INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10051, '岗位查询', 10005, 1, 'F', '0', '0', 'perm:post:query', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10051); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10052, '岗位新增', 10005, 2, 'F', '0', '0', 'perm:post:add', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10052); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10053, '岗位修改', 10005, 3, 'F', '0', '0', 'perm:post:edit', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10053); INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time) SELECT 10054, '岗位删除', 10005, 4, 'F', '0', '0', 'perm:post:remove', 1, now() WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10054); -- --------------------------------------------------------------------------- -- 存量路径口径修正(幂等):C 的 path 存完整路由路径(无前导 /)、 -- component 存 /views 之后相对路径(无前导 /);M 的 path 存分组 id 或短词。 -- 旧种子曾按 RuoYi 短 path 口径写入,这里统一为前端路由口径。 -- --------------------------------------------------------------------------- UPDATE perm_menu SET path = '10000' WHERE menu_id = 10000 AND path = 'perm'; UPDATE perm_menu SET path = 'perm/user/index' WHERE menu_id = 10001 AND path = 'user'; UPDATE perm_menu SET path = 'perm/role/index' WHERE menu_id = 10002 AND path = 'role'; UPDATE perm_menu SET path = 'perm/menu/index' WHERE menu_id = 10003 AND path = 'menu'; UPDATE perm_menu SET path = 'perm/dept/index' WHERE menu_id = 10004 AND path = 'dept'; UPDATE perm_menu SET path = 'perm/post/index' WHERE menu_id = 10005 AND path = 'post'; -- 图标与前端 menu.json 口径对齐(存量种子曾用的旧图标幂等修正) UPDATE perm_menu SET icon = 'i-mdi:account-group-outline' WHERE menu_id = 10001 AND icon = 'i-mdi:account-outline'; UPDATE perm_menu SET icon = 'i-mdi:shield-check-outline' WHERE menu_id = 10002 AND icon = 'i-mdi:account-group-outline'; -- menu-outline 不是 mdi 集合中的有效图标(运行时 0×0),统一替换为列表图标 UPDATE perm_menu SET icon = 'i-mdi:format-list-bulleted' WHERE menu_id = 10003 AND icon = 'i-mdi:menu-outline'; -- ============================================================================ -- 业务菜单种子(融合阶段):id 沿用前端 menu.json 的 1000 段,path/name 口径不变 -- M 目录 / C 页面 / F 操作;F 的 menu_id = 父 C id × 100 + 序号 -- ext JSON 承载 affix / group / permsOnly,不新增表字段 -- 图标仅复用 uno.config.ts safelist 现有项(新增图标须同步 safelist) -- ============================================================================ INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, ext, create_by, create_time) SELECT v.menu_id, v.menu_name, v.parent_id, v.order_num, v.path, v.component, v.menu_type, v.visible, v.status, v.perms, v.icon, v.ext, 1, now() FROM (VALUES (1000::bigint, '案件数据', 0::bigint, 1, 'data/index', 'data/index', 'C', '0', '0', 'case:data:list', 'i-mdi:briefcase-outline', '{"affix":true}'), (1001, '数据画像', 0, 2, 'dm/portrait', 'dm/portrait', 'C', '0', '0', 'dm:portrait:list', 'i-mdi:account-details-outline', ''), (1002, '通信分析', 0, 3, '1002', NULL, 'M', '0', '0', '', 'i-mdi:message-text-outline', ''), (1003, '持续联系', 1002, 1, 'continuous/index', 'continuous/index', 'C', '0', '0', 'call:continuous:list', 'i-mdi:phone-sync-outline', ''), (1004, '夜间联系', 1002, 2, 'night/index', 'night/index', 'C', '0', '0', 'call:night:list', 'i-mdi:weather-night', ''), (1005, '敏感日期联系', 1002, 3, 'sensitive/index', 'sensitive/index', 'C', '0', '0', 'call:sensitive:list', 'i-mdi:calendar-alert', ''), (1006, '交易分析', 0, 4, '1006', NULL, 'M', '0', '0', '', 'i-mdi:bank-transfer', ''), (1007, '敏感日期交易', 1006, 1, 'transSensitive/index', 'transSensitive/index', 'C', '0', '0', 'trans:sensitive:list', 'i-mdi:calendar-star', ''), (1008, '每月规律交易', 1006, 2, 'monthRegular/index', 'monthRegular/index', 'C', '0', '0', 'trans:continuous:list', 'i-mdi:calendar-sync-outline', ''), (1009, '交易频率分析', 1006, 3, 'tradeFrequency/index', 'tradeFrequency/index', 'C', '0', '0', 'trans:frequency:list', 'i-mdi:chart-timeline-variant', ''), (1010, '大额交易分析', 1006, 4, 'largeAmount/index', 'largeAmount/index', 'C', '0', '0', 'trans:big:list', 'i-mdi:cash-multiple', ''), (1011, '资金流向分析', 1006, 5, 'capitalFlow/index', 'capitalFlow/index', 'C', '0', '0', 'trans:fundsFlow:list', 'i-mdi:swap-horizontal', ''), (1012, '现金交易分析', 1006, 6, 'cashTrans/index', 'cashTrans/index', 'C', '0', '0', 'trans:cashFlow:list', 'i-mdi:cash', ''), (1013, '资金快转分析', 1006, 7, 'fastTransfer/index', 'fastTransfer/index', 'C', '0', '0', 'trans:fastFundFlow:list', 'i-mdi:flash-outline', ''), (1014, '银行卡代持分析', 1006, 8, 'cardHolding/index', 'cardHolding/index', 'C', '0', '0', 'trans:cardHold:list', 'i-mdi:credit-card-outline', ''), (1015, '定期存取分析', 1006, 9, 'fixedDeposit/index', 'fixedDeposit/index', 'C', '0', '0', 'trans:fixedDeposit:list', 'i-mdi:bank-outline', ''), (1016, '金融理财分析', 1006, 10, 'financialWealth/index', 'financialWealth/index', 'C', '0', '0', 'trans:financial:list', 'i-mdi:finance', ''), (1017, '活动轨迹分析', 0, 5, '1017', NULL, 'M', '0', '0', '', 'i-mdi:map-marker-path', ''), (1018, '同行分析', 1017, 1, 'peerAnalysis/index', 'peerAnalysis/index', 'C', '0', '0', 'track:togetherTravel:list', 'i-mdi:account-group-outline', ''), (1019, '同住分析', 1017, 2, 'coResidence/index', 'coResidence/index', 'C', '0', '0', 'track:togetherLive:list', 'i-mdi:bed-outline', ''), (1020, '碰面分析', 1017, 3, 'faceToFace/index', 'faceToFace/index', 'C', '0', '0', 'track:meet:list', 'i-mdi:handshake-outline', ''), (1021, '早晚通话地', 1017, 4, 'morningEveningCall/index', 'morningEveningCall/index', 'C', '0', '0', 'track:enLocal:list', 'i-mdi:phone-in-talk-outline', ''), (1022, '快递收发地', 1017, 5, 'expressDelivery/index', 'expressDelivery/index', 'C', '0', '0', 'track:express:list', 'i-mdi:package-variant-closed', ''), (1023, '通话基站分析', 1017, 6, 'baseStation/index', 'baseStation/index', 'C', '0', '0', 'track:cellTower:list', 'i-mdi:signal-variant', ''), (1024, '行为时空分析', 0, 6, 'behaviorSpacetime/index', 'behaviorSpacetime/index', 'C', '0', '0', 'otg:behavior:list', 'i-mdi:map-clock-outline', ''), (1028, '对象关系分析', 0, 7, 'graph/g6-editor', 'graph/g6-editor', 'C', '0', '0', 'graph:editor:list', 'i-mdi:graph-outline', ''), (10262, '基站查询', 0, 8, 'aiBaseStation/index', 'aiBaseStation/index', 'C', '0', '0', 'tower:aibase:list', 'i-mdi:radio-tower', '') ) AS v(menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, ext) WHERE NOT EXISTS (SELECT 1 FROM perm_menu m WHERE m.menu_id = v.menu_id); -- 业务页只菜单级权限(用户决策):有菜单即有操作权限,操作 F 节点退库(幂等清理) DELETE FROM perm_role_menu WHERE menu_id IN ( SELECT menu_id FROM perm_menu WHERE menu_type = 'F' AND parent_id IN (1000,1001,1003,1004,1005,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016, 1018,1019,1020,1021,1022,1023,1024,10262,1027,1028,1029,1091,1092)); DELETE FROM perm_menu WHERE menu_type = 'F' AND parent_id IN (1000,1001,1003,1004,1005,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016, 1018,1019,1020,1021,1022,1023,1024,10262,1027,1028,1029,1091,1092); -- 对象关系分析只留 g6-editor 一个入口;隐藏视图页与 Relation Graph 测试页下线 DELETE FROM perm_role_menu WHERE menu_id IN (1027, 1029); DELETE FROM perm_menu WHERE menu_id IN (1027, 1029); -- 纯操作码容器(无路由):案件管理 /case、模板库(内嵌组件) -- getRouters 跳过 permsOnly 子树;菜单码照常经 selectMenuPermsByUserId 收集 -- 业务页只菜单级:C 节点 perms 即该页全部操作的准入码 -- 注:系统维护(/sys 工具/清缓存/升级)与向量调试无对应页面,不走菜单权限,仅登录校验 INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, ext, create_by, create_time) SELECT v.menu_id, v.menu_name, v.parent_id, v.order_num, v.path, NULL, v.menu_type, '1', '0', v.perms, '#', '{"permsOnly":true}', 1, now() FROM (VALUES (1090::bigint, '系统操作', 0::bigint, 99, '1090', 'M', ''), (1091, '案件管理', 1090, 1, 'case', 'C', 'case:manage:list'), (1092, '模板管理', 1090, 2, 'tpl', 'C', 'tpl:tpl:list') ) AS v(menu_id, menu_name, parent_id, order_num, path, menu_type, perms) WHERE NOT EXISTS (SELECT 1 FROM perm_menu m WHERE m.menu_id = v.menu_id); UPDATE perm_menu SET perms = 'case:manage:list' WHERE menu_id = 1091 AND (perms IS NULL OR perms = ''); UPDATE perm_menu SET perms = 'tpl:tpl:list' WHERE menu_id = 1092 AND (perms IS NULL OR perms = ''); -- 系统维护无页面:移除历史种子节点与授权(幂等),相关端点仅登录校验 DELETE FROM perm_role_menu WHERE menu_id IN (1093, 109301, 109302, 109303); DELETE FROM perm_menu WHERE menu_id IN (1093, 109301, 109302, 109303); -- ============================================================================ -- 智能工作台菜单种子(20000 段):ext.group='workbench',主窗口导航不渲染, -- 工作台窗口取该子树做侧栏;路由仍为静态 WorkbenchRoute(导航 DB 化≠路由动态化) -- ============================================================================ INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, ext, create_by, create_time) SELECT v.menu_id, v.menu_name, v.parent_id, v.order_num, v.path, v.component, v.menu_type, '0', '0', v.perms, v.icon, v.ext, 1, now() FROM (VALUES (20000::bigint, '智能工作台', 0::bigint, 100, 'workbench', NULL, 'M', '', 'i-mdi:robot-outline', '{"group":"workbench"}'), (20100, '智能体', 20000, 1, 'workbench/agent', NULL, 'M', '', 'i-mdi:robot-outline', ''), (20101, '任务工作台', 20100, 1, 'workbench/chat', 'workbench/chat/index', 'C', 'ai:chat:query', 'i-mdi:message-text-outline', ''), (20102, '数剑配置', 20100, 2, 'workbench/agent/entry', 'workbench/agent/entry/index', 'C', 'ai:agent:query', 'i-mdi:star-outline', ''), (20103, '专家配置', 20100, 3, 'workbench/agent/expert', 'workbench/agent/expert/index', 'C', 'ai:expert:query', 'i-mdi:account-tie-outline', ''), (20104, '任务管理', 20100, 4, 'workbench/agent/session', 'workbench/agent/session/index', 'C', 'ai:session:query', 'i-mdi:format-list-bulleted', ''), (20105, '长期记忆', 20100, 5, 'workbench/agent/memory', 'workbench/agent/memory/index', 'C', 'ai:memory:query', 'i-mdi:history', ''), (20106, '技能仓库', 20100, 6, 'workbench/agent/skill', 'workbench/agent/skill/index', 'C', 'ai:skill:query', 'i-mdi:lightbulb-on-outline', ''), (20107, '场景包', 20100, 7, 'workbench/agent/pack', 'workbench/agent/pack/index', 'C', 'ai:pack:query', 'i-mdi:package-variant-closed', ''), (20200, '模型与推理', 20000, 2, 'workbench/model', NULL, 'M', '', 'i-mdi:chip', ''), (20201, '模型配置', 20200, 1, 'workbench/model/config', 'workbench/model/config/index', 'C', 'ai:model:query', 'i-mdi:server', ''), (20203, '上下文与压缩策略', 20200, 3, 'workbench/model/compaction', 'workbench/model/compaction/index', 'C', 'ai:runtime:query', 'i-mdi:arrow-collapse-vertical', ''), (20300, '知识库', 20000, 3, 'workbench/kb', NULL, 'M', '', 'i-mdi:bookshelf', ''), (20301, '知识库管理', 20300, 1, 'workbench/kb/base', 'workbench/kb/base/index', 'C', 'ai:kb:query', 'i-mdi:book-open-page-variant-outline', ''), (20302, '文档与切片', 20300, 2, 'workbench/kb/doc', 'workbench/kb/doc/index', 'C', 'ai:doc:query', 'i-mdi:file-pdf-box', ''), (20303, '检索测试', 20300, 3, 'workbench/kb/retrieval', 'workbench/kb/retrieval/index', 'C', 'ai:retrieval:query', 'i-mdi:magnify', ''), (20400, '工具与集成', 20000, 4, 'workbench/integration', NULL, 'M', '', 'i-mdi:link-variant', ''), (20401, '业务工具注册', 20400, 1, 'workbench/integration/registry', 'workbench/integration/registry/index', 'C', 'ai:tool:query', 'i-mdi:tools', ''), (20402, 'MCP 服务端', 20400, 2, 'workbench/integration/mcp-server', 'workbench/integration/mcpServer/index', 'C', 'ai:mcpServer:query', 'i-mdi:server-network', ''), (20403, 'MCP 客户端', 20400, 3, 'workbench/integration/mcp-client', 'workbench/integration/mcpClient/index', 'C', 'ai:mcpClient:query', 'i-mdi:lan-connect', ''), (20500, '审计与观测', 20000, 5, 'workbench/audit', NULL, 'M', '', 'i-mdi:monitor-dashboard', ''), (20501, '会话与消息审计', 20500, 1, 'workbench/audit/message', 'workbench/audit/message/index', 'C', 'ai:audit:query', 'i-mdi:message-text-outline', ''), (20502, '工具调用明细', 20500, 2, 'workbench/audit/tool-call', 'workbench/audit/toolCall/index', 'C', 'ai:auditTool:query', 'i-mdi:progress-wrench', ''), (20503, '链路追踪', 20500, 3, 'workbench/audit/trace', 'workbench/audit/trace/index', 'C', 'ai:trace:query', 'i-mdi:transit-connection-variant', ''), (20504, '编排运行', 20500, 4, 'workbench/audit/route-run', 'workbench/audit/routeRun/index', 'C', 'ai:route:query', 'i-mdi:call-split', ''), (20505, '健康自检', 20500, 5, 'workbench/audit/health', 'workbench/audit/health/index', 'C', 'ai:health:query', 'i-mdi:heart-pulse', '') ) AS v(menu_id, menu_name, parent_id, order_num, path, component, menu_type, perms, icon, ext) WHERE NOT EXISTS (SELECT 1 FROM perm_menu m WHERE m.menu_id = v.menu_id); -- compress 不是 mdi 集合中的有效图标(运行时 0×0),统一替换为箭头收起图标 UPDATE perm_menu SET icon = 'i-mdi:arrow-collapse-vertical' WHERE menu_id = 20203 AND icon = 'i-mdi:compress'; INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time) SELECT v.menu_id, v.menu_name, v.parent_id, v.order_num, '', NULL, 'F', '0', '0', v.perms, '#', 1, now() FROM (VALUES (2010101::bigint, '会话查询', 20101::bigint, 1, 'ai:chat:query'), (2010102, '发送消息', 20101, 2, 'ai:chat:add'), (2010103, '会话编辑', 20101, 3, 'ai:chat:edit'), (2010104, '会话删除', 20101, 4, 'ai:chat:remove'), (2010105, '执行与停止', 20101, 5, 'ai:chat:execute'), (2010106, '打开工作台', 20101, 6, 'ai:workbench:execute'), (2010201, '配置查询', 20102, 1, 'ai:agent:query'), (2010202, '配置编辑', 20102, 2, 'ai:agent:edit'), (2010301, '专家查询', 20103, 1, 'ai:expert:query'), (2010302, '专家编辑', 20103, 2, 'ai:expert:edit'), (2010303, '专家删除', 20103, 3, 'ai:expert:remove'), (2010401, '任务查询', 20104, 1, 'ai:session:query'), (2010402, '任务关闭', 20104, 2, 'ai:session:edit'), (2010403, '任务删除', 20104, 3, 'ai:session:remove'), (2010404, '任务导出', 20104, 4, 'ai:session:export'), (2010501, '记忆查询', 20105, 1, 'ai:memory:query'), (2010502, '记忆编辑', 20105, 2, 'ai:memory:edit'), (2010503, '记忆删除', 20105, 3, 'ai:memory:remove'), (2010601, '技能查询', 20106, 1, 'ai:skill:query'), (2010602, '技能编辑', 20106, 2, 'ai:skill:edit'), (2010603, '技能删除', 20106, 3, 'ai:skill:remove'), (2010701, '场景包查询', 20107, 1, 'ai:pack:query'), (2010702, '场景包编辑', 20107, 2, 'ai:pack:edit'), (2010703, '场景包删除', 20107, 3, 'ai:pack:remove'), (2020101, '模型查询', 20201, 1, 'ai:model:query'), (2020102, '模型编辑', 20201, 2, 'ai:model:edit'), (2020103, '模型删除', 20201, 3, 'ai:model:remove'), (2020301, '策略查询', 20203, 1, 'ai:runtime:query'), (2020302, '策略编辑', 20203, 2, 'ai:runtime:edit'), (2030101, '知识库查询', 20301, 1, 'ai:kb:query'), (2030102, '知识库编辑', 20301, 2, 'ai:kb:edit'), (2030103, '知识库删除', 20301, 3, 'ai:kb:remove'), (2030201, '文档查询', 20302, 1, 'ai:doc:query'), (2030202, '文档编辑', 20302, 2, 'ai:doc:edit'), (2030203, '文档删除', 20302, 3, 'ai:doc:remove'), (2030301, '检索查询', 20303, 1, 'ai:retrieval:query'), (2030302, '检索测试', 20303, 2, 'ai:retrieval:execute'), (2040101, '工具查询', 20401, 1, 'ai:tool:query'), (2040102, '工具编辑', 20401, 2, 'ai:tool:edit'), (2040103, '工具删除', 20401, 3, 'ai:tool:remove'), (2040201, '服务端查询', 20402, 1, 'ai:mcpServer:query'), (2040202, '服务端编辑', 20402, 2, 'ai:mcpServer:edit'), (2040301, '客户端查询', 20403, 1, 'ai:mcpClient:query'), (2040302, '客户端编辑', 20403, 2, 'ai:mcpClient:edit'), (2040303, '客户端删除', 20403, 3, 'ai:mcpClient:remove'), (2050101, '审计查询', 20501, 1, 'ai:audit:query'), (2050102, '审计导出', 20501, 2, 'ai:audit:export'), (2050103, '保留策略编辑', 20501, 3, 'ai:audit:edit'), (2050201, '工具调用查询', 20502, 1, 'ai:auditTool:query'), (2050301, '链路查询', 20503, 1, 'ai:trace:query'), (2050401, '编排查询', 20504, 1, 'ai:route:query'), (2050501, '自检查询', 20505, 1, 'ai:health:query'), (2050502, '执行自检', 20505, 2, 'ai:health:execute') ) AS v(menu_id, menu_name, parent_id, order_num, perms) WHERE NOT EXISTS (SELECT 1 FROM perm_menu m WHERE m.menu_id = v.menu_id); -- 「用量与配额」菜单下架(页面与路由代码保留):幂等清理菜单、按钮与角色绑定 DELETE FROM perm_role_menu WHERE menu_id IN (20202, 2020201, 2020202); DELETE FROM perm_menu WHERE menu_id IN (20202, 2020201, 2020202); -- 超级管理员绑定全部菜单(新增菜单后重跑本段即可补齐) INSERT INTO perm_role_menu (role_id, menu_id) SELECT 1, m.menu_id FROM perm_menu m WHERE NOT EXISTS (SELECT 1 FROM perm_role_menu rm WHERE rm.role_id = 1 AND rm.menu_id = m.menu_id); -- ============================================================================ -- 内置超管种子(幂等):本脚本自包含,不再依赖 sys_user。 -- 哈希与历史 sql/user.sql 的 admin 初始常量一致(admin123,首登后改密); -- 存量库若已由历史迁移段写入同 ID 账号,NOT EXISTS 命中即跳过。 -- 历史存量迁移(sys_user → perm_user,保留原 ID)已于 2026-11-18 执行完毕, -- 迁移段随融合下线;sys_user 表仅保留作回滚兜底,代码不再读写。 -- ============================================================================ INSERT INTO perm_user (user_id, dept_id, user_name, nick_name, password, status, del_flag, create_by, create_time, remark) SELECT 1, 101, 'admin', '系统管理员', '$2a$10$dwBgMS1xW2PbUsuce16g/ujiVEH2H4pAjWoyeSMqLClwfOs1N9fri', '0', '0', 1, now(), '内置超管(admin123,首登后改密)' WHERE NOT EXISTS (SELECT 1 FROM perm_user WHERE user_id = 1); INSERT INTO perm_user_role (user_id, role_id) SELECT 1, 1 WHERE NOT EXISTS (SELECT 1 FROM perm_user_role WHERE user_id = 1 AND role_id = 1); -- ============================================================================ -- 普通角色默认授权(幂等):融合前业务接口无校验,普通账号全功能可用; -- 为不回归,common 默认授予全部业务/工作台/系统操作菜单与操作码, -- 仅排除 perm 管理子树(perms 以 perm: 开头 + 目录 10000)与向量调试码。 -- 管理员后续按角色回收即可收紧。 -- ============================================================================ INSERT INTO perm_role_menu (role_id, menu_id) SELECT 2, m.menu_id FROM perm_menu m WHERE m.perms NOT LIKE 'perm:%' AND m.menu_id <> 10000 AND m.perms <> 'embedding:debug:execute' AND NOT EXISTS (SELECT 1 FROM perm_role_menu rm WHERE rm.role_id = 2 AND rm.menu_id = m.menu_id); -- ============================================================================ -- 校验 -- ============================================================================ -- SELECT user_id, user_name, nick_name, status FROM perm_user ORDER BY user_id; -- SELECT ur.user_id, r.role_key FROM perm_user_role ur JOIN perm_role r ON r.role_id = ur.role_id; -- SELECT count(*) FROM perm_role_menu WHERE role_id = 1; -- 应等于 perm_menu 总数