I was asked a reasonable question about a production Rails application: is the database following ACID, is it normalized where it should be, is currency handled correctly, and are there deadlocks?

The audit covered 185 tables, 447 foreign keys, 262 unique indexes and 128 transaction blocks. It produced 22 findings and nine pull requests.

The part worth writing down is not the findings. It is that three of them were wrong about their own cause, and that when I went back and reviewed my own merged fixes adversarially, I found two more real bugs plus a guard I had written as a comment instead of a test.

This post is the whole thing: the concepts from the ground up, the specific things that were broken, why the obvious fix was wrong more than once, and the method that caught it. If you are new to databases, the concept sections stand alone. If you have shipped this stuff for years, the failure modes in parts 3 and 4 are the ones I would want to read.

The method, first

An audit that reads code and reports impressions is worth very little. The findings that mattered all came from the same loop:

1. Trace the actual write path. Not "this looks unsafe" — which callers reach this, in what order, holding what.
2. Reproduce it. If you claim a deadlock, produce a deadlock. Two threads, two connections, a loop.
3. Fix it.
4. Neuter the fix and confirm the test fails. A test that passes against broken code is not a test.
5. Re-run the reproduction.

Step 4 is the one people skip, and it is the one that keeps you honest. Every guard in this audit was verified by reverting the fix underneath it. Twice, that revealed the test had been passing for the wrong reason.

A related discipline: measure before you believe a count. Early on I wrote a grep to find transaction blocks and it reported 2. There were 148. The pattern [^\w.]transaction — written to "avoid matching the dot" — threw out every ActiveRecord::Base.transaction do, which was 110 of them. A scan that finds nothing passes every check you run against it.

Part 1 — ACID

What ACID actually means

Four guarantees a database gives you about a transaction — a group of statements you want treated as one unit.

Atomicity. All of it happens, or none of it. If you debit one account and credit another, you never get the debit alone.

Consistency. The database moves from one valid state to another. "Valid" means your constraints hold — foreign keys, uniqueness, CHECK constraints. Note what this does not mean: it is not a promise your business logic is right. It is a promise the database will refuse to store anything that violates the rules you declared. If you declare no rules, consistency guarantees nothing.

Isolation. Concurrent transactions do not see each other's half-finished work. How strictly is a setting — Postgres defaults to READ COMMITTED, which means each statement sees a fresh snapshot.

Durability. Once it commits, it survives a crash.

Postgres gives you all four. The question an audit asks is not "does Postgres implement ACID" — it does — but "is the application actually using transactions where atomicity matters?" That is entirely on you.

Finding: a state machine with no transaction

The worst thing I found was a payment webhook handler. Roughly:

def call
  subscription.update!(status: "active")
  subscription.update!(metadata: subscription.metadata.merge(...))
  Billing::Referrals.credit(subscription.tenant)
  NotifyService.call(subscription)
end

Four sequential writes, no transaction. If the third raised, the first two were already committed. A subscription could end up active with no credit, or with metadata describing a state it was not in. Payment webhooks retry, so this ran again — landing in a different partial state each time.

The fix is to wrap it. But wrapping it introduced a subtler bug, which is the part actually worth teaching.

The rescue that does not rescue

This code shipped inside the new transaction:

ActiveRecord::Base.transaction do
  record_delivery!(sub)
  sub.lock!
  dispatch!(sub)

  begin
    optional_side_effect!(sub)   # writes to the database
  rescue StandardError => e
    Rails.logger.warn(e)         # "best effort, don't fail the whole thing"
  end
end

That rescue looks like it contains the failure. Inside a transaction, it contains nothing.

In Postgres, when a statement fails, the entire transaction enters an aborted state. Every subsequent statement returns:

ERROR:  current transaction is aborted, commands ignored until end of
        transaction block

Rescuing the Ruby exception does not un-abort the Postgres transaction. Your handler swallows the error, execution continues, and every write after that point silently fails until something finally raises at COMMIT.

WITHOUT a savepoint                    WITH a savepoint
───────────────────────                ───────────────────────
BEGIN                                  BEGIN
  INSERT …            ok                 INSERT …            ok
  UPDATE …            ok                 UPDATE …            ok
                                         SAVEPOINT sp1
  side effect         ✗ ← aborts           side effect       ✗
    rescue → logged     the whole         ROLLBACK TO sp1    ← healthy again
    execution continues transaction         rescue → logged
                                             (outside the block)
  UPDATE …            ✗ 25P02             UPDATE …           ok
  INSERT …            ✗ 25P02             INSERT …           ok
COMMIT                ✗ raises here     COMMIT               ok
                        far from
                        the cause

Note where the error surfaces without the savepoint: at COMMIT, in a different file, long after the statement that actually caused it.

The fix is a savepoint — a nested transaction Postgres can roll back to without discarding the outer one:

begin
  ActiveRecord::Base.transaction(requires_new: true) do
    optional_side_effect!(sub)
  end
rescue StandardError => e
  Rails.logger.warn(e)   # rescue OUTSIDE the savepoint block
end

The rescue must sit outside the savepoint, or you roll back and then keep writing inside a block that is about to be discarded.

How I nearly shipped a test that proved nothing. My first spec raised a plain StandardError inside the savepoint and asserted the outer transaction survived. It passed. It also passed with requires_new: true removed — because a Ruby exception never touched Postgres, so the transaction was never poisoned in the first place. The test proved the rescue worked and said nothing about savepoints.

The fix was to fail the way production fails:

ActiveRecord::Base.connection.execute("SELECT 1 / 0")

Now removing requires_new: true fails with PG::InFailedSqlTransaction, which is the whole point.

If you take one thing from this section: a test for error handling must produce the real error. A synthetic exception exercises your rescue and nothing underneath it.

Idempotency: the ledger you need before you need it

Payment gateways deliver at least once. The same checkout.session.completed will arrive twice, and your handler must produce the same end state both times.

There was no ledger. The fix is unglamorous and it is the whole answer:

create_table :payment_events do |t|
  t.string :gateway,  null: false
  t.string :event_id, null: false
  t.datetime :created_at, null: false
end
add_index :payment_events, [:gateway, :event_id], unique: true

Record the delivery first, inside the transaction, before doing anything else. If the insert violates the unique index, this is a replay — return. The uniqueness lives in the database, so two workers processing the same event concurrently cannot both pass the check.

One design note: this table deliberately is not tenant-scoped. It is a gateway-level ledger, and a gateway event id is unique globally, not per customer. Scoping it would have let the same event process once per tenant.

CHECK constraints, and the one that would have caused an outage

185 tables had five CHECK constraints between them, none on money. So the database would happily store a negative quantity, a 3000% discount, or a document whose total did not equal its own subtotal plus tax.

Adding constraints to a live table has two traps.

Trap 1: the lock. ALTER TABLE ... ADD CONSTRAINT takes an ACCESS EXCLUSIVE lock, which blocks everything — including reads — and holds it until COMMIT. My first version issued 29 of them in a single transaction across 11 tables. Deploy ran migrations with lock_timeout = 0, so one blocked ALTER would have waited forever with every other query queued behind it.

Worse than it sounds: in Postgres a blocked lock request queues in front of everyone arriving after it. One ALTER waiting on a long-running SELECT stalls every subsequent query on that table, not just writers.

The fix is three things together:

disable_ddl_transaction!            # each ALTER commits on its own

def change
  with_lock_timeout do             # bounded wait + retry, never infinite
    add_check_constraint :quotes, "tax_pct BETWEEN 0 AND 100",
                         name: "quotes_tax_pct_range",
                         validate: false,        # NOT VALID: no table scan
                         if_not_exists: true     # partial run is re-runnable
  end
end

validate: false emits NOT VALID, which skips the full-table scan. The constraint applies to all new writes immediately; existing rows go unverified until you run VALIDATE CONSTRAINT separately — which takes only a SHARE UPDATE EXCLUSIVE lock and is safe to run live.

Trap 2, and this one nearly caused the outage it was meant to prevent.

The audit said: add total = subtotal + tax as a CHECK. Before writing it, I probed the invariant across 144 realistic combinations of quantity, price, discount and tax rate. 25 of them already violated it.

7 × 10.10 at 33.33% off, 13% VAT
  subtotal 47.14 + tax 6.13 = 53.27      stored total: 53.26

The code stored (subtotal + tax).round(2) as the total while rounding subtotal and tax independently. Those disagree whenever the two roundings break in opposite directions. A customer doing arithmetic on the quote they were sent got a different answer from the total they were asked to pay.

Now, the critical part. A row-level CHECK is re-evaluated on the whole row after ANY update — not just updates touching the constrained columns. And NOT VALID does not exempt an old row from being re-checked the next time anything writes to it.

There was a service that marked a quote as viewed — no recomputation, just a timestamp — and it ran when a customer opened their quote link. Adding that constraint over drifting data would have turned every stale quote into a 500 on a customer-facing page. The constraint would have caused the outage it existed to prevent.

So the order was: fix the arithmetic, ship a read-only drift report, run it against real data, and only then arm the constraint. And when I did arm it, the migration re-runs the drift check itself rather than trusting that someone remembered to:

def up
  guard_against_drift!   # aborts cleanly, changing nothing, naming the counts
  TABLES.each { |t| constraint t, "total = subtotal + tax_amount" }
end

Staging and production hold different data and the migration runs on both. A clean report from whichever environment someone happened to be shelled into proves nothing about the other.

Part 2 — Normalization

The textbook version, briefly

Normalization is about not storing the same fact twice.

• 1NF — one value per cell, no repeating groups.
• 2NF — every non-key column depends on the whole key.
• 3NF — and depends on nothing but the key.

The reason is not tidiness. It is that duplicated facts drift. Store a customer's city on both the customer and every order, and one day they disagree, and now nothing in the system knows which is true.

The frame that is actually useful

In application work the question is almost never "is this 3NF". It is:

Is this value stored or derived — and if it is stored, what maintains it?

A cached total is denormalization. That is fine — it is often necessary. It is only a bug when nothing keeps it current.

The audit flagged several cached totals as unmaintained. Tracing every writer showed they were all recomputed on every path that could change them. The finding was real in theory and dead in practice, so the fix was not a callback that could never fire — it was a reconciliation proof and an architecture spec that fails if a new writer appears without recomputing.

Machinery for an untriggerable bug is a cost with no benefit. Trace before you build.

"Three tables, three rules — pick one"

Line items existed on quotes, orders and invoices. Quotes computed a line total in code; orders and invoices stored it in a column. The finding said: pick one.

Tracing found two rules, both correct, neither written down.

QUOTE                                ORDER  ─────►  INVOICE
─────                                ─────          ───────
#line_total   computed               line_total   stored (a column)
qty × price × (1 − line disc)        … × (1 − order discount)
        │                                    │
   BEFORE the order discount            AFTER it
        │                                    │
        ▼                                    ▼
  the PDF prints                       Σ lines == subtotal
    Subtotal   Σ lines                 (a report SQL-sums this column,
  − Discount   ← exactly the gap        which is the only reason to store it)
  + Tax
  ─────────
  = Total

Collapsing the quote onto the order's rule deletes that discount row's meaning. Collapsing the order onto the quote's rule breaks a report that cannot aggregate a method in SQL. There is no single rule that survives both.

They are different numbers on purpose. The quote PDF prints the lines, then an explicit "Order discount" row worth exactly the difference, then tax. Making the quote post-discount deletes that row's meaning.

And only one has a reason to be stored: a product performance report does SUM(line_total) grouped by product, and you cannot aggregate a Ruby method in SQL. Nothing sums quote line totals — they are only rendered.

So the answer was no column, no migration. Documenting the contract on all three models and pinning it with a spec that fails if anyone flattens it — including a guard that fires if the report stops SQL-summing, because at that point the stored column loses its justification too.

Normalization advice that ignores read patterns is guesswork. "Why is this stored?" has exactly one good answer: because something aggregates it in the database.

Part 3 — Money

Currency is not a number

An amount without a currency is not money, it is a number. This sounds obvious and is violated constantly, because a column called amount reads like a quantity.

The app was built for one country first and sold globally. Roughly a hundred money columns carried a currency suffix in their name — a fossil from when there was only one currency. They now hold the amount in the record's own currency, which is routinely something else entirely. A "4500" on a USD quote means $4,500.

Summing across currencies

Twenty money rollups summed an amount column across whatever rows matched, with no regard for currency. A workspace holding two currencies reported their arithmetic sum — a figure denominated in nothing.

The worst was externally visible: a payout ledger stamps each row with the currency of the record it came from, so a recipient's "total owed" was two currencies added together and shown to someone outside the company.

Two rules came out of fixing all twenty:

1. Only money is scoped. Counts stay currency-agnostic. Filtering a count hides real records from a figure that was already correct. Where a COUNT and a SUM share one query, the condition belongs in a FILTER on each SUM:

SELECT COUNT(*)                                        AS records,
       SUM(amount) FILTER (WHERE currency = 'USD')     AS amount_usd
  FROM orders

2. Filter rather than convert — while you have nothing to convert with. Inventing an FX rate produces a figure that looks authoritative and is wrong by however far the real rate has moved. Filtering is honest about what it does not know.

The bug that filtering created

This is my favourite finding in the audit, because I caused it.

The currency filter resolved to the tenant's current currency. Nothing re-stamped existing rows when a workspace changed its currency in settings. So:

BEFORE — workspace currency: A
  rows      [ A · 100k ] [ A · 100k ] [ A · 100k ]
  filter    WHERE currency = 'A'  ─────────────────►  300,000  ✓

AFTER  — admin picks currency B in Settings → Defaults
  rows      [ A · 100k ] [ A · 100k ] [ A · 100k ]   ← unchanged, untouched
  filter    WHERE currency = 'B'  ─────────────────►        0  ✗
                            ▲
                            └── nothing re-stamped the rows when the
                                parent's currency moved out from under them

Every dashboard, summary and rollup in the workspace read zero. The setting was labelled "Primary currency — governs currency display". It reads as cosmetic. It empties every total in the product.

The instinct is to reach for FX conversion. That would have been wrong, and checking rather than assuming is what showed why:

• There was no per-record currency picker anywhere in the product. Genuinely mixed data could not be entered through the UI.
• The product's own public roadmap listed multi-currency rollups as planned.
• Mixed rows came almost entirely from re-denomination — someone changing their own currency — not from multi-currency selling.

Converting historical amounts is the wrong answer to re-denomination. It rewrites closed records and paid invoices against a rate nobody maintains.

The right answer is that rows carrying the tenant's previous currency were following the tenant, not making a statement. So they follow again. Rows set deliberately keep theirs. Amounts never change — only the label moves.

That reasoning held because of something specific: before this work, nothing ever wrote a currency. Every row fell through to the column default regardless of what the workspace sold in, and no customer ever saw a currency derived from it, because the PDF resolved the workspace currency at render time. Re-stamping was not rewriting history. It was populating a column correctly for the first time.

One carve-out: quotes past draft. A sent quote is the only document a customer has seen denominated, so it keeps what it was issued as. Orders and invoices had no mailer, no PDF and no public route — internal records — so they follow.

On real data this turned out to be 1,091 records across four workspaces invisible in every money rollup.

Rounding: where you round decides whether the document adds up

Two ways to total a set of lines:

three lines of 18.785

  round( Σ x )                     Σ round( x )
  ─────────────                    ─────────────
    18.785                           18.79   ← the line the customer sees
  + 18.785                         + 18.79
  + 18.785                         + 18.79
  ─────────                        ───────
    56.355                           56.37
  round → 56.36                             ✓ matches the printed lines
          ▲
          └── stored as the subtotal, printed beside lines that add to 56.37

They differ. The printed document shows rounded lines, so if you store round(Σ x) as the subtotal, the lines a customer can add up do not match the subtotal you printed next to them.

I fixed this once for the quote's total = subtotal + tax and assumed it propagated downstream, because orders copy that trio wholesale. The trio does travel by copy. The lines do not — an order recomputes and rounds each one. Sweeping 320 combinations found 196 broken.

The fix belongs at the source: round each line to currency precision before summing, so the number the order stores per line is the number the subtotal is built from. Everything downstream inherits it.

Money rule: round at the point a figure becomes a printed line, then only ever add rounded figures. Any other order produces a document that does not reconcile.

Part 4 — Concurrency

This is the part that surprised me most, and it is a good story because the obvious fix was wrong three times in a row.

Deadlock, in one paragraph

Two transactions each hold a row the other needs. Neither can proceed. Postgres detects the cycle and kills one, which surfaces as a failed request. Data stays correct; a user gets a 500.

The standard advice — acquire locks in a consistent order — is correct and almost always applied at the table level: "always lock accounts before orders." That reasoning is exactly what let this one hide, because the cycle was between two rows of the same table, inside one callback.

Act 1: a line of dead code

A model enforced "exactly one primary email per customer" like this:

before_save :ensure_single_primary

def ensure_single_primary
  return unless is_primary?

  CustomerEmail.transaction do
    CustomerEmail.where(customer_id: customer_id)
                .where.not(id: id)
                .lock("FOR UPDATE")          # ← this
                .update_all(is_primary: false)
  end
end

The finding was: that .lock("FOR UPDATE") is dead code, because Rails builds no lock clause for an UPDATE. Filed as a comment nit.

I checked by instrumenting the SQL:

-- what actually goes out
UPDATE "customer_emails" SET "is_primary" = $1
 WHERE "customer_id" = $2 AND "id" != $3

-- what the same scope emits as a SELECT
SELECT "customer_emails".* FROM "customer_emails"
 WHERE "customer_id" = $1 AND "id" != $2 FOR UPDATE

Confirmed dead. The finding's conclusion — "safe anyway, the comment just credits the wrong mechanism" — was wrong. The lock that never happened was load-bearing.

Act 2: the deadlock it was hiding

Demoting the siblings in before_save and letting the record's own save write the third gives two writes over two disjoint row sets, acquired in opposite orders by two concurrent requests:

time ──────────────────────────────────────────────────────────►

T1  "make A primary"    [ locks row B ]·············[ wants row A ]  ✗
T2  "make B primary"           [ locks row A ]······[ wants row B ]  ✗
                                   │                      │
                                   │  held by T2          │  held by T1
                                   ▼                      ▼

                        T1 ──── waits for ────► T2
                         ▲                       │
                         └──── waits for ────────┘
                                  a cycle

The two lock sets are disjoint — T1 never touches A until its own save, and T2 never touches B until its own save. That is precisely what makes the order caller-dependent, and a cycle possible.

Each holds what the other needs. I reproduced it on two connections: 2 deadlocks in 30 rounds.

The fix looked elegant — collapse it to one statement, so both transactions match the same rows under the same WHERE and take the locks in the same scan order:

CustomerEmail.where(customer_id: customer_id)
            .update_all(["is_primary = (id IS NOT DISTINCT FROM ?)", id])

(IS NOT DISTINCT FROM rather than = because on create id is still nil, and id = NULL is NULL — a plain = would demote nothing.)

Deadlocks: 0 in 30. Shipped.

Act 3: what I had not tested

Reviewing my own merged work later, I checked the path I had not checked — two concurrent creates:

rounds where TWO rows ended up primary: 20/20

Every single time. A statement can only lock rows that exist. Two concurrent creates each demote what they can see, neither sees the other's un-inserted row, and both commit is_primary = true.

The comment I had shipped said "the surviving data was always correct — it is the request that fails, not the invariant." That was false, and I had written it from testing the update path and generalising.

Two fixes together:

1. Lock the parent row first. Serialise on the customer, so the second create waits, sees the first's row, and demotes it.
2. A partial unique index, because an invariant that only holds when everyone remembers a callback is not an invariant:

CREATE UNIQUE INDEX CONCURRENTLY idx_customer_emails_one_primary
    ON customer_emails (customer_id) WHERE is_primary;

That index is what protects against bulk imports, update_all, update_column and raw SQL — every path that goes around the model entirely.

Act 4: two more bugs, both found only by running it

The index rejected my own single statement. Postgres checks a unique index per updated tuple, and an UPDATE visits rows in physical order — which is not id order, because a previously-updated row lives at the end of the heap. So the statement could set the new primary true while the old one still was, and trip the index on its own write.

It passed a two-row probe. It failed at four rows. Purely on heap layout.

So: back to two statements — demote the others, then promote this one — which never holds two primaries at any instant. Safe from deadlock now because the parent lock supplies the ordering.

And splitting it reintroduced something subtler. An object loaded while it was primary, and demoted by another request since, has no in-memory change — so Rails writes no UPDATE at all, and the demote alone leaves the customer with zero primaries. The one-statement version had got that right for free. The promote had to become explicit:

def ensure_single_primary
  return unless is_primary? && customer_id.present?

  Customer.where(id: customer_id).lock(true).pick(Arel.sql("1"))   # parent first

  CustomerEmail.where(customer_id: customer_id).where.not(id: id)
              .update_all(is_primary: false)
  CustomerEmail.where(id: id).update_all(is_primary: true) if persisted?
end

Final verification, 40 mixed concurrent rounds — plain parent writes, nested attribute writes, and primary-email saves interleaved: zero errors, exactly one primary.

Neither of those two bugs was visible from reading the code. Both came from running a test written for a different reason.

Four attempts, and each one was correct about the bug in front of it:

  attempt                      fixes                    breaks / misses
  ─────────────────────────    ──────────────────────   ─────────────────────────
  1  demote siblings,          (the original intent)    deadlock: disjoint lock
     lock clause on an UPDATE   — clause never sent       sets, opposite order
                                                        
  2  one statement over        deadlock  ✓              concurrent CREATEs: a
     every row                                           statement cannot lock
                                                         a row that doesn't exist
                                                        
  3  + partial unique index    creates  ✓               the index rejects the
                               every bypass path  ✓      single statement — heap
                                                         order, not id order
                                                        
  4  parent lock, then         deadlock  ✓              — nothing found in 40
     demote, then promote      creates  ✓                 mixed concurrent rounds
     explicitly                index  ✓
                               stale in-memory copy ✓

Attempt 2 shipped. It took an adversarial pass over my own merged work to reach attempt 4, and attempts 3 and 4 exist only because I ran the thing rather than reasoned about it.

The other concurrency traps

Read-modify-write on a JSON column. Read the blob, merge a key, write it back. Two requests interleave and one silently overwrites the other's key. Fix it in one statement so Postgres re-evaluates against the locked, latest row:

UPDATE tenants
   SET settings = jsonb_set(settings, '{counter}', to_jsonb(...))
 WHERE id = $1
RETURNING settings

COUNT + 1 as a document number. Two concurrent creates count the same value and produce the same invoice number. The answer is a unique index plus a retry loop — and each retry must run in its own savepoint, or the first violation poisons the transaction and the retry cannot execute.

return from inside a transaction commits it. On Rails 8 a return out of a transaction do block commits rather than rolling back. So a bail-out branch reads as "give up, undo" and behaves as "give up, keep".

The finding named one site. There were twelve. Every one was correct today — eleven return before writing anything, and the twelfth returns after a write it wants committed. So rewriting eleven correct services for zero behaviour change was not the job. What was missing was any signal when that stopped being true, because the hazard is not the return — it is a write appearing above one. That became a census pinned by an architecture spec: each file listed with the number of returning transaction blocks it is allowed to have.

Use raise to abort and roll back. Use next, or carry the result out in a local, to stop early and keep the writes. Both say which one they mean.

Part 5 — Testing this stuff

The test that passed because it could not fail

The order-line invariant had an assertion. It used 2 × 10,000 at a 10% discount. Those numbers cannot produce a rounding mismatch — the arithmetic is exact. It passed for years and pinned nothing.

For anything arithmetic, sweep:

QTYS.product(PRICES, DISCOUNTS, ORDER_DISCOUNTS).each do |qty, price, disc, od|
  # …assert the invariant, collect every failure
end
expect(mismatches).to be_empty, "…"

Collect all failures and report them, rather than aborting on the first — the distribution tells you whether you have an edge case or a systemic bug. Here it was 196 of 320.

Don't race threads in CI

A deadlock test is timing-dependent and will flake. Reproduce the race once, locally, to prove the bug is real — then commit a test for the property that makes it impossible:

it "takes the parent lock before it touches any of the customer's rows" do
  sql = statements_while { record.update!(is_primary: true) }

  lock_at     = sql.index { |s| s =~ /FROM "customers".*FOR UPDATE/im }
  first_write = sql.index { |s| s =~ /UPDATE "customer_emails"/i }

  expect(lock_at).to be < first_write
end

Deterministic, fast, and it fails for the right reason.

Fixtures that build impossible objects

The moment I armed the document constraints, the suite lit up — and every failure was a fixture. The order and invoice factories set a total with subtotal and tax left at zero. Every order built anywhere in the test suite was a document that did not add up.

That is how the original arithmetic bug stayed invisible for so long. A fixture that cannot represent a real record will never catch a bug about real records.

The fix is to derive rather than hard-code, so overrides stay consistent:

total      { 500_000 }
tax_amount { 0 }
subtotal   { total.to_d - tax_amount.to_d }

A constraint changes what your tests can even express

Once total = subtotal + tax was enforced, the spec for the drift report could no longer plant drift — because preventing exactly that is the constraint's job. It now drops the constraint for the duration and says why.

That has a corollary worth writing in the code: with the constraint live, the drift report can only ever surface rows that predate it. It is a migration aid, not a monitor, and it should read clean forever once the backfill is done. A report nobody understands the lifetime of becomes a dashboard everyone ignores.

Part 6 — What I got wrong

Three of the findings I wrote were wrong about their own cause:

Then I reviewed my own merged fixes and found two more defects plus a guard I had written as a prose comment instead of a test.

Every one had the same shape:

Verified on the path I changed. Assumed on the neighbours.

• Tested the update path, assumed the create path — 20/20 broken.
• Fixed the formula, assumed existing rows would follow — they don't; an order raised today from an old quote still copies the stale subtotal.
• Wrote a guard for eleven other people's files, not for my own.

The most useful habit I took from this: after fixing something, ask "what is the sibling of the thing I just fixed, and did I check it or assume it?" Every single time, the answer pointed at a real bug.

And the opposite lesson, from running the reports against real data: four checks, three came back completely clean. Most of what an audit predicts does not materialise. The one that did was 1,091 records invisible in every money rollup — and it was a bug I had introduced two changes earlier.

The checklist

If you are auditing a database-backed application, in rough order of value:

ACID

• Does every multi-step state change run in one transaction?
• Is there a rescue around a write inside a transaction? It contains nothing — use a savepoint, with the rescue outside it.
• Do at-least-once inputs (webhooks, queues) have a uniqueness-backed idempotency ledger?
• How many CHECK constraints do you have relative to tables? If money has none, the database will store anything.
• Are your constraints NOT VALID? Then historical rows are unproven, and they are re-checked the next time anything writes to them.

Normalization

• For each stored aggregate: what maintains it, and is there a test that fails if a new writer skips it?
• Is it stored because something aggregates it in SQL? If not, derive it.

Money

• Does every money column have a currency, and does every SUM filter by it?
• Are counts being filtered by currency? They should not be.
• Do you round before summing or after? Check against a printed document.
• What happens to existing rows when someone changes a currency setting?

Concurrency

• Any read-modify-write on a JSON or counter column? One statement instead.
• Any invariant enforced only by a callback? Add the index or constraint.
• Does anything take locks on two rows of the same table in caller-dependent order?
• Any return inside a transaction block?
• Reproduce every deadlock you claim. Then test the property, not the race.

Method

• Neuter every fix and confirm the test fails.
• Sweep arithmetic invariants; never assert them with convenient numbers.
• Verify which datastore you are connected to before believing any number.
• After each fix: what is its sibling, and did I check it or assume it?

The audit closed with 22 findings shipped or explicitly deferred with reasons, across nine pull requests and a test suite of 12,671 examples. The two deferrals — dated FX rates and a credit ledger — are written down with what it would take and when to revisit, because "we decided not to" and "nobody noticed" look identical six months later, and only one of them is a decision.