BI Builder

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

Tech Stack

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:

prisma/schema.prisma

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

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

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:

Can't reach database server

Access denied

Unknown database

SSL connection error

?sslmode=require

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

prisma/schema.prisma

• 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:

Decimal

Float

Int

DateTime

Enum

String

@relation

Boolean

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:

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:

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:

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

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

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"

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

#data-aggregation-queries

#responsive-grid-layout

#kpi-card-component

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