openbrain-mcp/migrations/V2__hybrid_search.sql

ALTER TABLE memories
    ADD COLUMN IF NOT EXISTS tsv tsvector;

CREATE OR REPLACE FUNCTION memories_tsv_trigger()
RETURNS trigger
LANGUAGE plpgsql
AS $$
BEGIN
    NEW.tsv :=
        setweight(to_tsvector('pg_catalog.english', COALESCE(NEW.content, '')), 'A') ||
        setweight(
            to_tsvector('pg_catalog.english', COALESCE(array_to_string(NEW.keywords, ' '), '')),
            'B'
        );
    RETURN NEW;
END;
$$;

UPDATE memories
SET tsv =
    setweight(to_tsvector('pg_catalog.english', COALESCE(content, '')), 'A') ||
    setweight(
        to_tsvector('pg_catalog.english', COALESCE(array_to_string(keywords, ' '), '')),
        'B'
    )
WHERE tsv IS NULL;

DROP TRIGGER IF EXISTS memories_tsv_update ON memories;

CREATE TRIGGER memories_tsv_update
BEFORE INSERT OR UPDATE OF content, keywords ON memories
FOR EACH ROW
EXECUTE FUNCTION memories_tsv_trigger();

CREATE INDEX IF NOT EXISTS idx_memories_tsv
    ON memories USING GIN (tsv);