Subscription Management

Manage user subscriptions, premium tier access, and billing-related administrative tasks.

Overview

BookWish uses a tiered subscription model:

Free - Basic registered users (no subscription)
Premium - Paid subscribers with enhanced features
Bookstore - Store owners (separate from premium)

Premium subscriptions are managed through Stripe, with webhook integration to update user tiers automatically.

Subscription System

How Subscriptions Work

Subscription Flow:

User purchases subscription via Stripe
Stripe webhook notifies BookWish backend
Backend updates user tier to premium
User gains access to premium features
Subscription renews automatically
Cancellation downgrades to free

Stripe Integration

Key Stripe Entities:

Customer - Stripe customer record linked to BookWish user
Subscription - Active subscription record
Payment Method - Saved payment method
Invoice - Billing records

BookWish User Fields:

{
  tier: 'premium',           // Set when subscription active
  stripeCustomerId?: string, // Links to Stripe customer (if stored)
}

Viewing Subscriptions

Premium Users

All Premium Users:

SELECT id, username, email, display_name, created_at
FROM users
WHERE tier = 'premium'
ORDER BY created_at DESC;

Recent Premium Subscriptions:

SELECT id, username, email, updated_at
FROM users
WHERE tier = 'premium'
  AND updated_at >= NOW() - INTERVAL '7 days'
ORDER BY updated_at DESC;

Subscription Status

Check User Subscription:

To verify a user's subscription status, you may need to:

Query Stripe API with user's email/customer ID
Check Stripe dashboard directly
Review webhook logs for that user

Note: BookWish currently stores subscription status in the user's tier field, but detailed subscription information (billing cycle, next renewal, payment method) is maintained in Stripe.

Managing Subscriptions

Grant Premium Access

Manually Grant Premium:

UPDATE users
SET tier = 'premium'
WHERE id = 'user_id';

When to Manually Grant:

Promotional subscription (influencer, contest winner)
Compensation for platform issues
Migration from legacy system
Testing/development purposes
Special partnerships

Important: Manual grants bypass Stripe, so there's no automatic renewal or billing. Document why premium was granted manually.

Revoke Premium Access

Downgrade to Free:

UPDATE users
SET tier = 'free'
WHERE id = 'user_id';

When to Manually Revoke:

Subscription expired but webhook failed
Policy violation (abuse of premium features)
Refund issued
User request (with cancellation in Stripe)
Testing/development

Important: If user has active Stripe subscription, they may be re-upgraded on next webhook. Cancel in Stripe first.

Handle Failed Payments

Failed Payment Process:

Stripe attempts retry automatically
If retry fails, sends webhook
Backend should downgrade user to free
User receives email notification

Manual Intervention: If webhook doesn't fire or fails:

-- Downgrade user after failed payment
UPDATE users
SET tier = 'free'
WHERE id = 'user_id';

Resolution:

User updates payment method in Stripe
Stripe retries billing
Successful payment triggers webhook
User automatically upgraded back to premium

Process Refunds

Refund Process:

Issue refund in Stripe dashboard
Stripe sends webhook
Backend downgrades user to free
User notified

Manual Refund: If handling outside Stripe:

-- Downgrade user after refund
UPDATE users
SET tier = 'free'
WHERE id = 'user_id';

Document:

Reason for refund
Amount refunded
Date processed
Communication with user

Subscription Analytics

Subscription Metrics

Total Premium Users:

SELECT COUNT(*) as premium_count
FROM users
WHERE tier = 'premium';

Premium Conversion Rate:

SELECT
  COUNT(CASE WHEN tier = 'premium' THEN 1 END) as premium_users,
  COUNT(CASE WHEN tier IN ('free', 'premium') THEN 1 END) as eligible_users,
  ROUND(100.0 * COUNT(CASE WHEN tier = 'premium' THEN 1 END) /
    NULLIF(COUNT(CASE WHEN tier IN ('free', 'premium') THEN 1 END), 0), 2) as conversion_rate
FROM users
WHERE is_guest = false;

New Premium Subscriptions (Last 30 Days):

-- This approximation uses updated_at, which may not be perfect
-- Better to track subscription events separately
SELECT DATE(updated_at) as date,
       COUNT(*) as new_premium
FROM users
WHERE tier = 'premium'
  AND updated_at >= NOW() - INTERVAL '30 days'
GROUP BY DATE(updated_at)
ORDER BY date;

Revenue Metrics

Note: Revenue metrics should be pulled from Stripe, not BookWish database.

Key Stripe Metrics:

Monthly Recurring Revenue (MRR)
Annual Recurring Revenue (ARR)
Churn Rate
Average Revenue Per User (ARPU)
Lifetime Value (LTV)

Access via:

Stripe Dashboard → Reports
Stripe API
Third-party analytics tools

Subscription Cohorts

User Cohorts by Tier:

SELECT
  DATE_TRUNC('month', created_at) as cohort,
  tier,
  COUNT(*) as user_count
FROM users
WHERE is_guest = false
GROUP BY cohort, tier
ORDER BY cohort DESC, tier;

Subscription Issues

Webhook Failures

Symptoms:

User paid but still shows as free tier
User cancelled but still shows as premium
Tier doesn't match Stripe status

Investigation:

Check webhook logs in backend
Verify webhook endpoint is accessible
Check Stripe webhook dashboard for errors
Review webhook signature verification

Resolution:

Identify failed webhook event in Stripe
Manually apply user tier change
Fix webhook issue
Resend webhook (Stripe dashboard)
Monitor for recurrence

Duplicate Subscriptions

Symptoms:

User has multiple Stripe subscriptions
User charged twice
Confusion about subscription status

Investigation:

Check Stripe for all user subscriptions
Identify which is intended
Review creation timeline

Resolution:

Cancel duplicate subscription(s)
Issue refund if user was overcharged
Confirm correct subscription active
Update user tier if needed
Communicate with user

Tier Mismatch

Symptoms:

User's tier doesn't match Stripe status
Premium features not working/showing

Investigation:

-- Check user's current tier
SELECT id, username, email, tier, updated_at
FROM users
WHERE id = 'user_id';

Then verify in Stripe dashboard.

Resolution:

If Stripe shows active subscription but user is free:

UPDATE users SET tier = 'premium' WHERE id = 'user_id';

If Stripe shows cancelled but user is premium:

UPDATE users SET tier = 'free' WHERE id = 'user_id';

Document the discrepancy for investigation.

Premium Features

Feature Access Control

Premium users get access to:

Unlimited wishlists (free users limited)
Priority support
Advanced features (varies by implementation)
Ad-free experience (if applicable)

Feature Gating: Features are typically gated in the backend by checking req.user.tier:

if (req.user.tier !== 'premium' && req.user.tier !== 'admin') {
  return res.status(403).json({
    error: 'Premium subscription required',
    minTier: 'premium'
  });
}

Tier-Based Limits: See tier limit configuration in backend:

const TIER_LIMITS = {
  guest: { maxWishlists: 0 },
  free: { maxWishlists: 1 },
  premium: { maxWishlists: -1 },  // unlimited
  bookstore: { maxWishlists: -1 },
  admin: { maxWishlists: -1 }
};

Promotional Subscriptions

Comp Subscriptions

Grant Free Premium:

For promotional purposes (partnerships, influencers, press):

UPDATE users
SET tier = 'premium'
WHERE id = 'influencer_user_id';

Track Comp Subscriptions:

Maintain separate list/spreadsheet
Document reason and duration
Set calendar reminder for review
Consider creating separate comp tier (future)

Expiration: Comp subscriptions don't auto-expire, so:

Set reminder to review
Communicate duration upfront
Manually downgrade when expired

Stripe Promo Codes:

Created in Stripe dashboard
Applied during checkout
Automatically tracked by Stripe
No admin action needed (webhook handles)

Managing Promo Codes:

Create in Stripe (not BookWish)
Set discount amount/percentage
Set duration (once, forever, repeating)
Set redemption limits
Track usage in Stripe

Subscription Support

User Requests

Common Requests:

"I can't access premium features"

Verify user tier in database
Check Stripe subscription status
Update tier if mismatch
Test feature access
Confirm resolution with user

"I was charged but didn't subscribe"

Check Stripe for transaction
Verify user identity
Review subscription creation
Investigate if fraud
Issue refund if appropriate

"I cancelled but still being charged"

Check Stripe subscription status
Verify cancellation processed
Cancel if still active
Refund if charged after cancellation
Confirm cancellation with user

"I want to upgrade/downgrade"

Direct user to subscription settings
Verify change in Stripe
Confirm tier updated
Test feature access

Billing Disputes

Dispute Process:

User contacts about charge
Verify transaction in Stripe
Review subscription history
Check for fraud indicators
Decide: refund, explain, or escalate

Refund Decision Factors:

Was subscription used?
How long ago was purchase?
Prior refund history
Reason for dispute
Platform policy

Issue Refund:

Process in Stripe dashboard
Cancel subscription if requested
Document reason
Update user tier
Communicate outcome

Data Privacy

User Data Requests

GDPR/CCPA Requests:

For data export or deletion requests:

Export Subscription Data:

SELECT id, email, tier, created_at, updated_at
FROM users
WHERE id = 'user_id';

Plus Stripe data from Stripe API/dashboard.

Delete Subscription Data:

Cancel active subscriptions in Stripe
Delete Stripe customer (if requested)
Delete/anonymize BookWish user record
Document request and completion

Data Security

Protect Subscription Data:

Don't share payment details
Encrypt sensitive fields
Limit admin access to billing
Log all billing-related actions
Follow PCI compliance (Stripe handles)

Best Practices