Production Database Migrations

Current Status

The following migrations need to be applied to your production Supabase database:

1. 015_add_tone_packs.sql - Creates tone packs tables
2. 016_add_drops_currency.sql - Adds drops currency system
3. 017_add_token_tracking.sql - Adds token tracking and conversion rates

How to Apply Migrations

Option 1: Supabase Dashboard (Recommended)

1. Go to your Supabase Dashboard

   • Navigate to https://supabase.com/dashboard
   • Select your project

2. Open SQL Editor

   • Click on "SQL Editor" in the left sidebar
   • Click "New query"

3. Run Each Migration in Order

   Migration 015: Tone Packs

   • Copy the entire contents of supabase/migrations/015_add_tone_packs.sql
   • Paste into SQL Editor
   • Click "Run" (or press Cmd/Ctrl + Enter)
   • Wait for success message

   Migration 016: Drops Currency

   • Copy the entire contents of supabase/migrations/016_add_drops_currency.sql
   • Paste into SQL Editor
   • Click "Run"
   • Wait for success message

   Migration 017: Token Tracking

   • Copy the entire contents of supabase/migrations/017_add_token_tracking.sql
   • Paste into SQL Editor
   • Click "Run"
   • Wait for success message

4. Verify

   • Go to "Table Editor" in Supabase
   • You should see these new tables:
     • tone_packs
     • tone_pack_images
     • tone_pack_shares
     • drop_transactions
     • monthly_drops
     • tone_pack_purchases
     • token_usage
     • currency_conversion_rates
   • The profiles table should have a drops_balance column
   • The tone_packs table should have a price_drops column

Option 2: Supabase CLI

If you have Supabase CLI installed:

# Install Supabase CLI (if not installed)
npm install -g supabase

# Link to your project
supabase link --project-ref your-project-ref

# Push all migrations
supabase db push

What Each Migration Does

Migration 015: Tone Packs

• Creates tone_packs table for tone pack metadata
• Creates tone_pack_images table for tone-to-image mappings
• Creates tone_pack_shares table for sharing
• Sets up RLS policies

Migration 016: Drops Currency

• Adds drops_balance column to profiles table
• Creates drop_transactions table for transaction history
• Creates monthly_drops table for tracking monthly awards
• Adds price_drops column to tone_packs table
• Creates tone_pack_purchases table
• Sets up database trigger for signup bonus (50 drops)

Migration 017: Token Tracking

• Creates token_usage table for tracking OpenAI API usage
• Creates currency_conversion_rates table for managing conversion rates
• Creates get_user_token_usage_monthly() function for monthly aggregation
• Sets up indexes for performance

After Running Migrations

Once all migrations are applied:

1. Restart your Railway backend (if needed)
2. Test the features:
   • Create a tone pack in settings
   • Check your drops balance
   • View token usage in admin panel (if super admin)

Troubleshooting

If you get errors:

1. "relation already exists" - The table already exists, you can skip that migration
2. "column already exists" - The column already exists, safe to ignore
3. "permission denied" - Make sure you're using the SQL Editor (not the Table Editor)
4. "function already exists" - The function already exists, safe to ignore
5. "policy already exists" - The policy already exists, safe to ignore

Most migrations use CREATE IF NOT EXISTS or ADD COLUMN IF NOT EXISTS so they're safe to run multiple times.

Quick Verification Query

Run this in SQL Editor to verify all tables exist:

SELECT table_name 
FROM information_schema.tables 
WHERE table_schema = 'public' 
  AND table_name IN (
    'tone_packs',
    'tone_pack_images',
    'tone_pack_shares',
    'drop_transactions',
    'monthly_drops',
    'tone_pack_purchases',
    'token_usage',
    'currency_conversion_rates'
  )
ORDER BY table_name;

You should see all 8 tables listed.