xiaoshu-app/scripts/sql/operator-accounts.sql

-- Role/status changes recheck the actor under a company lock. Two managers
-- cannot concurrently disable/demote each other and leave no manager behind.
CREATE OR REPLACE FUNCTION xs_operator_role(u jsonb) RETURNS text LANGUAGE sql IMMUTABLE AS $$
 SELECT CASE
 WHEN u->>'adminRoleKey' IN ('super-admin','ops-manager','ops-staff','ops-auditor') THEN u->>'adminRoleKey'
 WHEN (CASE WHEN jsonb_typeof(u->'roles')='array' THEN u->'roles' ELSE '[]'::jsonb END) ? 'super-admin' THEN 'super-admin'
 WHEN (CASE WHEN jsonb_typeof(u->'roles')='array' THEN u->'roles' ELSE '[]'::jsonb END) ? 'ops-manager' THEN 'ops-manager'
 WHEN (CASE WHEN jsonb_typeof(u->'roles')='array' THEN u->'roles' ELSE '[]'::jsonb END) ? 'ops-staff' THEN 'ops-staff'
 WHEN (CASE WHEN jsonb_typeof(u->'roles')='array' THEN u->'roles' ELSE '[]'::jsonb END) ? 'ops-auditor' THEN 'ops-auditor'
 WHEN COALESCE(u->>'adminRoleKey','')='' AND NOT EXISTS(SELECT 1 FROM jsonb_array_elements_text((CASE WHEN jsonb_typeof(u->'roles')='array' THEN u->'roles' ELSE '[]'::jsonb END)) r WHERE r LIKE 'ops-%')
 AND (u->>'isAdmin'='true' OR u->>'role'='admin' OR (CASE WHEN jsonb_typeof(u->'roles')='array' THEN u->'roles' ELSE '[]'::jsonb END) ? 'admin') THEN 'ops-manager'
 ELSE '' END
$$;
CREATE OR REPLACE FUNCTION xs_operator_change(co text, actor_id text, target_id text, operation text, payload jsonb, reason text, expected text, audit_id text)
RETURNS jsonb LANGUAGE plpgsql AS $$
DECLARE actor "_User"%ROWTYPE; target "_User"%ROWTYPE; actor_role text; target_role text; next_role text; before_value jsonb; after_value jsonb; revoked integer := 0;
BEGIN
 PERFORM set_config('xiaoshu.operator_write','on',true);
 IF co IS NULL OR co='' OR length(trim(reason)) NOT BETWEEN 2 AND 500 THEN RAISE EXCEPTION '帐套或操作原因无效'; END IF;
 PERFORM pg_advisory_xact_lock(hashtextextended('operator-account:'||co,0));
 SELECT * INTO actor FROM "_User" WHERE "objectId"=actor_id FOR UPDATE;
 actor_role := xs_operator_role(to_jsonb(actor));
 IF actor."objectId" IS NULL OR COALESCE(actor."isDisabled",false) OR COALESCE(actor."isDeleted",false) OR actor_role NOT IN ('ops-manager','super-admin') OR actor."company" IS DISTINCT FROM co THEN RAISE EXCEPTION '当前账号无权管理运营账号'; END IF;
 SELECT * INTO target FROM "_User" WHERE "objectId"=target_id AND "company"=co FOR UPDATE;
 target_role := xs_operator_role(to_jsonb(target));
 IF target."objectId" IS NULL OR COALESCE(target."isDeleted",false) OR (target_role='' AND COALESCE(target."identityType",'')<>'operator' AND NOT COALESCE(target."isAdmin",false) AND COALESCE(target."role",'')<>'admin' AND NOT COALESCE(target."roles",'[]'::jsonb) ? 'admin') THEN RAISE EXCEPTION '运营账号不存在'; END IF;
 IF target_role='super-admin' AND actor_role<>'super-admin' THEN RAISE EXCEPTION '不能修改超级管理员'; END IF;
 IF NULLIF(expected,'') IS NOT NULL AND date_trunc('milliseconds',target."updatedAt") IS DISTINCT FROM expected::timestamptz THEN RAISE EXCEPTION '账号已被其他管理员修改，请刷新'; END IF;
 before_value := jsonb_build_object('displayName',target."realName",'roleKey',target_role,'disabled',COALESCE(target."isDisabled",false));
 IF operation IN ('save','create') THEN
  next_role := payload->>'roleKey';
  IF next_role IS NULL OR next_role NOT IN ('super-admin','ops-manager','ops-staff','ops-auditor') OR (next_role='super-admin' AND actor_role<>'super-admin') THEN RAISE EXCEPTION '不能分配该角色'; END IF;
  IF actor_id=target_id AND next_role<>target_role THEN RAISE EXCEPTION '不能修改自己的管理角色'; END IF;
  IF length(trim(COALESCE(payload->>'displayName',''))) NOT BETWEEN 1 AND 80 THEN RAISE EXCEPTION '请填写 1 至 80 字的姓名'; END IF;
  UPDATE "_User" SET "realName"=trim(payload->>'displayName'),"nickname"=trim(payload->>'displayName'),"roles"=jsonb_build_array('admin',next_role),"adminRoleKey"=next_role,"role"='admin',"isAdmin"=true,"identityType"='operator',"isDisabled"=CASE WHEN operation='create' THEN false ELSE "isDisabled" END,"updatedAt"=date_trunc('milliseconds',clock_timestamp()) WHERE "objectId"=target_id;
  IF next_role<>target_role THEN DELETE FROM "_Session" WHERE "user"=target_id;GET DIAGNOSTICS revoked=ROW_COUNT; END IF;
 ELSIF operation='status' THEN
  IF actor_id=target_id THEN RAISE EXCEPTION '不能停用当前登录账号'; END IF;
  IF jsonb_typeof(payload->'disabled') IS DISTINCT FROM 'boolean' THEN RAISE EXCEPTION '账号状态无效'; END IF;
  UPDATE "_User" SET "isDisabled"=(payload->>'disabled')::boolean,"updatedAt"=date_trunc('milliseconds',clock_timestamp()) WHERE "objectId"=target_id;
  IF (payload->>'disabled')::boolean THEN DELETE FROM "_Session" WHERE "user"=target_id;GET DIAGNOSTICS revoked=ROW_COUNT; END IF;
 ELSIF operation='revoke-sessions' THEN
  DELETE FROM "_Session" WHERE "user"=target_id;GET DIAGNOSTICS revoked=ROW_COUNT;
 ELSE RAISE EXCEPTION '不支持的账号操作'; END IF;
 SELECT jsonb_build_object('displayName',"realName",'roleKey',xs_operator_role(to_jsonb(u)),'disabled',COALESCE("isDisabled",false)) INTO after_value FROM "_User" u WHERE "objectId"=target_id;
 INSERT INTO "OperatorAccountAudit"("objectId","company","actorId","actorName","targetId","targetName","action","reason","before","after","createdAt","updatedAt") VALUES(audit_id,co,actor_id,actor."username",target_id,target."username",operation,reason,before_value,after_value,now(),now());
 PERFORM set_config('xiaoshu.operator_write','off',true);
 RETURN jsonb_build_object('objectId',target_id,'revokedSessions',revoked);
END $$;

-- Parse user endpoints allow owners to update custom fields even with a private
-- ACL. Protect authorization at storage level, including crafted sign-up data.
CREATE OR REPLACE FUNCTION xs_guard_operator_authority() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE old_json jsonb; new_json jsonb := to_jsonb(NEW); key text;
BEGIN
 IF current_setting('xiaoshu.operator_write',true)='on' THEN IF TG_OP='DELETE' THEN RETURN OLD; END IF; RETURN NEW; END IF;
 IF TG_OP='DELETE' THEN IF xs_operator_role(to_jsonb(OLD))<>'' THEN RAISE EXCEPTION '运营账号请通过账号管理停用，不能直接删除'; END IF; RETURN OLD; END IF;
 IF TG_OP='INSERT' THEN
  IF xs_operator_role(new_json)<>'' OR NEW."isAdmin"=true OR COALESCE(NEW."adminRoleKey",'')<>'' OR NEW."role"='admin' THEN RAISE EXCEPTION '管理员角色只能通过运营账号管理分配'; END IF;
  RETURN NEW;
 END IF;
 old_json := to_jsonb(OLD);
 FOREACH key IN ARRAY ARRAY['roles','role','adminRoleKey','isAdmin'] LOOP
  IF old_json->key IS DISTINCT FROM new_json->key THEN RAISE EXCEPTION '管理员权限字段只能通过运营账号管理修改'; END IF;
 END LOOP;
 IF xs_operator_role(old_json)<>'' OR OLD."identityType"='operator' THEN
  FOREACH key IN ARRAY ARRAY['company','identityType','isDisabled','isDeleted'] LOOP
   IF old_json->key IS DISTINCT FROM new_json->key THEN RAISE EXCEPTION '管理员归属与状态只能通过运营账号管理修改'; END IF;
  END LOOP;
 END IF;
 RETURN NEW;
END $$;
DROP TRIGGER IF EXISTS xs_operator_authority_guard ON "_User";
CREATE TRIGGER xs_operator_authority_guard BEFORE INSERT OR UPDATE OR DELETE ON "_User" FOR EACH ROW EXECUTE FUNCTION xs_guard_operator_authority();