157 lines
4.7 KiB
Markdown
157 lines
4.7 KiB
Markdown
# 🧹 Cleanup Old Stripe Functions
|
|
|
|
## Overview
|
|
|
|
Since we're now using **@supabase/stripe-sync-engine**, we need to remove the custom webhook handler functions that were created by the old `sql/36_stripe_webhooks.sql` file.
|
|
|
|
## ✅ What's Been Done
|
|
|
|
### Files Deleted
|
|
- ✅ `api/src/stripe-webhook.ts` - Custom webhook handler (replaced by library)
|
|
- ✅ `sql/36_stripe_webhooks.sql` - Custom webhook SQL functions (replaced by library)
|
|
|
|
### Files Updated
|
|
- ✅ `api/src/stripe.ts` - Now uses `StripeSync` library
|
|
- ✅ `apps/main/src/hooks/stripe.ts` - Uses `stripe` schema
|
|
- ✅ `sql/35_stripe_wrappers.sql` - Uses `stripe` schema, updated function references
|
|
|
|
## 🗑️ Cleanup Database Functions
|
|
|
|
Run this SQL to remove the old custom functions:
|
|
|
|
```sql
|
|
\i sql/cleanup_old_stripe_functions.sql
|
|
```
|
|
|
|
Or run manually:
|
|
|
|
```sql
|
|
-- Drop old custom webhook handler functions
|
|
drop function if exists public.handle_stripe_customer_created(text, text, uuid);
|
|
drop function if exists public.handle_stripe_customer_updated(text, text);
|
|
drop function if exists public.handle_stripe_customer_deleted(text);
|
|
drop function if exists public.handle_stripe_product_upsert(text, text, text, boolean, text, jsonb);
|
|
drop function if exists public.handle_stripe_product_deleted(text);
|
|
drop function if exists public.handle_stripe_price_upsert(text, text, boolean, text, bigint, text, integer, integer, jsonb);
|
|
drop function if exists public.handle_stripe_price_deleted(text);
|
|
drop function if exists public.handle_stripe_subscription_upsert(text, text, text, text, integer, boolean, timestamp with time zone, timestamp with time zone, timestamp with time zone, timestamp with time zone, timestamp with time zone);
|
|
drop function if exists public.handle_stripe_subscription_deleted(text);
|
|
```
|
|
|
|
## ✅ Verify Cleanup
|
|
|
|
Run this to check if old functions are gone:
|
|
|
|
```sql
|
|
SELECT routine_name
|
|
FROM information_schema.routines
|
|
WHERE routine_schema = 'public'
|
|
AND routine_name LIKE 'handle_stripe_%';
|
|
```
|
|
|
|
Should return **0 rows**.
|
|
|
|
## ✅ Verify Kept Functions
|
|
|
|
These functions are still needed and should exist:
|
|
|
|
```sql
|
|
SELECT
|
|
routine_schema,
|
|
routine_name
|
|
FROM information_schema.routines
|
|
WHERE routine_schema IN ('public', 'stripe')
|
|
AND routine_name IN (
|
|
'is_paying_user',
|
|
'get_user_subscription_status',
|
|
'get_user_stripe_customer_id',
|
|
'sync_subscription_user_id',
|
|
'update_profile_subscription_status',
|
|
'update_updated_at_column'
|
|
)
|
|
ORDER BY routine_name;
|
|
```
|
|
|
|
Should return **6 rows** (all the functions we still use).
|
|
|
|
## 📋 Migration Order
|
|
|
|
For a fresh setup:
|
|
|
|
1. **Run stripe-sync-engine migrations**
|
|
```typescript
|
|
import { runMigrations } from '@supabase/stripe-sync-engine';
|
|
await runMigrations({ databaseUrl: process.env.DATABASE_URL });
|
|
```
|
|
Creates `stripe` schema and all base tables
|
|
|
|
2. **Run custom SQL**
|
|
```sql
|
|
\i sql/35_stripe_wrappers.sql
|
|
```
|
|
Adds `user_id`, RLS, triggers, helper functions
|
|
|
|
3. **(Optional) Run cleanup if old functions exist**
|
|
```sql
|
|
\i sql/cleanup_old_stripe_functions.sql
|
|
```
|
|
Removes old custom webhook handlers
|
|
|
|
## 🎯 What Each Function Does
|
|
|
|
### Functions We Keep (In `public` schema):
|
|
|
|
| Function | Purpose | Used By |
|
|
|----------|---------|---------|
|
|
| `is_paying_user(uuid)` | Check if user has active subscription | Frontend queries |
|
|
| `get_user_subscription_status(uuid)` | Get subscription details | Optional |
|
|
| `get_user_stripe_customer_id(uuid)` | Get Stripe customer ID | API endpoints |
|
|
| `update_profile_subscription_status()` | Trigger: Update profile.is_paying | Automatic |
|
|
| `update_updated_at_column()` | Trigger: Update timestamps | Automatic |
|
|
|
|
### Functions We Keep (In `stripe` schema):
|
|
|
|
| Function | Purpose | Used By |
|
|
|----------|---------|---------|
|
|
| `sync_subscription_user_id()` | Trigger: Populate user_id in subscriptions | Automatic |
|
|
|
|
### Functions We Removed:
|
|
|
|
| Function | Why Removed |
|
|
|----------|-------------|
|
|
| `handle_stripe_customer_*` | ✅ Library handles it |
|
|
| `handle_stripe_product_*` | ✅ Library handles it |
|
|
| `handle_stripe_price_*` | ✅ Library handles it |
|
|
| `handle_stripe_subscription_*` | ✅ Library handles it |
|
|
|
|
## 🎉 Result
|
|
|
|
**Before:**
|
|
- Custom webhook handlers: 9 functions
|
|
- Custom webhook processing code: 267 lines (TypeScript)
|
|
- Custom SQL functions: 315 lines
|
|
|
|
**After:**
|
|
- Library handles all webhooks automatically
|
|
- Custom functions: 6 (only for profile integration)
|
|
- Total custom code: ~150 lines
|
|
|
|
**Reduction**: ~80% less code to maintain! 🎊
|
|
|
|
## 📝 Summary
|
|
|
|
✅ Old webhook handler functions removed
|
|
✅ stripe-sync-engine library integrated
|
|
✅ All code updated to use `stripe` schema
|
|
✅ RLS policies in place
|
|
✅ Helper functions preserved
|
|
✅ Triggers working
|
|
|
|
**Next**: Test with fake accounts (see `docs/TESTING_WITH_FAKE_ACCOUNTS.md`)
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|