Metadata-Version: 2.4
Name: dbagent-cli
Version: 0.3.9
Summary: Production-Grade AI Database Copilot with Interactive Desktop & Browser Data Grid Viewers (CLI)
Author: Santhosh Gupta
Requires-Python: >=3.9
Description-Content-Type: text/markdown
Requires-Dist: typer>=0.9.0
Requires-Dist: rich>=13.0.0
Requires-Dist: prompt-toolkit>=3.0.0
Requires-Dist: pydantic>=2.0.0
Requires-Dist: sqlalchemy>=2.0.0
Requires-Dist: requests>=2.28.0
Requires-Dist: httpx>=0.24.0
Requires-Dist: python-dotenv>=1.0.0
Provides-Extra: all
Requires-Dist: psycopg2-binary>=2.9.0; extra == "all"
Requires-Dist: pymysql>=1.0.0; extra == "all"
Requires-Dist: pymongo>=4.0.0; extra == "all"
Requires-Dist: duckdb>=0.9.0; extra == "all"
Provides-Extra: dev
Requires-Dist: pytest>=7.0.0; extra == "dev"
Requires-Dist: pytest-asyncio>=0.20.0; extra == "dev"

# ⚡ DB-Agent

> **Enterprise AI Database Copilot & Autonomous Data Agent (CLI)**  
> *Author: Santhosh Gupta | 100% Free, Standalone, Works Offline (Ollama) or with Cloud AI (Gemini / Groq / OpenRouter).*

[![PyPI Version](https://img.shields.io/pypi/v/dbagent-cli.svg)](https://pypi.org/project/dbagent-cli/)
[![Python Version](https://img.shields.io/pypi/pyversions/dbagent-cli.svg)](https://pypi.org/project/dbagent-cli/)
[![License: MIT](https://img.shields.io/badge/License-MIT-yellow.svg)](https://opensource.org/licenses/MIT)

---

## 🌟 What is DB-Agent?

**DB-Agent** is a production-grade AI Database Agent and Copilot. It empowers developers, DBAs, data analysts, and non-technical users to inspect schemas, discover complex multi-hop table relationships, generate and optimize SQL, execute queries with enterprise safety guardrails, and visualize data directly in the terminal.

---

## 🚀 2-Minute Quickstart

### Step 1: Install from PyPI
```bash
pip install "dbagent-cli[all]"
```

### Step 2: 1-Click AI Setup
```bash
db-agent setup
```
Choose between:
1. **Local Offline AI (Ollama)**: Zero API keys, 100% private, runs entirely on your machine.
2. **Free Cloud AI (Gemini / Groq / OpenRouter)**: Ultra-fast cloud processing.
3. **Database Tools Only**: Standard DB introspection and CLI tools without AI.

### Step 3: Connect Database & Save Alias
```bash
db-agent connect postgresql://user:password@localhost:5432/mydb --alias my_db
```
*(Supports PostgreSQL, MySQL, SQLite, SQL Server, Oracle, DuckDB, and MongoDB).*

### Step 4: Ask Plain English Questions or Launch Chat!
```bash
# 1. Ask a question and get instant live results table + ASCII charts
db-agent ask "list top 10 users" --db my_db

# 2. Or start the interactive AI copilot shell
db-agent chat --db my_db
```

---

## 🛡️ Key Enterprise Features

### 1. 🧠 Relationship Graph & Smart Multi-Hop JOINs
Automatically discovers multi-hop join paths between disconnected tables without guessing arbitrary joins.
```bash
# Find shortest relationship path between two tables:
db-agent path users events --db my_db

# Explain join cardinality (1:1, 1:N) and recommended join syntax:
db-agent explain-join customers orders --db my_db
```

### 2. ⚡ Persistent Schema Cache & Change Detection
Never runs expensive full database scans on every query. Local cache (`~/.dbagent/cache/`) detects schema diffs:
```bash
db-agent refresh-schema --db my_db
db-agent schema-status --db my_db
```

### 3. 🔒 Safety Modes & Production Protection
Five operational safety modes enforce strict boundaries before SQL execution:
* **`SAFE`**: Only `SELECT`, `SHOW`, `DESCRIBE`, `EXPLAIN`, `WITH` allowed. All writes and DDL are strictly blocked.
* **`READ_ONLY`**: Enforced on `PRODUCTION` environments with 5s timeouts and row caps.
* **`CONFIRM`**: Reads auto-execute; write and DDL operations require interactive confirmation.
* **`SANDBOX`**: Writes executed inside uncommitted transactions with before/after diff preview.
* **`ADMIN`**: Full administrative access with audit tracking.

### 4. ⏪ Change Sets & Snapshot Rollback
Before executing modifying queries, DB-Agent captures a recovery snapshot and creates an auditable ChangeSet:
```bash
db-agent changes
db-agent changes-show CS-20260828-001
db-agent rollback CS-20260828-001 --db my_db
```

### 5. 🏥 Database Doctor & Query Optimizer
Run comprehensive health checks on connectivity, latency, unindexed foreign keys, and slow queries:
```bash
# Database health diagnostics:
db-agent doctor --db my_db

# EXPLAIN plan analysis & index recommendations:
db-agent optimize "SELECT * FROM events WHERE camera_id = 42" --db my_db
```

### 6. 📚 Business Semantic Dictionary & Query Templates
Teach DB-Agent your domain-specific metrics and definitions:
```bash
# Define a business metric:
db-agent define offline_camera "A camera with no heartbeat for > 2 minutes" --sql "last_heartbeat < NOW() - INTERVAL '2 mins'"

# List all definitions:
db-agent definitions

# Manage trusted SQL templates:
db-agent template list
```

### 7. 🎭 Sensitive Data Masking & RBAC
Automatically classifies columns into PII, Secrets, and Financial data, masking confidential values in outputs (`s***@gmail.com`, `**********`, `****-****-****-1234`).

### 8. 📊 Automatic Visualization Detector
Detects when query results represent time series, category distributions, or single metrics, and renders ASCII/Unicode charts directly in your terminal.

---

## 📖 Complete CLI Command Reference

| Command | How to Use | Purpose |
|---|---|---|
| **`ask`** | `db-agent ask "list top 10 users" --db my_db` | Natural language to SQL -> executes & renders table. |
| **`chat`** | `db-agent chat --db my_db` | Interactive terminal copilot with multi-turn context. |
| **`doctor`** | `db-agent doctor --db my_db` | Comprehensive database and AI health diagnostic check. |
| **`path`** | `db-agent path users events --db my_db` | Shortest multi-hop foreign key join path discovery. |
| **`explain-join`** | `db-agent explain-join customers orders` | Explains join relationship, cardinality, and syntax. |
| **`optimize`** | `db-agent optimize "<SQL>" --db my_db` | Analyzes EXPLAIN plan and recommends indexes. |
| **`scan`** | `db-agent scan --db my_db --export db.md` | Full database schema introspection & Markdown export. |
| **`refresh-schema`**| `db-agent refresh-schema --db my_db` | Refreshes schema cache and reports detected diffs. |
| **`schema-status`** | `db-agent schema-status --db my_db` | Displays schema cache age, version, and health. |
| **`changes`** | `db-agent changes` | Lists recorded write ChangeSets. |
| **`changes-show`**| `db-agent changes-show CS-20260828-001` | Shows before/after snapshot of a ChangeSet. |
| **`rollback`** | `db-agent rollback CS-20260828-001` | Generates and executes inverse rollback SQL. |
| **`define`** | `db-agent define revenue "SUM(order.amount)"` | Saves a business definition into the semantic layer. |
| **`definitions`** | `db-agent definitions` | Lists all defined business terminology. |
| **`template`** | `db-agent template list` | Manages reusable, parameterized query templates. |
| **`audit`** | `db-agent audit` | Displays recent query execution audit log. |
| **`connect`** | `db-agent connect <url> --alias my_db` | Tests connection and saves profile alias. |
| **`use`** | `db-agent use gemini` | Switches default AI provider. |
| **`run`** | `db-agent run "SELECT * FROM users" --db my_db` | Executes raw SQL query with Rich table output. |
| **`generate`** | `db-agent generate "ETL script" --db my_db -o etl.py` | Generates Python ETL, Alembic migrations, or APIs. |
| **`setup`** | `db-agent setup` | 1-Click setup wizard for local or cloud AI. |
| **`config`** | `db-agent config` | Interactive AI API keys and preferences wizard. |
| **`profiles`** | `db-agent profiles` | Lists all saved database connection profiles. |
| **`models`** | `db-agent models` | Status of local Ollama models and cloud AI providers. |
| **`guide`** | `db-agent guide` | Displays full interactive command cheatsheet. |

---

## 💬 In-Chat REPL Commands (`db-agent chat`)

Inside the interactive chat shell, you can use built-in colon commands:

* **`:doctor`** — Run database health diagnostics.
* **`:path <t1> <t2>`** — Discover shortest join path between tables.
* **`:join <t1> <t2>`** — Explain join cardinality and recommended join syntax.
* **`:mode <SAFE|CONFIRM|SANDBOX|ADMIN>`** — Switch safety mode mid-session.
* **`:changes`** — List recent write ChangeSets.
* **`:tables`** — List all database tables.
* **`:table <name>`** — Inspect columns, types, primary keys, and sample data.
* **`:fk <name>`** — View incoming and outgoing foreign keys.
* **`:provider <name>`** — Switch AI provider (`ollama`, `gemini`, `groq`, `openrouter`).
* **`:model <name>`** — Switch active model name on the fly.
* **`:explain`** — Show generated SQL and query analysis for the last turn.
* **`:history`** — View query history for the session.
* **`:auto` / `:noauto`** — Toggle automatic execution of read queries.
* **`:run`** — Re-execute the last generated SQL query.
* **`:clear`** — Clear multi-turn conversational context.
* **`:export <file>`** — Save the last generated SQL to a file.
* **`:exit`** — Exit chat session.

---

## 📄 License
MIT License. Built with ❤️ by Santhosh Gupta.
