AI-Database-with-Kafka-ELK-Stack-n-Prometheus

AI-Database-with-Kafka-ELK-Stack-n-Prometheus

A sophisticated system integrating MySQL, Apache Kafka, ELK Stack, and Prometheus with Claude/Cursor API to enable conversational interaction with a database. This system leverages cutting-edge DevOps practices for real-time data streaming, monitoring, and observability.

---

🏗️ Project Overview

This project enables users to chat with a MySQL database via an AI-powered interface. It eliminates the need to write SQL queries by leveraging natural language processing (NLP). Real-time data flow is managed by Kafka, with ELK Stack handling log management, and Prometheus tracking performance metrics.

✨ Key Features

• Conversational Interaction — Query the database using natural language via Claude/Cursor API.
• Real-Time Messaging — Apache Kafka streams interactions and system events.
• Robust Log Management — ELK Stack (Elasticsearch, Logstash, Kibana) for comprehensive logging.
• Performance Monitoring — Prometheus collects metrics for health and alerting.
• Scalable & Containerized — Dockerized microservices for easy deployment.

---

📁 Folder Structure

├── /                   # Root Directory (Contains all files)
├── /prometheus.yml      # Prometheus Configuration
├── /mcp.json            # Claude/Cursor API & DB Configuration
├── /docker-compose.yml  # Docker Compose File
├── /README.md           # Project Documentation

---

🔑 Environment Variables

Set the following environment variables in mcp.json or docker-compose.yml for the system to work seamlessly.

Variable	Description
AI_API_KEY	Your Claude/Cursor API Key for NLP.
MYSQL_ROOT_PASSWORD	Root password for MySQL database.
MYSQL_USER	Database user for login.
MYSQL_PASSWORD	Password for the MySQL user.
KAFKA_BROKER	Kafka broker URL for real-time messaging.
PROMETHEUS_PORT	Monitoring port (default: 9090).
KAFKA_ZOOKEEPER_CONNECT	Zookeeper connection string.

✅ Example mcp.json Configuration

{
  "api_key": "<INSERT_CLAUDE_OR_CURSOR_API_KEY>",
  "mysql_host": "mysql",
  "mysql_user": "chat_user",
  "mysql_password": "chat_pass",
  "kafka_broker": "kafka:9092"
}

---

🚀 How to Run the Project

1. Clone the Repository

git clone <repository-url>
cd AI-Database-with-Kafka-ELK-Stack-n-Prometheus

2. Set Up Environment Variables

Ensure your mcp.json file is correctly configured with API keys and database credentials.

3. Run Docker Containers

Build and start all services with a single command:

docker-compose up --build

✅ Access Services:

• Prometheus Dashboard: http://localhost:9090
• Kibana Dashboard: http://localhost:5601
• Kafka Broker: http://localhost:9092
• MySQL: localhost:3306

---

🔍 System Architecture

1. User Interaction:
   • User interacts via a chat interface powered by Claude/Cursor API.
2. AI Processing:
   • AI understands and processes natural language queries.
3. Kafka Streaming:
   • Apache Kafka handles message streaming for real-time processing.
4. MySQL Database:
   • Database is queried based on processed NLP output.
5. ELK Stack:
   • Elasticsearch, Logstash, and Kibana manage and visualize logs.
6. Prometheus:
   • Tracks system health and performance metrics.

---

⚙️ Docker-Compose.yml

version: "3.9"
services:
  
  # MySQL Database
  mysql:
    image: mysql:8.0
    container_name: mysql_db
    restart: always
    environment:
      MYSQL_ROOT_PASSWORD: chat_pass
      MYSQL_USER: chat_user
      MYSQL_PASSWORD: chat_pass
      MYSQL_DATABASE: chat_db
    ports:
      - "3306:3306"
    volumes:
      - mysql_data:/var/lib/mysql

  # Kafka Broker
  kafka:
    image: wurstmeister/kafka:2.13-2.7.0
    container_name: kafka_broker
    environment:
      KAFKA_BROKER_ID: 1
      KAFKA_ZOOKEEPER_CONNECT: zookeeper:2181
      KAFKA_ADVERTISED_LISTENERS: PLAINTEXT://kafka:9092
    ports:
      - "9092:9092"
    depends_on:
      - zookeeper

  # Zookeeper
  zookeeper:
    image: wurstmeister/zookeeper
    container_name: zookeeper
    ports:
      - "2181:2181"

  # Elasticsearch
  elasticsearch:
    image: docker.elastic.co/elasticsearch/elasticsearch:7.17.10
    container_name: elasticsearch
    environment:
      - discovery.type=single-node
    ports:
      - "9200:9200"

  # Logstash
  logstash:
    image: docker.elastic.co/logstash/logstash:7.17.10
    container_name: logstash
    ports:
      - "5044:5044"
    depends_on:
      - elasticsearch

  # Kibana
  kibana:
    image: docker.elastic.co/kibana/kibana:7.17.10
    container_name: kibana
    ports:
      - "5601:5601"
    depends_on:
      - elasticsearch

  # Prometheus
  prometheus:
    image: prom/prometheus:latest
    container_name: prometheus
    volumes:
      - ./prometheus.yml:/etc/prometheus/prometheus.yml
    ports:
      - "9090:9090"

volumes:
  mysql_data:

---

📊 Monitoring with Prometheus

• Access: http://localhost:9090
• Metrics Tracked:
  • Kafka message throughput.
  • MySQL query performance.
  • API latency and system health.

---

🚦 Kafka Message Flow

1. Producer (Chat Interface):
   • Sends user messages to Kafka.
2. Kafka Broker:
   • Queues and streams messages.
3. Consumer:
   • Consumes messages, queries MySQL, and sends responses.

---

🏗️ Future Enhancements

• Add Grafana for advanced visualization.
• Integrate Redis for caching frequent queries.
• Implement Auto-Scaling for high load management.

---

❓ Troubleshooting

• Port Conflicts?
  • Stop running containers using conflicting ports.
• Kafka Not Running?
  • Ensure Zookeeper is running before Kafka.
• Prometheus Not Scraping?
  • Check prometheus.yml configuration.

---

✅ Enjoy building your real-time, AI-powered database interaction system! 🚀
Feel free to contribute or reach out with questions.