Skip to main content

Migration Deployment Guide

Overview​

This guide ensures all database migrations are properly applied to staging and production environments through the standard migration process, not manual SQL changes.

Workflow​

Local Development → Test on Staging → Deploy to Production
     ↓                    ↓                    ↓
  Migrations          Migrations          Migrations
  (supabase/          (supabase/          (supabase/
   migrations/)        migrations/)        migrations/)

Key Principles​

  1. All database changes must be in migration files - Never apply manual SQL to production
  2. Test on staging first - Always verify migrations work on staging before production
  3. Use Supabase CLI - Deploy migrations via supabase db push --linked --include-all
  4. Verify before deploying - Use scripts/verify-migrations-applied.sh to check status

Migration Files Location​

All migrations are stored in: supabase/migrations/

Migration files follow the naming pattern:

YYYYMMDDHHMMSS_description.sql

Example: 20260113000012_grant_table_permissions_to_authenticated.sql

Deployment Process​

Step 1: Create Migration Files​

When making database changes:

  1. Create a new migration file in supabase/migrations/
  2. Use descriptive names
  3. Include comments explaining the purpose
  4. Make migrations idempotent when possible (use CREATE OR REPLACE, IF NOT EXISTS, etc.)

Step 2: Test on Staging​

Option A: Using Supabase CLI (Recommended)

# Link to staging
supabase link --project-ref kqnsvwdnnkaujdsabegb

# Push migrations
supabase db push --linked --include-all

# Verify migrations applied
./scripts/verify-migrations-applied.sh staging

Option B: Direct SQL Application (If CLI not available)

# Apply migration directly
./scripts/apply-migration-to-staging.sh

# Verify migrations applied
./scripts/verify-migrations-applied.sh staging

Step 3: Verify on Staging​

  1. Test the application functionality
  2. Check for errors in console/logs
  3. Verify database changes are correct
  4. Run verification script

Step 4: Deploy to Production​

# Link to production
supabase link --project-ref zvyvpucxdnftgrixxrxy

# Push migrations
supabase db push --linked --include-all

# Verify migrations applied
./scripts/verify-migrations-applied.sh production

Verification Script​

The scripts/verify-migrations-applied.sh script:

  • Compares local migration files with applied migrations in database
  • Reports which migrations are missing
  • Provides instructions for applying missing migrations

Usage:

# Check staging
./scripts/verify-migrations-applied.sh staging

# Check production
./scripts/verify-migrations-applied.sh production

Common Migration Types​

1. Table Permissions (GRANT statements)​

Example: 20260113000012_grant_table_permissions_to_authenticated.sql

GRANT SELECT, INSERT, UPDATE, DELETE ON public.table_name TO authenticated;

Note: GRANT statements are idempotent - safe to run multiple times.

2. RPC Functions​

Example: 20260113000007_create_restaurant_recent_activity_rpc.sql

CREATE OR REPLACE FUNCTION public.function_name(...)
RETURNS TABLE(...)
LANGUAGE sql
STABLE
SECURITY DEFINER
AS $$ ... $$;

GRANT EXECUTE ON FUNCTION public.function_name(...) TO authenticated;

3. RLS Policies​

CREATE POLICY "policy_name" ON public.table_name
FOR SELECT
USING (condition);

4. Schema Changes​

ALTER TABLE public.table_name
ADD COLUMN column_name type;

Troubleshooting​

Migration Already Applied Error​

If you see "migration already applied" errors:

  1. Check if the migration is actually in the database: 
    SELECT * FROM supabase_migrations.schema_migrations 
    WHERE version = 'YYYYMMDDHHMMSS';
  2. If it exists but has errors, you may need to repair it (see Supabase docs)

Missing Permissions After Migration​

If permissions are missing after migration:

  1. Check if GRANT statements are in the migration file
  2. Verify the migration was applied: ./scripts/verify-migrations-applied.sh
  3. Check if pg_dump --no-acl was used (this strips permissions)

Staging Out of Sync​

If staging is missing migrations:

  1. Run verification: ./scripts/verify-migrations-applied.sh staging
  2. Apply missing migrations: supabase db push --linked --include-all
  3. Re-verify: ./scripts/verify-migrations-applied.sh staging

Important Notes​

  1. Never use pg_dump --no-acl - This strips GRANT permissions
  2. Always include GRANT statements in migrations - Don't rely on manual application
  3. Test migrations on staging first - Production should mirror staging
  4. Keep migration files in version control - They're the source of truth

Migration Checklist​

Before deploying to production:

  • All changes are in migration files (not manual SQL)
  • Migrations tested on staging
  • Verification script passes on staging
  • Application works correctly on staging
  • No console errors on staging
  • Ready to deploy to production
  • scripts/verify-migrations-applied.sh - Migration verification script
  • scripts/apply-migration-to-staging.sh - Direct migration application script
  • scripts/clone-prod-to-staging.sh - Database cloning (includes permission sync)
  • supabase/migrations/ - All migration files