Codex Local
bf1233d5df
ci / verify (push) Canceled after 0s
Add customer and operator login, BLE provisioning, device fleet controls, call memory, memoir generation, and refreshed family and maintenance portals. Include API contracts, migrations, firmware sources, tests, assets, and deployment documentation.
271 lines
23 KiB
PL/PgSQL
271 lines
23 KiB
PL/PgSQL
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;
|