convez.ai
AgenteAvançado
Purvalk28

Academic Advisor

Purvalk28/academic-advisor

Assistente inteligente para catálogos acadêmicos com SQL seguro e RAG

Ver no GitHub
InicialPluginScore · de 0 a 100

Um 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-advisor

1 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

Estrelas
0
Forks
0
Acompanhando
0
Saúde
28%
Último commit
há 5 meses
Versão
—
Issues abertas
0
Contribuidores
1

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.

Academic-advisor

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_log table 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, expand POST /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+C in the uvicorn terminal.
  • Frontend: Ctrl+C in the Streamlit terminal.
  • Database: docker compose stop (data preserved) or docker compose down -v (data deleted).

Example queries

QuestionTool usedLatency
Which COMP courses cover machine learning?sql_query~4.3s
List all thesis optionssql_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 pleasecannot_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_prereqs column. 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_log table. 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-v2 embeddings, 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 courses table; 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

Ver o original no GitHub

Bom para

Relacionados