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:

• customers - Customer profiles linked to auth.users

• subscriptions - Active and historical subscriptions

• payments - Payment transaction records

• invoices - Invoice history and status

• 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

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_table.sql for webhook logging

Execute table creation:

bash scripts/create-payment-tables.sh

This script will:

• Create all five tables with proper indexes

• Set up foreign key relationships

• Add check constraints for data validation

• Create updated_at triggers

Phase 2: Implement Row Level Security

Review RLS policies:

• Read templates/rls_policies.sql for complete policy definitions

• Understand customer data isolation

• Review subscription access controls

• Check payment data protection rules

Enable RLS and create policies:

bash scripts/setup-rls-policies.sh

This script will:

• Enable RLS on all payment tables

• Create SELECT policies for authenticated users

• Create INSERT policies with validation

• Create UPDATE policies with ownership checks

• Create DELETE policies (restricted)

Phase 3: Run Complete Migration

Use the complete schema migration:

bash scripts/migrate-schema.sh

This orchestrates:

• Table creation in correct order

• Index creation for performance

• RLS policy setup

• Validation of schema structure

Verify migration success:

bash scripts/validate-schema.sh

Validates:

• All tables exist

• Indexes are created

• RLS is enabled

• Policies are active

• Foreign keys are valid

Phase 4: Test RLS Policies

Review RLS testing examples:

• Read examples/rls-testing-examples.sql

• Test customer data isolation

• Verify subscription access controls

• Confirm payment data protection

Run sample queries:

• Read examples/sample-queries.sql

• Test common subscription queries

• Verify payment history retrieval

• Check invoice generation queries

Security Compliance

Row Level Security Policies

All tables implement RLS with these principles:

• Customer Isolation: Users only access their own customer record

• Subscription Ownership: Users only see their own subscriptions

• Payment Privacy: Payment records restricted to owners

• Invoice Access: Invoices accessible only to associated customers

• Webhook Audit: Webhook events visible to admins only

Data Protection

• Sensitive fields: Payment methods, billing details encrypted

• PII protection: Customer data isolated per user

• Audit trail: All webhook events logged

• No direct access: All queries filtered through RLS

Environment Variables

Connection configuration reads from:

SUPABASE_URL=your_supabase_url_here SUPABASE_ANON_KEY=your_supabase_anon_key_here SUPABASE_SERVICE_ROLE_KEY=your_service_role_key_here # For migrations only

Available Scripts

create-payment-tables.sh

Creates all five payment tables with proper structure, indexes, and constraints.

Usage:

bash scripts/create-payment-tables.sh

Environment Required:

• SUPABASE_URL

• SUPABASE_SERVICE_ROLE_KEY

setup-rls-policies.sh

Enables RLS and creates comprehensive security policies for all tables.

Usage:

bash scripts/setup-rls-policies.sh

Environment Required:

• SUPABASE_URL

• SUPABASE_SERVICE_ROLE_KEY

migrate-schema.sh

Orchestrates complete schema setup including tables, indexes, and RLS.

Usage:

bash scripts/migrate-schema.sh

Environment Required:

• SUPABASE_URL

• SUPABASE_SERVICE_ROLE_KEY

validate-schema.sh

Validates that all tables, indexes, and policies are correctly configured.

Usage:

bash scripts/validate-schema.sh

Environment Required:

• SUPABASE_URL

• SUPABASE_ANON_KEY

Available Templates

customers_table.sql

Customer profile table with Supabase Auth integration.

Fields:

• id (uuid, primary key)

• user_id (uuid, foreign key to auth.users)

• email (text)

• name (text)

• billing_address (jsonb)

• created_at , updated_at (timestamptz)

subscriptions_table.sql

Subscription tracking with status and billing cycles.

Fields:

• id (uuid, primary key)

• customer_id (uuid, foreign key)

• plan_id (text)

• status (enum: active, canceled, past_due, trialing)

• current_period_start , current_period_end (timestamptz)

• cancel_at_period_end (boolean)

• created_at , updated_at (timestamptz)

payments_table.sql

Payment transaction records.

Fields:

• id (uuid, primary key)

• subscription_id (uuid, foreign key)

• amount (numeric)

• currency (text)

• status (enum: succeeded, pending, failed)

• payment_method (text)

• provider_payment_id (text)

• created_at (timestamptz)

invoices_table.sql

Invoice generation and tracking.

Fields:

• id (uuid, primary key)

• subscription_id (uuid, foreign key)

• invoice_number (text, unique)

• amount_due (numeric)

• amount_paid (numeric)

• status (enum: draft, open, paid, void)

• due_date (date)

• created_at , updated_at (timestamptz)

webhook_events_table.sql

Webhook event logging for audit and replay.

Fields:

• id (uuid, primary key)

• provider (text, e.g., 'stripe', 'paddle')

• event_type (text)

• payload (jsonb)

• processed (boolean)

• created_at (timestamptz)

rls_policies.sql

Complete RLS policy definitions for all tables with customer isolation.

Available Examples

complete-schema-migration.sql

Complete migration script showing full schema setup in one file.

sample-queries.sql

Common query patterns:

• Get active subscriptions for user

• Retrieve payment history

• Generate invoice summaries

• Check subscription status

rls-testing-examples.sql

Test cases for RLS policies:

• Verify customer isolation

• Test subscription access

• Validate payment privacy

• Confirm admin-only webhook access

Requirements

• Supabase project with database access

• Service role key for migrations (secure storage required)

• Anon key for client-side queries

• PostgreSQL extensions: uuid-ossp , pgcrypto

Migration Strategy

Initial Setup

• Run create-payment-tables.sh to create schema

• Run setup-rls-policies.sh to enable security

• Run validate-schema.sh to confirm setup

Schema Updates

• Create new migration file in templates/

• Test in development environment first

• Apply using migrate-schema.sh

• Always validate after migrations

Rollback Support

Each template includes a rollback section:

-- Rollback DROP TABLE IF EXISTS table_name CASCADE;

Performance Considerations

Indexes Created

• customers.user_id

• Fast auth lookups

• subscriptions.customer_id

• Customer subscription queries

• subscriptions.status

• Status filtering

• payments.subscription_id

• Payment history

• invoices.subscription_id

• Invoice retrieval

• webhook_events.provider, event_type

• Event filtering

Query Optimization

• Use explicit WHERE clauses with RLS

• Include auth.uid() checks in queries

• Cache frequently accessed data

• Use EXPLAIN ANALYZE for slow queries

Integration with Payment Providers

Stripe

• Use webhook_events to log Stripe webhooks

• Map provider_payment_id to Stripe payment intent IDs

• Store Stripe customer ID in customers.metadata

Paddle

• Log Paddle webhooks with provider='paddle'

• Map subscription IDs to Paddle subscription IDs

• Store Paddle customer ID in customer metadata

LemonSqueezy

• Track LemonSqueezy events in webhook_events

• Map variant IDs to plan_id in subscriptions

• Store LemonSqueezy customer ID in metadata

Compliance Notes

• PCI DSS: No credit card numbers stored (use payment provider tokens)

• GDPR: Customer data can be deleted via user_id cascade

• Audit Trail: All webhook events logged for compliance

• Data Retention: Configure automated archival policies as needed