Skip to content

Latest commit

 

History

History
127 lines (93 loc) · 4.39 KB

DATABASE_MIGRATION_INSTRUCTIONS.md

File metadata and controls

127 lines (93 loc) · 4.39 KB

Database Migration Instructions

⚠️ Error Fixed: Updated Migration Available

The previous migration failed due to missing created_at column. I've created a new safe migration script.

Steps to Run Migration

Option 1: Safe Migration (Recommended)

  1. Go to Supabase Dashboard

  2. Run the Safe Migration Script

    • Copy the contents of safe-migration.sql
    • Paste into the SQL Editor
    • Click "Run" to execute
    • This script safely checks for existing columns before adding new ones

Option 2: Updated Simple Migration

  1. Use the Updated Simple Script
    • Copy the contents of run-this-in-supabase.sql (now updated with created_at)
    • Paste into SQL Editor and run

What the Migration Does

Adds to dreams table:

  • created_at - Timestamp when dream was created (if missing)
  • updated_at - Timestamp when dream was last updated (if missing)
  • sandbox_id - UUID for permanent dream identification
  • processing_status - Status of AI analysis
  • ai_analysis - JSONB field for AI analysis results
  • ai_tags - Array of AI-generated tags
  • ai_emotions - Array of detected emotions
  • ai_themes - Array of identified themes
  • confidence_score - AI confidence level
  • mood_score - Numerical mood assessment
  • lucidity_indicators - Array of lucidity markers
  • symbolic_elements - JSONB of symbolic analysis
  • personal_insights - Text field for personalized insights
  • sleep_quality - Sleep quality rating

Creates new tables:

  • dream_patterns - For storing identified patterns
  • pattern_analysis_jobs - For tracking background job progress

Creates indexes and policies:

  • Performance indexes for search and filtering
  • Row Level Security (RLS) policies for data protection
  • Triggers for automatic timestamp updates

After Migration

Once the migration is complete:

  1. Test the API endpoints

    • POST /api/dreams - Should generate sandbox IDs
    • POST /api/patterns/analyze - Should trigger pattern analysis

  2. Monitor Inngest Jobs

    • Check Inngest dashboard for job execution
    • Verify AI analysis is being stored

  3. Test Frontend Features

    • Create new dreams and verify AI analysis
    • Use pattern analysis button on patterns page
    • Check that data persists correctly

Troubleshooting

If you encounter any issues:

  1. Check current table structure first:

    SELECT column_name, data_type, is_nullable 
FROM information_schema.columns 
WHERE table_name = 'dreams' 
ORDER BY ordinal_position;

  2. Check Supabase logs for detailed error messages

  3. Verify RLS policies are enabled and working

  4. Verify indexes were created successfully

Previous Error Fix

The previous error "column 'created_at' does not exist" has been fixed by:

  • Adding conditional column creation in the safe migration script
  • Including created_at and updated_at columns in the simple migration script
  • Using proper PostgreSQL syntax to check for existing columns before adding new ones

✅ Current Status

Completed:

  • ✅ Inngest CLI installed and running (port 8288)
  • ✅ Required packages installed (uuid, inngest, openai)
  • ✅ Enhanced Dreams API with sandbox ID generation
  • ✅ Individual Dream Analysis Inngest job created
  • ✅ Pattern Analysis Inngest job created
  • ✅ Pattern Analysis API endpoint created
  • ✅ Inngest functions registered in API route
  • ✅ Migration scripts fixed for missing columns

Next Steps After Migration:

  1. Run the safe migration script in Supabase
  2. Test individual dream analysis by saving a dream
  3. Test pattern analysis by clicking the button in patterns page
  4. Monitor Inngest dashboard for job execution
  5. Verify AI responses are being generated and stored

🚀 Ready to Test!

Once the database migration is complete, the backend will be ready for testing:

  1. Save a Dream → Should trigger individual AI analysis with humor
  2. Click "New AI Analysis" in patterns page → Should analyze all dreams for patterns
  3. Check Inngest Dashboard → Monitor job execution in real-time

Both Inngest dev server (port 8288) and Next.js dev server (port 3001) are running and ready!

Need Help?

If the migration fails again, share the exact error message and I'll help debug the issue. The new safe migration script should handle most edge cases and existing schema variations.