How akshayrohilla-ai/ai-vm-analytics-assistant works
1. Overall shape
- This is a small, Python-based AI assistant with a single-user interactive front end and a server-side query pipeline that turns natural language into SQL and runs it against PostgreSQL.
- The repository is organized more like an application prototype than a multi-service system.
2. Primary entry point
- app.py is the main runtime entry point called out by the README.
- It likely hosts the Streamlit chat UI, handles user input, and displays the generated answer/results.
3. Query orchestration layer
- sql_assistant.py appears to contain the main assistant logic: prompt construction, LLM invocation, SQL generation, validation/guardrails, and retry handling.
- This is the central coordination layer between the UI, the model, and the database.
4. Database access layer
- sql_assistant_db.py is the persistence/infrastructure boundary.
- It likely encapsulates PostgreSQL connection details and query execution so the assistant logic does not talk to the database directly.
- The README indicates the data is primarily a single table (vm_data), which simplifies the schema boundary.
5. LLM / model dependency
- The assistant uses Ollama with the Phi3 local model for natural-language-to-SQL generation.
- This is an external runtime dependency, but it is local/on-machine rather than a cloud API, which affects deployment and latency.
6. End-to-end data flow
- The main flow is: user question in Streamlit → assistant builds prompt/context → LLM generates SQL → validation/guardrails check the SQL → PostgreSQL executes the query → results are returned to the UI.
- The architecture is therefore a classic chat-to-query execution pipeline with a feedback loop for fixing bad SQL.
7. Guardrails and retry boundary
- A key architectural feature is the SQL validation layer before execution.
- The README explicitly mentions protection against invalid or hallucinated queries plus an automatic retry mechanism, so the assistant likely has a “generate → validate → repair” loop rather than trusting the first model output.
8. Schema and domain assumptions
- The system is built around VM analytics: counts, CPU usage, memory, location-based filters, and similar operational questions.
- This means the prompt and validation logic are probably tuned to a constrained domain vocabulary and column mapping, not generic SQL over arbitrary schemas.
9. Alternative / experimental app path
- rag_app.py suggests an alternate implementation or experiment, likely a RAG-enhanced version of the assistant.
- In architecture terms, treat this as a parallel UI/flow variant rather than the core path unless you confirm it is used by app.py.
10. Support files and artifacts
- data.txt, output.txt, and test.py look like supporting artifacts for sample data, captured output, or local testing.
- main.py may be a secondary launcher or development entry point, but the README points to app.py as the canonical run target.
11. Operational boundaries
- The main external boundary is the PostgreSQL database; the main AI boundary is Ollama/Phi3.
- Everything else is local Python code, so the architecture is mostly a thin application layer around those two dependencies.
12. What to diagram
- For an accurate architecture diagram, draw:
- a Streamlit UI front end,
- a query assistant/orchestration component,
- an LLM service via Ollama,
- a SQL validation/retry step,
- a PostgreSQL database with vm_data,
- and a return path for formatted results back to the UI.
Main entry points: sql_assistant.py Assistant · sql_assistant_db.py DB layer · app.py app.py · main.py main.py
Components 16
UI
User-facing interactive entry points
Core
Assistant orchestration and execution logic
- Assistant · Coordinates prompt building, SQL generation, validation, and retries
sql_assistant.py - Prompting · Builds domain context and instructions for the model
- SQL gen · Turns natural language into candidate SQL
- Guardrails · Checks generated SQL and triggers repair or retry when needed
- Formatter · Formats query results for the UI
Data
Persistent data access and domain schema
External
Local model and database dependencies
- Ollama Phi3 · Local model used for natural-language-to-SQL generation
- PostgreSQL · External database engine that executes SQL
Alt flow
Experimental or secondary app path
- rag_app.py · Alternative or experimental assistant app path
rag_app.py
Connections 18
- app.py → Chat UI: hosts · Runs the interactive chat interface
- Chat UI → Assistant: asks · User questions are passed into the assistant pipeline
- Assistant → Prompting: builds · Creates schema-aware instructions and context
- Prompting → Ollama Phi3: prompts · Sends the constructed prompt to the local model
- Ollama Phi3 → SQL gen: returns SQL · Model output is treated as a candidate query
- SQL gen → Guardrails: checks · Candidate SQL is validated before execution
- Guardrails → Assistant: repair loop · Invalid SQL can be sent back for regeneration or retry
- Guardrails → DB layer: executes · Validated SQL is handed to the database access layer
- DB layer → PostgreSQL: connects · Uses PostgreSQL as the execution engine
- PostgreSQL → vm_data: queries · Primary table queried for VM analytics
- DB layer → Assistant: returns rows · Query results come back to the assistant orchestration
- Assistant → Formatter: formats · Shapes results into a user-friendly answer
- Formatter → Chat UI: renders · Formatted answer is displayed in the chat UI
- rag_app.py → Assistant: alternative flow · Secondary app path likely reuses or adapts the core assistant
- main.py → Assistant: launches · Possible secondary entry point into the same backend flow
- test.py → Assistant: exercises · Local script for testing assistant behavior
- Samples → Prompting: informs · Example data may support development or prompt tuning
- Output → Formatter: illustrates · Captured output may inform response formatting