ChatSQL

ChatSQL is an AI-powered, web-based tool that allows users to design, deploy, and optimize database schemas using natural language. It features a sophisticated multi-agent system with specialized agents for database design and performance optimization. Users can describe their database needs in plain English, and the system will generate optimized SQL schemas, deploy them to TigerData's PostgreSQL platform, and continuously monitor performance with zero-copy optimization testing.

✨ Features

• Multi-Agent Architecture: Specialized agents for design, optimization, and monitoring work together to deliver production-ready database solutions.
• Unified Agent Tools API: Secure, centralized API for all agent operations with built-in security and error handling.
• Natural Language to SQL: Describe your database needs in plain English and get AI-generated, optimized SQL schemas.
• Zero-Copy Optimization: Test performance improvements safely using TigerData's zero-copy fork technology without affecting production.
• Real-time WebSocket Communication: Live chat interface with instant agent responses and status updates.
• Interactive Schema Design: Design Agent analyzes requirements and generates optimized PostgreSQL schemas with proper indexing and constraints.
• Performance Optimization: Optimize Agent creates isolated test environments, applies optimizations, and merges successful changes.
• Real-time Monitoring: Track agent status, database performance metrics, and API health in real-time.
• Dual AI Provider Support: Choose between Google Gemini and OpenAI for schema generation with configurable API keys.
• WebSocket-Powered Terminal: Execute database operations with live streaming updates and progress indicators.
• API Endpoint Monitoring: Add and monitor external API endpoints with real-time health checks and response tracking.

Agent Collaboration System

The application features a sophisticated multi-agent system that transforms natural language into database operations through a unified tools API:

Architecture Flow

User Input → ChatSQL Interface (Port 3001)
    ↓
AI Provider (Gemini/OpenAI) → SQL Schema Generation
    ↓
Agent Orchestrator → Design Agent / Optimize Agent
    ↓
AgentToolsService API (/api/agents/tools)
    ↓
TigerData API → PostgreSQL Database Operations
    ↓
Response → Chat Interface (Database Results + Schema Visualization)

Design Agent → Optimize Agent

1. Design Agent: Converts natural language requirements into SQL schemas

   • Analyzes user input for database needs
   • Uses call_api tool to inspect external data sources
   • Generates optimized table structures with proper data types
   • Uses execute_sql tool to deploy schemas to TigerData
   • Uses get_schema_info tool to validate and inspect existing schemas

2. Optimize Agent: Enhances performance by testing modifications using zero-copy forks

   • Monitors query performance metrics
   • Uses create_zero_copy_fork tool to create isolated testing environments
   • Uses execute_sql tool to test optimizations safely
   • Uses delete_fork tool to clean up test environments
   • Merges successful optimizations back to production

Available Agent Tools

All 6 tools are accessible via POST /api/agents/tools:

Tool	Purpose
call_api	Call external APIs for data inspection
execute_sql	Execute SQL queries with security protection
create_zero_copy_fork	Create TigerData forks for safe testing
delete_fork	Delete TigerData forks to clean up
get_schema_info	Inspect table schemas and metadata
get_monitoring_data	Get chart-ready performance data

Real-time Charts & Monitoring

• Interactive Chart.js visualizations
• API response latency tracking
• Service health status monitoring
• Query performance metrics
• Auto-refresh every 30 seconds

Zero-Copy Fork System

• Test optimizations without production impact
• Automatic rollback on failures
• Performance comparison before merge
• Isolated testing environment

🚀 Getting Started

ChatSQL consists of a React frontend with a Node.js/Express backend featuring specialized AI agents for database design and optimization.

Prerequisites

• Node.js 18+
• API key from Google (Gemini) or OpenAI
• TigerData account (for database deployment)

Installation & Setup

1. Clone and install dependencies:

   git clone <repository-url>
   cd chatsql
   npm install
   cd server && npm install

2. Environment Configuration: Create a .env file in the server directory:

   TIGER_DATA_URL=your_tigardata_connection_string
   OPENAI_API_KEY=your_openai_api_key
   GOOGLE_API_KEY=your_google_api_key
   PORT=3002

3. Start the backend server:

   cd server
   npm run dev

4. Start the frontend (in a new terminal):

   npm run dev

5. Open your browser to http://localhost:3001

Configuration

1. Click the Settings icon (⚙️) in the top-right corner
2. Configure your AI Provider (Google Gemini or OpenAI) and enter API keys
3. Set up TigerData credentials for database deployment
4. Add any external API endpoints you want to monitor

Usage

Start chatting with the agents! Try commands like:

• "Create a user management database"
• "Design an e-commerce schema with products and orders"
• "Optimize my database performance"
• "Add indexes to the users table"

🛠️ Tech Stack

• Frontend: React 18 with TypeScript
• Backend: Node.js/Express with WebSocket support
• Styling: Tailwind CSS with custom gradients
• AI Integration:
  • Google Gemini API (@google/generative-ai)
  • OpenAI API
• Database: TigerData PostgreSQL with zero-copy forks
• Real-time Communication: WebSocket for agent chat and status updates
• Visualization: Chart.js for performance metrics and API monitoring
• UI Components: Lucide React icons, Paper Design shaders
• Build Tools: Webpack 5, Babel, TypeScript compiler

📁 Project Structure

├── src/                           # React Frontend
│   ├── components/
│   │   ├── App.tsx               # Main application component
│   │   ├── chat/
│   │   │   ├── ChatPanel.tsx     # WebSocket chat interface
│   │   │   └── ChainOfThoughtLog.tsx
│   │   ├── layout/
│   │   │   ├── Header.tsx        # App header with settings
│   │   │   ├── SettingsModal.tsx # Configuration modal
│   │   │   └── TabbedPanel.tsx   # Right panel tabs
│   │   └── panels/
│   │       ├── TerminalPanel.tsx # Real-time terminal
│   │       ├── ApiMonitoringPanel.tsx
│   │       ├── ChartPanel.tsx    # Performance charts
│   │       └── VisualSchemaViewer.tsx
│   ├── hooks/
│   │   ├── useChatLogic.ts       # Chat state management
│   │   └── useMcpClient.ts       # MCP client hook
│   ├── clients/
│   │   ├── AiService.ts          # AI provider abstraction
│   │   └── MCPClient.ts          # MCP communication
│   ├── services/
│   │   └── apiClient.ts          # HTTP client utilities
│   ├── utils/
│   │   ├── constants.ts          # App constants
│   │   └── sqlParser.ts          # SQL parsing utilities
│   └── index.tsx                 # React entry point
├── server/                       # Node.js Backend
│   ├── src/
│   │   ├── index.ts              # Express server setup
│   │   ├── config/
│   │   │   └── environment.ts    # Environment configuration
│   │   ├── agents/
│   │   │   ├── AgentOrchestrator.ts # Agent coordination
│   │   │   ├── DesignAgent.ts    # Schema design agent
│   │   │   └── OptimizationAgent.ts # Performance optimization
│   │   ├── routes/
│   │   │   ├── agents.ts         # Agent API endpoints
│   │   │   ├── chat.ts           # Chat WebSocket routes
│   │   │   └── sql.ts            # SQL execution routes
│   │   ├── services/
│   │   │   └── AgentToolsService.ts # Unified tools API
│   │   └── index.ts
│   └── package.json
├── database-server/              # Database MCP Server
│   ├── src/
│   │   └── index.ts              # MCP server for DB operations
│   └── package.json
├── mcp-proxy/                    # Legacy MCP Proxy
├── design_agent_prompt.md        # Design Agent system prompt
├── optimize_agent_prompt.md      # Optimize Agent system prompt
├── AGENT_TOOLS_INTEGRATION.md    # Integration documentation
└── package.json                  # Root package.json

🤝 Contributing

We welcome contributions! Please see our Contributing Guide for details on:

• Setting up a development environment
• Code style and testing guidelines
• Submitting pull requests
• Agent development best practices

📄 License

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

---

Ready to revolutionize database development? Experience the power of AI-driven database design with ChatSQL's multi-agent architecture! 🚀