POSTGRESQL / SOURCE INTEGRITY
Migration checksum mismatches
A migration checksum mismatch means the recorded file bytes and the current file bytes differ. It does not tell you whether a live table or index changed. Schema Migrator snapshots compare SQL files by path and SHA-256, keeping this question separate from catalog drift.
Reproduce a changed-file comparison without a database
Run this standard-library Python example in a temporary directory. It keeps the original SQL unchanged and adds a comment to a separate copy, so it demonstrates a mismatch without modifying an active migration.
python3 - <<'PY'
from hashlib import sha256
from pathlib import Path
from tempfile import TemporaryDirectory
original = b"create table observations (id bigint primary key);\n"
with TemporaryDirectory() as directory:
before = Path(directory) / "before.sql"
after = Path(directory) / "after.sql"
before.write_bytes(original)
after.write_bytes(original + b"-- review note\n")
base_hash = sha256(before.read_bytes()).hexdigest()
compare_hash = sha256(after.read_bytes()).hexdigest()
print("base_sha256:", base_hash)
print("compare_sha256:", compare_hash)
print("changed:", base_hash != compare_hash)
PYBoth files are disposable inputs. The comment changes bytes even though it does not change the table definition.
base_sha256: 750a67cd5c8cfcb2214b3ab0e3e68c7fd579221b005c70b9ca76597188db5b53
compare_sha256: 056db53ecbcb355def021845147df816ff383ace88b5664099dfaf06fd7e22bf
changed: TrueThese are actual SHA-256 values for the synthetic inputs above, not values from a migration run or database.
Read the path and both checksums
Snapshot comparison matches files by path. A path present only in the newer snapshot is added. One present only in the base is removed. A path present in both is changed when its SHA-256 differs. Unchanged paths do not produce diff items.
| Path | Base snapshot | Comparison snapshot | Diff category |
|---|---|---|---|
| tables/001_observations.sql | Original bytes | Same bytes plus a comment | changed |
| indexes/002_observed_at.sql | Absent | New file | added |
| views/003_review.sql | File present | Absent | removed |
- Keep the full before and after checksums and the original source available to the reviewer.
- A rename can appear as one removed path and one added path. The diff categorizes paths. It does not infer a semantic rename.
- Whitespace, line endings and comments can change a file checksum without changing database semantics. Inspect the actual diff before deciding on a correction.
Resolve the discrepancy without erasing evidence
First check which source version was reviewed and recorded. If a recorded migration was accidentally edited, restore the reviewed bytes from version control. If the intended schema needs to change, add a new ordered migration rather than rewriting the old record.
- For a file that has not been applied, follow the project review process and record the new reviewed version before execution.
- Do not replace a recorded checksum solely to make the mismatch disappear. That removes the evidence you need to understand the discrepancy.
- If the concern is the live database, perform catalog drift inspection separately. A file can change while the database stays the same, or the database can change while files stay identical.
Source code
The implementation behind the examples. Examples are educational inputs, not production records or measured results.
- SQL-file snapshot comparison
Diff items are added, removed or changed, with before and after SHA-256 values.
- Migration planning
Ordered SQL inputs remain separate from snapshot comparison.