DEV Community

Pouria Zandakbari
Pouria Zandakbari

Posted on

I Built a Private AI Assistant That Queries My Git History and Project Management Data — Using Only Local LLMs

#llm #rag #privacy #python

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
Enter fullscreen mode Exit fullscreen mode

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%'
Enter fullscreen mode Exit fullscreen mode

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:

  1. Full database schema — auto-introspected at startup
  2. Sample values — actual repo names, anonymized author identifiers, statuses from the database
  3. Few-shot SQL examples — teaches the model the query patterns
  4. 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}
..."""
Enter fullscreen mode Exit fullscreen mode

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)
    # Search task_boards, task_items, commits, branches...
    # Return matching IDs, names, values
Enter fullscreen mode Exit fullscreen mode

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
Enter fullscreen mode Exit fullscreen mode

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.
Enter fullscreen mode Exit fullscreen mode 
$ 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...
Enter fullscreen mode Exit fullscreen mode 
$ 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.
Enter fullscreen mode Exit fullscreen mode

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)
Enter fullscreen mode Exit fullscreen mode

No LangChain. No vector database. No embeddings. No cloud services.

Key Takeaways

  1. 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.
  2. Local LLMs are production-ready. Ollama + qwen2.5-coder:7b runs fast on a MacBook and generates correct SQL reliably.
  3. Auto-discovery beats prompt engineering. Instead of hoping the LLM guesses the right table values, search the database first and feed it exact matches.
  4. Privacy and simplicity can coexist. The whole system is a few hundred lines of Python, runs offline, and handles real questions.
  5. 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.

Top comments (0)