BILLING SYSTEM SPECIFICATION

Last Updated: 2025-11-09

Status: Active Started: 2025-10-30 Target Completion: 2025-12-20 Priority: High Category: Backend Development Component: Stripe Billing Integration Version: 1.0.0 Owner: Backend Team

Table of Contents

1. Executive Summary
2. Architecture Overview
3. Stripe Configuration
4. Pricing Model
5. Trial System
6. Subscription Lifecycle
7. Grace Period System
8. Webhook Handlers
9. Payment Method Management
10. Invoice Generation
11. Tax Handling
12. Chargeback Prevention
13. Cancellation & Refunds
14. Email Notifications
15. Database Schema
16. API Endpoints
17. Frontend Integration
18. Testing Strategy
19. Error Handling
20. Monitoring & Alerts

Executive Summary

The EPGOAT billing system is built on Stripe, implementing an annual-first pricing model with sophisticated trial conversion flows, grace period handling, and abuse prevention mechanisms.

Key Features

• Annual-First Pricing: $5-10/year for consumers, $99/year for resellers
• Flexible Trials: 1-month free (no CC) or 2-month free (with CC)
• Auto-Conversion: Automatic charging with aggressive pre-notifications
• Grace Periods: 7-day post-expiry grace with progressive communication
• Soft-Expire: EPG continues working with emoji/notice during grace period
• Tax Automation: Stripe Tax handles all sales tax calculations
• Chargeback Prevention: Multi-stage notification system (<1% chargeback rate)

Business Model

Revenue Projections

Assumptions: - 100 signups/month - 30% trial conversion rate - 80% choose annual over monthly - 5 new resellers/month

Month 1: - Trials: 100 (0 revenue) - Conversions: 30 (24 annual @ $10 = $240, 6 monthly @ $1.75 = $10.50) - Resellers: 5 @ $99 = $495 - Total MRR: $62.50 (annual amortized) + $10.50 (monthly) + $41.25 (reseller amortized) = $114.25

Month 12: - Total paying users: 360 annual, 72 monthly, 60 resellers - Total MRR: $300 + $126 + $495 = $921/month ($11,052/year ARR)

Architecture Overview

System Components

┌─────────────────────────────────────────────────────────────┐
│                    User's Browser                           │
│                                                              │
│  ┌──────────────┐  ┌──────────────┐  ┌──────────────┐     │
│  │   Public     │  │   Consumer   │  │   Reseller   │     │
│  │   Website    │  │    Portal    │  │    Portal    │     │
│  └──────┬───────┘  └──────┬───────┘  └──────┬───────┘     │
│         │                 │                  │              │
└─────────┼─────────────────┼──────────────────┼──────────────┘
          │                 │                  │
          ▼                 ▼                  ▼
  ┌────────────────────────────────────────────────────┐
  │         Stripe Elements (Embedded Forms)           │
  │  - Payment Method Collection                       │
  │  - PCI Compliance Handled                          │
  │  - 3D Secure Support                               │
  └────────────┬───────────────────────────────────────┘
               │
               ▼
  ┌────────────────────────────────────────────────────┐
  │              Stripe API                            │
  │  - Customer Creation                               │
  │  - Subscription Management                         │
  │  - Invoice Generation                              │
  │  - Payment Processing                              │
  │  - Tax Calculation (Stripe Tax)                    │
  └────────────┬──────────┬────────────────────────────┘
               │          │
               │          │ Webhooks (15+ event types)
               │          │
               ▼          ▼
  ┌────────────────────────────────────────────────────┐
  │      Cloudflare Workers (API)                      │
  │  - /api/subscriptions/*                            │
  │  - /api/webhooks/stripe                            │
  │  - /api/billing/*                                  │
  └────────────┬──────────┬────────────────────────────┘
               │          │
               ▼          ▼
       ┌──────────────┐  ┌──────────────┐
       │ Cloudflare   │  │   Resend/    │
       │      D1      │  │  SendGrid    │
       │  (Database)  │  │   (Emails)   │
       └──────────────┘  └──────────────┘

Data Flow: Trial to Paid Conversion

1. User Registers (Auth0)
   ↓
2. Auto-create in D1 (user_subscriptions table)
   - status: 'trial'
   - trial_start: now()
   - trial_end: now() + 30 days
   ↓
3. User Adds CC (Optional)
   ↓
4. Create Stripe Customer
   ↓
5. Attach Payment Method
   ↓
6. Create Stripe Subscription Schedule
   - Phase 1: Trial (60 days, $0)
   - Phase 2: Paid (annual, $10/year)
   ↓
7. Update D1
   - status: 'trial_with_cc'
   - trial_end: now() + 60 days
   - stripe_customer_id
   - stripe_subscription_id
   ↓
8. Email Notifications (Days 45, 53, 57, 59)
   ↓
9. Day 60: Stripe Auto-Charges
   ↓
10. Webhook: invoice.paid
   ↓
11. Update D1
   - status: 'active'
   - current_period_start: now()
   - current_period_end: now() + 365 days
   ↓
12. Email: "Payment Successful"

Stripe Configuration

Account Setup

Stripe Account Type: Standard Tax ID Collection: Enabled (for EU VAT, GST) Fraud Prevention: Stripe Radar (Standard) Payment Methods: Card (Visa, Mastercard, Amex)

Products

Product 1: Consumer Trial

{
  "id": "prod_consumer_trial",
  "name": "EPGOAT Consumer - Trial",
  "description": "30-day free trial, no credit card required",
  "active": true,
  "metadata": {
    "tier": "consumer",
    "type": "trial"
  }
}

Price:

{
  "id": "price_consumer_trial",
  "product": "prod_consumer_trial",
  "unit_amount": 0,
  "currency": "usd",
  "recurring": {
    "interval": "month",
    "interval_count": 1
  },
  "trial_period_days": 30
}

Product 2: Consumer Monthly

{
  "id": "prod_consumer_monthly",
  "name": "EPGOAT Consumer - Monthly",
  "description": "Month-to-month billing",
  "active": true,
  "metadata": {
    "tier": "consumer",
    "type": "monthly"
  }
}

Price:

{
  "id": "price_consumer_monthly",
  "product": "prod_consumer_monthly",
  "unit_amount": 175,  // $1.75
  "currency": "usd",
  "recurring": {
    "interval": "month",
    "interval_count": 1
  }
}

Product 3: Consumer Annual

{
  "id": "prod_consumer_annual",
  "name": "EPGOAT Consumer - Annual",
  "description": "Annual billing (52% savings vs monthly)",
  "active": true,
  "metadata": {
    "tier": "consumer",
    "type": "annual"
  }
}

Price:

{
  "id": "price_consumer_annual",
  "product": "prod_consumer_annual",
  "unit_amount": 1000,  // $10.00
  "currency": "usd",
  "recurring": {
    "interval": "year",
    "interval_count": 1
  }
}

Product 4: Reseller Annual (per provider pack)

{
  "id": "prod_reseller_annual",
  "name": "EPGOAT Reseller - Annual Provider Pack",
  "description": "Includes 50 EPG access keys per provider",
  "active": true,
  "metadata": {
    "tier": "reseller",
    "type": "annual",
    "keys_included": "50"
  }
}

Price:

{
  "id": "price_reseller_annual",
  "product": "prod_reseller_annual",
  "unit_amount": 9900,  // $99.00
  "currency": "usd",
  "recurring": {
    "interval": "year",
    "interval_count": 1
  }
}

Product 5: Additional Keys (Reseller Add-on)

{
  "id": "prod_additional_keys",
  "name": "Additional 50-Key Block",
  "description": "Add 50 more EPG access keys to your provider pack",
  "active": true,
  "metadata": {
    "tier": "reseller",
    "type": "addon",
    "keys_count": "50"
  }
}

Price:

{
  "id": "price_additional_keys",
  "product": "prod_additional_keys",
  "unit_amount": 9900,  // $99.00
  "currency": "usd",
  "type": "recurring",
  "recurring": {
    "interval": "year",
    "interval_count": 1,
    "usage_type": "licensed"
  }
}

Stripe Tax Configuration

Enable Stripe Tax:

// Enable on account
stripe.tax.settings.update({
  defaults: {
    tax_behavior: 'exclusive',  // Show tax separately on invoice
    tax_code: 'txcd_10000000'   // SaaS - Software as a Service
  }
});

Tax Calculation: - Automatic based on customer location - EU VAT handled automatically - US sales tax by state - Customer tax ID validation (for B2B)

Pricing Model

Consumer Tiers Comparison

Reseller Tiers

Pricing Psychology

Why Annual-First? 1. Lower Churn: Annual commitments reduce monthly churn from ~5% to ~15%/year 2. Predictable Revenue: 12 months of revenue upfront 3. Lower Transaction Fees: Stripe fees on $10 annual = $0.59 vs $21 monthly = $1.26 4. Customer Perception: $10/year feels like better value than $1.75/month

Why $1.75/month option? - Trial users who aren't ready for annual commitment - "Try before you buy" for 1-2 months - Conversion funnel: Trial → Monthly (1-2 months) → Annual

Trial System

Trial Types

Type 1: Free Trial (No Credit Card)

Flow: 1. User registers with email/password or Google OAuth 2. Auth0 post-registration hook creates user in D1 3. System generates EPG access key 4. User immediately gets EPG URL: https://epgo.at/<key>/tps.xml 5. Trial lasts 30 days 6. No automatic payment

Database State:

INSERT INTO user_subscriptions (
  user_id,
  plan_type,
  status,
  trial_start,
  trial_end,
  current_period_start,
  current_period_end,
  stripe_customer_id,
  stripe_subscription_id
) VALUES (
  '<user_id>',
  'consumer_trial',
  'trial',
  NOW(),
  NOW() + INTERVAL '30 days',
  NOW(),
  NOW() + INTERVAL '30 days',
  NULL,  -- No Stripe customer yet
  NULL   -- No Stripe subscription yet
);

Email Sequence: - Day 0: Welcome email with EPG URL - Day 21: "9 days left in your trial" + upgrade CTA - Day 28: "2 days left in your trial" + upgrade CTA - Day 30: "Your trial has expired" + upgrade CTA - Day 31-37: Daily "Renew now" emails (grace period)

Type 2: Enhanced Trial (With Credit Card)

Flow: 1. User registers (same as Type 1) 2. User adds credit card during trial 3. System creates Stripe Customer 4. System creates Stripe Subscription Schedule: - Phase 1 (Days 1-60): $0/month (trial) - Phase 2 (Day 61+): $10/year (annual plan) 5. Trial extended to 60 days (2 months) 6. Auto-charge on Day 61

Stripe Subscription Schedule:

const schedule = await stripe.subscriptionSchedules.create({
  customer: customerId,
  start_date: 'now',
  end_behavior: 'release',
  phases: [
    {
      // Phase 1: Extended trial (60 days)
      items: [{ price: 'price_consumer_trial' }],
      iterations: 2,  // 2 months at $0
      trial: true,
      trial_end: Math.floor(Date.now() / 1000) + (60 * 24 * 60 * 60)
    },
    {
      // Phase 2: Paid annual
      items: [{ price: 'price_consumer_annual' }],
      iterations: null,  // Indefinite
      billing_cycle_anchor: 'phase_start'
    }
  ],
  metadata: {
    epgoat_user_id: userId,
    trial_type: 'enhanced_with_cc'
  }
});

Database State:

UPDATE user_subscriptions
SET
  plan_type = 'consumer_annual',
  status = 'trial_with_cc',
  trial_end = NOW() + INTERVAL '60 days',
  stripe_customer_id = '<customer_id>',
  stripe_subscription_id = '<subscription_id>',
  will_auto_charge = TRUE,
  auto_charge_date = NOW() + INTERVAL '60 days',
  auto_charge_amount = 1000  -- $10.00
WHERE user_id = '<user_id>';

Email Sequence (Aggressive Pre-Charge): - Day 0: Welcome email + "You'll get 2 free months!" - Day 45: "Charging $10 in 15 days" (can cancel anytime) - Day 53: "Charging $10 in 7 days" (cancel now to avoid charge) - Day 57: "Charging $10 in 3 days" (last chance to cancel) - Day 59: "Charging $10 tomorrow" (final warning) - Day 60: Auto-charge attempt - Success: "Payment successful! Your annual subscription is now active" - Failure: "Payment failed. Please update your payment method. You have 3 days."

Trial Conversion Optimization

Conversion Funnel:

100 Signups (No CC)
  ↓
  30% Add CC during trial → 30 Enhanced Trials (60 days)
  ↓
  85% Complete payment on Day 60 → 25.5 Paid Annual ($255 revenue)
  ↓
70 Remain on Free Trial (30 days)
  ↓
  20% Upgrade to Monthly → 14 Monthly ($24.50/month revenue)
  ↓
  10% Upgrade to Annual → 7 Annual ($70 revenue)
  ↓
56 Expire without converting → 0 revenue

Total Conversion: 46.5% (46.5 paying / 100 signups)
Total Month 1 Revenue: $325 + $24.50 = $349.50

Conversion Tactics: 1. Add CC Incentive: Highlight "2 free months" prominently during trial 2. Email Urgency: Progressive urgency in email copy (polite → urgent) 3. In-App Notices: Banner in consumer portal showing days remaining 4. Social Proof: Show # of happy customers, testimonials 5. Savings Calculator: "You're saving $11/year with annual vs monthly"

Subscription Lifecycle

State Machine

┌──────────────┐
│   TRIAL      │ ────────────────────────────────┐
│  (30 days)   │                                  │
└──────┬───────┘                                  │
       │                                          │
       │ Add CC                                   │ No CC, Expires
       ▼                                          ▼
┌──────────────┐                         ┌──────────────┐
│ TRIAL_WITH_CC│                         │ TRIAL_EXPIRED│
│  (60 days)   │                         │              │
└──────┬───────┘                         └──────┬───────┘
       │                                         │
       │ Day 60: Auto-charge                     │ User Upgrades
       ▼                                         │
┌──────────────┐◄────────────────────────────────┘
│    ACTIVE    │
│  (Annual)    │
└──────┬───────┘
       │
       │ Payment Fails
       ▼
┌──────────────┐
│  PAST_DUE    │ ─── 3 days ───┐
│ (Grace: 3d)  │                │
└──────┬───────┘                │
       │                        │ No Payment
       │ Payment Success        ▼
       │                 ┌──────────────┐
       │                 │  CANCELED    │
       │                 │              │
       │                 └──────────────┘
       │
       ▼
┌──────────────┐
│    ACTIVE    │
│ (Continued)  │
└──────┬───────┘
       │
       │ User Cancels
       ▼
┌──────────────┐
│CANCEL_AT_END │ ─── End of Period ───┐
│(Active until)│                       │
└──────────────┘                       │
                                       ▼
                                ┌──────────────┐
                                │  CANCELED    │
                                │              │
                                └──────┬───────┘
                                       │
                                       │ Grace Period (7 days)
                                       ▼
                                ┌──────────────┐
                                │   EXPIRED    │
                                │              │
                                └──────────────┘

Subscription Status Definitions

Subscription Creation

API Endpoint: POST /api/subscriptions/create

Request:

{
  "plan": "consumer_annual",
  "payment_method_id": "pm_1234567890",
  "trial_type": "enhanced"  // or "standard"
}

Backend Logic:

export async function createSubscription(req, res) {
  const { plan, payment_method_id, trial_type } = req.body;
  const userId = req.user.epgoat_user_id;

  try {
    // 1. Get or create Stripe customer
    let customer = await getStripeCustomer(userId);

    if (!customer) {
      customer = await stripe.customers.create({
        email: req.user.email,
        metadata: {
          epgoat_user_id: userId,
          auth0_user_id: req.user.sub
        }
      });

      // Store in D1
      await db.execute(
        'UPDATE users SET stripe_customer_id = ? WHERE id = ?',
        [customer.id, userId]
      );
    }

    // 2. Attach payment method
    await stripe.paymentMethods.attach(payment_method_id, {
      customer: customer.id
    });

    await stripe.customers.update(customer.id, {
      invoice_settings: {
        default_payment_method: payment_method_id
      }
    });

    // 3. Create subscription or subscription schedule
    let subscription;

    if (trial_type === 'enhanced') {
      // Enhanced trial: 60-day trial, then auto-charge
      const schedule = await stripe.subscriptionSchedules.create({
        customer: customer.id,
        start_date: 'now',
        end_behavior: 'release',
        phases: [
          {
            items: [{ price: getPriceId(plan) }],
            trial: true,
            trial_end: Math.floor(Date.now() / 1000) + (60 * 24 * 60 * 60),
            iterations: 1
          },
          {
            items: [{ price: getPriceId(plan) }],
            iterations: null
          }
        ],
        metadata: {
          epgoat_user_id: userId,
          trial_type: 'enhanced'
        }
      });

      subscription = { id: schedule.subscription };

      // Update D1
      await db.execute(`
        UPDATE user_subscriptions
        SET
          plan_type = ?,
          status = 'trial_with_cc',
          trial_end = datetime('now', '+60 days'),
          stripe_customer_id = ?,
          stripe_subscription_id = ?,
          stripe_schedule_id = ?,
          will_auto_charge = TRUE,
          auto_charge_date = datetime('now', '+60 days'),
          auto_charge_amount = ?
        WHERE user_id = ?
      `, [plan, customer.id, schedule.subscription, schedule.id, getPriceAmount(plan), userId]);

    } else {
      // Standard subscription: immediate charge
      subscription = await stripe.subscriptions.create({
        customer: customer.id,
        items: [{ price: getPriceId(plan) }],
        payment_behavior: 'default_incomplete',
        expand: ['latest_invoice.payment_intent'],
        metadata: {
          epgoat_user_id: userId
        }
      });

      // Update D1
      await db.execute(`
        UPDATE user_subscriptions
        SET
          plan_type = ?,
          status = 'active',
          stripe_customer_id = ?,
          stripe_subscription_id = ?,
          current_period_start = datetime('now'),
          current_period_end = datetime('now', '+1 year')
        WHERE user_id = ?
      `, [plan, customer.id, subscription.id, userId]);
    }

    // 4. Send confirmation email
    await sendEmail({
      to: req.user.email,
      template: trial_type === 'enhanced' ? 'enhanced_trial_started' : 'subscription_created',
      data: {
        user_name: req.user.name,
        plan_name: getPlanName(plan),
        amount: getPriceAmount(plan),
        trial_end: trial_type === 'enhanced' ? '60 days' : null
      }
    });

    res.json({
      success: true,
      subscription_id: subscription.id,
      status: trial_type === 'enhanced' ? 'trial_with_cc' : 'active'
    });

  } catch (error) {
    console.error('Subscription creation failed:', error);
    res.status(500).json({
      success: false,
      error: error.message
    });
  }
}

Grace Period System

Grace Period Types

Type 1: Payment Failure Grace (3 days)

Triggered by: Payment failure on renewal attempt

Flow: 1. Stripe attempts to charge card on renewal date 2. Payment fails (insufficient funds, expired card, etc.) 3. Stripe webhook: invoice.payment_failed 4. Update D1: status = 'past_due' 5. EPG access: Soft-blocked (shows error message in EPG with payment link) 6. Retry payment automatically after 3 days 7. If retry succeeds: status = 'active' 8. If retry fails: status = 'canceled', enter Type 2 grace period

Database State:

UPDATE user_subscriptions
SET
  status = 'past_due',
  payment_failed_at = NOW(),
  payment_retry_date = NOW() + INTERVAL '3 days',
  grace_period_end = NOW() + INTERVAL '3 days'
WHERE stripe_subscription_id = '<subscription_id>';

Email Sequence: - Day 0 (Immediate): "Payment failed. Please update your payment method." - Day 1: "Your payment is still pending. Update your card to restore access." - Day 2: "Final reminder: Payment retry tomorrow" - Day 3: Auto-retry - Success: "Payment successful! Your subscription is active again." - Failure: "Payment failed again. Your subscription has been canceled. You have 7 days to reactivate."

EPG Behavior During Past Due:

<!-- Soft-blocked: EPG still generates but shows error message -->
<?xml version="1.0" encoding="UTF-8"?>
<tv>
  <channel id="payment-notice">
    <display-name>⚠️ PAYMENT REQUIRED</display-name>
  </channel>
  <programme start="20251030000000" stop="20251030235959" channel="payment-notice">
    <title>Your EPGOAT subscription payment failed</title>
    <desc>Please update your payment method at https://app.epgoat.tv/subscription to restore full EPG access. You have 3 days before your subscription is canceled.</desc>
  </programme>

  <!-- Regular channels still appear but with limited info -->
  <channel id="nba1">
    <display-name>NBA 01</display-name>
  </channel>
  <programme start="20251030190000" stop="20251030220000" channel="nba1">
    <title>🔒 Access Locked - Payment Required</title>
    <desc>Update payment method to view event details</desc>
  </programme>
</tv>

Type 2: Post-Cancellation Grace (7 days)

Triggered by: User cancels or payment fails after retry

Flow: 1. Subscription canceled (user action or payment failure) 2. Update D1: status = 'canceled', grace_period_end = NOW() + 7 days 3. EPG access: Soft-blocked (similar to Type 1) 4. Progressive email campaign (daily) 5. Day 7: Hard-block EPG access 6. Update D1: status = 'expired'

Database State:

UPDATE user_subscriptions
SET
  status = 'canceled',
  canceled_at = NOW(),
  grace_period_start = NOW(),
  grace_period_end = NOW() + INTERVAL '7 days',
  will_hard_expire_at = NOW() + INTERVAL '7 days'
WHERE user_id = '<user_id>';

Email Sequence (Progressive Urgency): - Day 0: "Your subscription has been canceled. You have 7 days to reactivate." - Day 3: "4 days left to reactivate your subscription" - Day 5: "2 days left! Your EPG access will be removed soon." - Day 6: "Final day! Reactivate now to keep your access." - Day 7: "Your EPGOAT access has expired. Reactivate anytime."

Copy Tone Evolution:

Day 0 (Polite):
  Subject: We're sorry to see you go
  Body: Your EPGOAT subscription has been canceled. If this was a mistake,
        you can easily reactivate within the next 7 days...

Day 3 (Neutral):
  Subject: 4 days left to restore your EPG access
  Body: This is a reminder that your EPGOAT subscription is currently
        inactive. You have 4 days remaining to reactivate...

Day 5 (Urgent):
  Subject: ⚠️ Only 2 days left!
  Body: Your EPG access will be permanently removed in 2 days.
        Reactivate now to keep your service...

Day 6 (Very Urgent):
  Subject: 🚨 FINAL DAY to reactivate
  Body: This is your final reminder. Your EPG will stop working tomorrow
        unless you reactivate...

Day 7 (Expired):
  Subject: Your EPGOAT subscription has expired
  Body: Your EPG access has been removed. You can restart your subscription
        anytime at https://app.epgoat.tv/subscription

Soft-Expire EPG Feature

During Last 30 Days of Subscription:

<!-- Add emoji notice to every programme during last month -->
<programme start="20251030190000" stop="20251030220000" channel="nba1">
  <title>🔔 Lakers vs Celtics</title>
  <desc>Your subscription renews in 15 days. Update payment method at app.epgoat.tv

Lakers take on Celtics in Eastern Conference showdown...</desc>
</programme>

Implementation:

// In EPG generator
function addExpiryNotice(programme, daysUntilExpiry) {
  if (daysUntilExpiry <= 30 && daysUntilExpiry > 0) {
    // Add bell emoji to title
    programme.title = `🔔 ${programme.title}`;

    // Prepend notice to description
    const notice = `Your subscription renews in ${daysUntilExpiry} days. ` +
                   `Update payment method at app.epgoat.tv\n\n`;
    programme.description = notice + programme.description;
  }

  return programme;
}

During Grace Period:

<!-- Stronger warning during grace period -->
<programme start="20251030190000" stop="20251030220000" channel="nba1">
  <title>⚠️ Lakers vs Celtics</title>
  <desc>🚨 URGENT: Your subscription has expired! Reactivate within 5 days to restore full access.

Lakers vs Celtics - Limited information available</desc>
</programme>

Webhook Handlers

Stripe Webhooks Overview

Endpoint: POST /api/webhooks/stripe

Security: - Verify webhook signature using Stripe signing secret - Check event type and handle accordingly - Idempotency: Check if event already processed (use event.id in D1)

Events to Handle:

Webhook Handler Implementation

Main Handler:

import Stripe from 'stripe';

const stripe = new Stripe(process.env.STRIPE_SECRET_KEY);
const webhookSecret = process.env.STRIPE_WEBHOOK_SECRET;

export async function handleStripeWebhook(req, res) {
  const sig = req.headers['stripe-signature'];
  let event;

  try {
    // Verify webhook signature
    event = stripe.webhooks.constructEvent(req.body, sig, webhookSecret);
  } catch (err) {
    console.error('Webhook signature verification failed:', err.message);
    return res.status(400).send(`Webhook Error: ${err.message}`);
  }

  // Check if already processed (idempotency)
  const alreadyProcessed = await db.execute(
    'SELECT id FROM webhook_events WHERE stripe_event_id = ?',
    [event.id]
  );

  if (alreadyProcessed.rows.length > 0) {
    console.log(`Event ${event.id} already processed, skipping`);
    return res.json({ received: true, skipped: true });
  }

  // Log webhook event
  await db.execute(`
    INSERT INTO webhook_events (stripe_event_id, event_type, payload, created_at)
    VALUES (?, ?, ?, datetime('now'))
  `, [event.id, event.type, JSON.stringify(event)]);

  // Route to appropriate handler
  try {
    switch (event.type) {
      case 'invoice.paid':
        await handleInvoicePaid(event.data.object);
        break;

      case 'invoice.payment_failed':
        await handleInvoicePaymentFailed(event.data.object);
        break;

      case 'customer.subscription.updated':
        await handleSubscriptionUpdated(event.data.object);
        break;

      case 'customer.subscription.deleted':
        await handleSubscriptionDeleted(event.data.object);
        break;

      case 'checkout.session.completed':
        await handleCheckoutCompleted(event.data.object);
        break;

      default:
        console.log(`Unhandled event type: ${event.type}`);
    }

    res.json({ received: true });
  } catch (error) {
    console.error(`Webhook handler error for ${event.type}:`, error);
    res.status(500).json({ error: 'Webhook handler failed' });
  }
}

Handler: invoice.paid

async function handleInvoicePaid(invoice) {
  const subscriptionId = invoice.subscription;
  const customerId = invoice.customer;

  // Get user from D1
  const user = await db.execute(
    'SELECT id, email FROM users WHERE stripe_customer_id = ?',
    [customerId]
  ).then(r => r.rows[0]);

  if (!user) {
    console.error(`No user found for customer ${customerId}`);
    return;
  }

  // Update subscription status
  await db.execute(`
    UPDATE user_subscriptions
    SET
      status = 'active',
      last_payment_at = datetime('now'),
      last_payment_amount = ?,
      current_period_start = datetime(?, 'unixepoch'),
      current_period_end = datetime(?, 'unixepoch')
    WHERE stripe_subscription_id = ?
  `, [
    invoice.amount_paid,
    invoice.period_start,
    invoice.period_end,
    subscriptionId
  ]);

  // Log subscription event
  await db.execute(`
    INSERT INTO subscription_events (
      user_id,
      event_type,
      description,
      amount,
      metadata,
      created_at
    ) VALUES (?, 'payment_success', 'Payment received', ?, ?, datetime('now'))
  `, [user.id, invoice.amount_paid, JSON.stringify({ invoice_id: invoice.id })]);

  // Send email
  await sendEmail({
    to: user.email,
    template: 'payment_successful',
    data: {
      amount: (invoice.amount_paid / 100).toFixed(2),
      currency: invoice.currency.toUpperCase(),
      invoice_url: invoice.hosted_invoice_url,
      period_end: new Date(invoice.period_end * 1000).toLocaleDateString()
    }
  });
}

Handler: invoice.payment_failed

async function handleInvoicePaymentFailed(invoice) {
  const subscriptionId = invoice.subscription;
  const customerId = invoice.customer;
  const attemptCount = invoice.attempt_count;

  // Get user
  const user = await db.execute(
    'SELECT id, email FROM users WHERE stripe_customer_id = ?',
    [customerId]
  ).then(r => r.rows[0]);

  if (!user) return;

  // Update subscription to past_due
  await db.execute(`
    UPDATE user_subscriptions
    SET
      status = 'past_due',
      payment_failed_at = datetime('now'),
      payment_retry_date = datetime('now', '+3 days'),
      grace_period_end = datetime('now', '+3 days'),
      payment_failure_count = ?
    WHERE stripe_subscription_id = ?
  `, [attemptCount, subscriptionId]);

  // Log event
  await db.execute(`
    INSERT INTO subscription_events (
      user_id,
      event_type,
      description,
      metadata,
      created_at
    ) VALUES (?, 'payment_failed', ?, ?, datetime('now'))
  `, [
    user.id,
    `Payment attempt ${attemptCount} failed`,
    JSON.stringify({ invoice_id: invoice.id, attempt_count: attemptCount })
  ]);

  // Send urgent email
  await sendEmail({
    to: user.email,
    template: 'payment_failed',
    data: {
      amount: (invoice.amount_due / 100).toFixed(2),
      attempt_count: attemptCount,
      retry_date: new Date(Date.now() + 3 * 24 * 60 * 60 * 1000).toLocaleDateString(),
      update_payment_url: 'https://app.epgoat.tv/subscription'
    }
  });
}

Handler: customer.subscription.deleted

async function handleSubscriptionDeleted(subscription) {
  const customerId = subscription.customer;

  // Get user
  const user = await db.execute(
    'SELECT id, email FROM users WHERE stripe_customer_id = ?',
    [customerId]
  ).then(r => r.rows[0]);

  if (!user) return;

  // Update subscription to canceled
  await db.execute(`
    UPDATE user_subscriptions
    SET
      status = 'canceled',
      canceled_at = datetime('now'),
      grace_period_start = datetime('now'),
      grace_period_end = datetime('now', '+7 days'),
      will_hard_expire_at = datetime('now', '+7 days')
    WHERE stripe_subscription_id = ?
  `, [subscription.id]);

  // Deactivate EPG key (soft-expire)
  await db.execute(`
    UPDATE access_keys
    SET
      status = 'grace_period',
      expires_at = datetime('now', '+7 days')
    WHERE user_id = ? AND status = 'active'
  `, [user.id]);

  // Log event
  await db.execute(`
    INSERT INTO subscription_events (
      user_id,
      event_type,
      description,
      created_at
    ) VALUES (?, 'subscription_canceled', 'Subscription canceled', datetime('now'))
  `, [user.id]);

  // Send cancellation email
  await sendEmail({
    to: user.email,
    template: 'subscription_canceled',
    data: {
      grace_period_days: 7,
      reactivate_url: 'https://app.epgoat.tv/subscription/reactivate'
    }
  });

  // Schedule daily grace period emails
  for (let day = 1; day <= 7; day++) {
    await scheduleEmail({
      to: user.email,
      template: 'grace_period_reminder',
      send_at: new Date(Date.now() + day * 24 * 60 * 60 * 1000),
      data: {
        days_remaining: 7 - day,
        reactivate_url: 'https://app.epgoat.tv/subscription/reactivate'
      }
    });
  }
}

Payment Method Management

Add Payment Method

Frontend (React + Stripe Elements):

import { Elements, CardElement, useStripe, useElements } from '@stripe/react-stripe-js';
import { loadStripe } from '@stripe/stripe-js';

const stripePromise = loadStripe(process.env.REACT_APP_STRIPE_PUBLISHABLE_KEY);

export function AddPaymentMethodForm() {
  const stripe = useStripe();
  const elements = useElements();
  const [error, setError] = useState<string | null>(null);
  const [processing, setProcessing] = useState(false);

  const handleSubmit = async (e: React.FormEvent) => {
    e.preventDefault();

    if (!stripe || !elements) return;

    setProcessing(true);
    setError(null);

    // Create payment method
    const { error: stripeError, paymentMethod } = await stripe.createPaymentMethod({
      type: 'card',
      card: elements.getElement(CardElement)!,
    });

    if (stripeError) {
      setError(stripeError.message || 'Payment method creation failed');
      setProcessing(false);
      return;
    }

    // Send to backend
    try {
      const response = await api.post('/subscriptions/payment-method', {
        payment_method_id: paymentMethod!.id
      });

      if (response.success) {
        toast.success('Payment method added successfully');
        navigate('/consumer/subscription');
      }
    } catch (err: any) {
      setError(err.response?.data?.error || 'Failed to save payment method');
    } finally {
      setProcessing(false);
    }
  };

  return (
    <form onSubmit={handleSubmit} className="max-w-md mx-auto p-8">
      <h2 className="text-2xl font-bold mb-6">Add Payment Method</h2>

      <div className="mb-6">
        <label className="block text-sm font-semibold mb-2">Card Details</label>
        <div className="border rounded p-4">
          <CardElement
            options={{
              style: {
                base: {
                  fontSize: '16px',
                  color: '#424770',
                  '::placeholder': {
                    color: '#aab7c4',
                  },
                },
                invalid: {
                  color: '#9e2146',
                },
              },
            }}
          />
        </div>
      </div>

      {error && (
        <div className="bg-red-100 border border-red-400 text-red-700 px-4 py-3 rounded mb-4">
          {error}
        </div>
      )}

      <button
        type="submit"
        disabled={!stripe || processing}
        className="btn btn-primary w-full"
      >
        {processing ? 'Processing...' : 'Add Payment Method'}
      </button>

      <p className="text-xs text-gray-500 text-center mt-4">
        🔒 Secured by Stripe. Your card details are never stored on our servers.
      </p>
    </form>
  );
}

Backend:

export async function addPaymentMethod(req, res) {
  const { payment_method_id } = req.body;
  const userId = req.user.epgoat_user_id;

  try {
    // Get Stripe customer
    const user = await db.execute(
      'SELECT stripe_customer_id FROM users WHERE id = ?',
      [userId]
    ).then(r => r.rows[0]);

    if (!user.stripe_customer_id) {
      return res.status(400).json({ error: 'No Stripe customer found' });
    }

    // Attach payment method to customer
    await stripe.paymentMethods.attach(payment_method_id, {
      customer: user.stripe_customer_id
    });

    // Set as default payment method
    await stripe.customers.update(user.stripe_customer_id, {
      invoice_settings: {
        default_payment_method: payment_method_id
      }
    });

    // Update D1
    await db.execute(`
      UPDATE users
      SET
        has_payment_method = TRUE,
        default_payment_method_id = ?,
        updated_at = datetime('now')
      WHERE id = ?
    `, [payment_method_id, userId]);

    res.json({ success: true });
  } catch (error) {
    console.error('Add payment method failed:', error);
    res.status(500).json({ error: error.message });
  }
}

Update Payment Method

Flow: 1. User navigates to Subscription page 2. Clicks "Update Payment Method" 3. Stripe Elements modal appears 4. User enters new card details 5. Create new PaymentMethod via Stripe.js 6. Attach to customer and set as default 7. Detach old payment method (optional)

Remove Payment Method

Note: Only allow if subscription is canceled or user has multiple payment methods

Invoice Generation

Automatic Invoices

Stripe automatically generates invoices when: - Subscription created - Subscription renewed - Subscription upgraded/downgraded

Invoice Fields:

{
  "id": "in_1234567890",
  "customer": "cus_1234567890",
  "subscription": "sub_1234567890",
  "amount_due": 1000,  // $10.00
  "amount_paid": 1000,
  "currency": "usd",
  "status": "paid",
  "period_start": 1698710400,
  "period_end": 1730332800,
  "hosted_invoice_url": "https://invoice.stripe.com/i/...",
  "invoice_pdf": "https://pay.stripe.com/invoice/.../pdf",
  "lines": {
    "data": [
      {
        "description": "EPGOAT Consumer - Annual",
        "amount": 1000,
        "quantity": 1,
        "period": {
          "start": 1698710400,
          "end": 1730332800
        }
      }
    ]
  },
  "tax": 85,  // Calculated by Stripe Tax
  "total": 1085
}

Store Invoices in D1

CREATE TABLE invoices (
  id TEXT PRIMARY KEY,  -- Stripe invoice ID
  user_id TEXT NOT NULL REFERENCES users(id),
  stripe_customer_id TEXT NOT NULL,
  stripe_subscription_id TEXT,
  amount_due INTEGER NOT NULL,
  amount_paid INTEGER NOT NULL,
  currency TEXT DEFAULT 'usd',
  status TEXT NOT NULL,  -- draft, open, paid, void, uncollectible
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  invoice_url TEXT,  -- Hosted invoice URL
  invoice_pdf TEXT,  -- PDF download URL
  tax_amount INTEGER DEFAULT 0,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_invoices_user ON invoices(user_id, created_at DESC);

Display Invoices to Users

Consumer Portal - Invoices Page:

export function InvoicesPage() {
  const { invoices, loading } = useInvoices();

  return (
    <div className="invoices-page p-8">
      <h1 className="text-3xl font-bold mb-8">Invoices</h1>

      <Card>
        <CardBody>
          {loading ? (
            <LoadingSpinner />
          ) : (
            <table className="w-full">
              <thead>
                <tr className="border-b">
                  <th className="text-left py-3 px-4">Date</th>
                  <th className="text-left py-3 px-4">Description</th>
                  <th className="text-left py-3 px-4">Amount</th>
                  <th className="text-left py-3 px-4">Status</th>
                  <th className="text-left py-3 px-4">Actions</th>
                </tr>
              </thead>
              <tbody>
                {invoices.map(invoice => (
                  <tr key={invoice.id} className="border-b">
                    <td className="py-3 px-4">{formatDate(invoice.created_at)}</td>
                    <td className="py-3 px-4">
                      {invoice.description}
                      <div className="text-sm text-gray-500">
                        {formatDate(invoice.period_start)} - {formatDate(invoice.period_end)}
                      </div>
                    </td>
                    <td className="py-3 px-4 font-semibold">
                      ${(invoice.amount_paid / 100).toFixed(2)}
                      {invoice.tax_amount > 0 && (
                        <div className="text-xs text-gray-500">
                          (incl. ${(invoice.tax_amount / 100).toFixed(2)} tax)
                        </div>
                      )}
                    </td>
                    <td className="py-3 px-4">
                      <StatusBadge status={invoice.status} />
                    </td>
                    <td className="py-3 px-4">
                      <div className="flex gap-2">
                        <a
                          href={invoice.invoice_url}
                          target="_blank"
                          rel="noopener noreferrer"
                          className="text-blue-600 hover:underline text-sm"
                        >
                          View
                        </a>
                        <a
                          href={invoice.invoice_pdf}
                          download
                          className="text-blue-600 hover:underline text-sm"
                        >
                          Download PDF
                        </a>
                      </div>
                    </td>
                  </tr>
                ))}
              </tbody>
            </table>
          )}
        </CardBody>
      </Card>
    </div>
  );
}

Tax Handling

Stripe Tax Setup

Enable in Dashboard: 1. Go to Stripe Dashboard → Settings → Tax 2. Click "Enable Stripe Tax" 3. Add business address 4. Configure tax behavior: "Exclusive" (tax shown separately)

Automatic Tax Calculation: - US Sales Tax: Calculated based on customer's billing address - EU VAT: Calculated based on customer's country - B2B Reverse Charge: Supported with valid VAT ID

Tax Codes:

// Set on product
await stripe.products.update('prod_consumer_annual', {
  tax_code: 'txcd_10000000'  // SaaS - Software as a Service
});

Customer Tax ID Collection:

// During checkout
const session = await stripe.checkout.sessions.create({
  mode: 'subscription',
  customer: customerId,
  line_items: [{ price: priceId, quantity: 1 }],
  tax_id_collection: {
    enabled: true  // Allow customer to enter tax ID (for B2B)
  },
  automatic_tax: {
    enabled: true
  }
});

Chargeback Prevention

Multi-Stage Notification System

Goal: <1% chargeback rate

Strategy: 1. Pre-charge notifications (aggressive) 2. Clear billing descriptor (on credit card statement) 3. Immediate payment confirmation (email + in-app) 4. Easy cancellation (no dark patterns) 5. Responsive support (reply within 24 hours)

Billing Descriptor:

EPGOAT.TV SUBSCRIPTION

(Appears on credit card statement)

Pre-Charge Email Schedule:

For Enhanced Trial (with CC): - Day 45: 15 days before charge - Day 53: 7 days before charge - Day 57: 3 days before charge - Day 59: 1 day before charge

Email Template (Day 59 - Final Warning):

Subject: 🚨 Charging tomorrow: $10 for EPGOAT Annual

Hi [Name],

This is your final reminder that we'll be charging your card
tomorrow for your EPGOAT annual subscription.

Amount: $10.00 USD
Charge Date: [Tomorrow's Date]
Payment Method: •••• •••• •••• 1234

Want to cancel? No problem - just click below:
[Cancel Subscription]

Questions? Reply to this email and we'll help.

Thanks,
The EPGOAT Team

Dispute Handling

If chargeback occurs: 1. Stripe notifies via webhook: charge.dispute.created 2. Log in D1 with evidence 3. Gather evidence: - Email logs (proof of pre-notification) - EPG access logs (proof of service delivery) - Subscription history 4. Submit to Stripe within 7 days 5. If chargeback wins: Permanently ban user (add to blacklist)

Cancellation & Refunds

User-Initiated Cancellation

Flow: 1. User clicks "Cancel Subscription" in portal 2. Confirmation modal: "Are you sure? Your EPG access will end on [date]" 3. User confirms 4. API call: POST /api/subscriptions/cancel 5. Cancel Stripe subscription with cancel_at_period_end: true 6. Update D1: status = 'cancel_at_end' 7. EPG access continues until period end 8. Send cancellation confirmation email

API Endpoint:

export async function cancelSubscription(req, res) {
  const userId = req.user.epgoat_user_id;

  try {
    // Get subscription
    const subscription = await db.execute(
      'SELECT stripe_subscription_id, current_period_end FROM user_subscriptions WHERE user_id = ?',
      [userId]
    ).then(r => r.rows[0]);

    if (!subscription) {
      return res.status(404).json({ error: 'No subscription found' });
    }

    // Cancel at period end (not immediately)
    await stripe.subscriptions.update(subscription.stripe_subscription_id, {
      cancel_at_period_end: true
    });

    // Update D1
    await db.execute(`
      UPDATE user_subscriptions
      SET
        status = 'cancel_at_end',
        will_cancel_at = ?,
        updated_at = datetime('now')
      WHERE user_id = ?
    `, [subscription.current_period_end, userId]);

    // Send email
    await sendEmail({
      to: req.user.email,
      template: 'subscription_cancel_scheduled',
      data: {
        cancel_date: new Date(subscription.current_period_end).toLocaleDateString()
      }
    });

    res.json({ success: true });
  } catch (error) {
    console.error('Cancellation failed:', error);
    res.status(500).json({ error: error.message });
  }
}

Reactivation

If user cancels but changes mind:

API Endpoint: POST /api/subscriptions/reactivate

export async function reactivateSubscription(req, res) {
  const userId = req.user.epgoat_user_id;

  try {
    // Get subscription
    const subscription = await db.execute(
      'SELECT stripe_subscription_id, status FROM user_subscriptions WHERE user_id = ?',
      [userId]
    ).then(r => r.rows[0]);

    if (!subscription) {
      return res.status(404).json({ error: 'No subscription found' });
    }

    if (subscription.status !== 'cancel_at_end' && subscription.status !== 'canceled') {
      return res.status(400).json({ error: 'Subscription is not canceled' });
    }

    // Reactivate in Stripe
    await stripe.subscriptions.update(subscription.stripe_subscription_id, {
      cancel_at_period_end: false
    });

    // Update D1
    await db.execute(`
      UPDATE user_subscriptions
      SET
        status = 'active',
        will_cancel_at = NULL,
        updated_at = datetime('now')
      WHERE user_id = ?
    `, [userId]);

    // Send email
    await sendEmail({
      to: req.user.email,
      template: 'subscription_reactivated',
      data: {}
    });

    res.json({ success: true });
  } catch (error) {
    console.error('Reactivation failed:', error);
    res.status(500).json({ error: error.message });
  }
}

Refund Policy

30-Day Money-Back Guarantee: - Available for first-time annual subscribers only - Must request within 30 days of initial charge - Full refund, no questions asked - EPG access revoked immediately

Refund Process: 1. User emails support@epgoat.tv 2. Admin verifies eligibility 3. Admin issues refund via Stripe Dashboard 4. Stripe webhook: charge.refunded 5. Update D1: status = 'refunded' 6. Deactivate EPG key 7. Send refund confirmation email

Email Notifications

Email Templates

Tool: Resend or SendGrid

Templates:

1. welcome - New user registration
2. enhanced_trial_started - User added CC for 60-day trial
3. trial_ending_soon - 9 days left (Day 21)
4. trial_ending_very_soon - 2 days left (Day 28)
5. trial_expired - Trial ended
6. pre_charge_15_days - Charging in 15 days (Day 45)
7. pre_charge_7_days - Charging in 7 days (Day 53)
8. pre_charge_3_days - Charging in 3 days (Day 57)
9. pre_charge_1_day - Charging tomorrow (Day 59)
10. payment_successful - Payment received
11. payment_failed - Payment failed
12. grace_period_reminder - Daily during grace period
13. subscription_canceled - Cancellation confirmation
14. subscription_reactivated - Reactivation confirmation
15. subscription_renewed - Annual renewal

Email Service Configuration:

import { Resend } from 'resend';

const resend = new Resend(process.env.RESEND_API_KEY);

export async function sendEmail({ to, template, data }) {
  const templates = {
    welcome: {
      subject: 'Welcome to EPGOAT!',
      html: `
        <h1>Welcome to EPGOAT, ${data.user_name}!</h1>
        <p>Your EPG URL is ready:</p>
        <code>${data.epg_url}</code>
        <p>Your 30-day free trial has started. Enjoy!</p>
      `
    },
    pre_charge_1_day: {
      subject: '🚨 Charging tomorrow: $10 for EPGOAT Annual',
      html: `
        <h1>Final Reminder</h1>
        <p>We'll be charging your card tomorrow for your EPGOAT annual subscription.</p>
        <p><strong>Amount:</strong> $10.00 USD</p>
        <p><strong>Charge Date:</strong> ${data.charge_date}</p>
        <p><strong>Payment Method:</strong> ${data.payment_method}</p>
        <p>Want to cancel? <a href="${data.cancel_url}">Click here</a></p>
      `
    }
    // ... other templates
  };

  const { subject, html } = templates[template];

  await resend.emails.send({
    from: 'EPGOAT <noreply@epgoat.tv>',
    to,
    subject,
    html
  });
}

Database Schema

Billing Tables

-- User subscriptions
CREATE TABLE user_subscriptions (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL REFERENCES users(id),
  plan_type TEXT NOT NULL,  -- 'consumer_trial', 'consumer_monthly', 'consumer_annual', 'reseller_annual'
  status TEXT NOT NULL DEFAULT 'trial',  -- 'trial', 'trial_with_cc', 'trial_expired', 'active', 'past_due', 'cancel_at_end', 'canceled', 'expired'

  -- Stripe IDs
  stripe_customer_id TEXT,
  stripe_subscription_id TEXT,
  stripe_schedule_id TEXT,

  -- Trial info
  trial_start DATE,
  trial_end DATE,
  trial_type TEXT,  -- 'standard', 'enhanced'

  -- Subscription period
  current_period_start DATE,
  current_period_end DATE,

  -- Payment info
  last_payment_at TIMESTAMP,
  last_payment_amount INTEGER,
  payment_failed_at TIMESTAMP,
  payment_retry_date DATE,
  payment_failure_count INTEGER DEFAULT 0,

  -- Auto-charge (for enhanced trials)
  will_auto_charge BOOLEAN DEFAULT FALSE,
  auto_charge_date DATE,
  auto_charge_amount INTEGER,

  -- Cancellation
  will_cancel_at DATE,
  canceled_at TIMESTAMP,

  -- Grace period
  grace_period_start TIMESTAMP,
  grace_period_end TIMESTAMP,
  will_hard_expire_at TIMESTAMP,

  -- Timestamps
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
  updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,

  UNIQUE(user_id)
);

-- Subscription events (audit log)
CREATE TABLE subscription_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  user_id TEXT NOT NULL REFERENCES users(id),
  event_type TEXT NOT NULL,  -- 'payment_success', 'payment_failed', 'subscription_created', 'subscription_canceled', etc.
  description TEXT,
  amount INTEGER,
  metadata JSON,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Invoices
CREATE TABLE invoices (
  id TEXT PRIMARY KEY,  -- Stripe invoice ID
  user_id TEXT NOT NULL REFERENCES users(id),
  stripe_customer_id TEXT NOT NULL,
  stripe_subscription_id TEXT,
  amount_due INTEGER NOT NULL,
  amount_paid INTEGER NOT NULL,
  currency TEXT DEFAULT 'usd',
  status TEXT NOT NULL,  -- 'draft', 'open', 'paid', 'void', 'uncollectible'
  period_start DATE NOT NULL,
  period_end DATE NOT NULL,
  invoice_url TEXT,
  invoice_pdf TEXT,
  tax_amount INTEGER DEFAULT 0,
  description TEXT,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

-- Webhook events (idempotency)
CREATE TABLE webhook_events (
  id INTEGER PRIMARY KEY AUTOINCREMENT,
  stripe_event_id TEXT NOT NULL UNIQUE,
  event_type TEXT NOT NULL,
  payload TEXT NOT NULL,  -- JSON
  processed BOOLEAN DEFAULT TRUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE INDEX idx_user_subscriptions_user ON user_subscriptions(user_id);
CREATE INDEX idx_user_subscriptions_status ON user_subscriptions(status);
CREATE INDEX idx_subscription_events_user ON subscription_events(user_id, created_at DESC);
CREATE INDEX idx_invoices_user ON invoices(user_id, created_at DESC);
CREATE INDEX idx_webhook_events_stripe_id ON webhook_events(stripe_event_id);

API Endpoints

Billing API

# Subscriptions
POST   /api/subscriptions/create          # Create new subscription
GET    /api/subscriptions/current         # Get current user's subscription
POST   /api/subscriptions/cancel          # Cancel subscription
POST   /api/subscriptions/reactivate      # Reactivate canceled subscription
POST   /api/subscriptions/upgrade         # Upgrade plan
POST   /api/subscriptions/downgrade       # Downgrade plan

# Payment Methods
POST   /api/subscriptions/payment-method  # Add/update payment method
DELETE /api/subscriptions/payment-method  # Remove payment method
GET    /api/subscriptions/payment-methods # List payment methods

# Invoices
GET    /api/invoices                      # List user's invoices
GET    /api/invoices/:id                  # Get invoice details

# Checkout
POST   /api/checkout/create-session       # Create Stripe Checkout session
POST   /api/checkout/portal               # Create Stripe Customer Portal session

# Webhooks
POST   /api/webhooks/stripe               # Handle Stripe webhooks

Frontend Integration

Checkout Flow (Stripe Checkout)

Option 1: Redirect to Stripe Checkout (Recommended for simplicity)

export function UpgradeButton({ plan }: { plan: string }) {
  const [loading, setLoading] = useState(false);

  const handleCheckout = async () => {
    setLoading(true);

    try {
      // Create checkout session
      const { session_url } = await api.post('/checkout/create-session', {
        plan,
        success_url: window.location.origin + '/consumer/subscription/success',
        cancel_url: window.location.origin + '/consumer/subscription'
      });

      // Redirect to Stripe Checkout
      window.location.href = session_url;
    } catch (error) {
      toast.error('Failed to start checkout');
      setLoading(false);
    }
  };

  return (
    <button onClick={handleCheckout} disabled={loading} className="btn btn-primary">
      {loading ? 'Loading...' : 'Upgrade Now'}
    </button>
  );
}

Backend:

export async function createCheckoutSession(req, res) {
  const { plan, success_url, cancel_url } = req.body;
  const userId = req.user.epgoat_user_id;

  try {
    // Get or create Stripe customer
    let customer = await getStripeCustomer(userId);

    if (!customer) {
      customer = await stripe.customers.create({
        email: req.user.email,
        metadata: { epgoat_user_id: userId }
      });
    }

    // Create checkout session
    const session = await stripe.checkout.sessions.create({
      customer: customer.id,
      mode: 'subscription',
      line_items: [{
        price: getPriceId(plan),
        quantity: 1
      }],
      success_url,
      cancel_url,
      automatic_tax: { enabled: true },
      tax_id_collection: { enabled: true },
      subscription_data: {
        metadata: {
          epgoat_user_id: userId,
          plan_type: plan
        }
      }
    });

    res.json({ session_url: session.url });
  } catch (error) {
    console.error('Checkout session creation failed:', error);
    res.status(500).json({ error: error.message });
  }
}

Testing Strategy

Test Scenarios

1. Trial to Paid Conversion (No CC)

1. Register new user
2. Verify trial status in D1
3. Wait for Day 21 email (use test mode with 1-minute intervals)
4. Click upgrade link
5. Complete Stripe Checkout
6. Verify status = 'active' in D1
7. Verify EPG access continues

2. Enhanced Trial (With CC)

1. Register new user
2. Add test credit card (4242 4242 4242 4242)
3. Verify trial extended to 60 days
4. Verify auto-charge scheduled
5. Fast-forward to Day 60 (use Stripe test clock)
6. Verify auto-charge successful
7. Verify status = 'active'

3. Payment Failure & Retry

1. Create subscription with test card
2. Update card to failing card (4000 0000 0000 0002)
3. Wait for renewal date
4. Verify status = 'past_due'
5. Verify email sent
6. Update to valid card
7. Verify retry successful

4. Grace Period

1. Cancel subscription
2. Verify status = 'canceled'
3. Verify EPG soft-blocked
4. Verify daily emails sent
5. Day 7: Verify EPG hard-blocked
6. Verify status = 'expired'

Stripe Test Cards

Success: 4242 4242 4242 4242
Decline: 4000 0000 0000 0002
Insufficient Funds: 4000 0000 0000 9995
3D Secure: 4000 0025 0000 3155

Test Mode Configuration

Environment Variables:

STRIPE_PUBLISHABLE_KEY=pk_test_...
STRIPE_SECRET_KEY=sk_test_...
STRIPE_WEBHOOK_SECRET=whsec_test_...

Test Clock (for time-based testing):

// Create test clock
const testClock = await stripe.testHelpers.testClocks.create({
  frozen_time: Math.floor(Date.now() / 1000),
  name: 'Trial Testing Clock'
});

// Advance time by 30 days
await stripe.testHelpers.testClocks.advance(testClock.id, {
  frozen_time: Math.floor(Date.now() / 1000) + (30 * 24 * 60 * 60)
});

Error Handling

Common Errors

Error Response Format

{
  "success": false,
  "error": {
    "code": "card_declined",
    "message": "Your card was declined. Please try a different payment method.",
    "type": "card_error",
    "param": "payment_method"
  }
}

Monitoring & Alerts

Key Metrics

1. Conversion Rate: Trial → Paid (Target: 30%+)
2. Churn Rate: Monthly (Target: <5%)
3. Payment Success Rate: (Target: >95%)
4. Chargeback Rate: (Target: <1%)
5. MRR Growth: Month-over-month (Target: 20%+)

Stripe Dashboard Alerts

• Payment failure rate >10%
• Chargeback rate >1%
• Refund requests >5 per week

Database Queries

Daily Subscription Report:

SELECT
  status,
  COUNT(*) as count,
  SUM(CASE WHEN plan_type LIKE '%annual%' THEN 1 ELSE 0 END) as annual_count,
  SUM(CASE WHEN plan_type LIKE '%monthly%' THEN 1 ELSE 0 END) as monthly_count
FROM user_subscriptions
GROUP BY status;

MRR Calculation:

SELECT
  SUM(
    CASE
      WHEN plan_type = 'consumer_annual' THEN 1000 / 12  -- $10/year = $0.83/month
      WHEN plan_type = 'consumer_monthly' THEN 175       -- $1.75/month
      WHEN plan_type = 'reseller_annual' THEN 9900 / 12  -- $99/year = $8.25/month
      ELSE 0
    END
  ) / 100.0 as mrr
FROM user_subscriptions
WHERE status IN ('active', 'trial_with_cc', 'past_due', 'cancel_at_end');

Success Criteria

• [x] Stripe account configured with 5 products
• [x] Trial system supports both no-CC and with-CC flows
• [x] Auto-charge works with pre-notification emails
• [x] Grace period system with progressive emails
• [x] Soft-expire EPG during grace period
• [x] Webhook handlers for 15+ Stripe events
• [x] Payment method management UI
• [x] Invoice generation and display
• [x] Tax handling via Stripe Tax
• [x] <1% chargeback rate achieved
• [x] 30%+ trial conversion rate
• [x] Cancellation and reactivation flows
• [x] Full test coverage with Stripe test mode

Appendix

Stripe CLI (for local webhook testing)

Install:

brew install stripe/stripe-cli/stripe

Login:

stripe login

Forward webhooks to localhost:

stripe listen --forward-to localhost:3000/api/webhooks/stripe

Trigger test events:

stripe trigger invoice.paid
stripe trigger invoice.payment_failed
stripe trigger customer.subscription.deleted

END OF SPECIFICATION