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!)

# Show what would change WITHOUT applying
python scripts/sync-production-schema.py --dry-run

2. REVIEW OUTPUT

# Output shows:
# ✓ Tables/columns that already exist (skipped)
# ℹ New tables/columns that would be created
# ⚠ Any potential issues

3. APPLY TO PRODUCTION

# Apply changes to production database
export DATABASE_URL="postgresql://..."
python scripts/sync-production-schema.py --apply

4. VERIFY

# Connect and verify schema
psql "$DATABASE_URL" -c "\dt"  # List tables
psql "$DATABASE_URL" -c "\d table_name"  # Describe table

Required Locations

• •Schema Sync Script: /Users/aideveloper/core/scripts/sync-production-schema.py
• •Documentation: /Users/aideveloper/core/docs/deployment/SCHEMA_SYNC_GUIDE.md
• •Alembic Migrations (for documentation): /Users/aideveloper/core/src/backend/alembic/versions/

When to Update Schema

Adding New Tables

1. •Define models in src/backend/app/models/
2. •Add table creation logic to scripts/sync-production-schema.py
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. •Update model in src/backend/app/models/
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:

# In Procfile or deploy script
release: python scripts/sync-production-schema.py --apply

GitHub Actions:

- name: Sync Production Schema
  env:
    DATABASE_URL: ${{ secrets.DATABASE_URL }}
  run: python scripts/sync-production-schema.py --apply

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 --apply
• •✅ 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.