Metadata-Version: 2.4
Name: dbagent-cli
Version: 0.11.1
Summary: The Privacy-First AI Database Copilot, Schema Graph Introspector & Post-Commit Recovery Engine (CLI)
Author: Santhosh Gupta
License: MIT
Project-URL: Homepage, https://github.com/santhoshgupta/dbagent-cli
Project-URL: Documentation, https://github.com/santhoshgupta/dbagent-cli#readme
Project-URL: Repository, https://github.com/santhoshgupta/dbagent-cli.git
Project-URL: Issues, https://github.com/santhoshgupta/dbagent-cli/issues
Keywords: database,sql,ai,llm,copilot,postgresql,mysql,sqlite,duckdb,mongodb,cli,introspection
Classifier: Development Status :: 4 - Beta
Classifier: Environment :: Console
Classifier: Intended Audience :: Developers
Classifier: Intended Audience :: System Administrators
Classifier: License :: OSI Approved :: MIT License
Classifier: Operating System :: OS Independent
Classifier: Programming Language :: Python :: 3
Classifier: Programming Language :: Python :: 3.9
Classifier: Programming Language :: Python :: 3.10
Classifier: Programming Language :: Python :: 3.11
Classifier: Programming Language :: Python :: 3.12
Classifier: Topic :: Database
Classifier: Topic :: Database :: Front-Ends
Classifier: Topic :: Scientific/Engineering :: Artificial Intelligence
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: postgres
Requires-Dist: psycopg2-binary>=2.9.0; extra == "postgres"
Provides-Extra: mysql
Requires-Dist: pymysql>=1.0.0; extra == "mysql"
Provides-Extra: mongo
Requires-Dist: pymongo>=4.0.0; extra == "mongo"
Provides-Extra: duckdb
Requires-Dist: duckdb>=0.9.0; extra == "duckdb"
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"
Requires-Dist: build>=1.0.0; extra == "dev"
Requires-Dist: twine>=4.0.0; extra == "dev"

# ⚡ DB-Agent (`dbagent-cli`)

> **The Privacy-First AI Database Copilot, Schema Graph Introspector & Post-Commit Recovery Engine**  
> *Author: Santhosh Gupta | 100% Free, Standalone, Works 100% Offline (Ollama) or with Cloud AI (Claude 3.7 / GPT-4o / DeepSeek / Gemini / Groq).*

[![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%20DuckDB%20%7C%20MSSQL%20%7C%20Oracle%20%7C%20MongoDB-blue.svg)](https://pypi.org/project/dbagent-cli/)

---

## 🌟 What is DB-Agent?

**DB-Agent** is an enterprise-ready, privacy-first database AI copilot that translates plain English into dialect-precise SQL, explores schemas on-demand (<30ms), enforces strict multi-tier safety guardrails, and captures pre-write snapshots allowing you to safely rollback database modifications days or weeks after `COMMIT`.

```text
"Show me the top 10 customers by order value"
                      ↓
           [ DB-Agent CLI Engine ]
   (Targeted Schema Sub-Graph + AST Safety)
                      ↓
  SELECT c.customer_id, c.first_name, SUM(o.total_amount) AS total_spend
  FROM customers c
  JOIN orders o ON c.customer_id = o.customer_id
  GROUP BY c.customer_id, c.first_name
  ORDER BY total_spend DESC LIMIT 10;
                      ↓
            ⚡ Executed in 0.4s
  ┌─────────────┬────────────┬─────────────┐
  │ customer_id │ first_name │ total_spend │
  ├─────────────┼────────────┼─────────────┤
  │ 1           │ Alice      │ $3,450.00   │
  │ 3           │ Claire     │ $2,198.50   │
  └─────────────┴────────────┴─────────────┘
```

---

## 🚀 5-Second Instant Trial (Zero Setup Required)

Want to test DB-Agent without configuring a database or API keys?

```bash
# 1. Install DB-Agent
pip install "dbagent-cli[all]"

# 2. Launch built-in demo e-commerce database with interactive AI copilot
db-agent demo
```

---

## ⚡ 3-Minute Quickstart

### 1. Install
```bash
pip install --upgrade "dbagent-cli[all]"
```

### 2. Configure AI Backend (1-Click Setup)
```bash
db-agent setup
```
Choose from 8 built-in providers:
- **Local Ollama** (100% private, 100% offline with `qwen2.5-coder`, `llama3.3`, `deepseek-r1`)
- **Google Gemini** (`gemini-3.6-flash`, `gemini-2.5-flash`)
- **Anthropic Claude** (`claude-3-7-sonnet-20250219`, `claude-3-5-sonnet`)
- **OpenAI ChatGPT** (`gpt-4o`, `gpt-4.5-preview`, `o3-mini`)
- **DeepSeek AI** (`deepseek-chat` V3, `deepseek-reasoner` R1)
- **Mistral AI** (`codestral-latest`, `mistral-large-latest`)
- **Groq** (`llama-3.3-70b-versatile`)
- **OpenRouter** (`meta-llama/llama-3.3-70b-instruct`)

### 3. Connect Your Database
```bash
# Interactive guided connection
db-agent connect

# Or connect directly with an alias
db-agent connect postgresql://user:password@localhost:5432/app_db --alias prod_db
```

### 4. Ask Plain English Questions
```bash
# One-off query:
db-agent ask "count active users grouped by country" --db prod_db

# CI/CD machine-readable JSON export:
db-agent ask "count orders by status" --db prod_db --format json

# Interactive Claude-Style Shell with live streaming & prompt queuing:
db-agent chat --db prod_db
```

---

## 📊 Database Compatibility Matrix

| Database Engine | Introspection | Direct Queries | Multi-Hop JOINs | Transactions | 30-Day Recovery | Status |
| :--- | :---: | :---: | :---: | :---: | :---: | :--- |
| **PostgreSQL / CockroachDB** | ✓ | ✓ | ✓ | ✓ | ✓ | **Fully Supported** |
| **MySQL / MariaDB** | ✓ | ✓ | ✓ | ✓ | ✓ | **Fully Supported** |
| **SQLite** | ✓ | ✓ | ✓ | ✓ | ✓ | **Fully Supported** |
| **DuckDB** | ✓ | ✓ | ✓ | ✓ | ✓ | **Fully Supported** |
| **Microsoft SQL Server** | ✓ | ✓ | ✓ | ✓ | ✓ | **Fully Supported** |
| **Oracle Database** | ✓ | ✓ | ✓ | ✓ | ✓ | **Fully Supported** |
| **MongoDB** | ✓ (JSON) | ✓ (Filters) | N/A | ✓ | N/A | **Document Supported** |

---

## 🪟 Interactive Viewing & Data Grid Suite

When querying wide enterprise tables with 20 to 60+ columns, DB-Agent provides 5 flexible viewing tools:

| View Mode | In-Chat Command | Description |
|---|---|---|
| **Desktop GUI Grid** | `:window` / `:grid` | Native desktop window with horizontal/vertical scrolling, live search filters, and CSV export. |
| **Browser Virtual Table** | `:web` / `:browser` | Self-contained HTML virtual data grid opened in default browser. |
| **Horizontal Column Paging**| `:page <n>` | Browse wide column sets in clean horizontal slices (`:page 1`, `:page 2`). |
| **Vertical Record Cards** | `:card` / `:v` | Renders rows as expanded vertical record cards. |
| **Responsive Zoom View** | `:wide` | Expands across full terminal width. |

---

## 🛡️ 5 Operational Safety Tiers

| Mode | Reads | Writes | DDL | Confirmation | Best For |
| :--- | :---: | :---: | :---: | :---: | :--- |
| **`SAFE`** | Allowed | **Blocked** | **Blocked** | None | Read-only analysis |
| **`READ_ONLY`** | Allowed | **Blocked** | **Blocked** | None | **Production Environments** (5s timeout, 1,000 row cap) |
| **`CONFIRM`** (Default) | Allowed | Allowed | Allowed | **Required** | Day-to-day development |
| **`SANDBOX`** | Allowed | Uncommitted | Uncommitted | None | Dry-run testing with before/after diffs |
| **`ADMIN`** | Allowed | Allowed | Allowed | None | Advanced DBAs |

---

## ⏪ Long-Term Post-Commit Recovery Engine

Unlike standard database transactions that only protect uncommitted sessions, DB-Agent captures **pre-write row snapshots** before modifying data:

```text
User: "Update inactive customers to ACTIVE where created_at > '2025-01-01'"
AI:   Modifying 42 rows. Capturing pre-write snapshot...
      ✓ Change committed successfully (Change ID: CS-20260831-001)

... 10 days later ...

User: db-agent rollback CS-20260831-001 --db my_db
AI:   Checking concurrency conflicts... No conflicting writes detected.
      ✓ Restored 42 rows to their exact original state.
```

---

## ⚡ Performance Benchmarks (`db-agent benchmark`)

Reproducible benchmark metrics measured on standard hardware:

```text
Benchmark Category                   Latency / Metric        Rating
─────────────────────────────────────────────────────────────────────────────
Cold Schema Scan (6 tables)          12.4 ms                 ⚡ Ultra Fast (<50ms)
Cached Schema Load                   0.08 ms                 ⚡ Sub-Millisecond
Schema Cache Speedup                 155x faster             🚀 Verified
BFS Multi-Hop Join Routing (3 hops)  0.15 ms                 ⚡ Sub-Millisecond
Query Result Cache Hit               0.02 ms                 ⚡ Instant
Process Memory Footprint             28.4 MB                 🟢 Lightweight
```

Run benchmarks locally:
```bash
db-agent benchmark
```

---

## 🔌 Plugin & Extensibility Architecture

Extend DB-Agent with custom database connectors or AI providers via standard Python entry points:

```python
# my_connector.py
from dbagent.connectors.base import BaseConnector

class ClickHouseConnector(BaseConnector):
    # Implement test_connection, inspect_schema, execute_query
    pass
```

In your package `pyproject.toml`:
```toml
[project.entry-points."dbagent.connectors"]
clickhouse = "my_package:ClickHouseConnector"
```

---

## 📖 CLI Command Reference

| Command | Usage | Description |
| :--- | :--- | :--- |
| `demo` | `db-agent demo` | Instant zero-setup trial on sample e-commerce database. |
| `ask` | `db-agent ask "prompt" --db alias` | Translates natural language to SQL and executes. |
| `chat` | `db-agent chat --db alias` | Interactive AI copilot terminal REPL shell. |
| `connect` | `db-agent connect <url> --alias name`| Tests credentials and saves database profile. |
| `doctor` | `db-agent doctor --db alias` | Runs connectivity, schema, index, and latency health checks. |
| `benchmark`| `db-agent benchmark` | Runs reproducible latency and memory benchmark suite. |
| `examples` | `db-agent examples` | Displays categorized copy-paste prompt recipes. |
| `rollback` | `db-agent rollback <id>` | Safely reverts post-commit write changes. |
| `changes` | `db-agent changes` | Lists recorded write ChangeSets and recovery status. |
| `scan` | `db-agent scan --db alias -o doc.md`| Introspects full schema and exports Markdown data catalog. |
| `optimize` | `db-agent optimize "<sql>"` | Analyzes EXPLAIN query plan and suggests indexes. |
| `path` | `db-agent path table1 table2` | Finds shortest foreign-key join path between tables. |
| `define` | `db-agent define term "definition"` | Teaches business terminology to semantic dictionary. |
| `setup` | `db-agent setup` | 1-Click AI configuration wizard. |
| `use` | `db-agent use <provider>` | Switches active AI provider (`claude`, `openai`, `gemini`, `ollama`...). |

---

## 🔒 Privacy & Security First

- **Zero Credentials Sent to AI**: DB hostnames, usernames, passwords, and data rows **never** leave your machine. Only schema DDL is sent to the LLM.
- **100% Offline with Ollama**: Run completely air-gapped with local LLMs.
- **Sensitive Data Masking**: Automatic regex masking of emails (`a***@b.com`), passwords (`**********`), and cards.
- **Append-Only Audit Logs**: Records all queries and execution durations in `~/.dbagent/audit.jsonl`.

---

## 📄 License & Community

- **License**: [MIT License](LICENSE)
- **Author**: Santhosh Gupta
- **Documentation & Recipes**: [examples/](examples/)
- **Contributing**: [CONTRIBUTING.md](CONTRIBUTING.md)
- **Security Policy**: [SECURITY.md](SECURITY.md)
- **Privacy Policy**: [PRIVACY.md](PRIVACY.md)
