docs/projects/DataCrew.md

# Course Project: DataCrew

**A CLI tool that uses local LLMs and multi-agent systems to transform spreadsheets into intelligent PDF reports.**

---

## Overview

```
┌─────────────────────────────────────────────────────────────────┐
│                        DataCrew CLI                              │
├─────────────────────────────────────────────────────────────────┤
│  $ datacrew ingest sales_2024.xlsx                              │
│  $ datacrew ask "What were the top 5 products by revenue?"      │
│  $ datacrew report "Q4 Executive Summary" --output report.pdf   │
└─────────────────────────────────────────────────────────────────┘
```

---

## Architecture

```
CSV/XLSX ──► SQLite ──► MCP Server ──► Multi-Agent Crew ──► PDF Report
                              │
                              ▼
                    Docker Model Runner
                      (Local LLM)
```

### Data Flow

```
┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│   CSV/XLSX   │────►│    SQLite    │────►│  MCP Server  │
│    Files     │     │   Database   │     │   (Tools)    │
└──────────────┘     └──────────────┘     └──────┬───────┘
                                                  │
                                                  ▼
┌──────────────┐     ┌──────────────┐     ┌──────────────┐
│  PDF Report  │◄────│  Agent Crew  │◄────│  Local LLM   │
│   Output     │     │  (CrewAI)    │     │   (Docker)   │
└──────────────┘     └──────────────┘     └──────────────┘
```

---

## Agent System

| Agent | Role | Tools | Output |
|-------|------|-------|--------|
| **Data Analyst** | Understands schema, writes SQL queries | MCP Database Tools | Query results, data summaries |
| **Insights Agent** | Interprets results, finds patterns | Python Analysis, Statistics | Key findings, trends, anomalies |
| **Report Writer** | Creates narrative sections | LLM Generation | Executive summary, section text |
| **PDF Composer** | Formats and assembles final report | ReportLab/WeasyPrint | Formatted PDF document |

### Agent Workflow

```
User Request: "Generate Q4 Executive Summary"
                    │
                    ▼
         ┌─────────────────────┐
         │    Data Analyst     │
         │  "What data do we   │
         │   need for Q4?"     │
         └──────────┬──────────┘
                    │ SQL Queries
                    ▼
         ┌─────────────────────┐
         │   Insights Agent    │
         │  "What patterns     │
         │   emerge from       │
         │   this data?"       │
         └──────────┬──────────┘
                    │ Key Findings
                    ▼
         ┌─────────────────────┐
         │   Report Writer     │
         │  "Write narrative   │
         │   sections for      │
         │   each finding"     │
         └──────────┬──────────┘
                    │ Text Sections
                    ▼
         ┌─────────────────────┐
         │   PDF Composer      │
         │  "Assemble into     │
         │   formatted PDF"    │
         └──────────┬──────────┘
                    │
                    ▼
              report.pdf
```

---

## CLI Commands

### `datacrew ingest`

Ingest CSV or XLSX files into the local SQLite database.

```bash
# Ingest a single file
datacrew ingest sales_2024.xlsx

# Ingest with custom table name
datacrew ingest sales_2024.xlsx --table quarterly_sales

# Ingest multiple files
datacrew ingest data/*.csv

# Ingest with schema inference options
datacrew ingest sales.csv --infer-types --date-columns "order_date,ship_date"
```

**Options:**

| Flag | Description | Default |
|------|-------------|---------|
| `--table` | Custom table name | Filename (sanitized) |
| `--if-exists` | Behavior if table exists: `fail`, `replace`, `append` | `fail` |
| `--infer-types` | Automatically infer column types | `true` |
| `--date-columns` | Comma-separated list of date columns | Auto-detect |
| `--db` | Database file path | `./data/datacrew.db` |

### `datacrew ask`

Query the database using natural language.

```bash
# Simple query
datacrew ask "What were the top 5 products by revenue?"

# Query with output format
datacrew ask "Show monthly sales trends" --format table

# Query with export
datacrew ask "List all customers from California" --export customers_ca.csv

# Interactive mode
datacrew ask --interactive
```

**Options:**

| Flag | Description | Default |
|------|-------------|---------|
| `--format` | Output format: `table`, `json`, `csv`, `markdown` | `table` |
| `--export` | Export results to file | None |
| `--explain` | Show generated SQL query | `false` |
| `--interactive` | Enter interactive query mode | `false` |
| `--limit` | Maximum rows to return | `100` |

### `datacrew report`

Generate PDF reports using the multi-agent system.

```bash
# Generate a report
datacrew report "Q4 Executive Summary"

# Specify output file
datacrew report "Q4 Executive Summary" --output reports/q4_summary.pdf

# Use a template
datacrew report "Monthly Sales" --template executive

# Include specific analyses
datacrew report "Product Analysis" --include trends,comparisons,recommendations
```

**Options:**

| Flag | Description | Default |
|------|-------------|---------|
| `--output`, `-o` | Output PDF file path | `./report.pdf` |
| `--template` | Report template: `executive`, `detailed`, `minimal` | `executive` |
| `--include` | Analyses to include | All |
| `--date-range` | Date range for analysis | All data |
| `--verbose`, `-v` | Show agent reasoning | `false` |

### `datacrew config`

Manage configuration settings.

```bash
# Show current config
datacrew config show

# Set LLM model
datacrew config set llm.model "llama3.2:3b"

# Set database path
datacrew config set database.path "./data/mydata.db"

# Reset to defaults
datacrew config reset
```

### `datacrew schema`

Inspect database schema.

```bash
# List all tables
datacrew schema list

# Show table details
datacrew schema describe sales

# Show sample data
datacrew schema sample sales --rows 5
```

---

## Configuration

Configuration is stored in `~/.config/datacrew/config.toml` or `./datacrew.toml` in the project directory.

```toml
[datacrew]
version = "1.0.0"

[database]
path = "./data/datacrew.db"
echo = false

[llm]
provider = "docker"           # docker, ollama, openai
model = "llama3.2:3b"
temperature = 0.7
max_tokens = 4096
base_url = "http://localhost:11434"

[llm.docker]
runtime = "nvidia"            # nvidia, cpu
memory_limit = "8g"

[agents]
verbose = false
max_iterations = 10

[agents.analyst]
role = "Data Analyst"
goal = "Analyze data and write accurate SQL queries"

[agents.insights]
role = "Insights Specialist"
goal = "Find meaningful patterns and trends in data"

[agents.writer]
role = "Report Writer"
goal = "Create clear, compelling narrative content"

[agents.composer]
role = "PDF Composer"
goal = "Assemble professional PDF reports"

[reports]
output_dir = "./reports"
default_template = "executive"

[reports.templates.executive]
include_charts = true
include_recommendations = true
max_pages = 10

[reports.templates.detailed]
include_charts = true
include_recommendations = true
include_raw_data = true
max_pages = 50

[observability]
enabled = true
provider = "langfuse"         # langfuse, langsmith, console
trace_agents = true
log_tokens = true
```

---

## Docker Stack

### docker-compose.yml

```yaml
version: "3.9"

services:
  # Local LLM via Docker Model Runner
  llm:
    image: ollama/ollama:latest
    runtime: nvidia
    environment:
      - OLLAMA_HOST=0.0.0.0
    volumes:
      - ollama_data:/root/.ollama
    ports:
      - "11434:11434"
    deploy:
      resources:
        reservations:
          devices:
            - driver: nvidia
              count: 1
              capabilities: [gpu]
    healthcheck:
      test: ["CMD", "curl", "-f", "http://localhost:11434/api/tags"]
      interval: 30s
      timeout: 10s
      retries: 3

  # MCP Server for database access
  mcp-server:
    build:
      context: ./src/datacrew/mcp
      dockerfile: Dockerfile
    environment:
      - DATABASE_PATH=/data/datacrew.db
      - MCP_PORT=3000
    volumes:
      - ./data:/data
    ports:
      - "3000:3000"
    depends_on:
      - llm

  # Main application (for containerized usage)
  datacrew:
    build:
      context: .
      dockerfile: Dockerfile
    environment:
      - LLM_BASE_URL=http://llm:11434
      - MCP_SERVER_URL=http://mcp-server:3000
      - DATABASE_PATH=/data/datacrew.db
    volumes:
      - ./data:/data
      - ./reports:/reports
      - ./input:/input:ro
    depends_on:
      llm:
        condition: service_healthy
      mcp-server:
        condition: service_started
    profiles:
      - cli

volumes:
  ollama_data:
```

### Running the Stack

```bash
# Start LLM and MCP server
docker compose up -d llm mcp-server

# Pull the model (first time only)
docker compose exec llm ollama pull llama3.2:3b

# Run DataCrew commands
docker compose run --rm datacrew ingest /input/sales.xlsx
docker compose run --rm datacrew ask "What is total revenue?"
docker compose run --rm datacrew report "Sales Summary" -o /reports/summary.pdf

# Or run locally with Docker backend
datacrew ingest sales.xlsx
datacrew ask "What is total revenue?"
datacrew report "Sales Summary"
```

---

## Project Structure

```
datacrew/
├── pyproject.toml              # pixi/uv project config
├── pixi.lock
├── docker-compose.yml          # Full stack orchestration
├── Dockerfile
├── datacrew.toml               # Default configuration
├── README.md
│
├── src/
│   └── datacrew/
│       ├── __init__.py
│       ├── __main__.py         # Entry point
│       ├── cli.py              # Typer CLI commands
│       ├── config.py           # TOML configuration loader
│       │
│       ├── ingestion/          # CSV/XLSX → SQLite
│       │   ├── __init__.py
│       │   ├── readers.py      # File readers (pandas, openpyxl)
│       │   ├── schema.py       # Schema inference
│       │   └── database.py     # SQLite operations
│       │
│       ├── query/              # Natural language queries
│       │   ├── __init__.py
│       │   ├── nl2sql.py       # NL to SQL conversion
│       │   ├── executor.py     # Query execution
│       │   └── formatter.py    # Result formatting
│       │
│       ├── agents/             # CrewAI agents
│       │   ├── __init__.py
│       │   ├── crew.py         # Crew orchestration
│       │   ├── analyst.py      # Data Analyst agent
│       │   ├── insights.py     # Insights Specialist agent
│       │   ├── writer.py       # Report Writer agent
│       │   └── composer.py     # PDF Composer agent
│       │
│       ├── tools/              # Agent tools
│       │   ├── __init__.py
│       │   ├── sql_tools.py    # SQL execution tools
│       │   ├── analysis.py     # Statistical analysis tools
│       │   └── charts.py       # Chart generation tools
│       │
│       ├── mcp/                # MCP server
│       │   ├── __init__.py
│       │   ├── server.py       # MCP server implementation
│       │   ├── tools.py        # MCP tool definitions
│       │   └── Dockerfile      # MCP server container
│       │
│       ├── reports/            # PDF generation
│       │   ├── __init__.py
│       │   ├── generator.py    # Report generation orchestrator
│       │   ├── pdf.py          # PDF creation (WeasyPrint)
│       │   ├── charts.py       # Chart rendering
│       │   └── templates/      # HTML/CSS templates
│       │       ├── executive.html
│       │       ├── detailed.html
│       │       ├── minimal.html
│       │       └── styles.css
│       │
│       ├── llm/                # LLM integration
│       │   ├── __init__.py
│       │   ├── client.py       # LLM client (Docker/Ollama/OpenAI)
│       │   └── prompts.py      # Prompt templates
│       │
│       └── observability/      # Logging & tracing
│           ├── __init__.py
│           ├── tracing.py      # Distributed tracing
│           └── metrics.py      # Token/cost tracking
│
├── tests/
│   ├── __init__.py
│   ├── conftest.py             # Pytest fixtures
│   ├── test_cli.py
│   ├── test_ingestion.py
│   ├── test_query.py
│   ├── test_agents.py
│   ├── test_reports.py
│   └── fixtures/
│       ├── sample_sales.csv
│       ├── sample_products.xlsx
│       └── expected_outputs/
│
├── data/                       # Local data directory
│   └── .gitkeep
│
├── reports/                    # Generated reports
│   └── .gitkeep
│
└── docs/                       # Documentation (Quarto)
    ├── _quarto.yml
    ├── index.qmd
    └── chapters/
```

---

## Technology Stack

| Category | Tools |
|----------|-------|
| **Package Management** | pixi, uv |
| **CLI Framework** | Typer, Rich |
| **Local LLM** | Docker Model Runner, Ollama |
| **LLM Framework** | LangChain |
| **Multi-Agent** | CrewAI |
| **MCP** | Docker MCP Toolkit |
| **Database** | SQLite |
| **Data Processing** | pandas, openpyxl |
| **PDF Generation** | WeasyPrint |
| **Charts** | matplotlib, plotly |
| **Observability** | Langfuse, OpenTelemetry |
| **Testing** | pytest, DeepEval |
| **Containerization** | Docker, Docker Compose |

---

## Example Usage

### End-to-End Workflow

```bash
# 1. Start the Docker stack
docker compose up -d

# 2. Ingest your data
datacrew ingest quarterly_sales_2024.xlsx
datacrew ingest product_catalog.csv
datacrew ingest customer_data.csv

# 3. Explore with natural language queries
datacrew ask "How many records are in each table?"
datacrew ask "What are the top 10 products by revenue in Q4?"
datacrew ask "Show me the monthly sales trend for 2024"

# 4. Generate a comprehensive report
datacrew report "2024 Annual Sales Analysis" \
  --template detailed \
  --output reports/annual_2024.pdf \
  --include trends,top_products,regional_breakdown,recommendations \
  --verbose

# 5. View agent reasoning (verbose mode)
# [Data Analyst] Analyzing schema... found 3 tables
# [Data Analyst] Executing: SELECT strftime('%Y-%m', order_date) as month, SUM(revenue) ...
# [Insights Agent] Identified trend: 23% YoY growth in Q4
# [Insights Agent] Anomaly detected: December spike in electronics category
# [Report Writer] Generating executive summary...
# [PDF Composer] Assembling 12-page report...
# ✓ Report saved to reports/annual_2024.pdf
```

### Sample Report Output

```
┌────────────────────────────────────────────────────────────────┐
│                    2024 Annual Sales Analysis                  │
│                      Executive Summary                         │
├────────────────────────────────────────────────────────────────┤
│                                                                │
│  Key Findings:                                                 │
│  • Total revenue: $4.2M (+23% YoY)                            │
│  • Top product category: Electronics (38% of revenue)         │
│  • Strongest region: West Coast (42% of sales)                │
│  • Customer retention rate: 78%                                │
│                                                                │
│  [Monthly Revenue Trend Chart]                                 │
│                                                                │
│  Recommendations:                                              │
│  1. Expand electronics inventory for Q1 2025                  │
│  2. Increase marketing spend in Midwest region                │
│  3. Launch loyalty program to improve retention               │
│                                                                │
└────────────────────────────────────────────────────────────────┘
```

---

## Learning Outcomes

By building DataCrew, learners will be able to:

1. ✅ Set up modern Python projects with pixi and reproducible environments
2. ✅ Build professional CLI tools with Typer and Rich
3. ✅ Run local LLMs using Docker Model Runner
4. ✅ Ingest and query data from spreadsheets using natural language
5. ✅ Build MCP servers to connect AI agents to data sources
6. ✅ Design multi-agent systems with CrewAI
7. ✅ Generate PDF reports programmatically
8. ✅ Implement observability for AI applications
9. ✅ Test non-deterministic systems effectively
10. ✅ Deploy self-hosted AI applications with Docker Compose