`scripts/migrate.sh`

43 lines
#!/usr/bin/env bash
# Idempotent DB migration: brings the target database up to db/schema.sql.
#
# Wired into the deploy as BUILD_CMD (.deploy.conf), so it runs on the remote
# AFTER rsync and BEFORE pm2 reload — code is never shipped ahead of its schema
# again (the 2026-08-03 regression: `products.suppressed` + affiliate_settings /
# suppress_rules existed in code but not on the prod DB, so /shop 500'd).
#
# schema.sql is fully idempotent (CREATE TABLE/INDEX IF NOT EXISTS + explicit
# ADD COLUMN IF NOT EXISTS guards), so this is safe to run repeatedly and on a
# fresh OR a drifted database. ON_ERROR_STOP=1 makes a bad migration abort the
# deploy instead of half-applying.
set -euo pipefail
cd "$(dirname "$0")/.."

# Fail with a clear, actionable message rather than a bare "command not found" if
# the deploy host has no psql (postgresql-client not installed).
command -v psql >/dev/null 2>&1 || {
  echo "✗ psql not found on PATH — install postgresql-client on the deploy host" >&2
  exit 1
}

# The deploy excludes .env from rsync, so the remote keeps its own. Load it to
# get DATABASE_URL (never printed).
set -a; [ -f .env ] && . ./.env; set +a
: "${DATABASE_URL:?DATABASE_URL not set — need it in .env to migrate}"

echo "── db migrate: applying db/schema.sql ──"
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -f db/schema.sql
echo "✓ schema applied"

# Post-migration assertion: don't just trust that schema.sql ran — prove the exact
# three things whose absence 500'd /shop on 2026-08-03 are now queryable. LIMIT 0
# touches the schema, not the rows, so it's instant; ON_ERROR_STOP turns any
# missing column/table into a non-zero exit that aborts the deploy BEFORE pm2
# reloads onto a still-broken DB. This is what makes "green" mean "actually works".
echo "── verify: required schema present ──"
psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -q \
  -c 'SELECT suppressed FROM products LIMIT 0;' \
  -c 'SELECT 1 FROM affiliate_settings LIMIT 0;' \
  -c 'SELECT 1 FROM suppress_rules LIMIT 0;' >/dev/null
echo "✓ schema verified — products.suppressed + affiliate_settings + suppress_rules all present"