# I Built a Private AI Assistant That Queries My Git History and Project Management Data — Using Only Local LLMs > Source: > Published: 2026-05-21 22:14:40+00:00 **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 ``` python 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: ``` python def discover(question): keywords = extract_keywords(question) # Search task_boards, task_items, commits, branches... # Return matching IDs, names, values ``` 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: ``` bash $ 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.*