BI Builder

Build BI dashboards from existing databases, from data exploration to full implementation.

Tech Stack

Layer	Technology
Frontend Framework	Next.js 16 (App Router)
UI Components	shadcn/ui + Tailwind CSS
Charts	Recharts
ORM	Prisma
Database	MySQL / PostgreSQL / Supabase / SQLite

Core Workflow

Database Connection → Schema Exploration → Requirements Dialog → Metrics Design → Chart Planning → Page Implementation

Workflow Flexibility

Skip phases based on project state and user needs:

Scenario	Skip Phases	Starting Point
Project has prisma/schema.prisma	Phase 1	Go directly to Phase 2 schema analysis
User has clear requirements and metrics	Phase 3	Go directly to Phase 4 metrics design
Only need a single chart component	Phases 1-5	Read recharts-guide.md and implement
Only need data query logic	Phases 5-6	End after metrics design

Decision criteria:

• Check if prisma/schema.prisma exists in project
• Ask user "Do you have specific metrics requirements?"
• Ask user "Do you need a full dashboard or just a single chart?"

---

Phase 1: Database Connection

1.1 Check and Install Prisma

First, check if Prisma is already installed in the project:

# Check if prisma is in package.json dependencies
grep -q '"prisma"' package.json && echo "Prisma installed" || echo "Prisma not installed"

If Prisma is not installed, install it:

# Install Prisma as dev dependency
npm install prisma --save-dev

# Install Prisma Client
npm install @prisma/client

1.2 Initialize Prisma

# Initialize Prisma (creates prisma/schema.prisma and .env)
npx prisma init

Note: If prisma/schema.prisma already exists, skip this step.

1.3 Create .env with Placeholders

⚠️ Security Note: Never ask users to share database credentials directly.

First, ask user which database type they use, then create .env file with placeholders:

Which database are you using?
1. MySQL
2. PostgreSQL
3. Supabase
4. SQLite

For MySQL:

# Database Connection
# Please fill in your database credentials below
DATABASE_URL="mysql://YOUR_USERNAME:YOUR_PASSWORD@YOUR_HOST:3306/YOUR_DATABASE"

# Example:
# DATABASE_URL="mysql://root:password123@localhost:3306/myapp_db"

For PostgreSQL:

# Database Connection
# Please fill in your database credentials below
DATABASE_URL="postgresql://YOUR_USERNAME:YOUR_PASSWORD@YOUR_HOST:5432/YOUR_DATABASE"

# Example:
# DATABASE_URL="postgresql://postgres:password123@localhost:5432/myapp_db"

For Supabase:

# Supabase Database Connection
# Find your connection string in: Supabase Dashboard → Project Settings → Database → Connection string → URI
DATABASE_URL="postgresql://postgres.YOUR_PROJECT_REF:YOUR_PASSWORD@aws-0-YOUR_REGION.pooler.supabase.com:6543/postgres?pgbouncer=true"

# Direct connection (for migrations)
DIRECT_URL="postgresql://postgres.YOUR_PROJECT_REF:YOUR_PASSWORD@aws-0-YOUR_REGION.pooler.supabase.com:5432/postgres"

# Example:
# DATABASE_URL="postgresql://postgres.abcdefghijkl:MyPassword123@aws-0-us-east-1.pooler.supabase.com:6543/postgres?pgbouncer=true"

For SQLite:

# Database Connection
DATABASE_URL="file:./dev.db"

After creating the file, tell the user:

For MySQL/PostgreSQL:

I've created .env file with placeholders. Please fill in your actual database credentials:
- YOUR_USERNAME → your database username
- YOUR_PASSWORD → your database password
- YOUR_HOST → database host (e.g., localhost or IP address)
- YOUR_DATABASE → database name

Tip: Use a read-only account for safety.

Let me know when you've filled in the credentials.

For Supabase:

I've created .env file with Supabase placeholders. To get your connection string:

1. Go to Supabase Dashboard → Your Project
2. Click "Project Settings" (gear icon)
3. Go to "Database" section
4. Copy the "Connection string" → "URI" format
5. Replace [YOUR-PASSWORD] with your database password

Let me know when you've filled in the credentials.

1.4 Configure Prisma Schema

After user confirms .env is configured, update prisma/schema.prisma:

For MySQL/PostgreSQL/SQLite:

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider = "mysql"  // or postgresql, sqlite
  url      = env("DATABASE_URL")
}

For Supabase (requires directUrl for migrations):

generator client {
  provider = "prisma-client-js"
}

datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

1.5 Pull Database Schema

# Pull schema from existing database
npx prisma db pull

# Generate Prisma Client
npx prisma generate

1.6 Error Handling

When connection fails:

Error Message	Possible Cause	Solution
Can't reach database server	Network/Firewall	Check host address and port accessibility
Access denied	Insufficient permissions	Verify username, password, and user privileges
Unknown database	Database doesn't exist	Confirm database name spelling
SSL connection error	SSL configuration	Add ?sslmode=require to DATABASE_URL

Post-schema pull checks:

• If few tables (< 3) → Confirm connection to correct database
• If no relationships → May be legacy database, need manual relationship analysis

---

Phase 2: Schema Exploration & Analysis

2.1 Read Generated Schema

After prisma db pull, read prisma/schema.prisma and analyze:

• Table structure: What tables exist, what fields each has
• Data types: Numeric, datetime, categorical fields
• Relationships: Table associations (one-to-many, many-to-many)
• Indexes: Which fields are indexed, indicating common query dimensions

2.2 Identify Metric Potential

Identify buildable metrics by field type:

Field Type	Metric Potential
Decimal/Float/Int (amounts, quantities)	Sum, average, max/min
DateTime	Time series analysis, YoY/MoM comparisons
Enum/String (status, category)	Group statistics, distribution analysis
@relation	Join aggregations, multi-dimensional analysis
Boolean	Conversion rates, completion rates

2.3 Generate Data Overview Report

Present database overview to user:

## Database Overview

### Core Tables
- **orders** (Orders table): 12 fields, related to users, products
- **users** (Users table): 8 fields
- **products** (Products table): 10 fields, related to categories

### Available Metrics
**Transaction Metrics**
- Total revenue (orders.total)
- Order count (orders.count)
- Average order value (orders.total / orders.count)

**User Metrics**
- Total users (users.count)
- New users (users.created_at)

**Product Metrics**
- Sales ranking (order_items.quantity)
- Category distribution (categories)

### Time Dimensions
- orders.created_at → Supports daily/weekly/monthly analysis
- users.created_at → Supports user growth analysis

---

Phase 3: Requirements Dialog

3.1 Questioning Strategy

Principle: Ask one question at a time, prefer multiple choice, ask in rounds.

Round 1: Industry Identification (Highest Priority)

Question 0: What industry is your business in?
Options: E-commerce/Retail / SaaS Software / Financial Services / Content/Media / Education / Healthcare / Logistics/Supply Chain / Other

Industry determines metric direction:

Industry	Core Focus	Typical Metrics
E-commerce/Retail	Transaction conversion	GMV, AOV, Repeat purchase rate, Return rate, Inventory turnover
SaaS Software	User retention	MRR/ARR, Churn Rate, LTV, CAC, DAU/MAU
Financial Services	Risk & return	AUM, Bad debt rate, Delinquency rate, Approval rate
Content/Media	Traffic monetization	PV/UV, Session duration, Bounce rate, Ad revenue, Paid conversion
Education	Learning outcomes	Course completion rate, Renewal rate, Referral rate, Study time
Healthcare	Service efficiency	Visit volume, Bed turnover, Return visit rate, Satisfaction
Logistics/Supply Chain	Operational efficiency	Order fulfillment rate, Delivery time, Warehouse cost, Turnover rate

Round 2: Core Metrics Confirmation (Use AskUserQuestion tool)

Based on industry + schema analysis, generate metric options:

Question 1: Based on [industry] context and database analysis, which core metrics matter most to you? (Multiple select)
Options: [Combine industry typical metrics + schema-supported metrics]

Question 2: What's your primary time granularity for analysis?
Options: Daily / Weekly / Monthly / Quarterly

Round 3: Conditional Follow-ups

Only ask when conditions are met:

Condition	Follow-up
Schema has category tables	"Do you need category filtering?"
User selected multiple metrics	"Do you need metric comparisons (YoY/MoM)?"
Data volume may be large	"Do you need export functionality?"

Round 4: Confirmation

Show requirements confirmation template, ask "Is the above understanding correct?"

3.2 Data Structure Limitation Handling

When user requirements don't match data, clearly inform:

User Request	Missing Data	Response
Regional distribution analysis	No region field	"Database has no region information, cannot implement. Should we analyze by [available dimension] instead?"
Trend analysis	No datetime field	"Missing datetime field, can only do static statistics, cannot show trends."
User profiling	Limited user fields	"User data is limited, can only track basic metrics (count, new users)."

3.3 Requirements Confirmation Template

Organize user requirements:

## Requirements Confirmation

### Dashboard Name
Sales Analytics Dashboard

### Core Metrics (KPI Cards)
1. Total Revenue - orders.total sum
2. Order Count - orders count
3. AOV - Total Revenue / Order Count
4. New Users - users count (this month)

### Chart Requirements
| Chart | Type | Data Source | Dimension |
|-------|------|-------------|-----------|
| Revenue Trend | Line Chart | orders.total | By day/month |
| Category Sales | Pie Chart | categories | Category distribution |
| Top 10 Products | Bar Chart | products | Sales ranking |
| Order Status | Pie Chart | orders.status | Status distribution |

### Filters
- Date range picker
- Product category dropdown
- Order status multi-select

### Other Requirements
- CSV export support
- Responsive layout

---

Phase 4: Metrics Design

4.1 Define Metric Calculation Logic

Based on confirmed requirements, define calculation for each metric:

// lib/metrics.ts

// KPI Metrics
export async function getKPIs(startDate: Date, endDate: Date) {
  const [revenue, orders, users] = await Promise.all([
    // Total revenue
    prisma.order.aggregate({
      where: { createdAt: { gte: startDate, lte: endDate }, status: { not: 'CANCELLED' } },
      _sum: { total: true },
    }),
    // Order count
    prisma.order.count({
      where: { createdAt: { gte: startDate, lte: endDate }, status: { not: 'CANCELLED' } },
    }),
    // New users
    prisma.user.count({
      where: { createdAt: { gte: startDate, lte: endDate } },
    }),
  ]);

  return {
    revenue: Number(revenue._sum.total) || 0,
    orders,
    avgOrderValue: orders > 0 ? Number(revenue._sum.total) / orders : 0,
    newUsers: users,
  };
}

4.2 Time Series Metrics

// Aggregate by time granularity
export async function getRevenueTrend(
  startDate: Date,
  endDate: Date,
  granularity: 'day' | 'week' | 'month'
) {
  const format = {
    day: '%Y-%m-%d',
    week: '%Y-%u',
    month: '%Y-%m',
  }[granularity];

  return prisma.$queryRaw`
    SELECT
      DATE_FORMAT(created_at, ${format}) as period,
      SUM(total) as revenue,
      COUNT(*) as orders
    FROM orders
    WHERE created_at BETWEEN ${startDate} AND ${endDate}
      AND status != 'CANCELLED'
    GROUP BY period
    ORDER BY period
  `;
}

4.3 Grouped Metrics

// Category distribution
export async function getCategoryDistribution(startDate: Date, endDate: Date) {
  return prisma.$queryRaw`
    SELECT
      c.name as category,
      SUM(oi.quantity * oi.price) as revenue,
      SUM(oi.quantity) as quantity
    FROM order_items oi
    JOIN products p ON oi.product_id = p.id
    JOIN categories c ON p.category_id = c.id
    JOIN orders o ON oi.order_id = o.id
    WHERE o.created_at BETWEEN ${startDate} AND ${endDate}
      AND o.status != 'CANCELLED'
    GROUP BY c.id, c.name
    ORDER BY revenue DESC
  `;
}

Before writing complex queries → Must read data-layer.md#data-aggregation-queries

---

Phase 5: Chart Planning

5.1 Visualization Type Selection

Data Type	Recommended Component	Reason
Time trends	LineChart / AreaChart	Show change over time
Distribution	PieChart	Intuitive proportion display
Rankings	BarChart (horizontal)	Easy comparison and reading
Multi-metric comparison	ComposedChart	Combine bar and line charts
Status distribution	PieChart / BarChart	Show counts per status
Detailed records	DataTable	Sortable, filterable, paginated list
Transaction logs	DataTable	Search, filter, export capabilities
Item listings	DataTable	With actions (view, edit, delete)

5.2 Layout Type Selection

Ask user about their dashboard purpose to recommend a layout:

What is the primary purpose of this dashboard?
1. Executive Overview - High-level KPIs for quick decision-making
2. Operations Monitoring - Real-time data and alerts
3. Deep Analysis - Multi-dimensional filtering and exploration
4. Period Comparison - YoY/MoM comparison and benchmarking

Layout Type	Best For	Key Features
Executive Dashboard	C-level, managers	KPI cards + main trend + distribution
Operational Dashboard	Operations team	Real-time status + live table + alerts
Analytical Dashboard	Analysts	Sidebar filters + drill-down + detailed table
Comparison Dashboard	Strategy, planning	Period selector + dual charts + change analysis

Before implementing layout → Must read dashboard-patterns.md#common-bi-layout-patterns

5.3 Layout Structure

Default Executive Dashboard layout:

┌─────────────────────────────────────────────────────────┐
│ Filter Bar: [Date Range] [Category] [Status] [Apply]    │
├─────────┬─────────┬─────────┬───────────────────────────┤
│ KPI 1   │ KPI 2   │ KPI 3   │ KPI 4                     │
│ Revenue │ Orders  │ AOV     │ New Users                 │
├─────────────────────────────┬───────────────────────────┤
│                             │                           │
│   Revenue Trend (Line)      │   Category Dist (Pie)     │
│   lg:col-span-2             │                           │
│                             │                           │
├─────────────────────────────┴───────────────────────────┤
│                                                         │
│              Top 10 Products (Bar Chart)                │
│                                                         │
├─────────────────────────────────────────────────────────┤
│              Order Details (DataTable)                  │
└─────────────────────────────────────────────────────────┘

---

Phase 6: Page Implementation

6.1 Directory Structure

app/dashboard/
├── page.tsx              # Main page
├── loading.tsx           # Loading skeleton
└── components/
    ├── kpi-cards.tsx         # KPI cards
    ├── revenue-chart.tsx     # Revenue trend chart
    ├── category-pie.tsx      # Category pie chart
    ├── top-products.tsx      # Product ranking
    ├── data-table.tsx        # Reusable DataTable component
    ├── columns.tsx           # Table column definitions
    ├── filters.tsx           # Filters
    └── export-button.tsx     # Export button

lib/
├── prisma.ts             # Prisma client
└── metrics.ts            # Metric calculation functions

app/api/dashboard/
├── route.ts              # Combined data API
├── kpi/route.ts          # KPI API
├── revenue/route.ts      # Revenue trend API
└── categories/route.ts   # Category data API

6.2 Implementation Order

1. Prisma client → lib/prisma.ts
2. Metric functions → lib/metrics.ts
3. API routes → app/api/dashboard/
4. KPI cards → Simplest, verify data flow first
5. Chart components → Implement one by one
6. Filters → Add interactivity
7. Export functionality → Complete last

6.3 Component Implementation

Chart components must use "use client" and ResponsiveContainer:

"use client";

import { ResponsiveContainer, LineChart, Line, XAxis, YAxis, Tooltip } from "recharts";

export function RevenueChart({ data }: { data: { period: string; revenue: number }[] }) {
  return (
    <ResponsiveContainer width="100%" height={300}>
      <LineChart data={data}>
        <XAxis dataKey="period" />
        <YAxis />
        <Tooltip />
        <Line type="monotone" dataKey="revenue" stroke="hsl(var(--primary))" />
      </LineChart>
    </ResponsiveContainer>
  );
}

Before creating chart components → Must read recharts-guide.md for the corresponding chart type

Before creating DataTable components → Must read table-patterns.md

Before implementing page layout → Must read dashboard-patterns.md

Before implementing export functionality → Must read export-patterns.md

---

Quick Reference

Prisma Commands

npx prisma db pull      # Pull schema from database
npx prisma generate     # Generate Prisma Client
npx prisma studio       # Open database management UI

Chart Color Scheme

const CHART_COLORS = [
  "hsl(221, 83%, 53%)",  // blue
  "hsl(142, 71%, 45%)",  // green
  "hsl(38, 92%, 50%)",   // amber
  "hsl(0, 84%, 60%)",    // red
  "hsl(262, 83%, 58%)",  // purple
];

Responsive Breakpoints

// KPI row
<div className="grid grid-cols-1 sm:grid-cols-2 lg:grid-cols-4 gap-4">

// Main chart area
<div className="grid grid-cols-1 lg:grid-cols-3 gap-4">
  <div className="lg:col-span-2">{/* Large chart */}</div>
  <div>{/* Small chart */}</div>
</div>

Reference Document Usage Rules

⚠️ Do not read all documents upfront. Only load on-demand when entering the corresponding phase.

Required Reading Triggers

Trigger Timing	Must Read	Section
Entering Phase 4 (before writing Prisma queries)	data-layer.md	#data-aggregation-queries
Entering Phase 5 (when selecting chart types)	recharts-guide.md	Corresponding chart type section
Entering Phase 6 (before implementing page layout)	dashboard-patterns.md	#responsive-grid-layout #kpi-card-component
When user needs DataTable	table-patterns.md	Full document
When user needs export functionality	export-patterns.md	Full document

Document Index

• data-layer.md - Prisma queries, Schema analysis, API design
• recharts-guide.md - Chart code examples by type
• table-patterns.md - DataTable with sorting, filtering, pagination
• dashboard-patterns.md - Page layouts, KPI cards, filters
• export-patterns.md - CSV export, image export