Natural Language SQL Interface

A web application that converts natural language queries to SQL using AI, built with FastAPI and Vite + TypeScript.

Features

• 🗣️ Natural language to SQL conversion using OpenAI or Anthropic
• 📁 Drag-and-drop file upload (.csv and .json)
• 📊 Interactive table results display
• 🔒 SQL injection protection
• ⚡ Fast development with Vite and uv
• 🏥 Comprehensive System Health Monitoring - Real-time status of all critical services
• 🔍 Pre-Submission Health Checks - Validates system health before creating GitHub issues
• 💾 Smart Persistence - Project paths and tab selections persist across page refreshes
• 📡 Service Monitoring - Tracks Backend API, Database, Webhook, Cloudflare Tunnel, and Frontend
• 🔬 Advanced Observability - Hook events, pattern learning, cost tracking, and work log system
• 📝 Work Log Panel (Panel 10) - Twitter-style 280-character session summaries with tags and linking
• 🤖 Pattern Learning - Automatic detection of recurring workflow patterns for optimization
• 💰 Cost Intelligence - Track API cost savings and automation ROI
• ✅ Comprehensive Test Coverage - 33+ test cases with Vitest and React Testing Library
• 🎨 Session 19 Improvements - Standardized patterns, real-time WebSocket updates, reusable UI components
  • ⚡ Real-Time Updates - WebSocket architecture with <2s latency (NO POLLING)
  • 🧩 Reusable Components - LoadingState, ErrorBanner, ConfirmationDialog
  • 📋 Repository Standards - Consistent CRUD naming across all repositories
  • 🛡️ Error Handling - Standardized errorHandler utility for consistent logging
  • 📚 Comprehensive Documentation - Migration guides, API references, pattern documentation

Prerequisites

• Python 3.10+
• uv (Python package manager)
• Node.js 18+
• Bun (or your preferred npm tool: npm, yarn, etc.)
• OpenAI API key and/or Anthropic API key
• PostgreSQL (required for observability, pattern learning, and analytics)
  • Docker recommended: docker run -d -p 5432:5432 -e POSTGRES_PASSWORD=changeme postgres:15-alpine
  • Or use provided docker-compose.yml

Setup

1. Install Dependencies

# Backend
cd app/server
uv sync --all-extras

# Frontend
cd app/client
bun install

2. Environment Configuration

Set up your API keys in the server directory:

cp .env.sample .env
# Edit .env and add your API keys

cd app/server
cp .env.sample .env
# Edit .env and add your API keys

Quick Start

Use the provided script to start both services:

./scripts/start.sh

Press Ctrl+C to stop both services.

The script will:

• Check that .env exists in app/server/
• Start the backend on http://localhost:8000
• Start the frontend on http://localhost:5173
• Handle graceful shutdown when you exit

Health Checks

Verify system health with comprehensive checks:

# Terminal-based health check (7 sections including observability)
./scripts/health_check.sh

# API endpoint (9 pre-flight checks before ADW workflows)
curl localhost:8000/api/v1/preflight-checks

# Panel 1 UI - Automatic display of all health checks

3-Layer Observability Health Checks:

1. Observability Database - PostgreSQL connection + required tables
2. Hook Events Recording - Verify pattern capture is working
3. Pattern Analysis System - Analytics scripts functional

Manual Start (Alternative)

Backend

cd app/server
# .env is loaded automatically by python-dotenv
uv run python server.py

Frontend

cd app/client
bun run dev

Usage

1. Upload Data: Click "Upload" to open the modal
   • Use sample data buttons for quick testing
   • Or drag and drop your own .csv or .json files
   • Uploading a file with the same name will overwrite the existing table
2. Query Your Data: Type a natural language query like "Show me all users who signed up last week"
   • Press Cmd+Enter (Mac) or Ctrl+Enter (Windows/Linux) to run the query
3. View Results: See the generated SQL and results in a table format
4. Manage Tables: Click the × button on any table to remove it

Development

Backend Commands

cd app/server
uv run python server.py      # Start server with hot reload
uv run pytest               # Run tests
uv add <package>            # Add package to project
uv remove <package>         # Remove package from project
uv sync --all-extras        # Sync all extras

Frontend Commands

cd app/client
bun run dev                 # Start dev server
bun run build              # Build for production
bun run preview            # Preview production build
bun run test               # Run tests in watch mode
bun run test:run           # Run tests once
bun run test:coverage      # Generate test coverage report
bun run test:ui            # Run tests with UI

Project Structure

.
├── app/                    # Main application
│   ├── client/             # Vite + TypeScript frontend
│   └── server/             # FastAPI backend
│
├── adws/                   # AI Developer Workflow (ADW) - GitHub issue automation system
├── scripts/                # Utility scripts (start.sh, stop_apps.sh)
├── specs/                  # Feature specifications
├── ai_docs/                # AI/LLM documentation
├── agents/                 # Agent execution logging
└── logs/                   # Structured session logs

API Endpoints

Core Functionality

• POST /api/upload - Upload CSV/JSON file
• POST /api/query - Process natural language query
• GET /api/schema - Get database schema
• POST /api/insights - Generate column insights

Health & Monitoring

• GET /api/health - Basic health check
• GET /api/system-status - NEW: Comprehensive system health monitoring for all critical services

Workflow Management

• GET /api/workflows - List active workflows
• GET /api/workflow-history - Get workflow execution history
• GET /api/routes - List all registered API routes

Security

SQL Injection Protection

The application implements comprehensive SQL injection protection through multiple layers:

1. Centralized Security Module (core/sql_security.py):

   • Identifier validation for table and column names
   • Safe query execution with parameterized queries
   • Proper escaping for identifiers using SQLite's square bracket notation
   • Dangerous operation detection and blocking

2. Input Validation:

   • All table and column names are validated against a whitelist pattern
   • SQL keywords cannot be used as identifiers
   • File names are sanitized before creating tables
   • User queries are validated for dangerous operations

3. Query Execution Safety:

   • Parameterized queries used wherever possible
   • Identifiers (table/column names) are properly escaped
   • Multiple statement execution is blocked
   • SQL comments are not allowed in queries

4. Protected Operations:

   • File uploads with malicious names are sanitized
   • Natural language queries cannot inject SQL
   • Table deletion uses validated identifiers
   • Data insights generation validates all inputs

Security Best Practices for Development

When adding new SQL functionality:

1. Always use the sql_security module functions
2. Never concatenate user input directly into SQL strings
3. Use execute_query_safely() for all database operations
4. Validate all identifiers with validate_identifier()
5. For DDL operations, use allow_ddl=True explicitly

Testing Security

Run the comprehensive security tests:

cd app/server
uv run pytest tests/test_sql_injection.py -v

Additional Security Features

• CORS configured for local development only
• File upload validation (CSV and JSON only)
• Comprehensive error logging without exposing sensitive data
• Database operations are isolated with proper connection handling

System Monitoring & Health Checks

The application includes comprehensive real-time monitoring of all critical services to ensure system reliability and provide early warning of issues.

System Status Panel

A real-time dashboard displays the health of all critical services:

• Backend API - FastAPI server uptime and performance
• Database - SQLite connection status and table count
• Webhook Service - GitHub webhook processor health and statistics
• Cloudflare Tunnel - Public endpoint connectivity status
• Frontend - React development server availability

Features:

• Color-coded status indicators (🟢 Healthy, 🟡 Degraded, 🔴 Error, ⚪ Unknown)
• Automatic refresh every 30 seconds
• Manual refresh button
• Detailed service metrics and uptime information
• Overall system health percentage

Pre-Submission Health Checks

Before creating GitHub issues, the system automatically checks service health and warns users if critical services are unavailable:

• Error State: Displays warning listing unavailable services
• Degraded State: Warns about potential delays but allows submission
• Healthy State: Proceeds without warnings

This prevents workflow failures due to service outages.

Smart Persistence

User preferences automatically persist across sessions:

• Project Path Persistence: Last-used project path saved to browser localStorage
• Tab Persistence: Active tab selection restored after page refresh
• Session Continuity: Maintains context across browser restarts

Monitoring Endpoint

# Check comprehensive system status
curl http://localhost:8000/api/system-status | python3 -m json.tool

# Response includes:
# - overall_status: "healthy" | "degraded" | "error"
# - services: { backend_api, database, webhook, cloudflare_tunnel, frontend }
# - summary: { healthy_services, total_services, health_percentage }

AI Developer Workflow (ADW)

The ADW system is a comprehensive automation framework that integrates GitHub issues with Claude Code CLI to classify issues, generate implementation plans, and automatically create pull requests. ADW processes GitHub issues by classifying them as /chore, /bug, or /feature commands and then implementing solutions autonomously.

Prerequisites

Before using ADW, ensure you have the following installed and configured:

• GitHub CLI: brew install gh (macOS) or equivalent for your OS
• Claude Code CLI: Install from Claude Code documentation
• Python with uv: curl -LsSf https://astral.sh/uv/install.sh | sh
• GitHub authentication: gh auth login

Environment Variables

Set these environment variables before running ADW:

export GITHUB_REPO_URL="https://github.com/owner/repository"
export ANTHROPIC_API_KEY="sk-ant-xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"
export CLAUDE_CODE_PATH="/path/to/claude"  # Optional, defaults to "claude"
export GITHUB_PAT="ghp_xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx"  # Optional, only if using different account than 'gh auth login'

Usage Modes

ADW supports three main operation modes:

1. Manual Processing

Process a single GitHub issue manually (in isolated worktree):

cd adws/
uv run adw_plan_build_iso.py <issue-number>

2. Automated Monitoring

Continuously monitor GitHub for new issues (polls every 20 seconds):

cd adws/
uv run trigger_cron.py

3. Webhook Server

Start a webhook server for real-time GitHub event processing:

cd adws/
uv run trigger_webhook.py

How ADW Works

1. Issue Classification: Analyzes GitHub issues and determines type (/chore, /bug, /feature)
2. Planning: Generates detailed implementation plans using Claude Code CLI
3. Implementation: Executes the plan by making code changes, running tests, and ensuring quality
4. Integration: Creates git commits and pull requests with semantic commit messages

For More Information

For detailed technical documentation, configuration options, and troubleshooting, see adws/README.md.

Troubleshooting

Backend won't start:

• Check Python version: python --version (requires 3.12+)
• Verify API keys are set: echo $OPENAI_API_KEY

Frontend errors:

• Clear node_modules: rm -rf node_modules && bun install
• Check Node version: node --version (requires 18+)

CORS issues:

• Ensure backend is running on port 8000
• Check vite.config.ts proxy settings