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.
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;
|