Bi Builder

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

// 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 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](references/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 (
    
      
        
        
        
        
      
    
  );
}

Before creating chart components → Must read [recharts-guide.md](references/recharts-guide.md) for the corresponding chart type

Before creating DataTable components → Must read [table-patterns.md](references/table-patterns.md)

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

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

// Main chart area

  {/* Large chart */}
  {/* Small chart */}

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](references/data-layer.md) - Prisma queries, Schema analysis, API design
• [recharts-guide.md](references/recharts-guide.md) - Chart code examples by type
• [table-patterns.md](references/table-patterns.md) - DataTable with sorting, filtering, pagination
• [dashboard-patterns.md](references/dashboard-patterns.md) - Page layouts, KPI cards, filters
• [export-patterns.md](references/export-patterns.md) - CSV export, image export

Source & license

This open-source skill is cataloged on AgentStack and links to its original source — we do not rehost the code.

• Author: DangJin
• Source: DangJin/bi-builder-skills
• License: MIT

Install and usage instructions live in the source repository linked above.