[object Object]

← back to Costa Rica

costa-rica: add idempotent ordered migration runner scripts/apply-migrations.sh — TK-10346

c25de8af882a515fa0c08e272da53a345a4083ed · 2026-08-08 07:11:20 -0700 · Steve

Closes the manual-skip gap permanently: applies scripts/migrate_*.sql in ascending order,
tracks applied files in a schema_migrations ledger, safe to re-run (--status/--dry-run/--baseline
modes). All migrations are idempotent-authored (IF NOT EXISTS / DROP CONSTRAINT IF EXISTS) so a
re-run no-ops existing objects. Verified end-to-end against a throwaway scratch DB: all 8 migrations
apply in order from schema.sql, double-book EXCLUDE constraints created, re-run = 0 changed, scratch
DB dropped clean. Prod-apply commands drafted into the go-live memo (gated).

Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>

Files touched

Diff

commit c25de8af882a515fa0c08e272da53a345a4083ed
Author: Steve <steve@designerwallcoverings.com>
Date:   Sat Aug 8 07:11:20 2026 -0700

    costa-rica: add idempotent ordered migration runner scripts/apply-migrations.sh — TK-10346
    
    Closes the manual-skip gap permanently: applies scripts/migrate_*.sql in ascending order,
    tracks applied files in a schema_migrations ledger, safe to re-run (--status/--dry-run/--baseline
    modes). All migrations are idempotent-authored (IF NOT EXISTS / DROP CONSTRAINT IF EXISTS) so a
    re-run no-ops existing objects. Verified end-to-end against a throwaway scratch DB: all 8 migrations
    apply in order from schema.sql, double-book EXCLUDE constraints created, re-run = 0 changed, scratch
    DB dropped clean. Prod-apply commands drafted into the go-live memo (gated).
    
    Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
 scripts/apply-migrations.sh | 65 +++++++++++++++++++++++++++++++++++++++++++++
 1 file changed, 65 insertions(+)

diff --git a/scripts/apply-migrations.sh b/scripts/apply-migrations.sh
new file mode 100755
index 0000000..106467a
--- /dev/null
+++ b/scripts/apply-migrations.sh
@@ -0,0 +1,65 @@
+#!/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, records each in a schema_migrations
+# ledger, and is SAFE TO RE-RUN (already-applied files are skipped, not re-executed).
+#
+# 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 --baseline    # mark ALL as applied WITHOUT running
+#                                                               #   (adopt the runner on an already-migrated DB)
+#   DATABASE_URL=...  scripts/apply-migrations.sh --status      # list applied vs pending
+#
+# Each migrate_*.sql is already wrapped in BEGIN..COMMIT; the runner additionally uses
+# ON_ERROR_STOP so a failure aborts that file cleanly and the ledger is not marked.
+set -euo pipefail
+
+: "${DATABASE_URL:?set DATABASE_URL (e.g. postgresql:///costa_rica_directory?host=/tmp)}"
+cd "$(dirname "$0")/.."
+MODE="${1:-apply}"
+
+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
+
+applied() { "${PSQL[@]}" -c "SELECT 1 FROM schema_migrations WHERE filename = '$1' LIMIT 1;"; }
+
+files=(scripts/migrate_*.sql)
+[ -e "${files[0]}" ] || { echo "no scripts/migrate_*.sql found"; exit 1; }
+# ascending order by numeric prefix (migrate_002_... < migrate_003_... < ...)
+IFS=$'\n' files=($(printf '%s\n' "${files[@]}" | sort)); unset IFS
+
+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)
+      "${PSQL[@]}" -c "INSERT INTO schema_migrations(filename) VALUES ('$base') ON CONFLICT DO NOTHING;" >/dev/null
+      echo "  baselined (not run)  $base" ;;
+    apply)
+      echo "  applying  $base ..."
+      "${PSQL[@]}" -f "$f" >/dev/null
+      "${PSQL[@]}" -c "INSERT INTO schema_migrations(filename) VALUES ('$base') ON CONFLICT DO NOTHING;" >/dev/null
+      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)";;
+  apply)      echo "— done: $done applied, $(( ${#files[@]} - done )) already present";;
+esac

← 3f07d61 costa-rica: move root migration files into scripts/ so the m  ·  back to Costa Rica  ·  costa-rica: harden migration runner per Cody gate — TK-10346 7d2eaa7 →