AI Driven - Self Service Dashboards

Project Technical Requirements
Objective
Develop an integrated data pipeline and analytics platform that enables automated data extraction, transformation, and loading (ELT) from an ERP system to a modern data warehouse, with an interactive dashboard and a natural language chat interface powered by a basic LLM.
________________________________________
1. ELT Pipeline
• Source Database:
• Type: MySQL 5
• Role: Primary ERP data source
• ETL Tool:
• Platform: Airbyte (open source)
• Function: Automated extraction and loading of data from MySQL to the target database
• Target Database:
• Type: PostgreSQL (latest stable version, optionally with TimescaleDB for time-series data)
• Role: Central data warehouse for analytics and transformations
• Transformation:
• Tool: dbt (core version)
• Function: SQL-based transformations to prepare data for analytics and visualization
• Scope: Data cleaning, aggregation, and preparation for dashboard consumption
________________________________________
2. Dashboard and Self-Service Analytics
• Frontend Framework:
• Platform: Next.js (latest version, using App Router)
• Language: TypeScript
• Visualization:
• Libraries: charjs and/or Recharts for interactive charts and graphs
• Features:
• Comparison graphs (time series, segment comparisons, etc.)
• Dynamic filtering and customizable visualizations
• Self-Service:
• Functionality:
• Users can select dimensions and measures
• Drag-and-drop interface for building and arranging charts
• Real-time data exploration with minimal IT support
• Performance:
• Caching: Implement Redis or similar for query result caching
• Rendering: Server-side rendering (SSR) and incremental static regeneration (ISR) for fast load times
• Real-time Updates: WebSocket integration for live data updates
________________________________________
3. LLM Chat Interface
• Model:
• Type: Basic large language model
• Integration: local deployment
• Query Engine:
• Framework: LangChain
• Function: Converts natural language queries into SQL and executes them against PostgreSQL
• Security:
• Validation: SQL parser with allow-list for permitted operations to prevent injection
• Scope: Users can ask questions in plain language and receive visualized answers
• User Experience:
• Interface: Chat-based, integrated into the Next.js dashboard
• Output: Visualized data and summarized insights based on user queries
________________________________________
4. Infrastructure and Deployment
• Deployment:
• Containerization: Docker for all major components (Airbyte, PostgreSQL, Next.js app)
• Orchestration: Docker Compose for local development, Kubernetes for production (optional)
• Monitoring:
• Pipeline: Airbyte monitoring and Grafana dashboards
• Application: New Relic or similar for application and database performance monitoring
• Security:
• Data: TLS encryption for all database connections
• Authentication: NextAuth.js with OAuth2 providers (Azure AD, Google)
• Authorization: Row-level security in PostgreSQL for data access control
• Audit: Database audit triggers and log archiving
________________________________________

Please provide examples of similar work done.
Delivery - Automates testing
Monitoring
Documentation
configuration on server
Source code - clean code
Training