SmartBiz AI – Backend

An AI powered Business Intelligence backend that enables users to analyze structured datasets using natural language. Users can upload CSV or Excel files, ask questions in plain English and receive AI generated insights, SQL queries, dashboards, business reports, charts and marketing content all generated from their own data.

Live Application: https://smartbiz-ai-vert.vercel.app

Built with: FastAPI • DuckDB • Firestore • Supabase Storage • OpenAI • Google Gemini • ReportLab • Docker

Overview

Business Intelligence tools often require technical expertise in SQL, dashboard creation and data visualization before valuable insights can be extracted from data. This creates a barrier for many business users who simply want answers without learning complex analytics tools.

SmartBiz AI removes this barrier by combining Large Language Models with an automated analytics pipeline. Users upload a dataset, ask questions in natural language and the platform automatically generates SQL queries, executes them, creates visualizations, builds interactive dashboards, prepares business reports, and even produces marketing content based entirely on the uploaded data.

The backend is designed with scalability, automation and cloud deployment in mind while supporting multiple AI providers and persistent cloud storage.

Project Highlights

Natural language to SQL generation
AI powered data analysis and visualization
Interactive dashboard generation
Automated Business Intelligence reports
AI generated marketing content
PDF report generation
Multi model LLM support with automatic fallback
Persistent cloud dataset storage
Credit based usage management
Serverless deployment architecture
REST API built with FastAPI

Features

AI Chat

Users can ask questions about uploaded datasets using natural language.

The backend automatically:

Understands user intent
Generates SQL queries
Executes SQL using DuckDB
Produces analytical explanations
Generates tables and visualizations

Dashboard Generation

Automatically creates dashboards containing:

KPI cards
Interactive charts
Trend analysis
Aggregated metrics
Visual summaries

Business Intelligence Reports

Generate structured reports including:

Executive Summary
Key Performance Indicators
Business Insights
Trend Analysis
Anomaly Detection
Actionable Recommendations

Reports can be exported as PDF documents.

Marketing Studio

Generate marketing content directly from business insights, including:

Social media captions
Marketing campaigns
Product descriptions
Promotional copy
Email campaigns

System Architecture

flowchart LR

A[Upload CSV / Excel]
-->B[FastAPI Backend]

B-->C[Dataset Cleaning]

C-->D[Supabase Storage]

D-->E[Dataset Cache]

E-->F[DuckDB]

F-->G[LLM SQL Generation]

G-->H[OpenAI / Gemini]

H-->I[Charts]

H-->J[Dashboards]

H-->K[Business Reports]

H-->L[Marketing Studio]

I-->M[Frontend]

J-->M

K-->M

L-->M

Application Workflow

Upload Dataset
        │
        ▼
Dataset Cleaning
        │
        ▼
Cloud Storage
        │
        ▼
Metadata Storage
        │
        ▼
DuckDB Query Engine
        │
        ▼
Natural Language Query
        │
        ▼
Large Language Model
        │
        ▼
SQL Generation
        │
        ▼
Query Execution
        │
        ▼
Insights, Charts & Reports

Screenshots

Home Dashboard

AI Chat Interface

Dataset Upload

Auto Generated Dashboard

System Design

Dataset Storage

Uploaded datasets are cleaned before processing and stored permanently in Supabase Storage. This ensures that datasets remain available even when the application is redeployed.

Metadata Management

Firebase Firestore stores:

Dataset metadata
User information
Chat history
Generated reports
Credit balances

Each dataset is identified using an 8-character unique dataset ID that links Firestore, Supabase Storage and the local cache.

Analytics Engine

DuckDB serves as the analytical engine for executing SQL queries directly on uploaded datasets. This provides high performance analytical processing without requiring a dedicated database server.

AI Layer

The backend supports multiple Large Language Models.

OpenAI is used as the primary provider.
Google Gemini serves as an automatic fallback.

This architecture improves reliability while minimizing service interruptions.

Dataset Cache

Cloud deployment environments typically provide temporary local storage.

To optimize performance:

Clean datasets are stored permanently in Supabase.
Frequently accessed datasets are cached locally.
Missing datasets are automatically restored when requested.
LRU cache management minimizes storage usage.

Credit System

SmartBiz AI includes a built-in credit management system.

AI operations consume credits.
New users receive configurable free credits.
Credit balances are stored in Firestore.
Usage can be monitored and managed by administrators.

API Endpoints

Endpoint	Description
`GET /`	Service status
`GET /health`	Backend health check
`GET /health/llm`	LLM provider connectivity

Environment Variables

Create a .env file or configure the following environment variables.

Variable	Description
OPENAI_API_KEY	OpenAI API Key
GEMINI_API_KEY	Gemini API Key
SUPABASE_URL	Supabase Project URL
SUPABASE_SERVICE_KEY	Supabase Service Role Key
SUPABASE_BUCKET	Dataset Storage Bucket
FIREBASE_SERVICE_ACCOUNT_JSON	Firebase Service Account
SIGNUP_FREE_CREDITS	Free credits assigned to new users
DUCKDB_MEMORY_LIMIT	DuckDB memory allocation
DUCKDB_THREADS	Number of processing threads
LOCAL_DATASET_CACHE	Maximum datasets cached locally
ADMIN_EMAILS	Administrator email addresses

Refer to .env.example for the complete configuration.

Project Structure

backend/

├── main.py
├── routes/
├── services/
├── analytics/
├── database/
├── llm/
├── reports/
├── marketing/
├── utils/
├── middleware/
├── requirements.txt
├── Dockerfile
└── .env.example

Installation

Clone the Repository

git clone https://github.com/yourusername/smartbiz-ai.git

cd smartbiz-ai

Create a Virtual Environment

python -m venv venv

Windows

venv\Scripts\activate

Linux/macOS

source venv/bin/activate

Install Dependencies

pip install -r backend/requirements.txt

Configure Environment Variables

OPENAI_API_KEY=YOUR_OPENAI_API_KEY
GEMINI_API_KEY=YOUR_GEMINI_API_KEY
SUPABASE_URL=YOUR_SUPABASE_URL
SUPABASE_SERVICE_KEY=YOUR_SUPABASE_SERVICE_KEY
FIREBASE_SERVICE_ACCOUNT_JSON=YOUR_FIREBASE_JSON

Run the Backend

uvicorn backend.main:app --host 0.0.0.0 --port 8000 --reload

Technology Stack

Category	Technology
Backend Framework	FastAPI
Server	Uvicorn
Programming Language	Python
Data Processing	Pandas
SQL Engine	DuckDB
Cloud Storage	Supabase Storage
Database	Firebase Firestore
Large Language Models	OpenAI, Google Gemini
PDF Generation	ReportLab
Containerization	Docker

Future Improvements

Multi dataset querying
Retrieval Augmented Generation (RAG)
Role based access control
Dashboard sharing
Scheduled report generation
AI agents for autonomous analysis
Vector database integration
Real time collaboration
Multi language report generation
Docker Compose deployment
Kubernetes support

License

This project is licensed under the MIT License.

Name		Name	Last commit message	Last commit date
backend		backend
docs		docs
frontend		frontend
screenshots		screenshots
.dockerignore		.dockerignore
.env.example		.env.example
.gitattributes		.gitattributes
.gitignore		.gitignore
Dockerfile		Dockerfile
README.md		README.md
README_FILES.txt		README_FILES.txt
analyze_code_quality.py		analyze_code_quality.py
archive_old_tests.py		archive_old_tests.py
check_db.py		check_db.py
package-lock.json		package-lock.json
package.json		package.json
tmp_report_input.json		tmp_report_input.json
validate_endpoints.py		validate_endpoints.py

Latest commit

History

Folders and files

Repository files navigation