clever-system
Satyam MaravaniyaGenBI 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
Comments (0)
Sign in to leave a comment
Project Tasks23
Style Instant Feedback Messages
Design and implement a styling system for instant feedback messages in the chat interface. Include success, error, and warning states with fade-out animations. Ensure the design aligns with the system's color and theme.
Build Upload CSV Page
Implement the Upload CSV page to allow users to upload datasets. Include file validation, drag-and-drop functionality, and integration with the backend for storage in PostgreSQL. Ensure alignment with the existing design and theme.
Design Home Page
Create an interactive landing page with a 3D rotating universe of data. Include hover tooltips, smooth navigation, and a glowing central star for the chat interface. Ensure the design aligns with the specified color and theme.
Align Colors and Theme
Ensure the system's color scheme and typography align with the SRD specifications. Use Deep Blue (#003366), White (#FFFFFF), Light Gray (#F5F5F5), Green (#28A745), Orange (#FFA500), and Red (#FF0000). Apply Open Sans or Roboto fonts.
Develop Upload CSV API
Create a backend API to handle CSV uploads. Parse the file, dynamically create tables in PostgreSQL, and store the original dataset. Ensure validation and error handling for file size and format.
Build Data Preview Page
Implement the Data Preview page to display uploaded CSV data in a table format. Include pagination, sorting, and filtering options. Reference the existing Upload page design for consistency.
Build Version History Page
Create the Version History page to display a list of dataset changes. Include tooltips for quick summaries and expandable details for human-readable explanations and impact summaries.
Build Tooltip Feature
Implement tooltips for quick summaries in the Version History page. Ensure the tooltips display concise information about each version, such as the timestamp and a brief summary of changes.
Build Expandable Details Feature
Implement expandable details for the Version History page. Allow users to click on entries to view detailed human-readable explanations and impact summaries.
Implement Chat Interface Backend
Develop the backend for the chat interface to handle user queries and modifications. Integrate Google Gemini API for AI-powered analytics and data manipulation.
Enhance Version History Tooltips
Update the tooltip feature on the Version History page to include concise human-readable summaries of changes, such as 'Removed duplicates: 20 rows affected'. Ensure alignment with the SRD's clarity and usability requirements.
Develop Query Execution API
Create an API to execute user queries and modifications on the dataset. Integrate with Google Gemini for AI-powered analytics and ensure safe execution of SQL queries. Return results and impact summaries.
Build Results Page
Implement the Results page to display query results and data modifications. Include a section for impact summaries, such as rows/columns affected, and ensure integration with the backend APIs.
Develop Version Control API
Create an API to manage dataset versioning. Include endpoints for retrieving version history, reverting to previous versions, and viewing SQL logs.
Build Chat Interface Page
Implement the Chat Interface page to allow users to interact with the dataset. Include a text input box, response display area, and integration with the backend chat API.
Develop Undo Change API
Create an API to handle undo operations for the last change made to the dataset. Ensure integration with the Version Control API and maintain consistency in the version history.
Develop Audit Log API
Create an API to manage the audit log. Include endpoints for retrieving a detailed record of all actions, including timestamps, user actions, and SQL logs.
Build Audit Log Page
Implement the Audit Log page to display a detailed record of all actions performed on the dataset. Include timestamps, user actions, and SQL logs. Ensure alignment with the existing design and theme.
Build Revert Version Feature
Implement the frontend feature to allow users to revert to previous dataset versions. Integrate with the Version Control API and display human-readable summaries of changes.
Develop Impact Summary API
Create an API to generate human-readable summaries of dataset changes, including the number of rows and columns affected.
Build Undo Change Feature
Implement the frontend feature to allow users to undo the last change made to the dataset. Include a button in the chat interface and integrate with the Undo Change API. Display instant feedback with human-readable impact summaries.
Build Impact Summary Display
Implement the frontend feature to display impact summaries of dataset changes. Integrate with the Impact Summary API and ensure alignment with the chat interface.
Build Version Impact Summary
Implement a feature to display human-readable impact summaries of dataset changes. Include details such as the number of rows or columns affected. Ensure integration with the chat interface for instant feedback and the Version History page for historical summaries.
No comments yet. Be the first!