# dbcli — Database CLI for AI Agents

**Languages:** [English](./README.md) | [繁體中文](./README.zh-TW.md)

A unified database CLI tool that enables AI agents (Claude Code, Gemini, Copilot, Cursor) to safely query, discover, and operate on databases.

**Core Value:** AI agents can safely and intelligently access project databases through a single, permission-controlled CLI tool with sensitive data protection.

> **Security update:** `dbcli init` now writes only a small project binding stub into `./.dbcli/config.json`. The full connection configuration is stored under `~/.config/dbcli/projects/<project-id>/config.json`, so sensitive settings do not live inside the project workspace by default.

## Internationalization (i18n)

dbcli supports multiple languages via the `DBCLI_LANG` environment variable:

```bash
# English (default)
dbcli init

# Traditional Chinese
DBCLI_LANG=zh-TW dbcli init

# Or set in .env
export DBCLI_LANG=zh-TW
dbcli init
```

**Supported languages:**
- `en` — English (default)
- `zh-TW` — Traditional Chinese (Taiwan)

All messages, help text, error messages, and command output respond to the language setting automatically.


## Quick Start

### Installation

#### Global Installation (Recommended)

```bash
bun install -g @carllee1983/dbcli
# or: npm install -g @carllee1983/dbcli
```

**dbcli runs on Bun.** npm and npx are supported as distribution channels, but the
installed `dbcli` executable requires Bun 1.3.3+ on your `PATH` — install it first with
`curl -fsSL https://bun.sh/install | bash`. Only the `./agent-core` subpath export is
importable from a plain Node process.

#### Zero-Install (No Installation Needed)

```bash
bunx @carllee1983/dbcli init
bunx @carllee1983/dbcli query "SELECT * FROM users"
# or with npm: npx @carllee1983/dbcli init
```

#### Update

```bash
# Self-update (recommended)
dbcli upgrade

# Or via npm
npm update -g @carllee1983/dbcli
```

#### Development Installation

```bash
git clone https://github.com/CarlLee1983/dbcli.git
cd dbcli
bun install
bun run src/cli.ts -- --help
# or: bun run dev -- --help
```

When `dbcli` is not on your `PATH`, use `bun run src/cli.ts <subcommand> ...` (same as `bun run dev -- <subcommand> ...`).

### First Steps

```bash
# Initialize project with database connection
dbcli init

# Interactive shell (SQL + dbcli commands, tab completion)
dbcli shell

# List available tables
dbcli list

# View table structure
dbcli schema users

# Query data
dbcli query "SELECT * FROM users"

# Preview schema DDL (dry-run by default; add --execute to apply)
dbcli migrate create posts --column "id:serial:pk" --column "title:varchar(200):not-null"

# Generate AI agent skill
dbcli skill --install claude
```

### Agent first-look

```bash
dbcli inspect --for-agent
```

A single read-only command that returns a bounded JSON snapshot of the current
connection, permission level, blacklist size, schema cache freshness, available
saved-query intents, and the safest next commands to run. No host, no port, no
credentials — safe to log or pipe to an LLM.

### Diagnostic report

```bash
dbcli report --format json
```

Builds on `inspect` to also run curated read-only built-in `@diag/*` snippets
grouped into `health` / `capacity` / `perf` sections. Bounded by per-snippet
timeout and per-evidence row cap. Use `--format markdown` for human reading,
`--section health,capacity` to scope, or `--for-agent` for compact JSON.

### Guide

```bash
dbcli guide slow-query --format json
```

Deterministic next-command planner. Pick a goal (`slow-query`, `capacity`,
`health`, `index-usage`, `permissions`, `schema-overview`) and `dbcli guide`
emits an ordered plan that combines `dbcli inspect`, engine-appropriate
`@diag/*` snippets, and `dbcli queries suggest` / `dbcli doctor` follow-ups.
The planner is cache-first (no network); add `--probe` to refresh the
underlying inspect context. Use `--list` to see all goals,
`--format markdown` for human reading, or `--for-agent` for compact JSON.

### Interactive HTML Dashboards

```bash
# Open results in browser
dbcli query "SELECT * FROM orders" --ui

# Run a saved snippet with visualization metadata
dbcli q @analytics/revenue --ui

# Export query results as a standalone HTML file
dbcli export "SELECT * FROM users" --format html --output report.html
```

`dbcli` can render query results as fully interactive, standalone HTML dashboards. These reports are powered by React + Recharts and are zero-dependency — the entire application and data are inlined into a single HTML file.

- **`--ui` flag**: Automatically generates a temporary report and opens it in your default browser.
- **`visual:` block**: Snippet frontmatter can define KPIs and charts (Line, Bar, Area, Pie) to drive the dashboard.
- **Security**: Result sets are redacted by the blacklist before injection, and data is safely escaped for HTML.
- **Completeness warnings**: Truncation and security metadata are shown before KPIs, charts, and the raw table so incomplete or masked data is never presented as a complete result.

### Recovery & Guided Remediation

```bash
# Lookup recovery commands for a code
dbcli recovery --code CONN_REFUSED --format json

# Execute a failing command with recovery opt-in
dbcli query "SELECT 1" --recovery

# (v1.17.0+) Inspect or apply the last saved recovery plan
dbcli recover                       # View last plan (Markdown)
dbcli recover --apply               # Execute safe steps (readonly/dry-run)
dbcli recover --apply --allow-write=readonly-cmd  # Allow local writes
dbcli recover --apply --allow-write=write-cmd     # Allow database writes

# (v1.17.0+) Multi-turn recovery (for AI agents)
dbcli recover --next --after-step 1 --result '{"status":"ok"}'
```

Machine-readable error envelope with guided remediation. As of v1.16.0 every
first-party command accepts the `--recovery` flag. In v1.17.0, the `recover`
command was added to automate the execution of these plans.

- **Risk Gating:** `--apply` is safe-by-default, running only `readonly` and
  `dry-run` steps. Elevated tiers require `--allow-write`.
- **Verification:** After a successful `--apply`, dbcli automatically runs a
  verification step to confirm the fix actually worked.
- **Multi-turn Protocol:** The `--next` flag allows agents to advance recovery
  one step at a time, providing the result of the previous step to enable
  deterministic branching.

`dbcli inspect --require-schema-cache` throws `SCHEMA_CACHE_MISSING` when the
active SQL connection has no usable schema cache; combine with `--recovery`
to receive the structured envelope.

For write commands (`insert` / `update` / `delete`), `BLACKLIST_COLUMN_WRITE`
and `PERMISSION_DENIED` envelopes lead with a `risk: 'dry-run'` step
that suggests previewing the SQL with `--dry-run` before re-attempting.

### MongoDB Atlas / SRV Connections

MongoDB connections are supported via both standard `mongodb://` URIs and Atlas-style `mongodb+srv://` URIs.

```bash
# Atlas / SRV connection
dbcli init --system mongodb --conn-name atlas --uri "mongodb+srv://user:pass@cluster.example.mongodb.net/mydb"

# List collections in the configured MongoDB database
dbcli list --use atlas

# Query a collection with JSON filter or pipeline
dbcli query '{"status":"active"}' --collection users --use atlas
```

For MongoDB, `list` and `query` operate on the database configured for the connection, and `query` requires `--collection <name>`.

For a command-by-command support matrix across PostgreSQL, MySQL, MariaDB, MongoDB, Redis, and Elasticsearch, see [docs/feature-matrix.md](./docs/feature-matrix.md).

### Redis & Elasticsearch Support

dbcli extends its unified interface to Redis and Elasticsearch, providing consistent discovery and querying.

#### Redis

```bash
# Connect to Redis
dbcli init --system redis --host localhost --port 6379

# List keys (uses SCAN)
dbcli list

# Inspect a key (type, TTL, size, sample)
dbcli schema my-key

# Run Redis commands (whitelisted)
dbcli query "GET my-key"
dbcli query "HGETALL user:1"
```

#### Elasticsearch

```bash
# Connect to Elasticsearch
dbcli init --system elasticsearch --host localhost --port 9200

# List indices and document counts
dbcli list

# Show mapping/structure of an index
dbcli schema my-index

# Query using Lucene or DSL JSON
dbcli query "status:active" --index my-index
dbcli query '{"query": {"match_all": {}}}' --index my-index
```

---

## Multi-connection Support (v2)

dbcli supports multiple named database connections within a single project. This is useful for managing different environments (development, staging, production) or multiple databases.

The project `.dbcli` directory now acts as a binding + cache layer. The actual connection config is stored in `~/.config/dbcli/projects/<project-id>/`, which keeps sensitive settings out of the workspace by default.

### Initializing Named Connections

To create a named connection, use the `--conn-name` option during `init`. You can also specify a custom `.env` file for that connection.

```bash
# Add a staging connection using .env.staging
dbcli init --conn-name staging --env-file .env.staging

# Add a production connection with environment variable references
dbcli init --conn-name prod --env-file .env.production --use-env-refs
```

For connections shared across projects, use the user-global scope. It stores a v2 registry at `~/.config/dbcli/config.json` and leaves the current project's `.dbcli` binding untouched:

```bash
dbcli --global init --conn-name shared --system postgresql --host db.example.com \
  --port 5432 --user app --password '<secret>' --name appdb \
  --skip-test --no-interactive --force
dbcli --global use --list --format json
dbcli --global query "SELECT 1"
```

Root-level `--global` must precede the command. Without it, commands continue to use the project configuration.

### Managing Connections

Use the `dbcli use` command to switch between connections or list them.

```bash
# List all connections (* marks the current default)
dbcli use --list

# Switch the default connection to 'staging'
dbcli use staging

# Show the current default connection
dbcli use

# Remove a connection
dbcli init --remove staging

# Rename a connection
dbcli init --rename staging:production
```

### Using a Specific Connection Temporarily

You can use the `--use <name>` global flag, the supported command-level form, or `DBCLI_CONNECTION` to execute against a specific connection without changing the default. Selection precedence is explicit `--use`, then `DBCLI_CONNECTION`, then the configured default. A selector is rejected for legacy v1 single-connection configuration instead of being silently ignored.

```bash
# Query the production database once
dbcli query "SELECT count(*) FROM users" --use prod

# Check staging table health
dbcli check users --use staging

# Select one connection for this process
DBCLI_CONNECTION=prod dbcli query "SELECT count(*) FROM users"
```

`query`, `schema`, `list`, `export`, and `check` accept the command-level `--use` form shown above. Other commands use the global form before the subcommand.

For read-only comparisons, an explicit comma-separated `--use` fans one query out to multiple named connections:

```bash
dbcli query --use primary,staging "SELECT count(*) FROM users" --format json
```

SQL fan-out permits `SELECT`, `SHOW`, `DESCRIBE`, and `EXPLAIN`; MongoDB permits filters and read-only pipelines; Elasticsearch permits search. Redis, writes, `--recovery`, `--ui`, CSV, and HTML are rejected. Connections run independently: JSON returns an ordered `results` array, table output labels each section, and one failure does not cancel the others. Exit codes are `0` for all success, `2` for mixed outcomes, and `1` for all failures or a preflight rejection. `DBCLI_CONNECTION` always names one literal connection and never enables fan-out.

---


#### `dbcli init`

Initialize a new dbcli project with database connection configuration.

**Usage:**
```bash
dbcli init [OPTIONS]
```

**Options (Basic):**
- `--system <type>` — Database system: `postgresql`, `mysql`, `mariadb`, `mongodb`
- `--host <host>` — Database host
- `--port <port>` — Database port
- `--user <user>` — Database user
- `--password <pass>` — Database password
- `--name <db>` — Database name
- `--permission <level>` — Permission level: `query-only`, `read-write`, `data-admin`, `admin`
- **MongoDB only:** `--uri <uri>` — full connection URI (`mongodb://…` or `mongodb+srv://…`); `--auth-source <db>` — auth database (default `admin` when using user/password)
- `--use-env-refs` — Store environment variable references instead of actual values in config
- `--skip-test` — Skip connection test
- `--no-interactive` — Non-interactive mode (requires all options)
- `--force` — Overwrite existing config without confirmation

**Options (Multi-connection v2):**
- `--conn-name <name>` — Create a named connection (e.g., `staging`, `prod`)
- `--env-file <path>` — Load credentials from a specific `.env` file for this connection
- `--remove <name>` — Remove a named connection from the config
- `--rename <old:new>` — Rename an existing connection (format: `old:new`)

**Behavior:**
- Reads `.env` file if present (auto-fills DATABASE_URL, DB_* variables)
- Prompts for missing values (host, port, user, password, database name, permission level)
- Creates a project binding stub in `.dbcli/config.json` and stores the full config under `~/.config/dbcli/projects/<project-id>/`
- Tests database connection before saving

**Examples:**
```bash
# Interactive initialization
dbcli init

# Multi-connection setup
dbcli init --conn-name staging --env-file .env.staging
dbcli init --conn-name prod --env-file .env.production --use-env-refs

# Store env var references (non-interactive)
dbcli init --use-env-refs --system mysql \
  --env-host DB_HOST --env-port DB_PORT \
  --env-user DB_USER --env-password DB_PASSWORD \
  --env-database DB_DATABASE \
  --no-interactive
```

---

#### `dbcli use` (Requires v2 config)

Manage or switch the default database connection in multi-connection projects.

**Usage:**
```bash
dbcli use [connection-name] [OPTIONS]
```

**Options:**
- `--list` — List all connections and show the current default

**Examples:**
```bash
# Show current default connection
dbcli use

# Switch default connection to 'prod'
dbcli use prod

# List all connections
dbcli use --list
```

---

> **`--use-env-refs`:** When enabled, the config stores environment variable names (e.g., `{"$env": "DB_HOST"}`) instead of actual values. This avoids writing sensitive credentials into the config file, making it suitable for multi-environment deployments and CI/CD pipelines. At connection time, dbcli automatically reads the actual values from the referenced environment variables.

> **Storage model:** The project `.dbcli` directory is now a binding + cache layer, not the canonical home for secrets. If you inspect `./.dbcli/config.json`, you should only see the binding metadata; the full config lives in the home storage path shown above.

---

#### `dbcli list`

List all tables in the connected database.

**Usage:**
```bash
dbcli list [OPTIONS]
```

**Options:**
- `--format json` — Output as JSON instead of ASCII table

**Examples:**
```bash
# Table format (human-readable)
dbcli list

# JSON format (for AI parsing)
dbcli list --format json

# Pipe to tools
dbcli list --format json | jq '.data[].name'
```

---

#### `dbcli schema [table]`

Show table structure (columns, types, constraints, foreign keys).

**Usage:**
```bash
dbcli schema [table]
dbcli schema                 # Scan entire database and update .dbcli
dbcli schema users           # Show structure of 'users' table
```

**Options:**
- `--format json` — Output as JSON
- `--refresh` — Detect and update schema changes incrementally (requires --force for approval)
- `--reset` — Clear all existing schema data and re-fetch from database (useful after switching DB connections)
- `--force` — Skip confirmation for schema refresh/overwrite/reset

**Examples:**
```bash
# Show users table structure
dbcli schema users

# JSON output with full metadata
dbcli schema users --format json

# Update schema with new tables (incremental)
dbcli schema --refresh --force

# Clear and re-fetch all schema (after switching DB)
dbcli schema --reset --force

# Scan entire database
dbcli schema
```

---

#### `dbcli query [query]`

Execute a SQL statement, MongoDB filter/pipeline, allow-listed Redis command, or Elasticsearch DSL/Lucene query and return results.

**Usage:**
```bash
dbcli query "SELECT * FROM users"
dbcli query --query-file ./queries/active-users.sql
```

**Options:**
- `--format json|table|csv|html` — Output format (default: table)
- `--ui` — Render HTML to a temporary file and open it in the system browser
- `--limit <number>` — Cap rows (overrides the automatic limit in query-only mode)
- `--no-limit` — Disable the automatic 1000-row cap in query-only mode
- `-f, --query-file <path>` — Read a UTF-8 query from a file; use `-` for piped stdin
- `--fields <list>` — Include `a,b` or exclude `-a,-b` fields from SQL/MongoDB results
- `--truncate <number>` — Set the table cell limit in Unicode code points (default: 120)
- `--no-truncate` — Show complete table cells

**Behavior:**
- Enforces permission-based restrictions (Query-only mode blocks INSERT/UPDATE/DELETE)
- Requires exactly one query source: positional text, `--query-file <path>`, or piped stdin through `--query-file -`
- Auto-limits results to 1000 rows in Query-only mode, unless `--no-limit` or `--limit` applies
- Uses a one-row lookahead for dbcli-owned limits. Truncated tables say so in the footer, JSON returns `metadata.truncated` and `metadata.limit_applied`, and CSV appends a truncation comment
- Applies `--fields` after SQL execution and pushes it into MongoDB find/pipeline operations. Blacklist masking remains authoritative
- Truncates table cells only; JSON and CSV rows remain lossless. Explicit truncation flags with JSON, CSV, HTML, or `--ui` are rejected
- To write CSV/JSON to a file, use shell redirection or the `export` command

**Examples:**
```bash
# Table output (human-readable)
dbcli query "SELECT * FROM users"

# JSON (for AI/programmatic parsing)
dbcli query "SELECT * FROM users" --format json

# CSV to stdout (redirect to a file)
dbcli query "SELECT * FROM users" --format csv > users.csv

# Pipe to other tools
dbcli query "SELECT * FROM products" --format json | jq '.data[] | .name'

# Large result sets (paginate with LIMIT/OFFSET)
dbcli query "SELECT * FROM users LIMIT 100 OFFSET 0"

# Multiline SQL from stdin
dbcli query --query-file - <<'SQL'
SELECT id, email
FROM users
WHERE status = 'active';
SQL

# Include or exclude fields (use = when the value starts with a hyphen)
dbcli query "SELECT * FROM users" --fields id,email,status
dbcli query "SELECT * FROM users" --fields=-password_hash,-raw_payload
```

---

#### `dbcli insert [table]` (Requires Read-Write or Admin permission)

Insert data into table.

**Usage:**
```bash
dbcli insert users --data '{"name": "Alice", "email": "alice@example.com"}'
```

**Options:**
- `--data JSON` — Row data as JSON object (REQUIRED)
- `--dry-run` — Show SQL without executing
- `--force` — Skip confirmation

**Behavior:**
- Validates JSON format
- Generates parameterized SQL (prevents SQL injection)
- Shows confirmation prompt before inserting (unless --force used)

**Examples:**
```bash
# Insert single row
dbcli insert users --data '{"name": "Bob", "email": "bob@example.com"}'

# Preview SQL without executing
dbcli insert users --data '{"name": "Charlie"}' --dry-run

# Skip confirmation
dbcli insert users --data '{"name": "Diana"}' --force
```

---

#### `dbcli update [table]` (Requires Read-Write or Admin permission)

Update existing rows.

**Usage:**
```bash
dbcli update users --where "id=1" --set '{"name": "Alice Updated"}'
```

**Options:**
- `--where condition` — WHERE clause (REQUIRED, e.g., "id=1 AND status='active'")
- `--set JSON` — Updated columns as JSON object (REQUIRED)
- `--dry-run` — Show SQL without executing
- `--force` — Skip confirmation

**Examples:**
```bash
# Update single row
dbcli update users --where "id=1" --set '{"name": "Alice"}'

# Update multiple rows
dbcli update users --where "status='inactive'" --set '{"status":"active"}'

# Preview SQL
dbcli update users --where "id=1" --set '{"name": "Bob"}' --dry-run

# Skip confirmation
dbcli update users --where "id=2" --set '{"email": "new@example.com"}' --force
```

---

#### `dbcli delete [table]` (Requires Data-Admin or Admin permission)

Delete rows (blocked for query-only and read-write; requires elevated DML permission).

**Usage:**
```bash
dbcli delete users --where "id=1" --force
```

**Options:**
- `--where condition` — WHERE clause (REQUIRED)
- `--dry-run` — Show SQL without executing
- `--force` — Required to actually delete (safety guard)

**Examples:**
```bash
# Delete single row (requires --force)
dbcli delete users --where "id=1" --force

# Preview deletion
dbcli delete products --where "status='deprecated'" --dry-run

# Delete multiple rows
dbcli delete orders --where "created_at < '2020-01-01'" --force
```

---

#### `dbcli export "SQL"`

Export query results to file.

**Usage:**
```bash
dbcli export "SELECT * FROM users" --format json --output users.json
```

**Options:**
- `--format json|csv` — Output format
- `--output file` — Write to file (default: stdout for piping)
- `--limit <number>` — Deliberately accept a bounded export
- `--no-limit` — Export the complete result

**Behavior:**
- Query-only mode still applies its automatic 1000-row limit, but reaching it fails closed with exit code `1` and writes no partial file
- Re-run with `--no-limit` for the complete export or `--limit N` to accept a cap explicitly
- Generates RFC 4180 compliant CSV
- Creates well-formed JSON arrays

**Examples:**
```bash
# Export to JSON
dbcli export "SELECT * FROM users" --format json --output users.json

# Export to CSV
dbcli export "SELECT * FROM orders" --format csv --output orders.csv

# Pipe compressed export
dbcli export "SELECT * FROM products" --format csv | gzip > products.csv.gz

# Combine with query tools
dbcli export "SELECT * FROM users WHERE active=true" --format json | jq '.data | length'
```

---

#### `dbcli skill`

Generate or install AI agent skill documentation.

**Usage:**
```bash
dbcli skill                           # Output skill to stdout
dbcli skill --output SKILL.md         # Write to file
dbcli skill --install claude          # Install to Claude Code config
dbcli skill --install gemini          # Install to Gemini CLI (being phased out)
dbcli skill --install antigravity     # Install to Antigravity CLI (Gemini CLI's successor)
dbcli skill --install copilot         # Install to GitHub Copilot
dbcli skill --install cursor          # Install to Cursor IDE
dbcli skill --install codex           # Install to Codex skills
dbcli skill --install windsurf        # Install to Windsurf (.windsurfrules)
```

**Behavior:**
- Ships canonical **`assets/SKILL.md`** + **`assets/reference.md`** (single source of truth: concise skill + long command reference)
- Prints the skill to **stdout**, writes it with **`--output`**, or copies it to a **platform-specific path** with **`--install`**
- Actual database access is still enforced by your `.dbcli` permission level and blacklist — the skill text describes the full CLI surface

**Examples:**
```bash
# Generate skill for Claude Code
dbcli skill --install claude

# Generate skill manually for documentation
dbcli skill > ./docs/SKILL.md

# View generated skill (stdout)
dbcli skill

# Install for all platforms
dbcli skill --install claude && \
dbcli skill --install gemini && \
dbcli skill --install antigravity && \
dbcli skill --install copilot && \
dbcli skill --install cursor && \
dbcli skill --install codex
```

---

#### `dbcli blacklist`

Manage the data access blacklist to block AI agents from accessing sensitive tables or columns.

**Usage:**
```bash
dbcli blacklist list
dbcli blacklist table add <table>
dbcli blacklist table remove <table>
dbcli blacklist column add <table>.<column>
dbcli blacklist column remove <table>.<column>
```

**Subcommands:**

| Subcommand | Description |
|------------|-------------|
| `dbcli blacklist list` | Show current blacklist (tables and columns) |
| `dbcli blacklist table add <table>` | Add table to blacklist (blocks all operations) |
| `dbcli blacklist table remove <table>` | Remove table from blacklist |
| `dbcli blacklist column add <table>.<column>` | Add column to blacklist (omitted from SELECT results) |
| `dbcli blacklist column remove <table>.<column>` | Remove column from blacklist |

**Behavior:**
- Table blacklist blocks all operations on that table (query, insert, update, delete)
- Column blacklist silently omits columns from SELECT results and shows a security notification
- Blacklist rules are stored in `.dbcli` and apply to all permission levels
- Override for admin use via `DBCLI_OVERRIDE_BLACKLIST=true` environment variable

**Examples:**
```bash
# View current blacklist
dbcli blacklist list

# Block all access to sensitive tables
dbcli blacklist table add audit_logs
dbcli blacklist table add secrets_vault

# Hide sensitive columns from query results
dbcli blacklist column add users.password_hash
dbcli blacklist column add users.ssn

# Remove a table from blacklist
dbcli blacklist table remove audit_logs

# Remove a column from blacklist
dbcli blacklist column remove users.ssn

# Override blacklist (admin use only)
DBCLI_OVERRIDE_BLACKLIST=true dbcli query "SELECT * FROM secrets_vault"
```

---

#### `dbcli check`

Run data-quality and health checks on tables.

**Usage:**
```bash
dbcli check [table] [OPTIONS]
```

**Options:**
- `--all` — Check every table (skips huge tables unless `--include-large`)
- `--include-large` — Include huge tables in `--all` scan
- `--checks <types>` — Comma-separated checks: `nulls`, `duplicates`, `orphans`, `emptyStrings`, `rowCount`, `size`
- `--sample <number>` — Sample size for large tables (default: 10000)
- `--format json|table` — Output format (default: json)

**Examples:**
```bash
# Check users table
dbcli check users

# Run specific checks only
dbcli check orders --checks nulls,orphans --format table
# Scan all tables
dbcli check --all

# Table view + all-tables with selected checks
dbcli check orders --format table
dbcli check --all --checks nulls,duplicates --format json
```

---

#### `dbcli diff`

Save a schema snapshot, compare the live database to a previous snapshot, or compare an ORM definition with the local SQL schema cache. ORM drift is cache-only: it does not connect, refresh the cache, or execute proposals.

**Usage:**
```bash
dbcli diff --snapshot ./schema-before.json
dbcli diff --against ./schema-before.json
dbcli diff --against ./schema-before.json --format table
dbcli diff --against-orm prisma/schema.prisma --format json
dbcli diff --against-orm drizzle/meta/0001_snapshot.json --orm-format drizzle --format table
dbcli diff --against-orm schema.sql --orm-format typeorm --format table
```

**Options:**
- `--snapshot <path>` — Write the current schema to a JSON file
- `--against <path>` — Diff live schema vs. the saved snapshot
- `--against-orm <path>` — Compare Prisma, Drizzle snapshot, TypeORM/Sequelize DDL, raw DDL, or normalized JSON with the cached SQL schema
- `--orm-format prisma|drizzle|typeorm|sequelize|ddl|json` — Override ORM input detection
- `--format json|table|markdown` — Output format (default: `json`)
- `--config <path>` — Config path (default: `.dbcli`)

---

#### `dbcli snapshot`

Capture a **result fingerprint** of a query (distinct from `diff`, which snapshots *schema*): row count plus per-column aggregates (null/distinct counts, min/max/sum) and an order-independent checksum. Blacklisted columns are masked at the source, so the snapshot is safe to store. Use it as a baseline for `dbcli assert --against`. SQL engines only.

**Usage:**
```bash
dbcli snapshot "SELECT * FROM orders WHERE created_at >= '2026-05-01'"   # → .dbcli/snapshots/snap-<timestamp>.json
dbcli snapshot @analytics/daily-revenue --out base.json
dbcli snapshot "SELECT status, count(*) FROM orders GROUP BY status" --stdout
```

**Options:**
- `--out <path>` — Output path (default: `.dbcli/snapshots/snap-<timestamp>.json`)
- `--rows` — Also store the full (blacklist-masked) rows
- `--stdout` — Print snapshot JSON to stdout instead of writing a file
- `--format json|table` — Output format for `--stdout` (default: `json`)
- `--no-limit` — Disable the automatic query-only LIMIT

---

#### `dbcli assert`

Assert an **invariant** on a query result. Exits `1` on failure (composes in scripts/CI) unless `--no-fail`. SQL engines only.

**Usage:**
```bash
dbcli assert "SELECT count(*) FROM orders" --expect "value > 0"
dbcli assert "SELECT * FROM orders WHERE total < 0" --expect "rows == 0"
dbcli assert "SELECT email FROM users" --expect "col:email not null"
dbcli assert "SELECT sum(amount) FROM ledger_a" --vs "SELECT sum(amount) FROM ledger_b" --compare value
dbcli assert "SELECT * FROM orders" --against base.json --tolerance 0.01
```

**Options:**
- `--expect <condition>` — `rows > 0`, `value == 5000`, `col:email not null`, `col:id unique`, `col:amount between 0 and 100`, `col:age >= 18`
- `--vs <query>` — Reconcile against a second query
- `--compare rows|value` — Comparison mode for `--vs` (default: `value`)
- `--against <path>` — Compare the current result fingerprint to a saved snapshot
- `--tolerance <pct>` — Allowed relative drift for `--against` (e.g. `0.01`; default: `0` = exact checksum match)
- `--no-fail` — Always exit 0; report pass/fail in output only
- `--format json|table` — Output format (default: `json`)

---

#### `dbcli proxy` (v1.26)

Local-development **observability proxy** for MySQL, MariaDB, and PostgreSQL. Point an existing app at the proxy port; dbcli relays all TCP frames to the real database and appends one JSONL event per query to `.dbcli/proxy/events.jsonl`. Observe-only — no rewrite or blocking. Not a production gateway.

**Subcommands:** `mysql` · `mariadb` · `postgresql`

```bash
dbcli proxy mysql      --listen 127.0.0.1:3307 --target 127.0.0.1:3306
dbcli proxy postgresql --listen 127.0.0.1:5434 --target 127.0.0.1:5432
dbcli proxy mysql      --slow-ms 500 --redact literals
dbcli proxy mariadb    --events ./logs/proxy.jsonl
dbcli proxy analyze --events ./logs/proxy.jsonl --format markdown  # QueryLens report
```

**Options:**
- `--listen <addr:port>` — Proxy listen address
- `--target <addr:port>` — Real database address (inferred from config / `--use` if omitted)
- `--events <path>` — JSONL event log (default: `.dbcli/proxy/events.jsonl`)
- `--slow-ms <ms>` — Flag events slower than this threshold as `slow: true` (default: `1000`)
- `--redact none|literals` — Strip SQL literal values from event records (default: `none`)
- `--format text|json` — Startup output format (default: `text`)

**QueryLens:** `dbcli proxy analyze --format markdown` reads the event log offline and produces a shareable Markdown report. It redacts SQL and error-message literals in memory before analysis; use `--redact literals` while capturing as well to protect the log on disk.

---

#### `dbcli status`

Show non-sensitive configuration summary (permission level, DB system, blacklist counts, config metadata version). Does not print connection credentials — intended for AI agents.

**Usage:**
```bash
dbcli status
dbcli status --format text
dbcli status --format json
```

**Options:**
- `--format text|json` — Output format (default: `json`)

**Note:** This command reads the default project config path `.dbcli` (not the global `--config` flag).

---

#### `dbcli doctor`

Run diagnostic checks on environment, configuration, connection, and data.

```bash
dbcli doctor                    # Colored text output
dbcli doctor --format json      # JSON output for AI agents
```

**Checks:**
- **Environment:** Bun version compatibility, dbcli version (compares with npm registry)
- **Configuration:** Config file exists/valid, permission level, blacklist completeness
- **Connection & Data:** Database connectivity, schema cache freshness (> 7 days warning), large table warnings (> 1M rows)
- **MongoDB SRV diagnostics:** For `mongodb+srv://` connections, `doctor` reports whether the current execution environment can resolve SRV records directly or only through the DNS-over-HTTPS fallback used by `dbcli`

**Options:** `--format <text|json>`
**Exit code:** 0 = all pass or warnings only, 1 = errors found

---

#### `dbcli completion [shell]`

Generate shell completion scripts for tab auto-complete.

```bash
dbcli completion bash            # Output bash completion to stdout
dbcli completion zsh             # Output zsh completion to stdout
dbcli completion fish            # Output fish completion to stdout
dbcli completion --install       # Auto-detect shell and install to rc file
dbcli completion --install zsh   # Install for specific shell
```

**Supported shells:** bash, zsh, fish

Installed completions cover **nested subcommands** — for example `dbcli queries list --<TAB>`, `dbcli migrate add-column --<TAB>`, and `dbcli verify safe-backfill --<TAB>`.

Inside `dbcli shell`, command completion follows the current command surface, so newly added commands (`q`, `queries`, `inspect`, `verify`, `proxy`, `snapshot`, …) complete and dispatch automatically.

`--install` is **marker-managed**: it writes a single block to your shell rc file and re-running it replaces that block rather than duplicating it.

---

#### `dbcli upgrade`

Check for updates and self-upgrade dbcli.

```bash
dbcli upgrade                   # Check and upgrade if newer version available
dbcli upgrade --check           # Only check, do not upgrade
```

**Options:** `--check` — check only, don't install

**Background checks (stderr, skipped when `--quiet` or for `upgrade` / `skill`):**
- **CLI version:** dbcli checks the npm registry (cached, about once per 24 hours). If a newer package exists, a one-line hint prints after normal command output.
- **Installed skills:** If you used `dbcli skill --install <platform>`, dbcli compares each installed primary skill file (`SKILL.md` or `dbcli.mdc`) to the bundled `assets/SKILL.md`. When they differ, a short reminder lists which platforms to re-install (`dbcli skill --install <platform>`). Run `dbcli upgrade` to see the same skill status together with version info. Re-installing also refreshes `reference.md` next to the skill.

#### `dbcli shell`

Interactive database shell with SQL execution, auto-completion, and syntax highlighting.

**Usage:**
```bash
dbcli shell          # Interactive mode (SQL + dbcli commands)
dbcli shell --sql    # SQL-only mode
```

**Inside the shell:**
- Type SQL statements ending with `;` to execute queries
- Type dbcli commands without the `dbcli` prefix (e.g., `schema users`, `list`)
- Press Tab for context-aware auto-completion (SQL keywords, table/column names)
- Use `.help` for meta commands (`.quit`, `.clear`, `.format`, `.history`, `.timing`)
- Multi-line SQL: input accumulates until `;` is found
- History persists across sessions in `~/.dbcli_history`

**Permission:** Inherits from config. SQL and commands are fully permission/blacklist enforced.

#### `dbcli migrate`

Schema DDL operations. **All commands default to dry-run** — use `--execute` to actually run the SQL.

**Usage:**
```bash
# Create table
dbcli migrate create posts \
  --column "id:serial:pk" \
  --column "title:varchar(200):not-null" \
  --column "body:text" \
  --column "created_at:timestamp:default=now()"

# Execute (actually run the SQL)
dbcli migrate create posts --column "id:serial:pk" --execute

# Drop table (destructive — requires --execute --force)
dbcli migrate drop posts --execute --force

# Column operations
dbcli migrate add-column users bio text --nullable --execute
dbcli migrate alter-column users name --type "varchar(200)" --execute
dbcli migrate alter-column users email --rename user_email --execute
dbcli migrate drop-column users temp_field --execute --force

# Index operations
dbcli migrate add-index users --columns email --unique --execute
dbcli migrate drop-index idx_users_email --table users --execute --force

# Constraint operations
dbcli migrate add-constraint orders --fk user_id --references users.id --on-delete cascade --execute
dbcli migrate add-constraint users --unique email --execute
dbcli migrate add-constraint users --check "age >= 0" --execute
dbcli migrate drop-constraint orders fk_orders_user_id --execute --force

# Enum (PostgreSQL only)
dbcli migrate add-enum status active inactive suspended --execute
dbcli migrate alter-enum status --add-value archived --execute
dbcli migrate drop-enum status --execute --force
```

**Column spec format:** `name:type[:modifier...]` — Modifiers: `pk`, `not-null`, `unique`, `auto-increment`, `default=<value>`, `references=<table>.<column>`

**Options (all subcommands):** `--execute` (run SQL), `--force` (skip confirmation for DROP), `--config <path>`
**Permission:** admin only

---

## Query Risk Planning

Use `lint` for read-only static advice about SQL anti-patterns and optional rewrite drafts. It accepts inline SQL, saved queries, files, globs, and bulk input; it never connects or applies a rewrite:

```bash
dbcli lint "SELECT * FROM users WHERE LOWER(email) = 'a@example.com'" --format json
```

Use `plan` to inspect SQL safety before execution. It reads local dbcli config, permissions, blacklist rules, and cached schema metadata only; it does not connect to the database.

```bash
dbcli plan "UPDATE users SET status='inactive'" --format json
```

Decisions are:

- `ALLOW` — no obvious risk was detected.
- `WARN` — inspect warnings before executing.
- `BLOCK` — unsafe, unsupported, or violates configured safety constraints.

Text output is concise for humans:

```text
Decision: BLOCK
Operation: UPDATE
Target tables: users

Risk factors:
- UPDATE statement has no WHERE clause.

Recommendations:
- Add a WHERE clause.
- Use --dry-run on the actual write command.
```

JSON output includes `suggestedCommands` for agents:

```bash
dbcli plan "SELECT id FROM users WHERE id = 1 LIMIT 1" --format json
```

---

## Global Options

All commands support these global options:

| Flag | Description |
|------|-------------|
| `--config <path>` | Path to .dbcli config file (default: `.dbcli`) |
| `--global` | Use the user-global registry at `~/.config/dbcli/config.json` |
| `--use <connection>` | Use a named v2 connection for this invocation only (does not change the default) |
| `-v, --verbose` | Increase verbosity (`-v` verbose, `-vv` debug) |
| `-q, --quiet` | Suppress non-essential output |
| `--no-color` | Disable colored output (respects `NO_COLOR` env var) |

---

## Internals & Strategy

### Schema Update Strategy

dbcli maintains a schema snapshot in your `.dbcli` config file. This allows AI agents to understand the database structure without constant network overhead. Understanding when this cache updates is key:

1.  **Manual Updates:**
    *   `dbcli schema`: Performs a full scan of the database.
    *   `dbcli schema --refresh`: Incremental update. Detects changes and updates only the affected tables.
    *   `dbcli schema --reset`: Clears the cache and re-fetches everything.
2.  **Automatic Updates (DDL):**
    *   When you execute DDL through `dbcli migrate` (e.g., `add-column`), the CLI automatically re-scans the affected table and updates the `.dbcli` snapshot after successful execution.
3.  **Real-time Validation (Non-cached):**
    *   Commands like `insert`, `update`, `delete`, and `check` fetch the latest schema from the database immediately before execution to ensure data integrity, but they **do not** update the long-term snapshot in `.dbcli`.

> **Note:** If you change the database schema using external tools (like DBeaver or migration scripts), you **must** run `dbcli schema --refresh` to sync the snapshot so AI agents can see the changes.

### How `dbcli migrate` Works

The `migrate` command follows a strict safety pipeline to prevent accidental database corruption:

1.  **Permission Check:** Verifies the user has `admin` privileges. DDL is blocked for all other levels.
2.  **Blacklist Check:** Ensures the operation isn't targeting a table restricted in the security blacklist.
3.  **Dialect-Specific Generation:** The `DDLGenerator` translates your request into the correct SQL for your system:
    *   **PostgreSQL:** Uses `SERIAL`, native `ENUM` types, and double-quoted identifiers.
    *   **MySQL/MariaDB:** Uses `AUTO_INCREMENT`, inline `ENUM` definitions, and backticked identifiers.
4.  **Dry-run (Default):** All commands output the generated SQL for review without executing it.
5.  **Execution & Confirmation:**
    *   Requires the `--execute` flag to run.
    *   Destructive operations (like `drop`) require both `--execute` and `--force`.
6.  **Snapshot Sync:** After successful execution, it automatically triggers a schema refresh for the modified table to keep your `.dbcli` file up to date.

---

## Permission Model

dbcli implements a coarse-grained permission system with three levels. Permission level is set during `dbcli init` and stored in `.dbcli` config file. The blacklist system works alongside permissions to provide fine-grained protection for sensitive tables and columns (see [Data Access Control](#data-access-control)).

### Permission Levels

| Level | Allowed Commands | Blocked Commands | Use Case |
|-------|------------------|------------------|----------|
| **Query-only** | `init`, `list`, `schema`, `query`, `export` (limited to 1000 rows) | `insert`, `update`, `delete`, `migrate` | Read-only AI agents, data analysts, reporting |
| **Read-Write** | + `insert`, `update` | `delete`, `migrate` | Application developers, content managers |
| **Data-Admin** | + `delete` | `migrate` | Full DML access, no DDL |
| **Admin** | All commands including `migrate` (DDL) | — | Database administrators, schema modifications |

### Configuration

Permission level is set during initialization:

```bash
dbcli init
# Prompts: permission level (query-only / read-write / data-admin / admin)
# Stored in project .dbcli/config.json as: "permission": "query-only"
```

### Permission-Based Examples

#### Query-Only Mode (AI Agent)
```bash
# Allowed: Read operations
dbcli query "SELECT * FROM users"
dbcli schema users
dbcli export "SELECT * FROM orders" --format json

# Blocked: Write operations
dbcli insert users --data '{...}'  # ERROR: Permission denied
dbcli delete users --where "id=1"  # ERROR: Permission denied
```

#### Read-Write Mode (Application Developer)
```bash
# Allowed: Read + write
dbcli query "SELECT * FROM users"
dbcli insert users --data '{"name": "Alice"}'
dbcli update users --where "id=1" --set '{"name": "Bob"}'

# Blocked: Delete (requires data-admin or admin)
dbcli delete users --where "id=1"  # ERROR: Permission denied (read-write cannot DELETE)
```

#### Admin Mode (Database Administrator)
```bash
# Allowed: Everything including DDL
dbcli query "SELECT * FROM users"
dbcli insert users --data '{"name": "Eve"}'
dbcli update users --where "id=1" --set '{"status": "active"}'
dbcli delete users --where "id=1" --force  # Data-Admin+ can delete
dbcli migrate create posts --column "id:serial:pk" --execute  # Admin only
```

### Best Practices

- **AI Agents:** Use Query-only for read-only scenarios; prevents accidental data loss
- **Applications:** Use Read-Write for normal CRUD operations; prevents DROP TABLE accidents
- **Maintenance:** Use Admin only for schema changes, bulk deletes, or emergency operations
- **Principle of Least Privilege:** Assign minimum permission level needed for each use case

---

## Data Access Control

dbcli provides a blacklist system that works alongside the permission model to prevent AI agents from accessing sensitive tables or columns, regardless of their permission level.

### Table-Level Blacklist

Blocking a table prevents all operations on it — queries, inserts, updates, and deletes are all refused with a clear error message.

```bash
# Block a table
dbcli blacklist table add secrets_vault

# Attempting access is blocked at all permission levels
dbcli query "SELECT * FROM secrets_vault"
# ERROR: Table 'secrets_vault' is blacklisted
```

### Column-Level Blacklist

Blacklisted columns are silently omitted from SELECT results. A security notification is shown in the output so the agent is aware that the result set has been filtered.

```bash
# Blacklist sensitive columns
dbcli blacklist column add users.password_hash
dbcli blacklist column add users.ssn

# Query returns all other columns; notification shown
dbcli query "SELECT * FROM users"
# [Security] Columns omitted by blacklist: password_hash, ssn
```

### Security Notifications

Whenever a blacklist rule filters query output, dbcli appends a notification line to the result. This ensures AI agents do not silently operate on incomplete data without awareness.

### Override via Environment Variable

Administrators can bypass the blacklist for emergency or maintenance operations using the `DBCLI_OVERRIDE_BLACKLIST=true` environment variable:

```bash
DBCLI_OVERRIDE_BLACKLIST=true dbcli query "SELECT * FROM secrets_vault"
```

This override is logged and should only be used by administrators when necessary.

### Blacklist Configuration

Blacklist rules are stored in the `.dbcli` config file and can also be set manually:

```json
{
  "blacklist": {
    "tables": ["audit_logs", "secrets_vault"],
    "columns": {
      "users": ["password_hash", "ssn"]
    }
  }
}
```

### Blacklist vs. Permissions

The blacklist and permission model are complementary layers of access control:

| Layer | Controls | Applies To |
|-------|----------|------------|
| **Permission Model** | Operation type (read/write/delete) | All tables |
| **Blacklist** | Specific tables and columns | Targeted sensitive data |

A Query-only agent cannot write to any table, and also cannot read blacklisted tables or columns — both restrictions apply simultaneously.

---

## Saved queries

Save parameterised SELECT snippets and re-run them by name:

```bash
dbcli queries list
dbcli queries show @dau
dbcli q @dau --param days=30 --format json
```

Snippets live in `.dbcli-shared/queries/` (committed) or `.dbcli/queries/`
(gitignored, personal override). Each `.sql` file declares its frontmatter
in a `-- ---` block. Read `assets/reference.md` for the full schema.

---

## AI Integration Guide

dbcli ships AI-consumable skill files (`assets/SKILL.md` and `assets/reference.md`) and can copy them into your favorite AI tool directories.

### Quick Start

Generate skill for your preferred platform:

```bash
# Claude Code (Anthropic's VS Code extension)
dbcli skill --install claude

# Gemini CLI (Google's command-line AI)
dbcli skill --install gemini

# GitHub Copilot CLI
dbcli skill --install copilot

# GitHub Copilot CLI plugin marketplace
#   copilot plugin marketplace add CarlLee1983/dbcli
#   copilot plugin install dbcli-agent@dbcli-agent

# Cursor IDE (AI-native editor)
dbcli skill --install cursor

# Cursor plugin marketplace
#   /add-plugin dbcli-agent

# Codex plugin marketplace
#   codex plugin marketplace add CarlLee1983/dbcli
# Then open /plugins and install dbcli-agent.
# Full install guide: plugins/dbcli-agent/INSTALL.md
```

After installation, the AI agent will have access to dbcli commands and can use them to query, insert, update, or export data based on your permission level.

### Platform-Specific Setup

Every flow below assumes Bun 1.3.3+ on your `PATH`: the installed `dbcli` executable runs under Bun regardless of which package manager fetched it.

#### Claude Code (Anthropic)

1. Install dbcli globally: `bun install -g @carllee1983/dbcli`
2. Initialize: `dbcli init` (choose permission level)
3. Install skill: `dbcli skill --install claude`
4. Restart Claude Code extension
5. In Claude Code chat, ask: "Show me the database schema" or "Query active users"

**Skill location:** `~/.claude/skills/dbcli/` (SKILL.md + reference.md)

---

#### Gemini CLI (Google)

1. Install dbcli globally: `bun install -g @carllee1983/dbcli`
2. Initialize: `dbcli init`
3. Install skill: `dbcli skill --install gemini`
4. Start Gemini: `gemini start`
5. In chat, request: "Query the users table" or "Show database tables"

**Skill location:** `~/.gemini/skills/dbcli/` (SKILL.md + reference.md)

---

#### GitHub Copilot CLI

1. Install dbcli globally: `bun install -g @carllee1983/dbcli`
2. Initialize: `dbcli init`
3. Install skill: `dbcli skill --install copilot`
4. Install Copilot CLI: `npm install -g @github-next/github-copilot-cli`
5. Use copilot preview: `copilot --help` and explore dbcli integration

**Skill location:** `.github/skills/dbcli/` (SKILL.md + reference.md) when you run `dbcli skill --install copilot` in your project root.

---

#### Cursor IDE

1. Install dbcli globally: `bun install -g @carllee1983/dbcli`
2. Initialize: `dbcli init`
3. Install skill: `dbcli skill --install cursor`
4. Open Cursor editor
5. Use Cursor's Composer: "Insert a new user" or "Export user data"

**Skill location:** `.cursor/rules/dbcli.mdc` (summary + workflows) and `.cursor/skills/dbcli/reference.md` (full command flags and examples) under the **current working directory** when you run `dbcli skill --install cursor`.

**Cursor plugin install:** in Cursor Agent chat, run `/add-plugin dbcli-agent`, or see `plugins/dbcli-agent/INSTALL.md#cursor` for marketplace and fallback options.

---

#### Codex

1. Add the marketplace: `codex plugin marketplace add CarlLee1983/dbcli`.
2. Open `/plugins`, select the dbcli Agent marketplace, and install `dbcli-agent`.
3. For a persistent CLI, install dbcli globally: `bun install -g @carllee1983/dbcli` (or `npm install -g`, which still needs Bun on `PATH`).
4. Without a global install, the plugin skill uses `bunx @carllee1983/dbcli <command>` as its fallback.
5. Initialize: `dbcli init` or `bunx @carllee1983/dbcli init`.

**Plugin skill location:** `skills/dbcli/` (SKILL.md + reference.md).

**Install guide:** `plugins/dbcli-agent/INSTALL.md`.

---

### Example: AI Agent Workflow

**Scenario:** You want an AI agent to analyze user engagement data.

```bash
# 1. Install and initialize
bun install -g @carllee1983/dbcli
dbcli init  # Choose "query-only" for safety

# 2. Install skill to Claude Code
dbcli skill --install claude

# 3. In Claude Code chat, ask:
# "Analyze the last 7 days of user activity and summarize insights"

# Claude Code will:
# - Use: dbcli schema users, dbcli query "SELECT ..."
# - Parse JSON output
# - Provide analysis
```

### Updating the skill after upgrades

`dbcli skill` copies the bundled **`assets/SKILL.md`** (and for `--install`, **`assets/reference.md`** next to it). It is **not** regenerated from your live config. When you **upgrade dbcli** or the bundled skill changes, re-copy it to each platform you use:

```bash
dbcli skill --install claude
dbcli skill --install gemini
# ... etc.
```

If an installed primary skill file is older than the bundled `assets/SKILL.md`, dbcli prints a **stderr reminder** after most commands (see **`dbcli upgrade`**). Changing **permission level** or **blacklist** in `.dbcli` affects what the CLI allows at runtime — keep project context (e.g. `dbcli status`, `dbcli blacklist list`) in mind for agents even though the skill text lists the full command set.

---

## Audit Log

> **Default ON since v1.20.0.** Existing projects will begin creating
> `.dbcli/audit/<connection>.jsonl` on first command after upgrading.
> Set `audit.enabled = false` in `.dbcli` to opt out.

Every command that touches a database writes a structured JSONL entry to
`.dbcli/audit/<connection>.jsonl`. Inspect the recent history with:

```bash
dbcli audit tail --n 10                    # last 10 entries on current connection
dbcli audit tail --all --for-agent         # cross-connection JSON envelope
dbcli audit show <uuid-prefix>             # full entry by id prefix (>=4 chars)
dbcli audit show --recovery-ref <uuid>     # find entry that emitted a recovery envelope
dbcli audit health                         # writer state, rotation %, last write status
dbcli audit clear                          # erase audit log for current connection (prompts y/N)
```

Entries are **metadata-only** — never raw SQL bodies, never `--param` values,
never result cell contents. Redaction comes from the same source as v1.19.1's
agent-facing JSON contracts (`tests/helpers/sensitive-output.ts`).

**Recovery envelope linkage.** When a `--recovery` failure writes
`.dbcli/last-recovery.json`, the audit entry's `recovery_ref` field and the
envelope's `audit_ref` field reference each other. Agents can pivot between
audit history and recovery envelopes from either direction. The
`inspect` / `guide` / `recover` / `recover --apply` JSON output embeds
`audit_recent: AuditEntryBrief[]` (last 5 entries) so a fresh session has
immediate context.

**Full bi-directional coverage (v1.20.1+):** Recovery ↔ audit linkage is
wired on every `--recovery`-capable command — `query`, `inspect`, `insert`,
`update`, `delete`, `export`, `q`, and `schema`. The audit entry's
`recovery_ref` and the envelope's `audit_ref` carry matching UUIDs on every
failure path, so agents can pivot from a saved envelope
(`.dbcli/last-recovery.json`) to its audit entry via
`dbcli audit tail --recovery-ref <id>` (and back via
`dbcli audit show --recovery-ref <id>`). The v1.20.0 partial-coverage gap
on the 6 DML/DDL commands was closed in v1.20.1. The coverage matrix lives
in
[`.planning/phases/25-recovery-envelope-bi-directional-linkage/25-J1-COVERAGE-MATRIX.md`](./.planning/phases/25-recovery-envelope-bi-directional-linkage/25-J1-COVERAGE-MATRIX.md).

For deeper agent workflows (session handoff, forensics walk-through), see
[`assets/SKILL.md`](./assets/SKILL.md) §Audit Log usage.

---

## Troubleshooting

### Connection Issues

#### "ECONNREFUSED: Connection refused"

Database is not running or host/port is incorrect.

**Solutions:**

```bash
# Verify database is running
psql --version  # PostgreSQL installed?
mysql --version  # MySQL installed?

# Check connection string
dbcli init  # Re-run initialization to verify credentials

# Verify host/port from command line
psql -h localhost -U postgres  # PostgreSQL test
mysql -h 127.0.0.1 -u root      # MySQL test
```

#### "ENOTFOUND: getaddrinfo ENOTFOUND hostname"

Hostname resolution failed (typo or DNS issue).

**Solutions:**

```bash
# Verify hostname in project config (directory layout: .dbcli/config.json)
grep host .dbcli/config.json

# Test DNS resolution
ping your-hostname.com

# Use 127.0.0.1 instead of localhost if issues persist
dbcli init  # Re-initialize with correct host
```

---

### Permission Errors

#### "Permission denied: INSERT requires Read-Write or Admin"

Trying to write with Query-only permission level.

**Solution:** Re-initialize with higher permission level:

```bash
rm -rf .dbcli   # Remove project config (destructive — backup if needed)
dbcli init      # Re-run, choose "read-write", "data-admin", or "admin"
```

#### "Permission denied: DELETE operation requires Data-Admin or Admin"

DELETE is not allowed in query-only or read-write mode.

**Solution:** Use `data-admin` or `admin` permission (re-run `dbcli init`, or edit `.dbcli/config.json`), or ask an administrator.

```bash
dbcli init  # Choose "data-admin" or "admin"
dbcli delete users --where "id=1" --force
```

---

### Query Errors

#### "Table not found: users"

Table doesn't exist or name is misspelled.

**Solution:**

```bash
# Show all available tables
dbcli list

# Check spelling and retry
dbcli query "SELECT * FROM user" --format json
```

#### "Syntax error near SELECT"

SQL syntax error in query.

**Solution:**

```bash
# Test query in native database client first
psql  # Or mysql
# SELECT * FROM users;  <- Test here first

# Then use in dbcli
dbcli query "SELECT * FROM users"
```

---

### Performance Issues

#### "Query returns 1000 rows instead of full result set"

Query-only permission auto-limits results for safety.

**Solution:** Increase permission level or fetch data in chunks:

```bash
# Re-initialize with higher permission
dbcli init  # Choose "read-write" or "admin"

# OR fetch data in chunks
dbcli query "SELECT * FROM users LIMIT 100 OFFSET 0"
dbcli query "SELECT * FROM users LIMIT 100 OFFSET 100"
```

#### "CLI startup takes 30+ seconds (first run)"

bunx (or npx) is downloading and caching the package.

**Solution:** This is normal on first run. Subsequent runs are instant:

```bash
bunx @carllee1983/dbcli init  # First run: 30s (downloads)
bunx @carllee1983/dbcli init  # Second run: <1s (cached)

# Or install globally for faster startup
bun install -g @carllee1983/dbcli
dbcli init  # All future runs: <1s
```

---

### Cross-Platform Issues

#### Windows: "Command not found: dbcli"

The shim was not created, PATH was not updated, or Bun is missing — `dbcli` runs under
Bun on Windows too.

**Solutions:**

```bash
# Confirm Bun is installed and on PATH first
bun --version

# Restart terminal to refresh PATH
# OR reinstall globally
bun uninstall -g @carllee1983/dbcli
bun install -g @carllee1983/dbcli

# Verify installation
where dbcli  # Windows command to find executable
```

#### macOS/Linux: "Permission denied: ./dist/cli.mjs"

Executable bit not set.

**Solution:**

```bash
chmod +x dist/cli.mjs
./dist/cli.mjs --help
```

---

## System Requirements

### Database Support

- **PostgreSQL:** 12.0+
- **MySQL:** 8.0+
- **MariaDB:** 10.5+
- **MongoDB:** 4.4+ (query and collection listing via `mongodb://` and `mongodb+srv://`; see **MongoDB Atlas / SRV Connections** earlier in this document)

### Runtime

- **Bun:** 1.3.3+ — required. The published bundles use Bun APIs and Bun module
  resolution; `dbcli` does not run on Node.
- **Node.js:** only for the `./agent-core` subpath export, which is Node-importable
  (18.0.0+). `npm install -g` / `npx` work as distribution channels but still shell out
  to Bun at run time.

### Platforms

- **macOS:** Intel and Apple Silicon
- **Linux:** x86_64 (Ubuntu, Debian, CentOS, etc.)
- **Windows:** 10+ (via npm .cmd wrapper)

---

## Development

Package consumers building agent CLIs can import the semver-stable, database-independent interface from `@carllee1983/dbcli/agent-core`:

```ts
import {
  loadEnvFile,
  parseConnectionNames,
  resolveConnectionSelector,
  resolveEnvRef,
  trimAppliedLimit,
} from '@carllee1983/dbcli/agent-core'
```

The broader `@carllee1983/dbcli/core` interface remains dbcli-specific. CLI option factories, config-storage binding, and connection-string parsing are intentionally outside `agent-core`.

```bash
bun test                  # full test suite (Bun test runner)
bun run typecheck         # TypeScript compile-time validation
bun run test:unit         # unit + core tests only
bun run test:integration  # integration tests
bun run test:gherkin      # Gherkin feature tests at the CLI boundary
bun run test:docker       # integration tests with docker-compose.test.yml (MySQL + PostgreSQL)
bun run build             # bundle CLI to dist/ (used before publish)
```

CI treats `bun run typecheck` and `bun test` as the required pass/fail validation gate on every push and pull request. Lint, build, smoke checks, and benchmarks run in addition to that gate.

Live database integration tests use `.dbcli/config.json` by default. If your live
config lives elsewhere, set `LIVE_DB_CONFIG_PATH=/path/to/.dbcli` before running:

```bash
LIVE_DB_CONFIG_PATH=/path/to/.dbcli bun test tests/integration/live-db.test.ts
```

If no live config is available, `tests/integration/live-db.test.ts` skips instead
of falling back to the default PostgreSQL configuration. Set
`SKIP_INTEGRATION_TESTS=true` to skip all integration tests.

See [CONTRIBUTING.md](./CONTRIBUTING.md) for full setup, testing, and release process.

---

## License

See LICENSE file for details.