PRD Executive Summary

GREEN HORIZONS CUSTOMER PORTAL: PRODUCT REQUIREMENTS DOCUMENT
Executive Summary & Key Metrics
Version 2.0 - Production Ready
Last Updated: October 22, 2025

================================================================
PROJECT OVERVIEW
================================================================

Project Name: Modern Customer Portal with Stripe Integration
Client: Green Horizons Lawn Care & Landscaping
Target Launch: January 1, 2026
Business Context: Modernization of customer experience

Purpose: Build a modern customer-facing portal to replace the outdated
frontend interface while maintaining 100% compatibility with the
existing ColdFusion backend system.

================================================================
DATABASE DISCOVERY FINDINGS
================================================================

Total Scale:
- 1,397,322 services completed since 2001
- $86.5M total historical revenue (completed services)
- 35,324 service locations (30,523 active = 86.4%)
- 19,137 existing portal users
- 127,880 historical online orders
- Average ticket size: $61.90 per service

Service Offerings:
- 229+ distinct ServiceID offerings
- 5 service type categories
- 53% recurring services (mowing)
- 47% project-based services
- 95% single-service orders
- 5% multi-service orders (2-3 services per visit)

Database Structure:
- 4 databases: gh, ghc, tgs_main, greenhorizons_analysis
- 39 tables total across all databases
- MySQL 8.0 running in Docker
- Character set: latin1_swedish_ci (existing tables)
- Storage: ~1.6 GB

================================================================
CRITICAL CONSTRAINTS
================================================================

CANNOT:
- Modify ColdFusion application
- Change existing data formats
- Break backward compatibility
- Alter ServiceID meanings
- Disrupt crew scheduling

MUST:
- Insert orders ColdFusion can process
- Use existing customer accounts (tblcustomerlogin)
- Support all 229 ServiceIDs
- Maintain data integrity
- Support January 1 launch date

================================================================
SERVICE TYPES DISCOVERED
================================================================

Type 2 - Lawn Mowing & Maintenance (Core)
- Service Count: 734,576 services
- Volume: 52.6% of all services
- Average Price: $47.95
- Total Revenue: $35.2M (40.7% of total)
- Billing: Weekly/Biweekly recurring
- Business Importance: Core revenue driver, high volume

Type 4 - Landscaping, Design & Installation (Premium)
- Service Count: 404,723 services
- Volume: 29.0% of all services
- Average Price: $94.90
- Total Revenue: $38.4M (44.4% of total)
- Billing: Project-based/one-time
- Business Importance: Highest revenue generator despite lower volume

Type 1 - Lawn Treatment & Fertilization
- Service Count: 115,560 services
- Volume: 8.3% of all services
- Average Price: $51.12
- Total Revenue: $5.9M (6.8% of total)
- Billing: Scheduled applications
- Business Importance: Customer acquisition and health maintenance

Type 3 - Specialized Services & Add-ons (High-Value)
- Service Count: 50,865 services
- Volume: 3.6% of all services
- Average Price: $133.01
- Total Revenue: $6.8M (7.8% of total)
- Billing: One-time/project-based
- Business Importance: Premium offerings with highest per-service revenue

Type 5 - Snow Removal & Winter Services
- Service Count: 91,147 services
- Volume: 6.5% of all services
- Average Price: Data quality issue (investigating)
- Total Revenue: $111K reported (0.1% - anomaly detected)
- Billing: Weather-dependent
- Business Importance: Winter revenue stabilizer
- Note: Revenue data appears incomplete - investigation needed

================================================================
KEY FEATURES
================================================================

Authentication & Authorization:
- Login with existing credentials (ghc.tblcustomerlogin)
- Multiple properties per customer
- Session management
- Password reset via email

Service Offerings:
- Active services by category
- Stripe pricing integration
- Search and filtering
- Base pricing display

Order Processing:
- Single-service checkout via Stripe
- One-time payments
- Recurring subscriptions
- ColdFusion-compatible order format
- Idempotent webhook processing

Service History:
- View completed services
- Filter by property
- 12 months default view
- Pagination (50 per page)

Account Management:
- Profile information
- Payment methods via Stripe
- Subscription management
- Property switching

================================================================
DATABASE INTEGRATION
================================================================

New Tables (ghc database):
1. tblstripe_customers - Maps portal users to Stripe
2. Service offerings configuration with Stripe mapping
3. tblportal_orders - Order processing buffer
4. tblportal_audit_log - Comprehensive audit trail

Modified Tables:
- tblcustomerlogin - Add Stripe fields
- tblorderedservices - Add portal tracking

Key Patterns:
- Idempotency using Stripe event IDs
- Transaction safety with MySQL transactions
- Audit trail for all operations
- ColdFusion-compatible data formats

================================================================
IMPLEMENTATION ROADMAP (10 WEEKS)
================================================================

Phase 1: Foundation (Weeks 1-3)
Week 1: Database setup and migrations
Week 2: Service catalog creation
Week 3: Authentication implementation

Phase 2: Core Features (Weeks 4-6)
Week 4: Dashboard and service browsing
Week 5: Stripe checkout integration
Week 6: Webhook processing

Phase 3: Additional Features (Weeks 7-8)
Week 7: Service history and account pages
Week 8: Polish and optimization

Phase 4: Testing & Launch (Weeks 9-10)
Week 9: End-to-end testing
Week 10: Production deployment

================================================================
RISK ASSESSMENT
================================================================

High Priority Risks:
- ColdFusion incompatibility (Medium likelihood, Critical impact)
- Customer confusion during transition (High likelihood, Medium impact)
- January 1 deadline (Low likelihood, Critical impact)

Mitigation Strategies:
- Extensive ColdFusion compatibility testing
- Clear customer communication plan
- Rollback procedures documented
- Daily database backups
- Phased rollout option

================================================================
SUCCESS METRICS
================================================================

Launch Week:
- Zero data corruption
- 100% ColdFusion compatibility
- >99% webhook success rate
- <2 second page loads
- >95% payment success rate

30-Day Targets:
- 100+ orders per week
- >40% mobile traffic
- <30% cart abandonment
- $62-79 average order value (based on historical data)

90-Day Business Impact:
- $50K+ total portal revenue
- $30K+ recurring subscription ARR
- NPS score >40
- 10 hrs/week admin time saved

================================================================
SYSTEM ARCHITECTURE
================================================================

Technology Stack:

Frontend:
- Next.js 14 (App Router)
- React 18 Server Components
- TypeScript 5.5
- Tailwind CSS 3.4
- shadcn/ui components
- Stripe Elements

Backend:
- Next.js API Routes (serverless)
- MySQL 8.0 database
- Stripe Node.js SDK v14
- NextAuth.js authentication

Infrastructure:
- Vercel deployment
- MySQL 8.0 in Docker (existing)
- Stripe production environment

Data Flow (Order Processing):
1. Customer browses available services
2. Next.js queries service offerings from MySQL
3. Customer adds service to cart
4. Create Stripe Checkout Session (metadata: CAID, LocationID, ServiceID)
5. Redirect to Stripe for payment
6. Customer completes payment
7. Stripe webhook (checkout.session.completed)
8. Insert to tblportal_orders (with idempotency_key)
9. Insert to tblorderedservices (ColdFusion format)
10. Mark as processed
11. ColdFusion reads order from tblorderedservices

================================================================
USER PERSONAS
================================================================

Primary Persona: Residential Customer
- Homeowner in Twin Cities metro
- Property: 5,000-15,000 sq ft
- Services: Lawn mowing, fertilization, snow removal
- Tech proficiency: Medium

Pain Points (Current System):
- Legacy portal is outdated and confusing
- No modern payment options
- Difficult to view service history
- Not mobile-friendly

User Goals:
- Order services quickly (<2 minutes)
- Reorder previous services with one click
- View complete service history
- Manage payment methods securely
- Access on mobile device

Secondary Persona: Commercial Client
- Property manager or business owner
- Multiple properties (2-10 locations)
- Services: Maintenance, snow removal, cleanups
- Budget-conscious, needs detailed invoicing

================================================================
TECHNICAL REQUIREMENTS
================================================================

Performance:
- Page load time <2 seconds (95th percentile)
- API response time <500ms
- Webhook processing <5 seconds
- Support 100 concurrent users

Security:
- HTTPS/TLS 1.3 required
- SQL injection prevention (parameterized queries)
- XSS protection via React escaping
- CSRF token validation
- Stripe webhook signature verification
- Session timeout after 30 minutes
- PCI compliance via Stripe

Scalability:
- Horizontal scaling via Vercel serverless
- Database connection pooling (max 10)
- Vercel Edge Network for static assets
- Rate limiting (60 req/min per user)

Reliability:
- 99.9% uptime SLA
- Automated health checks every 5 minutes
- Error logging to monitoring service
- Daily database backups
- Rollback plan documented

================================================================
FEATURE SPECIFICATIONS
================================================================

Dashboard:
- Route: /dashboard
- Property selector dropdown
- Active subscriptions widget
- Recent services timeline (last 5)
- Quick action buttons

Service Offerings:
- Route: /services
- Service type filter tabs
- Service cards (grid layout)
- Search bar for filtering
- Recurring badge indicator

Checkout Flow:
- Routes: /services/:id/checkout, /checkout/success
- Single-service checkout
- Stripe hosted checkout
- Order confirmation page
- Error handling

Service History:
- Route: /history
- Property filter
- Date range filter (3/6/12 months, all time)
- Sortable table columns
- Pagination (50 per page)

Account Management:
- Route: /account
- Profile info (read-only from MySQL)
- Payment methods (Stripe iframe)
- Subscription management
- Property list

================================================================
TESTING STRATEGY
================================================================

Unit Testing (Jest):
- Coverage targets: 90%+ for API routes and DB functions
- Test order processing logic
- Test idempotency handling
- Test data format conversions

Integration Testing (Playwright):
- Critical flows: Login → Browse → Checkout → Payment
- Test reorder functionality
- Test property switching
- Test subscription management

Load Testing:
- 100 concurrent users browsing catalog
- 50 simultaneous checkouts
- 1000 webhook events in 1 minute

ColdFusion Compatibility:
- Verify CF can read portal orders
- Test character encoding
- Test NULL value handling
- Test date format compatibility

Security Testing:
- SQL injection testing
- XSS testing
- CSRF validation
- Webhook signature verification

================================================================
DATABASE SCHEMA DETAILS
================================================================

New Tables in ghc Database:

tblstripe_customers:
- id (PK, INT)
- CAID (FK to tblcustomerlogin)
- stripe_customer_id (VARCHAR, UNIQUE)
- default_payment_method (VARCHAR)
- created_at, updated_at (DATETIME)

Service Configuration:
- catalog_id (PK, INT)
- ServiceID (INT, UNIQUE)
- Service_Type (INT: 1-5)
- service_name (VARCHAR)
- service_description (TEXT)
- base_price (DECIMAL)
- stripe_product_id (VARCHAR)
- stripe_price_id (VARCHAR)
- is_recurring (BOOLEAN)
- billing_interval (ENUM)
- is_active (BOOLEAN)

tblportal_orders:
- order_id (PK, INT)
- CAID, LocationID (FK)
- stripe_checkout_session_id (VARCHAR)
- stripe_payment_intent_id (VARCHAR)
- order_type (ENUM: one_time, subscription)
- order_status (ENUM)
- order_total (DECIMAL)
- service_items (JSON)
- webhook_received_at (DATETIME)
- processed_to_coldfusion (BOOLEAN)
- idempotency_key (VARCHAR, UNIQUE)

tblportal_audit_log:
- audit_id (PK, INT)
- table_name, record_id
- action (ENUM: INSERT, UPDATE, DELETE)
- old_values, new_values (JSON)
- user_id, ip_address, user_agent
- created_at (DATETIME)

================================================================
ROLLBACK PROCEDURE
================================================================

If Critical Issues Detected:

Step 1: Disable Portal
- Set PORTAL_ENABLED=false in Vercel

Step 2: Archive New Tables
- RENAME TABLE tblportal_orders TO tblportal_orders_archive
- RENAME TABLE tblstripe_customers TO tblstripe_customers_archive
- Do not delete data

Step 3: Revert Columns (Optional)
- DROP COLUMN portal_order_id FROM tblorderedservices
- DROP COLUMN stripe_customer_id FROM tblcustomerlogin

Step 4: Communication Plan
- Email customers about temporary maintenance
- Update website with status message
- Notify stakeholders if applicable

================================================================
ENVIRONMENT VARIABLES
================================================================

Development (.env.local):
DATABASE_HOST=127.0.0.1
DATABASE_PORT=[configured]
DATABASE_USER=portal_app
DATABASE_NAME_GH=gh
DATABASE_NAME_GHC=ghc

STRIPE_SECRET_KEY=sk_test_...
STRIPE_PUBLISHABLE_KEY=pk_test_...
STRIPE_WEBHOOK_SECRET=whsec_...

NEXTAUTH_URL=http://localhost:3000
NEXTAUTH_SECRET=[random_32_char_string]

Production (Vercel):
- Same variables with live Stripe keys
- Production database host
- HTTPS URLs

================================================================
CONTACT INFORMATION
================================================================

Vambrace AI (Consultant):
- Luke Deasy, Lead Developer

Green Horizons:
- 12201 River Ridge Blvd, Burnsville, MN 55337
- Phone: 952.939.0155
- Email: customerservice@green-horizons.com

================================================================
DOCUMENT APPROVAL
================================================================

Prepared By: Vambrace AI (Luke Deasy)
Date: October 22, 2025
Version: 2.0 - Production Ready

Review Required:
- Green Horizons Ownership
- Operations Team (Service Validation)
- ColdFusion Lead (Compatibility Review)
- Finance Team (Pricing Approval)

================================================================
END OF DOCUMENT