# Dashboard Setup - Metabase Configuration Guide

## Architecture Overview

**Tool:** Metabase (Open Source Business Intelligence)  
**Host:** Self-hosted via Docker  
**Data Sources:** PostgreSQL, Redis, Stripe, Pipedrive  
**Deployment:** `http://dashboard.startuplx.com`

---

## Phase 1: Metabase Installation

### 1.1 Docker Compose Setup
```yaml
# Add to your main docker-compose.yml
  dashboard:
    image: metabase/metabase:latest
    restart: unless-stopped
    ports:
      - "3001:3000"
    volumes:
      - ./dashboard/data:/metabase-data
      - ./dashboard/logs:/metabase-logs
    environment:
      - MB_DB_FILE=/metabase-data/metabase.db.h2.db
      - MB_ENCRYPTION_KEY_FILE=/metabase-data/encryption-key.txt
      - MB_APPLICATION_NAME=StartupLX Dashboard
    networks:
      - app-network
    depends_on:
      - postgres

networks:
  app-network:
    driver: bridge
```

### 1.2 Generate Encryption Key
```bash
# Generate secure encryption key for password storage
openssl rand -hex 32 > ./dashboard/encryption-key.txt
chmod 600 ./dashboard/encryption-key.txt
```

### 1.3 Initialize Docker Stack
```bash
# From startuplx directory
docker-compose up -d dashboard

# Monitor logs
docker-compose logs -f dashboard

# Access at http://your-vps-ip:3001
```

---

## Phase 2: Connection Configuration

### 2.1 PostgreSQL Connection
```
Database Type: PostgreSQL
Name: startuplx-main
Host: postgres
Port: 5432
Database: startuplx
Username: ${DB_USER}
Password: ${DB_PASSWORD}
```

### 2.2 Stripe API Connection (via webhook data)
```sql
-- Create Stripe integration table
CREATE TABLE IF NOT EXISTS stripe_transactions (
    id SERIAL PRIMARY KEY,
    stripe_id VARCHAR(255),
    amount INTEGER,
    currency VARCHAR(3),
    status VARCHAR(50),
    customer_email VARCHAR(255),
    created_at TIMESTAMP DEFAULT NOW()
);

-- Stripe webhook will insert into this table
```

### 2.3 Pipedrive Integration
```
Method: Direct Database Connection (if using local PostgreSQL)
Or: REST API via Metabase REST API connector
```

---

## Phase 3: KPI Dashboard Cards

### 3.1 Revenue Dashboard

**Card 1: Monthly Recurring Revenue (MRR)**
```sql
SELECT 
    COALESCE(SUM(amount), 0) as mrr
FROM stripe_transactions 
WHERE status = 'paid' 
AND created_at >= DATE_TRUNC('month', NOW())
```

**Card 2: Revenue This Month**
```sql
SELECT 
    SUM(amount / 100.0) as revenue_usd
FROM stripe_transactions 
WHERE status = 'paid'
AND created_at >= DATE_TRUNC('month', CURRENT_DATE)
```

**Card 3: Project Pipeline Value**
```sql
SELECT 
    COALESCE(SUM(value), 0) as pipeline_value
FROM deals 
WHERE status NOT IN ('won', 'lost', 'deleted')
```

### 3.2 Lead Funnel Dashboard

**Card 4: Leads by Source (This Month)**
```sql
SELECT 
    source,
    COUNT(*) as leads,
    ROUND(COUNT(*) * 100.0 / SUM(COUNT(*)) OVER (), 1) as percentage
FROM leads 
WHERE created_at >= DATE_TRUNC('month', NOW())
GROUP BY source
ORDER BY leads DESC
```

**Card 5: Funnel Conversion Rates**
```sql
WITH funnel AS (
    SELECT 
        COUNT(*) as visitors
    FROM website_visitors
    WHERE date >= DATE_TRUNC('month', NOW())
)
SELECT 
    (COUNT(CASE WHEN status = 'booked' THEN 1 END) * 100.0 / 
     COUNT(CASE WHEN status = 'visited' THEN 1 END)) as conversion_rate
FROM leads 
WHERE created_at >= DATE_TRUNC('month', NOW())
```

### 3.3 Project Execution Dashboard

**Card 6: Active Projects Count**
```sql
SELECT 
    COUNT(*) as active_projects
FROM projects 
WHERE status NOT IN ('completed', 'cancelled')
```

**Card 7: Project Profit Margins**
```sql
SELECT 
    p.name,
    p.budget,
    p.actual_cost,
    ROUND((p.budget - p.actual_cost) * 100.0 / p.budget, 1) as margin_percent
FROM projects p
WHERE p.status NOT IN ('completed', 'cancelled')
ORDER BY margin_percent DESC
```

**Card 8: Hours Utilization**
```sql
WITH team_hours AS (
    SELECT 
        SUM(hours_billed) as billed,
        SUM(hours_available) as available
    FROM time_tracking
    WHERE month = DATE_TRUNC('month', NOW())
)
SELECT 
    ROUND(billed * 100.0 / available, 1) as utilization_percent,
    billed as billed_hours,
    available as total_hours
FROM team_hours
```

### 3.4 Maintenance Dashboard

**Card 9: Recurring Revenue Clients**
```sql
SELECT 
    COUNT(*) as maintenance_clients,
    ROUND(SUM(monthly_fee), 2) as recurring_revenue
FROM maintenance_clients 
WHERE status = 'active'
```

**Card 10: Client Growth Rate**
```sql
SELECT 
    ROUND((COUNT(*) - LAG(COUNT(*), 1) OVER (ORDER BY signup_month)) * 100.0 / 
          LAG(COUNT(*), 1) OVER (ORDER BY signup_month), 1) as growth_rate
FROM maintenance_clients 
WHERE signup_month >= DATE_TRUNC('month', NOW() - INTERVAL '3 months')
GROUP BY DATE_TRUNC('month', signup_date)
```

---

## Phase 4: Advanced Metrics

### 4.1 Custom Queries for Deep Insights

**Average Project Duration**
```sql
SELECT 
    ROUND(AVG(completed_at - started_at), 1) as avg_duration_days
FROM projects 
WHERE completed_at IS NOT NULL 
AND status = 'completed'
```

**Top Performing Team Members (if hiring)**
```sql
SELECT 
    u.name,
    COUNT(p.id) as projects_handled,
    ROUND(AVG(p.profit), 2) as avg_profit,
    SUM(p.profit) as total_profit
FROM users u
JOIN projects p ON u.id = p.assigned_to
WHERE p.completion_date >= CURRENT_DATE - INTERVAL '90 days'
GROUP BY u.id, u.name
ORDER BY total_profit DESC
```

**Client Lifetime Value**
```sql
SELECT 
    c.name,
    COUNT(i.id) as invoices,
    SUM(i.amount) as total_spent,
    MAX(i.date) as last_purchase,
    DATE_DIFF('day', MAX(i.date), CURRENT_DATE) as days_since_last
FROM clients c
JOIN invoices i ON c.id = i.client_id
GROUP BY c.id, c.name
HAVING SUM(i.amount) > 1000
ORDER BY total_spent DESC
```

---

## Phase 5: Dashboard Layout Configuration

### 5.1 Executive Dashboard (Primary View)
```
┌─────────────────┬─────────────────┐
│ Revenue MRR     │ Pipeline Value  │
├─────────────────┼─────────────────┤
│ Profit Margin   │ Utilization %   │
└─────────────────┴─────────────────┘

┌───────────────────────────────┐
│ Lead Funnel Conversion        │
├───────────────────────────────┤
│ New vs Returning Clients      │
└───────────────────────────────┘
```

### 5.2 Operations Dashboard
```
┌─────────────────┬─────────────────┐
│ Active Projects │ Completed This  │
│                 │ Month           │
├─────────────────┼─────────────────┤
│ Avg Duration    │ No-show Rate    │
└─────────────────┴─────────────────┘
```

### 5.3 Financial Dashboard
```
┌───────────────────────────────┐
│ MRR vs Target                 │
├───────────────────────────────┤
│ Cash Flow Forecast (30 days)  │
└───────────────────────────────┘

┌─────────────────┬─────────────────┐
│ Recurring Revenue │ Total ARR    │
├─────────────────┼─────────────────┤
│ Churn Rate      │ LTV             │
└─────────────────┴─────────────────┘
```

---

## Phase 6: Automated Alerts & Notifications

### 6.1 Email Alert Workflow (via n8n)

**Alert 1: Revenue Drop**
```
WHEN Monthly revenue < 80% of target
AND current_month = true
SEND Email to admin@startuplx.com
SUBJECT: "⚠️ Revenue Alert - Below Target"
BODY: "Current MRR is ${current} below target of ${target}. Review pipeline."
```

**Alert 2: Pipeline Stagnation**
```
WHEN Deal age > 14 days
AND Status = "Qualified Lead"
SEND Slack notification to #sales channel
```

**Alert 3: Maintenance Renewal**
```
WHEN Maintenance client expires in 7 days
SEND Email + Calendar invite to renew
```

### 6.2 Dashboard Auto-Refresh
```javascript
// Enable in Metabase Admin Settings
Dashboard Refresh: Every 5 minutes
Auto-refresh enabled for: Revenue Dashboard, Pipeline Dashboard
```

---

## Phase 7: User Management & Security

### 7.1 Role-Based Access

**Admin Group:** Full Access (All dashboards, all data)  
**Finance Group:** Financial dashboards only  
**Operations Group:** Projects, bookings, pipeline  

### 7.2 Security Configuration
```bash
# SSL termination handled by Nginx
# Metabase only accessible via HTTPS
# Admin authentication via Google SSO recommended

# In nginx.conf, proxy to Metabase:
location /dashboard/ {
    proxy_pass http://dashboard:3000/;
    proxy_set_header Host $host;
    proxy_set_header X-Forwarded-Proto $scheme;
    proxy_set_header X-Forwarded-For $proxy_add_x_forwarded_for;
}
```

---

## Phase 8: Integration Automation

### 8.1 Daily Data Sync (n8n Workflow)
```yaml
# Daily at 2 AM
Trigger: Cron (0 2 * * *)

Actions:
  1. Query Stripe API for new transactions
  2. Insert into postgres_transactions
  3. Update Metabase cache
  4. Queue for invoice generation
  5. Update dashboard cards
```

### 8.2 Key Database Tables for Metrics

```sql
-- Projects Table
CREATE TABLE projects (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    client_id INTEGER,
    budget INTEGER,
    actual_cost INTEGER,
    status VARCHAR(50),
    start_date DATE,
    completion_date DATE,
    assigned_to INTEGER,
    created_at TIMESTAMP DEFAULT NOW()
);

-- Leads Table  
CREATE TABLE leads (
    id SERIAL PRIMARY KEY,
    name VARCHAR(255),
    email VARCHAR(255),
    phone VARCHAR(50),
    source VARCHAR(100),
    status VARCHAR(50),
    budget INTEGER,
    created_at TIMESTAMP DEFAULT NOW(),
    updated_at TIMESTAMP DEFAULT NOW()
);

-- Maintenance Clients Table
CREATE TABLE maintenance_clients (
    id SERIAL PRIMARY KEY,
    client_id INTEGER,
    service_type VARCHAR(50),
    monthly_fee INTEGER,
    status VARCHAR(50),
    start_date DATE,
    renewal_date DATE,
    next_bill_date DATE
);
```

---

## Phase 9: Dashboard Design Standards

### 9.1 Color Palette (Match Website)
- Primary: `#2563eb` (Blue 600)
- Success: `#10b981` (Green 500)
- Warning: `#f59e0b` (Amber 500)
- Danger: `#ef4444` (Red 500)

### 9.2 Typography
- Font: Inter (clean, professional)
- Heading: Bold, 24-32px
- Body: Regular, 14-16px

### 9.3 Layout Guidelines
- 3-4 cards per row max
- Card padding: 20px
- Border radius: 12px
- Shadow: Subtle (1px)
- Clickable cards: Show hover effect

---

## Phase 10: Deployment Checklist

**Pre-Launch:**
- [ ] Metabase container running
- [ ] Database connections verified
- [ ] SSL certificate configured
- [ ] User accounts created
- [ ] Dashboard cards built

**Post-Launch:**
- [ ] KPI cards reflecting live data
- [ ] Alert workflows tested
- [ ] Performance metrics validated
- [ ] Documentation complete

**Success Metrics:**
- Dashboard loads in < 3 seconds
- All metrics update within 1 hour of source change
- 100% uptime maintained
- Admin access secured

---

## Quick Start Commands

```bash
# Start just the dashboard
docker-compose up -d dashboard

# View dashboard logs  
docker-compose logs -f dashboard

# Access Metabase admin
http://your-domain.com:3001

# Open in browser after initial setup
http://localhost:3001
```

## Essential Queries to Add First

1. **"Revenue This Month"** - Shows daily progress toward $7000/month goal
2. **"Active Projects"** - Capacity tracker (max 2)
3. **"Pipeline Value"** - Future revenue forecast
4. **"Maintenance MRR"** - Recurring income tracker
5. **"Lead Conversion Rate"** - Sales funnel efficiency

These 5 queries give you the complete picture of your automated business health.