# Stripe Security Fix - Migration 37

## Security Issue Fixed

**Issue**: The `public.active_subscriptions` view was defined as a regular view that exposed all users' subscription data. Without proper Row Level Security (RLS) policies, this view could potentially be queried by any authenticated user to see other users' subscription information.

**Severity**: High - Potential data exposure

## Changes Made

### Migration 37: `sql/37_secure_active_subscriptions.sql`

1. **Removed insecure view**
   - Dropped `public.active_subscriptions` view

2. **Added secure function**
   - `get_my_active_subscription()` - Returns only the authenticated user's active subscription
     - Uses `auth.uid()` to filter by current user
     - Uses `SECURITY DEFINER` with explicit `search_path` for security
     - Granted to `authenticated` role
     - Returns fields: subscription_id, user_id, user_email, first_name, last_name, status, current_period_start, current_period_end, cancel_at_period_end, product_name, currency, unit_amount, billing_interval, plan

## Migration Instructions

### 1. Run the migration

```bash
# Connect to your Supabase database and run:
psql -U postgres -d postgres -f sql/37_secure_active_subscriptions.sql
```

Or via Supabase Dashboard:
- Go to SQL Editor
- Copy and paste the contents of `sql/37_secure_active_subscriptions.sql`
- Run the migration

### 2. Regenerate TypeScript types

After running the migration, regenerate your database types:

```bash
# For API
supabase gen types typescript --project-id YOUR_PROJECT_ID > api/src/database.types.ts

# For packages/shared
supabase gen types typescript --project-id YOUR_PROJECT_ID > packages/shared/src/types/database.types.ts

# For xtablo-expo
supabase gen types typescript --project-id YOUR_PROJECT_ID > xtablo-expo/lib/database.types.ts
```

### 3. Update any code that references `active_subscriptions`

**If you were querying the view directly** (which should be avoided):

```typescript
// ❌ Old - INSECURE (showed all users' data)
const { data } = await supabase
  .from('active_subscriptions')
  .select('*');

// ✅ New - SECURE (only shows current user's data)
const { data } = await supabase
  .rpc('get_my_active_subscription');

// Returns: {
//   subscription_id, user_id, user_email, first_name, last_name,
//   status, current_period_start, current_period_end,
//   cancel_at_period_end, product_name, currency, unit_amount,
//   billing_interval, plan
// }
```

## Current Application Status

✅ **Good news**: The application code already uses the secure `get_user_stripe_subscriptions()` function instead of directly querying the view, so no application code changes are needed!

The view was only used for:
- Documentation examples
- Database type definitions (auto-generated)
- Potential ad-hoc queries

## Security Best Practices

### Why use functions instead of views for sensitive data?

1. **Explicit access control** - Functions can check `auth.uid()` to ensure users only see their own data
2. **Permission granularity** - Can grant execute permissions to specific roles
3. **Security definer** - Functions run with specific privileges and search paths
4. **Audit trail** - Function calls can be logged more easily than view queries

### SECURITY DEFINER best practices

When using `SECURITY DEFINER` on functions:

1. **Always set search_path** - Prevents SQL injection via schema manipulation
   ```sql
   set search_path = public, stripe
   ```

2. **Always validate inputs** - Check `auth.uid()` and other user inputs
   ```sql
   where (c.metadata->>'user_id')::uuid = auth.uid()
   ```

3. **Minimal permissions** - Only grant execute to roles that need it
   ```sql
   grant execute on function public.get_my_active_subscription() to authenticated;
   ```

4. **Avoid dynamic SQL** - Use parameterized queries, not string concatenation

## Testing

### Test user access (should work)

```sql
-- As an authenticated user, get your own subscription
SELECT * FROM get_my_active_subscription();

-- Should return your subscription or empty result if no active subscription
-- Should only show YOUR data, never other users' data
```

### Test in your application

```typescript
// In your React component
const { data: subscription } = await supabase
  .rpc('get_my_active_subscription');

console.log(subscription);
// Should show your subscription with all fields:
// billing_interval, product_name, status, etc.
```

## Related Files

- Migration: `sql/37_secure_active_subscriptions.sql`
- Previous migrations: 
  - `sql/35_stripe_wrappers.sql` (created the view)
  - `sql/36_fix_stripe_subscription_dates.sql` (updated the view)
- Documentation: This file

## Questions?

If you have questions about this security fix, please refer to:
- `docs/STRIPE_ARCHITECTURE.md` - Stripe integration architecture
- `docs/STRIPE_QUICK_REFERENCE.md` - Updated with secure query examples

