story/migrations/000059_call_memory_contract.up.sql

BEGIN;

ALTER TABLE curated_memory ADD COLUMN memory_type text NOT NULL DEFAULT 'STORY'
 CHECK(memory_type IN ('RELATIONSHIP','PROFILE','EVENT','PREFERENCE','OPEN_ITEM','STORY','INTERACTION'));
ALTER TABLE curated_memory ADD COLUMN proactive_policy text NOT NULL DEFAULT 'ALLOWED'
 CHECK(proactive_policy IN ('ALLOWED','RESPOND_ONLY','NEVER'));
ALTER TABLE curated_memory ADD COLUMN entity_key text NOT NULL DEFAULT '';
ALTER TABLE curated_memory ADD COLUMN item_status text CHECK(item_status IN ('OPEN','COMPLETED','CANCELLED','UNKNOWN'));
ALTER TABLE curated_memory ADD COLUMN planned_at timestamptz;
ALTER TABLE curated_memory ADD COLUMN completed_at timestamptz;
ALTER TABLE curated_memory ADD COLUMN completion_basis jsonb;
ALTER TABLE curated_memory ADD COLUMN statement_basis text NOT NULL DEFAULT 'USER_CONFIRMED';
ALTER TABLE curated_memory ADD COLUMN source_time timestamptz;
ALTER TABLE curated_memory ADD CONSTRAINT open_item_fields CHECK (
 (memory_type='OPEN_ITEM' AND item_status IS NOT NULL AND (item_status<>'COMPLETED' OR (completed_at IS NOT NULL AND completion_basis IS NOT NULL)))
 OR (memory_type<>'OPEN_ITEM' AND item_status IS NULL));
ALTER TABLE memory_version ALTER COLUMN confirmed_by DROP NOT NULL;

CREATE TABLE memory_user_state (
 subject_id uuid PRIMARY KEY REFERENCES subject_profile(id), tenant_id uuid NOT NULL REFERENCES tenant(id),
 memory_revision bigint NOT NULL DEFAULT 0 CHECK(memory_revision>=0),
 privacy_epoch bigint NOT NULL DEFAULT 0 CHECK(privacy_epoch>=0),
 viking_visible_revision bigint NOT NULL DEFAULT 0 CHECK(viking_visible_revision>=0 AND viking_visible_revision<=memory_revision),
 legacy_projection_pending boolean NOT NULL DEFAULT false,
 updated_at timestamptz NOT NULL DEFAULT now());
INSERT INTO memory_user_state(subject_id,tenant_id,legacy_projection_pending) SELECT id,tenant_id,true FROM subject_profile;

CREATE TABLE call_provider_profile (
 id text PRIMARY KEY CHECK(length(id)<=64), protocol_version text NOT NULL CHECK(protocol_version='CALL-MEMORY-1.1'),
 sdk_version text NOT NULL, bot_id text NOT NULL, model_version text NOT NULL,
 transport text NOT NULL CHECK(transport='RTC'), audio_profile jsonb NOT NULL,
 vendor_template jsonb NOT NULL, field_mapping jsonb NOT NULL, capabilities jsonb NOT NULL DEFAULT '{}',
 evidence jsonb NOT NULL DEFAULT '{}', projection_schema jsonb NOT NULL DEFAULT '{}', published boolean NOT NULL DEFAULT false,
 created_at timestamptz NOT NULL DEFAULT now(), UNIQUE(id,sdk_version));
CREATE TABLE call_device_ready (
 device_id uuid PRIMARY KEY REFERENCES device(id), boot_id uuid NOT NULL, binding_generation bigint NOT NULL,
 provider_profile_id text NOT NULL REFERENCES call_provider_profile(id), firmware_version text NOT NULL,
 expires_at timestamptz NOT NULL);

CREATE TABLE call_session (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), device_id uuid NOT NULL REFERENCES device(id),
 tenant_id uuid NOT NULL REFERENCES tenant(id), subject_id uuid REFERENCES subject_profile(id),
 boot_id uuid NOT NULL, hook_event_id text NOT NULL CHECK(length(hook_event_id) BETWEEN 1 AND 128),
 call_generation bigint NOT NULL CHECK(call_generation>=0), binding_generation bigint NOT NULL CHECK(binding_generation>=0),
 prepare_digest text, purpose text NOT NULL DEFAULT 'NORMAL' CHECK(purpose IN ('NORMAL','FIRST_TEST')),
 manifest_id uuid, manifest_b64 text, manifest_mac text, manifest_status text NOT NULL DEFAULT 'NONE'
 CHECK(manifest_status IN ('NONE','ISSUED','REVOKED','DISCARDED')),
 provider_profile_id text REFERENCES call_provider_profile(id), media_attempt_id text,
 privacy_epoch bigint NOT NULL DEFAULT 0, memory_revision bigint NOT NULL DEFAULT 0,
 memory_mode text NOT NULL DEFAULT 'OFF' CHECK(memory_mode IN ('OFF','HOT_ONLY','NATIVE_VIKING')),
 budget_snapshot jsonb,
 expires_at timestamptz, lease_expires_at timestamptz,
 authorization_renewed_at timestamptz,
 call_state text NOT NULL DEFAULT 'PREPARING' CHECK(call_state IN ('PREPARING','STARTING','TALKING','STOPPING','ENDED','CANCELLED','FAILED')),
 remote_state text NOT NULL DEFAULT 'UNKNOWN' CHECK(remote_state IN ('NOT_STARTED','UNKNOWN','ACTIVE','STOPPED_OBSERVED')),
 transcript_state text NOT NULL DEFAULT 'COLLECTING' CHECK(transcript_state IN ('COLLECTING','DRAINING','COMPLETE','PARTIAL','EMPTY')),
 transcript_revision bigint NOT NULL DEFAULT 0, highest_contiguous_seq bigint NOT NULL DEFAULT 0,
 last_text_at timestamptz, drain_started_at timestamptz, text_gap boolean NOT NULL DEFAULT false,
 local_audio_off boolean NOT NULL DEFAULT false, sdk_stopped boolean NOT NULL DEFAULT false,
 retrieval_observation text NOT NULL DEFAULT 'NOT_OBSERVABLE',
 created_at timestamptz NOT NULL DEFAULT now(), ended_at timestamptz,
 UNIQUE(device_id,boot_id,hook_event_id));
CREATE INDEX call_session_subject_active ON call_session(subject_id) WHERE manifest_status='ISSUED';
ALTER TABLE device_activation_attempt ADD COLUMN call_session_id uuid REFERENCES call_session(id);
CREATE UNIQUE INDEX activation_call_unique ON device_activation_attempt(call_session_id) WHERE call_session_id IS NOT NULL;
CREATE UNIQUE INDEX call_device_unresolved_media ON call_session(device_id)
 WHERE manifest_status IN ('ISSUED','REVOKED') AND NOT (local_audio_off AND sdk_stopped AND remote_state IN ('NOT_STARTED','STOPPED_OBSERVED'));

CREATE TABLE call_request_result (
 actor text NOT NULL, request_id uuid NOT NULL, path text NOT NULL, body_digest text NOT NULL,
 response jsonb NOT NULL, expires_at timestamptz NOT NULL DEFAULT now()+interval '7 days',
 PRIMARY KEY(actor,request_id));
CREATE TABLE call_event (
 session_id uuid NOT NULL REFERENCES call_session(id), event_id text NOT NULL,
 seq bigint NOT NULL CHECK(seq>0), event_type text NOT NULL, digest text NOT NULL, payload jsonb NOT NULL,
 created_at timestamptz NOT NULL DEFAULT now(), PRIMARY KEY(session_id,event_id), UNIQUE(session_id,seq));
CREATE TABLE call_text_fragment (
 session_id uuid NOT NULL REFERENCES call_session(id), utterance_id text NOT NULL, source_revision bigint NOT NULL CHECK(source_revision>=0),
 part_index integer NOT NULL CHECK(part_index BETWEEN 0 AND 15), part_count integer NOT NULL CHECK(part_count BETWEEN 1 AND 16),
 speaker text NOT NULL CHECK(speaker IN ('USER','ASSISTANT','UNKNOWN')), text_content text NOT NULL,
 content_sha256 text NOT NULL, is_final boolean NOT NULL, interrupted boolean NOT NULL DEFAULT false,
 source_at timestamptz NOT NULL, PRIMARY KEY(session_id,utterance_id,source_revision,part_index),
 CHECK(part_index<part_count AND octet_length(text_content)<=4096));
CREATE TABLE call_utterance (
 session_id uuid NOT NULL REFERENCES call_session(id), utterance_id text NOT NULL, source_revision bigint NOT NULL,
 speaker text NOT NULL CHECK(speaker IN ('USER','ASSISTANT','UNKNOWN')), text_content text NOT NULL,
 interrupted boolean NOT NULL, playback_complete boolean NOT NULL DEFAULT false, source_at timestamptz NOT NULL,
 PRIMARY KEY(session_id,utterance_id));
CREATE TABLE call_command (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), session_id uuid NOT NULL REFERENCES call_session(id),
 privacy_epoch bigint NOT NULL, type text NOT NULL CHECK(type IN ('STOP_CALL','PLAY_LOCAL_NOTICE')),
 reason text NOT NULL, expires_at timestamptz NOT NULL, phase text, evidence jsonb NOT NULL DEFAULT '{}',
 created_at timestamptz NOT NULL DEFAULT now(), UNIQUE(session_id,privacy_epoch,type));

CREATE TABLE memory_mutation (
 id uuid PRIMARY KEY, tenant_id uuid NOT NULL REFERENCES tenant(id), subject_id uuid NOT NULL REFERENCES subject_profile(id),
 actor_id uuid REFERENCES user_account(id), action text NOT NULL CHECK(action IN ('ADD','CORRECT','SET_USAGE','DELETE')),
 source_session_id uuid REFERENCES call_session(id), utterance_id text, source_revision bigint, action_index integer CHECK(action_index BETWEEN 0 AND 15),
 request_digest text NOT NULL, revision bigint NOT NULL DEFAULT 1,
 state text NOT NULL CHECK(state IN ('RECEIVED','NEEDS_CONFIRMATION','APPLYING','PROPAGATING','COMPLETED','REJECTED','CANCELLED','EXPIRED','BLOCKED')),
 targets jsonb NOT NULL DEFAULT '[]', expected_versions jsonb NOT NULL DEFAULT '{}', patch jsonb NOT NULL DEFAULT '{}',
 source_refs jsonb NOT NULL DEFAULT '[]',
 scope text NOT NULL DEFAULT 'MEMORY_AND_DERIVED' CHECK(scope IN ('MEMORY_AND_DERIVED','SOURCE_AND_DERIVED')),
 proposal_digest text, confirmation_hash text, confirmation_expires_at timestamptz, confirmation_used_at timestamptz,
 memory_revision bigint, privacy_epoch bigint,
 stage_states jsonb NOT NULL DEFAULT '{"main_store":"PENDING","serving_projection":"PENDING","derived_context":"PENDING","active_context":"PENDING","source_cleanup":"PENDING","backup_cleanup":"PENDING"}',
 required_stages jsonb NOT NULL DEFAULT '["main_store","serving_projection","derived_context","active_context","source_cleanup","backup_cleanup"]',
 feedback_state text NOT NULL DEFAULT 'PAGE_ONLY', error_code text, created_at timestamptz NOT NULL DEFAULT now(), updated_at timestamptz NOT NULL DEFAULT now(),
 UNIQUE(source_session_id,utterance_id,source_revision,action_index));
CREATE INDEX memory_mutation_subject ON memory_mutation(subject_id,created_at DESC);
CREATE TABLE memory_access_grant (
 subject_id uuid NOT NULL REFERENCES subject_profile(id), user_id uuid NOT NULL REFERENCES user_account(id),
 can_read boolean NOT NULL DEFAULT false, can_write boolean NOT NULL DEFAULT false,
 sensitive_read boolean NOT NULL DEFAULT false, version bigint NOT NULL DEFAULT 1, PRIMARY KEY(subject_id,user_id), CHECK(NOT can_write OR can_read));
-- Existing household owners retain memory access; device-manage alone grants nothing.
INSERT INTO memory_access_grant(subject_id,user_id,can_read,can_write,sensitive_read)
 SELECT sp.id,hm.user_id,true,true,true FROM subject_profile sp JOIN household_member hm ON hm.household_id=sp.household_id
 WHERE hm.status='active' AND hm.role='owner';

CREATE TABLE memory_projection_job (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), tenant_id uuid NOT NULL, subject_id uuid NOT NULL REFERENCES subject_profile(id),
 memory_id uuid NOT NULL REFERENCES curated_memory(id), record_version bigint NOT NULL, memory_revision bigint NOT NULL,
 critical boolean NOT NULL, state text NOT NULL DEFAULT 'PENDING'
 CHECK(state IN ('PENDING','WRITING','WRITE_ACKED','VISIBLE','RECONCILING','BLOCKED')),
 provider_kind text, provider_id text, last_error text, attempts integer NOT NULL DEFAULT 0,
 next_attempt_at timestamptz NOT NULL DEFAULT now(), created_at timestamptz NOT NULL DEFAULT now(),
 UNIQUE(subject_id,memory_revision));
CREATE INDEX memory_projection_due ON memory_projection_job(next_attempt_at) WHERE state<>'VISIBLE';
CREATE TABLE memory_projection (
 memory_id uuid PRIMARY KEY REFERENCES curated_memory(id), subject_id uuid NOT NULL REFERENCES subject_profile(id),
 provider_kind text NOT NULL, provider_id text NOT NULL, record_version bigint NOT NULL, visible_version bigint NOT NULL DEFAULT 0,
 operation_key text NOT NULL UNIQUE, updated_at timestamptz NOT NULL DEFAULT now());
CREATE TABLE memory_source_suppression (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), subject_id uuid NOT NULL REFERENCES subject_profile(id),
 memory_id uuid NOT NULL REFERENCES curated_memory(id), source_refs jsonb NOT NULL, privacy_epoch bigint NOT NULL,
 scope text NOT NULL DEFAULT 'MEMORY_AND_DERIVED', created_at timestamptz NOT NULL DEFAULT now(), UNIQUE(memory_id,privacy_epoch));
CREATE TABLE memory_deletion_ledger (
 memory_id uuid NOT NULL REFERENCES curated_memory(id), privacy_epoch bigint NOT NULL, subject_id uuid NOT NULL REFERENCES subject_profile(id),
 scope text NOT NULL, created_at timestamptz NOT NULL DEFAULT now(), backup_cleanup_verified_at timestamptz,
 evidence_ref text, PRIMARY KEY(memory_id,privacy_epoch));
CREATE TABLE call_memory_operator_evidence (
 id uuid PRIMARY KEY DEFAULT gen_random_uuid(), subject_id uuid NOT NULL REFERENCES subject_profile(id),
 operation text NOT NULL, call_session_id uuid REFERENCES call_session(id), evidence_ref text NOT NULL, recorded_at timestamptz NOT NULL DEFAULT now());
CREATE FUNCTION call_legacy_cleanup_pending(sid uuid) RETURNS boolean LANGUAGE sql STABLE AS $$
 SELECT EXISTS(SELECT 1 FROM conversation c WHERE c.subject_id=sid AND c.runtime_type='volc_hardware_agent' AND c.stop_reason='memory_changed'
 AND NOT EXISTS(SELECT 1 FROM call_memory_operator_evidence e WHERE e.subject_id=sid AND e.operation='attest-legacy-cleanup' AND e.recorded_at>=c.updated_at))
$$;
CREATE FUNCTION call_memory_authorized(sid uuid) RETURNS boolean LANGUAGE sql STABLE AS $$
 SELECT EXISTS(SELECT 1 FROM subject_profile sp JOIN household h ON h.id=sp.household_id WHERE sp.id=sid AND sp.status='active' AND h.status='active')
 AND EXISTS(SELECT 1 FROM consent_record WHERE subject_id=sid AND consent_type='personalized_memory' AND withdrawn_at IS NULL AND (expires_at IS NULL OR expires_at>now()))
 AND NOT EXISTS(SELECT 1 FROM privacy_request WHERE subject_id=sid AND request_type='DELETE' AND status NOT IN ('FAILED','REJECTED','CANCELLED'))
$$;
ALTER TABLE privacy_subtask DROP CONSTRAINT privacy_subtask_subsystem_check;
ALTER TABLE privacy_subtask ADD CONSTRAINT privacy_subtask_subsystem_check CHECK(subsystem IN ('postgresql','redis','object_storage','pgvector','provider_index','export_files','call_memory'));
CREATE TABLE call_postprocess_job (
 session_id uuid NOT NULL REFERENCES call_session(id), transcript_revision bigint NOT NULL, extractor_version text NOT NULL,
 state text NOT NULL DEFAULT 'PENDING', last_error text, created_at timestamptz NOT NULL DEFAULT now(),
 PRIMARY KEY(session_id,transcript_revision,extractor_version));

-- A delayed legacy upload must not restore a source explicitly erased by scope.
CREATE FUNCTION retain_erased_story_source() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN
 IF NEW.final_text IS DISTINCT FROM OLD.final_text AND EXISTS(
  SELECT 1 FROM memory_source_suppression s CROSS JOIN LATERAL jsonb_array_elements(s.source_refs) r
  JOIN story_draft sd ON sd.public_id::text=r->>'story_id'
  JOIN story_revision sr ON sr.story_id=sd.id JOIN story_source ss ON ss.story_revision_id=sr.id
  WHERE s.subject_id=NEW.subject_id AND s.scope='SOURCE_AND_DERIVED' AND ss.conversation_turn_id=NEW.id
 ) THEN NEW.final_text='[deleted]'; NEW.provider_ref=NULL; END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER retain_erased_story_source BEFORE UPDATE ON conversation_turn FOR EACH ROW EXECUTE FUNCTION retain_erased_story_source();

-- Database boundary includes edits made by existing APIs and background workers.
CREATE FUNCTION call_context_purge(sid uuid) RETURNS void LANGUAGE plpgsql AS $$ BEGIN
 UPDATE call_request_result SET response='{"revoked":true}'::jsonb WHERE response->>'manifest_b64' IN(SELECT manifest_b64 FROM call_session WHERE subject_id=sid AND manifest_status<>'ISSUED');
 UPDATE call_session SET manifest_b64=NULL WHERE subject_id=sid AND manifest_status<>'ISSUED';
 UPDATE context_snapshot SET sanitized_summary=NULL,memory_versions='[]'::jsonb WHERE subject_id=sid;
 UPDATE idempotency_record SET response_status=409,response_body='{"error":{"code":"MEMORY_CONTEXT_REVOKED","message":"记忆上下文已撤销，请开始新的通话"}}'::jsonb,updated_at=now() WHERE state='completed' AND resource_id IN(
  SELECT id FROM conversation WHERE subject_id=sid UNION SELECT public_id FROM conversation WHERE subject_id=sid UNION SELECT id FROM curated_memory WHERE subject_id=sid UNION SELECT public_id FROM curated_memory WHERE subject_id=sid);
END $$;
CREATE FUNCTION memory_changed() RETURNS trigger LANGUAGE plpgsql AS $$
DECLARE rev bigint; epoch bigint; critical boolean; refs jsonb;
BEGIN
 IF NEW.current_version_id IS NULL THEN RETURN NEW; END IF;
 IF TG_OP='UPDATE' AND OLD.status='PENDING_DELETE' AND NEW.status='DELETED' AND OLD.current_version_id=NEW.current_version_id AND OLD.version=NEW.version THEN RETURN NEW; END IF;
 IF TG_OP='UPDATE' AND OLD.current_version_id=NEW.current_version_id AND OLD.status=NEW.status
 AND OLD.proactive_policy=NEW.proactive_policy AND OLD.item_status IS NOT DISTINCT FROM NEW.item_status
 AND OLD.planned_at IS NOT DISTINCT FROM NEW.planned_at AND OLD.sensitivity=NEW.sensitivity THEN RETURN NEW; END IF;
 critical := TG_OP='UPDATE' AND OLD.current_version_id IS NOT NULL;
 INSERT INTO memory_user_state(subject_id,tenant_id) VALUES(NEW.subject_id,NEW.tenant_id) ON CONFLICT DO NOTHING;
 UPDATE memory_user_state SET memory_revision=memory_revision+1,privacy_epoch=privacy_epoch+CASE WHEN critical THEN 1 ELSE 0 END,updated_at=now()
 WHERE subject_id=NEW.subject_id RETURNING memory_revision,privacy_epoch INTO rev,epoch;
 INSERT INTO memory_projection_job(tenant_id,subject_id,memory_id,record_version,memory_revision,critical)
 VALUES(NEW.tenant_id,NEW.subject_id,NEW.id,NEW.version,rev,critical);
 IF critical THEN
  UPDATE conversation_summary SET status='SUPERSEDED' WHERE subject_id=NEW.subject_id AND status='READY';
  UPDATE call_session SET manifest_status='REVOKED',call_state='STOPPING',lease_expires_at=LEAST(lease_expires_at,now())
  WHERE subject_id=NEW.subject_id AND manifest_status='ISSUED';
  INSERT INTO call_command(session_id,privacy_epoch,type,reason,expires_at)
  SELECT id,epoch,'STOP_CALL','MEMORY_CHANGED',now()+interval '7 days' FROM call_session
  WHERE subject_id=NEW.subject_id AND manifest_status='REVOKED' AND NOT(local_audio_off AND sdk_stopped AND remote_state IN ('NOT_STARTED','STOPPED_OBSERVED'))
  ON CONFLICT DO NOTHING;
  UPDATE conversation SET status='STOPPING',stop_reason='memory_changed',lease_expires_at=NULL,version=version+1,updated_at=now()
  WHERE subject_id=NEW.subject_id AND runtime_type='volc_hardware_agent' AND status IN ('ROOM_ISSUED','ACTIVE');
  UPDATE hardware_agent_session SET status='STOPPING',updated_at=now() WHERE conversation_id IN
   (SELECT id FROM conversation WHERE subject_id=NEW.subject_id AND stop_reason='memory_changed' AND status='STOPPING');
  PERFORM call_context_purge(NEW.subject_id);
  SELECT source_refs INTO refs FROM memory_version WHERE id=OLD.current_version_id;
  INSERT INTO memory_source_suppression(subject_id,memory_id,source_refs,privacy_epoch)
  VALUES(NEW.subject_id,NEW.id,COALESCE(refs,'[]'),epoch) ON CONFLICT DO NOTHING;
 END IF;
 IF NEW.status IN ('PENDING_DELETE','DELETED') THEN
  INSERT INTO memory_deletion_ledger(memory_id,privacy_epoch,subject_id,scope) VALUES(NEW.id,epoch,NEW.subject_id,'MEMORY_AND_DERIVED') ON CONFLICT DO NOTHING;
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER memory_changed AFTER INSERT OR UPDATE ON curated_memory FOR EACH ROW EXECUTE FUNCTION memory_changed();

-- Backfill projection work without rewriting or publishing historical transcripts.
WITH numbered AS (
 SELECT id,tenant_id,subject_id,version,row_number() OVER(PARTITION BY subject_id ORDER BY created_at,id) AS revision
 FROM curated_memory WHERE current_version_id IS NOT NULL)
INSERT INTO memory_projection_job(tenant_id,subject_id,memory_id,record_version,memory_revision,critical)
 SELECT tenant_id,subject_id,id,version,revision,true FROM numbered;
UPDATE memory_user_state us SET memory_revision=(SELECT COALESCE(max(memory_revision),0) FROM memory_projection_job j WHERE j.subject_id=us.subject_id);

-- Current authorization changes fence already-issued manifests in the same commit.
CREATE FUNCTION call_privacy_fence(sid uuid, why text) RETURNS void LANGUAGE plpgsql AS $$
DECLARE epoch bigint;
BEGIN
 INSERT INTO memory_user_state(subject_id,tenant_id) SELECT id,tenant_id FROM subject_profile WHERE id=sid ON CONFLICT DO NOTHING;
 UPDATE memory_user_state SET privacy_epoch=privacy_epoch+1,updated_at=now() WHERE subject_id=sid RETURNING privacy_epoch INTO epoch;
 UPDATE call_session SET manifest_status='REVOKED',call_state='STOPPING',lease_expires_at=LEAST(lease_expires_at,now()) WHERE subject_id=sid AND manifest_status='ISSUED';
 INSERT INTO call_command(session_id,privacy_epoch,type,reason,expires_at) SELECT id,epoch,'STOP_CALL',why,now()+interval '7 days' FROM call_session WHERE subject_id=sid AND manifest_status='REVOKED' AND NOT(local_audio_off AND sdk_stopped AND remote_state IN ('NOT_STARTED','STOPPED_OBSERVED')) ON CONFLICT DO NOTHING;
 UPDATE conversation_summary SET status='SUPERSEDED' WHERE subject_id=sid AND status='READY';
 UPDATE conversation SET status='STOPPING',stop_reason='memory_changed',lease_expires_at=NULL,version=version+1,updated_at=now() WHERE subject_id=sid AND runtime_type='volc_hardware_agent' AND status IN ('ROOM_ISSUED','ACTIVE');
 UPDATE hardware_agent_session SET status='STOPPING',updated_at=now() WHERE conversation_id IN(SELECT id FROM conversation WHERE subject_id=sid AND stop_reason='memory_changed' AND status='STOPPING');
 PERFORM call_context_purge(sid);
END $$;
CREATE FUNCTION call_authorization_changed() RETURNS trigger LANGUAGE plpgsql AS $$
BEGIN
 IF TG_TABLE_NAME='consent_record' THEN
  IF NEW.consent_type IN ('personalized_memory','transcript_storage') AND (NEW.withdrawn_at IS NOT NULL OR NEW.expires_at<=now()) AND (OLD.withdrawn_at IS DISTINCT FROM NEW.withdrawn_at OR OLD.expires_at IS DISTINCT FROM NEW.expires_at) THEN
   PERFORM call_privacy_fence(NEW.subject_id,'CONSENT_REVOKED');
   UPDATE curated_memory SET status='DISABLED',version=version+1,updated_at=now() WHERE subject_id=NEW.subject_id AND status='ACTIVE';
  END IF;
 ELSIF TG_TABLE_NAME='subject_profile' THEN
  IF OLD.status IS DISTINCT FROM NEW.status AND NEW.status<>'active' THEN PERFORM call_privacy_fence(NEW.id,'SUBJECT_UNAVAILABLE'); END IF;
 ELSIF TG_TABLE_NAME='device_binding' THEN
  IF OLD.unbound_at IS NULL AND NEW.unbound_at IS NOT NULL THEN PERFORM call_privacy_fence(OLD.subject_id,'BINDING_REVOKED'); END IF;
 ELSIF TG_TABLE_NAME='privacy_request' THEN
  IF NEW.request_type='DELETE' THEN
   PERFORM call_privacy_fence(NEW.subject_id,'PRIVACY_DELETE');
   IF EXISTS(SELECT 1 FROM call_session WHERE subject_id=NEW.subject_id) OR EXISTS(SELECT 1 FROM memory_projection_job WHERE subject_id=NEW.subject_id) THEN
    INSERT INTO privacy_subtask(privacy_request_id,subsystem,target_type,target_ref) VALUES(NEW.id,'call_memory','subject',NEW.subject_id::text) ON CONFLICT DO NOTHING;
   END IF;
  END IF;
 END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER call_consent_changed AFTER UPDATE ON consent_record FOR EACH ROW EXECUTE FUNCTION call_authorization_changed();
CREATE TRIGGER call_subject_changed AFTER UPDATE ON subject_profile FOR EACH ROW EXECUTE FUNCTION call_authorization_changed();
CREATE TRIGGER call_binding_changed AFTER UPDATE ON device_binding FOR EACH ROW EXECUTE FUNCTION call_authorization_changed();
CREATE TRIGGER call_privacy_requested AFTER INSERT ON privacy_request FOR EACH ROW EXECUTE FUNCTION call_authorization_changed();
-- Publishing freezes the precise evidence and mapping used to sign a manifest.
CREATE FUNCTION immutable_call_profile() RETURNS trigger LANGUAGE plpgsql AS $$ BEGIN
 IF OLD.published AND (to_jsonb(OLD)-'published') IS DISTINCT FROM (to_jsonb(NEW)-'published') THEN RAISE EXCEPTION 'published call profile is immutable; create a new profile id'; END IF;
 RETURN NEW;
END $$;
CREATE TRIGGER immutable_call_profile BEFORE UPDATE ON call_provider_profile FOR EACH ROW EXECUTE FUNCTION immutable_call_profile();
COMMIT;