xtablo-source/docs/STRIPE_QUICK_REFERENCE.md

# Stripe Integration - Quick Reference

## 🚀 Quick Start (For Your Node.js API)

### 1. Install Dependencies
```bash
cd apps/api && npm install stripe @stripe/stripe-js
cd apps/main && npm install @stripe/stripe-js
```

### 2. Environment Variables

**API (`.env`):**
```env
STRIPE_SECRET_KEY=sk_test_xxxxx
STRIPE_WEBHOOK_SECRET=whsec_xxxxx
SUPABASE_SERVICE_ROLE_KEY=your_service_key
FRONTEND_URL=http://localhost:5173
```

**Frontend (`apps/main/.env`):**
```env
VITE_STRIPE_PUBLISHABLE_KEY=pk_test_xxxxx
```

### 3. Stripe Dashboard Setup

1. Create product named **"Standard"**
2. Add pricing (save price IDs)
3. Add webhook: `https://your-api.com/api/v1/stripe/webhook`
4. Copy webhook secret

### 4. Run Migrations
```sql
-- Execute in Supabase SQL Editor
\i sql/35_stripe_wrappers.sql
\i sql/36_stripe_webhooks.sql
```

## 📋 API Endpoints (Actions Only)

| Method | Endpoint | Auth | Description |
|--------|----------|------|-------------|
| POST | `/api/v1/stripe/webhook` | ❌ | Stripe webhook (signature verified) |
| POST | `/api/v1/stripe/create-checkout-session` | ✅ | Start subscription flow |
| POST | `/api/v1/stripe/create-portal-session` | ✅ | Open customer portal |
| POST | `/api/v1/stripe/cancel-subscription` | ✅ | Cancel subscription |
| POST | `/api/v1/stripe/reactivate-subscription` | ✅ | Reactivate subscription |

**Note:** Reading subscription data (status, prices, etc.) is done directly via Supabase client from the frontend.

## 🎣 Frontend Hooks

```typescript
import {
  // Direct Supabase queries (RLS-protected, no API call)
  useSubscription,           // Get full subscription from Supabase
  useIsPayingUser,          // Get is_paying from user profile
  useStripePrices,          // Get prices from Supabase
  
  // API calls (for Stripe actions)
  useCreateCheckoutSession, // Create checkout & redirect
  useCreatePortalSession,   // Open customer portal
  useCancelSubscription,    // Cancel at period end
  useReactivateSubscription // Undo cancellation
} from '@/hooks/stripe';
```

**Benefits of Direct Supabase Access:**
- ⚡ **Faster**: No API hop for reads
- 🔒 **Secure**: RLS policies protect data
- 📊 **Real-time**: Can subscribe to changes

## 💡 Common Use Cases

### Check if User is Paying
```typescript
const user = useUser();
if (user.is_paying) {
  // Show premium feature
}
```

### Subscribe to Standard Plan
```typescript
const { mutate: checkout } = useCreateCheckoutSession();

<button onClick={() => checkout({ priceId: 'price_xxxxx' })}>
  Subscribe to Standard
</button>
```

### Manage Subscription
```typescript
const { mutate: openPortal } = useCreatePortalSession();

<button onClick={() => openPortal()}>
  Manage Subscription
</button>
```

### Show Subscription Status
```typescript
const { data: subscription } = useSubscription();

{subscription?.status === 'active' && (
  <div>
    Active until {subscription.current_period_end}
    {subscription.cancel_at_period_end && (
      <span>Will cancel at period end</span>
    )}
  </div>
)}
```

## 🔍 Database Queries

### Frontend Queries (Using Supabase Client)
```typescript
// Get user's subscription
const { data } = await supabase
  .from('stripe_subscriptions')
  .select('*, price:stripe_prices(*, product:stripe_products(*))')
  .eq('user_id', userId)
  .single();

// Get available prices
const { data } = await supabase
  .from('stripe_prices')
  .select('*, product:stripe_products!inner(*)')
  .eq('active', true)
  .eq('product.name', 'Standard');
```

### Backend SQL Queries
```sql
-- Is user paying?
SELECT is_paying_user('user-uuid-here');

-- Get subscription details
SELECT * FROM get_user_subscription_status('user-uuid-here');

-- Get current user's active subscription (secure, RLS-compliant)
SELECT * FROM get_my_active_subscription();
```

## 🎨 Profile Fields

After subscription sync, profiles have:
```typescript
{
  is_paying: boolean,        // true if active/trialing subscription
  subscription_tier: string, // 'free' | 'standard'
}
```

## 🧪 Test Cards

| Card | Result |
|------|--------|
| `4242 4242 4242 4242` | ✅ Success |
| `4000 0000 0000 0002` | ❌ Declined |
| `4000 0000 0000 9995` | ❌ Insufficient funds |

## 🔔 Webhook Events Handled

- ✅ `customer.created`
- ✅ `customer.updated`
- ✅ `customer.deleted`
- ✅ `customer.subscription.created`
- ✅ `customer.subscription.updated`
- ✅ `customer.subscription.deleted`
- ✅ `product.created` / `updated` / `deleted`
- ✅ `price.created` / `updated` / `deleted`

## 🎯 Standard Plan Details

**Single Tier Model:**
- **Free**: Default (no subscription)
- **Standard**: Paid subscription

**Subscription automatically:**
- Sets `is_paying = true`
- Sets `subscription_tier = 'standard'`
- Updates on webhook events
- Reverts to free on cancellation

## 🔐 Security

- ✅ Webhook signature verification
- ✅ Row Level Security on all tables
- ✅ Users can only see their own subscriptions
- ✅ Service role for webhook functions
- ✅ Stripe keys in environment (never in code)

## 📞 Support

For issues, check:
1. API logs for webhook errors
2. Stripe Dashboard → Webhooks → Event logs
3. Supabase logs for database errors
4. Browser console for frontend errors

---

**Files:**
- Database: `sql/35_stripe_wrappers.sql` + `sql/36_fix_stripe_subscription_dates.sql` + `sql/37_secure_active_subscriptions.sql`
- Backend: `api/src/stripe.ts` + `api/src/stripeSync.ts`
- Frontend: `apps/main/src/hooks/stripe.ts`
- Types: `packages/shared/src/types/stripe.types.ts`
- Security: `docs/STRIPE_SECURITY_FIX.md`