 Server : Apache/2 System : Linux server-15-235-50-60 5.15.0-164-generic #174-Ubuntu SMP Fri Nov 14 20:25:16 UTC 2025 x86_64 User : gositeme ( 1004) PHP Version : 8.2.29 Disable Function : exec,system,passthru,shell_exec,proc_close,proc_open,dl,popen,show_source,posix_kill,posix_mkfifo,posix_getpwuid,posix_setpgid,posix_setsid,posix_setuid,posix_setgid,posix_seteuid,posix_setegid,posix_uname Directory : /home/gositeme/domains/soundstudiopro.com/private_html/ |
# Stephane Plan Sync Issue - Root Cause Analysis & Permanent Fix
**Date:** 2025-12-19
**User:** Stephane (ID: 5, Email: stevenberg450@gmail.com)
**Issue:** Subscription exists in Stripe but not properly synced to database
---
## What Happened
Stephane paid and upgraded to Essential plan ($5/month), but:
- ✅ Subscription exists in Stripe (3 subscriptions found)
- ❌ Plan not showing correctly in database (`users.plan` was 'free')
- ❌ `user_subscriptions` table had incorrect or missing records
- ❌ User saw "free" plan on various pages despite having active subscription
**Temporary Fix Applied:**
- Created `sync_subscription_from_stripe.php` script
- Manually synced subscription from Stripe to database
- Updated `users.plan` to 'essential'
- Created/updated `user_subscriptions` record
---
## Root Causes
### 1. **Webhook Failure Scenarios**
The subscription recording relies on two mechanisms:
**A. Primary: `subscription_success.php` (Immediate)**
- Runs when user completes Stripe checkout and is redirected
- **Failure Points:**
- User closes browser before redirect completes
- Network timeout during redirect
- User uses back button
- JavaScript error prevents redirect
**B. Backup: `webhooks/stripe.php` (Delayed)**
- Runs when Stripe sends `customer.subscription.created` event
- **Failure Points:**
- Webhook endpoint not configured in Stripe
- Webhook signature verification fails
- Network issues preventing webhook delivery
- Webhook processing error (exception thrown)
- User lookup fails (customer ID mismatch)
### 2. **User Identification Issues**
In `handleSubscriptionCreated()` (webhooks/stripe.php:1265-1281):
```php
// Get user by Stripe customer ID
$customer_id = $subscription->customer;
$stmt = $pdo->prepare("SELECT id FROM users WHERE stripe_customer_id = ?");
$stmt->execute([$customer_id]);
$user = $stmt->fetch(PDO::FETCH_ASSOC);
if (!$user) {
// Try to get user from metadata
$metadata = $subscription->metadata ?? (object)[];
$user_id = $metadata->user_id ?? null;
if (!$user_id) {
throw new Exception("User not found for customer: {$customer_id}");
}
}
```
**Failure Scenarios:**
- `stripe_customer_id` not set in `users` table when subscription created
- Customer ID mismatch (user has multiple Stripe customers)
- Metadata missing `user_id` (shouldn't happen, but possible)
### 3. **Plan Detection Issues**
Plan detection has multiple fallbacks (webhooks/stripe.php:1283-1312):
1. Match price ID to config (most reliable)
2. Check price metadata
3. Check subscription metadata
4. Default to 'essential'
**Failure Scenarios:**
- Price ID not in `config/subscription_plans.php`
- Price metadata missing
- Subscription metadata missing
- All fallbacks fail → defaults to 'essential' (might be wrong plan)
### 4. **Database Transaction Failures**
If database transaction fails (webhooks/stripe.php:1362-1431):
- Exception thrown → transaction rolled back
- Subscription not recorded
- Error logged but webhook returns 200 (Stripe won't retry)
- **No automatic retry mechanism**
### 5. **Multiple Subscriptions**
Stephane had **3 subscriptions** in Stripe:
- `sub_1SbU0PD0zXLMB4gHAhMLA8bt` (active)
- `sub_1SbTqRD0zXLMB4gHIzHNGJlt` (active)
- `sub_1SX3lTD0zXLMB4gHw7Sh8piJ` (active, was in DB as 'free')
**Root Cause:** User created multiple subscriptions (possibly due to clicking "Subscribe" multiple times, or network issues causing duplicate submissions).
**Current Fix:** Webhook now cancels old subscriptions (lines 1318-1429), but this only works if webhook fires successfully.
---
## Permanent Fix Strategy
### 1. **Automatic Sync on Login** ✅ RECOMMENDED
Add subscription sync check when user logs in:
**File:** `auth/login.php` or `includes/header.php`
```php
// After successful login, check if subscription needs syncing
if (isset($_SESSION['user_id'])) {
require_once __DIR__ . '/utils/subscription_helpers.php';
$user = getUserById($_SESSION['user_id']);
if ($user && !empty($user['stripe_customer_id'])) {
// Check if user has active subscription in Stripe but not in DB
$has_active_sub = hasActiveSubscription($_SESSION['user_id']);
$effective_plan = getEffectivePlan($_SESSION['user_id']);
// If user has Stripe customer ID but no active subscription in DB,
// trigger background sync (don't block login)
if (!$has_active_sub && $effective_plan === 'free' && !empty($user['stripe_customer_id'])) {
// Queue sync job (or run async)
// This ensures subscription is synced within minutes of login
}
}
}
```
### 2. **Periodic Sync Cron Job** ✅ RECOMMENDED
Create daily cron job to sync all subscriptions:
**File:** `cron/sync_subscriptions_from_stripe.php`
```php
<?php
/**
* Daily Subscription Sync from Stripe
* Syncs all active subscriptions from Stripe to database
* Runs daily to catch any missed webhooks
*/
require_once __DIR__ . '/../config/database.php';
require_once __DIR__ . '/../utils/subscription_helpers.php';
$stripe_secret = 'sk_live_...';
$pdo = getDBConnection();
// Get all users with Stripe customer IDs
$stmt = $pdo->query("SELECT id, email, stripe_customer_id FROM users WHERE stripe_customer_id IS NOT NULL AND stripe_customer_id != ''");
$users = $stmt->fetchAll(PDO::FETCH_ASSOC);
foreach ($users as $user) {
try {
// Fetch subscriptions from Stripe
$ch = curl_init();
curl_setopt($ch, CURLOPT_URL, "https://api.stripe.com/v1/subscriptions?customer=" . urlencode($user['stripe_customer_id']) . "&limit=10&status=all");
curl_setopt($ch, CURLOPT_HTTPHEADER, ['Authorization: Bearer ' . $stripe_secret]);
curl_setopt($ch, CURLOPT_RETURNTRANSFER, true);
$response = curl_exec($ch);
$http_code = curl_getinfo($ch, CURLINFO_HTTP_CODE);
curl_close($ch);
if ($http_code === 200) {
$subscriptions_data = json_decode($response, true);
$subscriptions = $subscriptions_data['data'] ?? [];
// Sync each active subscription
foreach ($subscriptions as $stripe_sub) {
if (in_array($stripe_sub['status'], ['active', 'trialing'])) {
// Use same logic as webhook to sync
syncSubscriptionFromStripe($stripe_sub, $user['id']);
}
}
}
} catch (Exception $e) {
error_log("Cron sync error for user {$user['id']}: " . $e->getMessage());
}
}
```
**Cron Setup:**
```bash
# Run daily at 2 AM
0 2 * * * /usr/bin/php /home/gositeme/domains/soundstudiopro.com/public_html/cron/sync_subscriptions_from_stripe.php
```
### 3. **Improved Webhook Error Handling** ✅ RECOMMENDED
**File:** `webhooks/stripe.php`
**Current Issue:** If webhook fails, it returns 200 (success) to Stripe, so Stripe won't retry.
**Fix:** Return appropriate HTTP codes and add retry logic:
```php
try {
handleSubscriptionCreated($subscription);
http_response_code(200);
echo json_encode(['status' => 'success']);
} catch (Exception $e) {
error_log("Webhook error: " . $e->getMessage());
// Return 500 to trigger Stripe retry (for transient errors)
// Return 400 for permanent errors (won't retry)
if (strpos($e->getMessage(), 'User not found') !== false) {
// Permanent error - don't retry
http_response_code(400);
echo json_encode(['error' => 'User not found']);
} else {
// Transient error - Stripe will retry
http_response_code(500);
echo json_encode(['error' => 'Processing error']);
}
}
```
### 4. **Self-Healing in getEffectivePlan()** ✅ RECOMMENDED
**File:** `utils/subscription_helpers.php`
Add automatic sync trigger when discrepancy detected:
```php
function getEffectivePlan($user_id) {
// ... existing code ...
// If we detect a discrepancy (user has Stripe customer ID but no active subscription),
// trigger background sync
$user = getUserById($user_id);
if ($user && !empty($user['stripe_customer_id'])) {
$active_sub = hasActiveSubscription($user_id);
if (!$active_sub) {
// Queue sync job (don't block current request)
// This will sync in background
queueSubscriptionSync($user_id, $user['stripe_customer_id']);
}
}
// ... rest of function ...
}
```
### 5. **Webhook Retry Queue** ⚠️ ADVANCED
For critical reliability, implement a retry queue:
**File:** `webhooks/stripe_retry_queue.php`
- Store failed webhook events in database
- Retry with exponential backoff
- Alert admin after N failed retries
---
## Implementation Priority
### 🔴 **High Priority (Do First)**
1. **Periodic Sync Cron Job** - Catches all missed webhooks daily
2. **Improved Webhook Error Handling** - Ensures Stripe retries on failures
3. **Automatic Sync on Login** - Fixes issues immediately when user logs in
### 🟡 **Medium Priority**
4. **Self-Healing in getEffectivePlan()** - Proactive sync when discrepancy detected
5. **Better Logging & Monitoring** - Track sync failures and alert admin
### 🟢 **Low Priority (Nice to Have)**
6. **Webhook Retry Queue** - Advanced reliability (only if issues persist)
---
## Testing the Fix
After implementing permanent fixes:
1. **Test Webhook Failure:**
- Temporarily break webhook endpoint
- Create subscription
- Verify cron job syncs it within 24 hours
2. **Test User Login Sync:**
- Create subscription with webhook disabled
- Log in as user
- Verify subscription synced immediately
3. **Test Multiple Subscriptions:**
- Create multiple subscriptions for same user
- Verify only one active, others canceled
---
## Monitoring
Add monitoring to track:
- Number of subscriptions synced per day (cron job)
- Webhook failure rate
- Users with Stripe customer ID but no active subscription
- Plan detection failures (price ID not found)
---
## Summary
**Root Cause:** Webhook failures + no automatic recovery mechanism
**Permanent Fix:** Multi-layered approach:
1. Daily cron sync (catches all missed webhooks)
2. Login-time sync (immediate fix for users)
3. Better webhook error handling (ensures retries)
4. Self-healing detection (proactive sync)
This ensures subscriptions are **always** synced, even if webhooks fail.