No API keys. No cloud. All data stays on my machine.
The Problem #
As a web developer, I constantly need to answer questions like:
- "Who committed the most to our main repo this month?"
- "What files were changed for the last campaign launch?"
- "What project tasks are still in progress for the web team?"
These answers exist — scattered across git log
, project management boards, and my own memory. I was tired of digging through terminal output and clicking through boards manually.
So I built a natural language interface that lets me ask these questions in plain English and get instant answers.
The Architecture: Text-to-SQL, Not Vector RAG #
Here's the key insight that shaped the entire project:
My data is structured, not unstructured. Commits have authors, dates, and repos. Project tasks have statuses, deadlines, and assignees. This isn't a pile of PDFs — it's relational data that fits naturally into a SQLite database.
Traditional RAG (vector embeddings + similarity search) is built for unstructured documents. For structured data, there's a better approach: Text-to-SQL.
User Question
↓
Local LLM (generates SQL)
↓
SQLite Database (executes query)
↓
Local LLM (summarizes results)
↓
Human-readable Answer
The LLM doesn't store or memorize my data. It just translates my question into SQL, runs it, and explains the results.
The Data Pipeline #
Step 1: Collect everything into SQLite
I wrote two Python collectors that populate a single SQLite database:
Git history collector (collect.py
):
- Runs
git log
across multiple repositories - Stores commits, file changes, branches, and tags
- Captures author, date, message, and insertions/deletions per file
Project management collector (collect_pm.py
):
- Queries the project management platform's GraphQL API (Monday.com in my case, but the pattern works for Jira, Linear, etc.)
- Stores boards, items, and subitems
- Extracts status, assignee, department, and deadline
- Flags web-team tasks automatically (
is_web = 1
)
The result: a single SQLite database holding everything needed to answer cross-cutting questions.
Step 2: Link git branches to project tasks
This was the crucial step. Git branches like feature/example-promo-banner
don't obviously connect to project items like "Example Promo Banner — Launch".
I created a branch_task_map
table that links them:
SELECT branch_name, task_name, board_name
FROM branch_task_map
WHERE branch_name LIKE '%promo-banner%'
This lets the system cross-reference: "What tasks relate to this branch?" or "What commits were made for this launch?"
The RAG System #
Why Ollama?
Privacy was non-negotiable. Project data, commit messages, and task details shouldn't leave the machine. Ollama runs the LLM entirely locally — no internet needed, no data sent anywhere.
I chose qwen2.5-coder:7b
as the model — it's excellent at SQL generation and runs fast on Apple Silicon.
The smart prompt
The system prompt is where the magic happens. It includes:
Full database schema— auto-introspected at startup - Sample values— actual repo names, anonymized author identifiers, statuses from the database - Few-shot SQL examples— teaches the model the query patterns - Today's date— so "this week" and "last month" work correctly
def build_system_prompt():
schema = get_schema() # Auto-introspect SQLite tables
samples = get_sample_values() # Real values from the DB
return f"""You are a data analyst assistant...
## Database Schema
{schema}
## Sample Values
{samples}
..."""
Auto-discovery: the secret sauce
Before the LLM even sees the question, the system extracts keywords and searches across all tables:
def discover(question):
keywords = extract_keywords(question)
This means when you ask "What's happening with the example promo banner launch?", the system has already found:
-
The matching project board
-
Related branches:
feature/example-promo-banner -
Recent commits referencing the same keywords
The LLM gets these exact values, so it writes precise SQL instead of guessing.
Self-correcting queries
If a SQL query returns 0 results, the system automatically retries with different keyword strategies:
Attempt 1: WHERE branch = 'feature/example-promo-banner' → 0 results
Attempt 2: WHERE message LIKE '%promo banner%' → 12 results
This handles the reality that commits are often on parent branches, not the feature branch itself.
The Result #
A CLI tool where I type questions and get answers:
$ python3 main.py "who committed the most this month?"
Developer A and Developer B lead this month
with roughly 350 commits each, followed by
Developer C with around 280 commits.
bash
$ python3 main.py "what web tasks are pending for the next launch?"
The upcoming launch has 8 web tasks remaining:
3 in progress, 2 ready for review, 3 not started...
bash
$ python3 main.py -v "what files changed for the example promo banner?"
-- SQL: SELECT DISTINCT fc.file_path, SUM(fc.insertions)...
-- WHERE c.message LIKE '%promo banner%'...
Several template and snippet files were modified,
concentrated in the promo banner section and a few
related shared components.
Project Structure #
The entire system is 8 files, ~400 lines of code, 2 dependencies:
custom-rag/
main.py # CLI entry point — REPL + one-shot mode
agent.py # LLM conversation loop (question → SQL → answer)
db.py # SQLite read-only, schema introspection, auto-discovery
prompts.py # System prompt with schema + few-shot examples
tools.py # Tool definitions
formatter.py # Rich terminal output
config.py # Paths and model settings
requirements.txt # rich, requests (that's it)
No LangChain. No vector database. No embeddings. No cloud services.
Key Takeaways #
Not all RAG needs vectors. If your data is structured, Text-to-SQL is simpler and more accurate than embedding everything into a vector store. -
Local LLMs are production-ready. Ollama +qwen2.5-coder:7b
runs fast on a MacBook and generates correct SQL reliably. - Auto-discovery beats prompt engineering. Instead of hoping the LLM guesses the right table values, search the database first and feed it exact matches. - Privacy and simplicity can coexist. The whole system is a few hundred lines of Python, runs offline, and handles real questions. - Cross-referencing is the real value. Any single data source is easy to query manually. The power comes from connecting git history with project management data in one natural language interface.
What's Next #
- Cron job to auto-refresh data every hour
- Adding chat message history as a third data source
- A simple web UI for non-terminal users
Built with Python, SQLite, Ollama, and qwen2.5-coder. All code runs locally — no data leaves the machine. All examples in this article use illustrative names and rounded figures; real commit authors, project codenames, and counts have been replaced or generalized.
If you're interested in the implementation details or want to build something similar, feel free to reach out or comment below.