Skip to content

Arpit-Jindal-01/Enterprise-Gen

BranchesTags

Repository files navigation

Enterprise GenAI Data Copilot

A Production-Grade Natural Language to SQL System with Enterprise Trust Layers

Python FastAPI Next.js License

🎯 Executive Summary

The Enterprise GenAI Data Copilot is a hackathon-ready, production-grade demonstration of how AI can democratize data access in enterprises. Non-technical users ask business questions in natural language and receive instant, trustworthy answers backed by validated SQL queries.

Key Differentiators

  • ✅ NOT a chatbot - This is an enterprise decision system
  • 🔒 Trust-first architecture with multi-layered validation
  • 🏢 Enterprise-grade security with read-only enforcement
  • 📊 Business intelligence with actionable insights
  • 🎨 Professional UI designed for corporate environments

🏗️ System Architecture

┌─────────────────────────────────────────────────────────────────┐
│                         FRONTEND                                 │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │  Next.js Dashboard (Enterprise UI)                       │   │
│  │  • Query Input  • SQL Display  • Trust Badges           │   │
│  │  • Result Panel • Insights     • Query History          │   │
│  └──────────────────────────────────────────────────────────┘   │
└───────────────────────────┬─────────────────────────────────────┘
                            │ REST API
┌───────────────────────────▼─────────────────────────────────────┐
│                    BACKEND (FastAPI)                             │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │              Query Orchestrator                          │   │
│  └──────────────────┬───────────────────────────────────────┘   │
│                     │                                            │
│  ┌──────────────────▼──────────────────────────────────────┐   │
│  │  1. Schema Grounding Layer (SchemaMapper)               │   │
│  │     → Maps business terms to database schema            │   │
│  └──────────────────┬───────────────────────────────────────┘   │
│                     │                                            │
│  ┌──────────────────▼──────────────────────────────────────┐   │
│  │  2. SQL Generator (LLM-powered)                         │   │
│  │     → Generates SQL with confidence scores              │   │
│  └──────────────────┬───────────────────────────────────────┘   │
│                     │                                            │
│  ┌──────────────────▼──────────────────────────────────────┐   │
│  │  3. SQL Validator (Trust Layer) ⚠️ CRITICAL             │   │
│  │     → Read-only enforcement                             │   │
│  │     → Schema validation                                 │   │
│  │     → SQL injection detection                           │   │
│  └──────────────────┬───────────────────────────────────────┘   │
│                     │                                            │
│  ┌──────────────────▼──────────────────────────────────────┐   │
│  │  4. Query Executor (Secure)                             │   │
│  │     → Timeout enforcement                               │   │
│  │     → Row limits                                        │   │
│  └──────────────────┬───────────────────────────────────────┘   │
│                     │                                            │
│  ┌──────────────────▼──────────────────────────────────────┐   │
│  │  5. Consistency Checker                                 │   │
│  │     → Statistical validation                            │   │
│  │     → Business rule checks                              │   │
│  └──────────────────┬───────────────────────────────────────┘   │
│                     │                                            │
│  ┌──────────────────▼──────────────────────────────────────┐   │
│  │  6. Insight Generator (LLM-powered)                     │   │
│  │     → Business explanations                             │   │
│  │     → Recommendations                                   │   │
│  └──────────────────────────────────────────────────────────┘   │
└───────────────────────────┬─────────────────────────────────────┘
                            │
┌───────────────────────────▼─────────────────────────────────────┐
│                   DATA LAYER                                     │
│  ┌──────────────────────────────────────────────────────────┐   │
│  │  SQLite Banking Database                                 │   │
│  │  • Customers (50 records)                               │   │
│  │  • Accounts (65+ records)                               │   │
│  │  •  Transactions (80+ records)                           │   │
│  │  • Loans (30 records)                                   │   │
│  └──────────────────────────────────────────────────────────┘   │
└─────────────────────────────────────────────────────────────────┘

🚀 Quick Start

Prerequisites

  • Python 3.9+
  • Node.js 18+
  • npm or yarn

Installation

1. Clone the Repository

cd enterprise-genai-copilot

2. Backend Setup

cd backend

# Create virtual environment
python -m venv venv
source venv/bin/activate  # On Windows: venv\Scripts\activate

# Install dependencies
pip install -r requirements.txt

# Initialize database
cd database
python init_db.py
cd ..

# Start backend server
python -m uvicorn app.main:app --reload

Backend will be available at http://localhost:8000
API docs at http://localhost:8000/api/docs

3. Frontend Setup

# In a new terminal
cd frontend

# Install dependencies
npm install

# Start development server
npm run dev

Frontend will be available at http://localhost:3000

4. Optional: Configure OpenAI API

Create .env file in the backend directory:

OPENAI_API_KEY=your-api-key-here
LLM_MODEL=gpt-4

Note: The system works in mock mode without an API key for demonstration purposes.

💡 Usage

Sample Business Questions

Try these questions in the dashboard:

Customer Analytics:

  • "How many active customers do we have?"
  • "How many high-risk customers are in our database?"
  • "What is the distribution of customers by state?"

Account Analysis:

  • "What is the total balance across all active accounts?"
  • "How many accounts by account type?"
  • "What is the average account balance?"

Transaction Intelligence:

  • "What is the average transaction amount in the last 30 days?"
  • "Show me transaction volume by type"
  • "What is the total transaction amount this month?"

Loan Portfolio:

  • "What is the total outstanding loan amount?"
  • "How many active loans do we have?"
  • "What is the average loan interest rate?"

Advanced Analytics:

  • "Who are the top 10 customers by total account balance?"
  • "Which customers have both loans and high account balances?"

🏆 Enterprise Features

Trust & Security Layers

  1. Schema Grounding

    • Prevents hallucination of non-existent tables/columns
    • Maps business terminology to technical schema
    • Provides context-aware SQL generation

  2. SQL Validation (Multi-layered)

    • ✅ Read-only enforcement (SELECT only)
    • ✅ Schema validation (tables/columns exist)
    • ✅ SQL injection detection
    • ✅ Dangerous pattern filtering
    • ✅ Column-level access control

  3. Query Execution Safety

    • Timeout enforcement (30s default)
    • Row count limits (10,000 default)
    • Execution metrics tracking
    • Connection pooling (production)

  4. Result Consistency Checking

    • Statistical anomaly detection
    • Business rule validation
    • Null value analysis
    • Range validation

  5. Trust Signals UI

    • Visual trust indicators
    • Confidence scores
    • Validation status
    • Execution metrics

Business Intelligence

  • Automated Insights: LLM generates executive-friendly explanations
  • Key Findings: Highlights critical observations
  • Caveats: Mentions limitations and considerations
  • Recommendations: Suggests next steps and actions

📂 Project Structure

enterprise-genai-copilot/
├── backend/
│   ├── app/
│   │   ├── main.py                 # FastAPI application
│   │   ├── config.py               # Configuration management
│   │   ├── models/
│   │   │   ├── schemas.py          # Pydantic models
│   │   │   └── database.py         # Database manager
│   │   ├── services/
│   │   │   ├── orchestrator.py     # Main coordinator
│   │   │   ├── schema_mapper.py    # Schema grounding
│   │   │   ├── sql_generator.py    # LLM SQL generation
│   │   │   ├── sql_validator.py    # Security validation
│   │   │   ├── query_executor.py   # Safe execution
│   │   │   ├── consistency_checker.py  # Result validation
│   │   │   └── insight_generator.py    # Business insights
│   │   ├── prompts/
│   │   │   ├── sql_generation.py   # SQL gen prompts
│   │   │   └── insight_generation.py  # Insight prompts
│   │   └── utils/
│   │       ├── logger.py           # Structured logging
│   │       └── auth.py             # Mock authentication
│   ├── database/
│   │   ├── schema.sql              # Database schema
│   │   ├── seed_data.sql           # Realistic data
│   │   └── init_db.py              # DB initialization
│   ├── tests/
│   │   ├── test_business_questions.py
│   │   ├── test_sql_validator.py
│   │   └── test_hallucination_scenarios.py
│   └── requirements.txt
│
├── frontend/
│   ├── src/
│   │   ├── app/
│   │   │   ├── page.tsx            # Main dashboard
│   │   │   ├── layout.tsx          # Root layout
│   │   │   └── globals.css         # Global styles
│   │   ├── components/
│   │   │   ├── Dashboard.tsx       # Main layout
│   │   │   ├── QueryInput.tsx      # Question input
│   │   │   ├── SQLPanel.tsx        # SQL display
│   │   │   ├── ResultPanel.tsx     # Results table
│   │   │   ├── ExplanationPanel.tsx  # Insights
│   │   │   ├── TrustBadges.tsx     # Trust indicators
│   │   │   └── QueryHistory.tsx    # History sidebar
│   │   ├── services/
│   │   │   └── api.ts              # API client
│   │   ├── types/
│   │   │   └── index.ts            # TypeScript types
│   │   └── lib/
│   │       └── utils.ts            # Utility functions
│   ├── package.json
│   └── tsconfig.json
│
├── docs/
│   ├── ARCHITECTURE.md
│   ├── API_DOCUMENTATION.md
│   └── DEMO_GUIDE.md
│
└── README.md

🧪 Testing

cd backend
pytest tests/ -v

Test coverage includes:

  • Business question accuracy
  • SQL validation security
  • Hallucination prevention
  • Edge case handling

📊 Database Schema

Customers

  • customer_id, full_name, email, phone
  • address, city, state, zip_code
  • customer_since, status, risk_rating

Accounts

  • account_id, customer_id, account_number
  • account_type (checking, savings, investment)
  • balance, status, opened_date, interest_rate

Transactions

  • transaction_id, account_id
  • transaction_type (deposit, withdrawal, transfer, payment)
  • amount, transaction_date, description, category

Loans

  • loan_id, customer_id, loan_number
  • loan_type (mortgage, personal, auto, business)
  • loan_amount, outstanding_balance, interest_rate
  • monthly_payment, start_date, maturity_date

🎨 UI Screenshots

Main Dashboard

Enterprise-style layout with query input, SQL panel, results, insights, and trust indicators.

Trust Badges

Visual indicators showing:

  • ✅ Schema Validated
  • 🔒 Read-Only Verified
  • ✓ Result Consistent
  • 👁️ Columns Whitelisted
  • ⚡ Execution Safe

🔐 Security Considerations

Production Deployment

  1. Authentication: Replace mock auth with OAuth/JWT
  2. API Keys: Secure storage in environment/secrets manager
  3. Database: Use PostgreSQL/MySQL with proper credentials
  4. Rate Limiting: Implement API rate limits
  5. Logging: Enhanced audit logging for compliance
  6. HTTPS: Enable TLS/SSL for all communications

📈 Scalability

Performance Optimizations

  • Caching: Redis for query results and schema metadata
  • Connection Pooling: SQLAlchemy for database connections
  • Async Processing: Background tasks for long queries
  • CDN: Frontend asset distribution
  • Load Balancing: Multiple backend instances

🤝 Contributing

This is a hackathon demo project. For production use:

  1. Add comprehensive error handling
  2. Implement user authentication
  3. Add query result caching
  4. Enhanced LLM prompt optimization
  5. Multi-database support
  6. Advanced analytics dashboard

📝 License

MIT License - See LICENSE file for details

🏅 Hackathon Evaluation Points

Technical Excellence

  • ✅ Production-grade architecture
  • ✅ Multi-layered trust system
  • ✅ Comprehensive testing
  • ✅ Clean, documented code
  • ✅ Enterprise UI/UX

Innovation

  • ✅ Schema grounding prevents hallucinations
  • ✅ Consistency checking validates results
  • ✅ Trust-first design for enterprises
  • ✅ Business insight generation

Business Impact

  • ✅ Democratizes data access
  • ✅ Reduces analyst workload
  • ✅ Accelerates decision-making
  • ✅ Maintains data governance

Completeness

  • ✅ Full-stack implementation
  • ✅ Realistic demo data
  • ✅ Test coverage
  • ✅ Comprehensive documentation

📞 Support

For questions or issues, please refer to the documentation in /docs or check the API documentation at /api/docs when the backend is running.

Built with ❤️ for Enterprise AI Innovation

About

No description, website, or topics provided.

Resources

Readme

License

MIT license
Activity

Stars

0 stars

Watchers

0 watching

Forks

0 forks
Report repository

Releases

No releases published

Packages

 
 
 

Contributors

Languages