Satyam0077/parcelpilot-ai-operations-agent

ParcelPilot AI Operations Agent

An internal AI agent for ParcelPilot's support/operations team (CalQuity AI Engineer Assessment — Option B). Authorized staff can ask natural-language questions about accounts, orders, tickets, support policies, cancellation rules, service credits, customer agreements, and product known issues, and the agent reasons across documents and structured data — applying explicit source precedence — to give a grounded, cited answer. State-changing actions (escalations) always require explicit confirmation before they execute.

Project overview

ParcelPilot's support team manually cross-references policies, SOPs, customer agreements, and past tickets to answer requests. This project automates that cross-referencing with an AI agent that:

• —Retrieves the right document(s) via semantic search (RAG).
• —Looks up structured account/order/ticket data via SQL, not the LLM's memory.
• —Applies explicit source precedence: signed customer agreement > current support policy > current SOP/product docs > deprecated docs/historical tickets (never authoritative).
• —Enforces per-account authorization in the tool/data layer, not just the prompt.
• —Requires explicit user confirmation before creating an escalation.
• —Surfaces a proactive dashboard of SLA-risk tickets, known-issue matches, and repeated-issue clusters.

Architecture

flowchart TD
    U[Internal Support User]
    UI[Streamlit Chat UI]
    API[FastAPI Backend]
    AG[LangGraph AI Agent]
    LLM[Gemini 3.5 Flash]

    DT[Document Search Tool]
    ST[Structured Data Tool]
    AT[Action Tool]

    VDB[(FAISS Index)]
    DB[(SQLite)]
    DOCS[ParcelPilot PDFs]
    XLSX[Assessment Excel Workbook]

    U --> UI
    UI --> API
    API --> AG
    AG --> LLM

    AG --> DT
    AG --> ST
    AG --> AT

    DOCS --> DT
    DT --> VDB

    XLSX --> DB
    ST --> DB

    AT --> DB

See `docs/architecture.md` for the full architecture note (agent design, RAG pipeline, source precedence, confirmation flow, API design, error handling, trade-offs) and `docs/product.md` for the product note.

Features

• —LangGraph AI agent with tool-calling and multi-step reasoning.
• —Document retrieval (RAG) over policies, SOPs, product docs, and customer agreements, using local/free embeddings and a persistent FAISS index.
• —Structured-data tools over accounts/orders/tickets, backed by SQLite.
• —State-changing action tool (create_escalation) gated behind explicit user confirmation — the LLM cannot execute it directly.
• —Source reliability / precedence enforced in code, not just the prompt.
• —Access control enforced in the tool/data layer via a mocked role/account-scope user context.
• —Proactive issue detection dashboard (SLA risk, known-issue matches, repeated-issue clusters).

Tech stack

Project structure

parcelpilot-ai-agent/
├── app/
│   ├── main.py               # FastAPI entrypoint
│   ├── config.py             # env-driven settings
│   ├── api/routes.py          # /api/chat, /api/confirm, /api/proactive
│   ├── agent/                # LangGraph graph, state, prompts, tool wrappers
│   ├── tools/                # document_search, structured_data, actions
│   ├── retrieval/            # PDF loading, chunking, embeddings, FAISS
│   ├── database/             # SQLAlchemy models, session, queries
│   ├── security/             # mocked users + authorization enforcement
│   └── services/             # source_reliability, proactive_detection
├── ingestion/
│   ├── ingest_excel.py       # Excel -> SQLite
│   └── ingest_documents.py   # PDFs -> FAISS
├── frontend/streamlit_app.py # chat UI + proactive dashboard
├── data/                     # source PDFs, workbook, generated DB/index
├── tests/                    # pytest suite
├── docs/architecture.md
├── docs/product.md
├── .env.example
├── requirements.txt
└── Dockerfile

Setup (Windows + Python 3.12)

py -3.12 -m venv .venv
.venv\Scripts\activate
pip install -r requirements.txt

copy .env.example .env

Open .env and replace your_gemini_api_key_here with your real Gemini API key. Do not commit .env — it is already in .gitignore.

Setup (macOS/Linux)

python3.12 -m venv .venv
source .venv/bin/activate
pip install -r requirements.txt
cp .env.example .env
# then edit .env with your real GEMINI_API_KEY

Data ingestion

Run both ingestion scripts once before starting the app (and again any time data/ParcelPilot_Assessment_Data.xlsx or data/documents/*.pdf change):

python -m ingestion.ingest_excel
python -m ingestion.ingest_documents

ingest_excel.py reads the README/accounts/orders/tickets sheets, writes the dataset snapshot time to data/dataset_snapshot.txt, and builds data/parcelpilot.db. ingest_documents.py loads the 6 supplied PDFs, chunks them, embeds them locally, and rebuilds the FAISS index at data/faiss/.

Run backend

uvicorn app.main:app --reload

Backend runs at:

• —API: http://localhost:8000 or http://127.0.0.1:8000
• —Interactive API docs: http://localhost:8000/docs
• —Health check: http://localhost:8000/health

Run frontend

In a second terminal (with the venv activated):

streamlit run frontend\streamlit_app.py

Frontend runs at:

• —Streamlit UI: http://localhost:8501

Pick a mocked user from the sidebar (Rohit, Maya, or Priya), then start chatting or view the "📊 Proactive Operations" dashboard.

Run Gradio frontend (Optional / Hugging Face Compatibility)

In a second terminal (with the venv activated):

python gradio_app.py

The Gradio interface will run at:

• —Gradio UI: http://localhost:7860

This interface directly reuses the underlying application and agent graph logic without requiring the FastAPI backend to be started separately.

Why Both Streamlit and Gradio?

Streamlit is the original, full-featured interactive frontend used during core development, testing, and in the official submission walkthrough video. It connects to the FastAPI backend, demonstrating a production-like decoupled client-server architecture.

Gradio was added later as an additional deployment-oriented entry point to accommodate Hugging Face hosting constraints. Free-tier CPU options on Hugging Face Spaces returned HTTP 402 for Docker Spaces (which require a paid/pro subscription for this user account). To provide a lightweight, alternative public trial interface, gradio_app.py was created.

The Gradio interface:

• —Operates as a standalone entry point suitable for Hugging Face Spaces.
• —Directly reuses the existing, unmodified Python application logic, database, FAISS documents registry, search precedence services, and LangGraph agent structure.
• —Does not replace or alter the core Streamlit UI, FastAPI service, tools, agents, or tests.
• —Ensures the evaluated walkthrough experience (using Streamlit) matches exactly while providing deployment flexibility.

Testing

The project includes a comprehensive test suite with 42 deterministic tests plus 4 optional live Gemini integration tests.

Current test status: ✅ 42 passed, 4 deselected

Run deterministic tests (recommended)

python -m pytest -v -m "not requires_gemini"

This skips live Gemini API tests to avoid consuming free-tier quota during development. Tests use a fake LLM for agent behavior validation.

Run live Gemini integration tests (optional)

# First ensure FAISS index exists
python -m ingestion.ingest_documents

# Set environment variable to opt-in
$env:RUN_GEMINI_TESTS="1"
python -m pytest -v -m "requires_gemini"

Note: Live Gemini tests require a valid API key and consume quota. They are explicitly opt-in to prevent accidental quota exhaustion.

Run specific test modules

python -m pytest tests/test_database.py -v      # Database operations
python -m pytest tests/test_authorization.py -v # Authorization/scoping
python -m pytest tests/test_tools.py -v         # Tool functions
python -m pytest tests/test_agent_local.py -v   # Agent graph with fake LLM

Mocked demo users

Environment variables

See `.env.example` for the full list:

• —GEMINI_API_KEY — your Gemini API key (required; never commit a real key).
• —GEMINI_MODEL — defaults to gemini-3.5-flash.
• —DATABASE_URL — SQLite connection string.
• —FAISS_INDEX_DIRECTORY, EMBEDDING_MODEL_NAME — vector store config.
• —PARCELPILOT_API_URL — backend URL the Streamlit UI calls.

AI tool usage

See `docs/product.md` for the disclosure of which AI coding tools were used and how — edit that section to reflect your own usage before submitting.

How it works

Agent / Tool Workflow

1. 1.User asks a question via Streamlit chat interface
2. 2.Question routed to FastAPI backend (/api/chat endpoint)
3. 3.LangGraph agent invokes Gemini with user message and conversation history
4. 4.Gemini analyzes the request and determines if tools are needed:
5. 5.Document search for policies/agreements/SOPs
6. 6.Structured data lookup for accounts/orders/tickets
7. 7.Action execution for escalations (requires confirmation)
8. 8.Tools execute with authorization checks:
9. 9.Account-scoped access enforced at tool layer
10. 10.FAISS retrieval with metadata filtering
11. 11.SQL queries with user permission validation
12. 12.Retrieved information returned to agent with source attribution
13. 13.Agent generates grounded response citing sources
14. 14.Response sent to UI with:
15. 15.Agent's natural language answer
16. 16.Activity log (which tools were called)
17. 17.Source citations (documents with page numbers, database records)
18. 18.Pending confirmation if action required

RAG / Document Ingestion Pipeline

The document retrieval system uses local embeddings and FAISS for efficient similarity search:

1. 1.Source documents (PDFs in data/documents/):
2. 2.Support policies
3. 3.Standard Operating Procedures (SOPs)
4. 4.Product documentation
5. 5.Customer-specific agreements
6. 6.Ingestion process (python -m ingestion.ingest_documents):
7. 7.Load PDFs using PyMuPDF
8. 8.Extract text with page number tracking
9. 9.Chunk text (500 token chunks, 100 token overlap)
10. 10.Generate embeddings using all-MiniLM-L6-v2 (runs locally on CPU, no API required)
11. 11.Build FAISS index (IndexFlatIP) with metadata sidecar
12. 12.Store at data/faiss/index.faiss + data/faiss/metadata.json
13. 13.Retrieval process (at query time):
14. 14.User query embedded with same model
15. 15.FAISS similarity search (top-k by inner product)
16. 16.Metadata filtering (accountid, documenttype, status)
17. 17.Source precedence ranking applied
18. 18.Results returned with document name, page number, status

Embedding model: all-MiniLM-L6-v2 runs entirely locally — no paid embedding API required.

Policy Precedence & Source Reliability

The system enforces explicit source precedence rules at runtime (not just in prompts):

Authority Hierarchy:

1. 1.Customer-specific signed agreements (authority_level=1) — highest authority
2. 2.Current support policies (authority_level=2)
3. 3.Current SOPs and product docs (authority_level=3)
4. 4.Deprecated documents (status="deprecated") — filtered out by default, never authoritative

Precedence enforcement:

• —Implemented in app/services/source_reliability.py
• —filter_out_deprecated(): removes deprecated sources from results
• —highest_authority_result(): selects source with lowest authority_level number
• —detect_conflict(): warns when sources of different authority levels contradict
• —Applied in app/tools/document_search.py before returning results to agent

Example: If a customer agreement says "48-hour cancellation window" but the general policy says "24-hour", the agreement wins (authority_level 1 < 2).

Safety & Authorization

Account-scoped access control:

• —User context includes user_id, role, and account_ids (list of accessible accounts)
• —Defined in app/security/authorization.py
• —Every tool call validates access:
• —can_access_account(): checks if user can view account/order/ticket
• —Unauthorized access attempts return error, not data
• —Authorization enforced at tool/data layer, not just prompts

Confirmation gate for state-changing actions:

• —Tool: create_escalation — can only be called after user confirmation
• —LangGraph workflow includes explicit confirmation_gate node
• —Agent can call prepare_escalation (read-only, describes what would happen)
• —Backend stores pending_action in conversation state
• —UI displays confirmation prompt
• —User must explicitly click "✅ Confirm" or "❌ Cancel"
• —Only after confirmation does execute_escalation run via /api/confirm endpoint
• —No state-changing action executes from an informational request alone

Conversation ownership:

• —Each conversation tied to the user who created it (user_id)
• —/api/confirm checks conversation["user_id"] == request.user_id
• —Prevents user A from confirming user B's pending action (403 Forbidden)

Proactive Operations Dashboard

Accessible via Streamlit "📊 Proactive Operations" tab, surfaces issues automatically:

Metrics displayed:

• —🚨 P1-Risk Tickets: Open tickets flagged as security incidents or outages
• —⏰ SLA at Risk: Tickets approaching or breaching response time targets
• —🔁 Repeated Issue Clusters: Groups of tickets with similar subjects/patterns
• —🔍 Known Issue Matches: Tickets matching documented known issues

SLA risk detection:

• —Compares ticket created_at time vs. priority-based SLA target
• —Marks as AT RISK if within 20% of target
• —Marks as BREACHED if exceeded target
• —Displays ticket ID, account, subject, elapsed time, and status

Implementation: app/services/proactive_detection.py + /api/proactive endpoint

Demo Flow (Recommended)

1. Start the application

Terminal 1 (Backend):

.venv\Scripts\activate
uvicorn app.main:app --reload

Backend: http://localhost:8000

Terminal 2 (Frontend):

.venv\Scripts\activate
streamlit run frontend\streamlit_app.py

Frontend: http://localhost:8501

2. Chat demo

• —Select user: Rohit (Support Agent - Northstar, Axis Labs)
• —Try example prompt: "What is the cancellation fee for a BOOKED shipment after 30 minutes?"
• —Show grounded answer with sources
• —Expand "📚 Sources" to show policy document citation with page number
• —Expand "🔍 Agent activity" to show which tools were called

3. Authorization demo

• —Ask: "Show me orders for ACCT-002" (LumenWorks)
• —Should return 403 Unauthorized (Rohit can't access Maya's accounts)
• —Switch user to Maya (Support Agent - LumenWorks, Beacon Retail)
• —Ask same question → now succeeds with order data

4. Confirmation flow demo

• —Ask: "Create an escalation for ticket TKT-006"
• —Agent should call prepare_escalation (read-only)
• —UI shows confirmation prompt with escalation summary
• —Click "✅ Confirm" or "❌ Cancel"
• —Show result message

5. Proactive Operations

• —Click "📊 Proactive Operations" tab
• —Show SLA-risk tickets with BREACHED/AT RISK status
• —Show repeated issue clusters
• —Show known issue matches
• —Switch to Priya (Operations Manager - all accounts) for full view

6. Architecture walkthrough

• —Show docs/architecture.md and docs/product.md
• —Highlight LangGraph workflow, FAISS RAG pipeline, confirmation gate

7. Test results

python -m pytest -v -m "not requires_gemini"

Show 42 passed output

Assignment Alignment (Option B)

This implementation addresses the CalQuity AI Engineer Assessment Option B requirements:

Docker (Optional)

A Dockerfile is included for containerized deployment:

docker build -t parcelpilot-agent .
docker run -p 8000:8000 --env-file .env parcelpilot-agent

Note: Docker is optional for local development and demo. The application runs directly using Python virtual environment as documented above. The Dockerfile is provided for production deployment scenarios.

To run both backend and frontend in containers, you would need a docker-compose.yml (not included) or run Streamlit in a second container pointing to the backend via PARCELPILOT_API_URL.

Known Limitations & Notes

Gemini SDK deprecation warning:

• —Current environment uses langchain-google-genai 2.0.7
• —Depends on google-generativeai 0.8.6 (deprecated SDK)
• —Shows FutureWarning about migration to google.genai package
• —Impact: Non-blocking warning; functionality works correctly for demo
• —Future work: Migrate to new SDK when langchain-google-genai updates adapter

Gemini free-tier quota:

• —Free tier has request-per-minute limits
• —Live Gemini tests explicitly opt-in to avoid quota exhaustion during development
• —Recommend using pytest -m "not requires_gemini" for rapid iteration

Windows SQLite file locking:

• —Test cleanup uses engine.dispose() to release file locks
• —Required for deterministic test execution on Windows

Local embeddings:

• —all-MiniLM-L6-v2 runs on CPU (no GPU required)
• —Embedding generation during ingestion takes ~1-2 minutes for full document set
• —No paid embedding API dependency