SQLite RAG Agent

A local-first retrieval agent that turns folders of Markdown and text into a cited knowledge assistant—using SQLite, not a vector database.

SQLite RAG Agent is a compact reference implementation for grounded retrieval-augmented generation. It works offline for ingestion and search, stores everything in one portable database, supports English and Chinese lexical matching, and produces evidence blocks designed for citation-first answers.

What makes it useful

• One-file infrastructure: documents, chunks, and the FTS index live in SQLite.
• Grounded by default: answer prompts require [n] citations and explicit abstention.
• No framework lock-in: use the CLI, import the Python API, or connect any OpenAI-compatible endpoint.
• Inspectable retrieval: search scores, source paths, titles, and chunk ordinals are visible.
• Practical ingestion: Markdown, text, RST, source code, JSON, and YAML files are supported.
• Idempotent updates: unchanged files are skipped; changed sources are replaced atomically.

Quick start

git clone https://github.com/zchstime/sqlite-rag-agent.git
cd sqlite-rag-agent
python -m pip install -e .

pocket-rag --db demo.db ingest examples/docs
pocket-rag --db demo.db search "What is the refund policy?"

Search is entirely offline. To generate a grounded answer:

export OPENAI_API_KEY="your-key"
export OPENAI_MODEL="gpt-4.1-mini"
pocket-rag --db demo.db ask "When can a subscription be refunded?"

Use --dry-run to inspect the exact evidence and messages without calling a model.

Python API

from sqlite_rag_agent import KnowledgeBase

with KnowledgeBase("knowledge.db") as kb:
    kb.ingest_text(
        "Refund requests are accepted within 30 days.",
        source="support-policy.md",
        title="Support policy",
    )
    for result in kb.search("refund window"):
        print(result.score, result.source, result.text)

Architecture

files → normalization → overlapping chunks → SQLite + FTS5
                                                   ↓
question → lexical/FTS retrieval → ranked evidence → grounded LLM prompt
                                                   ↓
                                      cited answer or abstention

The retriever blends SQLite BM25 candidates with a deterministic lexical overlap score. Character bigrams provide a dependency-free fallback for Chinese text where whitespace tokenization is not enough.

OpenAI-compatible endpoints

Set OPENAI_BASE_URL to use a compatible gateway or local server. The project intentionally keeps provider code in one small module so it can be audited or replaced.

Test

PYTHONPATH=src python -m unittest discover -s tests -v

Honest scope

This is designed for small and medium knowledge bases, prototypes, internal tools, and reproducible RAG experiments. At very large scale, use a dedicated retrieval service. File parsing is text-only; PDF and Office extraction should happen upstream.

Roadmap

• Optional embedding reranker stored in SQLite
• Retrieval evaluation datasets and hit-rate reports
• Incremental directory watcher
• HTML and PDF extraction adapters

If you want RAG that is easy to understand before it becomes complicated, Star this project and share the retrieval edge case you want covered next.

License

MIT