26 lines
1.2 KiB
PL/PgSQL
26 lines
1.2 KiB
PL/PgSQL
|
-- Durable transcript upload and a local, editable mirror of provider memories.
|
||
|
|
CREATE FUNCTION managed_call_memory_authorized(sid uuid) RETURNS boolean LANGUAGE sql STABLE AS $$
|
||
|
|
SELECT call_memory_authorized(sid) AND EXISTS(SELECT 1 FROM consent_record WHERE subject_id=sid AND consent_type='transcript_storage' AND withdrawn_at IS NULL AND (expires_at IS NULL OR expires_at>now()))
|
||
|
|
$$;
|
||
|
|
CREATE TABLE call_memory_sync (
|
||
|
|
session_id uuid NOT NULL REFERENCES call_session(id),
|
||
|
|
transcript_revision bigint NOT NULL,
|
||
|
|
state text NOT NULL DEFAULT 'PENDING' CHECK(state IN ('PENDING','UPLOADED','SYNCED','SKIPPED')),
|
||
|
|
attempts integer NOT NULL DEFAULT 0,
|
||
|
|
next_attempt_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
uploaded_at timestamptz,
|
||
|
|
last_error text,
|
||
|
|
created_at timestamptz NOT NULL DEFAULT now(),
|
||
|
|
PRIMARY KEY(session_id,transcript_revision)
|
||
|
|
);
|
||
|
|
CREATE INDEX call_memory_sync_due ON call_memory_sync(next_attempt_at);
|
||
|
|
CREATE TABLE managed_memory_origin (
|
||
|
|
memory_id uuid PRIMARY KEY REFERENCES curated_memory(id),
|
||
|
|
subject_id uuid NOT NULL REFERENCES subject_profile(id),
|
||
|
|
session_id uuid NOT NULL REFERENCES call_session(id),
|
||
|
|
provider_id text NOT NULL,
|
||
|
|
provider_digest text NOT NULL,
|
||
|
|
imported_version bigint NOT NULL DEFAULT 1,
|
||
|
|
UNIQUE(subject_id,provider_id)
|
||
|
|
);
|