Automated database migrations on deploy

Problem

Database migrations are not automatically applied during deployment. When new code that depends on schema changes is deployed, the app crashes because the database schema is out of sync.

Recent incident: Deployed code with run_number column but migration wasn't run, causing production outage.

Requirements

• Migrations must run automatically on every deploy
• Must be idempotent (safe to run multiple times)
• Must fail fast - if migration fails, app shouldn't start
• Must track which migrations have already been applied
• Must run in order (by filename: 0001, 0002, etc.)
• Must work with Docker Compose deployment
• Must be robust for multiple deploys per day

Solution: Startup Migration Runner

1. Migration Tracking Table

CREATE TABLE IF NOT EXISTS schema_migrations (
  version VARCHAR(255) PRIMARY KEY,
  applied_at TIMESTAMPTZ NOT NULL DEFAULT NOW()
);

2. Migration Runner Script (packages/db/src/migrate.ts)

Node.js script that:

• Connects to PostgreSQL
• Creates schema_migrations table if not exists
• Reads all .sql files from drizzle/migrations/
• Checks which have already been applied
• Runs pending migrations in order
• Records each successful migration
• Exits with error code if any migration fails

3. Docker Entrypoint

Modify dashboard Dockerfile to use entrypoint script that:

1. Runs migration script
2. If successful, starts the app
3. If migration fails, exits with error (container won't be healthy)

COPY --from=builder /app/packages/db/dist/migrate.js ./migrate.js
COPY --from=builder /app/packages/db/drizzle/migrations ./migrations

ENTRYPOINT ["./entrypoint.sh"]
CMD ["node", "dashboard/server.js"]

entrypoint.sh:

#!/bin/sh
set -e
echo "Running database migrations..."
node migrate.js
echo "Migrations complete, starting app..."
exec "$@"

4. File Structure

packages/db/
├── src/
│   ├── migrate.ts      # Migration runner
│   └── ...
├── drizzle/
│   └── migrations/
│       ├── 0001_add_parent_run_fk.sql
│       ├── 0002_pgboss_integration.sql
│       └── ...
└── entrypoint.sh       # Docker entrypoint

Implementation Steps

1. [ ] Create schema_migrations table migration (0000_schema_migrations.sql)
2. [ ] Create packages/db/src/migrate.ts script
3. [ ] Add build step for migrate.ts in packages/db
4. [ ] Create packages/db/entrypoint.sh
5. [ ] Update dashboard/Dockerfile to copy migrations and use entrypoint
6. [ ] Test locally with Docker Compose
7. [ ] Deploy and verify

Testing

• Run docker compose down -v && docker compose up - should apply all migrations
• Run docker compose restart dashboard - should skip already-applied migrations
• Add a new migration file - should apply only the new one on restart

Acceptance Criteria

• [ ] New deployments automatically run pending migrations
• [ ] Already-applied migrations are skipped
• [ ] Failed migrations prevent app from starting
• [ ] Migration status is logged
• [ ] Works with docker compose up -d --build