Metadata-Version: 2.4
Name: dbagent-cli
Version: 0.4.1
Summary: Enterprise AI Database Copilot, Schema Graph Introspector & Autonomous Recovery Agent (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 (`dbagent-cli`)

> **Enterprise AI Database Copilot, Schema Graph Introspector & Autonomous Recovery Agent**  
> *Author: Santhosh Gupta | 100% Free, Standalone, Works 100% Offline (Ollama) or with High-Speed Cloud AI (Gemini / Groq / OpenRouter).*

[![PyPI Version](https://img.shields.io/pypi/v/dbagent-cli.svg)](https://pypi.org/project/dbagent-cli/)
[![Python Versions](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)
[![Database Support](https://img.shields.io/badge/Databases-Postgres%20%7C%20MySQL%20%7C%20SQLite%20%7C%20MSSQL%20%7C%20Oracle%20%7C%20DuckDB%20%7C%20MongoDB-blue.svg)](https://pypi.org/project/dbagent-cli/)

---

## 🌟 What is DB-Agent?

**DB-Agent** (`dbagent-cli`) is an enterprise-grade, privacy-first AI Database Copilot. Designed for developers, DBAs, data engineers, and analysts, it transforms plain English prompts into highly optimized, dialect-precise SQL, executes queries under strict safety guardrails, and renders crystal-clear data visualizations.

### 🚀 Key Highlights:
* **Instant On-Demand Introspection**: Targeted single-query metadata resolution (< 30ms) across databases with 300+ tables.
* **Interactive Data Grid Suite**: Native desktop GUI window (`:window`), browser virtual grid (`:web`), column paging (`:page`), and responsive zoom-out (`:wide`).
* **Long-Term Post-Commit Rollback**: Pluggable recovery engine that snapshots pre-write row states, detects concurrency conflicts, and allows safe revert hours or days after `COMMIT`.
* **Multi-Hop Join Discovery**: Automatically computes the shortest foreign-key join paths between disconnected tables.
* **Enterprise Safety Guardrails**: 5 operational modes (`SAFE`, `READ_ONLY`, `CONFIRM`, `SANDBOX`, `ADMIN`), PII masking, and destructive query protection.
* **Zero-Cost Local or Cloud AI**: Native support for **Google Gemini** (`gemini-3.6-flash`), **Local Ollama** (100% offline), **Groq**, and **OpenRouter**.

---

## 📦 Installation

Install `dbagent-cli` via pip:

```bash
# Recommended: Install with all database drivers
pip install --upgrade "dbagent-cli[all]"
```

### Optional Driver Extras:
```bash
pip install "dbagent-cli[postgres]"   # PostgreSQL (psycopg2-binary, asyncpg)
pip install "dbagent-cli[mysql]"      # MySQL & MariaDB (pymysql, cryptography)
pip install "dbagent-cli[mongo]"      # MongoDB (pymongo)
pip install "dbagent-cli[duckdb]"     # DuckDB (duckdb, duckdb-engine)
pip install "dbagent-cli[mssql]"      # Microsoft SQL Server (pyodbc)
pip install "dbagent-cli[oracle]"     # Oracle Database (oracledb)
```

---

## ⚡ 2-Minute Quickstart

### 1. Configure AI Provider
```bash
db-agent setup
```
Follow the interactive prompt to choose your preferred AI backend:
* **Google Gemini (Recommended Cloud)**: Ultra-fast inference with free tier (`gemini-3.6-flash`).
* **Local Ollama (Recommended Offline)**: 100% private, runs entirely on your GPU/CPU with 0 API keys.
* **Groq / OpenRouter**: Fast open-weights inference (Llama 3.3, Qwen 2.5 Coder).

### 2. Connect Database & Save Profile Alias
```bash
db-agent connect postgresql://user:pass@localhost:5432/mydb --alias my_db
```
*(Supports PostgreSQL, MySQL, SQLite, MSSQL, Oracle, DuckDB, and MongoDB).*

### 3. Ask Plain English Questions or Start Interactive Chat
```bash
# Ask a one-off question:
db-agent ask "list top 10 most recent active users" --db my_db

# Launch the interactive AI shell:
db-agent chat --db my_db
```

---

## 🪟 Interactive Viewing & Multi-Column Scroller Suite

When working with wide enterprise tables containing 20, 40, or 60+ columns, DB-Agent provides 5 flexible viewing modes:

| View Mode | In-Chat Command | Description |
|---|---|---|
| **Desktop GUI Grid** | `:window` / `:grid` | Opens a native desktop window with ↔️ horizontal & ↕️ vertical scrollbars, live search/filter, and CSV export. |
| **Browser Data Table** | `:web` / `:browser` | Generates a standalone virtual data grid and opens it in your default browser. |
| **Column Paging** | `:page <n>` | Browse wide columns in clean horizontal chunks (`:page 1`, `:page 2`, `:page 3`). |
| **Vertical Card View** | `:card` / `:v` | Displays every row as an expanded vertical record card with all columns cleanly listed. |
| **Responsive Zoom-Out** | `:wide` | Dynamically auto-scales column count to fill wide or zoomed-out (`Ctrl` + `-`) terminal windows. |

---

## 🛡️ Enterprise Long-Term Rollback & Recovery Engine

Unlike standard transaction rollbacks that only protect uncommitted sessions, DB-Agent includes a **Post-Commit Long-Term Recovery Engine**:

```text
User: Update all inactive users to ACTIVE where last_login < '2025-01-01'
AI:   Modifying 42 rows. Capturing pre-write snapshot...
      Change committed successfully.
      Change ID: CS-20260828-001 (Recovery available for 30 days)

... 5 days later ...

User: db-agent rollback CS-20260828-001 --db my_db
AI:   Verifying live data checksums... No concurrency conflicts detected.
      Inverse UPDATE executed successfully. 42 rows restored!
```

### Recovery CLI Commands:
```bash
# List all recorded write ChangeSets:
db-agent changes

# Inspect before/after row snapshots of a change:
db-agent changes-show CS-20260828-001

# Preview rollback SQL without executing:
db-agent rollback CS-20260828-001 --preview

# Execute rollback with conflict safety:
db-agent rollback CS-20260828-001 --conflict-policy abort

# Roll back the most recent change:
db-agent rollback --last

# Manage recovery storage retention:
db-agent recovery status
db-agent recovery cleanup --older-than-days 30
```

---

## 🔒 Production Safety Guardrails & Operational Modes

DB-Agent provides 5 strictly enforced operational safety modes:

* **`SAFE`**: Only read-only queries (`SELECT`, `SHOW`, `DESCRIBE`, `EXPLAIN`, `WITH`) allowed. All writes and DDL are blocked.
* **`READ_ONLY`**: Enforced on `PRODUCTION` environments with query execution timeouts and row limits.
* **`CONFIRM`** *(Default)*: Read queries execute automatically; write (`UPDATE`, `INSERT`, `DELETE`) and DDL (`ALTER`, `DROP`) operations require user confirmation.
* **`SANDBOX`**: Executes writes within an uncommitted transaction to generate before/after diffs without modifying live data.
* **`ADMIN`**: Full administrative execution with audit tracking.

### Sensitive Data Masking:
Confidential fields (emails, passwords, API tokens, credit cards) are automatically masked in console outputs:
`a***@motivitylabs.com`, `**********`, `****-****-****-1234`.

---

## 🧠 Schema Graph & Multi-Hop JOIN Discovery

DB-Agent builds an in-memory directed graph of foreign-key relationships across your database schema:

```bash
# Discover shortest foreign-key path between disconnected tables:
db-agent path ivis_user ivis_camera_mapping --db my_db

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

---

## 🏥 Database Doctor & Query Diagnostics

Run comprehensive database health checks and EXPLAIN plan optimizations:

```bash
# Full connectivity, latency, index, and table health diagnostics:
db-agent doctor --db my_db

# Analyze query execution plan and recommend missing indexes:
db-agent optimize "SELECT * FROM ivis_user WHERE email = 'test@example.com'" --db my_db
```

---

## 📚 Business Semantic Dictionary

Teach DB-Agent your domain-specific metrics and shorthand:

```bash
# Define a business metric:
db-agent define active_user "A user who logged in within the last 30 days" --sql "last_login >= NOW() - INTERVAL '30 days'"

# List all saved business terminology:
db-agent definitions

# Manage parameterized query templates:
db-agent template list
```

---

## 📖 Complete CLI Command Reference

| Command | Usage | Description |
|---|---|---|
| **`ask`** | `db-agent ask "<prompt>" --db <alias>` | Translates natural language to SQL, executes query, and renders table. |
| **`chat`** | `db-agent chat --db <alias>` | Launches interactive multi-turn AI copilot shell. |
| **`doctor`** | `db-agent doctor --db <alias>` | Runs database health, connectivity, and index diagnostics. |
| **`path`** | `db-agent path <t1> <t2> --db <alias>` | Discovers shortest multi-hop foreign-key join path. |
| **`explain-join`** | `db-agent explain-join <t1> <t2>` | Explains join relationship, cardinality, and syntax. |
| **`optimize`** | `db-agent optimize "<SQL>" --db <alias>` | Analyzes EXPLAIN plan and recommends performance indexes. |
| **`scan`** | `db-agent scan --db <alias> -o schema.md` | Full database introspection and Markdown catalog export. |
| **`refresh-schema`** | `db-agent refresh-schema --db <alias>` | Refreshes schema cache and reports detected diffs. |
| **`schema-status`** | `db-agent schema-status --db <alias>` | Displays schema cache age, version, and health. |
| **`changes`** | `db-agent changes` | Lists recorded write ChangeSets. |
| **`changes-show`** | `db-agent changes-show <id>` | Shows before/after snapshot of a ChangeSet. |
| **`rollback`** | `db-agent rollback <id> [--preview] [--last]` | Generates and executes inverse rollback SQL. |
| **`recovery`** | `db-agent recovery [list\|status\|cleanup]` | Manages snapshot storage and retention policies. |
| **`define`** | `db-agent define <term> "<desc>" --sql "<sql>"` | Saves business terminology into semantic dictionary. |
| **`definitions`** | `db-agent definitions` | Lists all defined business terminology. |
| **`template`** | `db-agent template [list\|add\|run]` | Manages reusable, parameterized query templates. |
| **`audit`** | `db-agent audit` | Displays recent query execution audit log. |
| **`connect`** | `db-agent connect <url> --alias <name>` | Tests database connection and saves profile. |
| **`profiles`** | `db-agent profiles` | Lists all configured database profiles. |
| **`use`** | `db-agent use <provider>` | Switches active AI provider (`gemini`, `ollama`, `groq`, `openrouter`). |
| **`models`** | `db-agent models` | Status of local Ollama models and cloud AI providers. |
| **`setup`** | `db-agent setup` | 1-Click AI configuration setup wizard. |
| **`config`** | `db-agent config` | Interactive AI API keys and preferences wizard. |
| **`guide`** | `db-agent guide` | Displays full interactive command cheatsheet. |

---

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

Inside the interactive chat REPL, use these colon commands:

```text
  :window                     -> Open native desktop GUI data grid with horizontal & vertical scrollbars
  :web                        -> Open interactive browser data table with virtual scrolling
  :page <n>                   -> View column page <n> for wide tables (e.g. :page 1, :page 2)
  :card                       -> View last result in expanded vertical card format (all columns)
  :wide                       -> Render all columns across expanded / zoomed-out terminal
  :scroll                     -> Interactive terminal column scroller
  :tables                     -> List all tables in this database
  :table <name>               -> Inspect columns, PKs, types & sample data
  :fk <name>                  -> View mapped foreign keys & related tables
  :path <t1> <t2>             -> Discover shortest join path between tables
  :join <t1> <t2>             -> Explain join cardinality & syntax
  :doctor                     -> Run database health diagnostics
  :mode <name>                -> Switch mode (SAFE, READ_ONLY, CONFIRM, SANDBOX, ADMIN)
  :changes                    -> List recent write ChangeSets
  :provider <name>            -> Switch AI provider (gemini, ollama, groq, openrouter)
  :model <name>               -> Switch active model name (e.g. gemini-3.6-flash)
  :models                     -> Show active AI provider and model status
  :explain                    -> Show generated SQL & query analysis for last turn
  :history                    -> View history of queries in this session
  :auto / :noauto             -> Toggle automatic execution of read queries
  :run                        -> Re-execute the last generated SQL query
  :clear                      -> Clear conversation context
  :export <file>              -> Save the last generated SQL to a file
  :exit                       -> Exit chat session
```

---

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