ai_visible_scope_20261118.sql 5.0 KB

123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115
  1. -- ============================================================
  2. -- AI 记录级数据权限:可见范围(visible_scope)+ 归属部门(owner_dept_id)
  3. --
  4. -- 适用表:ai_entry_agent / ai_expert_agent / ai_skill / ai_scenario_pack / ai_knowledge_base
  5. -- 执行方式:手工在目标库执行(项目无 Flyway/Liquibase),脚本幂等,可重复执行。
  6. --
  7. -- 存量语义保持:
  8. -- * 入口智能体为全局单例,空值统一按「全员」处理;
  9. -- * 其余表原口径为「内置 or 创建人本人」,故内置数据回填 '0'(全员/公开),
  10. -- 非内置数据回填 '2'(仅创建人/本人),避免存量私有数据被意外公开。
  11. -- * 已存在旧 visible_scope 值(如从 bizbuddy 迁移的库)时保留原值,不覆盖。
  12. -- ============================================================
  13. -- ---------- 1. 加列(先不加默认值,便于区分存量空值) ----------
  14. alter table ai_entry_agent add column if not exists owner_dept_id bigint;
  15. alter table ai_entry_agent add column if not exists visible_scope char(1);
  16. alter table ai_expert_agent add column if not exists owner_dept_id bigint;
  17. alter table ai_expert_agent add column if not exists visible_scope char(1);
  18. alter table ai_skill add column if not exists owner_dept_id bigint;
  19. alter table ai_skill add column if not exists visible_scope char(1);
  20. alter table ai_scenario_pack add column if not exists owner_dept_id bigint;
  21. alter table ai_scenario_pack add column if not exists visible_scope char(1);
  22. alter table ai_knowledge_base add column if not exists owner_dept_id bigint;
  23. alter table ai_knowledge_base add column if not exists visible_scope char(1);
  24. -- ---------- 2. 存量数据回填 ----------
  25. -- 入口智能体:全局单例,空值按全员处理
  26. update ai_entry_agent
  27. set visible_scope = '0'
  28. where visible_scope is null or visible_scope = '';
  29. -- 其余四类:内置 -> 全员/公开;非内置 -> 仅创建人/本人
  30. update ai_expert_agent
  31. set visible_scope = case when is_builtin = '1' then '0' else '2' end
  32. where visible_scope is null or visible_scope = '';
  33. update ai_skill
  34. set visible_scope = case when is_builtin = '1' then '0' else '2' end
  35. where visible_scope is null or visible_scope = '';
  36. update ai_scenario_pack
  37. set visible_scope = case when is_builtin = '1' then '0' else '2' end
  38. where visible_scope is null or visible_scope = '';
  39. update ai_knowledge_base
  40. set visible_scope = case when is_builtin = '1' then '0' else '2' end
  41. where visible_scope is null or visible_scope = '';
  42. -- 旧数据已指定「本部门」但归属部门为空:按创建人部门补齐
  43. update ai_entry_agent a
  44. set owner_dept_id = u.dept_id
  45. from perm_user u
  46. where a.owner_dept_id is null
  47. and a.visible_scope = '1'
  48. and a.create_by = u.user_id
  49. and u.dept_id is not null;
  50. update ai_expert_agent a
  51. set owner_dept_id = u.dept_id
  52. from perm_user u
  53. where a.owner_dept_id is null
  54. and a.visible_scope = '1'
  55. and a.create_by = u.user_id
  56. and u.dept_id is not null;
  57. update ai_skill a
  58. set owner_dept_id = u.dept_id
  59. from perm_user u
  60. where a.owner_dept_id is null
  61. and a.visible_scope = '1'
  62. and a.create_by = u.user_id
  63. and u.dept_id is not null;
  64. update ai_scenario_pack a
  65. set owner_dept_id = u.dept_id
  66. from perm_user u
  67. where a.owner_dept_id is null
  68. and a.visible_scope = '1'
  69. and a.create_by = u.user_id
  70. and u.dept_id is not null;
  71. update ai_knowledge_base a
  72. set owner_dept_id = u.dept_id
  73. from perm_user u
  74. where a.owner_dept_id is null
  75. and a.visible_scope = '1'
  76. and a.create_by = u.user_id
  77. and u.dept_id is not null;
  78. -- ---------- 3. 新数据默认值 ----------
  79. alter table ai_entry_agent alter column visible_scope set default '0';
  80. alter table ai_expert_agent alter column visible_scope set default '0';
  81. alter table ai_skill alter column visible_scope set default '0';
  82. alter table ai_scenario_pack alter column visible_scope set default '0';
  83. alter table ai_knowledge_base alter column visible_scope set default '0';
  84. -- ---------- 4. 列注释 ----------
  85. comment on column ai_entry_agent.owner_dept_id is '归属部门(可见范围=本部门时生效)';
  86. comment on column ai_entry_agent.visible_scope is '可见范围(0全员 1本部门 2仅创建人)';
  87. comment on column ai_expert_agent.owner_dept_id is '归属部门(可见范围=本部门时生效)';
  88. comment on column ai_expert_agent.visible_scope is '可见范围(0全员 1本部门 2仅创建人)';
  89. comment on column ai_skill.owner_dept_id is '归属部门(生效范围=本部门时生效)';
  90. comment on column ai_skill.visible_scope is '生效范围(0公开 1本部门 2仅本人)';
  91. comment on column ai_scenario_pack.owner_dept_id is '归属部门(生效范围=本部门时生效)';
  92. comment on column ai_scenario_pack.visible_scope is '生效范围(0公开 1本部门 2仅本人)';
  93. comment on column ai_knowledge_base.owner_dept_id is '归属部门(生效范围=本部门时生效)';
  94. comment on column ai_knowledge_base.visible_scope is '生效范围(0公开 1本部门 2仅本人)';