Record each migration's checksum in the same transaction as its DDL

The answer. A migration runner is trustworthy when three things hold: it is idempotent (re-running applies nothing), atomic per file (never half-applied, never falsely recorded), and it notices edits to files that already ran. Plain psql gives you all three with a schema_migrations table that stores each file's SHA-256. Apply with psql -1 (--single-transaction) and pass the migration file and the ledger insert to the same invocation, so the insert commits with the DDL or rolls back with it. Before applying, read the whole ledger and refuse if any recorded checksum differs from the file on disk; after applying, re-read it and require an exact match with the repository set. Never run raw psql -f against that database; the ledger is only true while the script is its only writer.

The pattern.

#!/usr/bin/env bash
set -euo pipefail
: "${DB_URL:?}"; DIR=migrations; export LC_ALL=C          # bytewise filename order
psql "$DB_URL" -X -q -v ON_ERROR_STOP=1 -c "create table if not exists public.schema_migrations (
  filename text primary key, applied_at timestamptz not null default now(),
  checksum_sha256 text check (checksum_sha256 ~ '^[0-9a-f]{64}$'))"
ledger=$(psql "$DB_URL" -X -t -A -c "select filename||'|'||checksum_sha256 from public.schema_migrations")
for f in "$DIR"/*.sql; do
  name=$(basename "$f"); sum=$(shasum -a 256 "$f" | awk '{print $1}')
  case "$(awk -F'|' -v n="$name" '$1==n{print $2}' <<<"$ledger")" in
    "")     ;;                                            # not yet applied
    "$sum") continue ;;                                   # applied, unchanged
    *)      echo "checksum drift: $name" >&2; exit 1 ;;   # edited after it ran
  esac
  psql "$DB_URL" -X -v ON_ERROR_STOP=1 -1 -v n="$name" -v s="$sum" -f "$f" -f - <<'SQL'
insert into public.schema_migrations (filename, checksum_sha256) values (:'n', :'s');
SQL
done

The receipt. In our product (HarperFlow), this script has been the only path into the database since 2026-05-19; the tracking table itself arrived as migration 0104_schema_migrations, and legacy rows without a checksum are back-filled only behind an explicit opt-in flag. The repository holds 171 migration files, numbered up to 0246, and one runner targets local dev, the original self-hosted instance, release-staging and production. The most recent production apply verified on 2026-08-07 (migration 0244, over the session pooler — see session-pooler-5432-for-ddl-direct-host-ipv6-only) ended with the ledger check: every filename and file-byte SHA-256 in schema_migrations equal to the repository set. How often the drift check has fired isn't recorded; the transcript of each run is the release evidence.

Watch out.

Filenames must be canonical (NNNN_snake_case.sql) and sorted under LC_ALL=C, or two machines apply in different orders.
Never print the connection URL; it usually embeds the password and the log is the artifact you keep.
A check or not null constraint that validates existing rows fails the whole transaction if one row violates it. Count the violators read-only first, then apply.