`scripts/apply-migrations.sh`

92 lines
#!/usr/bin/env bash
# costa-rica — ordered, idempotent, logged migration runner.
# TK-10346. Closes the "manual go-live pass skips a migration" gap: applies every
# scripts/migrate_*.sql in ascending numeric order (sort -V), records each in a
# schema_migrations ledger, and is SAFE TO RE-RUN (already-applied files are skipped).
#
# Usage:
#   DATABASE_URL=postgresql:///costa_rica_directory?host=/tmp  scripts/apply-migrations.sh          # apply pending
#   DATABASE_URL=...  scripts/apply-migrations.sh --dry-run                 # show what WOULD apply, touch nothing
#   DATABASE_URL=...  scripts/apply-migrations.sh --status                  # list applied vs pending
#   DATABASE_URL=...  scripts/apply-migrations.sh --baseline-through <file> # mark pending files <= <file> as applied
#                                                                           #   WITHOUT running them (adopt on a DB
#                                                                           #   already migrated up to <file>)
#   DATABASE_URL=...  FORCE=1 scripts/apply-migrations.sh --baseline        # mark ALL pending as applied w/o running
#                                                                           #   (DANGEROUS — see the guard below)
#
# TRANSACTIONS: 004/008/009 are wrapped in BEGIN..COMMIT (atomic). 002/003/005/006/007 are
# NOT wrapped, but every migration is idempotent-authored (CREATE ... IF NOT EXISTS /
# DROP CONSTRAINT IF EXISTS), and the ledger is marked ONLY after a clean psql exit — so a
# partial failure leaves the file PENDING and a re-run recovers. ON_ERROR_STOP aborts on error.
set -euo pipefail

: "${DATABASE_URL:?set DATABASE_URL (e.g. postgresql:///costa_rica_directory?host=/tmp)}"
cd "$(dirname "$0")/.."
MODE="${1:-apply}"
THROUGH="${2:-}"

PSQL=(psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -qtA)

# Ledger — records which migration files have been applied.
"${PSQL[@]}" -c "CREATE TABLE IF NOT EXISTS schema_migrations (
  filename   text PRIMARY KEY,
  applied_at timestamptz NOT NULL DEFAULT now()
);" >/dev/null

# SQL-safe: double any single quote in the filename before interpolation (works with psql -c,
# unlike :'var' which psql does not interpolate in a -c command string).
sqlq()    { printf "%s" "${1//\'/\'\'}"; }
applied() { "${PSQL[@]}" -c "SELECT 1 FROM schema_migrations WHERE filename = '$(sqlq "$1")' LIMIT 1;"; }
mark()    { "${PSQL[@]}" -c "INSERT INTO schema_migrations(filename) VALUES ('$(sqlq "$1")') ON CONFLICT DO NOTHING;" >/dev/null; }

files=(scripts/migrate_*.sql)
[ -e "${files[0]}" ] || { echo "no scripts/migrate_*.sql found"; exit 1; }
# ascending numeric order (sort -V: 010 sorts after 009, unlike lexicographic sort)
IFS=$'\n' files=($(printf '%s\n' "${files[@]}" | sort -V)); unset IFS

# --baseline footgun guard: bare --baseline on a prod DB missing a critical migration would
# silently mark it applied WITHOUT running it (e.g. 008 double-book EXCLUDE / 009 integrity)
# → prod thinks it is protected but is not. Require FORCE=1 and print exactly what gets skipped.
if [ "$MODE" = "--baseline" ] && [ "${FORCE:-0}" != "1" ]; then
  echo "REFUSING bare --baseline: it marks EVERY pending file as applied WITHOUT running it." >&2
  echo "Pending files that would be skipped-without-running:" >&2
  for f in "${files[@]}"; do b="$(basename "$f")"; [ -z "$(applied "$b")" ] && echo "    $b" >&2; done
  echo "If a DB is truly migrated only THROUGH some file, use:  --baseline-through <that-file>" >&2
  echo "To force full baseline anyway (you accept the above are NOT executed):  FORCE=1 ... --baseline" >&2
  exit 3
fi

pending=0 done=0
for f in "${files[@]}"; do
  base="$(basename "$f")"
  if [ -n "$(applied "$base")" ]; then
    [ "$MODE" = "--status" ] && echo "  applied  $base"
    continue
  fi
  pending=$((pending+1))
  case "$MODE" in
    --status|--dry-run) echo "  PENDING  $base" ;;
    --baseline) mark "$base"; echo "  baselined (NOT run)  $base" ;;
    --baseline-through)
      [ -n "$THROUGH" ] || { echo "usage: --baseline-through <filename>" >&2; exit 2; }
      if [[ "$base" < "$THROUGH" || "$base" == "$THROUGH" ]]; then
        mark "$base"; echo "  baselined (NOT run)  $base"
      else
        echo "  left PENDING  $base"; pending=$((pending))  # remains pending, will need apply
      fi ;;
    apply)
      echo "  applying  $base ..."
      "${PSQL[@]}" -f "$f" >/dev/null
      mark "$base"; done=$((done+1)); echo "  ✓ applied $base" ;;
    *) echo "unknown mode: $MODE"; exit 2 ;;
  esac
done

case "$MODE" in
  --status)            echo "— $pending pending, $(( ${#files[@]} - pending )) applied";;
  --dry-run)           echo "— dry-run: $pending would apply, 0 changed";;
  --baseline)          echo "— baselined $pending file(s) as applied (none executed)";;
  --baseline-through)  echo "— baselined through $THROUGH (none executed); run 'apply' for the rest";;
  apply)               echo "— done: $done applied, $(( ${#files[@]} - done )) already present";;
esac