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