# V3Q.AI — Setup & Operations Manual

## Table of Contents
1. [Overview](#overview)
2. [Prerequisites](#prerequisites)
3. [Quick Install](#quick-install)
4. [Manual Setup](#manual-setup)
5. [Configuration](#configuration)
6. [First Run — Web UI Walkthrough](#first-run--web-ui-walkthrough)
7. [Packaging & Deployment](#packaging--deployment)
8. [Security Architecture](#security-architecture)
9. [Operations & Monitoring](#operations--monitoring)
10. [Troubleshooting](#troubleshooting)

---

## Overview

V3Q.AI is a secure hybrid LLM text-to-SQL system. Business users ask questions in natural language, and the system generates SQL queries to answer them.

**How it works:**
1. User asks a question ("How many orders last month?")
2. **Claude API** (cloud) generates SQL from schema metadata — it NEVER sees actual data
3. **SQL Validator** checks the query is safe (SELECT only, no injection)
4. Query executes against **local PostgreSQL** (not remote databases)
5. **Local Qwen LLM** (via Ollama) analyzes results and generates a summary
6. Results shown in chat with data table, SQL viewer, and auto-charts

**Notification system:**
- Subscribe to document status changes by Belegnummer + email
- Automatic change detection during table sync (staging table diff)
- Email notifications via SMTP when order status changes
- Cross-system chain tracking (SAP B1 → ECC → Infor)

---

## Prerequisites

| Software | Version | Purpose | Required? |
|----------|---------|---------|-----------|
| Python | 3.12+ | Backend runtime | **Yes** |
| Node.js | 18+ | Frontend build | **Yes** |
| Docker Desktop | Latest | PostgreSQL container | Recommended |
| PostgreSQL | 16+ | Database (if no Docker) | One of Docker/native |
| Ollama | Latest | Local LLM runtime | Yes (for summaries) |

### Accounts
| Service | What you need |
|---------|--------------|
| Anthropic | API key for Claude (https://console.anthropic.com) |

### Network Ports
- **5432**: PostgreSQL
- **8001**: V3Q.AI backend API
- **5173**: Frontend dev server (development only)
- **11434**: Ollama (localhost only)
- Outbound HTTPS: api.anthropic.com

---

## Quick Install

### macOS / Linux

```bash
# 1. Unzip the package (or clone)
unzip v3q-ai-1.0.0.zip
cd middleware

# 2. Run the auto-setup script
chmod +x scripts/setup.sh
./scripts/setup.sh

# 3. Edit your API key
nano config/.env
# Set: ANTHROPIC_API_KEY=sk-ant-your-key-here

# 4. Start the backend
cd backend
source .venv/bin/activate
python main.py

# 5. Start the frontend (separate terminal)
cd frontend
npm run dev

# 6. Open browser
open http://localhost:5173
```

The setup script automatically:
- Detects your OS and installed tools
- Starts PostgreSQL (via Docker or Homebrew)
- Creates the database and all tables (idempotent — safe to re-run)
- Creates Python venv and installs all dependencies
- Installs npm packages and builds the frontend
- Runs preflight checks and shows clear results

### Windows

```powershell
# Run as Administrator
cd C:\V3Q\middleware
.\scripts\install_windows.ps1
```

---

## Manual Setup

If you prefer to do each step yourself:

### Step 1: Environment File

```bash
cp config/.env.example config/.env
# Edit and set:
#   ANTHROPIC_API_KEY=sk-ant-your-key-here
#   POSTGRES_PASSWORD=your-secure-password
```

### Step 2: PostgreSQL

**With Docker (recommended):**
```bash
docker compose up -d
# Waits for healthy, auto-runs setup_postgresql.sql on first start
docker compose ps   # should show v3q-postgres as "healthy"
```

**Without Docker (native PostgreSQL):**
```bash
# Create database and run schema
createdb v3q
psql -U your_user -d v3q -f scripts/setup_postgresql.sql
# Update config/.env: POSTGRES_USER=your_user
# Update config/app.yaml: database.local_pg.user: your_user
```

### Step 3: Python Backend

```bash
cd backend
python3 -m venv .venv
source .venv/bin/activate        # macOS/Linux
# .\.venv\Scripts\Activate.ps1   # Windows

pip install -r requirements.txt
```

### Step 4: Ollama + Model

```bash
# Install from https://ollama.com, then:
ollama serve                    # Start Ollama server
ollama pull qwen2.5:32b        # Download model (~20GB)
```

> For servers with less RAM, use `qwen2.5:14b` or `llama3.1:8b` and update `config/app.yaml` → `ollama.model`.

### Step 5: Frontend

```bash
cd frontend
npm install
npm run dev      # Development with hot reload on :5173
# OR
npx vite build   # Production build to dist/
```

### Step 6: Start

```bash
cd backend
source .venv/bin/activate
python main.py
# Backend runs on http://localhost:8001
# Frontend dev server on http://localhost:5173 (proxies API to :8001)
```

---

## Configuration

### config/app.yaml

```yaml
app:
  port: 8001           # Backend API port
  debug: false         # Set true for development

database:
  local_pg:
    host: localhost
    port: 5432
    database: v3q
    user: v3q_admin    # Must match your PostgreSQL user

ollama:
  model: "qwen2.5:32b"  # Change if using smaller model

claude:
  model: "claude-opus-4-20250514"
  max_tokens: 4096

notifications:
  enabled: false       # Set true after configuring SMTP
  smtp:
    host: ""           # e.g. smtp.gmail.com
    port: 587
    from_email: ""     # e.g. noreply@yourcompany.com
```

### config/.env

```env
ANTHROPIC_API_KEY=sk-ant-xxxxx    # REQUIRED
POSTGRES_PASSWORD=your-password    # REQUIRED for Docker
POSTGRES_USER=v3q_admin            # Must match app.yaml
```

---

## First Run — Web UI Walkthrough

After starting backend + frontend, open **http://localhost:5173**

### 1. Config Tab — Add Database Connections

Click **Config** in the top bar, then:

1. **Add Connection** — enter name, select type (SQL Server for ECC, HANA for B1)
2. **Test** — verifies the connection works
3. **Discover** — scans the remote schema for tables

Connection string examples:
- SQL Server: `DRIVER={ODBC Driver 17 for SQL Server};SERVER=myserver;DATABASE=mydb;UID=user;PWD=pass`
- HANA: `DRIVER={HDBODBC};SERVERNODE=myserver:30015;UID=user;PWD=pass`

### 2. Config Tab — Sync Tables

After discovery:
1. Select tables to sync from the remote DB
2. Click **Sync** — copies data to local PostgreSQL
3. Tables appear under "Synced Tables" with row counts

### 3. Chat Tab — Ask Questions

Switch to **Chat** tab:
- Type a question: "How many sales orders last month?"
- Claude generates SQL → you approve it → results shown with table + charts

### 4. Tracking Tab — Document Chains

Switch to **Tracking** tab:
- Search by Belegnummer (order number)
- View cross-system document chains (B1 → ECC → Infor)

### 5. Notifications Tab — Subscribe to Updates

Switch to **Notifications** tab:
1. Enter a **Belegnummer** (e.g. `4500012345`) and your **email**
2. Click **Subscribe** — immediately shows current chain status
3. A confirmation email is sent (if SMTP configured)
4. Every time the order status changes during sync, you get an email

To configure email:
1. Go to **Notifications → Settings** tab
2. Enter SMTP details (host, port, credentials)
3. Click **Send Test** to verify
4. Enable notifications with the toggle

---

## Packaging & Deployment

### Create a distributable zip

On your development machine:

```bash
cd middleware
chmod +x scripts/package.sh
./scripts/package.sh
# Output: ../v3q-ai-1.0.0.zip
```

### Deploy to target server

```bash
# 1. Copy zip to server
scp v3q-ai-1.0.0.zip user@server:/opt/

# 2. On the server:
cd /opt
unzip v3q-ai-1.0.0.zip
cd middleware
chmod +x scripts/setup.sh
./scripts/setup.sh

# 3. Edit config
nano config/.env    # set ANTHROPIC_API_KEY

# 4. Start
cd backend && source .venv/bin/activate && python main.py
```

The setup script is idempotent — run it as many times as you want. It detects what's already installed and skips it.

---

## Security Architecture

### What Claude API sees (SAFE)
- Table names, column names, data types, row counts
- Join relationships
- User's question
- Abstract error feedback ("The column name was incorrect")

### What Claude API NEVER sees
- Actual data values (customer names, amounts, addresses)
- SQL error messages (processed locally by Qwen)
- Connection strings or server IPs
- Email addresses, phone numbers, SSNs

### SQL Safety
- **Whitelist**: Only SELECT statements
- **Blacklist**: INSERT, UPDATE, DELETE, DROP, ALTER, TRUNCATE, CREATE, EXEC
- **Injection protection**: No semicolons, no comments (--), no UNION attacks
- **Schema restriction**: Only v3q_data and v3q_results accessible
- **Read-only**: All queries wrapped in SET TRANSACTION READ ONLY

### Audit
Every Claude API call logged to `v3q_config.audit_log` — view in Config → API & Logs.

---

## Operations & Monitoring

### Log Files (JSON-lines format)

| File | Contents |
|------|----------|
| `logs/v3q_errors.jsonl` | Warnings + Errors — feed to Claude Code |
| `logs/v3q_app.jsonl` | All application logs |
| `logs/v3q_api.jsonl` | All LLM API interactions |
| `logs/v3q_queries.jsonl` | All SQL queries executed |

### Feeding logs to Claude Code

```bash
# On server:
curl http://localhost:8001/api/setup/logs/export > errors.jsonl

# On dev machine:
cat errors.jsonl | claude "Analyze these V3Q.AI errors and suggest fixes"
```

### Health Check

```bash
curl http://localhost:8001/health
# {"status": "ok", "version": "1.0.0", "service": "V3Q.AI"}
```

---

## Troubleshooting

### "PostgreSQL connection failed"
```bash
# Docker:
docker compose ps              # Check container status
docker compose logs postgres   # Check logs
docker compose restart postgres

# Native:
pg_isready -h localhost -p 5432
brew services restart postgresql@16   # macOS
sudo systemctl restart postgresql     # Linux
```

### "Schema / tables missing"
```bash
# Safe to re-run — fully idempotent:
psql -U v3q_admin -d v3q -f scripts/setup_postgresql.sql
# Or with Docker:
docker compose exec postgres psql -U v3q_admin -d v3q -f /docker-entrypoint-initdb.d/01_setup.sql
```

### "Ollama not available"
```bash
ollama serve                     # Start
curl http://localhost:11434      # Test
ollama list                      # Check models
ollama pull qwen2.5:32b         # Download model
```

### "Claude API error"
- Check `ANTHROPIC_API_KEY` in config/.env
- Check outbound HTTPS to api.anthropic.com
- Check credits: https://console.anthropic.com

### "npm install fails"
```bash
cd frontend
rm -rf node_modules package-lock.json
npm install
```

### "pip install fails on pyodbc/hdbcli"
```bash
# macOS:
brew install unixodbc

# Linux:
sudo apt install unixodbc-dev python3-dev
```

### Run tests
```bash
cd middleware
python -m pytest tests/ -v
```
