--- name: bi-builder description: Build BI dashboards from databases. Use when creating dashboards, charts, or analytics pages with Next.js + shadcn\/ui + Recharts + Prisma. --- # 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: ```bash # 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: ```bash # Install Prisma as dev dependency npm install prisma --save-dev # Install Prisma Client npm install @prisma\/client ``` ### 1.2 Initialize Prisma ```bash # 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:** ```env # 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:** ```env # 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:** ```env # 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:** ```env # 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:** ```prisma generator client { provider = "prisma-client-js" } datasource db { provider = "mysql" \/\/ or postgresql, sqlite url = env("DATABASE_URL") } ``` **For Supabase (requires directUrl for migrations):** ```prisma generator client { provider = "prisma-client-js" } datasource db { provider = "postgresql" url = env("DATABASE_URL") directUrl = env("DIRECT_URL") } ``` ### 1.5 Pull Database Schema ```bash # 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: ```markdown ## 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: ```markdown ## 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: ```typescript \/\/ 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 ```typescript \/\/ 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 ```typescript \/\/ 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](references\/data-layer.md#data-aggregation-queries)** --- ## Phase 5: Chart Planning ### 5.1 Chart Type Selection | Metric Type | Recommended Chart | 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 | ### 5.2 Layout Planning ``` ┌─────────────────────────────────────────────────────────┐ │ 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 ├── 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`: ```tsx "use client"; import { ResponsiveContainer, LineChart, Line, XAxis, YAxis, Tooltip } from "recharts"; export function RevenueChart({ data }: { data: { period: string; revenue: number }[] }) { return ( <\/LineChart> <\/ResponsiveContainer> ); } ``` **Before creating chart components → Must read [recharts-guide.md](references\/recharts-guide.md) for the corresponding chart type** **Before implementing page layout → Must read [dashboard-patterns.md](references\/dashboard-patterns.md)** **Before implementing export functionality → Must read [export-patterns.md](references\/export-patterns.md)** --- ## Quick Reference ### Prisma Commands ```bash npx prisma db pull # Pull schema from database npx prisma generate # Generate Prisma Client npx prisma studio # Open database management UI ``` ### Chart Color Scheme ```tsx 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 ```tsx \/\/ KPI row

\/\/ Main chart area

{\/* Large chart *\/}<\/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 export functionality | export-patterns.md | Full document | ### Document Index - [data-layer.md](references\/data-layer.md) - Prisma queries, Schema analysis, API design - [recharts-guide.md](references\/recharts-guide.md) - Chart code examples by type - [dashboard-patterns.md](references\/dashboard-patterns.md) - Page layouts, KPI cards, filters - [export-patterns.md](references\/export-patterns.md) - CSV export, image export