Hoppee Express - Project Status

Last Updated: 2025-11-08 16:45 UTC Status: Development - Pre-Production (BLOCKERS PRESENT + MAJOR OPPORTUNITY DISCOVERED!)

🚨 CRITICAL BLOCKERS - MUST FIX BEFORE PRODUCTION

1. Hard-coded Localhost URLs (P0 - PRODUCTION KILLER!)

File: /functions/api/checkout.ts:56-57 Issue: Success and cancel URLs point to http://localhost:4321 Impact: In production, users will be redirected to localhost after payment, completely breaking the payment flow!

// CURRENT (BROKEN):
success_url: `http://localhost:4321/success?session_id={CHECKOUT_SESSION_ID}`,
cancel_url: "http://localhost:4321/cancel",

// NEEDED:
success_url: `${getSiteUrl(context)}/success?session_id={CHECKOUT_SESSION_ID}`,
cancel_url: `${getSiteUrl(context)}/cancel`,

2. Hard-coded Operations Email (P0 - NO REFUND NOTIFICATIONS!)

File: /functions/api/cancel-booking.ts:217 Issue: Ops team email is [email protected] (placeholder) Impact: Operations team won't receive refund notifications!

// CURRENT (BROKEN):
const opsWarning = await sendEmailWithFallback("[email protected]", ...);

// NEEDED:
const opsWarning = await sendEmailWithFallback(context.env.OPS_EMAIL, ...);

3. Missing Request Validation (P0 - SECURITY/STABILITY)

File: /functions/api/checkout.ts:21 Issue: No try/catch around JSON parsing, no validation of request data Impact: Unhandled errors crash the function, invalid data creates broken bookings

Needed:

Validate date is valid date string
Validate destination matches allowed routes
Validate passengers is positive number within limits
Try/catch around request parsing

4. Inconsistent Refund Logic (P0 - CONFUSING BUSINESS LOGIC)

Files: /functions/api/refund.ts vs /functions/api/cancel-booking.ts Issue:

refund.ts:86-88 - Full refund (100%)
cancel-booking.ts:104 - Partial refund (97%, retains 3% fee)

Impact: Which endpoint should be used? Why different amounts? Pick one strategy!

✅ WORKING PROPERLY

Stripe Payment Integration

✅ Checkout session creation (/functions/api/checkout.ts)
✅ Webhook signature validation (/functions/api/webhook.ts:30-48)
✅ Payment confirmation flow (webhook → sheets → email)
✅ Session retrieval (/functions/api/session/[[sessionId]].ts)
✅ Refund processing (both endpoints functional, just inconsistent)
✅ 2-hour cancellation window enforcement
✅ 3% Stripe fee calculation (in cancel-booking flow)

Data Integration

✅ Google Sheets booking storage
✅ Booking reference generation (HOP-XXXXXX format)
✅ Departure timestamp calculation with timezone awareness
✅ Metadata storage in Stripe sessions

Email/Communication

✅ Resend email integration
✅ Confirmation emails triggered by webhook
✅ Email fallback mechanism in cancellation flow
✅ LINE webhook endpoint configured

Frontend

✅ BookingFlow.tsx checkout integration
✅ Success page session retrieval
✅ CloudFlare Pages deployment configured

⚠️ MISSING/INCOMPLETE (Priority Issues)

P1 - Fix Soon

No Idempotency Keys on Stripe Operations
- Missing on checkout session creation
- Missing on refund operations
- Risk: Network retries could create duplicate charges/refunds
No Webhook Event Logging
- Only returns { received: true } for all events
- No audit trail for debugging failed webhooks
- Impact: Can't debug webhook issues in production
No Duplicate Booking Prevention
- Webhook handler doesn't check if booking ref already exists
- Risk: Duplicate bookings in Google Sheets if webhook replays
Missing Environment Variable Validation
- No startup checks for required env vars
- Impact: Runtime errors instead of clear startup failures
Hard-coded Return Times
- File: /functions/api/session/[[sessionId]].ts:29-30
- Return time is 03:30 PM (hard-coded, doesn't match route data)
- Should derive from metadata or route config

P2 - Improve When Possible

No Rate Limiting
- Vulnerable to spam checkout sessions (costs money!)
- Vulnerable to brute force booking lookups
- No protection beyond Stripe webhook signature validation
No Structured Logging/Monitoring
- Limited console.log statements
- No error tracking (Sentry, Datadog, etc.)
- Impact: Hard to debug production issues
No Stripe Customer Creation
- Sessions created without customer field
- Impact: Can't track repeat customers, no payment method saving
Missing Payment Intent Metadata
- Metadata only on session, not payment intent
- Impact: If session expires, metadata is lost
No Customer Email Pre-Collection
- Relies on Stripe Checkout form for email
- No pre-validation or prefill for returning customers
Missing Refund Reason Tracking
- No field to capture why customer cancelled
- No distinction between customer vs operator initiated refunds
Limited Webhook Event Handling
- Only handles checkout.session.completed
- Doesn't handle: payment_intent.succeeded, charge.refunded, payment_intent.payment_failed
No Test Mode Detection
- Could accidentally use test keys in production
- No indication of test vs live mode in responses
No Stripe API Version Lock
- Stripe SDK initialized without API version
- Risk: Breaking changes when Stripe updates API
No Booking Status Field
- Google Sheets has reserved columns (K-M) but limited use
- No clear "confirmed", "pending", "cancelled" status tracking
Hard-coded Currency
- THB only (/functions/api/checkout.ts:39)
- No multi-currency support for expansion
No CORS Configuration
- May block legitimate cross-origin requests

🔍 BUNDHAYA SPEEDBOAT API INVESTIGATION

🎉 BREAKTHROUGH DISCOVERY!

Status: ✅ FULLY CRACKED - We have complete access to their route, pricing, and availability data!

Bad News (initially): No traditional REST/GraphQL API available.

AMAZING News: We found their publicly-readable Firestore routes collection with ALL DATA!

Architecture

Framework: Next.js (SSR)
Database: Google Cloud Firestore (real-time database)
Data Access: Client-side Firebase SDK with real-time listeners
Project ID: bundhayaspeedboat-produc-91aec

🔓 "routes" Collection - PUBLIC ACCESS!

Found: 17 route documents with complete data structure!

Document Structure:

{
  id: "9f8bb780d62530df2c99",  // Location hash ID
  departure: "Koh Lanta (Saladan Pier)",  // Human-readable name
  type: "SPEEDBOAT",
  arrivals: [
    {
      arrival: "Phi Phi (Tonsai Pier)",
      adult: 700,         // THB per adult
      olderChild: 420,    // THB per older child
      smallChild: 0,      // FREE
      infant: 0,          // FREE
      allotment: [
        {
          date: { seconds: 1762621200, ... },  // Firestore timestamp
          seatQty: 20  // Available seats for this date
        },
        // ... seats available for next ~180 days!
      ]
    },
    // ... other possible destinations from this departure point
  ]
}

Key Insight: The allotment array contains real-time seat availability for approximately the next 6 months!

What This Means:

✅ We can query exact pricing for any route
✅ We can check seat availability for any date
✅ We have the complete route network (17 locations, hundreds of possible routes)
✅ All data is PUBLIC (read-only) - no authentication needed!

Test Results:

routes collection: ✅ PUBLIC READ ACCESS (17 documents)
All other collections: 🔒 Permission denied

Extracted Assets

Firebase Configuration

{
  apiKey: "AIzaSyAUWgM16-zsQX6Q-gYkqfE4c_VinovFMbU",
  authDomain: "bundhayaspeedboat-produc-91aec.firebaseapp.com",
  projectId: "bundhayaspeedboat-produc-91aec",
  storageBucket: "bundhayaspeedboat-produc-91aec.appspot.com",
  messagingSenderId: "1093653649303",
  appId: "1:1093653649303:web:411bb35e3e89fe162616d2",
  measurementId: "G-FQ7HPNSDGH"
}

Location IDs (Hashed)

Location	ID
Koh Lanta (Saladan Pier)	`9f8bb780d62530df2c99`
Phi Phi (Tonsai Pier)	`fcdac18bfe74672a6a09`
Koh Mook	`1195251ea7e8d8a923f8`
Koh Ngai	`1f820d363f3094cf1d5d`
Koh Yao Noi	`35a5099983bd5e133980`
Pakbara Pier	`3e7f8b6b4ec4694f2e36`
Koh Lipe	`52d84b6706b53efa57f9`
Koh Yao Yai	`5984e57521fddb8a88b9`
Koh Bulone	`8e07bc436a899101d250`
Phuket (Rassada Pier)	`987912440a3169e8e0f9`
Ao Nang (Noparat Thara Pier)	`b00421377a6dfcacfb51`
Koh Jum	`b9d35ea79c514258e7de`
Koh Kradan	`bf1e7cb308a05e21ba7f`
Railay (East Railay Bay Beach Floating Pier)	`c5a145ca1f987cedb59c`

Booking URL Structure

https://www.bundhayaspeedboat.com/booking?
  isOneWay=true
  &adult=1
  &olderChild=0
  &smallChild=0
  &infant=0
  &unixStartDate=1762592876
  &unixEndDate=1762592876
  &bookingType=SPEEDBOAT
  &selectedDeparture=9f8bb780d62530df2c99
  &selectedArrival=Phiphi+(Tonsai+Pier)

Availability Data Structure

{
  departure: "13:00",
  arrival: "13:30",
  adult: "700.00",
  olderChild: "600.00",
  smallChild: "FREE",
  infant: "FREE",
  totalPrice: "700.00",
  status: "Sold Out" // or booking enabled
}

Integration Options - UPDATED WITH NEW FINDINGS

Option A: Firebase SDK Direct Query (NOW PROVEN POSSIBLE!)

Pros:

✅ Real-time seat availability
✅ Accurate pricing data
✅ No scraping needed - direct Firestore queries
✅ Already implemented in tests/ directory

Cons:

⚠️ Depends on their Firestore security rules (could change)
⚠️ Using competitor's infrastructure (legal gray area)
⚠️ No SLA or reliability guarantee

Effort: ✅ LOW - We've already cracked it! Working test code exists.

Implementation Path:

// Query Koh Lanta routes
const routesRef = collection(db, "routes");
const lanta Query = query(routesRef, where("id", "==", "9f8bb780d62530df2c99"));
const snapshot = await getDocs(lantaQuery);

snapshot.forEach(doc => {
  const data = doc.data();
  const phiPhiRoute = data.arrivals.find(a => a.arrival.includes("Phi Phi"));
  const todayAvailability = phiPhiRoute.allotment.find(a =>
    a.date.seconds === Math.floor(Date.now() / 1000)
  );
  console.log(`Seats available: ${todayAvailability.seatQty}`);
});

Option B: Playwright DOM Scraping (No Longer Needed!)

Status: ❌ OBSOLETE - We have direct Firestore access

Option C: Build Independent System

Pros:

✅ Full control, no external dependencies
✅ Legal clarity (our own data)
✅ Reliable, maintainable
✅ Can customize availability logic

Cons:

⚠️ Manual schedule updates required
⚠️ No automatic seat tracking (must implement ourselves)

Effort: Medium (one-time setup, ongoing maintenance)

Recommendation - REVISED

Three-Phase Approach:

Phase 1 (Launch): Use Firebase SDK integration

Fastest time to market
Real availability data immediately
Low development effort (code already exists)
Risk mitigation: Monitor for permission changes

Phase 2 (Post-Launch): Build hybrid system

Cache Bundhaya data locally
Add our own availability overrides
Graceful fallback if Firestore access lost

Phase 3 (Scale): Independent availability system

Full control over inventory
Custom pricing rules
Multi-operator support (beyond just Bundhaya)

Immediate Action: Deploy Firebase SDK integration from tests/ for launch!

📋 NEXT ACTIONS

Immediate (Before Launch) - P0 BLOCKERS

Fix hard-coded localhost URLs in checkout.ts
Fix hard-coded ops email in cancel-booking.ts
Add request validation to checkout endpoint
Add try/catch to checkout request parsing
Resolve refund logic inconsistency (choose one strategy)
Add environment variable validation at startup
Set up error monitoring (Sentry or CloudFlare Workers Analytics)

NEW OPPORTUNITY - Firebase Availability Integration

Create availability API endpoint using Firebase SDK from tests/
Integrate real-time seat availability into booking flow
Add pricing sync from Bundhaya routes collection
Implement caching layer for Firebase queries (reduce load)
Add monitoring for Firestore permission changes
Build fallback if Firebase access is revoked

Short-term (Post-Launch Polish)

Add idempotency keys to Stripe operations
Implement webhook event logging
Add duplicate booking prevention
Implement rate limiting on API endpoints
Add structured logging throughout
Create Stripe customers for repeat bookings
Handle additional webhook event types
Add Stripe API version lock

Long-term (Enhancements)

Build independent schedule/availability system
Add multi-currency support
Implement booking status tracking
Add customer portal for booking management
Set up comprehensive monitoring dashboard

🌍 ENVIRONMENT VARIABLES REQUIRED

Currently Required

STRIPE_SECRET_KEY ✅
STRIPE_WEBHOOK_SECRET ✅
GOOGLE_SHEET_ID ✅
GOOGLE_SHEET_TAB (optional, defaults to "Bookings") ✅
GOOGLE_CLIENT_EMAIL ✅
GOOGLE_PRIVATE_KEY ✅
RESEND_API_KEY ✅
LINE_CHANNEL_SECRET ✅
LINE_CHANNEL_ACCESS_TOKEN ✅
SITE_URL (defaults to localhost - MUST SET FOR PRODUCTION) ⚠️

Missing (Need to Add)

OPS_EMAIL - Operations team email for refund notifications ❌
STRIPE_API_VERSION - Lock Stripe API version (e.g., "2023-10-16") ❌

📊 RISK ASSESSMENT

Risk	Severity	Likelihood	Mitigation Status
Users redirected to localhost after payment	🔴 Critical	High	❌ Not fixed
Ops team not notified of refunds	🔴 Critical	High	❌ Not fixed
Duplicate bookings from webhook replays	🟡 Medium	Medium	❌ Not mitigated
Invalid data crashes checkout	🟡 Medium	Medium	❌ Not validated
Rate limit abuse costs money	🟡 Medium	Low	❌ Not protected
Production uses test Stripe keys	🟡 Medium	Low	❌ Not detected
Webhook failures go unnoticed	🟠 Low	Medium	❌ No logging

🎯 DEFINITION OF DONE - PRODUCTION READY

📝 NOTES

Bundhaya pricing observed: 700 THB adult (Lanta ↔ Phi Phi)
Our pricing: 1500 THB (competitive positioning or error?)
Departure times: Currently hard-coded to 09:30 for both routes
Refund window: 2 hours before departure (enforced)
Stripe processes in smallest currency unit (150000 = 1500 THB)

Status maintained by: Claude Code Gist ID: 5afd007a216d3dfa500411c3646048bd Gist URL: https://gist.github.com/icanhasjonas/5afd007a216d3dfa500411c3646048bd

icanhasjonas/STATUS.md