Fix Deployment Failure Due to Migration Desynchronization

[YuraPetrovskyi]

Expected Behavior

Once this Issue is resolved:

✅ The GitHub Actions deployment workflow will pass successfully.
✅ All past migration history will be properly synchronized between local and remote environments.
✅ Future migrations will be applied without errors.

Acceptance Criteria

• Revert all outdated migrations on the remote database.
• Synchronize the local and remote database schemas.
• Manually clean up ALTER PUBLICATION statements in the migration file.
• Make sure that local testing is successful

Current Behavior

Right now, deployment via GitHub Actions fails with the following error message:

Make sure your local git repo is up-to-date. If the error persists, try repairing the migration history table:
supabase migration repair --status reverted 20240419112011 20240513103433 20240513103606 20240513161817 20240513222224 20240604162858 20240708170411 20240709102032 20240906121523 20240913135301 20241004151748 20241114231634

This indicates that Supabase still expects old migrations that were previously deleted, causing a mismatch between the local and remote migration histories.

And during local testing, running supabase db reset triggered:

ERROR: must be member of role "supabase_admin" (SQLSTATE 42501)
At statement 91: ALTER PUBLICATION "supabase_realtime_messages_publication" OWNER TO "supabase_admin"
These errors suggest that Supabase expects certain database structures that do not match our new migration file.

Root Cause Analysis
1️⃣ Old migrations were removed without properly reverting them on the remote database.
2️⃣ Supabase still retains a record of old migrations, which causes deployment to fail.
3️⃣ The supabase db pull command generated unnecessary ALTER PUBLICATION statements, leading to permission issues during local testing.

Proposed Solution & Fix
✔️ Step 1: Revert outdated migrations on the remote database:
npx supabase migration repair <migration_id> --status reverted
✔️ Step 2: Pull the latest database schema from Supabase to sync local and remote environments:
npx supabase db pull
✔️ Step 3: Create a new migration file for the avatar column update:
npm run migrate update-profile-avatar
✔️ Step 4: Run tests to make sure everything is fine.

Problems with local testing:
Attempted to test locally with npx supabase db reset and encountered another error:

ERROR: must be member of role "supabase_admin" (SQLSTATE 42501)
At statement 91: ALTER PUBLICATION "supabase_realtime_messages_publication" OWNER TO "supabase_admin"

🔍 This was caused by unnecessary ALTER PUBLICATION statements that were automatically added in the new migration.

Root Cause Analysis:

• The supabase db pull command created unnecessary ALTER PUBLICATION statements, which do not exist in our local setup.
• The supabase_admin role is not available in the local environment, causing permission issues when testing locally.

Proposed Fix:

• Remove unnecessary ALTER PUBLICATION statements from the migration file (20250207173330_remote_schema.sql):

Any other notes

🚀 Once this Issue is resolved, all future migrations should be applied correctly via GitHub Actions without manual intervention.

This Issue is critical because without a working staging deployment, we cannot test or release new changes effectively.

Assignment

This issue is free for anyone to take

[github-actions]

🤖 meep morp!

This Issue is now marked as stale because there has been no activity for a while.

🔎 The Issue will be automaticaly unassigned and moved to the Backlog if it doesn't receive new activity in the next 7 days.

💡 To unstale this Issue please push any commits that are ready or provide an update in the comments.