-- ============================================================ -- AI 记录级数据权限:可见范围(visible_scope)+ 归属部门(owner_dept_id) -- -- 适用表:ai_entry_agent / ai_expert_agent / ai_skill / ai_scenario_pack / ai_knowledge_base -- 执行方式:手工在目标库执行(项目无 Flyway/Liquibase),脚本幂等,可重复执行。 -- -- 存量语义保持: -- * 入口智能体为全局单例,空值统一按「全员」处理; -- * 其余表原口径为「内置 or 创建人本人」,故内置数据回填 '0'(全员/公开), -- 非内置数据回填 '2'(仅创建人/本人),避免存量私有数据被意外公开。 -- * 已存在旧 visible_scope 值(如从 bizbuddy 迁移的库)时保留原值,不覆盖。 -- ============================================================ -- ---------- 1. 加列(先不加默认值,便于区分存量空值) ---------- alter table ai_entry_agent add column if not exists owner_dept_id bigint; alter table ai_entry_agent add column if not exists visible_scope char(1); alter table ai_expert_agent add column if not exists owner_dept_id bigint; alter table ai_expert_agent add column if not exists visible_scope char(1); alter table ai_skill add column if not exists owner_dept_id bigint; alter table ai_skill add column if not exists visible_scope char(1); alter table ai_scenario_pack add column if not exists owner_dept_id bigint; alter table ai_scenario_pack add column if not exists visible_scope char(1); alter table ai_knowledge_base add column if not exists owner_dept_id bigint; alter table ai_knowledge_base add column if not exists visible_scope char(1); -- ---------- 2. 存量数据回填 ---------- -- 入口智能体:全局单例,空值按全员处理 update ai_entry_agent set visible_scope = '0' where visible_scope is null or visible_scope = ''; -- 其余四类:内置 -> 全员/公开;非内置 -> 仅创建人/本人 update ai_expert_agent set visible_scope = case when is_builtin = '1' then '0' else '2' end where visible_scope is null or visible_scope = ''; update ai_skill set visible_scope = case when is_builtin = '1' then '0' else '2' end where visible_scope is null or visible_scope = ''; update ai_scenario_pack set visible_scope = case when is_builtin = '1' then '0' else '2' end where visible_scope is null or visible_scope = ''; update ai_knowledge_base set visible_scope = case when is_builtin = '1' then '0' else '2' end where visible_scope is null or visible_scope = ''; -- 旧数据已指定「本部门」但归属部门为空:按创建人部门补齐 update ai_entry_agent a set owner_dept_id = u.dept_id from perm_user u where a.owner_dept_id is null and a.visible_scope = '1' and a.create_by = u.user_id and u.dept_id is not null; update ai_expert_agent a set owner_dept_id = u.dept_id from perm_user u where a.owner_dept_id is null and a.visible_scope = '1' and a.create_by = u.user_id and u.dept_id is not null; update ai_skill a set owner_dept_id = u.dept_id from perm_user u where a.owner_dept_id is null and a.visible_scope = '1' and a.create_by = u.user_id and u.dept_id is not null; update ai_scenario_pack a set owner_dept_id = u.dept_id from perm_user u where a.owner_dept_id is null and a.visible_scope = '1' and a.create_by = u.user_id and u.dept_id is not null; update ai_knowledge_base a set owner_dept_id = u.dept_id from perm_user u where a.owner_dept_id is null and a.visible_scope = '1' and a.create_by = u.user_id and u.dept_id is not null; -- ---------- 3. 新数据默认值 ---------- alter table ai_entry_agent alter column visible_scope set default '0'; alter table ai_expert_agent alter column visible_scope set default '0'; alter table ai_skill alter column visible_scope set default '0'; alter table ai_scenario_pack alter column visible_scope set default '0'; alter table ai_knowledge_base alter column visible_scope set default '0'; -- ---------- 4. 列注释 ---------- comment on column ai_entry_agent.owner_dept_id is '归属部门(可见范围=本部门时生效)'; comment on column ai_entry_agent.visible_scope is '可见范围(0全员 1本部门 2仅创建人)'; comment on column ai_expert_agent.owner_dept_id is '归属部门(可见范围=本部门时生效)'; comment on column ai_expert_agent.visible_scope is '可见范围(0全员 1本部门 2仅创建人)'; comment on column ai_skill.owner_dept_id is '归属部门(生效范围=本部门时生效)'; comment on column ai_skill.visible_scope is '生效范围(0公开 1本部门 2仅本人)'; comment on column ai_scenario_pack.owner_dept_id is '归属部门(生效范围=本部门时生效)'; comment on column ai_scenario_pack.visible_scope is '生效范围(0公开 1本部门 2仅本人)'; comment on column ai_knowledge_base.owner_dept_id is '归属部门(生效范围=本部门时生效)'; comment on column ai_knowledge_base.visible_scope is '生效范围(0公开 1本部门 2仅本人)';