Payment Tracking Reference Guide

StratIQX Payment System v2.0 - Complete Reference

This document provides comprehensive reference information for the StratIQX payment tracking system, including flows, error codes, status management, and troubleshooting.

---

Payment Flow Overview

High-Level Architecture

Frontend (Onboarding) → Payment Service → Stripe → Database → Webhooks

Complete Payment Journey

1. User completes onboarding → Service Selection
2. User reaches payment screen → Form loads (no payment intent yet)
3. User fills payment details → Validation occurs locally  
4. User clicks "Pay" → Payment intent created + Smart duplicate detection
5. Stripe processes payment → Webhook confirms/fails payment
6. Database updated → Emails sent → Service activated

---

Payment Status Lifecycle

Service Purchase Statuses

Status	Description	Triggers	Next Status
pending	Payment intent created, processing	User clicks "Pay"	active or failed
active	Payment succeeded, service activated	Webhook: payment_intent.succeeded	completed
in_progress	Service being delivered	Service delivery starts	completed
completed	Service fully delivered	Delivery confirmation	Final
cancelled	Payment cancelled by user	User cancellation	Final
failed	Payment failed permanently	Stripe failure + retries exhausted	Final
abandoned	Payment intent abandoned	Auto-cleanup (30+ min old)	Final

Status Transitions

pending → active → in_progress → completed ✅ (Happy path)
pending → failed ❌ (Payment failure)
pending → cancelled ❌ (User cancellation)  
pending → abandoned 🗑️ (Auto-cleanup)

---

Payment Error Codes Reference

Core Payment Errors (PAY_xxx)

Code	Description	User Message	Resolution
PAY_001	Missing/invalid required fields	"Please fill in all required payment information"	Validate form fields
PAY_002	Service not found or inactive	"Selected service is not available"	Check service configuration
PAY_003	Insufficient payment amount	"Minimum payment of $X required"	Check service pricing
PAY_004	Stripe API error	"Payment processing error, please try again"	Check Stripe connection
PAY_005	Database error	"Unable to process payment, please try again"	Check DB connection
PAY_006	Authentication failed	"Please log in to complete payment"	Re-authenticate user
PAY_007	Invalid promo code	"Promo code is invalid or expired"	Validate/remove promo
PAY_008	Service already active	"You already have this service active"	Check user services
PAY_009	Duplicate payment detected	"Payment for this service is already processing"	Wait or refresh
PAY_010	Abandoned payment cleanup	"Previous payment attempt was cleaned up"	Informational

Service Errors (SVC_xxx)

Code	Description	Resolution
SVC_001	Service not found	Check service ID exists and is active
SVC_002	Service already active for user	Check for duplicate service purchases
SVC_003	Service activation failed	Review service_purchases status and error logs

Delivery Errors (DEL_xxx)

Code	Description	Resolution
DEL_001	Delivery creation failed	Check purchase_id and delivery requirements
DEL_002	Delivery URL generation failed	Verify storage service connectivity

---

Smart Duplicate Detection System

Detection Logic Flow

1. Cleanup Phase (Auto-runs on every payment attempt)
   ├── Find payments older than 30 minutes with status 'pending'
   ├── Mark as 'abandoned' with cleanup metadata
   └── Log cleanup actions

2. Detection Phase  
   ├── Query for 'pending' payments within last 15 minutes
   ├── Check same user + same service combination
   └── Return detailed timing information

3. Decision Phase
   ├── IF duplicate found → Block with helpful error message
   └── IF no duplicate → Allow payment to proceed

Key Parameters

• Cleanup Threshold: 30 minutes (abandoned payment auto-cleanup)
• Detection Window: 15 minutes (active duplicate prevention)
• Blocked Status: pending only (actively processing payments)
• Allowed Status: active, completed, failed, abandoned (no blocking)

Error Messages

// Smart error with timing information
"A payment for this service is actively processing (started 3 minutes ago). Please wait or refresh the page."

// Cleanup notification  
"Previous payment attempt was automatically cleaned up due to inactivity"

---

Payment Intent Lifecycle

Lazy Payment Intent Creation (Option A)

Component Mount:
├── Load Stripe Elements ✅
├── Initialize form fields ✅  
├── NO payment intent creation ❌ (prevents premature DB records)
└── Set loading = false ✅

User Submission:
├── Validate form fields
├── Create payment intent NOW 🎯
├── Smart duplicate detection
├── Process payment with fresh client secret
└── Complete or fail payment

Payment Intent Metadata

{
  userId: "user_123",
  serviceType: "strategic-analysis-premium", 
  serviceName: "Strategic Analysis Premium",
  originalAmount: "297.00",
  depositAmount: "297.00",
  promoCode: "LAUNCH50",
  environment: "production",
  timestamp: "2025-09-01T00:30:00.000Z"
}

---

Database Schema Reference

Service Purchases Table

CREATE TABLE service_purchases (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL,
  service_id TEXT NOT NULL,
  amount REAL NOT NULL,
  deposit_paid REAL NOT NULL,
  balance_due REAL DEFAULT 0,
  status TEXT DEFAULT 'pending',
  stripe_payment_id TEXT NOT NULL,
  metadata TEXT DEFAULT '{}',
  created_at TEXT NOT NULL,
  updated_at TEXT NOT NULL
);

Smart Tracking Metadata

{
  "source": "payment_handler_v2",
  "payment_intent_created_at": "2025-09-01T00:30:00.000Z",
  "duplicate_detection_enabled": true,
  "detection_window_minutes": 15,
  "cleanup_threshold_minutes": 30,
  "initial_status": "pending",
  "user_agent": "api_request",
  "abandonment_reason": "auto_cleanup_30min",
  "abandoned_at": "2025-09-01T01:00:00.000Z"
}

---

Troubleshooting Guide

Common Issues & Solutions

Issue: "Payment already in progress" error

Symptoms: User gets duplicate payment error on legitimate retry Diagnosis: Check for pending payments within 15 minutes

SELECT * FROM service_purchases 
WHERE user_id = ? AND service_id = ? 
AND status = 'pending' 
AND created_at > datetime('now', '-15 minutes');

Solutions:

1. Wait 15 minutes for auto-cleanup
2. Manually mark old payment as 'abandoned'
3. Check if payment actually succeeded

Issue: Payments stuck in 'pending' status

Symptoms: Payments never transition to 'active' or 'failed' Diagnosis: Webhook processing failure Solutions:

1. Check webhook delivery in Stripe dashboard
2. Verify webhook signature configuration
3. Manually process webhook event
4. Check service bindings (AUTH_SERVICE, COMMUNICATION_SERVICE)

Issue: Auto-cleanup not working

Symptoms: Old pending payments not being cleaned up Diagnosis: Cleanup function not executing Solutions:

1. Check cleanupAbandonedPayments function logs
2. Verify database permissions for UPDATE operations
3. Check SQL syntax for datetime comparisons
4. Monitor cleanup execution in payment handler logs

---

Monitoring & Logging

Key Log Messages

// Successful duplicate detection
"✅ No duplicate payments found for user: user_123 service: premium-analysis"

// Duplicate payment blocked  
"🚫 Duplicate payment detected: { existingPaymentId: 'pi_123', minutesAgo: 5, status: 'pending' }"

// Abandoned payment cleanup
"🧹 Cleaned up 2 abandoned payment(s): user_123"

// Payment intent creation
"🚀 Creating payment intent for strategic-analysis-premium"

// Service purchase record  
"✅ Service purchase record created: { userId: 'user_123', paymentIntentId: 'pi_123', status: 'pending' }"

Health Check Endpoints

• Payment Service: https://stratiqx-payment-prod.michshat.workers.dev/health
• Service Status: https://stratiqx-payment-prod.michshat.workers.dev/status

Database Monitoring Queries

-- Check payment status distribution
SELECT status, COUNT(*) as count 
FROM service_purchases 
GROUP BY status 
ORDER BY count DESC;

-- Find payments older than detection window
SELECT * FROM service_purchases 
WHERE status = 'pending' 
AND created_at < datetime('now', '-15 minutes')
ORDER BY created_at DESC;

-- Recent duplicate detection attempts
SELECT * FROM service_purchases 
WHERE json_extract(metadata, '$.duplicate_detection_enabled') = true
ORDER BY created_at DESC 
LIMIT 10;

---

Testing Procedures

Test Duplicate Detection

1. Setup: Create payment intent for user/service
2. Test: Try to create another payment within 15 minutes
3. Expected: Second attempt blocked with timing info
4. Cleanup: Wait 30 minutes or manually set status to 'abandoned'

Test Abandoned Payment Cleanup

1. Setup: Create payment intent, don't complete
2. Wait: 31 minutes (or modify created_at in DB)
3. Test: Create new payment intent for same user/service
4. Expected: Old payment cleaned up, new payment allowed

Test Payment Flow End-to-End

1. Onboarding: Complete workflow to payment screen
2. Refresh: Refresh page multiple times
3. Expected: No "payment in progress" errors
4. Payment: Fill form and submit
5. Expected: Payment intent created only on submission
6. Webhook: Verify payment confirmation via webhook

---

Configuration Reference

Environment Variables

# Production
STRIPE_SECRET_KEY=sk_live_...
STRIPE_WEBHOOK_SECRET=whsec_...
ENVIRONMENT=production
AUTH_BASE_URL=https://auth.stratiqx.ai
COMMUNICATION_API_URL=https://comms.stratiqx.ai/send

# Development  
STRIPE_SECRET_KEY=sk_test_...
ENVIRONMENT=development

Service Bindings

• AUTH_SERVICE: stratiqx-identity-server-prod
• COMMUNICATION_SERVICE: stratiqx-communication-prod
• Database: stratiqx-main (D1)
• Cache: KV Namespace for service caching

---

Maintenance Tasks

Weekly Tasks

• Review abandoned payment cleanup effectiveness
• Monitor duplicate detection false positives
• Check webhook delivery success rates

Monthly Tasks

• Analyze payment status distribution trends
• Review error code frequency for improvements
• Update documentation with new patterns

As-Needed Tasks

• Clear old abandoned payments (older than 30 days)
• Update detection window parameters based on usage
• Adjust error messages based on user feedback

---

Last Updated: September 1, 2025
System Version: Payment Service v2.0
Detection Algorithm: Smart Duplicate Detection with Auto-Cleanup
Frontend: Lazy Payment Intent Creation Pattern