Data Analytics Agent

A production-ready AI agent that safely answers data questions by querying a Postgres database, returning structured results, and rendering them in an interactive web UI. Built with Mastra framework and TypeScript, following Better Agents best practices.

Features

• Natural Language Querying: Ask questions in plain English about your data
• Safe SQL Generation: Automatically generates and executes secure SQL queries
• Structured Results: Returns data in JSON, tables, and charts
• Interactive UI: Web-based interface for exploring results
• Multi-turn Conversations: Supports follow-up questions and query refinements
• Error Handling: Graceful handling of invalid queries and database errors
• Comprehensive Testing: End-to-end scenario tests and evaluations
• Instrumentation: Full LangWatch integration for monitoring and analytics

Prerequisites

• Node.js 18+
• pnpm package manager
• PostgreSQL database
• API keys for:
  • OpenAI (or other LLM provider)
  • LangWatch

Installation

1. Clone the repository:

git clone <repository-url>
cd data-analytics-agent

2. Install dependencies:

pnpm install

3. Copy environment variables:

cp .env.example .env

4. Fill in your API keys and database connection details in .env:

OPENAI_API_KEY=your_openai_api_key
LANGWATCH_API_KEY=your_langwatch_api_key
DATABASE_URL=postgresql://user:password@localhost:5432/database

Setup

1. Ensure your PostgreSQL database is running and accessible.

2. Install LangWatch CLI globally:

pnpm add -g @langwatch/cli

3. Sync prompts:

langwatch prompt sync

Usage

Running the Agent

Start the development server:

pnpm dev

The agent will be available at http://localhost:3000 (or the configured port).

Interacting with the Agent

1. Open your browser and navigate to the agent URL

2. Ask questions in natural language, such as:

   • "What are the total sales by month?"
   • "Show me customers with orders over $1000"
   • "What's the average order value by product category?"

3. The agent will:

   • Generate a safe SQL query
   • Execute it against your database
   • Return structured results
   • Display them in an interactive UI

API Usage

The agent exposes REST endpoints for programmatic access:

// Example API call
const response = await fetch('/api/query', {
  method: 'POST',
  headers: { 'Content-Type': 'application/json' },
  body: JSON.stringify({
    question: "What are the top 10 products by sales?"
  })
});

const result = await response.json();

Testing

Scenario Tests

Run end-to-end scenario tests:

pnpm test:scenarios

Unit Tests

Run unit tests with vitest:

pnpm test

Evaluations

Run evaluation notebooks:

# Requires Jupyter
jupyter notebook tests/evaluations/

Development

Project Structure

├── app/                 # Main application code
│   └── index.ts        # Agent entry point
├── prompts/            # Versioned prompt files (YAML)
├── tests/
│   ├── evaluations/    # Jupyter notebooks for evaluations
│   └── scenarios/      # End-to-end scenario tests
├── prompts.json        # Prompt registry
├── .env               # Environment variables
└── AGENTS.md          # Development guidelines

Adding New Features

1. Understand Requirements: Clarify what the agent should do
2. Design the Approach: Plan components needed
3. Implement with Prompts: Use LangWatch Prompt CLI for prompts
4. Write Tests: Create scenario tests for validation
5. Run Tests: Verify everything works

Prompt Management

All prompts are managed via LangWatch Prompt CLI:

# Create a new prompt
langwatch prompt create my_new_prompt

# Edit the YAML file in prompts/
# Then sync
langwatch prompt sync

Database Schema

The agent expects a standard e-commerce schema. Update the prompts and agent logic if your schema differs.

Example schema:

• customers table
• orders table
• order_items table
• products table

Configuration

Environment Variables

• OPENAI_API_KEY: Your OpenAI API key
• LANGWATCH_API_KEY: LangWatch API key for instrumentation
• DATABASE_URL: PostgreSQL connection string
• PORT: Server port (default: 3000)

Database Connection

Configure your Postgres connection in .env. The agent uses connection pooling for performance.

Monitoring

The agent is fully instrumented with LangWatch for:

• Query performance monitoring
• Error tracking
• Usage analytics
• Prompt optimization

Access the LangWatch dashboard at https://app.langwatch.ai/

Contributing

1. Follow the guidelines in AGENTS.md
2. Use LangWatch Prompt CLI for prompt management
3. Write scenario tests for new features
4. Run all tests before submitting PRs

License

This project is licensed under the MIT License - see the LICENSE file for details.

Resources

• AGENTS.md - Development guidelines
• LangWatch Documentation
• Mastra Framework
• Vercel AI SDK
• Scenario Testing