← back to Costa Rica
costa-rica: harden migration runner per Cody gate — TK-10346
7d2eaa7d6c2feda69dab35b3ae4a8479cb89f49b · 2026-08-08 07:42:32 -0700 · Steve
- SQL-escape filenames in the ledger queries (no bare interpolation).
- GUARD bare --baseline behind FORCE=1 (it marks pending files applied WITHOUT running them —
a footgun that could silently skip 008 double-book EXCLUDE / 009 integrity on prod); print
exactly which files would be skipped.
- Add --baseline-through <file> for safe partial adoption on an already-migrated prod DB
(baseline 002-007, apply only 008/009).
- sort -V for numeric ordering (010 after 009, not lexicographic).
- Correct the header comment: only 004/008/009 are BEGIN..COMMIT-wrapped; recovery rests on
idempotent authoring + mark-only-on-clean-exit.
Re-verified on throwaway scratch DBs: happy-path apply=8 idempotent, EXCLUDE constraints land,
baseline guard refuses (exit 3), baseline-through leaves 008/009 pending. Suite 101/101.
Memo §7 now verifies the EXCLUDE artifact post-apply (not just exit code).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
Files touched
M scripts/apply-migrations.sh
Diff
commit 7d2eaa7d6c2feda69dab35b3ae4a8479cb89f49b
Author: Steve <steve@designerwallcoverings.com>
Date: Sat Aug 8 07:42:32 2026 -0700
costa-rica: harden migration runner per Cody gate — TK-10346
- SQL-escape filenames in the ledger queries (no bare interpolation).
- GUARD bare --baseline behind FORCE=1 (it marks pending files applied WITHOUT running them —
a footgun that could silently skip 008 double-book EXCLUDE / 009 integrity on prod); print
exactly which files would be skipped.
- Add --baseline-through <file> for safe partial adoption on an already-migrated prod DB
(baseline 002-007, apply only 008/009).
- sort -V for numeric ordering (010 after 009, not lexicographic).
- Correct the header comment: only 004/008/009 are BEGIN..COMMIT-wrapped; recovery rests on
idempotent authoring + mark-only-on-clean-exit.
Re-verified on throwaway scratch DBs: happy-path apply=8 idempotent, EXCLUDE constraints land,
baseline guard refuses (exit 3), baseline-through leaves 008/009 pending. Suite 101/101.
Memo §7 now verifies the EXCLUDE artifact post-apply (not just exit code).
Co-Authored-By: Claude Opus 4.8 (1M context) <noreply@anthropic.com>
---
scripts/apply-migrations.sh | 68 +++++++++++++++++++++++++++++++--------------
1 file changed, 47 insertions(+), 21 deletions(-)
diff --git a/scripts/apply-migrations.sh b/scripts/apply-migrations.sh
index 106467a..bbab377 100755
--- a/scripts/apply-migrations.sh
+++ b/scripts/apply-migrations.sh
@@ -1,23 +1,29 @@
#!/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).
+# 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 --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
+# 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)
#
-# 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.
+# 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)
@@ -27,12 +33,28 @@ PSQL=(psql "$DATABASE_URL" -v ON_ERROR_STOP=1 -qtA)
applied_at timestamptz NOT NULL DEFAULT now()
);" >/dev/null
-applied() { "${PSQL[@]}" -c "SELECT 1 FROM schema_migrations WHERE filename = '$1' LIMIT 1;"; }
+# 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 order by numeric prefix (migrate_002_... < migrate_003_... < ...)
-IFS=$'\n' files=($(printf '%s\n' "${files[@]}" | sort)); unset IFS
+# 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
@@ -44,22 +66,26 @@ for f in "${files[@]}"; do
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" ;;
+ --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
- "${PSQL[@]}" -c "INSERT INTO schema_migrations(filename) VALUES ('$base') ON CONFLICT DO NOTHING;" >/dev/null
- done=$((done+1))
- echo " ✓ applied $base" ;;
+ 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)";;
- apply) echo "— done: $done applied, $(( ${#files[@]} - done )) already present";;
+ --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
← c25de8a costa-rica: add idempotent ordered migration runner scripts/
·
back to Costa Rica
·
costa-rica: /yoloforever cycle 1 ledger — TK-10346 c67be91 →