clever-system

bySatyam Maravaniya

GenBI System – Build Specification 🎯 Objective I want to build a system where: User uploads a CSV file File is stored inside PostgreSQL (Docker-based PostgreSQL container) User interacts via chat AI analyzes data, gives insights, answers queries AI can modify data (add/remove/update rows or columns) All changes are tracked User can revert (undo) any action System maintains full audit trail This must be built as a production-ready architecture. 🧠 Core Functional Requirements 1️⃣ File Upload & Storage User uploads CSV Backend parses CSV Create table dynamically in PostgreSQL Store original dataset Maintain version history table Use Docker-based PostgreSQL image 2️⃣ Chat-Based Data Interaction User can: Ask questions: Ask for insights: Trends Aggregations Outliers Ask for modifications: "Remove duplicates" "Delete rows where amount < 0" "Add new column profit = revenue - cost" "Update all dates to 2024 format" System must: Generate SQL or Python code Execute safely Update database Return result 3️⃣ Data Versioning & Revert Every modification creates new version Store: Previous state SQL executed Timestamp User action User can: Revert to any previous version View change history Maintain audit log table 4️⃣ Agentic Architecture Use: LangGraph Tool-based architecture Google Gemini API key The AI must act as: Data Analyst + Data Engineer Agent Agent Flow: Understand user query Decide which tool to use Execute tool Validate output Return response 🛠 Required Tools (LangGraph Tools) Create tools: Schema Inspection Tool SQL Query Tool Data Cleaning Tool Aggregation Tool Data Update Tool Version Control Tool Revert Tool Insight Generation Tool Each tool: Executes structured logic Returns structured output Logs operation 🏗 Technical Stack Backend Python FastAPI LangGraph Google Gemini API (API Key) PostgreSQL SQLAlchemy Docker Frontend Node.js (or Next.js) Chat Interface File Upload UI Version History Panel Revert Button Data Table Preview 🐳 Infrastructure Docker Setup Create: Dockerfile (Backend) Dockerfile (Frontend) Dockerfile (PostgreSQL optional if needed) docker-compose.yml Docker Compose services: backend (FastAPI) frontend (Node.js) postgres redis (optional for state/session) 🔐 Security & Safety No raw SQL execution without validation Validate generated SQL Use parameterized queries Restrict dangerous operations (DROP DATABASE, etc.) Role-based DB access Session isolation 🔄 System Flow User uploads CSV CSV stored into PostgreSQL Schema extracted Metadata sent to Gemini Agent decides action Tool executes SQL/Python Result returned Version stored Audit log updated User can revert 📊 Output & Tracking System must show: Query result SQL executed Version number Change log Previous versions Revert option 📦 Deliverables Required Generate: Full system architecture Folder structure FastAPI backend skeleton LangGraph agent flow Gemini prompt template Tool implementations (basic structure) PostgreSQL schema design Versioning strategy Dockerfile (backend & frontend) docker-compose.yml Example API endpoints Sample agent execution flow