Deploy a Local LLM, Part 4: Adding a Knowledge Base with sqlite-vec Hybrid Retrieval

This is the final part of the Local AI Model Deployment series. The previous three parts produced an inference service that is reachable from the internet and wired into your daily tools. This part gives it a knowledge base: answers grounded in your own documents, with citations.

Everything runs on the same machine with no external dependencies. Two things get particular attention because they are easy to get wrong: how to chunk documents, and why vector search alone fails on technical content.

Local LLM deployment series (4 parts): ① Model and engine → ② Exposing it → ③ Clients → ④ Knowledge base RAG. This is part 4.

Architecture

Ingest:   document → split by heading → embed → sqlite-vec + FTS5
Retrieve: query → vector recall + lexical recall → RRF fusion
          → top-k into a system message → model

Why the embedding model runs on CPU: the generation model already occupies most of the VRAM, and embedding is far lighter — a query embeds a single sentence, which you will never notice, and slower batch ingestion does not matter. Measured, bge-m3 FP16 on CPU uses 1.6 GB of RAM and zero VRAM.

Step 1: Start an embedding service

A single engine process cannot serve both generation and embeddings, so this is a second instance.

curl -L --retry 20 -C - \
  -o /opt/llm/models/bge-m3-FP16.gguf \
  "https://modelscope.cn/models/gpustack/bge-m3-GGUF/resolve/master/bge-m3-FP16.gguf"

Embeddings are more sensitive to quantization than generation, and since this runs on CPU with plenty of RAM, use FP16 rather than compressing.

[Unit]
Description=bge-m3 embedding service (knowledge base, CPU)
After=network-online.target

[Service]
Type=exec
User=youruser
Environment=LD_LIBRARY_PATH=/opt/llama.cpp
ExecStart=/opt/llama.cpp/llama-server \
  --model /opt/llm/models/bge-m3-FP16.gguf \
  --alias bge-m3 \
  --embeddings \
  --pooling cls \
  --host 127.0.0.1 --port 8001 \
  --ctx-size 8192 \
  --batch-size 8192 --ubatch-size 8192 \
  --n-gpu-layers 0 \
  --threads 8 \
  --no-webui \
  --api-key sk-your-key
Restart=on-failure

[Install]
WantedBy=multi-user.target

Three flags that matter:

• --n-gpu-layers 0 forces CPU so it never competes with the generation model for VRAM.
• --ubatch-size 8192 must be raised. The default is 512 tokens, and a document chunk easily exceeds that — you get a hard 500: input (573 tokens) is too large to process. bge-m3 accepts 8192, so match the context size.
• --pooling cls is the correct pooling mode for the bge family.

Verify:

curl -s 127.0.0.1:8001/v1/embeddings \
  -H "Authorization: Bearer $KEY" -H 'Content-Type: application/json' \
  -d '{"model":"bge-m3","input":["test text"]}' \
| python3 -c "
import json,sys
v = json.load(sys.stdin)['data'][0]['embedding']
v = v[0] if v and isinstance(v[0], list) else v
print(f'{len(v)} dimensions')"

Expect 1024 dimensions. Note that some versions wrap a single result in an extra list ([[...]]) — handle that in code.

Step 2: Create the store

pip install sqlite-vec

import sqlite3, sqlite_vec

SCHEMA = """
CREATE TABLE IF NOT EXISTS documents (
  id INTEGER PRIMARY KEY, name TEXT NOT NULL,
  bytes INTEGER NOT NULL DEFAULT 0,
  n_chunks INTEGER NOT NULL DEFAULT 0,
  added_at INTEGER NOT NULL
);

CREATE TABLE IF NOT EXISTS chunks (
  id INTEGER PRIMARY KEY,
  doc_id INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
  ordinal INTEGER NOT NULL, heading TEXT, text TEXT NOT NULL
);
CREATE INDEX IF NOT EXISTS idx_chunks_doc ON chunks(doc_id);

-- trigram is what makes CJK work; the default unicode61 turns a whole
-- sentence into a single token
CREATE VIRTUAL TABLE IF NOT EXISTS chunks_fts USING fts5(
  text, content='chunks', content_rowid='id', tokenize='trigram'
);
"""

def connect(path):
    con = sqlite3.connect(path, timeout=30)
    con.row_factory = sqlite3.Row
    con.execute("PRAGMA journal_mode=WAL")
    con.execute("PRAGMA foreign_keys=ON")
    con.enable_load_extension(True)
    sqlite_vec.load(con)
    con.enable_load_extension(False)
    return con

with connect("kb.db") as con:
    con.executescript(SCHEMA)
    con.execute(
        "CREATE VIRTUAL TABLE IF NOT EXISTS vec_chunks USING vec0("
        "chunk_id INTEGER PRIMARY KEY, embedding float[1024])")

tokenize='trigram' is a prerequisite for any CJK content. SQLite FTS5’s default unicode61 does not segment Chinese, Japanese, or Korean — an entire sentence becomes one token and lexical search stops working. Trigram uses a sliding three-character window and handles mixed scripts.

Step 3: Chunk the documents

The order of operations here determines retrieval quality.

The intuitive approach is “split recursively by character count, then tag each chunk with a heading”. That fails twice: small documents collapse into a single chunk, destroying granularity; and when a chunk spans several sections, content from the first section gets labelled with the last section’s heading — citations that point at the wrong place, and hard to notice.

The correct order is two-level: split at headings first (semantic boundaries), then subdivide oversized sections.

import re

CHUNK_TARGET = 1100      # target characters per chunk
CHUNK_OVERLAP = 150      # overlap so answers are not cut at a boundary
MAX_CHUNK = 2000

_HEADING_RE = re.compile(r"^(#{1,6})\s+(.+?)\s*$", re.M)
_FENCE_RE = re.compile(r"^```.*?^```", re.M | re.S)

_SEPARATORS = ["\n# ", "\n## ", "\n### ", "\n\n", "\n",
               ". ", "! ", "? ", ", ", " "]


def _split_sections(text):
    """Split into (breadcrumb heading, body) pairs at markdown headings."""
    # Exclude fenced code blocks — a shell `# comment` is textually
    # identical to a heading
    spans = [(m.start(), m.end()) for m in _FENCE_RE.finditer(text)]
    marks = [m for m in _HEADING_RE.finditer(text)
             if not any(a <= m.start() < b for a, b in spans)]
    if not marks:
        return [(None, text)]

    sections, trail = [], {}
    if marks[0].start() > 0:
        lead = text[:marks[0].start()].strip()
        if lead:
            sections.append((None, lead))

    for i, m in enumerate(marks):
        level, title = len(m.group(1)), m.group(2).strip()
        trail = {k: v for k, v in trail.items() if k < level}
        trail[level] = title

        end = marks[i + 1].start() if i + 1 < len(marks) else len(text)
        body = text[m.start():end].strip()
        content = body[m.end() - m.start():].strip()
        # A heading with no body (an H1 immediately followed by H2s) should
        # not become its own chunk — those tiny fragments act as noise and
        # can outrank the passage that actually answers the question
        if len(content) < 20:
            continue
        sections.append((" › ".join(trail[k] for k in sorted(trail)), body))
    return sections

Two details worth calling out:

• Fenced code blocks must be excluded. A # comment in a shell snippet is indistinguishable from a heading. Without this, a citation breadcrumb reads README.md › 3. Remember to remove this afterwards... — which was a line inside a “`bash block.
• Skip empty sections. A heading-only section produces a fragment of a dozen characters that behaves as pure noise. In testing such a fragment ranked first, pushing the correct passage out of the results.

Then subdivide within each section, with overlap:

def chunk_text(text):
    text = text.replace("\r\n", "\n").strip()
    chunks = []
    for heading, body in _split_sections(text):
        parts = (_split_recursive(body, CHUNK_TARGET, _SEPARATORS)
                 if len(body) > CHUNK_TARGET else [body])
        for i, part in enumerate(parts):
            piece = part.strip()
            if not piece:
                continue
            # Overlap within a section only. Overlapping across sections
            # would mix in unrelated content and pollute citations.
            if i > 0 and CHUNK_OVERLAP:
                tail = parts[i - 1][-CHUNK_OVERLAP:].strip()
                if tail:
                    piece = tail + "\n" + piece
            # Re-attach the heading to continuation chunks, or the second
            # chunk onward loses its context
            if heading and not piece.lstrip().startswith("#"):
                piece = f"{heading}\n{piece}"
            chunks.append({"ordinal": len(chunks),
                           "text": piece[:MAX_CHUNK], "heading": heading})
    return chunks

Step 4: Hybrid retrieval

This is the most important section. Pure vector search fails on technical documentation far more often than people expect.

The two methods have complementary blind spots:

• “How do I stop the model from reasoning?” is semantic — only vector search finds it, since the source text may never use those words.
• reasoning_budget, a specific .gguf filename, a model number — embeddings are notoriously insensitive to identifiers. In testing, vector search could not rank the correct passage; lexical search matched it exactly.

So run both and fuse with Reciprocal Rank Fusion:

import json

def search(con, query, embed_fn, top_k=5, pool=30):
    qvec = embed_fn([query])[0]

    vec_hits = [r["chunk_id"] for r in con.execute(
        "SELECT chunk_id FROM vec_chunks "
        "WHERE embedding MATCH ? AND k = ? ORDER BY distance",
        (json.dumps(qvec), pool))]

    fts_hits = []
    for expr in _fts_queries(query):
        try:
            fts_hits = [r["rowid"] for r in con.execute(
                "SELECT rowid FROM chunks_fts WHERE chunks_fts MATCH ? "
                "ORDER BY rank LIMIT ?", (expr, pool))]
        except sqlite3.OperationalError:
            continue          # FTS5 rejected this form; try the next
        if fts_hits:
            break

    # RRF: fuse by rank, not score, so no normalization is needed between
    # two incomparable scales
    K, score = 60, {}
    for rank, cid in enumerate(vec_hits):
        score[cid] = score.get(cid, 0.0) + 1.0 / (K + rank + 1)
    for rank, cid in enumerate(fts_hits):
        score[cid] = score.get(cid, 0.0) + 1.0 / (K + rank + 1)

    return sorted(score.items(), key=lambda kv: -kv[1])[:top_k]


def _fts_queries(query):
    """Progressively looser FTS5 expressions, tried until one hits.

    Under trigram, phrase matching is substring matching, so a
    natural-language question matches nothing — the entire lexical arm
    contributes zero. Splitting into terms and OR-ing them is what lets
    identifiers do their job.
    """
    q = query.replace('"', " ").strip()
    if not q:
        return []
    out = [f'"{q}"']                                  # 1. whole phrase
    terms = [t for t in re.split(r"[\s,.?!:;()\[\]]+", q)
             if len(t) >= 3]                          # trigram needs 3+ chars
    if terms:
        out.append(" OR ".join(f'"{t}"' for t in terms[:8]))
    return out

RRF’s advantage is that it needs no weight tuning: cosine distance and BM25 scores live on completely different scales, and a weighted sum requires constant fiddling. Ranks are inherently comparable. K=60 is the value from the original paper and rarely needs changing.

Step 5: Build the context

def build_context(hits, max_chars=6000):
    """Assemble retrieved chunks into model context plus a citation list.

    Deliberately capped around 6000 characters: filling a small model's
    32K window dilutes attention. Three to five precise passages beat a
    pile of loosely related ones.
    """
    blocks, cites, used = [], [], 0
    for i, h in enumerate(hits, 1):
        label = h["doc_name"] + (f" › {h['heading']}" if h.get("heading") else "")
        block = f"[{i}] Source: {label}\n{h['text']}"
        if used + len(block) > max_chars:
            break
        blocks.append(block); used += len(block)
        cites.append({"n": i, "doc_name": h["doc_name"],
                      "heading": h.get("heading")})
    return "\n\n---\n\n".join(blocks), cites


RAG_PROMPT = (
    "Below are passages retrieved from a knowledge base. Base your answer "
    "on them and cite the number of any passage you use, e.g. [1].\n"
    "If the passages do not contain the answer, say so plainly rather than "
    "inventing one.\n\n"
)

That last sentence matters — explicitly permitting “the sources do not say” measurably reduces confabulation.

Step 6: Wrap it as an OpenAI-compatible endpoint

Expose it at a path that behaves exactly like /v1, so any OpenAI client gains a knowledge base by changing its base URL:

@app.post("/rag/v1/chat/completions")
async def rag_chat(request: Request):
    body = await request.json()
    messages = body.get("messages") or []

    # Retrieve using the last user message
    last = next((m for m in reversed(messages) if m.get("role") == "user"), None)
    query = ""
    if last:
        c = last.get("content")
        query = c if isinstance(c, str) else " ".join(
            p.get("text", "") for p in (c or []) if p.get("type") == "text")

    cites = []
    if query.strip():
        try:
            hits = search(con, query, embed_fn, int(body.pop("rag_top_k", 5)))
            if hits:
                ctx, cites = build_context(hits)
                messages = [{"role": "system",
                             "content": RAG_PROMPT + ctx}] + messages
                body["messages"] = messages
        except Exception as exc:
            # A knowledge base failure must not break the conversation —
            # degrade to a plain answer and report the reason in a header
            return await forward(request, body,
                                 {"X-RAG-Error": str(exc)[:200], "X-RAG-Hits": "0"})

    # Retrieval work calls for following the sources, not improvising
    body.setdefault("chat_template_kwargs", {}).setdefault("enable_thinking", False)

    return await forward(request, body, {
        "X-RAG-Hits": str(len(cites)),
        "X-RAG-Sources": json.dumps([c["doc_name"] for c in cites])[:500],
    })

Three design decisions:

• Degrade, do not fail. If the knowledge base or embedding service is down, conversation should still work — just without grounding. The reason goes in a header so clients need no changes to see it.
• Disable thinking by default. Retrieval work is about following sources.
• Report hits in response headers. X-RAG-Hits and X-RAG-Sources let you see which documents were used without modifying any client — invaluable while tuning.

Verification

Ingest a few of your own documents, then ask something only those documents can answer:

curl -sS -D /tmp/h.txt https://your-domain/rag/v1/chat/completions \
  -H "Authorization: Bearer $KEY" -H 'Content-Type: application/json' \
  -d '{"model":"gemma4","max_tokens":2048,
       "messages":[{"role":"user","content":"What was that specific number in my notes?"}]}' \
| python3 -c "import json,sys; print(json.load(sys.stdin)['choices'][0]['message']['content'])"

grep -i "x-rag" /tmp/h.txt

A good result contains the exact figure from your document, carries a [1]-style citation, and the headers show the hit count and source filenames.

Testing retrieval alone (without generation) is more convenient for tuning — you can see whether each hit came from the vector arm or the lexical arm, which quickly separates a chunking problem from a retrieval-strategy problem.

Known limitations

• No PDF or Word parsing. This is deliberate: better to reject a format than to quietly ingest garbled text, which is extremely hard to diagnose later when retrieval quality degrades. Convert to text yourself.
• No reranker. Top-k goes straight into context. Adding a bge-reranker improves precision at the cost of another model in memory and an extra inference pass.
• Context capped at ~6000 characters. Filling a small model’s window dilutes attention; three to five precise passages outperform a pile of related ones.

Series recap

Across four parts you now have a complete local inference stack:

1. Model and engine — sizing by VRAM, running as a service
2. Exposure — tunnel and direct routes, certificates and routing
3. Client integration — translation extensions, editors, your own code
4. Knowledge base — this article

The failures encountered along the way are catalogued separately in a symptom-indexed troubleshooting article.

One closing lesson, more valuable than any specific configuration: any step that can “succeed” while doing nothing needs an independent factual check afterwards. A download command returning 0 does not mean the file is complete. A flag being present does not mean the engine honoured it. A service listening does not mean packets arrive. Getting into the habit of reading the values the software actually computed — rather than the ones you believe you set — will save you more time than any other practice here.