dangjin/bi-builder-skills/bi-builder

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

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

# Install Prisma as dev dependency
npm install prisma --save-dev
 
# Install Prisma Client
npm install @prisma/client

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

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

# 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"

# 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"

# 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"

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

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.

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.

generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider = "mysql"  // or postgresql, sqlite
  url      = env("DATABASE_URL")
}

generator client {
  provider = "prisma-client-js"
}
 
datasource db {
  provider  = "postgresql"
  url       = env("DATABASE_URL")
  directUrl = env("DIRECT_URL")
}

# Pull schema from existing database
npx prisma db pull
 
# Generate Prisma Client
npx prisma generate

## 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

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

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

## 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

// 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,
  };
}