sequenceDiagram
    autonumber
    actor Agent as AI Agent
    participant MCP as Stdio MCP Server
    participant DB as SQLite Engine
    participant Plan as EXPLAIN QUERY PLAN
    participant Guard as Transaction Guardrail (SAVEPOINT / Rollback)
    participant Out as Diagnostic Output

    Agent->>MCP: tools/call (JSON-RPC over stdio)
    MCP->>DB: Route request and validate parameters (Zod)

    alt Query analysis: explain_query_plan / suggest_optimal_indexes
        MCP->>DB: Execute SQL statement
        DB->>Plan: EXPLAIN QUERY PLAN <sql>
        Plan-->>DB: Scan tree (SCAN / SEARCH / TEMP B-TREE)
        DB-->>MCP: Raw query-plan rows
        MCP->>Out: Cost level, warnings, index recommendations
    else Migration dry-run: dry_run_migration
        MCP->>Guard: Candidate DDL statement
        Guard->>DB: SAVEPOINT dry_run_migration
        Guard->>DB: Execute candidate DDL
        Guard->>Guard: Static risk scan (DROP / NOT NULL / TRUNCATE)
        Guard->>DB: ROLLBACK TO SAVEPOINT dry_run_migration
        Guard->>DB: RELEASE SAVEPOINT dry_run_migration
        Guard->>Out: Risk level and rollback confirmation
    end

    Out-->>MCP: Markdown diagnostic document
    MCP-->>Agent: Structured MCP tool response

{
  "sqlQuery": "SELECT * FROM orders WHERE user_id = 42 AND status = 'pending' ORDER BY created_at DESC"
}

## Query Plan Analysis

**SQL:** `SELECT * FROM orders WHERE user_id = 42 AND status = 'pending' ORDER BY created_at DESC`
**Cost Level:** HIGH
**Full Table Scan Detected:** ❌ YES
**Index Scans Used:** ❌ NONE

### Raw EXPLAIN QUERY PLAN Output

### ⚠️ Warnings
- ⚠️ FULL TABLE SCAN detected on table "orders" — no suitable index found
- ⚠️ Temporary B-Tree sort detected — ORDER BY without supporting index

### 💡 Recommendations
- Consider adding an index on the WHERE/JOIN column(s) of table "orders"
- Add an index that covers the ORDER BY column(s) to avoid filesort

### 🚨 Tables Requiring Indexes
- **orders**: Run `suggest_optimal_indexes` with tableName="orders" for precise recommendations

{
  "tableName": "orders"
}

## Table Schema: `orders`

**Columns (7):**
  id                       INTEGER          NULLABLE 🔑 PK
  user_id                  INTEGER          NOT NULL
  product_name             TEXT             NOT NULL
  amount                   REAL             NOT NULL
  status                   TEXT             NOT NULL  DEFAULT 'pending'
  created_at               TEXT             NOT NULL
  shipped_at               TEXT             NULLABLE

**Primary Key(s):** id

**Foreign Keys:**
  - user_id → users(id) ON DELETE NO ACTION

**Indexes (0):**
  (no indexes)

**Index Coverage:** 14% of columns indexed
**Unindexed Columns:** user_id, product_name, amount, status, created_at, shipped_at

> ⚠️ 6 unindexed columns detected — query performance may suffer without targeted indexes

{
  "migrationSql": "ALTER TABLE orders ADD COLUMN priority_score INTEGER NOT NULL"
}

## Dry-Run Migration Report

**Migration SQL:**
```sql
ALTER TABLE orders ADD COLUMN priority_score INTEGER NOT NULL

A safe migration such as `ALTER TABLE orders ADD COLUMN discount REAL DEFAULT 0.0` returns `Risk Level: ✅ SAFE`, `Transaction Rolled Back: ✅ YES`, and executes successfully before being reverted.

---

### 4. `suggest_optimal_indexes`

Parses `WHERE` predicates, multi-table `JOIN` keys, and `ORDER BY` columns, then generates production-ready `CREATE INDEX IF NOT EXISTS` statements with performance rationale. At least one of `sqlQuery` or `tableName` must be provided.

**Parameters**

| Name | Type | Required | Description |
|------|------|----------|-------------|
| `sqlQuery` | `string` | Conditional | SQL query to analyze for index opportunities. |
| `tableName` | `string` | Conditional | Table name for coverage-based recommendations. |

**Example input**

```json
{
  "tableName": "orders"
}

## Index Optimization Recommendations

### Suggested Indexes for `orders`
- **user_id**: `CREATE INDEX IF NOT EXISTS idx_orders_user_id ON orders (user_id);`
- **product_name**: `CREATE INDEX IF NOT EXISTS idx_orders_product_name ON orders (product_name);`
- **amount**: `CREATE INDEX IF NOT EXISTS idx_orders_amount ON orders (amount);`
- **status**: `CREATE INDEX IF NOT EXISTS idx_orders_status ON orders (status);`
- **created_at**: `CREATE INDEX IF NOT EXISTS idx_orders_created_at ON orders (created_at);`
- **shipped_at**: `CREATE INDEX IF NOT EXISTS idx_orders_shipped_at ON orders (shipped_at);`

---
**6 index(es) recommended.** Apply them in a migration and re-run `explain_query_plan` to verify improvement.

{
  "rowCount": 1000
}

## ✅ Demo Database Seeded Successfully

| Table | Rows | Indexes |
|-------|------|---------|
| users | 1000 | PRIMARY KEY only (intentionally unindexed) |
| orders | 3000 | PRIMARY KEY only (intentionally unindexed) |
| transactions | 2000 | PRIMARY KEY only (intentionally unindexed) |

**Next steps to test the DBA tools:**
1. Run `explain_query_plan` with `SELECT * FROM orders WHERE user_id = 42 AND status = 'pending'`
2. Run `suggest_optimal_indexes` with the same query
3. Run `dry_run_migration` with `ALTER TABLE orders ADD COLUMN discount REAL NOT NULL`

git clone https://github.com/thedailyhub31-blip/intelligent-db-dba-mcp-server.git
cd intelligent-db-dba-mcp-server
npm install
npm run build
npm test

{
  "mcpServers": {
    "intelligent-db-dba": {
      "command": "node",
      "args": [
        "/absolute/path/to/intelligent-db-dba-mcp-server/dist/src/index.js"
      ]
    }
  }
}

.
├── src/
│   ├── index.ts                  # MCP server, tool registration, request routing
│   ├── db/
│   │   ├── databaseEngine.ts     # sql.js engine, plan analysis, schema inspection
│   │   └── migrationGuardrail.ts # SAVEPOINT dry-runs and DDL risk assessment
│   ├── tools/
│   │   └── dbaTools.ts           # Zod schemas and tool handlers
│   └── utils/
│       └── logger.ts             # stderr + server.log logging
├── scripts/
│   └── test-client.ts            # 10-check MCP stdio test harness
├── package.json
├── tsconfig.json
├── LICENSE
└── README.md