TypeORM Migrations Strategy — Payday v0.1.0

#Overview

Starting with v0.1.0, Payday uses TypeORM migrations instead of DB_SYNC=true. This ensures:

• Production safety: No automatic schema mutations
• Data integrity: Full version control of schema changes
• Rollback capability: Down migrations for emergency reverts
• Auditability: Complete migration history in database

#Current Migrations

##1. 1774017863432-CreateInitialSchema.ts

Status: Baseline schema (23 tables, 12 enums, all indexes & triggers) Run: First, on any new database Includes:

• User + Authentication tables
• Content tables (Posts, Channels, Resources, Opportunities, Events)
• Social tables (Connections, Conversations, Messages)
• Admin tables (Moderation Logs, Member Applications)
• Support tables (Subscriptions, Notifications, Badges, Ratings)

##2. 1774017863433-SeedInitialData.ts

Status: Seeding (12 channels + 4 test users for dev) Run: After CreateInitialSchema Dev: Includes test users (admin@payday.dev, test2-4@payday.dev) Prod: Remove test user seeding before deploying in production

#Environment Configuration

##Development (DB_SYNC=false)

• Uses migrations on startup
• Full SQL logging for debugging
• Queries printed to console

DB_SYNC=false
DB_LOGGING=true
NODE_ENV=development

##Production (DB_SYNC=false)

• Migrations run automatically on startup
• No SQL logging (performance, security)
• Requires all migrations to be committed and tested

DB_SYNC=false
DB_LOGGING=false
NODE_ENV=production

#Migration Workflow

##Generating New Migrations (Development)

After modifying an entity, generate a migration:

cd server/
npm run migration:generate -- src/database/migrations/AddNewColumn

Important:

• Always review the generated migration before committing
• Test on a test database first
• Ensure both up() and down() are correct

##Running Migrations Manually

# Run pending migrations (happens auto on startup)
docker compose exec api npm run migration:run

# Revert last migration  
docker compose exec api npm run migration:revert

# Show migration status
docker compose exec api npm run migration:show

##CLI Commands Available

# In server/package.json scripts:
npm run migration:generate   # Generate from entity changes
npm run migration:run        # Run pending migrations
npm run migration:revert     # Undo last migration
npm run migration:show       # Show migration history

#Deployment Checklist

##Before Deploying New Migration to Production

• Migration tested on local dev database
• Migration tested on staging database (full copy of production)
• Both up() and down() methods verified
• No data loss in down() (use cascading deletes or archive tables)
• Performance tested: large table migrations don't lock DB for >30 seconds
• Committed to Git with full review
• Tagged in release notes
• Rollback plan documented (if needed)

##Zero-Downtime Deployments

For migrations that affect production data:

1. Expand phase: Add new column/table (backwards compatible)
2. Migrate phase: Deploy code that uses both old & new structure
3. Deploy phase: Code switches to new structure atomically
4. Cleanup phase: Remove old structure in future migration

Example:

// Migration 1: Add new column
ALTER TABLE users ADD COLUMN email_verified_v2 BOOLEAN DEFAULT false;

// Code: Use both (v1 and v2)
// Code deploys first, then migration runs on startup

// Migration 2 (later): Drop old column
ALTER TABLE users DROP COLUMN email_verified;

#Troubleshooting

##"typeorm_metadata table not found"

• Database is fresh: Run migrations normally (auto on startup)
• Database has data but no history: This is OK, TypeORM will create metadata table

##"Migration XXX already exists"

• Do not re-run migrations
• Check database for corrupted migration record: SELECT * FROM typeorm_metadata

##"Migration would data" error

• Add IF EXISTS clause in migration
• Or provide explicit rollback in down()

##Rollback failed, can't revert

Emergency recovery:

# 1. Connect to database
docker compose exec postgres psql -U payday_user -d payday_db

# 2. View migration history
SELECT * FROM typeorm_metadata ORDER BY name DESC;

# 3. Manually delete problematic row (DANGEROUS!)
DELETE FROM typeorm_metadata WHERE name = 'Problem_Migration_Name';

# 4. Run migration again
npm run migration:run

#Production Readiness

✅ Schema changes are now safely versioned ✅ Rollbacks are automated ✅ Downtime risk is minimized with proper migration design ✅ Database state is trackable in code

Next: When new features require schema changes, follow the migration workflow above. Test thoroughly before production deployment.