Milton — AI-Powered KPI Dashboard for SMBs
Milton is a web application that helps small and medium businesses understand their performance through automated data analysis and KPI dashboards. Users upload their existing business data files (Excel/CSV), and the app uses AI to classify and map that data, calculate relevant KPIs, and generate insights — without requiring any technical knowledge.
Table of Contents
Product Overview
Target users: Startup founders, SMB owners/operators (no technical background required)
Core value: Upload your business data (bookings, orders, transactions, memberships) → get automatic KPI dashboards and AI-generated insights.
Supported business types:
- Fitness Studio / Gym / Yoga Studio
- Restaurant / Cafe
- E-commerce
- (Partial / legacy: SaaS, Agency)
Key capabilities:
- AI-driven business type detection (from conversation or file contents)
- AI-powered column mapping — automatically maps CSV/Excel columns to data fields
- Automatic KPI calculation from uploaded data
- AI-generated insights and anomaly detection
- PDF report generation
- Visual data model builder (for understanding table relationships)
Tech Stack
| Layer | Technology |
|---|
| Framework | Next.js 16 (App Router, TypeScript) |
| UI | React 19, Tailwind CSS 4, Radix UI, Shadcn/ui, Lucide icons |
| Database & Auth | Supabase (PostgreSQL + Row Level Security) |
| AI | Vercel AI SDK + OpenAI API (gpt-4o-mini) |
| Charts | Recharts |
| Data Model Visualizer | React Flow |
| File Parsing | xlsx (Excel), PapaParse (CSV) |
| PDF Generation | jsPDF + html2canvas |
| Form Handling | React Hook Form + Zod |
| Testing | Vitest (unit), Playwright (E2E) |
Getting Started
Prerequisites
- Node.js v24+
- A Supabase project
- An OpenAI API key
Installation
git clone <repo-url>
cd milton
npm install
Environment Variables
Create a .env.local file:
# OpenAI
OPENAI_API_KEY=your-openai-api-key
# Supabase (public — safe in browser)
NEXT_PUBLIC_SUPABASE_URL=your-supabase-url
NEXT_PUBLIC_SUPABASE_ANON_KEY=your-anon-key
# Supabase (secret — server-side only, bypasses RLS)
SUPABASE_SERVICE_ROLE_KEY=your-service-role-key
Where to find Supabase keys: Dashboard → Settings → API
Run
npm run dev # development server at http://localhost:3000
npm run build # production build
npm run test # run unit tests
Architecture Overview
┌─────────────────────────────────────────────────────────────────┐
│ Next.js App Router │
│ │
│ Pages (app/) API Routes (app/api/) │
│ ├── /auth ├── /auth/signup-complete │
│ ├── /onboarding ├── /ai/* (classify, analyze, insights) │
│ ├── /dashboard ├── /data/* (parse, upload, status) │
│ ├── /management ├── /kpis/* (calculate, select) │
│ └── / └── /analytics/* (per business type) │
└───────────────────────────────┬─────────────────────────────────┘
│
┌───────────────────────┼───────────────────────┐
│ │ │
Supabase OpenAI API Client Browser
(Auth + DB) (gpt-4o-mini) (React components)
Core Data Flow
User upload (CSV/Excel)
│
▼
/api/data/parse — Extract sheets, headers, sample rows
│
▼
/api/ai/dataset-classifier — Identify which business table this file maps to
│
▼
Data Mapping UI — User confirms/adjusts column → field mappings
│
▼
Value Normalization — Case-correct categories, normalize dates
│
▼
/api/data/upload-model-table — Insert rows into model_data table (JSONB)
│
▼
/api/kpis/calculate — Query model_data, aggregate, return KPI values
│
▼
Dashboard — Display KPI cards, charts, AI insights
User Flows
1. Onboarding (First-Time User)
- Sign up — Email/password registration; Supabase creates
auth.users, app creates profiles and companies records
- Chat (
/onboarding/chat) — Describe your business in natural language; AI identifies business type (fitness_studio, restaurant, ecommerce, etc.)
- Data Sources (
/onboarding/data-sources) — Select which data sources you have (bookings, transactions, orders, etc.)
- Upload (
/onboarding/upload) — Upload Excel or CSV files; AI classifies each sheet to a canonical table
- Map & Normalize — Confirm column-to-field mappings per table; review value normalization
- KPI Selection (
/onboarding/kpi-selection) — Choose and rank which KPIs to track
- Dashboard — Redirect to
/dashboard with KPIs populated
2. Dashboard Usage
- View KPI cards with current values and trend indicators
- Drill into analytics by business area (members, revenue, classes, etc.)
- Upload additional data files at any time
- Generate and export PDF reports with AI-written insights
- Adjust KPI selection and date ranges
3. Admin Operations
Accessible at /management/ (requires admin role):
- KPIs — Create/edit KPI definitions, link to business types, publish/unpublish
- Templates — Manage business model templates (required tables, relationships, KPI lists)
- Data Tables — Define canonical table schemas (fields, types, relationships)
Codebase Structure
milton/
├── app/ # Next.js App Router
│ ├── (root)/page.tsx # Landing page
│ ├── auth/ # Login, signup, OAuth callback
│ ├── onboarding/ # 6-step onboarding flow
│ │ ├── chat/ # AI business model chat
│ │ ├── data-sources/ # Source selection
│ │ ├── upload/ # File upload step
│ │ ├── model/ # Data model viewer
│ │ └── kpi-selection/ # KPI selection step
│ ├── dashboard/ # Main app
│ │ ├── page.tsx # KPI dashboard home
│ │ ├── analytics/ # Analytics & drill-downs
│ │ ├── data/ # Data management
│ │ ├── upload/ # Additional uploads
│ │ ├── reporting/ # PDF report generation
│ │ ├── account/ # User account
│ │ └── settings/ # Dashboard settings
│ ├── management/ # Admin panel
│ │ ├── dashboard/ # Admin overview
│ │ ├── kpis/ # KPI CRUD
│ │ ├── templates/ # Template CRUD
│ │ └── data-tables/ # Data table CRUD
│ └── api/ # API routes (see API Reference)
│
├── components/
│ ├── dashboard/ # Dashboard & upload UI components
│ ├── onboarding/ # Onboarding wizard components
│ ├── management/ # Admin panel components
│ ├── layout/ # Navigation, providers, theme
│ ├── auth/ # Login/signup forms
│ ├── ai-elements/ # Chat UI components
│ └── ui/ # Shadcn/Radix primitives
│
├── lib/
│ ├── model/
│ │ ├── transform.ts # Core model types (TableDef, ModelProposal, etc.) + graph logic
│ │ └── default-models.ts # Hardcoded fallback models (fitness, restaurant, ecom)
│ ├── kpi-calculations/ # One file per KPI calculator function
│ │ ├── fitness/ # Fitness studio KPIs
│ │ ├── restaurant/ # Restaurant KPIs
│ │ ├── ecom/ # E-commerce KPIs
│ │ ├── financial/ # Cross-sector financial KPIs
│ │ ├── fieldAccessors.ts # Flexible field reading from JSONB rows
│ │ └── index.ts # Exports all calculators
│ ├── ai/ # AI helper modules
│ │ ├── dashboard-context.ts # Build AI context from KPI data
│ │ ├── normalize-values.ts # AI value normalization
│ │ ├── generate-ids.ts # AI-generated IDs for table rows
│ │ ├── insights.ts # Generic insight framework
│ │ ├── fitness-studio-insights.ts
│ │ └── restaurant-insights.ts
│ ├── types/
│ │ ├── data.ts # Shared data types (DataTable, TransactionData, etc.)
│ │ ├── kpi.ts # KPI types and selectors
│ │ ├── pipeline.ts # CRM/pipeline types
│ │ └── report.ts # Report data structures
│ ├── supabase/
│ │ ├── client.ts # Browser Supabase client
│ │ └── server.ts # Server-side Supabase client (service role)
│ ├── data-table-service.ts # Data table CRUD (Supabase)
│ ├── business-models.ts # BusinessModel record CRUD
│ ├── dashboard-kpis.ts # KPI routing by business type
│ ├── kpi-recipe-service.ts # Map KPI IDs → calculator functions
│ ├── kpi-availability.ts # Which KPIs are available
│ ├── report-data-service.ts # Gather data for PDF reports
│ └── utils.ts # cn(), normalizeDateValue(), etc.
│
├── types/
│ └── schema.ts # ColumnMapping and other shared types
│
├── supabase/ # DB migrations
├── test-data/ # Sample data files for testing
└── tests/ # Test suites
├── unit/ # Vitest unit tests
└── smoke/ # Playwright E2E tests
Database Schema
Core Tables
| Table | Purpose |
|---|
auth.users | Supabase-managed authentication |
profiles | User profile (id, user_id, company_id, full_name, email); see milton_admins for admin flag |
companies | Tenant record; linked to creator user |
business_models | Per-company business type, KPI selections, canonical data model, onboarding answers |
data_tables | Admin-defined canonical table schemas (fields as JSONB array) |
model_data | All uploaded business data — rows stored as JSONB, scoped to company_id + model_table_id |
kpis | KPI definitions (name, formula reference, business_types, is_published) |
business_model_templates | Templates per business type (required tables, relationships, KPI lists) |
custom_datasets | Metadata about uploaded files (schema, source info) |
dashboard_insights | Cached AI-generated insight text |
Key Relationships
auth.users
└── profiles (one-to-one)
└── companies (one-to-many via created_by)
└── business_models (one-to-one)
└── model_data (one-to-many) ← all uploaded rows live here
└── data_tables (many-to-one via model_table_id)
model_data Structure
This is the central storage table for all uploaded business data:
model_data (
id uuid PRIMARY KEY,
company_id uuid REFERENCES companies(id),
model_table_id uuid REFERENCES data_tables(id),
data jsonb, -- the actual row data
indexed_fields jsonb, -- extracted fields for faster querying
created_at timestamptz
)
All KPI calculations query this table, filtering by company_id and model_table_id, then reading the flexible data JSONB column.
Row Level Security
- Users can only read/write rows where
company_id matches their company
- Admin-only tables (kpis, data_tables, templates) have admin-role policies
- Server-side operations using the service role key bypass RLS where needed
Business Model System
The "business model" represents a company's canonical data structure — which tables they use, what relationships exist, and what KPIs are relevant.
How it Works
- Templates (admin-defined, in Supabase) — define which tables, relationships, and KPIs belong to each business type
- Default Models (hardcoded in
lib/model/default-models.ts) — fallback definitions used during onboarding when no Supabase template is available
- Canonical Model (user-specific, stored in
business_models.canonical_model as JSONB) — the actual model applied to a company, derived from the template + any user customizations
TableDef and ModelProposal
The core types in lib/model/transform.ts:
interface FieldDef {
name: string;
type: "string" | "number" | "integer" | "date" | "currency" | "boolean";
required: boolean;
primaryKey?: boolean;
references?: { table: string; field?: string } | null;
allowedValues?: string[]; // For enum/categorical fields
displayName?: string; // Human-readable label (e.g., "Order Items" vs "ecom_order_items")
}
interface TableDef {
name: string; // Internal name used in DB (e.g., "ecom_orders")
displayName?: string; // User-facing label (e.g., "Orders")
fields: FieldDef[];
fileMapping?: { fileName?: string; columnMap?: Record<string, string> };
}
interface ModelProposal {
businessType: string;
recommendedTables: TableDef[];
relationships: RelationshipDef[];
}
Supported Business Types
| Key | Display Name | Tables |
|---|
fitness_studio | Fitness Studio | Customers, Classes, Instructors, Bookings, Payments |
restaurant | Restaurant | Tables, Menu Items, Orders, Order Items, Reservations |
ecommerce | E-commerce | Orders, Order Items, Products, Customers, Marketing Spend |
KPI System
KPI Definition
KPIs are stored in the kpis Supabase table. Each KPI has:
name — display namedefinition — descriptionformula — optional formula string (documentation)required_data — array of data_tables.id UUIDs the KPI needsbusiness_types — which business types this KPI applies tois_published — whether visible to users (drafts hidden)
KPI Calculators
Each KPI has a dedicated TypeScript function in lib/kpi-calculations/:
// Common signature
async function calculateXxx(
supabase: SupabaseClient,
userId: string,
fromDate: string,
toDate: string
): Promise<{
currentValue: number;
historicalData: Array<{ period: string; value: number }>;
}>;
Fitness Studio KPIs:
- Active Members, Monthly Churn Rate, Churned Members, New Members
- Revenue Per Member, Revenue Per Class, Average Member Tenure
- Total Classes Held, Average Class Size, Class Attendance Rate
- Average Class Occupancy, Utilization Rate
- No Show Rate, Cancellation Rate
Financial KPIs (cross-sector):
- Burn Rate, Net Income, Net Cash Flow, Runway, Revenue Growth Rate
Restaurant KPIs:
- Total Revenue, Covers, Average Ticket Size, Prime Cost %
E-commerce KPIs:
- True CAC, CAC Payback Period, Marketing Efficiency Ratio
- Contribution Margin After Marketing
- Product Profitability Leaderboard, Growth Quality Score
KPI Availability
When a user opens the KPI selector, the app:
- Fetches
GET /api/data/status → gets which data_tables have uploaded data (by UUID)
- For each KPI, checks if all
required_data UUIDs have data
- If any required table is missing, the KPI is locked (greyed out, cannot select)
- Name-based fallback matching handles cases where UUIDs differ between environments
KPI Display
Selected KPIs appear on the dashboard as:
- Cards — current value with trend indicator (vs previous period)
- Charts — historical time series (line/bar/area via Recharts)
- Both modes can be selected per KPI
AI Integration
The app uses two OpenAI integration patterns:
1. Vercel AI SDK (Streaming)
Used for conversational interfaces:
- Onboarding chat (
/app/onboarding/chat)
- Dashboard AI insights
- Milton chat sidebar
2. Direct OpenAI API Calls
Used for one-shot classification/analysis tasks:
- Dataset classification (
/api/ai/dataset-classifier) — which business table does a file map to?
- Business model analysis (
/api/ai/business-model-analyzer) — what type of business is this?
- Value normalization (
/api/data/normalize-values) — correct inconsistent categorical values
- ID generation (
lib/ai/generate-ids.ts) — human-readable IDs for uploaded rows
- Insight generation (various AI modules in
lib/ai/)
Model Used
All AI calls use gpt-4o-mini for cost efficiency. The model is not configurable per-call; it is set globally in each AI helper.
Authentication & Permissions
Flow
- User signs up at
/auth/signup
- Supabase creates
auth.users
POST /api/auth/signup-complete creates profiles (basic info only) and companies records- Session stored via Supabase SSR (HTTP-only cookies)
Roles
| Role | Access |
|---|
user | Own company data only; dashboard, uploads, KPI selection |
admin | All management routes; KPI/template/table CRUD |
Admin role is stored in public.milton_admins. Checked server-side via isUserAdminServer() in lib/profile-service-server.ts, which calls the is_milton_admin() RPC (migration 020).
Supabase Clients
lib/supabase/client.ts — browser client (anon key, subject to RLS)lib/supabase/server.ts — server client (service role key, bypasses RLS)
API Reference
Data Management
| Method | Endpoint | Description |
|---|
| POST | /api/data/parse | Parse Excel/CSV; returns sheets, headers, sample rows, all rows |
| POST | /api/data/upload-model-table | Insert mapped rows into model_data table |
| DELETE | /api/data/delete-model-table | Remove data for a specific table |
| GET | /api/data/status | Which data tables have uploaded data (with row counts) |
| GET | /api/data/table-names | Resolve table UUIDs to names |
| GET | /api/data/table-preview | Preview first N rows of a table |
| POST | /api/data/normalize-values | AI-normalize categorical field values |
KPI Endpoints
| Method | Endpoint | Description |
|---|
| POST | /api/kpis/calculate | Calculate all selected KPIs for a date range |
| POST | /api/kpis/series | Get historical time series for a KPI |
| GET | /api/kpis/selected | Get user's selected KPI IDs and display modes |
| POST | /api/kpis/select | Save KPI selection |
AI Endpoints
| Method | Endpoint | Description |
|---|
| POST | /api/ai/business-model-analyzer | Classify business type from description |
| POST | /api/ai/dataset-classifier | Classify uploaded dataset to a canonical table |
| POST | /api/ai/onboarding | Stream onboarding chat responses |
| POST | /api/ai/insight-generator | Generate AI insights from KPI data |
| POST | /api/milton/ask | Dashboard chat assistant |
Analytics (per business type)
| Endpoint | Business Type | Returns |
|---|
GET /api/analytics/fitness-studio/kpis | Fitness | All fitness KPI values |
GET /api/analytics/ecommerce/kpis | E-commerce | All ecom KPI values |
GET /api/analytics/restaurant/overview | Restaurant | Revenue, covers, etc. |
Various /api/analytics/fitness-studio/* | Fitness | Members, instructors, classes, cash flow, charts |
Admin Endpoints (admin role required)
| Method | Endpoint | Description |
|---|
| GET/POST | /api/admin/kpis | List / create KPIs |
| PUT/DELETE | /api/admin/kpis/[id] | Update / delete KPI |
| GET | /api/admin/kpis/impact/[id] | Impact analysis before delete |
| GET/POST | /api/admin/templates | List / create templates |
| GET/PUT/DELETE | /api/admin/templates/[key] | Manage specific template |
| GET/POST | /api/admin/data-tables | List / create data table definitions |
| PUT/DELETE | /api/admin/data-tables/[id] | Update / delete data table |
| GET | /api/admin/data-tables/impact/[id] | Impact analysis before delete |
Testing
Unit Tests (Vitest)
npm run test:unit # run once
npm run test:unit:watch # watch mode
Located in tests/unit/. Cover:
- All KPI calculator functions with mocked Supabase responses
- KPI time series calculations
E2E Tests (Playwright)
npx playwright test # run all E2E tests
Located in tests/smoke/. Cover:
- Landing page load
- Auth flows (login, signup)
- Dashboard basic functionality
Test Data
Sample data files for manual testing are in test-data/ organized by business type (e.g., test-data/restaurant/).
Key Files Quick Reference
| File | Purpose |
|---|
lib/model/transform.ts | Core data model types and transformation logic |
lib/model/default-models.ts | Hardcoded fallback business models |
lib/kpi-calculations/fieldAccessors.ts | Flexible field reading from JSONB rows |
lib/kpi-calculations/index.ts | Exports all KPI calculator functions |
lib/utils.ts | normalizeDateValue(), cn(), shared utilities |
lib/data-table-service.ts | Read data table definitions from Supabase |
lib/dashboard-kpis.ts | Routes KPI calculation requests by business type |
lib/kpi-recipe-service.ts | Maps KPI IDs to calculator functions |
app/api/data/upload-model-table/route.ts | Central upload handler with ID generation, FK resolution, normalization |
app/api/data/parse/route.ts | File parsing endpoint (Excel/CSV → rows) |
components/dashboard/ModelTableDetailView.tsx | Single-table upload + mapping UI |
components/dashboard/MultiFileUploadView.tsx | Multi-file drag-drop upload flow |
components/dashboard/data-mapping-confirmation.tsx | Column mapping modal |
components/dashboard/kpi-selector.tsx | KPI selection dialog |
