story/migrations/000041_memory_tools_and_quality.up.sql

BEGIN;

CREATE TABLE conversation_memory_use (
    id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    public_id          uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    tenant_id          uuid NOT NULL REFERENCES tenant(id),
    subject_id         uuid NOT NULL,
    conversation_id    uuid NOT NULL REFERENCES conversation(id),
    memory_id          uuid NOT NULL REFERENCES curated_memory(id),
    memory_version_id  uuid NOT NULL REFERENCES memory_version(id),
    use_type           varchar(24) NOT NULL CHECK (use_type IN ('context_snapshot', 'in_call_tool', 'provider_native')),
    relevance          double precision,
    source_refs        jsonb NOT NULL DEFAULT '[]'::jsonb CHECK (jsonb_typeof(source_refs)='array'),
    used_at            timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT conversation_memory_use_subject_tenant_fk FOREIGN KEY (subject_id, tenant_id)
        REFERENCES subject_profile(id, tenant_id),
    CONSTRAINT conversation_memory_use_key UNIQUE (conversation_id, memory_version_id, use_type)
);
CREATE INDEX conversation_memory_use_trace_idx ON conversation_memory_use (tenant_id, subject_id, conversation_id, used_at DESC);

CREATE TABLE memory_tool_call (
    id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    public_id          uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    tenant_id          uuid NOT NULL REFERENCES tenant(id),
    subject_id         uuid NOT NULL,
    conversation_id    uuid NOT NULL REFERENCES conversation(id),
    query_digest       char(64) NOT NULL,
    result_count       integer NOT NULL DEFAULT 0 CHECK (result_count >= 0),
    status             varchar(24) NOT NULL CHECK (status IN ('SUCCEEDED', 'EMPTY', 'FAILED')),
    error_code         varchar(64),
    latency_ms         integer NOT NULL CHECK (latency_ms >= 0),
    engine_mode        varchar(24) NOT NULL,
    created_at         timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT memory_tool_call_subject_tenant_fk FOREIGN KEY (subject_id, tenant_id)
        REFERENCES subject_profile(id, tenant_id)
);
CREATE INDEX memory_tool_call_observe_idx ON memory_tool_call (tenant_id, status, created_at DESC);

CREATE TABLE fact_candidate_revision (
    id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    fact_candidate_id  uuid NOT NULL REFERENCES fact_candidate(id),
    version_no         bigint NOT NULL CHECK (version_no > 0),
    fact_type          varchar(80) NOT NULL,
    content            text NOT NULL,
    sensitivity        varchar(24) NOT NULL CHECK (sensitivity IN ('normal', 'sensitive', 'high')),
    status             varchar(24) NOT NULL CHECK (status IN ('CANDIDATE', 'CONFIRMED', 'REJECTED', 'SUPERSEDED')),
    edited_by          uuid REFERENCES user_account(id),
    edit_reason        varchar(500),
    error_type         varchar(32) CHECK (error_type IS NULL OR error_type IN (
        'PERSON_ERROR','TIME_ERROR','PLACE_ERROR','RELATION_ERROR','OVER_INFERENCE',
        'KEY_POINT_OMISSION','DUPLICATE_CONTENT','UNNATURAL_EXPRESSION','SENSITIVE_INFORMATION'
    )),
    created_at         timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT fact_candidate_revision_key UNIQUE (fact_candidate_id, version_no)
);
CREATE INDEX fact_candidate_revision_quality_idx ON fact_candidate_revision (error_type, created_at DESC) WHERE error_type IS NOT NULL;

ALTER TABLE story_revision ADD COLUMN error_type varchar(32) CHECK (error_type IS NULL OR error_type IN (
    'PERSON_ERROR','TIME_ERROR','PLACE_ERROR','RELATION_ERROR','OVER_INFERENCE',
    'KEY_POINT_OMISSION','DUPLICATE_CONTENT','UNNATURAL_EXPRESSION','SENSITIVE_INFORMATION'
));
ALTER TABLE memory_version ADD COLUMN error_type varchar(32) CHECK (error_type IS NULL OR error_type IN (
    'PERSON_ERROR','TIME_ERROR','PLACE_ERROR','RELATION_ERROR','OVER_INFERENCE',
    'KEY_POINT_OMISSION','DUPLICATE_CONTENT','UNNATURAL_EXPRESSION','SENSITIVE_INFORMATION'
));

CREATE TABLE content_review_signal (
    id                 uuid PRIMARY KEY DEFAULT gen_random_uuid(),
    public_id          uuid NOT NULL UNIQUE DEFAULT gen_random_uuid(),
    tenant_id          uuid NOT NULL REFERENCES tenant(id),
    subject_id         uuid NOT NULL,
    resource_type      varchar(24) NOT NULL CHECK (resource_type IN ('story','fact','memory')),
    resource_public_id uuid NOT NULL,
    action             varchar(24) NOT NULL CHECK (action IN ('EDITED','CONFIRMED','REJECTED','DISABLED','DELETED')),
    error_type         varchar(32) CHECK (error_type IS NULL OR error_type IN (
        'PERSON_ERROR','TIME_ERROR','PLACE_ERROR','RELATION_ERROR','OVER_INFERENCE',
        'KEY_POINT_OMISSION','DUPLICATE_CONTENT','UNNATURAL_EXPRESSION','SENSITIVE_INFORMATION'
    )),
    reason             varchar(500) NOT NULL,
    model_ref          varchar(200),
    prompt_version     varchar(80),
    generation_version bigint,
    actor_id           uuid NOT NULL REFERENCES user_account(id),
    created_at         timestamptz NOT NULL DEFAULT now(),
    CONSTRAINT content_review_signal_subject_tenant_fk FOREIGN KEY (subject_id, tenant_id)
        REFERENCES subject_profile(id, tenant_id)
);
CREATE INDEX content_review_signal_version_idx ON content_review_signal (tenant_id, model_ref, prompt_version, created_at DESC);
CREATE INDEX content_review_signal_error_idx ON content_review_signal (tenant_id, error_type, created_at DESC) WHERE error_type IS NOT NULL;

INSERT INTO fact_candidate_revision(fact_candidate_id,version_no,fact_type,content,sensitivity,status,edited_by,edit_reason,created_at)
SELECT id,version,fact_type,content,sensitivity,status,reviewed_by,review_reason,created_at FROM fact_candidate;

COMMIT;