Skip to content
BranchesTags

Repository files navigation

NLP Engine for Employee Data

A powerful natural language processing application that enables users to query database information using plain English. This comprehensive tool integrates cutting-edge AI technology with robust database connectivity to provide intuitive data exploration and analysis capabilities.

Table of Contents

Overview

The NLP Engine for Employee Data is an intelligent interface that bridges the gap between non-technical users and complex database systems. Built on a modern tech stack featuring React, TypeScript, and Google's Gemini AI, it enables users to:

  • Query complex databases using natural language
  • Visualize database schema and relationships
  • Generate instant, professional data visualizations
  • Receive AI-powered suggestions for analysis
  • Upload and process file-based data alongside databases
  • Securely manage multiple database connections

This application is ideal for HR professionals, data analysts, managers, and anyone who needs to work with employee data without writing SQL queries manually.

Key Features

  • Natural Language Queries: Ask questions about your data in plain English without writing SQL
  • Multi-Database Support: Connect to MySQL, PostgreSQL, SQLite, and SQL Server databases
  • Schema Discovery and Visualization: Automatically analyze and visualize database structures
  • AI-Powered Analysis: Use Google's Gemini AI to convert natural language to SQL and provide insights
  • Smart Query Caching: Performance optimizations for frequently asked questions
  • File-Based Data Processing: Upload and analyze CSV, JSON, and other structured data files
  • Real-time Data Visualization: Generate charts, graphs and tables from query results
  • Secure Architecture: Dedicated Node.js server handles all database connections safely
  • Enterprise-Grade Security: Rate limiting, CORS protection, and SQL injection prevention
  • Multi-Project Management: Create and manage multiple projects with different data sources
  • Chat Interface: Conversational UI for intuitive data exploration
  • Dark/Light Mode: Customizable UI theme for comfortable viewing
  • Cross-Platform Compatibility: Works on Linux, macOS, and Windows environments

System Architecture

The application is structured using a modern client-server architecture:

Frontend Layer

  • UI Components: React-based interface with TypeScript for type safety
  • State Management: React hooks with local storage persistence
  • API Communication: Fetch API for communication with backend server
  • Data Visualization: Dynamic rendering of query results in various formats
  • Theming System: Customizable light/dark mode with Tailwind CSS

Backend Layer

  • API Server: Express.js server handling HTTP requests
  • Connection Management: Secure pooling and management of database connections
  • Query Processing: Translation of natural language to SQL with AI assistance
  • Schema Analysis: Automatic detection of tables, columns, and relationships
  • Security Middleware: Rate limiting, CORS, and input validation
  • Error Handling: Comprehensive error capture and client-friendly responses

AI Integration Layer

  • Gemini API: Integration with Google's Gemini AI model
  • Context Building: Conversation history management for contextual understanding
  • Query Generation: Conversion of natural language to SQL queries
  • Error Recovery: Intelligent handling of query errors and suggestion generation

Technology Stack

Frontend

  • Framework: React 19 with TypeScript
  • Build Tool: Vite for fast development and optimized builds
  • Styling: Tailwind CSS for responsive design
  • UI Components: Custom component library with responsive design

Backend

  • Runtime: Node.js with Express
  • Database Drivers: MySQL2, PostgreSQL, SQLite3, and SQL Server
  • Security: Helmet, Express Rate Limit, CORS
  • Utilities: UUID for ID generation, nodemon for development

AI Services

  • NLP Engine: Google Gemini API
  • Text Processing: Custom context management and prompt engineering
  • Query Analysis: Pattern recognition for SQL generation

Installation

Prerequisites

  • Node.js: Version 18 or higher
  • NPM: Version 8 or higher
  • Databases: Any of MySQL, PostgreSQL, SQLite, or SQL Server (optional)
  • API Key: Google Gemini API key for AI functionality

Quick Start

Linux/macOS

# Clone the repository
git clone https://github.com/rinu143/NLP-Engine-for-employee-data.git
cd NLP-Engine-for-employee-data

# Start the application
./start.sh

Windows

# Clone the repository
git clone https://github.com/rinu143/NLP-Engine-for-employee-data.git
cd NLP-Engine-for-employee-data

# Start the application
start.bat
# Or for simpler display without colors:
start-plain.bat

Manual Setup

If you prefer to set up manually:

  1. Install Dependencies
# Install frontend dependencies
npm install

# Install server dependencies
cd server
npm install
cd ..
  1. Environment Setup Create a .env file in the root directory:
VITE_GEMINI_API_KEY=your_gemini_api_key_here
  1. Start Services
# Terminal 1: Start database server
cd server
npm run dev

# Terminal 2: Start frontend
npm run dev
  1. Access the Application Open your browser and navigate to http://localhost:3001

Usage Guide

Creating a Project

  1. Launch the Application: After starting the application, you'll be presented with a login screen. Click "Sign in with Google" (mock login for demonstration purposes).

  2. Create a New Project: Click the "New Project" button in the sidebar.

  3. Name Your Project: Click on the project name to edit it and give it a meaningful name related to your data.

  4. Project Organization: The application allows you to manage multiple projects, each with its own database connections, files, and chat history.

Connecting to Databases

  1. Database Panel: Navigate to the database panel in your project.

  2. Connection String: Enter a connection string for your database. The system supports:

    • MySQL: mysql://username:password@hostname:port/database
    • PostgreSQL: postgresql://username:password@hostname:port/database
    • SQLite: sqlite:///path/to/database.db
    • SQL Server: mssql://username:password@hostname:port/database

  3. Connection Test: The system automatically tests the connection and displays the status.

  4. Schema Discovery: Upon successful connection, the application automatically discovers and displays your database schema.

Working with Files

  1. File Upload: If you don't have a database, you can upload structured data files (CSV, JSON, etc.).

  2. Schema Inference: The system automatically analyzes uploaded files to infer their structure.

  3. File Management: Uploaded files appear in the Files panel where they can be viewed, analyzed, or removed.

Natural Language Queries

  1. Chat Interface: Use the chat panel to interact with your data using natural language.

  2. Query Examples:

    • "Show me all employees in the Sales department"
    • "What's the average salary by department?"
    • "Who has been working here for more than 5 years?"
    • "Show me a breakdown of employees by job title"

  3. Context Awareness: The system maintains conversation context, so you can ask follow-up questions.

Data Visualization

  1. Automatic Visualization: Query results are automatically rendered in the most appropriate format (tables, charts).

  2. Customization: Some visualizations can be customized by clicking on elements or using the visualization controls.

  3. Export Options: Data can be exported for use in other applications.

Database Support

MySQL

mysql://username:password@hostname:port/database
mysql://root:password@localhost:3306/employees_db

PostgreSQL

postgresql://username:password@hostname:port/database
postgres://username:password@hostname:port/database

SQLite

sqlite:///absolute/path/to/database.db
sqlite://./relative/path/to/database.db

SQL Server

mssql://username:password@hostname:port/database
sqlserver://username:password@hostname:port/database

Security Features

The NLP Engine incorporates multiple layers of security:

  1. Database Connection Security:

    • Connection strings are never stored in plain text in the browser
    • All database operations go through a secure server layer
    • Read-only operations by default, with no data modification capabilities

  2. API Security:

    • Rate limiting to prevent abuse
    • CORS protection
    • Helmet.js for HTTP header security

  3. Input Validation:

    • SQL injection prevention
    • Input sanitization for all user inputs
    • Prepared statements for database queries

  4. Error Handling:

    • Secure error messages that don't leak implementation details
    • Comprehensive server-side logging
    • Graceful failure recovery

  5. Authentication & Session Management:

    • Secure logout functionality with complete cache and storage clearing
    • Session data management through localStorage
    • Mock authentication system (can be extended to real authentication)

Configuration Options

Environment Variables

The application uses environment variables for configuration:

  • VITE_GEMINI_API_KEY: Your Google Gemini API key
  • PORT: Server port (defaults to 3002 if not specified)

Server Configuration

The backend server's behavior can be customized through:

  • Rate limiting parameters in server.js
  • CORS settings for controlling allowed origins
  • Database connection timeouts and pool settings

Frontend Preferences

User preferences that persist across sessions:

  • Selected theme (light/dark mode)
  • Active project selection
  • Chat history and context

Troubleshooting

Common Issues

  1. Connection Errors:

    • Verify your database is running and accessible
    • Check connection string format for proper escaping of special characters
    • Ensure database user has appropriate permissions

  2. API Key Issues:

    • Verify your Gemini API key is correctly set in the .env file
    • Check for API usage limits or restrictions

  3. Performance Problems:

    • Large databases may require more time for schema discovery
    • Complex queries might take longer to process
    • Consider clearing the cache if responses seem stale

Logging

The application maintains logs at multiple levels:

  • Frontend console logs for UI interactions
  • Server logs for database operations and errors
  • Performance metrics tracked per project

Development

Project Structure

NLP-Engine-for-employee-data/
├── components/               # React UI components
│   ├── ChatPanel.tsx         # Chat interface component
│   ├── Dashboard.tsx         # Main application dashboard
│   ├── DatabasePanel.tsx     # Database connection management
│   ├── DataVisualizer.tsx    # Data visualization component
│   ├── FilePanel.tsx         # File upload and management
│   ├── FormattedContent.tsx  # Content formatting utilities
│   ├── LibraryView.tsx       # Library/resource browser
│   ├── LoginPage.tsx         # Authentication component
│   ├── SchemaViewer.tsx      # Database schema visualization
│   └── Sidebar.tsx           # Application navigation sidebar
├── server/                   # Backend server components
│   ├── server.js             # Express server main file
│   ├── services/             # Server-side services
│   │   ├── ConnectionManager.js  # Database connection pooling
│   │   └── DatabaseService.js    # Database operations
│   └── local_database.db     # Default SQLite database
├── services/                 # Frontend services
│   ├── databaseService.ts    # Frontend DB communication service
│   └── geminiService.ts      # AI service integration
├── App.tsx                   # Main React application
├── constants.ts              # Application constants
├── index.html                # HTML entry point
├── index.tsx                 # Application entry point
├── package.json              # Frontend dependencies
├── start.sh                  # Linux/macOS startup script
├── start.bat                 # Windows startup script
├── start-plain.bat           # Plain text Windows startup script
├── types.ts                  # TypeScript type definitions
└── vite.config.ts            # Vite build configuration

Building from Source

To build the application from source:

# Build the frontend
npm run build

# The output will be in the dist/ directory
# Serve the built application with a static server
npm run preview

Running Tests

# Run frontend tests
npm test

# Run server tests
cd server
npm test

Contributing

Contributions are welcome! Here's how you can contribute to the project:

  1. Fork the repository
  2. Create a feature branch (git checkout -b feature/amazing-feature)
  3. Make your changes
  4. Commit your changes (git commit -m 'Add some amazing feature')
  5. Push to the branch (git push origin feature/amazing-feature)
  6. Open a Pull Request

License

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

Acknowledgments

  • Google Gemini API for natural language processing capabilities
  • The React and TypeScript teams for their excellent frameworks
  • The open-source database driver communities for SQLite, MySQL, PostgreSQL, and SQL Server

### SQLite
```bash
sqlite:////absolute/path/to/database.db
sqlite:////tmp/skillsync_enterprise.db
sqlite://./relative/path/database.db

SQL Server

mssql://username:password@hostname:port/database
sqlserver://sa:password@localhost:1433/CompanyDB

💬 Example Queries

Basic Queries

  • "Show me all employees in the IT department"
  • "List employees with salary greater than 75000"
  • "Find all managers and their contact information"

Analytics

  • "What's the average salary by department?"
  • "Count employees hired each year"
  • "Show department with highest turnover rate"

Complex Analysis

  • "Find the top 5 highest paid employees with their managers"
  • "Show salary distribution across all departments"
  • "List employees hired in the last 6 months with their positions"

�️ API Endpoints

The database server provides these endpoints:

Connection Management

  • POST /api/test-connection - Test database connection
  • POST /api/close-connection - Close database connection
  • GET /api/connections - List active connections

Database Operations

  • POST /api/discover-schema - Discover database schema
  • POST /api/execute-query - Execute SELECT queries

Monitoring

  • GET /health - Server health check

🔒 Security Features

  • Rate Limiting: 100 requests per 15 minutes per IP
  • CORS Protection: Configured for development environments
  • SQL Injection Prevention: Only SELECT queries allowed
  • Connection Limits: Maximum 50 concurrent connections
  • Auto Cleanup: Connections expire after 30 minutes
  • Input Validation: All parameters validated and sanitized

📊 Monitoring & Logging

The server provides comprehensive logging:

Connection Events

Successfully connected to mysql database: employees_db
Schema extracted: 8 tables, 45 columns, 6 tables with relationships
Query executed successfully: 25 rows returned

Error Tracking

  • Connection failures with detailed error messages
  • Query execution errors and timeouts
  • Schema discovery issues
  • Rate limiting and security events

🚦 Development

Available Scripts

Frontend:

  • npm run dev - Start development server
  • npm run build - Build for production
  • npm run preview - Preview production build

Server:

  • npm run dev - Start server with auto-reload
  • npm start - Start server in production mode

Project Structure

├── components/              # React components
│   ├── ChatPanel.tsx       # Natural language interface
│   ├── Dashboard.tsx       # Main application
│   ├── DatabasePanel.tsx   # Connection management
│   └── ...                 # Other UI components
├── services/               # Frontend services
│   ├── databaseService.ts  # Server communication
│   └── geminiService.ts    # AI integration
├── server/                 # Backend server
│   ├── server.js          # Express application
│   ├── services/          # Database services
│   │   ├── DatabaseService.js    # Database operations
│   │   └── ConnectionManager.js  # Connection pooling
│   └── package.json       # Server dependencies
├── types.ts               # TypeScript definitions
├── start.sh              # Startup script
└── README.md             # This file

🐛 Troubleshooting

Connection Issues

Database Server Not Running:

Error: Cannot connect to database server at http://localhost:3002
Solution: Make sure the server is running with `cd server && npm run dev`

Database Connection Failed:

Error: Failed to connect to mysql database: Access denied
Solution: Verify credentials and database permissions

SQLite File Access:

Error: SQLite database file not accessible
Solution: Check file path and permissions, use absolute paths

Performance Issues

Slow Schema Discovery:

  • Large databases may take 10-15 seconds
  • Check database performance and indexes
  • Consider connection timeout settings

Query Timeouts:

  • Complex queries have 30-second timeout
  • Optimize queries or increase timeout
  • Check database server performance

Development Issues

Port Conflicts:

  • Frontend: Change port in vite.config.ts
  • Server: Set PORT environment variable

CORS Errors:

  • Check server CORS configuration
  • Verify frontend is calling correct server URL

🔄 Environment Variables

Frontend (.env)

VITE_GEMINI_API_KEY=your_api_key

Server (optional)

PORT=3002
NODE_ENV=production
MAX_CONNECTIONS=50
CONNECTION_TIMEOUT=1800000

🤝 Contributing

  1. Fork the repository
  2. Create feature branch (git checkout -b feature/amazing-feature)
  3. Commit changes (git commit -m 'Add amazing feature')
  4. Push to branch (git push origin feature/amazing-feature)
  5. Open Pull Request

📄 License

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

🚧 Roadmap

  • Database Support: MongoDB, Oracle, Redis integration
  • Advanced Analytics: Data visualization dashboard
  • Query Management: History, saved queries, templates
  • Multi-tenancy: User authentication and data isolation
  • Export Features: CSV, Excel, PDF report generation
  • Real-time Data: WebSocket streaming for live updates
  • Performance: Query optimization suggestions
  • Deployment: Docker containers and cloud deployment guides

📞 Support

  • 🐛 Issues: Create GitHub issues for bugs
  • 💡 Features: Request features via GitHub discussions
  • 📖 Documentation: Check server README in /server/README.md
  • 🔧 Development: Review troubleshooting section above

Made with ❤️ for better data accessibility

About

No description, website, or topics provided.

Resources

Readme
Activity

Stars

1 star

Watchers

0 watching

Forks

1 fork
Report repository

Releases

Packages

Contributors

Languages