the-discoverer 1.0.1

AI-powered database discovery and query agent

The Discoverer

AI-powered database discovery and query agent supporting multiple SQL and NoSQL databases with vector database optimization for schema and content.

Author

Malek Khannoussi

• GitHub: @khannoussi-malek
• LinkedIn: khannoussi-malek

Features

• Multi-Database Support: PostgreSQL, MySQL, MongoDB, SQLite (extensible to Cassandra, Elasticsearch)
• Vector Database: Fast schema and content discovery using Qdrant
• AI-Powered Queries: Natural language to SQL/NoSQL query generation with pattern matching
• Performance Optimized:
  • Multi-layer caching (in-memory → Redis → Vector DB)
  • Parallel query execution across databases
  • Connection pooling per database
  • Batch operations for embeddings
• Hybrid Query Routing: Smart routing between content vector DB and schema-based queries
• Visualization: Automatic chart generation from query results using Plotly
• Clean Architecture: KISS, DRY principles with design patterns (Repository, Adapter, Factory, Strategy)

Quick Start

Prerequisites

• Python 3.10+
• Docker and Docker Compose (for services)
• OpenAI API key (for LLM features)

Installation

1. Clone the repository:

git clone https://github.com/khannoussi-malek/the-discoverer.git
cd the-discoverer

2. Install dependencies:

pip install -r requirements.txt

3. Set up environment variables:

cp .env.example .env
# Edit .env with your configuration

4. Start services with Docker Compose:

docker-compose up -d

5. Run the application:

uvicorn src.api.main:app --reload

6. (Optional) Install CLI tool:

pip install -e .
# Or use directly: python -m src.cli.main

CLI Usage

The Discoverer includes a command-line interface for easy interaction:

# Register a database
discoverer register --database-id db1 --type postgresql --host-db localhost --port 5432 --database mydb --user postgres --password pass

# List databases
discoverer list-databases

# Execute a query
discoverer query "Count all users" --format table

# Check health
discoverer health

# Sync database schema
discoverer sync db1

# Export query result
discoverer export query_id --format csv

Configuration

Environment Variables

See .env.example for all available configuration options.

Database Configuration

Configure databases in config/databases.yaml (copy from config/databases.yaml.example).

API Documentation

Once running, visit:

• API Docs: http://localhost:8000/docs
• ReDoc: http://localhost:8000/redoc

Quick Start Example

1. Register a database:

curl -X POST "http://localhost:8000/api/discovery/databases" \
  -H "Content-Type: application/json" \
  -d '{
    "id": "my_db",
    "type": "postgresql",
    "name": "My Database",
    "host": "localhost",
    "port": 5432,
    "database": "mydb",
    "user": "user",
    "password": "password"
  }'

2. Execute a query:

curl -X POST "http://localhost:8000/api/query/execute" \
  -H "Content-Type: application/json" \
  -d '{
    "query": "Show me all customers"
  }'

3. Index table content:

curl -X POST "http://localhost:8000/api/indexing/databases/my_db/tables/customers/index?strategy=smart"

Architecture

src/
├── domain/          # Business entities
├── application/     # Business logic
├── infrastructure/  # External dependencies
└── api/            # Presentation layer

Development

Using Makefile

make install      # Install dependencies
make dev          # Install dev dependencies
make test         # Run tests
make lint         # Run linters
make format       # Format code
make run          # Run application
make docker-up    # Start Docker services

Manual Commands

Running Tests

pytest

Code Quality

black src/
flake8 src/
mypy src/

Setup Vector DB

python scripts/setup_vector_db.py

Features Overview

Core Features

• ✅ Multi-database support (PostgreSQL, MySQL, SQLite, MongoDB, Cassandra, Elasticsearch)
• ✅ Vector database for fast schema/content discovery
• ✅ AI-powered query generation with pattern matching
• ✅ Hybrid query routing (content vector DB + schema-based)
• ✅ Automatic visualization generation
• ✅ Performance monitoring and statistics
• ✅ Comprehensive error handling
• ✅ Request logging
• ✅ Health checks
• ✅ Query streaming support
• ✅ Integration tests
• ✅ Query templates/saved queries
• ✅ Export functionality (CSV, JSON, Excel)
• ✅ Query result pagination
• ✅ Query optimization and analysis
• ✅ Batch query execution
• ✅ Circuit breaker for fault tolerance
• ✅ Performance benchmarking utilities
• ✅ Query scheduling (cron-like)
• ✅ Prometheus metrics
• ✅ Server-side pagination
• ✅ WebSocket support for real-time updates
• ✅ Query result transformation
• ✅ Analytics and usage tracking
• ✅ CLI Tool
• ✅ Enhanced Authentication (JWT, user management)
• ✅ Advanced visualization (10+ chart types)
• ✅ Python SDK (async and sync)
• ✅ Chart export (PNG, PDF, HTML, SVG)
• ✅ Query versioning
• ✅ Database health monitoring with automatic reconnection
• ✅ Schema change detection
• ✅ API key management and authentication
• ✅ Query result comparison
• ✅ JavaScript/TypeScript SDK
• ✅ Chart templates
• ✅ Parquet export
• ✅ Dashboard creation
• ✅ Query result sharing
• ✅ Query result caching strategies
• ✅ Cost tracking (LLM API usage)
• ✅ REST API webhooks
• ✅ Query result compression
• ✅ Database connection pooling per database
• ✅ Avro export
• ✅ Query result streaming improvements
• ✅ Export templates
• ✅ Scheduled exports

API Endpoints

• /api/discovery/* - Database discovery and management
• /api/query/* - Query execution (with pagination and streaming)
• /api/visualization/* - Chart generation
• /api/indexing/* - Content indexing
• /api/history/* - Query history and statistics
• /api/stats/* - Performance statistics
• /api/templates/* - Query templates (save, list, execute, search)
• /api/export/* - Export query results (CSV, JSON, Excel)
• /api/optimization/* - Query optimization and analysis
• /api/batch/* - Batch query execution
• /api/scheduler/* - Query scheduling (create, list, execute, pause, resume)
• /api/metrics/prometheus - Prometheus metrics endpoint
• /api/pagination/* - Server-side pagination for SQL queries
• /api/ws/* - WebSocket endpoints for real-time updates
• /api/transformation/* - Query result transformation
• /api/analytics/* - Usage analytics and statistics
• /api/auth/* - Authentication (register, login, user management)
• /api/api-keys/* - API key management (create, list, update, revoke, delete)
• /api/versioning/* - Query versioning and comparison
• /api/comparison/* - Query result comparison
• /api/chart-templates/* - Chart template management
• /api/dashboards/* - Dashboard creation and management
• /api/sharing/* - Query result sharing
• /api/cache/* - Cache management
• /api/cost-tracking/* - LLM cost tracking
• /api/webhooks/* - Webhook management
• /api/compression/* - Data compression utilities
• /api/pools/* - Connection pool management
• /api/export-templates/* - Export template management
• /api/scheduled-exports/* - Scheduled export management
• /health - Health check
• /health/databases - Database health status
• /health/databases/{id} - Individual database health

SDKs Available

• ✅ Python SDK - Async and sync clients (src/sdk/client.py, src/sdk/sync_client.py)
• ✅ JavaScript/TypeScript SDK - Browser and Node.js support (src/sdk/javascript/)

Additional Features

• ✅ Query history tracking and search
• ✅ Rate limiting (60 requests/minute)
• ✅ Configuration file loading (YAML)
• ✅ SQLite database support
• ✅ Request logging middleware
• ✅ Comprehensive error handling

Documentation

📚 📖 Documentation Index - Start here! Complete navigation guide to all documentation.

Quick Links

Getting Started:

• 📖 Documentation Index - Navigation hub for all docs
• 🚀 Getting Started Guide - Quick setup (5 minutes)
• 💡 Examples - Code samples and usage patterns

Core Documentation:

• 📡 API Reference - Complete API endpoint documentation
• 🏗️ Architecture - System architecture and design
• 🚢 Deployment Guide - Production deployment instructions

SDKs & Integration:

• 🐍 Python SDK - Python SDK documentation (async & sync)
• 🌐 JavaScript/TypeScript SDK - Browser and Node.js support
• ⚡ CLI Tool - Command-line interface

Feature Guides:

• 📊 Dashboards - Dashboard creation and management
• 📈 Chart Templates - Reusable chart configurations
• 🔗 Query Result Sharing - Share query results securely
• 🔔 Webhooks - Webhook configuration and usage
• ⏰ Scheduler - Query scheduling and automation
• 🔌 Connection Pools - Database connection pool management
• 🗜️ Compression - Data compression utilities
• 🌐 WebSocket - Real-time WebSocket support
• 📊 Metrics & Monitoring - Performance monitoring

See the Documentation Index for complete navigation and learning paths.

Quick Commands

# Load databases from config file
python scripts/load_databases.py config/databases.yaml

# Setup vector database
python scripts/setup_vector_db.py

# Index a database schema
python scripts/index_database.py <database_id>

Contributing

Contributions are welcome! Please feel free to submit a Pull Request.

License

MIT License

Copyright (c) 2024 Malek Khannoussi

Permission is hereby granted, free of charge, to any person obtaining a copy of this software and associated documentation files (the "Software"), to deal in the Software without restriction, including without limitation the rights to use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of the Software, and to permit persons to whom the Software is furnished to do so, subject to the following conditions:

The above copyright notice and this permission notice shall be included in all copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.