Lone Badger Tech

Hash certification

How bsm proves two databases got identical changes in the same order.

Script hash

Each change's script hash is SHA-256 over its name, dependencies, flags, and the text of deploy, revert and verify, each part length-prefixed so text cannot move between scripts without changing the hash. Before hashing, a UTF-8 byte-order mark is removed and CRLF/CR become LF: a Windows checkout and a macOS checkout of the same commit hash identically. Every other difference, including whitespace, changes the hash.

Chain id

Each change also gets a chain id: SHA-256 of the provider, the change name, its script hash and the chain id of the change before it. Like a git commit id, it depends on everything earlier. The chain id of the last change is the plan head (bsm plan show).

When a database deploys a change, it records a chain id computed from its own deploy order. If the database received a prefix of the plan, in plan order, with identical scripts, its head equals the plan's chain id at that position, and bsm status reports it as certified. Two databases with the same head received byte-identical history.

Rendered hash

Scripts can contain variables that differ per environment, so the SQL that runs in test and prod is not the same text. Badger SQL Migrate therefore stores a second hash per change: SHA-256 of deploy and verify as rendered for that environment, plus one for the rendered revert. That separates "same change" (chain id, equal across environments) from "same SQL executed here" (rendered hash). bsm verify reports changes whose variables now render differently; revert refuses to run a revert script that no longer matches.

Schema fingerprint

After each run bsm records a fingerprint: a hash of a canonical, sorted description of the schema (tables, columns, types, defaults, indexes, keys, constraints, module definitions, types, sequences, synonyms). System-generated constraint names, which differ from server to server, are masked; the bsm journal schema is excluded. bsm verify compares the live schema with the last fingerprint, so a hotfix made outside bsm shows up as drift.

What status tells you

StatusMeaning
up to date and certifiedEvery planned change applied, in plan order, with identical scripts.
applied, not in plan orderSame changes, different order (out-of-order deploys on a shared database).
edited after deployThe working copy's scripts differ from what this database ran.
deployed but not in planAn orphan: removed from the plan, or from another branch. It can still be reverted.

Releases of bsm itself publish SHA-256 sums for every download, so the tool can be verified the same way.