freedakgmail
5afe021b56
- JWT 载荷增加 uid；登录返回 id；前端持久化 uid 并在变更请求中携带 userId - 操作人服务端解析（优先JWT.uid，回退body.userId），审计写入 actor_id + 当时显示名 - audit_logs 增加 actor_id 列；持久化与加载带 actorId - assessments.assessorId 改存用户ID，列表/详情按ID解析显示名（assessorName） - 看板待办「分给我的」改为按 userId 匹配；发起人显示真实姓名 - 审批线指派按 salesId(用户ID) 计算 - scripts/backfill-actor-ids.sql 回填历史（旧账号名→当前用户ID），本地+生产已执行
38 lines
1.5 KiB
SQL
38 lines
1.5 KiB
SQL
-- 历史数据回填：把按"人名字符串"关联的记录改为按用户 ID 关联。
|
|
-- 处理旧账号名→当前账号名的改名映射，再解析为用户 ID。可重复执行（幂等）。
|
|
|
|
WITH alias(oldname, newname) AS (VALUES
|
|
('销售账号','张伟'), ('销售二','王芳'), ('销售三','李娜'),
|
|
('风控账号','刘洋'), ('风控二','陈静'),
|
|
('管理账号','赵磊'), ('管理二','孙莉'),
|
|
('系统账号','周强')
|
|
)
|
|
UPDATE assessments a
|
|
SET assessment = jsonb_set(a.assessment, '{assessorId}', to_jsonb(u.id))
|
|
FROM alias al JOIN users u ON u.username = al.newname
|
|
WHERE a.assessment->>'assessorId' = al.oldname;
|
|
|
|
-- assessorId 已是当前用户名 → 也转为 ID
|
|
UPDATE assessments a
|
|
SET assessment = jsonb_set(a.assessment, '{assessorId}', to_jsonb(u.id))
|
|
FROM users u
|
|
WHERE a.assessment->>'assessorId' = u.username;
|
|
|
|
-- 审计：按旧名映射回填 actor_id，并把 username 刷新为当前账号名
|
|
WITH alias(oldname, newname) AS (VALUES
|
|
('销售账号','张伟'), ('销售二','王芳'), ('销售三','李娜'),
|
|
('风控账号','刘洋'), ('风控二','陈静'),
|
|
('管理账号','赵磊'), ('管理二','孙莉'),
|
|
('系统账号','周强')
|
|
)
|
|
UPDATE audit_logs al2
|
|
SET actor_id = u.id, username = u.username
|
|
FROM alias al JOIN users u ON u.username = al.newname
|
|
WHERE al2.actor_id IS NULL AND al2.username = al.oldname;
|
|
|
|
-- 审计：username 已是当前账号名 → 回填 actor_id
|
|
UPDATE audit_logs al2
|
|
SET actor_id = u.id
|
|
FROM users u
|
|
WHERE al2.actor_id IS NULL AND al2.username = u.username;
|