🗄️ DBA-Architect

Principal Database Administrator (DBA) & Data Architecture AI Skill Agent + MCP Server
Autonomously audit SQL migrations, optimize composite B-Tree indexes via ESR rules, eliminate N+1 queries, diagnose EXPLAIN plans, execute zero-downtime migrations, and prevent transaction deadlocks for PostgreSQL & MySQL.

English | Bahasa Indonesia

🌟 Overview

DBA-Architect bridges the gap between software development and deep database performance engineering. Modern AI assistants often guess or generate naive SQL recommendations; DBA-Architect replaces guesswork with 8 deterministic AST & heuristic engines built upon battle-tested database administration playbooks.

Whether you are authoring a DDL migration in Prisma, tuning an e-commerce order checkout transaction, or scaling a 50M-row table, DBA-Architect delivers deterministic audits, Mermaid visual execution plans, and non-blocking production runbooks directly to your AI Agent IDE.

Core Pillars

• 🛡️ Zero-Guesswork Deterministic Engine: Built-in AST parsers (pgsql-ast-parser, node-sql-parser) enforcing 30+ strict DBA rules with a deterministic Schema Health Score (0–100).

• 🌐 Universal AI Agent Ecosystem: First-class adapters for Google Antigravity, Cursor, Anthropic Claude Code, Windsurf, GitHub Copilot, Cline / Roo Code, Continue.dev, and Kiro AI IDE.

• ⚡ ESR Index Engineering: Implements the Equality, Sort, Range composite indexing algorithm, detects non-sargable predicates, and eliminates redundant left-prefix indexes.

• 🚀 Zero-Downtime Migration Runbooks: Converts blocking ALTER TABLE statements into phased Expand & Contract workflows (CONCURRENTLY, NOT VALID ➔ VALIDATE, ALGORITHM=INPLACE).

• 🔒 Concurrency & Deadlock Prevention: Detects lost update race conditions in financial paths, enforces pessimistic row-locking (SELECT ... FOR UPDATE), and flags dangerous external HTTP/network I/O inside transaction blocks.

Related MCP server: Postgres MCP Pro

🏗️ Architecture

graph TB
    subgraph ClientLayer ["1. Multi-Agent AI IDE Layer (Universal Adapters)"]
        A1["Google Antigravity<br/>(@dba-architect)"]
        A2["Cursor<br/>(.cursor/rules/dba-architect.mdc)"]
        A3["Claude Code<br/>(CLAUDE.md)"]
        A4["Windsurf<br/>(.windsurfrules)"]
        A5["GitHub Copilot<br/>(.github/copilot-instructions.md)"]
        A6["Cline / Roo Code<br/>(.clinerules)"]
        A7["Universal MCP Clients<br/>(Continue, Desktop, Kiro)"]
        A8["CLI Runner<br/>(npx dba-architect init)"]
    end

    subgraph ServerLayer ["2. Deterministic MCP Server (dba-architect)"]
        M1["JSON-RPC 2.0 Stdio Transport"]
        M2["AST Parsers: pgsql-ast-parser & node-sql-parser"]
        M3["Codebase Scanner & N+1 Loop Extractor"]
        M4["Capacity & B-Tree Footprint Calculator"]
    end

    subgraph ToolsSuite ["3. 8 Deterministic DBA Tools"]
        T1["analyze_migration"]
        T2["recommend_indexes_for_queries"]
        T3["scan_project_queries"]
        T4["analyze_explain_plan"]
        T5["plan_zero_downtime_migration"]
        T6["analyze_transaction_and_locking"]
        T7["recommend_partitioning_and_archiving"]
        T8["calculate_db_capacity_and_config"]
    end

    ClientLayer --> ServerLayer
    ServerLayer --> ToolsSuite

🛠️ 8 Deterministic MCP Tools & Rules Matrix

DBA-Architect exposes 8 specialized tools adhering to the Model Context Protocol (MCP):

🚀 Installation & Setup

You can use DBA-Architect through any of the following methods depending on your development workflow:

🔌 Method A: MCP Server Setup (Recommended for AI IDEs)

Configure your AI IDE to fetch and execute the MCP server directly via npx:

1. Google Antigravity & Gemini CLI

Add to your project's mcp_config.json or global config:

{
  "mcpServers": {
    "dba-architect": {
      "command": "npx",
      "args": ["-y", "github:hanifalkauni/dba-architect"]
    }
  }
}

2. Claude Desktop & Claude Code

Add to claude_desktop_config.json:

{
  "mcpServers": {
    "dba-architect": {
      "command": "npx",
      "args": ["-y", "github:hanifalkauni/dba-architect"]
    }
  }
}

3. Cursor & Continue.dev

In Cursor (Features ➔ MCP Servers) or Continue (config.json):

{
  "name": "dba-architect",
  "command": "npx",
  "args": ["-y", "github:hanifalkauni/dba-architect"]
}

Note for Local Development: If running from a local clone, replace "command": "npx" with "command": "node" and "args": ["/path/to/dba-architect/dist/index.js"].

📦 Method B: Universal Skills CLI (skills.sh)

Install DBA-Architect with a single command into Cursor, Claude Code, Windsurf, Copilot, or Gemini CLI:

npx skills add hanifalkauni/dba-architect

⚡ Method C: One-Command Adapter Injection via CLI

Run this command inside any target repository to automatically generate and configure adapters for all supported IDEs:

npx -y github:hanifalkauni/dba-architect init

💻 Method D: Manual Adapter Installation per AI IDE

Copy the adapter to your workspace skill directory:

mkdir -p .agents/skills/dba-architect
cp adapters/antigravity/SKILL.md .agents/skills/dba-architect/SKILL.md

(Or install globally at ~/.gemini/config/skills/dba-architect/SKILL.md)

Copy rules to your Cursor rules directory:

mkdir -p .cursor/rules
cp adapters/cursor/dba-architect.mdc .cursor/rules/dba-architect.mdc

Copy instructions to your project root:

cp adapters/claude/CLAUDE.md ./CLAUDE.md

Copy rules to your project root:

cp adapters/windsurf/.windsurfrules ./.windsurfrules

Copy instructions to your GitHub repository directory:

mkdir -p .github
cp adapters/copilot/copilot-instructions.md .github/copilot-instructions.md

Copy steering and MCP configuration:

mkdir -p .kiro/steering
cp adapters/kiro/dba-architect.md .kiro/steering/dba-architect.md
cp adapters/kiro/config.json .kiro/config.json

📂 Repository Structure

dba-architect/
├── adapters/                           # Multi-Agent IDE Adapter Suite
│   ├── antigravity/SKILL.md            # Google Antigravity / Gemini CLI Skill
│   ├── claude/CLAUDE.md                # Anthropic Claude Code & Desktop
│   ├── cline/.clinerules               # Cline / Roo Code Rules
│   ├── continue/                       # Continue.dev Rules & MCP Configuration
│   ├── copilot/copilot-instructions.md # GitHub Copilot Custom Instructions
│   ├── cursor/dba-architect.mdc        # Cursor MDC Rules (Globs & Frontmatter)
│   ├── kiro/                           # Kiro AI IDE Steering & MCP Configuration
│   └── windsurf/.windsurfrules         # Windsurf Cascade Rules
├── src/                                # Core Engine Source Code
│   ├── adapters.ts                     # CLI Adapter Installer Engine
│   ├── index.ts                        # Unified Entrypoint (CLI Runner + MCP Stdio)
│   ├── server.ts                       # MCP Server Implementation & Schema Registry
│   ├── constants/                      # Deterministic DBA Rules & Heuristics Catalog
│   ├── parsers/                        # SQL AST & DDL Parsers (PG & MySQL)
│   ├── tools/                          # 8 Deterministic Tool Handlers
│   ├── types/                          # TypeScript Interfaces & Zod Schemas
│   └── utils/                          # Safe File Walker & Heuristics Utilities
├── tests/                              # Comprehensive Test Suite (Vitest)
│   └── dba-architect.test.ts           # 18 Unit & Integration Tests (100% Passing)
├── dba-architect.config.json           # Default Configuration Template
├── schema.json                         # JSON Schema for Configuration Validation
├── SKILL.md                            # Canonical Universal Agent Skill Playbook
├── package.json                        # Node.js Package Manifest & Dependencies
├── tsconfig.json                       # TypeScript Compiler Configuration
└── LICENSE                             # MIT Open-Source License

🧪 Testing & Verification

Run the test suite using Vitest to verify all 8 tools and adapter installations:

# Run unit & integration tests
npm test

# Run tests in watch mode
npm run test:watch

# Compile TypeScript
npm run build

❓ FAQ & Troubleshooting

B-Tree indexes can only traverse a single range predicate efficiently. If a range condition is placed before equality or sort conditions, the database engine must scan all rows matching the range and filter the rest in memory or perform a separate sort step (External merge Disk). The ESR rule ensures optimal index selectivity and eliminates unnecessary sorting.

In PostgreSQL, standard CREATE INDEX acquires a SHARE lock that blocks writes (INSERT/UPDATE/DELETE). DBA-Architect automatically converts these to CREATE INDEX CONCURRENTLY and splits foreign key additions into ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT to avoid long-running exclusive locks. In MySQL, it generates ALGORITHM=INPLACE, LOCK=NONE.

MySQL InnoDB physically orders table rows on disk based on the Clustered Primary Key. Inserting non-sequential random UUIDv4 strings forces arbitrary page insertions, triggering high rates of B-Tree page splits (50% fragmentation) and massive random I/O. DBA-Architect enforces sequential UUIDv7 or BIGINT AUTO_INCREMENT.

The command is fully idempotent. It safely overwrites adapter files to match the latest template version while preserving any custom changes if your project already contains customized rules.

🛡️ Security Policy & Guardrails

DBA-Architect operates strictly under static, read-only analysis principles:

• Zero Production Execution: DBA-Architect does not connect to live production databases or execute destructive DDL directly. It outputs verified DDL scripts and runbooks for human review.

• Sensitive File Protection: Built-in file traversal guardrails automatically reject access to .env, private keys (.pem, .key), certificates, and .git files.

• Data Privacy & Masking: Query scanners automatically mask literals and personal identifiers (PII) before analyzing query structures.

📜 License

This project is licensed under the MIT License. See the LICENSE file for details.

SPDX-License-Identifier: MIT