subscription-schemas

# Subscription Schemas

Production-ready Supabase database schemas for subscription and payment management with comprehensive security policies.

## Security Requirements

This skill follows strict security rules:

- **NO hardcoded database credentials** - All connection strings use placeholders
- **Environment variable references** - Code reads from `SUPABASE_URL` and `SUPABASE_KEY`
- **Row Level Security (RLS)** - All tables protected with comprehensive policies
- **Data encryption** - Sensitive payment data properly protected
- **Audit logging** - Webhook events tracked for compliance

All examples use placeholder values like `your_supabase_url_here`.

## Database Schema Overview

The subscription schema consists of five core tables:

1. **customers** - Customer profiles linked to auth.users
2. **subscriptions** - Active and historical subscriptions
3. **payments** - Payment transaction records
4. **invoices** - Invoice history and status
5. **webhook_events** - Payment provider webhook logs

## Table Relationships

```
auth.users (Supabase Auth)
    ↓
customers (1:1 with users)
    ↓
subscriptions (1:many)
    ↓
payments (1:many per subscription)
invoices (1:many per subscription)

webhook_events (independent audit log)
```

## Use When

- Setting up a new subscription-based application
- Implementing payment tracking for SaaS products
- Migrating payment infrastructure to Supabase
- Adding Row Level Security to payment tables
- Configuring multi-tenant payment isolation
- Creating invoice and payment history tracking
- Implementing webhook event logging for Stripe/Paddle/LemonSqueezy

## Instructions

### Phase 1: Create Database Tables

1. **Review table schemas**:
   - Read `templates/customers_table.sql` for customer profiles
   - Read `templates/subscriptions_table.sql` for subscription management
   - Read `templates/payments_table.sql` for payment records
   - Read `templates/invoices_table.sql` for invoice tracking
   - Read `templates/webhook_events_tab