story/migrations/000076_elder_persona_sync.up.sql

-- Durable, coalescing persona synchronization. No personal facts in job payloads.
ALTER TABLE subject_voice_agent
 ADD COLUMN persona_base_role text NOT NULL DEFAULT '',
 ADD COLUMN persona_revision bigint NOT NULL DEFAULT 1 CHECK(persona_revision>0),
 ADD COLUMN persona_synced_revision bigint NOT NULL DEFAULT 0,
 ADD COLUMN persona_sync_state text NOT NULL DEFAULT 'PENDING' CHECK(persona_sync_state IN ('PENDING','SYNCED','DEFERRED','FAILED')),
 ADD COLUMN persona_sync_attempts integer NOT NULL DEFAULT 0,
 ADD COLUMN persona_next_retry_at timestamptz NOT NULL DEFAULT now(),
 ADD COLUMN persona_error_code text NOT NULL DEFAULT '',
 ADD COLUMN persona_synced_at timestamptz,
 ADD COLUMN persona_digest text NOT NULL DEFAULT '',
 ADD COLUMN persona_memory_allowed boolean NOT NULL DEFAULT false,
 ADD COLUMN persona_voice_allowed boolean NOT NULL DEFAULT false,
 ADD COLUMN persona_session_dirty boolean NOT NULL DEFAULT false;

-- Remove only the old compiler's trailing name sentence, retaining custom personas.
UPDATE subject_voice_agent SET persona_base_role=regexp_replace(COALESCE(config#>>'{S2SConfig,ProviderParams,dialog,system_role}',''),E'\n你正在陪伴的长者称呼是[^\n]*。$','');

CREATE FUNCTION queue_elder_persona_sync(sid uuid) RETURNS void LANGUAGE sql AS $$
 UPDATE subject_voice_agent SET persona_revision=persona_revision+1,persona_sync_state='PENDING',persona_sync_attempts=0,persona_next_retry_at=now(),persona_error_code='' WHERE subject_id=sid
$$;

-- Use the same elder lock as chat preparation and the synchronizer. Take it before
-- the change so a provider request cannot race a newly committed profile version.
CREATE FUNCTION elder_persona_profile_changed() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF ROW(OLD.preferred_name,OLD.language,OLD.profile_data->'background',OLD.profile_data->'interests',
        OLD.conversation_preferences->'conversation_goal',OLD.conversation_preferences->'pace',
        OLD.conversation_preferences->'preferred_topics',OLD.conversation_preferences->'avoided_topics',OLD.status)
 IS DISTINCT FROM
    ROW(NEW.preferred_name,NEW.language,NEW.profile_data->'background',NEW.profile_data->'interests',
        NEW.conversation_preferences->'conversation_goal',NEW.conversation_preferences->'pace',
        NEW.conversation_preferences->'preferred_topics',NEW.conversation_preferences->'avoided_topics',NEW.status) THEN
  PERFORM pg_advisory_xact_lock(hashtextextended(NEW.id::text,64));
  PERFORM queue_elder_persona_sync(NEW.id);
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER elder_persona_profile_changed BEFORE UPDATE ON subject_profile FOR EACH ROW EXECUTE FUNCTION elder_persona_profile_changed();

CREATE FUNCTION elder_persona_consent_changed() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 -- Do not take the elder lock here: privacy fencing has a separate lock order.
 IF NEW.consent_type IN ('personalized_memory','voice_chat') THEN PERFORM queue_elder_persona_sync(NEW.subject_id); END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER elder_persona_consent_changed AFTER INSERT OR UPDATE ON consent_record FOR EACH ROW EXECUTE FUNCTION elder_persona_consent_changed();

CREATE FUNCTION elder_persona_config_changed() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF ROW(OLD.config,OLD.agent_config,OLD.persona_base_role) IS DISTINCT FROM ROW(NEW.config,NEW.agent_config,NEW.persona_base_role) THEN
  IF OLD.config#>>'{S2SConfig,ProviderParams,dialog,system_role}' IS DISTINCT FROM NEW.config#>>'{S2SConfig,ProviderParams,dialog,system_role}' THEN
   NEW.persona_base_role=regexp_replace(COALESCE(NEW.config#>>'{S2SConfig,ProviderParams,dialog,system_role}',''),E'\n你正在陪伴的长者称呼是[^\n]*。$','');
  END IF;
  NEW.persona_revision=OLD.persona_revision+1;
  NEW.persona_sync_state='PENDING'; NEW.persona_sync_attempts=0; NEW.persona_next_retry_at=now(); NEW.persona_error_code='';
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER elder_persona_config_changed BEFORE UPDATE ON subject_voice_agent FOR EACH ROW EXECUTE FUNCTION elder_persona_config_changed();
CREATE INDEX elder_persona_retry_idx ON subject_voice_agent(persona_next_retry_at) WHERE status='READY';