setup.sql

CREATE TABLE public.chats (
    id bigint generated by DEFAULT AS identity,
    created_at timestamp WITH time zone NOT NULL DEFAULT NOW(),
    updated_at timestamp WITH time zone NOT NULL DEFAULT NOW(),
    user_email character varying NOT NULL,
    name character varying NOT NULL,
    messages jsonb NOT NULL,
    CONSTRAINT chats_pkey PRIMARY KEY (id)
) tablespace pg_default;

CREATE extension IF NOT EXISTS vector;

CREATE TABLE public.memories (
    id bigint generated by DEFAULT AS identity,
    created_at timestamp WITH time zone NOT NULL DEFAULT NOW(),
    updated_at timestamp WITH time zone NOT NULL DEFAULT NOW(),
    user_email character varying NOT NULL,
    memory_text text NOT NULL,
    memory_embedding vector(1536),
    CONSTRAINT memories_pkey PRIMARY KEY (id)
) tablespace pg_default;

CREATE INDEX ON memories USING ivfflat (memory_embedding vector_cosine_ops) WITH (lists = 100);

CREATE OR REPLACE FUNCTION recall_memories (
    query_embedding vector (1536),
    match_threshold float,
    match_count int
) RETURNS TABLE (
    id bigint, 
    user_email character varying,
    memory_text text, 
    similarity float
) language SQL stable AS $$
SELECT
    memories.id,
    memories.user_email,
    memories.memory_text,
    1 - (memories.memory_embedding <=> query_embedding) AS similarity
FROM
    memories
WHERE
    1 - (memories.memory_embedding <=> query_embedding) > match_threshold
ORDER BY
    similarity DESC
LIMIT
    match_count;
$$;