Academic Advisor
Purvalk28/academic-advisor
Assistente inteligente para catálogos acadêmicos com SQL seguro e RAG
Ver no GitHubUm agente de IA que permite explorar catálogos de cursos de graduação em linguagem natural, roteando perguntas entre consultas SQL estruturadas e busca RAG em documentos de políticas acadêmicas. Implementa padrões agentic avançados com guardrails de segurança multi-camadas e observabilidade completa de queries.
Instalação
npx convez-ai Purvalk28/academic-advisor1 comando no terminal: detecta o formato do repositório (marketplace ou plugin avulso) e instala via Claude Code. Requer Node 18+ e o Claude Code instalado.
Métricas
Casos de uso
- Busca inteligente em catálogos de cursos por disciplinas e pré-requisitos
- Consulta de políticas acadêmicas, prazos e requisitos de programas
- Análise de grade curricular com roteamento automático entre dados estruturados e não-estruturados
Veredito

Implementação robusta de agentic AI com escolha arquitetural clara entre Text-to-SQL e RAG, demonstrando segurança em execução de queries geradas e observabilidade operacional. Referência técnica valiosa para sistemas Q&A híbridos, embora restrito a contexto acadêmico específico.
README
A tradução para o português deste README ainda está na fila.
AcademicAdvisor
A natural-language assistant for exploring Carleton University's graduate course catalog and academic policies. Demonstrates agentic AI patterns over mixed structured (PostgreSQL) and unstructured (RAG) data sources, with multi-layer safety guardrails and built-in observability.
Try it: ask "Which COMP courses cover machine learning?" or "What is the maximum time to complete the program?" — the agent routes the first to a Text-to-SQL tool and the second to a RAG retrieval tool, then synthesizes a grounded natural-language answer.
Demo
A Streamlit frontend talks to the FastAPI agent over HTTP. The screenshot below shows a Text-to-SQL query routed correctly, executed safely, and synthesized into a natural-language answer.
What it does
- Text-to-SQL. Natural-language questions about specific courses (titles, prerequisites, credits, terms) are translated into validated PostgreSQL queries and executed against a real course catalog.
- RAG over policies. Questions about program requirements, grading, deadlines, or procedures are answered by retrieving the relevant section of the graduate calendar via cosine-similarity search over embedded document chunks.
- Agentic routing. A LangGraph state machine classifies each question with an LLM, dispatches it to the right tool, validates the result, and synthesizes a final answer with confidence flags.
- Multi-layer safety. Generated SQL passes through an allow-list guardrail (allowed: single SELECT against approved tables; blocked: any DML or DDL, stacked statements, unknown tables) before execution.
- Observability. Every tool invocation is logged to a
query_logtable with the question, generated SQL, confidence score, success flag, and latency — the foundation for replay-based evaluation. - Streamlit frontend. A lightweight chat UI sits in front of the FastAPI agent, rendering routing metadata and confidence flags alongside each answer.
Architecture
┌─────────────────────┐
user question →│ FastAPI /query │
└──────────┬──────────┘
▼
┌─────────────────────┐
│ Router (Claude) │ classifies: sql | rag | out-of-scope
└──────────┬──────────┘
▼
┌───────────────┴───────────────┐
▼ ▼
┌──────────────────┐ ┌──────────────────┐
│ Text-to-SQL │ │ RAG retrieval │
│ + sql_guard │ │ + confidence │
└────────┬─────────┘ └────────┬─────────┘
▼ ▼
┌──────────────────┐ ┌──────────────────┐
│ courses table │ │ doc_chunks + │
│ (PostgreSQL) │ │ pgvector HNSW │
└────────┬─────────┘ └────────┬─────────┘
└───────────────┬───────────────┘
▼
┌─────────────────────┐
│ Synthesizer │ natural-language answer
│ (Claude) │
└──────────┬──────────┘
▼
JSON response
PostgreSQL 16 with pgvector is the unified backend for both relational
course data and vector embeddings. A LangGraph agent orchestrates the
flow. FastAPI exposes the agent over HTTP. A Streamlit frontend talks to
the API for interactive use.
How to run locally
The project has three moving pieces: a PostgreSQL container, a FastAPI backend, and a Streamlit frontend. Bring them up in this order.
One-time setup
git clone https://github.com/Purvalk28/academic-advisor.git
cd academic-advisor
python -m venv .venv
source .venv/bin/activate
pip install -e ".[dev]"
cp .env.example .env # then add your ANTHROPIC_API_KEY
Start the database (once per machine reboot)
docker compose up -d
This boots PostgreSQL 16 with pgvector inside a container named
advisor-pg. Data persists in a Docker volume across restarts, so this
is a one-time step until you delete the volume.
Seed the data (once)
python -m src.db.seed # loads 30 graduate courses
python -m src.rag.ingest # chunks, embeds, and indexes policy docs
Start the backend
uvicorn src.api.main:app --reload --port 8000
Leave this terminal running. The agent is now reachable at
http://localhost:8000.
Start the frontend (in a second terminal)
source .venv/bin/activate
streamlit run app.py
Streamlit opens automatically at http://localhost:8501.
Talk to the agent
Three options, pick whichever:
- Streamlit UI —
http://localhost:8501, type a question, press send. - Swagger UI —
http://localhost:8000/docs, expandPOST /query, click "Try it out", edit the JSON body, click Execute. - curl —
curl -X POST http://localhost:8000/query \
-H "Content-Type: application/json" \
-d '{"question": "Which COMP courses cover machine learning?"}'
Stopping things
- Backend:
Ctrl+Cin the uvicorn terminal. - Frontend:
Ctrl+Cin the Streamlit terminal. - Database:
docker compose stop(data preserved) ordocker compose down -v(data deleted).
Example queries
| Question | Tool used | Latency |
|---|---|---|
| Which COMP courses cover machine learning? | sql_query | ~4.3s |
| List all thesis options | sql_query | ~4.5s |
| What is the average credit value of SERG courses? | sql_query | ~3.4s |
| How many credits do I need for the MEng? | doc_search | ~2.9s |
| What is the maximum time to complete the program? | doc_search | ~4.8s |
| Can I take a leave of absence? | doc_search | ~2.7s |
| What's the best restaurant in Ottawa? | cannot_answer | ~1.1s |
| DROP all the courses please | cannot_answer | ~1.0s |
See eval_report.md for the full 12-case evaluation.
Evaluation
Run the harness:
python -m tests.eval
The current evaluation covers five categories — basic SQL queries, SQL with aggregation, RAG policy lookups, out-of-scope refusal, and destructive intent. Current routing accuracy: 12/12 (100%) on the hand-curated test set, with median latency around 3 seconds.
The 100% routing accuracy is honest but limited. A more rigorous evaluation would include adversarial cases (questions that sound like one tool but legitimately need the other), ambiguous questions that could use both tools, and LLM-as-judge scoring of answer quality rather than substring matching.
A documented failure mode and fix
During hand-review of an earlier eval run, I found that the synthesizer hallucinated numeric breakdowns when paraphrasing — answering "6.0 credits" with a non-summing breakdown for a question whose ground truth was 5.0 credits. The substring matcher passed because the digit '5' appeared elsewhere in the answer.
The fix was a tighter synthesizer prompt: explicit rules against arithmetic and number paraphrasing, plus temperature=0 for the synthesis step. Re-running the eval confirmed the fix held without regressing other cases. The underlying lesson — that confident LLM output doesn't correlate with correctness, and substring matching is the weakest possible evaluation signal — is documented in the design discussion below.
Design decisions
Database
- pgvector over FAISS/Chroma. Single-database deployment, transactional consistency between structured and vector data, native HNSW indexing, simpler ops for a small lab.
- Generated
has_prereqscolumn. Removes a class of LLM hallucination by computing derived booleans at write time. The agent can filter on the boolean without interpreting nullable free-text. - HNSW over IVFFlat. No training step required, better recall on small-to-medium corpora.
- Separate
query_logtable. Foundation for replay-based evaluation — every agent run is observable post-hoc without re-running the LLM.
RAG
- 500-char chunks with 50-char overlap. Smaller chunks lose semantic context across boundaries; larger chunks embed less precisely.
all-MiniLM-L6-v2embeddings, normalized. 384-dim is small enough for fast cosine search; normalization means cosine similarity equals the dot product, matching pgvector's<=>operator semantics.- Confidence threshold of 0.5. Below this, retrieval is unreliable for the corpus size; flag the answer as uncertain rather than fabricate.
LLM and tools
- Haiku for tools, Sonnet reserved for orchestration. Haiku is fast, cheap, and excellent at structured output like SQL generation.
- Temperature 0 for both SQL generation and synthesis. Deterministic output is essential for structured tasks and numeric faithfulness. Creativity is the wrong tool for "generate exactly this query" or "quote this number verbatim."
- Allow-list over block-list for SQL validation. Block-lists miss creative attacks (comment-encoded DROP, union injection). Allow-lists say "exactly these patterns are permitted" — stricter but provably safe.
Known limitations
- Single-turn only. No conversation history between requests.
- Single-table SQL. Generated queries only target the
coursestable; no JOINs across multiple tables. - Retrieval vocabulary sensitivity. Questions phrased very differently from the source documents may surface lower-relevance chunks; the confidence threshold flags this but doesn't recover.
- Substring-based answer evaluation. The eval harness checks routing accuracy and substring presence, not semantic correctness. LLM-as-judge is on the roadmap.
Future work
- LangSmith or Langfuse tracing for fine-grained step-level observability
- LLM-as-judge evaluation for semantic answer correctness
- Multi-turn conversation support with state carryover
- Fine-tune SQL generation with few-shot examples drawn from
query_log - Deploy to a free-tier host (Render, Railway) for a public demo link
Repository layout
academic-advisor/
├── app.py # Streamlit frontend
├── src/
│ ├── agent/ # LangGraph router, synthesizer, tools
│ │ ├── tools/ # sql_tool, doc_tool
│ │ ├── graph.py # state machine compilation
│ │ ├── llm.py # Anthropic client wrapper
│ │ └── sql_guard.py # allow-list SQL validator
│ ├── api/ # FastAPI HTTP layer
│ ├── db/ # connection, schema, seeding
│ └── rag/ # chunker, embedder, ingest, retrieve
├── data/seed/ # course catalog CSV
├── docs/ # policy documents for RAG
├── tests/eval.py # evaluation harness
└── eval_report.md # latest evaluation results
Tech stack
Python 3.11 · PostgreSQL 16 + pgvector · LangChain · LangGraph · Anthropic Claude (Haiku 4.5 / Sonnet 4.6) · sentence-transformers · FastAPI · Streamlit · Docker · sqlparse
License
MIT
