I Built a Ledger Because Verifying Looked Too Expensive

My first design for tracking applied database changes was a ledger, and I threw it away.

The problem

Application code for one ticket went live, and its matching database change script was not applied until nine days later. In those nine days 6,195 archived rows across roughly 620 accounts went bad. The ordering was documented and understood. The second half simply never got done, and nothing was counting the days.

Our database changes are applied by hand against a shared production SQL Server, with no test instance and no migration runner. So the question was how to know which scripts had actually been applied.

The belief

One of the outside reviewers argued that a verification query per script was “too expensive and is likely to become neglected”. That argument carried the design toward a ledger of claimed actions: a table, a logging procedure, an append-only trigger and an execution wrapper that records every apply. The ledger was written and reviewed.

What the triage said

Then all 18 existing change script folders were triaged against the live database. 17 of the 18 were settled with a one-line catalog check: does this column exist, does this index exist. Writing all of those checks took about twenty minutes in total. The one awkward case was a data backfill with no schema footprint, and a cheap grouped count over a 363 row table settled that too.

That destroyed the argument that per-script verification was too expensive. Both reviewers were given the evidence, and each reversed its own earlier recommendation and chose verification only. The ledger was thrown away.

The triage also showed the hand typed index of applied scripts was wrong in both directions. It had one folder marked “(pending apply)” since May while that index had been live on production the whole time, and it left out 8 of the 18 folders.

Where the checks were wrong too

Checking whether a procedure change landed by searching the live procedure for its old filter string returned a match, because the match sat inside the header comment of the very ALTER that removes it. The check reported the opposite of the truth. For a procedure change, the reliable signal turned out to be sys.objects.modify_date across every affected object, not text.

A cross database check using OBJECT_DEFINITION returned NULL, because it only resolves an object id in the current database. That reported three applied changes as missing until the tool was actually run.

What shipped

Those 18 folders hold 30 scripts, and all 30 were enrolled. A manifest per change script carrying its verify query, a generator that writes the manifest by reading the SQL, and a checker that runs the queries against the live database. Run live against production: 30 checked, 26 verified applied, 4 known exceptions with owners and expiry dates. A full pass took 1.7s, catalog views only. The first live run also showed that 14 of the 363 rows in the meet results table had no meet date. That is a question about the save path and wants its own look.

What was not verified

The nightly run is not scheduled yet, so today the checker works on demand. Verification only sees the end state, so a script someone tried and abandoned looks the same as one nobody touched. The question of who ran what and when belongs in a separate deploy event log, which is not designed.

Try this tomorrow

Take the last change you applied to production that altered a stored procedure. Check it two ways against the live database: compare the procedure’s sys.objects.modify_date to the time you applied it, and search its body for the text the change removed. If the text search finds the old text sitting in a comment while the date says the change landed, you have just seen why a text match is not proof, and you have a verify query you can keep. Time how long that took you.

dxdev LESSON · paste into your AI coding agent
LESSON: Verify Deployment State From Production Evidence, Not Hand Typed Status Files
SOURCE: dxdev.com/blog/2026-09-07_verify-deployment-not-guess
WHAT HAPPENED: A code change shipped and its matching database script was not applied for nine days, so thousands of archived rows went bad. I believed verifying each script against the live database would be too expensive and neglected, so I built a ledger of claimed applies: a table, a logging procedure, a trigger and a wrapper. Before applying it I triaged all 18 existing script folders, and 17 were settled with a one-line catalog check in about twenty minutes. The ledger was thrown away. Two of my own checks were wrong too: a text search matched inside a header comment, and a function that only resolves objects in the current database returned NULL. What shipped was a manifest per script with a verify query, run live against production: 30 checked, 26 verified applied, 4 known exceptions.
THE RULE: Treat production deployment status as evidence that must be generated or verified from production, not as a human maintained assertion. When a question has a live source of truth, ask it instead of keeping a typed copy of the answer.
CHECK MY CODE, then report PASS or FAIL with file:line for each:
      1. Any file that claims which database changes have been applied to production is generated or checked from the live database, not typed by hand.
      2. Every database change has something that confirms it was actually applied after the matching application code ships.
      3. A question with a live source of truth is answered by asking that source, not by a typed copy of the answer.
THEN PRINT: a table (check, PASS/FAIL, evidence, fix) + a verdict (applies / partially / OUT_OF_SCOPE / no) + the single most important next action.