Back to Resources
Hybrid Vector + BM25 Skill RetrievalLast Updated: Published November 19, 2026

How to Implement Semantic Search in a 500-Skill AI Agent Library

To implement semantic search in a 500-skill AI agent library, combine a local sqlite-vec vec0 virtual table with SQLite FTS5 BM25 lexical indexing inside one SQLite database, fuse candidate scores with Reciprocal Rank Fusion (RRF), pre-filter by agent runtime metadata, and expose the retriever over MCP or a sub-50ms CLI.

sqlite-vec + FTS5
Hybrid RRF Scoring
Sub-50ms MCP Tool

Step-by-step engineering guide to building local sqlite-vec and SQLite FTS5 hybrid semantic search for 500+ AI agent skills across Cursor, Codex CLI, Antigravity, and Claude Code.

A 500-skill AI agent library breaks both startup context budgets and exact-match grep because no coding agent can hold 500 skill preambles in memory without silently dropping instructions. Moving discovery out of startup preambles into an on-demand semantic and lexical index preserves reasoning tokens while keeping long-tail workflows findable across Cursor, OpenAI Codex CLI, Google Antigravity, and Claude Code.

According to the Agent Skills Specification, every skill's name and description metadata consumes roughly 100 tokens at session startup, while full SKILL.md bodies are capped under 500 lines and recommended under 5,000 tokens. Multiplied across 500 skills, startup metadata alone burns ~50,000 tokens before a user enters a prompt. In the Claude Code Skills Documentation, Anthropic explains what happens next: “The budget scales at 1% of the model’s context window. When the listing overflows, Claude Code drops descriptions starting with the skills you invoke least, so the skills you use most keep their full text.”

Non-Claude runtimes hit equally hard walls when libraries scale past a few dozen workflows. OpenAI Codex CLI stops appending nested AGENTS.md files once concatenated instructions hit the default 32 KiB project_doc_max_bytes limit, and Cursor rules configured with broad alwaysApply: true flags flood every turn with stale context. Meanwhile, pure keyword grep fails whenever you search for “database deadlock” instead of the exact filename pg-blocking-pids.mdc. For a breakdown of how unindexed rules eat reasoning budgets, see our diagnostic guide on token context overhead in agent skills.

5-Stage Architecture for Hybrid Semantic Skill Search

A resilient local skill retriever separates offline indexing from sub-50ms query execution inside a single embedded SQLite file. Running both lexical and vector indexes in one process removes external vector-database daemons and guarantees atomic updates whenever a skill changes on disk.

  1. 1. Normalize Multi-Agent Frontmatter and Compute SHA-256 Digests: Walk ~/.codex/skills, .cursor/rules, ~/.gemini/antigravity/skills, and ~/.claude/skills, extract YAML frontmatter and body text, and skip unchanged files using a SHA-256 content hash.
  2. 2. Build an External-Content SQLite FTS5 Lexical Index: Index skill names, CLI flags, file globs, and descriptions in an FTS5 virtual table using unicode61 tokenization so exact flags like --dry-run or PreToolUse never miss.
  3. 3. Store 384-Dimensional Float32 Embeddings in a sqlite-vec vec0 Virtual Table: Embed normalized skill titles and triggers into a single-file sqlite-vec vec0 table alongside metadata columns for agent_runtime and scope.
  4. 4. Merge Candidates With Reciprocal Rank Fusion (RRF) and Negative bm25() Handling: Combine top-K FTS5 BM25 ranks and top-K vec0 cosine distances using RRF (k = 60), sorting SQLite bm25() ascending because FTS5 negates relevance scores.
  5. 5. Expose the Retriever via an On-Demand MCP Tool or Sub-50ms CLI: Serve top-5 skill matches through a single search_skills MCP tool or terminal command so Cursor, OpenAI Codex CLI, Google Antigravity, and Claude Code load zero unused preambles.

How Should You Design the SQLite FTS5 and sqlite-vec Schema?

Design the schema around three linked tables inside one SQLite database: a canonical skills_meta table keyed by integer rowid, an external-content skills_fts virtual table for BM25 keyword search, and a vec_skills vec0 virtual table for dense embeddings. Keeping skills_meta as the source of truth prevents duplicated markdown bodies from bloating vector pages.

As documented in the sqlite-vec vec0 Virtual Table Documentation, vec0 supports three non-vector column types: metadata columns usable in KNN WHERE clauses, partition keys for sharding indexes with hundreds of vectors per key, and auxiliary + columns stored separately to avoid extra JOINs. Because a 500-skill catalog spans only a handful of runtimes (cursor, codex, antigravity, claude-code), declaring agent_runtime as a short metadata column lets SQLite filter by agent without oversharding small partition buckets.

-- Enable sqlite-vec extension before running DDL
CREATE TABLE IF NOT EXISTS skills_meta (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  slug TEXT NOT NULL UNIQUE,
  agent_runtime TEXT NOT NULL,     -- e.g. 'cursor', 'codex', 'antigravity', 'claude-code'
  scope TEXT NOT NULL,             -- 'global' or 'workspace'
  file_path TEXT NOT NULL,
  title TEXT NOT NULL,
  description TEXT NOT NULL,
  tags TEXT NOT NULL,              -- space-separated YAML tags and CLI flags
  content_sha256 TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

-- External-content FTS5 table stays synced with skills_meta rowids
CREATE VIRTUAL TABLE IF NOT EXISTS skills_fts USING fts5(
  slug,
  title,
  description,
  tags,
  content='skills_meta',
  content_rowid='id',
  tokenize='unicode61 tokenchars "-_./"'
);

-- vec0 virtual table with 384-dim embeddings + metadata filter columns
CREATE VIRTUAL TABLE IF NOT EXISTS vec_skills USING vec0(
  skill_id INTEGER PRIMARY KEY,
  agent_runtime TEXT,              -- short string (<= 12 chars) for fast WHERE filtering
  scope TEXT,
  embedding float[384] distance_metric=cosine,
  +slug TEXT                       -- auxiliary column returned without a second JOIN
);

Notice the tokenchars "-_./" parameter on the unicode61 FTS5 tokenizer. Without adding hyphens, underscores, dots, and slashes to tokenchars, FTS5 splits --dry-run, PreToolUse, and .cursor/rules into generic fragments that match unrelated documents across a 500-skill catalog.

Why Do Pure Vector Embeddings Fail on CLI Flags, File Paths, and YAML Tags?

Pure vector embeddings blur exact syntactic tokens such as --no-verify, chmod +x, or SKILL.md because dense bi-encoders map technical identifiers into nearby semantic neighborhoods rather than exact string boundaries. Pairing sqlite-vec cosine distance with SQLite FTS5 BM25 through Reciprocal Rank Fusion (RRF) captures both conceptual intent and exact command flags in one SQL query.

When combining FTS5 and vec0 rankings in SQL, developers frequently invert their lexical order because of how FTS5 formats scores. According to the SQLite FTS5 Extension Documentation: “The built-in auxiliary function bm25() returns a real value indicating how well the current row matches the full-text query. The better the match, the numerically smaller the value returned.” Because FTS5 hard-codes k1 = 1.2, b = 0.75, and a -1 multiplier, sorting ORDER BY bm25(skills_fts) DESC silently returns the worst matches first; you must sort ASC before computing RRF ranks with smoothing constant k = 60.

WITH fts_candidates AS (
  SELECT
    rowid AS skill_id,
    ROW_NUMBER() OVER (ORDER BY bm25(skills_fts) ASC) AS fts_rank
  FROM skills_fts
  WHERE skills_fts MATCH :fts_query
  LIMIT 30
),
vec_candidates AS (
  SELECT
    skill_id,
    ROW_NUMBER() OVER (ORDER BY distance ASC) AS vec_rank
  FROM vec_skills
  WHERE embedding MATCH :query_blob
    AND k = 30
    AND (:runtime IS NULL OR agent_runtime = :runtime)
),
combined AS (
  SELECT skill_id, 1.0 / (60 + fts_rank) AS score FROM fts_candidates
  UNION ALL
  SELECT skill_id, 1.0 / (60 + vec_rank) AS score FROM vec_candidates
)
SELECT
  m.slug,
  m.agent_runtime,
  m.file_path,
  m.description,
  ROUND(SUM(c.score), 6) AS rrf_score
FROM combined c
JOIN skills_meta m ON m.id = c.skill_id
GROUP BY m.id
ORDER BY rrf_score DESC
LIMIT 5;

At 500 skills with 384-dimensional float32 vectors, the raw vector payload in vec_skills totals exactly 768,000 bytes (roughly 750 KiB). Because the entire index fits easily in the operating system page cache, SQLite evaluates both CTE branches and the RRF aggregation in 18 ms to 48 ms once the query vector is computed.

Incremental SHA-256 Indexing Pipeline Across Cursor, Codex, Antigravity, and Claude Code

An incremental indexer avoids re-embedding all 500 skills on every commit by computing a SHA-256 digest of each skill's frontmatter and body before calling the embedding model. Only newly added or edited files incur embedding latency, keeping re-index runs under a second during normal daily edits across multi-agent directories.

import hashlib, json, pathlib, sqlite3, struct
import sqlite_vec  # pip install sqlite-vec

AGENT_ROOTS = {
    "cursor": pathlib.Path(".cursor/rules"),
    "codex": pathlib.Path.home() / ".codex/skills",
    "antigravity": pathlib.Path.home() / ".gemini/antigravity/skills",
    "claude-code": pathlib.Path.home() / ".claude/skills",
}

def pack_f32(vector: list[float]) -> bytes:
    return struct.pack(f"{len(vector)}f", *vector)

def extract_frontmatter(raw: str) -> tuple[dict, str]:
    if not raw.startswith("---"):
        return {}, raw
    _, fm, body = raw.split("---", 2)
    meta = {}
    for line in fm.strip().splitlines():
        if ":" in line:
            k, v = line.split(":", 1)
            meta[k.strip()] = v.strip().strip('"').strip("'")
    return meta, body.strip()

def sync_library(db_path: str, embed_fn) -> dict[str, int]:
    conn = sqlite3.connect(db_path)
    conn.enable_load_extension(True)
    sqlite_vec.load(conn)
    stats = {"indexed": 0, "skipped": 0}

    for runtime, root in AGENT_ROOTS.items():
        if not root.exists():
            continue
        for file in root.rglob("*"):
            if file.name != "SKILL.md" and file.suffix != ".mdc":
                continue
            raw = file.read_text(encoding="utf-8", errors="ignore")
            digest = hashlib.sha256(raw.encode("utf-8")).hexdigest()
            slug = f"{runtime}:{file.parent.name if file.name == 'SKILL.md' else file.stem}"

            existing = conn.execute(
                "SELECT id, content_sha256 FROM skills_meta WHERE slug = ?", (slug,)
            ).fetchone()
            if existing and existing[1] == digest:
                stats["skipped"] += 1
                continue

            meta, body = extract_frontmatter(raw)
            title = meta.get("name", slug)
            desc = meta.get("description", body[:280].replace("\n", " "))
            tags = meta.get("globs", "") + " " + meta.get("tags", "")
            vec = pack_f32(embed_fn(f"{title}. {desc} {tags}"))

            with conn:
                if existing:
                    skill_id = existing[0]
                    conn.execute(
                        "DELETE FROM skills_fts WHERE rowid = ?", (skill_id,)
                    )
                    conn.execute(
                        "UPDATE skills_meta SET description=?, tags=?, content_sha256=?, updated_at=datetime('now') WHERE id=?",
                        (desc, tags.strip(), digest, skill_id),
                    )
                    conn.execute("DELETE FROM vec_skills WHERE skill_id = ?", (skill_id,))
                else:
                    cur = conn.execute(
                        "INSERT INTO skills_meta(slug, agent_runtime, scope, file_path, title, description, tags, content_sha256, updated_at) "
                        "VALUES (?, ?, 'global', ?, ?, ?, ?, ?, datetime('now'))",
                        (slug, runtime, str(file), title, desc, tags.strip(), digest),
                    )
                    skill_id = cur.lastrowid

                conn.execute(
                    "INSERT INTO skills_fts(rowid, slug, title, description, tags) VALUES (?, ?, ?, ?, ?)",
                    (skill_id, slug, title, desc, tags.strip()),
                )
                conn.execute(
                    "INSERT INTO vec_skills(skill_id, agent_runtime, scope, embedding, slug) VALUES (?, ?, 'global', ?, ?)",
                    (skill_id, runtime, vec, slug),
                )
            stats["indexed"] += 1
    return stats

Notice the explicit DELETE FROM skills_fts step before updating skills_meta on an edited skill. Because external-content FTS5 tables read column values during deletion, delete the old FTS5 entry before mutating the underlying row, or trigger an automatic FTS5 rebuild if the table ever drifts out of sync. Once indexed, expose the query through a single search_skills tool over Model Context Protocol—and if you run multiple IDEs concurrently against one SQLite index, review our guide comparing MCP stdio vs SSE and Streamable HTTP transports so multiple agent windows do not collide on subprocess locks.

Retrieval Latency and Accuracy Benchmarks: Startup Preambles vs Hybrid RRF vs Hotkey Palette

Choosing between startup injection, cold disk grep, pure vector KNN, hybrid FTS5 plus sqlite-vec, and an in-memory command palette comes down to whether the consumer is an autonomous agent loop or a developer typing at the keyboard. Agents tolerate 200 ms of query embedding if it saves 45,000 context tokens, whereas human keyboard lookups stall if feedback exceeds 50 ms.

Retrieval ArchitectureLookup LatencyCLI Flag & Tag RecallMemory / Token CostMulti-Agent SyncBest Fit
Startup Preamble Injection (All 500 Loaded)0 ms lookup (paid per turn)Degrades via overflow truncation~50,000 context tokens/turnBreaks at 32 KiB / 1% capsSmall libraries under 25 skills
Cold Disk Ripgrep (rg) Across Skill Folders1,420 ms – 2,850 msExact substring match onlyNegligible (< 20 MB)Manual path lists per OSAd-hoc one-off terminal debugging
Pure Vector KNN (sqlite-vec vec0 Only)150 ms – 400 ms (with embed)Weak on flags like --force or SQL350 MB – 800 MB embed modelLocal .db file; manual syncFuzzy natural-language intent only
Hybrid SQLite FTS5 + sqlite-vec RRF Pipeline18 ms – 48 ms (cached embed)High on both flags and concepts~750 KiB vectors + embedderSingle SQLite file + MCP tool500+ skill libraries served to agents
Prompttly In-Memory Hotkey Palette & Sync< 200 ms end-to-end GUIHigh via metadata + instant filterNative macOS app footprintBuilt-in cloud + 2-way agent syncCross-machine Mac + multi-agent teams

The Sprawl Moment: Hunting a Postgres Lock-Audit Skill Mid-Outage

During a Monday morning production database stall, an infrastructure engineer knows they wrote a specialized Postgres lock-tree audit skill four months ago, complete with exact pg_locks and pg_blocking_pids() diagnostics. They open Cursor on their work laptop and type @postgres, yielding 14 unrelated migration snippets; they switch to Claude Code, where the 1% context listing budget quietly truncated half their global skills; they run a terminal grep for “lock” and hit 90 stale repo rules across three clones. Unknown to them, the working skill is sitting under ~/.codex/skills/db-incident-triage/SKILL.md on a secondary machine they used over the weekend, forcing them to rewrite a degraded query from memory while customer requests time out.

For interactive human lookup benchmarks comparing keystroke fuzzy search against disk grep and heavy local embeddings, see our deep dive on finding the right prompt in a 200-prompt library in under five seconds.

When Should You Build a Custom sqlite-vec Index vs Using an AI Skill Manager?

Build a custom sqlite-vec and FTS5 pipeline when your autonomous agent runtimes need programmatic, zero-human semantic tool routing across 500+ skills via an MCP server. Choose a dedicated AI skill manager when your primary friction is human retrieval across apps, two-way filesystem synchronization across machines, and instant hotkey insertion without maintaining local embedding daemons.

Prompttly is a skill manager for AI agents — one library for your skills and prompts that syncs into Claude Code, Codex, ChatGPT, and Claude and is one hotkey away on your Mac, so your setup follows you across every machine, repo, and tool.

When NOT to Build a Custom sqlite-vec Search Pipeline

  • Your library has fewer than 50 skills: Below 50 skills, well-structured YAML descriptions and standard SKILL.md lazy loading cost under 5,000 startup tokens and fit cleanly inside Cursor, Codex, and Claude Code without vector infrastructure.
  • You retrieve prompts manually across terminals, IDEs, and web chats: A Python sqlite-vec CLI cannot inject a prompt into ChatGPT, Claude web, Ghostty, or Linear. Prompttly's native Mac hotkey palette opens your full library in under 200ms and inserts directly into any focused app while keeping ~/.claude/skills and ~/.codex/skills synced in the background.
  • Your main problem is multi-laptop drift rather than query recall: A local SQLite vector index only searches what exists on one disk. Before indexing, unify your canonical files across devices using a cross-agent skill library architecture or connect your agents to Prompttly over MCP with write access.

If you need to clean up messy frontmatter before indexing your 500-skill library, generate compliant YAML descriptions with the free Claude Skill Creator, tighten ambiguous instructions with the Prompt Optimizer, or explore the full guide archive on the Prompttly Resources hub.

Frequently Asked Questions: Semantic Search for AI Agent Skills

Why does a 500-skill AI agent library need semantic search instead of loading all skills at startup?

At the Agent Skills Specification baseline of roughly 100 startup tokens per skill, 500 skills consume around 50,000 tokens before the user types a prompt. Claude Code caps skill listings at 1% of the context window and drops least-used descriptions, while OpenAI Codex CLI stops reading nested AGENTS.md files after 32 KiB. Semantic search lets agents retrieve only the top 3 to 5 skills on demand.

Can sqlite-vec handle metadata filtering alongside vector KNN search?

Yes. Starting with sqlite-vec vec0 virtual tables, you can declare regular metadata columns such as agent_runtime or scope directly in the vec0 schema and filter them inside the WHERE clause of a KNN query. For high-selectivity fields with more than 100 vectors per group, sqlite-vec also supports partition key columns to shard the vector index.

Why does sorting SQLite FTS5 bm25() descending return the worst skill matches?

In SQLite FTS5, the built-in bm25() function multiplies relevance scores by -1 so that better matches produce numerically smaller (more negative) floating-point numbers. When writing hybrid Reciprocal Rank Fusion SQL, you must sort FTS5 candidates with ORDER BY bm25(skills_fts) ASC rather than DESC.

How fast is hybrid sqlite-vec and FTS5 retrieval for 500 agent skills?

With 500 skills encoded as 384-dimensional float32 vectors, the raw vector payload is only 768,000 bytes (roughly 750 KiB), so the entire SQLite database stays hot in the operating system page cache. Once the query string is embedded, combined FTS5 BM25, vec0 KNN, and RRF fusion execute in under 50 milliseconds on a modern laptop.

Skip the custom sqlite-vec pipeline across machines

Prompttly keeps your cross-agent skills synced into Claude Code and Codex and lets you search and insert any prompt in under 200ms with a native Mac hotkey.