# PiperQL — Full Documentation

> Open-source AI agent that lets you talk to PostgreSQL databases in plain English.

- URL: https://piperql.vercel.app
- GitHub: https://github.com/ajaysharmadeveloper/piperql
- License: AGPL-3.0
- Summary: https://piperql.vercel.app/llms.txt

## Overview

PiperQL is a self-hosted, open-source conversational AI agent for PostgreSQL databases. Users ask questions in natural language and receive direct answers — the AI handles schema understanding, SQL generation, query execution, and result formatting behind the scenes. No SQL is shown to the user unless explicitly requested. Your data never leaves your servers.

## Architecture

### Backend (FastAPI)
- REST API with async support
- LangGraph StateGraph for AI agent orchestration
- OpenAI GPT-4o-mini for language understanding
- SQLAlchemy ORM + Alembic for app database
- asyncpg for target database queries
- JWT authentication with bcrypt password hashing
- Server-Sent Events (SSE) for real-time streaming
- Mem0 for persistent AI memory across sessions
- Tavily for web search capability

### Frontend (Next.js)
- Next.js 16 with React 19
- Tailwind CSS 4 for styling
- Recharts for data visualization (10+ chart types)
- react-markdown with GFM support and syntax highlighting
- Light/dark/auto theme switching
- Real-time SSE streaming with AbortController support

### Agent Graph Flow

```
START → [retrieve_context] → [call_llm] → should_continue?
                                            ├─ no tools → [update_memory] → END
                                            └─ has tools → [call_tools] → [call_llm] (loop)
```

Loop detection: Same tool + same args called 3 times = force stop.

### Agent Tools

1. **query_database** — Execute SQL queries on target database (respects access mode)
2. **get_schema** — Fetch full database schema (tables, columns, types, constraints, foreign keys)
3. **get_table_sample** — Sample rows from a table for context
4. **list_databases** — List all databases on the PostgreSQL server
5. **generate_chart** — Create chart configs (bar, line, pie, donut, area, scatter, radar, radial_bar, stacked_bar, composed)
6. **web_search** — Search the web via Tavily for SQL syntax help and documentation

## Access Modes

| Mode | SELECT | INSERT/UPDATE/DELETE | DDL (CREATE/DROP/ALTER) |
|------|--------|---------------------|------------------------|
| Read Only | Instant | Blocked | Blocked |
| CRUD | Instant | Requires confirmation | Blocked |
| Full Access | Instant | Requires confirmation | Requires type-to-confirm |

Write operations always show the exact SQL to the user before execution.

## Database Models

### users
- id (UUID), username (unique), email (unique), password_hash, role (admin/user), created_at, updated_at

### conversations
- id (UUID), user_id (FK), title, summary, target_database, access_mode, created_at, updated_at

### messages
- id (UUID), conversation_id (FK), role (user/assistant), content, sql_query, query_result (JSONB), chart_config (JSONB), confirmation_status, created_at

### env_variables
- key (PK), value, description, updated_at
- Stores: OPENAI_API_KEY, OPENAI_MODEL, DB_HOST, DB_PORT, DB_USER, DB_PASSWORD, MEM0_API_KEY, TAVILY_API_KEY, FRONTEND_URL

## API Endpoints

### Authentication
- POST /api/auth/login — Login with username/password, returns JWT
- POST /api/auth/register — Create new user (admin only)
- GET /api/auth/me — Current user info

### Chat
- POST /api/chat/stream — SSE streaming agent response
- POST /api/chat/confirm — Confirm/cancel write operation

### Conversations
- GET /api/conversations/ — List user conversations
- POST /api/conversations/ — Create conversation
- DELETE /api/conversations/{id} — Delete conversation
- GET /api/conversations/{id}/messages — Get messages (paginated)

### Databases
- GET /api/databases/ — List PostgreSQL databases
- GET /api/databases/tables?db=name — List tables in database

### Settings
- GET /api/settings/ — List all settings
- PUT /api/settings/ — Update setting (admin)
- GET /api/settings/validate — Check missing required keys

### Health
- GET /health — Server health check

## SSE Stream Events

| Event | Description |
|-------|-------------|
| status | Agent progress updates (Loading schema, Analyzing, Executing query) |
| token | Streamed text token (word by word response) |
| result | Query result rows (JSON array) |
| chart | Chart configuration for frontend rendering |
| confirm | Write operation needs user confirmation |
| error | Error message |
| done | Stream complete with full response |

## Environment Variables

### Required (in .env)
- APP_DATABASE_URL — PostgreSQL connection for app database
- JWT_SECRET — Secret for JWT token signing

### Configurable (via Settings UI or .env)
- OPENAI_API_KEY — OpenAI API key
- OPENAI_MODEL — LLM model (default: gpt-4o-mini)
- DB_HOST, DB_PORT, DB_USER, DB_PASSWORD — Target database credentials
- MEM0_API_KEY — Mem0 persistent memory (optional)
- TAVILY_API_KEY — Web search (optional)
- FRONTEND_URL — Frontend CORS origin

## Installation

### Local Development
```bash
git clone https://github.com/ajaysharmadeveloper/piperql.git
cd piperql
make install
cp backend/.env.example backend/.env
# Edit .env: set APP_DATABASE_URL and JWT_SECRET
createdb ai_agent_db
make migrate
make create-admin
make run
```

### Docker
```bash
docker compose up -d
```

### Make Commands
- `make run` — Start both backend + frontend
- `make run-backend` — Start FastAPI backend only (port 8000)
- `make run-frontend` — Start Next.js frontend only (port 3000)
- `make install` — Install all dependencies
- `make migrate` — Run database migrations
- `make test` — Run all tests
- `make docker-up` — Start with Docker
- `make help` — Show all available commands

## License

AGPL-3.0 — https://github.com/ajaysharmadeveloper/piperql/blob/main/LICENSE

## Author

Ajay Kumar Sharma
- Website: https://www.ajaykumarsharma.co.in/
- GitHub: https://github.com/ajaysharmadeveloper
- LinkedIn: https://www.linkedin.com/in/ajaykumarsharma1996/
- Twitter/X: https://x.com/ajaykumar143136