Database Schema Management

USE SCHEMA SYNC SCRIPT, NOT ALEMBIC MIGRATIONS

PREFERRED APPROACH: Smart schema sync script that detects and applies only missing changes.

STRICT RULES

• * ✅ ALWAYS USE: scripts/sync-production-schema.py for production deployments
• ✅ IDEMPOTENT: Can run multiple times safely without errors
• ✅ TRANSPARENT: Shows exactly what will change before applying
• ❌ AVOID: Running Alembic migrations directly in production
• ❌ AVOID: Manual SQL scripts that aren't version controlled
• ⚠️ KEEP: Alembic migration files for documentation purposes only

Why Schema Sync Script?

✅ Advantages:

• * Simpler: One script vs managing many migration files
• Safer: Checks what exists before applying changes
• Idempotent: Run multiple times without errors
• Transparent: Shows diff before applying
• Flexible: Works with any database state (dev, staging, prod)
• No tracking: No need to manage "which migrations have run"

❌ Alembic Migration Problems:

• * Fails if run twice (not idempotent)
• Requires tracking which migrations applied
• All-or-nothing (can't skip one migration)
• Complex rollback scenarios
• Team coordination overhead

Workflow

1. DRY RUN FIRST (Always!)

CODEBLOCK0

2. REVIEW OUTPUT

CODEBLOCK1

3. APPLY TO PRODUCTION

CODEBLOCK2

4. VERIFY

CODEBLOCK3

Required Locations

• * Schema Sync Script: INLINECODE1
• Documentation: INLINECODE2
• Alembic Migrations (for documentation): INLINECODE3

When to Update Schema

Adding New Tables

1. 1. Define models in INLINECODE4
2. Add table creation logic to INLINECODE5
3. Update docs/deployment/SCHEMA_SYNC_GUIDE.md with new table info
4. Test with --dry-run first
5. Apply to dev, staging, then production

Adding New Columns

1. 1. Update model in INLINECODE8
2. Add column check and ADD COLUMN logic to sync script
3. Use IF NOT EXISTS patterns for safety
4. Test with --dry-run first
5. Apply to environments

Safety Checks Built-In

• * ✅ Checks table exists before creating
• ✅ Checks column exists before adding
• ✅ Transaction safety (rollback on error)
• ✅ Dry-run mode to preview changes
• ✅ Color-coded output for easy reading
• ✅ Summary of all changes applied

Integration with CI/CD

Railway Deployment:
CODEBLOCK4

GitHub Actions:
CODEBLOCK5

ENFORCEMENT

• * ❌ NEVER run alembic upgrade head in production
• ❌ NEVER manually execute SQL in production without sync script
• ❌ NEVER skip dry-run step for production changes
• ✅ ALWAYS use scripts/sync-production-schema.py for schema changes
• ✅ ALWAYS run --dry-run before INLINECODE13
• ✅ ALWAYS verify changes in dev/staging before production
• ✅ ALWAYS update documentation when adding new tables/columns

VIOLATION CONSEQUENCES

• * Database schema drift between environments
• Failed deployments from migration conflicts
• Data loss from incorrect migrations
• Production downtime from schema errors
• Team confusion about database state

THIS IS A REQUIRED STANDARD. USE SCHEMA SYNC SCRIPT FOR ALL DATABASE CHANGES.

Reference Files

See references/sync-vs-alembic.md for detailed comparison of sync script vs Alembic migrations.

See references/workflow-examples.md for code examples of adding tables, columns, indexes, and handling complex migrations.

Run scripts/verify-sync-script.sh to validate that sync script exists and is properly configured.