Skip to main content

Source: ocean/docs/adr/030-production-database-migration-issue.md | ✏️ Edit on GitHub

ADR-030: Production Database Migration Issue - Missing Tables

Status​

Accepted

Date​

2025-08-28

Context​

During user signup testing, we discovered a critical issue where the provisioning_events table (and potentially other tables) don't exist in the production database. The error message revealed:

ERROR: relation "provisioning_events" does not exist (SQLSTATE 42P01)

This indicates that the initial database migration (20250812233110_initial_schema.sql) was never applied to production, even though the application has been deployed multiple times.

Investigation​

  1. Local Environment: All tables exist and function correctly
  2. Production Environment: Core tables are missing, causing signup failures
  3. Error Pattern:
    • User attempts to sign up
    • handle_new_user trigger fires
    • Trigger attempts to log to provisioning_events table
    • Transaction fails with "table does not exist" error

Decision​

We need to ensure all migrations are properly applied to production:

  1. Immediate Fix:

    • Monitor the current deployment triggered by commit 306c1d1
    • Verify migrations are applied via GitHub Actions logs
    • If migrations fail, manually apply them through Supabase dashboard

  2. Long-term Solution:

    • Add migration status checks to deployment workflow
    • Implement pre-deployment database health checks
    • Add monitoring alerts for migration failures

Consequences​

Positive​

  • Once migrations are applied, signup functionality will work correctly
  • We've identified a gap in our deployment process
  • Future deployments will be more reliable with proper checks

Negative​

  • All signup attempts have been failing in production
  • Users have been unable to create accounts
  • Trust in the platform may have been affected

Neutral​

  • This is a one-time issue that shouldn't recur once properly fixed
  • The fix doesn't require code changes, only proper migration execution

Implementation Notes​

Verifying Migration Status​

-- Check if critical tables exist
SELECT table_name
FROM information_schema.tables
WHERE table_schema = 'public'
AND table_name IN (
  'provisioning_events',
  'organizations',
  'organization_members',
  'profiles',
  'organization_databases'
);

-- Check migration history
SELECT version, name, executed_at
FROM supabase_migrations.schema_migrations
ORDER BY executed_at DESC;

Manual Migration (if needed)​

If automatic migration fails, apply through Supabase Dashboard:

  1. Go to SQL Editor
  2. Run each migration file in order starting from 20250812233110_initial_schema.sql
  3. Verify tables are created
  4. Test signup flow

Monitoring​

Add these checks to prevent future issues:

  1. GitHub Actions should verify migration success
  2. Add Sentry alerts for "table does not exist" errors
  3. Implement database health endpoint that checks critical tables
  • User signup failures
  • "Database error saving new user" messages
  • GraphQL error handling fix (commit 306c1d1)

References​