82 lines
5.2 KiB
PL/PgSQL
82 lines
5.2 KiB
PL/PgSQL
|
BEGIN;
|
||
|
|
|
||
|
|
-- Keep device calls in their native tables; a story has exactly one origin.
|
||
|
|
ALTER TABLE call_session ADD CONSTRAINT call_session_story_scope UNIQUE(id,tenant_id,subject_id);
|
||
|
|
ALTER TABLE story_draft ALTER COLUMN conversation_id DROP NOT NULL;
|
||
|
|
ALTER TABLE story_draft ADD COLUMN call_session_id uuid;
|
||
|
|
ALTER TABLE story_draft ADD COLUMN call_privacy_epoch bigint;
|
||
|
|
ALTER TABLE story_draft ADD CONSTRAINT story_call_scope_fk
|
||
|
|
FOREIGN KEY(call_session_id,tenant_id,subject_id) REFERENCES call_session(id,tenant_id,subject_id);
|
||
|
|
ALTER TABLE story_draft ADD CONSTRAINT story_origin_check CHECK (
|
||
|
|
(conversation_id IS NOT NULL AND call_session_id IS NULL AND call_privacy_epoch IS NULL) OR
|
||
|
|
(conversation_id IS NULL AND call_session_id IS NOT NULL AND call_privacy_epoch IS NOT NULL AND call_privacy_epoch>=0));
|
||
|
|
ALTER TABLE story_draft ADD CONSTRAINT story_call_generation_key UNIQUE(call_session_id,generation_version);
|
||
|
|
|
||
|
|
ALTER TABLE story_source ALTER COLUMN conversation_turn_id DROP NOT NULL;
|
||
|
|
ALTER TABLE story_source ADD COLUMN call_session_id uuid;
|
||
|
|
ALTER TABLE story_source ADD COLUMN call_utterance_id text;
|
||
|
|
ALTER TABLE story_source ADD COLUMN call_source_revision bigint;
|
||
|
|
ALTER TABLE story_source ADD CONSTRAINT story_call_utterance_fk
|
||
|
|
FOREIGN KEY(call_session_id,call_utterance_id) REFERENCES call_utterance(session_id,utterance_id);
|
||
|
|
ALTER TABLE story_source ADD CONSTRAINT story_source_origin_check CHECK (
|
||
|
|
(conversation_turn_id IS NOT NULL AND call_session_id IS NULL AND call_utterance_id IS NULL AND call_source_revision IS NULL) OR
|
||
|
|
(conversation_turn_id IS NULL AND call_session_id IS NOT NULL AND call_utterance_id IS NOT NULL AND call_source_revision IS NOT NULL AND call_source_revision>=0 AND source_role='subject'));
|
||
|
|
ALTER TABLE story_source ADD CONSTRAINT story_call_source_key
|
||
|
|
UNIQUE(story_revision_id,call_session_id,call_utterance_id,source_start_offset,source_end_offset);
|
||
|
|
CREATE INDEX story_source_call_idx ON story_source(call_session_id,call_utterance_id) WHERE call_session_id IS NOT NULL;
|
||
|
|
|
||
|
|
CREATE TABLE call_story_extraction (
|
||
|
|
session_id uuid NOT NULL REFERENCES call_session(id),
|
||
|
|
transcript_revision bigint NOT NULL CHECK(transcript_revision>0),
|
||
|
|
privacy_epoch bigint NOT NULL CHECK(privacy_epoch>=0),
|
||
|
|
outcome text NOT NULL CHECK(outcome IN ('STORY','NO_STORY','EMPTY','REVIEWED')),
|
||
|
|
model_ref text NOT NULL DEFAULT '', prompt_version text NOT NULL DEFAULT '',
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
PRIMARY KEY(session_id,transcript_revision,privacy_epoch)
|
||
|
|
);
|
||
|
|
|
||
|
|
-- Authorization is frozen at call start and must still be valid at processing
|
||
|
|
-- and review time. A later grant must not authorize old calls retroactively.
|
||
|
|
CREATE FUNCTION call_story_authorized(sid uuid) RETURNS boolean LANGUAGE sql STABLE AS $$
|
||
|
|
SELECT EXISTS(SELECT 1 FROM call_session c JOIN subject_profile sp ON sp.id=c.subject_id AND sp.tenant_id=c.tenant_id
|
||
|
|
JOIN memory_user_state us ON us.subject_id=c.subject_id
|
||
|
|
WHERE c.id=sid AND sp.status='active' AND c.privacy_epoch=us.privacy_epoch
|
||
|
|
AND c.budget_snapshot->>'protocol'='DIRECT-CHAT-1'
|
||
|
|
AND c.budget_snapshot->>'story_extraction_allowed'='true'
|
||
|
|
AND NOT EXISTS(SELECT 1 FROM privacy_request pr WHERE pr.subject_id=c.subject_id AND pr.request_type='DELETE')
|
||
|
|
AND NOT EXISTS(SELECT 1 FROM (VALUES ('transcript_storage'),('story_extraction')) required(kind)
|
||
|
|
WHERE NOT EXISTS(SELECT 1 FROM consent_record cr WHERE cr.subject_id=c.subject_id AND cr.tenant_id=c.tenant_id
|
||
|
|
AND cr.consent_type=required.kind AND cr.granted_at<=c.created_at AND cr.withdrawn_at IS NULL
|
||
|
|
AND (cr.expires_at IS NULL OR cr.expires_at>now()))))
|
||
|
|
$$;
|
||
|
|
|
||
|
|
CREATE FUNCTION call_story_available(story uuid) RETURNS boolean LANGUAGE sql STABLE AS $$
|
||
|
|
SELECT EXISTS(SELECT 1 FROM story_draft sd WHERE sd.id=story AND (
|
||
|
|
sd.call_session_id IS NULL OR (
|
||
|
|
call_story_authorized(sd.call_session_id)
|
||
|
|
AND sd.call_privacy_epoch=(SELECT privacy_epoch FROM call_session WHERE id=sd.call_session_id)
|
||
|
|
AND EXISTS(SELECT 1 FROM story_source ss WHERE ss.story_revision_id=sd.current_revision_id)
|
||
|
|
AND NOT EXISTS(SELECT 1 FROM story_source ss LEFT JOIN call_utterance u
|
||
|
|
ON u.session_id=ss.call_session_id AND u.utterance_id=ss.call_utterance_id
|
||
|
|
WHERE ss.story_revision_id=sd.current_revision_id AND (
|
||
|
|
ss.call_session_id IS DISTINCT FROM sd.call_session_id OR u.session_id IS NULL
|
||
|
|
OR u.source_revision IS DISTINCT FROM ss.call_source_revision OR u.speaker<>'USER'
|
||
|
|
OR u.text_content='[deleted]' OR length(trim(u.text_content))=0
|
||
|
|
OR EXISTS(SELECT 1 FROM memory_source_suppression ms CROSS JOIN LATERAL jsonb_array_elements(ms.source_refs) r
|
||
|
|
WHERE ms.subject_id=sd.subject_id AND (r->>'story_id'=sd.public_id::text OR
|
||
|
|
(r->>'session_id'=ss.call_session_id::text AND (r->>'utterance_id' IS NULL OR r->>'utterance_id'=ss.call_utterance_id)))))))))
|
||
|
|
$$;
|
||
|
|
|
||
|
|
CREATE FUNCTION call_story_consent_changed() RETURNS trigger LANGUAGE plpgsql AS $$
|
||
|
|
BEGIN
|
||
|
|
IF NEW.consent_type='story_extraction' 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,'STORY_CONSENT_REVOKED');
|
||
|
|
END IF;
|
||
|
|
RETURN NEW;
|
||
|
|
END $$;
|
||
|
|
CREATE TRIGGER call_story_consent_changed AFTER UPDATE ON consent_record
|
||
|
|
FOR EACH ROW EXECUTE FUNCTION call_story_consent_changed();
|
||
|
|
|
||
|
|
COMMIT;
|