perm.sql 42 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128129130131132133134135136137138139140141142143144145146147148149150151152153154155156157158159160161162163164165166167168169170171172173174175176177178179180181182183184185186187188189190191192193194195196197198199200201202203204205206207208209210211212213214215216217218219220221222223224225226227228229230231232233234235236237238239240241242243244245246247248249250251252253254255256257258259260261262263264265266267268269270271272273274275276277278279280281282283284285286287288289290291292293294295296297298299300301302303304305306307308309310311312313314315316317318319320321322323324325326327328329330331332333334335336337338339340341342343344345346347348349350351352353354355356357358359360361362363364365366367368369370371372373374375376377378379380381382383384385386387388389390391392393394395396397398399400401402403404405406407408409410411412413414415416417418419420421422423424425426427428429430431432433434435436437438439440441442443444445446447448449450451452453454455456457458459460461462463464465466467468469470471472473474475476477478479480481482483484485486487488489490491492493494495496497498499500501502503504505506507508509510511512513514515516517518519520521522523524525526527528529530531532533534535536537538539540541542543544545546547548549550551552553554555556557558559560561562563564565566567568569570571572573574575576577578579580581582583584585586587588589590591592593594595596597598599600601602603604605606607608609610611612613614615616
  1. -- ============================================================================
  2. -- 用户权限体系迁移脚本(RuoYi-Vue-Plus 核心 RBAC → perm_* 独立表)
  3. -- 来源:E:\workspace\ai\biz-buddy\code\back-end\script\sql\postgres\postgres_ry_vue.sql
  4. -- 目标库:PostgreSQL master(application-dev.yaml 中的 spring.datasource.dynamic.datasource.master)
  5. -- 执行方式:手工执行(项目无 Flyway / Liquibase)
  6. -- 幂等性:本脚本可重复执行
  7. --
  8. -- 范围(已确认的迁移决定):
  9. -- * 9 张核心 RBAC 表,全部 perm_ 前缀,与既有 sys_user 完全隔离;
  10. -- * 既有 sys_user 表保留不动(回滚兜底),登录链路改读本脚本的 perm_user;
  11. -- * 内置超管 admin(id=1) 由本脚本自包含种子写入(case_info.owner_id 挂 perm_user.user_id);
  12. -- * 融合阶段:业务菜单(1000 段)/工作台菜单(20000 段)/操作码(F) 全部入库,
  13. -- 导航改 getRouters 动态下发;common 角色默认授全部业务码(不回归),perm 管理子树除外;
  14. -- * 不含字典/参数/客户端/日志/OSS/通知(范围外);
  15. -- * 超管约定沿用 RuoYi:user_id=1 或持有 superadmin 角色 → 权限 *:*:*。
  16. --
  17. -- 注意:这是**平台库**表,不需要登记到 table_info / table_field。
  18. -- ============================================================================
  19. -- ---------------------------------------------------------------------------
  20. -- 1. 部门表
  21. -- ---------------------------------------------------------------------------
  22. CREATE TABLE IF NOT EXISTS perm_dept (
  23. dept_id BIGINT PRIMARY KEY,
  24. parent_id BIGINT DEFAULT 0,
  25. ancestors VARCHAR(500) DEFAULT '',
  26. dept_name VARCHAR(30) DEFAULT '',
  27. dept_category VARCHAR(100) DEFAULT NULL,
  28. order_num INT DEFAULT 0,
  29. leader BIGINT DEFAULT NULL,
  30. phone VARCHAR(11) DEFAULT NULL,
  31. email VARCHAR(50) DEFAULT NULL,
  32. status CHAR(1) DEFAULT '0',
  33. del_flag CHAR(1) DEFAULT '0',
  34. create_dept BIGINT,
  35. create_by BIGINT,
  36. create_time TIMESTAMP,
  37. update_by BIGINT,
  38. update_time TIMESTAMP
  39. );
  40. CREATE INDEX IF NOT EXISTS idx_perm_dept_parent_id ON perm_dept (parent_id);
  41. COMMENT ON TABLE perm_dept IS '部门表';
  42. COMMENT ON COLUMN perm_dept.dept_id IS '部门ID';
  43. COMMENT ON COLUMN perm_dept.parent_id IS '父部门ID';
  44. COMMENT ON COLUMN perm_dept.ancestors IS '祖级列表';
  45. COMMENT ON COLUMN perm_dept.dept_name IS '部门名称';
  46. COMMENT ON COLUMN perm_dept.dept_category IS '部门类别编码';
  47. COMMENT ON COLUMN perm_dept.order_num IS '显示顺序';
  48. COMMENT ON COLUMN perm_dept.leader IS '负责人(perm_user.user_id)';
  49. COMMENT ON COLUMN perm_dept.status IS '部门状态(0正常 1停用)';
  50. COMMENT ON COLUMN perm_dept.del_flag IS '删除标志(0存在 1删除)';
  51. -- ---------------------------------------------------------------------------
  52. -- 2. 用户信息表
  53. -- ---------------------------------------------------------------------------
  54. CREATE TABLE IF NOT EXISTS perm_user (
  55. user_id BIGINT PRIMARY KEY,
  56. dept_id BIGINT,
  57. user_name VARCHAR(30) NOT NULL,
  58. nick_name VARCHAR(30) NOT NULL,
  59. user_type VARCHAR(10) DEFAULT 'sys_user',
  60. email VARCHAR(50) DEFAULT '',
  61. phone_number VARCHAR(11) DEFAULT '',
  62. gender CHAR(1) DEFAULT '0',
  63. avatar BIGINT,
  64. password VARCHAR(100) DEFAULT '',
  65. status CHAR(1) DEFAULT '0',
  66. del_flag CHAR(1) DEFAULT '0',
  67. login_ip VARCHAR(128) DEFAULT '',
  68. login_date TIMESTAMP,
  69. create_dept BIGINT,
  70. create_by BIGINT,
  71. create_time TIMESTAMP,
  72. update_by BIGINT,
  73. update_time TIMESTAMP,
  74. remark VARCHAR(500) DEFAULT NULL
  75. );
  76. CREATE INDEX IF NOT EXISTS idx_perm_user_dept_id ON perm_user (dept_id);
  77. CREATE INDEX IF NOT EXISTS idx_perm_user_user_name ON perm_user (user_name);
  78. -- 与既有 sys_user 一致:用户名不区分大小写唯一
  79. CREATE UNIQUE INDEX IF NOT EXISTS uk_perm_user_username ON perm_user (lower(user_name)) WHERE del_flag = '0';
  80. COMMENT ON TABLE perm_user IS '用户信息表(权限体系,登录链路读本表)';
  81. COMMENT ON COLUMN perm_user.user_id IS '用户ID';
  82. COMMENT ON COLUMN perm_user.dept_id IS '部门ID';
  83. COMMENT ON COLUMN perm_user.user_name IS '用户账号';
  84. COMMENT ON COLUMN perm_user.nick_name IS '用户昵称';
  85. COMMENT ON COLUMN perm_user.gender IS '用户性别(0男 1女 2未知)';
  86. COMMENT ON COLUMN perm_user.avatar IS '头像地址';
  87. COMMENT ON COLUMN perm_user.password IS 'BCrypt 密码哈希';
  88. COMMENT ON COLUMN perm_user.status IS '账号状态(0正常 1停用)';
  89. COMMENT ON COLUMN perm_user.del_flag IS '删除标志(0存在 1删除)';
  90. COMMENT ON COLUMN perm_user.login_ip IS '最后登录IP';
  91. COMMENT ON COLUMN perm_user.login_date IS '最后登录时间';
  92. -- ---------------------------------------------------------------------------
  93. -- 3. 岗位信息表
  94. -- ---------------------------------------------------------------------------
  95. CREATE TABLE IF NOT EXISTS perm_post (
  96. post_id BIGINT PRIMARY KEY,
  97. dept_id BIGINT,
  98. post_code VARCHAR(64) NOT NULL,
  99. post_category VARCHAR(100) DEFAULT NULL,
  100. post_name VARCHAR(50) NOT NULL,
  101. post_sort INT NOT NULL,
  102. status CHAR(1) NOT NULL,
  103. del_flag CHAR(1) DEFAULT '0',
  104. create_dept BIGINT,
  105. create_by BIGINT,
  106. create_time TIMESTAMP,
  107. update_by BIGINT,
  108. update_time TIMESTAMP,
  109. remark VARCHAR(500) DEFAULT NULL
  110. );
  111. CREATE INDEX IF NOT EXISTS idx_perm_post_dept_id ON perm_post (dept_id);
  112. COMMENT ON TABLE perm_post IS '岗位信息表';
  113. COMMENT ON COLUMN perm_post.post_id IS '岗位ID';
  114. COMMENT ON COLUMN perm_post.dept_id IS '部门ID';
  115. COMMENT ON COLUMN perm_post.post_code IS '岗位编码';
  116. COMMENT ON COLUMN perm_post.post_category IS '岗位类别编码';
  117. COMMENT ON COLUMN perm_post.post_name IS '岗位名称';
  118. COMMENT ON COLUMN perm_post.post_sort IS '显示顺序';
  119. COMMENT ON COLUMN perm_post.status IS '状态(0正常 1停用)';
  120. COMMENT ON COLUMN perm_post.del_flag IS '删除标志(0存在 1删除)';
  121. -- ---------------------------------------------------------------------------
  122. -- 4. 角色信息表
  123. -- ---------------------------------------------------------------------------
  124. CREATE TABLE IF NOT EXISTS perm_role (
  125. role_id BIGINT PRIMARY KEY,
  126. role_name VARCHAR(30) NOT NULL,
  127. role_key VARCHAR(100) NOT NULL,
  128. role_sort INT NOT NULL,
  129. data_scope CHAR(1) DEFAULT '1',
  130. menu_check_strictly BOOLEAN DEFAULT TRUE,
  131. dept_check_strictly BOOLEAN DEFAULT TRUE,
  132. status CHAR(1) NOT NULL,
  133. del_flag CHAR(1) DEFAULT '0',
  134. create_dept BIGINT,
  135. create_by BIGINT,
  136. create_time TIMESTAMP,
  137. update_by BIGINT,
  138. update_time TIMESTAMP,
  139. remark VARCHAR(500) DEFAULT NULL
  140. );
  141. COMMENT ON TABLE perm_role IS '角色信息表';
  142. COMMENT ON COLUMN perm_role.role_id IS '角色ID';
  143. COMMENT ON COLUMN perm_role.role_name IS '角色名称';
  144. COMMENT ON COLUMN perm_role.role_key IS '角色权限字符串';
  145. COMMENT ON COLUMN perm_role.role_sort IS '显示顺序';
  146. COMMENT ON COLUMN perm_role.data_scope IS '数据范围(1全部 2自定 3本部门 4本部门及以下 5仅本人 6部门及以下或本人);本期仅存储与配置,SQL 过滤融合时启用';
  147. COMMENT ON COLUMN perm_role.menu_check_strictly IS '菜单树选择项是否关联显示';
  148. COMMENT ON COLUMN perm_role.dept_check_strictly IS '部门树选择项是否关联显示';
  149. COMMENT ON COLUMN perm_role.status IS '角色状态(0正常 1停用)';
  150. COMMENT ON COLUMN perm_role.del_flag IS '删除标志(0存在 1删除)';
  151. -- ---------------------------------------------------------------------------
  152. -- 5. 菜单权限表
  153. -- ---------------------------------------------------------------------------
  154. CREATE TABLE IF NOT EXISTS perm_menu (
  155. menu_id BIGINT PRIMARY KEY,
  156. menu_name VARCHAR(50) NOT NULL,
  157. parent_id BIGINT DEFAULT 0,
  158. order_num INT DEFAULT 0,
  159. path VARCHAR(200) DEFAULT '',
  160. component VARCHAR(255) DEFAULT NULL,
  161. query_param VARCHAR(255) DEFAULT NULL,
  162. is_frame CHAR(1) DEFAULT 'N',
  163. is_cache CHAR(1) DEFAULT 'Y',
  164. menu_type CHAR(1) DEFAULT '',
  165. visible CHAR(1) DEFAULT '0',
  166. status CHAR(1) DEFAULT '0',
  167. perms VARCHAR(100) DEFAULT NULL,
  168. icon VARCHAR(100) DEFAULT '#',
  169. active_menu VARCHAR(255) DEFAULT '',
  170. ext VARCHAR(2000) DEFAULT '',
  171. create_dept BIGINT,
  172. create_by BIGINT,
  173. create_time TIMESTAMP,
  174. update_by BIGINT,
  175. update_time TIMESTAMP,
  176. remark VARCHAR(500) DEFAULT ''
  177. );
  178. COMMENT ON TABLE perm_menu IS '菜单权限表';
  179. COMMENT ON COLUMN perm_menu.menu_id IS '菜单ID';
  180. COMMENT ON COLUMN perm_menu.menu_name IS '菜单名称';
  181. COMMENT ON COLUMN perm_menu.parent_id IS '父菜单ID';
  182. COMMENT ON COLUMN perm_menu.order_num IS '显示顺序';
  183. COMMENT ON COLUMN perm_menu.path IS '路由地址';
  184. COMMENT ON COLUMN perm_menu.component IS '组件路径';
  185. COMMENT ON COLUMN perm_menu.query_param IS '路由参数';
  186. COMMENT ON COLUMN perm_menu.is_frame IS '是否为外链(Y是 N否)';
  187. COMMENT ON COLUMN perm_menu.is_cache IS '是否缓存(Y缓存 N不缓存)';
  188. COMMENT ON COLUMN perm_menu.menu_type IS '菜单类型(M目录 C菜单 F按钮)';
  189. COMMENT ON COLUMN perm_menu.visible IS '显示状态(0显示 1隐藏)';
  190. COMMENT ON COLUMN perm_menu.status IS '菜单状态(0正常 1停用)';
  191. COMMENT ON COLUMN perm_menu.perms IS '权限标识';
  192. COMMENT ON COLUMN perm_menu.icon IS '菜单图标';
  193. COMMENT ON COLUMN perm_menu.active_menu IS '激活菜单路径';
  194. COMMENT ON COLUMN perm_menu.ext IS '扩展字段';
  195. -- ---------------------------------------------------------------------------
  196. -- 6. 用户和角色关联表
  197. -- ---------------------------------------------------------------------------
  198. CREATE TABLE IF NOT EXISTS perm_user_role (
  199. user_id BIGINT NOT NULL,
  200. role_id BIGINT NOT NULL,
  201. CONSTRAINT perm_user_role_pk PRIMARY KEY (user_id, role_id)
  202. );
  203. CREATE INDEX IF NOT EXISTS idx_perm_user_role_rid ON perm_user_role (role_id);
  204. COMMENT ON TABLE perm_user_role IS '用户和角色关联表';
  205. -- ---------------------------------------------------------------------------
  206. -- 7. 角色和菜单关联表
  207. -- ---------------------------------------------------------------------------
  208. CREATE TABLE IF NOT EXISTS perm_role_menu (
  209. role_id BIGINT NOT NULL,
  210. menu_id BIGINT NOT NULL,
  211. CONSTRAINT perm_role_menu_pk PRIMARY KEY (role_id, menu_id)
  212. );
  213. COMMENT ON TABLE perm_role_menu IS '角色和菜单关联表';
  214. -- ---------------------------------------------------------------------------
  215. -- 8. 角色和部门关联表(自定数据权限)
  216. -- ---------------------------------------------------------------------------
  217. CREATE TABLE IF NOT EXISTS perm_role_dept (
  218. role_id BIGINT NOT NULL,
  219. dept_id BIGINT NOT NULL,
  220. CONSTRAINT perm_role_dept_pk PRIMARY KEY (role_id, dept_id)
  221. );
  222. COMMENT ON TABLE perm_role_dept IS '角色和部门关联表';
  223. -- ---------------------------------------------------------------------------
  224. -- 9. 用户与岗位关联表
  225. -- ---------------------------------------------------------------------------
  226. CREATE TABLE IF NOT EXISTS perm_user_post (
  227. user_id BIGINT NOT NULL,
  228. post_id BIGINT NOT NULL,
  229. CONSTRAINT perm_user_post_pk PRIMARY KEY (user_id, post_id)
  230. );
  231. COMMENT ON TABLE perm_user_post IS '用户与岗位关联表';
  232. -- ============================================================================
  233. -- 种子数据(固定 ID,全部 NOT EXISTS 保护,可重复执行)
  234. -- 部门 100+、岗位 200+、角色 1/2、菜单 10000+(避开 menu.json 的 1000 段)
  235. -- ============================================================================
  236. -- 部门:根 + 默认部门(存量账号统一挂默认部门)
  237. INSERT INTO perm_dept (dept_id, parent_id, ancestors, dept_name, order_num, status, del_flag, create_by, create_time)
  238. SELECT 100, 0, '0', '总部', 0, '0', '0', 1, now()
  239. WHERE NOT EXISTS (SELECT 1 FROM perm_dept WHERE dept_id = 100);
  240. INSERT INTO perm_dept (dept_id, parent_id, ancestors, dept_name, order_num, status, del_flag, create_by, create_time)
  241. SELECT 101, 100, '0,100', '默认部门', 1, '0', '0', 1, now()
  242. WHERE NOT EXISTS (SELECT 1 FROM perm_dept WHERE dept_id = 101);
  243. -- 岗位
  244. INSERT INTO perm_post (post_id, dept_id, post_code, post_name, post_sort, status, del_flag, create_by, create_time)
  245. SELECT 201, 100, 'manager', '管理岗', 1, '0', '0', 1, now()
  246. WHERE NOT EXISTS (SELECT 1 FROM perm_post WHERE post_id = 201);
  247. INSERT INTO perm_post (post_id, dept_id, post_code, post_name, post_sort, status, del_flag, create_by, create_time)
  248. SELECT 202, 101, 'staff', '普通岗', 2, '0', '0', 1, now()
  249. WHERE NOT EXISTS (SELECT 1 FROM perm_post WHERE post_id = 202);
  250. -- 角色:超级管理员(role_key=superadmin,代码侧超管判定依据之一)+ 普通角色
  251. INSERT INTO perm_role (role_id, role_name, role_key, role_sort, data_scope, status, del_flag, create_by, create_time, remark)
  252. SELECT 1, '超级管理员', 'superadmin', 1, '1', '0', '0', 1, now(), '超级管理员(拥有全部权限)'
  253. WHERE NOT EXISTS (SELECT 1 FROM perm_role WHERE role_id = 1);
  254. INSERT INTO perm_role (role_id, role_name, role_key, role_sort, data_scope, status, del_flag, create_by, create_time, remark)
  255. SELECT 2, '普通角色', 'common', 2, '5', '0', '0', 1, now(), '普通角色(默认仅本人数据范围,菜单按需分配)'
  256. WHERE NOT EXISTS (SELECT 1 FROM perm_role WHERE role_id = 2);
  257. -- 菜单:权限管理目录 + 5 个页面 + 按钮级权限
  258. -- 目录
  259. 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)
  260. SELECT 10000, '权限管理', 0, 90, 'perm', NULL, 'N', 'Y', 'M', '0', '0', '', 'i-mdi:shield-account-outline', 1, now(), '权限管理目录'
  261. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10000);
  262. -- 页面菜单(component 与前端 src/perm/views 对应)
  263. 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)
  264. SELECT 10001, '用户管理', 10000, 1, 'user', 'perm/user/index', 'N', 'Y', 'C', '0', '0', 'perm:user:list', 'i-mdi:account-group-outline', 1, now(), '用户管理菜单'
  265. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10001);
  266. 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)
  267. SELECT 10002, '角色管理', 10000, 2, 'role', 'perm/role/index', 'N', 'Y', 'C', '0', '0', 'perm:role:list', 'i-mdi:shield-check-outline', 1, now(), '角色管理菜单'
  268. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10002);
  269. 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)
  270. SELECT 10003, '菜单管理', 10000, 3, 'menu', 'perm/menu/index', 'N', 'Y', 'C', '0', '0', 'perm:menu:list', 'i-mdi:format-list-bulleted', 1, now(), '菜单管理菜单'
  271. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10003);
  272. 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)
  273. SELECT 10004, '部门管理', 10000, 4, 'dept', 'perm/dept/index', 'N', 'Y', 'C', '0', '0', 'perm:dept:list', 'i-mdi:file-tree-outline', 1, now(), '部门管理菜单'
  274. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10004);
  275. 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)
  276. SELECT 10005, '岗位管理', 10000, 5, 'post', 'perm/post/index', 'N', 'Y', 'C', '0', '0', 'perm:post:list', 'i-mdi:badge-account-outline', 1, now(), '岗位管理菜单'
  277. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10005);
  278. -- 用户管理按钮
  279. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  280. SELECT 10011, '用户查询', 10001, 1, 'F', '0', '0', 'perm:user:query', 1, now()
  281. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10011);
  282. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  283. SELECT 10012, '用户新增', 10001, 2, 'F', '0', '0', 'perm:user:add', 1, now()
  284. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10012);
  285. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  286. SELECT 10013, '用户修改', 10001, 3, 'F', '0', '0', 'perm:user:edit', 1, now()
  287. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10013);
  288. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  289. SELECT 10014, '用户删除', 10001, 4, 'F', '0', '0', 'perm:user:remove', 1, now()
  290. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10014);
  291. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  292. SELECT 10015, '重置密码', 10001, 5, 'F', '0', '0', 'perm:user:resetPwd', 1, now()
  293. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10015);
  294. -- 角色管理按钮
  295. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  296. SELECT 10021, '角色查询', 10002, 1, 'F', '0', '0', 'perm:role:query', 1, now()
  297. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10021);
  298. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  299. SELECT 10022, '角色新增', 10002, 2, 'F', '0', '0', 'perm:role:add', 1, now()
  300. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10022);
  301. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  302. SELECT 10023, '角色修改', 10002, 3, 'F', '0', '0', 'perm:role:edit', 1, now()
  303. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10023);
  304. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  305. SELECT 10024, '角色删除', 10002, 4, 'F', '0', '0', 'perm:role:remove', 1, now()
  306. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10024);
  307. -- 菜单管理按钮
  308. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  309. SELECT 10031, '菜单查询', 10003, 1, 'F', '0', '0', 'perm:menu:query', 1, now()
  310. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10031);
  311. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  312. SELECT 10032, '菜单新增', 10003, 2, 'F', '0', '0', 'perm:menu:add', 1, now()
  313. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10032);
  314. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  315. SELECT 10033, '菜单修改', 10003, 3, 'F', '0', '0', 'perm:menu:edit', 1, now()
  316. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10033);
  317. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  318. SELECT 10034, '菜单删除', 10003, 4, 'F', '0', '0', 'perm:menu:remove', 1, now()
  319. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10034);
  320. -- 部门管理按钮
  321. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  322. SELECT 10041, '部门查询', 10004, 1, 'F', '0', '0', 'perm:dept:query', 1, now()
  323. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10041);
  324. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  325. SELECT 10042, '部门新增', 10004, 2, 'F', '0', '0', 'perm:dept:add', 1, now()
  326. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10042);
  327. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  328. SELECT 10043, '部门修改', 10004, 3, 'F', '0', '0', 'perm:dept:edit', 1, now()
  329. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10043);
  330. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  331. SELECT 10044, '部门删除', 10004, 4, 'F', '0', '0', 'perm:dept:remove', 1, now()
  332. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10044);
  333. -- 岗位管理按钮
  334. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  335. SELECT 10051, '岗位查询', 10005, 1, 'F', '0', '0', 'perm:post:query', 1, now()
  336. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10051);
  337. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  338. SELECT 10052, '岗位新增', 10005, 2, 'F', '0', '0', 'perm:post:add', 1, now()
  339. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10052);
  340. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  341. SELECT 10053, '岗位修改', 10005, 3, 'F', '0', '0', 'perm:post:edit', 1, now()
  342. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10053);
  343. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, menu_type, visible, status, perms, create_by, create_time)
  344. SELECT 10054, '岗位删除', 10005, 4, 'F', '0', '0', 'perm:post:remove', 1, now()
  345. WHERE NOT EXISTS (SELECT 1 FROM perm_menu WHERE menu_id = 10054);
  346. -- ---------------------------------------------------------------------------
  347. -- 存量路径口径修正(幂等):C 的 path 存完整路由路径(无前导 /)、
  348. -- component 存 /views 之后相对路径(无前导 /);M 的 path 存分组 id 或短词。
  349. -- 旧种子曾按 RuoYi 短 path 口径写入,这里统一为前端路由口径。
  350. -- ---------------------------------------------------------------------------
  351. UPDATE perm_menu SET path = '10000' WHERE menu_id = 10000 AND path = 'perm';
  352. UPDATE perm_menu SET path = 'perm/user/index' WHERE menu_id = 10001 AND path = 'user';
  353. UPDATE perm_menu SET path = 'perm/role/index' WHERE menu_id = 10002 AND path = 'role';
  354. UPDATE perm_menu SET path = 'perm/menu/index' WHERE menu_id = 10003 AND path = 'menu';
  355. UPDATE perm_menu SET path = 'perm/dept/index' WHERE menu_id = 10004 AND path = 'dept';
  356. UPDATE perm_menu SET path = 'perm/post/index' WHERE menu_id = 10005 AND path = 'post';
  357. -- 图标与前端 menu.json 口径对齐(存量种子曾用的旧图标幂等修正)
  358. UPDATE perm_menu SET icon = 'i-mdi:account-group-outline' WHERE menu_id = 10001 AND icon = 'i-mdi:account-outline';
  359. UPDATE perm_menu SET icon = 'i-mdi:shield-check-outline' WHERE menu_id = 10002 AND icon = 'i-mdi:account-group-outline';
  360. -- menu-outline 不是 mdi 集合中的有效图标(运行时 0×0),统一替换为列表图标
  361. UPDATE perm_menu SET icon = 'i-mdi:format-list-bulleted' WHERE menu_id = 10003 AND icon = 'i-mdi:menu-outline';
  362. -- ============================================================================
  363. -- 业务菜单种子(融合阶段):id 沿用前端 menu.json 的 1000 段,path/name 口径不变
  364. -- M 目录 / C 页面 / F 操作;F 的 menu_id = 父 C id × 100 + 序号
  365. -- ext JSON 承载 affix / group / permsOnly,不新增表字段
  366. -- 图标仅复用 uno.config.ts safelist 现有项(新增图标须同步 safelist)
  367. -- ============================================================================
  368. 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)
  369. 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()
  370. FROM (VALUES
  371. (1000::bigint, '案件数据', 0::bigint, 1, 'data/index', 'data/index', 'C', '0', '0', 'case:data:list', 'i-mdi:briefcase-outline', '{"affix":true}'),
  372. (1001, '数据画像', 0, 2, 'dm/portrait', 'dm/portrait', 'C', '0', '0', 'dm:portrait:list', 'i-mdi:account-details-outline', ''),
  373. (1002, '通信分析', 0, 3, '1002', NULL, 'M', '0', '0', '', 'i-mdi:message-text-outline', ''),
  374. (1003, '持续联系', 1002, 1, 'continuous/index', 'continuous/index', 'C', '0', '0', 'call:continuous:list', 'i-mdi:phone-sync-outline', ''),
  375. (1004, '夜间联系', 1002, 2, 'night/index', 'night/index', 'C', '0', '0', 'call:night:list', 'i-mdi:weather-night', ''),
  376. (1005, '敏感日期联系', 1002, 3, 'sensitive/index', 'sensitive/index', 'C', '0', '0', 'call:sensitive:list', 'i-mdi:calendar-alert', ''),
  377. (1006, '交易分析', 0, 4, '1006', NULL, 'M', '0', '0', '', 'i-mdi:bank-transfer', ''),
  378. (1007, '敏感日期交易', 1006, 1, 'transSensitive/index', 'transSensitive/index', 'C', '0', '0', 'trans:sensitive:list', 'i-mdi:calendar-star', ''),
  379. (1008, '每月规律交易', 1006, 2, 'monthRegular/index', 'monthRegular/index', 'C', '0', '0', 'trans:continuous:list', 'i-mdi:calendar-sync-outline', ''),
  380. (1009, '交易频率分析', 1006, 3, 'tradeFrequency/index', 'tradeFrequency/index', 'C', '0', '0', 'trans:frequency:list', 'i-mdi:chart-timeline-variant', ''),
  381. (1010, '大额交易分析', 1006, 4, 'largeAmount/index', 'largeAmount/index', 'C', '0', '0', 'trans:big:list', 'i-mdi:cash-multiple', ''),
  382. (1011, '资金流向分析', 1006, 5, 'capitalFlow/index', 'capitalFlow/index', 'C', '0', '0', 'trans:fundsFlow:list', 'i-mdi:swap-horizontal', ''),
  383. (1012, '现金交易分析', 1006, 6, 'cashTrans/index', 'cashTrans/index', 'C', '0', '0', 'trans:cashFlow:list', 'i-mdi:cash', ''),
  384. (1013, '资金快转分析', 1006, 7, 'fastTransfer/index', 'fastTransfer/index', 'C', '0', '0', 'trans:fastFundFlow:list', 'i-mdi:flash-outline', ''),
  385. (1014, '银行卡代持分析', 1006, 8, 'cardHolding/index', 'cardHolding/index', 'C', '0', '0', 'trans:cardHold:list', 'i-mdi:credit-card-outline', ''),
  386. (1015, '定期存取分析', 1006, 9, 'fixedDeposit/index', 'fixedDeposit/index', 'C', '0', '0', 'trans:fixedDeposit:list', 'i-mdi:bank-outline', ''),
  387. (1016, '金融理财分析', 1006, 10, 'financialWealth/index', 'financialWealth/index', 'C', '0', '0', 'trans:financial:list', 'i-mdi:finance', ''),
  388. (1017, '活动轨迹分析', 0, 5, '1017', NULL, 'M', '0', '0', '', 'i-mdi:map-marker-path', ''),
  389. (1018, '同行分析', 1017, 1, 'peerAnalysis/index', 'peerAnalysis/index', 'C', '0', '0', 'track:togetherTravel:list', 'i-mdi:account-group-outline', ''),
  390. (1019, '同住分析', 1017, 2, 'coResidence/index', 'coResidence/index', 'C', '0', '0', 'track:togetherLive:list', 'i-mdi:bed-outline', ''),
  391. (1020, '碰面分析', 1017, 3, 'faceToFace/index', 'faceToFace/index', 'C', '0', '0', 'track:meet:list', 'i-mdi:handshake-outline', ''),
  392. (1021, '早晚通话地', 1017, 4, 'morningEveningCall/index', 'morningEveningCall/index', 'C', '0', '0', 'track:enLocal:list', 'i-mdi:phone-in-talk-outline', ''),
  393. (1022, '快递收发地', 1017, 5, 'expressDelivery/index', 'expressDelivery/index', 'C', '0', '0', 'track:express:list', 'i-mdi:package-variant-closed', ''),
  394. (1023, '通话基站分析', 1017, 6, 'baseStation/index', 'baseStation/index', 'C', '0', '0', 'track:cellTower:list', 'i-mdi:signal-variant', ''),
  395. (1024, '行为时空分析', 0, 6, 'behaviorSpacetime/index', 'behaviorSpacetime/index', 'C', '0', '0', 'otg:behavior:list', 'i-mdi:map-clock-outline', ''),
  396. (1028, '对象关系分析', 0, 7, 'graph/g6-editor', 'graph/g6-editor', 'C', '0', '0', 'graph:editor:list', 'i-mdi:graph-outline', ''),
  397. (10262, '基站查询', 0, 8, 'aiBaseStation/index', 'aiBaseStation/index', 'C', '0', '0', 'tower:aibase:list', 'i-mdi:radio-tower', '')
  398. ) AS v(menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, ext)
  399. WHERE NOT EXISTS (SELECT 1 FROM perm_menu m WHERE m.menu_id = v.menu_id);
  400. -- 业务页只菜单级权限(用户决策):有菜单即有操作权限,操作 F 节点退库(幂等清理)
  401. DELETE FROM perm_role_menu WHERE menu_id IN (
  402. SELECT menu_id FROM perm_menu WHERE menu_type = 'F' AND parent_id IN
  403. (1000,1001,1003,1004,1005,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,
  404. 1018,1019,1020,1021,1022,1023,1024,10262,1027,1028,1029,1091,1092));
  405. DELETE FROM perm_menu WHERE menu_type = 'F' AND parent_id IN
  406. (1000,1001,1003,1004,1005,1007,1008,1009,1010,1011,1012,1013,1014,1015,1016,
  407. 1018,1019,1020,1021,1022,1023,1024,10262,1027,1028,1029,1091,1092);
  408. -- 对象关系分析只留 g6-editor 一个入口;隐藏视图页与 Relation Graph 测试页下线
  409. DELETE FROM perm_role_menu WHERE menu_id IN (1027, 1029);
  410. DELETE FROM perm_menu WHERE menu_id IN (1027, 1029);
  411. -- 纯操作码容器(无路由):案件管理 /case、模板库(内嵌组件)
  412. -- getRouters 跳过 permsOnly 子树;菜单码照常经 selectMenuPermsByUserId 收集
  413. -- 业务页只菜单级:C 节点 perms 即该页全部操作的准入码
  414. -- 注:系统维护(/sys 工具/清缓存/升级)与向量调试无对应页面,不走菜单权限,仅登录校验
  415. 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)
  416. 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()
  417. FROM (VALUES
  418. (1090::bigint, '系统操作', 0::bigint, 99, '1090', 'M', ''),
  419. (1091, '案件管理', 1090, 1, 'case', 'C', 'case:manage:list'),
  420. (1092, '模板管理', 1090, 2, 'tpl', 'C', 'tpl:tpl:list')
  421. ) AS v(menu_id, menu_name, parent_id, order_num, path, menu_type, perms)
  422. WHERE NOT EXISTS (SELECT 1 FROM perm_menu m WHERE m.menu_id = v.menu_id);
  423. UPDATE perm_menu SET perms = 'case:manage:list' WHERE menu_id = 1091 AND (perms IS NULL OR perms = '');
  424. UPDATE perm_menu SET perms = 'tpl:tpl:list' WHERE menu_id = 1092 AND (perms IS NULL OR perms = '');
  425. -- 系统维护无页面:移除历史种子节点与授权(幂等),相关端点仅登录校验
  426. DELETE FROM perm_role_menu WHERE menu_id IN (1093, 109301, 109302, 109303);
  427. DELETE FROM perm_menu WHERE menu_id IN (1093, 109301, 109302, 109303);
  428. -- ============================================================================
  429. -- 智能工作台菜单种子(20000 段):ext.group='workbench',主窗口导航不渲染,
  430. -- 工作台窗口取该子树做侧栏;路由仍为静态 WorkbenchRoute(导航 DB 化≠路由动态化)
  431. -- ============================================================================
  432. 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)
  433. 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()
  434. FROM (VALUES
  435. (20000::bigint, '智能工作台', 0::bigint, 100, 'workbench', NULL, 'M', '', 'i-mdi:robot-outline', '{"group":"workbench"}'),
  436. (20100, '智能体', 20000, 1, 'workbench/agent', NULL, 'M', '', 'i-mdi:robot-outline', ''),
  437. (20101, '任务工作台', 20100, 1, 'workbench/chat', 'workbench/chat/index', 'C', 'ai:chat:query', 'i-mdi:message-text-outline', ''),
  438. (20102, '数剑配置', 20100, 2, 'workbench/agent/entry', 'workbench/agent/entry/index', 'C', 'ai:agent:query', 'i-mdi:star-outline', ''),
  439. (20103, '专家配置', 20100, 3, 'workbench/agent/expert', 'workbench/agent/expert/index', 'C', 'ai:expert:query', 'i-mdi:account-tie-outline', ''),
  440. (20104, '任务管理', 20100, 4, 'workbench/agent/session', 'workbench/agent/session/index', 'C', 'ai:session:query', 'i-mdi:format-list-bulleted', ''),
  441. (20105, '长期记忆', 20100, 5, 'workbench/agent/memory', 'workbench/agent/memory/index', 'C', 'ai:memory:query', 'i-mdi:history', ''),
  442. (20106, '技能仓库', 20100, 6, 'workbench/agent/skill', 'workbench/agent/skill/index', 'C', 'ai:skill:query', 'i-mdi:lightbulb-on-outline', ''),
  443. (20107, '场景包', 20100, 7, 'workbench/agent/pack', 'workbench/agent/pack/index', 'C', 'ai:pack:query', 'i-mdi:package-variant-closed', ''),
  444. (20200, '模型与推理', 20000, 2, 'workbench/model', NULL, 'M', '', 'i-mdi:chip', ''),
  445. (20201, '模型配置', 20200, 1, 'workbench/model/config', 'workbench/model/config/index', 'C', 'ai:model:query', 'i-mdi:server', ''),
  446. (20203, '上下文与压缩策略', 20200, 3, 'workbench/model/compaction', 'workbench/model/compaction/index', 'C', 'ai:runtime:query', 'i-mdi:arrow-collapse-vertical', ''),
  447. (20300, '知识库', 20000, 3, 'workbench/kb', NULL, 'M', '', 'i-mdi:bookshelf', ''),
  448. (20301, '知识库管理', 20300, 1, 'workbench/kb/base', 'workbench/kb/base/index', 'C', 'ai:kb:query', 'i-mdi:book-open-page-variant-outline', ''),
  449. (20302, '文档与切片', 20300, 2, 'workbench/kb/doc', 'workbench/kb/doc/index', 'C', 'ai:doc:query', 'i-mdi:file-pdf-box', ''),
  450. (20303, '检索测试', 20300, 3, 'workbench/kb/retrieval', 'workbench/kb/retrieval/index', 'C', 'ai:retrieval:query', 'i-mdi:magnify', ''),
  451. (20400, '工具与集成', 20000, 4, 'workbench/integration', NULL, 'M', '', 'i-mdi:link-variant', ''),
  452. (20401, '业务工具注册', 20400, 1, 'workbench/integration/registry', 'workbench/integration/registry/index', 'C', 'ai:tool:query', 'i-mdi:tools', ''),
  453. (20402, 'MCP 服务端', 20400, 2, 'workbench/integration/mcp-server', 'workbench/integration/mcpServer/index', 'C', 'ai:mcpServer:query', 'i-mdi:server-network', ''),
  454. (20403, 'MCP 客户端', 20400, 3, 'workbench/integration/mcp-client', 'workbench/integration/mcpClient/index', 'C', 'ai:mcpClient:query', 'i-mdi:lan-connect', ''),
  455. (20500, '审计与观测', 20000, 5, 'workbench/audit', NULL, 'M', '', 'i-mdi:monitor-dashboard', ''),
  456. (20501, '会话与消息审计', 20500, 1, 'workbench/audit/message', 'workbench/audit/message/index', 'C', 'ai:audit:query', 'i-mdi:message-text-outline', ''),
  457. (20502, '工具调用明细', 20500, 2, 'workbench/audit/tool-call', 'workbench/audit/toolCall/index', 'C', 'ai:auditTool:query', 'i-mdi:progress-wrench', ''),
  458. (20503, '链路追踪', 20500, 3, 'workbench/audit/trace', 'workbench/audit/trace/index', 'C', 'ai:trace:query', 'i-mdi:transit-connection-variant', ''),
  459. (20504, '编排运行', 20500, 4, 'workbench/audit/route-run', 'workbench/audit/routeRun/index', 'C', 'ai:route:query', 'i-mdi:call-split', ''),
  460. (20505, '健康自检', 20500, 5, 'workbench/audit/health', 'workbench/audit/health/index', 'C', 'ai:health:query', 'i-mdi:heart-pulse', '')
  461. ) AS v(menu_id, menu_name, parent_id, order_num, path, component, menu_type, perms, icon, ext)
  462. WHERE NOT EXISTS (SELECT 1 FROM perm_menu m WHERE m.menu_id = v.menu_id);
  463. -- compress 不是 mdi 集合中的有效图标(运行时 0×0),统一替换为箭头收起图标
  464. UPDATE perm_menu SET icon = 'i-mdi:arrow-collapse-vertical' WHERE menu_id = 20203 AND icon = 'i-mdi:compress';
  465. INSERT INTO perm_menu (menu_id, menu_name, parent_id, order_num, path, component, menu_type, visible, status, perms, icon, create_by, create_time)
  466. SELECT v.menu_id, v.menu_name, v.parent_id, v.order_num, '', NULL, 'F', '0', '0', v.perms, '#', 1, now()
  467. FROM (VALUES
  468. (2010101::bigint, '会话查询', 20101::bigint, 1, 'ai:chat:query'),
  469. (2010102, '发送消息', 20101, 2, 'ai:chat:add'),
  470. (2010103, '会话编辑', 20101, 3, 'ai:chat:edit'),
  471. (2010104, '会话删除', 20101, 4, 'ai:chat:remove'),
  472. (2010105, '执行与停止', 20101, 5, 'ai:chat:execute'),
  473. (2010106, '打开工作台', 20101, 6, 'ai:workbench:execute'),
  474. (2010201, '配置查询', 20102, 1, 'ai:agent:query'),
  475. (2010202, '配置编辑', 20102, 2, 'ai:agent:edit'),
  476. (2010301, '专家查询', 20103, 1, 'ai:expert:query'),
  477. (2010302, '专家编辑', 20103, 2, 'ai:expert:edit'),
  478. (2010303, '专家删除', 20103, 3, 'ai:expert:remove'),
  479. (2010401, '任务查询', 20104, 1, 'ai:session:query'),
  480. (2010402, '任务关闭', 20104, 2, 'ai:session:edit'),
  481. (2010403, '任务删除', 20104, 3, 'ai:session:remove'),
  482. (2010404, '任务导出', 20104, 4, 'ai:session:export'),
  483. (2010501, '记忆查询', 20105, 1, 'ai:memory:query'),
  484. (2010502, '记忆编辑', 20105, 2, 'ai:memory:edit'),
  485. (2010503, '记忆删除', 20105, 3, 'ai:memory:remove'),
  486. (2010601, '技能查询', 20106, 1, 'ai:skill:query'),
  487. (2010602, '技能编辑', 20106, 2, 'ai:skill:edit'),
  488. (2010603, '技能删除', 20106, 3, 'ai:skill:remove'),
  489. (2010701, '场景包查询', 20107, 1, 'ai:pack:query'),
  490. (2010702, '场景包编辑', 20107, 2, 'ai:pack:edit'),
  491. (2010703, '场景包删除', 20107, 3, 'ai:pack:remove'),
  492. (2020101, '模型查询', 20201, 1, 'ai:model:query'),
  493. (2020102, '模型编辑', 20201, 2, 'ai:model:edit'),
  494. (2020103, '模型删除', 20201, 3, 'ai:model:remove'),
  495. (2020301, '策略查询', 20203, 1, 'ai:runtime:query'),
  496. (2020302, '策略编辑', 20203, 2, 'ai:runtime:edit'),
  497. (2030101, '知识库查询', 20301, 1, 'ai:kb:query'),
  498. (2030102, '知识库编辑', 20301, 2, 'ai:kb:edit'),
  499. (2030103, '知识库删除', 20301, 3, 'ai:kb:remove'),
  500. (2030201, '文档查询', 20302, 1, 'ai:doc:query'),
  501. (2030202, '文档编辑', 20302, 2, 'ai:doc:edit'),
  502. (2030203, '文档删除', 20302, 3, 'ai:doc:remove'),
  503. (2030301, '检索查询', 20303, 1, 'ai:retrieval:query'),
  504. (2030302, '检索测试', 20303, 2, 'ai:retrieval:execute'),
  505. (2040101, '工具查询', 20401, 1, 'ai:tool:query'),
  506. (2040102, '工具编辑', 20401, 2, 'ai:tool:edit'),
  507. (2040103, '工具删除', 20401, 3, 'ai:tool:remove'),
  508. (2040201, '服务端查询', 20402, 1, 'ai:mcpServer:query'),
  509. (2040202, '服务端编辑', 20402, 2, 'ai:mcpServer:edit'),
  510. (2040301, '客户端查询', 20403, 1, 'ai:mcpClient:query'),
  511. (2040302, '客户端编辑', 20403, 2, 'ai:mcpClient:edit'),
  512. (2040303, '客户端删除', 20403, 3, 'ai:mcpClient:remove'),
  513. (2050101, '审计查询', 20501, 1, 'ai:audit:query'),
  514. (2050102, '审计导出', 20501, 2, 'ai:audit:export'),
  515. (2050103, '保留策略编辑', 20501, 3, 'ai:audit:edit'),
  516. (2050201, '工具调用查询', 20502, 1, 'ai:auditTool:query'),
  517. (2050301, '链路查询', 20503, 1, 'ai:trace:query'),
  518. (2050401, '编排查询', 20504, 1, 'ai:route:query'),
  519. (2050501, '自检查询', 20505, 1, 'ai:health:query'),
  520. (2050502, '执行自检', 20505, 2, 'ai:health:execute')
  521. ) AS v(menu_id, menu_name, parent_id, order_num, perms)
  522. WHERE NOT EXISTS (SELECT 1 FROM perm_menu m WHERE m.menu_id = v.menu_id);
  523. -- 「用量与配额」菜单下架(页面与路由代码保留):幂等清理菜单、按钮与角色绑定
  524. DELETE FROM perm_role_menu WHERE menu_id IN (20202, 2020201, 2020202);
  525. DELETE FROM perm_menu WHERE menu_id IN (20202, 2020201, 2020202);
  526. -- 超级管理员绑定全部菜单(新增菜单后重跑本段即可补齐)
  527. INSERT INTO perm_role_menu (role_id, menu_id)
  528. SELECT 1, m.menu_id FROM perm_menu m
  529. WHERE NOT EXISTS (SELECT 1 FROM perm_role_menu rm WHERE rm.role_id = 1 AND rm.menu_id = m.menu_id);
  530. -- ============================================================================
  531. -- 内置超管种子(幂等):本脚本自包含,不再依赖 sys_user。
  532. -- 哈希与历史 sql/user.sql 的 admin 初始常量一致(admin123,首登后改密);
  533. -- 存量库若已由历史迁移段写入同 ID 账号,NOT EXISTS 命中即跳过。
  534. -- 历史存量迁移(sys_user → perm_user,保留原 ID)已于 2026-11-18 执行完毕,
  535. -- 迁移段随融合下线;sys_user 表仅保留作回滚兜底,代码不再读写。
  536. -- ============================================================================
  537. INSERT INTO perm_user (user_id, dept_id, user_name, nick_name, password, status, del_flag, create_by, create_time, remark)
  538. SELECT 1, 101, 'admin', '系统管理员',
  539. '$2a$10$dwBgMS1xW2PbUsuce16g/ujiVEH2H4pAjWoyeSMqLClwfOs1N9fri', '0', '0', 1, now(), '内置超管(admin123,首登后改密)'
  540. WHERE NOT EXISTS (SELECT 1 FROM perm_user WHERE user_id = 1);
  541. INSERT INTO perm_user_role (user_id, role_id)
  542. SELECT 1, 1
  543. WHERE NOT EXISTS (SELECT 1 FROM perm_user_role WHERE user_id = 1 AND role_id = 1);
  544. -- ============================================================================
  545. -- 普通角色默认授权(幂等):融合前业务接口无校验,普通账号全功能可用;
  546. -- 为不回归,common 默认授予全部业务/工作台/系统操作菜单与操作码,
  547. -- 仅排除 perm 管理子树(perms 以 perm: 开头 + 目录 10000)与向量调试码。
  548. -- 管理员后续按角色回收即可收紧。
  549. -- ============================================================================
  550. INSERT INTO perm_role_menu (role_id, menu_id)
  551. SELECT 2, m.menu_id FROM perm_menu m
  552. WHERE m.perms NOT LIKE 'perm:%'
  553. AND m.menu_id <> 10000
  554. AND m.perms <> 'embedding:debug:execute'
  555. AND NOT EXISTS (SELECT 1 FROM perm_role_menu rm WHERE rm.role_id = 2 AND rm.menu_id = m.menu_id);
  556. -- ============================================================================
  557. -- 校验
  558. -- ============================================================================
  559. -- SELECT user_id, user_name, nick_name, status FROM perm_user ORDER BY user_id;
  560. -- SELECT ur.user_id, r.role_key FROM perm_user_role ur JOIN perm_role r ON r.role_id = ur.role_id;
  561. -- SELECT count(*) FROM perm_role_menu WHERE role_id = 1; -- 应等于 perm_menu 总数