Rollback Strategy

Drizzle migrations are forward-only. There is no pnpm db:rollback command. Reversing a change means writing a new forward migration that undoes the problematic one, or restoring from a backup if data was lost.

Decision Tree

When a migration causes problems, follow this decision tree:

1. Is the application still functional?

   • Yes: Write a corrective migration (the normal case)
   • No: Continue to step 2

2. Is there data loss risk?

   • No: Write a corrective migration
   • Yes: Restore from backup or PITR, then apply a corrective migration

Corrective Migration (Normal Case)

Most rollbacks are handled by writing a new migration that reverses the problematic change. This is the standard approach for:

• Accidentally added columns or tables
• Wrong column type or constraint
• Missing or incorrect indexes
• RLS policy errors

Procedure

1. Write the reversal in the schema or as a hand-crafted migration:

   -- Reversal: drop the column added in 0010
   ALTER TABLE "research_plan" DROP COLUMN IF EXISTS "bad_column";

2. If reverting a schema change, edit the TypeScript schema to match the desired state, then run pnpm db:generate. Drizzle Kit will generate the DROP/ALTER statements.

3. If hand-crafting, create the SQL file manually and add the journal entry:

   • Create the .sql file with the correct sequence number
   • Run pnpm db:generate to capture the current schema as a snapshot (you may need to generate a no-op migration to get the snapshot, then replace the SQL)

4. Test locally against a database that has the broken migration applied:

   pnpm db:migrate    # Apply the corrective migration
   pnpm test          # Verify tests pass

5. Commit and push to main. The CI migrate-prod job applies the corrective migration to production automatically.

Common Reversal Patterns

Original Change	Reversal SQL
Added a column	ALTER TABLE t DROP COLUMN IF EXISTS col;
Added a table	DROP TABLE IF EXISTS t;
Added an index	DROP INDEX IF EXISTS idx;
Added a constraint	ALTER TABLE t DROP CONSTRAINT IF EXISTS c;
Changed column type	ALTER TABLE t ALTER COLUMN col TYPE old_type;
Added RLS policy	DROP POLICY IF EXISTS p ON t;
Added enum value	Cannot be reversed in PostgreSQL (enum values are permanent)

Enum Value Warning

PostgreSQL does not support removing values from an existing enum type. If a migration added an enum value that needs to be removed, the options are:

1. Leave the unused value in place (preferred if harmless)
2. Create a new enum type, migrate the column, and drop the old type (complex, requires careful sequencing)

Backup Restore (Data Loss Case)

If the migration caused data loss (e.g., dropped a column with data, truncated a table), use Cloud SQL's backup or point-in-time recovery.

Automated Backups

Cloud SQL takes daily backups at 04:00 UTC with 7-day retention. Point-in-time recovery (PITR) is enabled.

Pre-Migration Manual Backup

Before applying a risky migration to production, create an on-demand backup:

gcloud sql backups create \
  --instance=trovella-prod \
  --project=trovella-prod \
  --description="Pre-migration backup $(date -u +%Y-%m-%dT%H:%M:%SZ)"

Point-in-Time Recovery

Restore to any point within the 7-day retention window by cloning to a recovery instance:

gcloud sql instances clone trovella-prod trovella-prod-recovery \
  --project=trovella-prod \
  --point-in-time="2026-04-05T10:30:00Z"

Then:

1. Connect to the recovery instance and export the needed data
2. Apply the corrective migration to production
3. Import the recovered data into production
4. Delete the recovery instance when done

Full Restore

If the entire database needs to be reverted:

# List available backups
gcloud sql backups list --instance=trovella-prod --project=trovella-prod

# Restore (replaces ALL data in the instance)
gcloud sql backups restore <backup-id> \
  --restore-instance=trovella-prod \
  --project=trovella-prod

Warning: Full restore replaces all data. Every change after the backup point is lost.

Prevention

The best rollback is one you never need. These practices reduce the risk:

1. Always review generated SQL before applying (see Development Workflow)
2. Test locally first against Docker PostgreSQL before pushing
3. Use IF NOT EXISTS / IF EXISTS guards in hand-edited migrations
4. Make schema changes additive when possible -- add new columns as nullable, then backfill, then add constraints in a follow-up migration
5. CI catches errors early -- the ephemeral database in the quality job validates migrations on a fresh database before migrate-prod runs

Related

• CI Deployment -- how migrations reach production
• Development Workflow -- the review step that catches issues before they deploy
• Reference -- how to connect to production for debugging