Production-ready Supabase database schemas for customers, subscriptions, payments, invoices, and webhook events with comprehensive Row Level Security policies. Use when setting up payment infrastructure, creating subscription tables, implementing secure payment data storage, or configuring RLS policies for multi-tenant payment systems.
Limited to specific tools
Additional assets for this skill
This skill is limited to using the following tools:
examples/complete-schema-migration.sqlexamples/rls-testing-examples.sqlexamples/sample-queries.sqlscripts/create-payment-tables.shscripts/migrate-schema.shscripts/setup-rls-policies.shscripts/validate-schema.shtemplates/customers_table.sqltemplates/invoices_table.sqltemplates/payments_table.sqltemplates/rls_policies.sqltemplates/subscriptions_table.sqltemplates/webhook_events_table.sqlProduction-ready Supabase database schemas for subscription and payment management with comprehensive security policies.
This skill follows strict security rules:
SUPABASE_URL and SUPABASE_KEYAll examples use placeholder values like your_supabase_url_here.
The subscription schema consists of five core tables:
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)
Review table schemas:
templates/customers_table.sql for customer profilestemplates/subscriptions_table.sql for subscription managementtemplates/payments_table.sql for payment recordstemplates/invoices_table.sql for invoice trackingtemplates/webhook_events_table.sql for webhook loggingExecute table creation:
bash scripts/create-payment-tables.sh
This script will:
Review RLS policies:
templates/rls_policies.sql for complete policy definitionsEnable RLS and create policies:
bash scripts/setup-rls-policies.sh
This script will:
Use the complete schema migration:
bash scripts/migrate-schema.sh
This orchestrates:
Verify migration success:
bash scripts/validate-schema.sh
Validates:
Review RLS testing examples:
examples/rls-testing-examples.sqlRun sample queries:
examples/sample-queries.sqlAll tables implement RLS with these principles:
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
Creates all five payment tables with proper structure, indexes, and constraints.
Usage:
bash scripts/create-payment-tables.sh
Environment Required:
SUPABASE_URLSUPABASE_SERVICE_ROLE_KEYEnables RLS and creates comprehensive security policies for all tables.
Usage:
bash scripts/setup-rls-policies.sh
Environment Required:
SUPABASE_URLSUPABASE_SERVICE_ROLE_KEYOrchestrates complete schema setup including tables, indexes, and RLS.
Usage:
bash scripts/migrate-schema.sh
Environment Required:
SUPABASE_URLSUPABASE_SERVICE_ROLE_KEYValidates that all tables, indexes, and policies are correctly configured.
Usage:
bash scripts/validate-schema.sh
Environment Required:
SUPABASE_URLSUPABASE_ANON_KEYCustomer 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)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)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)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 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)Complete RLS policy definitions for all tables with customer isolation.
Complete migration script showing full schema setup in one file.
Common query patterns:
Test cases for RLS policies:
uuid-ossp, pgcryptocreate-payment-tables.sh to create schemasetup-rls-policies.sh to enable securityvalidate-schema.sh to confirm setuptemplates/migrate-schema.shEach template includes a rollback section:
-- Rollback
DROP TABLE IF EXISTS table_name CASCADE;
customers.user_id - Fast auth lookupssubscriptions.customer_id - Customer subscription queriessubscriptions.status - Status filteringpayments.subscription_id - Payment historyinvoices.subscription_id - Invoice retrievalwebhook_events.provider, event_type - Event filteringauth.uid() checks in queriesEXPLAIN ANALYZE for slow querieswebhook_events to log Stripe webhooksprovider_payment_id to Stripe payment intent IDscustomers.metadataprovider='paddle'