DBA Admin Centralized

A comprehensive platform to centralize and analyze database information with AI-powered Q&A, Watson Assistant integration, and modern webhook automation. Built with Node.js, MongoDB, Vue.js, and Ollama LLM integration.

🚀 Features

• 🤖 AI-Powered Database Analytics - Ask questions about your databases using Llama 3.1 8B
• 🔗 Watson Assistant Integration - Webhook endpoints for automated actions
• 🔐 Secure Authentication - Basic Auth with environment-based credentials
• 📊 Centralized Database Management - View and manage database information by hostname
• ⚡ Real-time Health Monitoring - Monitor database status and performance
• 🧩 Modular LLM Architecture - Support for multiple AI providers
• 🎨 Modern UI - Clean, responsive interface with IBM Carbon Design
• 🐳 Containerized Deployment - Docker Compose for easy deployment
• 🔌 Webhook Automation - Execute scripts and create issues via API

📁 Project Structure

dba-admin-centralized/
├── backend/                 # Node.js API with Express
│   ├── controllers/         # API controllers
│   ├── middleware/          # Auth, rate limiting, error handling
│   ├── routes/             # API routes
│   ├── services/           # Core business logic
│   │   ├── core/          # Database, query, orchestration
│   │   ├── llm/           # LLM integration
│   │   └── sql/           # SQL parsing and validation
│   └── openapi-webhook.json # Watson Assistant API spec
├── frontend/               # Vue.js application
│   ├── src/
│   │   ├── components/    # Vue components
│   │   ├── views/         # Page views
│   │   └── utils/         # API utilities
│   └── .env               # Frontend environment variables
├── scripts/               # Deployment and management scripts
├── nginx/                 # Reverse proxy configuration
└── docker-compose.yml     # Container orchestration

🚀 Quick Start

Prerequisites

• Docker and Docker Compose
• Ollama installed locally
• macOS, Linux, or Windows with WSL2

One-Command Deployment

# Start everything
docker-compose down && docker-compose up -d --build

# Stop everything
docker-compose down

🔐 Authentication

The system uses Basic Authentication with environment variables:

Backend (.env)

API_USER=your_username
API_TOKEN=your_secure_token
PORT=5002
MONGODB_URI=mongodb://localhost:27017/dba-admin
LLM_PROVIDER=ollama
OLLAMA_BASE_URL=http://localhost:11434
OLLAMA_MODEL=llama3.1:8b

Frontend (.env)

VITE_API_USER=your_username
VITE_API_TOKEN=your_secure_token

📡 API Endpoints

Database Management

# Get all databases
curl -u username:token \
  https://your-domain.com/api/databases

# Get database by hostname
curl -u username:token \
  https://your-domain.com/api/databases/{hostname}

# Create database
curl -u username:token \
  -X POST -H "Content-Type: application/json" \
  -d '{"hostname":"new-server","ip":"192.168.1.100","type":"PostgreSQL","version":"15.2","region":"US-East","dataCenter":"DC3"}' \
  https://your-domain.com/api/databases

# Update database
curl -u username:token \
  -X PUT -H "Content-Type: application/json" \
  -d '{"hostname":"updated-server","ip":"192.168.1.101","type":"PostgreSQL","version":"15.3","region":"US-East","dataCenter":"DC3"}' \
  https://your-domain.com/api/databases/{hostname}

# Delete database
curl -u username:token \
  -X DELETE \
  https://your-domain.com/api/databases/{hostname}

AI-Powered Q&A

# Ask questions about your databases
curl -u username:token \
  -X POST -H "Content-Type: application/json" \
  -d '{"question":"How many databases do we have?"}' \
  https://your-domain.com/api/qa

Watson Assistant Webhooks

# Execute scripts
curl -u username:token \
  -X POST -H "Content-Type: application/json" \
  -d '{"script":"database_backup","env":"prod"}' \
  https://your-domain.com/api/run-script

# Create Jira issues
curl -u username:token \
  -X POST -H "Content-Type: application/json" \
  -d '{"script":"create_issue","summary":"High CPU Usage","description":"Database server showing high CPU","assignee":"admin@company.com"}' \
  https://your-domain.com/api/create-issue

🤖 Watson Assistant Integration

OpenAPI Specification

The project includes backend/openapi-webhook.json for Watson Assistant integration:

• Webhook endpoints for automated actions
• Basic Auth security
• Script execution capabilities
• Issue creation workflows

Integration Flow

1. User asks Watson for an action
2. Watson calls webhook with parameters
3. Backend executes the requested action
4. Result returned to Watson
5. Watson responds to user

Example Watson Actions

• "Create a backup of the production database"
• "Create a Jira ticket for high CPU usage"
• "Restart the database service"
• "Check database status"

🎯 Usage Examples

Database Analytics Questions

• "Which region has the most databases?"
• "How many PostgreSQL databases are in EU-West?"
• "Show me all Oracle databases"
• "What's the distribution of database types?"
• "Which datacenter has the most Redis instances?"
• "List all databases with version 21c"
• "How many databases per region?"
• "Compare PostgreSQL vs MySQL counts"

Webhook Automation

• Database backups
• Service restarts
• Issue creation
• Monitoring alerts
• Performance checks

🏗️ Architecture

Services

• Frontend: Vue.js application (port 80)
• Backend: Node.js API with LLM integration (port 4000)
• Database: MongoDB (port 27017)
• Ollama: LLM service (port 11434)

Security Features

• Basic Authentication for all API endpoints
• Rate limiting to prevent abuse
• Environment-based credentials
• CORS protection for web requests
• Input validation and sanitization

LLM Integration

• Modular architecture supporting multiple providers
• Ollama integration for local LLM processing
• Context-aware responses
• SQL generation from natural language
• Error handling and fallbacks

🔧 Configuration

Environment Variables

Backend (.env)

API_USER=your_username
API_TOKEN=your_secure_token
PORT=5002
MONGODB_URI=mongodb://
LLM_PROVIDER=ollama
OLLAMA_BASE_URL=http://
OLLAMA_MODEL=llama3.1:8b

Frontend (.env)

VITE_API_USER=your_username
VITE_API_TOKEN=your_secure_token

Model Options

Available models for Ollama:

• llama3.1:8b (default) - Fast, good quality
• llama3.1:70b - Higher quality, slower
• codellama:7b - Code-focused responses
• mistral:7b - Alternative model

🛠️ Development

Backend Development

cd backend
npm install
npm run dev

Frontend Development

cd frontend
npm install
npm run dev

Testing API Endpoints

# Test authentication
curl -u username:token \
  https://your-domain.com/api/databases

# Test Q&A
curl -u username:token \
  -X POST -H "Content-Type: application/json" \
  -d '{"question":"How many databases do we have?"}' \
  https://your-domain.com/api/qa

🚨 Troubleshooting

Authentication Issues

# Check credentials format
echo "username:token" | base64

# Test Basic Auth
curl -H "Authorization: Basic $(echo -n 'username:token' | base64)" \
  https://your-domain.com/api/databases

LLM Service Issues

# Check Ollama status
curl http://localhost:11434/api/tags

# Restart Ollama
pkill ollama && ollama serve

# Check backend LLM status
curl -u username:token \
  https://your-domain.com/api/llm/status

Docker Issues

# Rebuild containers
docker-compose down
docker-compose up -d --build

# View logs
docker-compose logs -f

# Check container status
docker-compose ps

📊 Performance

Optimization Features

• Caching for database queries
• Connection pooling for MongoDB
• Rate limiting to prevent abuse
• Compression for API responses
• Efficient LLM processing

Monitoring

• Health checks for all services
• Error logging and tracking
• Performance metrics collection
• Resource usage monitoring

🔒 Security

Authentication

• Basic Auth for all API endpoints
• Environment-based credentials
• No hardcoded secrets in code
• Secure credential management

Data Protection

• Input validation and sanitization
• SQL injection prevention
• XSS protection in frontend
• CORS configuration for web security

Security Best Practices

• Use strong, unique credentials for each environment
• Rotate credentials regularly
• Use HTTPS in production
• Never commit .env files to version control
• Implement proper logging and monitoring
• Consider OAuth 2.0 for production deployments

🤝 Contributing

1. Fork the repository
2. Create a feature branch
3. Make your changes
4. Add tests if applicable
5. Submit a pull request

📄 License

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

---