How It Works

async def search_nodes(tenant, project, query, embedding=None, limit=20):
    """Returns graph_nodes ordered by RRF(vec_sim, bm25)."""
    rows = await pool.fetch("""
      WITH candidates AS (
        SELECT *,
               1 - (embedding <=> $emb::vector) AS vec_sim,
               ts_rank_cd(search_vector, websearch_to_tsquery('english', $q))
                 AS bm25
        FROM graph_nodes
        WHERE tenant_id=$t AND project_id=$p
          AND (
            (embedding IS NOT NULL AND 1 - (embedding <=> $emb::vector) > 0.2)
            OR (search_vector @@ websearch_to_tsquery('english', $q))
          )
      ),
      vec_ranked  AS (SELECT id, ROW_NUMBER() OVER (ORDER BY vec_sim DESC) r FROM candidates),
      bm25_ranked AS (SELECT id, ROW_NUMBER() OVER (ORDER BY bm25    DESC) r FROM candidates)
      SELECT c.*,
             COALESCE(1.0/(60 + v.r), 0) + COALESCE(1.0/(60 + b.r), 0) AS rrf
      FROM candidates c
      LEFT JOIN vec_ranked  v ON c.id = v.id
      LEFT JOIN bm25_ranked b ON c.id = b.id
      ORDER BY rrf DESC
      LIMIT $limit
    """, ...)

-- tsvector column for BM25
ALTER TABLE graph_nodes ADD COLUMN IF NOT EXISTS search_vector TSVECTOR;
CREATE INDEX IF NOT EXISTS idx_nodes_search ON graph_nodes USING GIN (search_vector);

-- Title weighted 'A', content weighted 'B'
CREATE OR REPLACE FUNCTION graph_nodes_search_vector_update() RETURNS trigger AS $$
BEGIN
    NEW.search_vector :=
        setweight(to_tsvector('english', coalesce(NEW.title, '')),   'A') ||
        setweight(to_tsvector('english', coalesce(NEW.content, '')), 'B');
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_graph_nodes_search_vector
    BEFORE INSERT OR UPDATE ON graph_nodes
    FOR EACH ROW EXECUTE FUNCTION graph_nodes_search_vector_update();