Search your data

-- Doc example: search your data — FTS5/BM25 (level 0, pure SQL) + vectors with an
-- embedder-as-a-task + hybrid RRF fusion. The embedder here is a deterministic fake
-- (bag of hashed words) so the example runs offline; swap `embed` for a real model
-- (API call or local GGUF) without touching anything else — that's the point.
intent: "doc example: FTS5 + vector search with an embedder task, fused with RRF"
require db(":memory:")

db_open(":memory:")
sql_exec("CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, description TEXT)")
sql_exec("CREATE TABLE product_vecs (id INTEGER PRIMARY KEY, vec TEXT)")

-- Level 0: FTS5 external-content + triggers keep the index in sync — pure SQL, no models.
sql_exec("CREATE VIRTUAL TABLE products_fts USING fts5(name, description, content='products', content_rowid='id')")
sql_exec("CREATE TRIGGER products_ai AFTER INSERT ON products BEGIN INSERT INTO products_fts(rowid, name, description) VALUES (new.id, new.name, new.description); END")

-- The embedder is a TASK: (text) => vector. Deterministic fake here; a real one is an
-- API call or a local model. ⚠️ SQL triggers can NEVER call a task — keep the write
-- path in ONE task (insert_product) so the vector table can't silently drift.
task embed(t)
    let ws be where(split(fold(t), " "), (w) => length(w) > 0)
    let hs be apply(ws, (w) => (sha256(w)[0] * 256 + sha256(w)[1]) % 64)
    give array(apply(range(64), (i) => count_where(hs, (h) => h == i)))

task cosine(a, b)
    let na be sqrt(sum(a * a))
    let nb be sqrt(sum(b * b))
    give when na == 0 or nb == 0 then 0 otherwise sum(a * b) / (na * nb)

task insert_product(name, description)
    sql_exec("INSERT INTO products (name, description) VALUES (?, ?)", [name, description])
    let pid be sql("SELECT last_insert_rowid() AS id")[0].id
    sql_exec("INSERT INTO product_vecs (id, vec) VALUES (?, ?)", [pid, json_encode(to_list(embed(name + " " + description)))])
    give pid

insert_product("Notebook Aurora 14", "notebook economica y liviana ideal para estudiar")
insert_product("Cargador USB-C 65W", "cargador rapido compatible con notebook y celular")
insert_product("Auriculares Nimbus", "auriculares inalambricos con cancelacion de ruido")

-- Hybrid = RRF (reciprocal rank fusion): no weights to calibrate, just ranks.
task rank_of(xs, pid)
    let hit be index_of(xs, (x) => x.id == pid)
    give when hit == nothing then -1 otherwise hit

task rrf_score(lex, sem, pid)
    let l be rank_of(lex, pid)
    let s be rank_of(sem, pid)
    give (when l >= 0 then 1 / (60 + l) otherwise 0) + (when s >= 0 then 1 / (60 + s) otherwise 0)

task hybrid_search(q, top)
    let lex be []
    try
        set lex to sql("SELECT rowid AS id, bm25(products_fts) AS s FROM products_fts WHERE products_fts MATCH ? ORDER BY s LIMIT 20", [q])
    recover err
        set lex to []
    let qv be embed(q)
    let scored be apply(sql("SELECT id, vec FROM product_vecs"), (r) => {"id": r.id, "s": cosine(qv, array(json_decode(r.vec)))})
    let sem be slice(sort_by(where(scored, (x) => x.s > 0), (x) => 0 - x.s), 0, 20)
    let ids be unique(flatten([apply(lex, (x) => x.id), apply(sem, (x) => x.id)]))
    let ranked be slice(sort_by(ids, (i) => 0 - rrf_score(lex, sem, i)), 0, top)
    when length(ranked) == 0
        give []
    let idlist be join(apply(ranked, (i) => text(floor(i))), ",")
    let rows be sql("SELECT id, name FROM products WHERE id IN (" + idlist + ")")
    give sort_by(rows, (r) => index_of(ranked, r.id))

print("top: " + (hybrid_search("notebook para estudiar", 3)[0])["name"])

test "BM25 alone finds the lexical match"
    let hits be sql("SELECT rowid AS id FROM products_fts WHERE products_fts MATCH ? ORDER BY bm25(products_fts)", ["cargador"])
    assert_eq((hits[0])["id"], 2)

test "hybrid RRF ranks the right product first"
    let top be hybrid_search("notebook para estudiar", 3)
    assert_eq((top[0])["name"], "Notebook Aurora 14")
    let top2 be hybrid_search("cargador celular", 2)
    assert_eq((top2[0])["name"], "Cargador USB-C 65W")

test "an empty query has zero signal on both sides — empty result, not an error"
    -- embed("") is the zero vector (cosine 0 everywhere) and FTS5 has nothing to match.
    -- Note: a NONSENSE query can still return weak hits with THIS fake embedder (hash
    -- buckets collide) — a real embedding model is what makes low-similarity junk rank
    -- low; RRF already puts lexical matches first either way.
    assert_eq(hybrid_search("", 3), [])