Widget Creation Automation - Project Details

Complete technical documentation covering how it works, step-by-step workflow, and OpenAI prompts.

Where Can We use our automation in devrev?

The Opportunity

As of now, customers are using COMPUTER less. Why can't we take this to the next level? We can give them access to the notebook with the same OpenAI tool we're using to create this automation.

The vision: Users can choose their dataset, speak with Computer in natural language, and create widgets/dashboards automatically. No SQL knowledge required. No waiting for developers.

The Impact

Might potentially increase DevRev Usage: More widgets and dashboards = more engagement with the platform
Might potentially increase Computer App Usage: Customers will actively use the notebook feature to create insights
Reduces Time Spent on Routine Tasks: Teams can focus on creative, high-value work instead of repetitive widget creation
Self-Service Analytics: Organizations can create their own insights without waiting for developer support
Faster Time to Insights: From days/weeks to minutes - instant widget creation
Scalability: Handle hundreds of widget requests without proportional increase in developer workload

Current State vs. Future State

Current State

Organizations request widgets from our team
Developers spend hours creating each widget
Long wait times for customers
Limited number of widgets due to resource constraints
Customers use Computer less

Future State

Organizations create widgets themselves
AI automates widget creation in minutes
Instant widget generation
Unlimited widgets - scale as needed
Increased Computer app usage and engagement

How It Works

Overview

Widget Creation Automation is an AI-powered tool that transforms CSV data into production-ready SQL queries and DevRev widget JSON configurations. It uses OpenAI's GPT-4o-mini model to analyze data structures, generate SQL queries, and create widget configurations automatically.

Core Components

1. CSV Parser

Component: CSVUpload.jsx

Parses CSV files using PapaParse library
Currently supports up to 3 CSV files (scalable to many files in the future)
Extracts headers, data rows, and metadata
Handles JSON/array fields automatically

2. Query Generator

Component: QuerySuggestions.jsx

Uses OpenAI API to generate SQL queries
Automatically detects relationships between tables
Generates JOIN queries when multiple tables are detected
Fixes NULL handling in SQL queries automatically

3. Widget Generator

Component: QueryDetail.jsx

Creates DevRev widget JSON configurations
Processes SQL queries to extract dimensions and measures
Generates visualization configurations
Ensures widget JSON follows DevRev schema requirements

4. Visualization Engine

Component: ChartVisualization.jsx

Renders charts using Recharts library
Transforms CSV data to match SQL query structure
Provides interactive data visualization

Key Features

Data Flow

Step-by-Step Workflow

Upload CSV Files

Component: CSVUpload.jsx

User uploads CSV files (currently up to 3, scalable to many in the future)
System parses each file using PapaParse
For each file:
- Extracts headers (column names)
- Parses data rows
- Generates default table name from filename
- Shows preview (first 5 rows)
User can customize table names for each file
System validates all files have table names

Key Features:

Supports multiple CSV files (currently up to 3, scalable to many in the future)
Automatic table name generation
Data preview before processing
Error handling for malformed CSV files

Generate Query Suggestions

Component: CSVUpload.jsx → QuerySuggestions.jsx

User clicks "Generate Query Suggestions"
System shows loading progress (10% → 100%)
System builds prompt with:
- All column names from all tables
- Table names and structure
- Special instructions for JOIN queries (if multiple tables)
OpenAI API call generates SQL queries
System automatically fixes NULL handling:
- Boolean columns: Adds COALESCE(column, FALSE) = FALSE
- Array/JSON fields: Uses IS NOT NULL instead of COALESCE
- Date fields: Uses IS NOT NULL
- ID fields: Uses IS NOT NULL
Queries are organized by table (if multiple tables)
System detects correlations between tables and generates JOIN queries

Output: Array of query objects with:

title: Human-readable query title
description: What the query does
suggested_query: Complete SQL query

Select Query

Component: QuerySuggestions.jsx

User views list of generated queries
Queries are organized by:
- JOIN queries (if multiple tables detected)
- Individual table queries (grouped by table)
Each query card shows:
- Query title and description
- SQL query preview
- "View Details & Visualize" button
User clicks on a query to see details

View Query Details & Generate Widget

Component: QueryDetail.jsx

System extracts dataset name from SQL query (FROM clause)
System processes SQL query:
- Extracts dimensions (non-aggregate columns)
- Extracts measures (aggregate functions: COUNT, SUM, AVG, etc.)
- Removes GROUP BY and HAVING clauses (handled by widget builder)
- Adds IS NOT NULL filters for dimension columns
System generates widget JSON:
- Creates data_sources with dimensions and measures
- Creates sub_widgets with visualization configurations
- Ensures field types are lowercase (DevRev requirement)
- Handles JSON/array fields with JSON_EXTRACT_STRING
- Converts COUNT(*) to SUM(count) pattern
System generates chart suggestions:
- Table chart
- Column chart
- Line chart
Each chart suggestion includes complete widget JSON

Key Processing:

SQL query is processed ONCE to ensure consistency
Dimensions and measures are extracted from SQL
Widget JSON follows DevRev schema exactly
Field types are automatically detected and set correctly

Visualize Data

Component: ChartVisualization.jsx

User selects a chart type (table, column, or line)
System transforms CSV data to match SQL query structure
System renders chart using Recharts
User can:
- View the visualization
- Copy widget JSON
- Go back to select different chart type

Note: In production, this would execute actual SQL queries against the data warehouse. Currently, it uses CSV data for visualization.

AI Integration

OpenAI Integration Overview

The system uses OpenAI's GPT-4o-mini model to power three main AI capabilities:

Initial Query Generation: Automatically generates SQL queries from CSV structure analysis
Custom Query Generation: Generates SQL queries from natural language descriptions provided by users
Column Descriptions: Generates user-friendly, business-focused descriptions of CSV columns

All prompts are designed to ensure DevRev OASIS compatibility, proper NULL handling, and correct SQL syntax. The system automatically validates and fixes generated queries to ensure they work correctly in DevRev widgets.

Model: GPT-4o-mini | Temperature: 0.7 | Max Tokens: 1000-3000 (depending on use case)

Technical Architecture

Frontend Stack

API Integration

Note: This is an MVP (Minimum Viable Product) version. Future iterations will include additional features, optimizations, and enhanced capabilities.

Key Algorithms

1. NULL Handling Fixer

Function: fixNullHandling()

Automatically fixes SQL queries to handle NULL values
Detects boolean columns and adds COALESCE
Handles array/JSON fields correctly
Preserves existing COALESCE statements

2. Correlation Detector

File: correlationDetector.js

Detects relationships between tables
Finds matching column names (e.g., id, user_id)
Calculates confidence scores
Generates JOIN queries automatically

3. SQL Query Processor

Function: processSqlQuery()

Extracts dimensions from SELECT clause
Extracts measures (aggregate functions)
Removes GROUP BY and HAVING (handled by widget builder)
Adds IS NOT NULL filters for dimensions

4. Widget JSON Generator

Function: createWorkingWidget()

Creates DevRev-compliant widget JSON
Handles field type detection
Generates dimensions and measures
Creates visualization configurations

Conclusion

Widget Creation Automation automates the entire widget creation workflow, from CSV upload to production-ready widget JSON. It uses AI to understand data structures, generate SQL queries, and create widget configurations automatically. The system handles complex scenarios like multi-table JOINs, NULL value handling, and DevRev schema requirements, making it easy for non-technical users to create sophisticated data visualizations.