# Phase 3 Implementation Plan: Metabase Dashboard & n8n Integration

## **Executive Summary**
Following the successful completion of **Phase 2 (n8n.io + ClawHub.ai UI Restyling)**, **Phase 3** transforms StartupLX into a fully operational, self-sustaining business automation platform. This phase connects the restyled frontend (`/api/lead-capture`, `KPIDashboard.astro`) with a backend PostgreSQL database, production-ready n8n workflow JSON definitions, Metabase analytical queries, and live metric endpoints matching the target architecture in `soul.md`.

---

## **Key Deliverables & Proposed Plan**

### **1. Database Layer Seeding (`database/seed.sql`)**
- **Objective**: Seed PostgreSQL with realistic baseline data matching `soul.md` KPI benchmarks.
- **Data Sets**:
  - **Projects**: 2 active capacity-full projects, completed past work.
  - **Leads & Pipeline**: 5 active qualified pipeline leads across web engineering & ERP solutions.
  - **Maintenance Clients**: 7 active clients ($100/mo retainer baseline = $700 MRR).
  - **Financials**: Stripe transaction history totaling $8,450 this month vs $7,000 target (+21%).
  - **Capacity & Time**: 32/40 hours utilized (80% utilization rate).

---

### **2. Production n8n Workflow JSON Specifications (`services/n8n-workflows/`)**
- **Objective**: Provide importable, execution-ready JSON workflow definitions for n8n.
- **Workflows**:
  1. `lead-qualification-workflow.json`:
     - Webhook trigger from Astro `/api/lead-capture`.
     - Lead qualification filter & scoring logic.
     - Insertion into PostgreSQL `leads` table.
  2. `project-onboarding-workflow.json`:
     - Triggered when lead status changes to `WON` or `QUALIFIED`.
     - Automatic creation of project entry in `projects` with 4-stage milestones (Discovery, Design, Dev, Launch).
     - Auto-generation of 50% deposit invoice in `invoices`.
  3. `maintenance-billing-workflow.json`:
     - Cron trigger (Monthly on 1st).
     - Renewal check for `maintenance_clients`.
     - Auto-generation of recurring $100/mo maintenance invoice & log entry in `stripe_transactions`.

---

### **3. Live Backend API Endpoints (`src/pages/api/`)**
- **Objective**: Connect the restyled frontend UI directly to live metrics and n8n webhooks.
- **Endpoints**:
  - `src/pages/api/lead-capture.ts`: Enhanced input sanitization, error handling, n8n webhook forwarding, and optional direct database fallback.
  - `src/pages/api/metrics.ts` *(New)*: Serves real-time KPI data (Revenue, Active Projects, Maintenance MRR, Utilization, Event Stream) to `KPIDashboard.astro` for dynamic client-side revalidation.

---

### **4. Metabase BI Dashboard Setup & Queries (`services/dashboard/`)**
- **Objective**: Deliver ready-to-run Metabase SQL queries matching `soul.md` metrics.
- **Files**:
  - `services/dashboard/queries.sql`: SQL queries for:
    - Monthly Revenue vs Goal ($7,000) gauge.
    - Active Project Capacity counter (2 / 2 max).
    - Maintenance MRR growth tracker.
    - Hours utilization percentage (32 / 40 hours).
    - Pipeline lead stage distribution.
  - `services/dashboard/METABASE-GUIDE.md`: Updated provisioning and dashboard card creation steps.

---

### **5. End-to-End Pipeline Testing & Validation Script (`scripts/test-phase3-pipeline.sh`)**
- **Objective**: Ensure end-to-end reliability from form submission to metrics update and production build.
- **Test Steps**:
  - Direct endpoint testing of `/api/lead-capture` with test lead payload.
  - Test metrics response from `/api/metrics`.
  - Schema and SQL seed syntax validation.
  - Verification of `npm run build` static compilation (zero errors).

---

## **Security & Quality Safeguards**
- **Strict Parameterization**: All SQL queries and API database interactions use safe parameterization to prevent SQL injection.
- **Input Sanitization**: Webhook payloads are sanitized for XSS and length constraints.
- **Secrets Management**: Webhook URLs and DB credentials rely exclusively on environment variables (`.env`).

---

## **User Confirmation Required**
Please review this implementation plan. Upon your approval, work will begin immediately on Phase 3 execution.