Owner: Platform Engineering | Review: On each migration toolchain change

How migrations work

SmartPRO uses Drizzle ORM with a custom journal system. Every schema change goes through three artifacts:

Adding a new migration

Edit schema.ts

Add columns/tables to `drizzle/schema.ts`. This is the source of truth — the drift guard compares against it.

Write the forward migration

Create `drizzle/NNNN_description.sql` (next sequential number). Use `ALTER TABLE ... ADD COLUMN IF NOT EXISTS` so re-runs are safe.
```sql
ALTER TABLE demo_bookings
  ADD COLUMN IF NOT EXISTS my_column VARCHAR(255) NULL;
```

Update the 0070 baseline

Add the column to the matching `CREATE TABLE IF NOT EXISTS` block in `drizzle/0070_drizzle_baseline_schema_recovery.sql`. This keeps the drift guard green.

Add a journal entry

Append to `drizzle/meta/_journal.json`:
```json
{
  "idx": 162,
  "version": "7",
  "when": 1750000000000,
  "tag": "NNNN_description",
  "breakpoints": true
}
```

Add bootstrap index (if needed)

If you added an index, add it to `drizzle/bootstrap/0070_indexes.sql` as `CREATE INDEX IF NOT EXISTS ...`.

Run tests

```bash
pnpm test
```
The drift guard (`drizzle/schema.test.ts`) will catch any mismatch between `schema.ts` and the 0070 baseline.

Applying migrations (production)

Preview first, then apply (the runner is tsx scripts/migrate.ts, invoked via pnpm):

DATABASE_URL="mysql://user:pass@host:port/db" pnpm db:migrate:dry   # list pending, no changes
DATABASE_URL="mysql://user:pass@host:port/db" pnpm db:migrate       # apply in journal order

The runner tracks applied migrations in __drizzle_migrations by SHA-256 content hash (not name or timestamp), so it skips anything already applied and is safe to re-run.

Verify with:

SELECT COUNT(*) FROM __drizzle_migrations;   -- increased by the number of pending migrations
SHOW COLUMNS FROM demo_bookings;             -- spot-check the new columns landed

pnpm db:push is disabled by design — it bypasses the migration chain. Never use it.

The drift guards

Two independent guards keep the schema honest:

• CI canary (.github/workflows/ci.yml → migrate-canary): spins up a fresh mysql:8.4, runs pnpm db:migrate, and asserts the applied count matches the SQL files — catches a SQL file added without a journal entry before merge.
• Runtime drift guard (server/schemaDriftGuard.ts): at startup, compares information_schema against the Drizzle schema and logs any missing tables/columns/indexes (non-fatal). Disable with DISABLE_SCHEMA_DRIFT_GUARD=1.

Rollback

Migrations are additive (no DROP statements). Rolling back means: 1. Deploy the old code (it won't use the new columns) 2. Leave the columns in place — they're harmless 3. If truly necessary, write a new migration to drop them (rare)