Sign in Subscribe

Two Documents, One Number: How We Fixed a Duplicate CRF Bug

Two Documents, One Number: How We Fixed a Duplicate CRF Bug

In broadcast, there is an old rule: two systems must never hand out the same ID. If two ingest stations both decide the next clip is number 13 because each one only looks at its own log, you will find out at the worst possible moment, usually on air. This week I found the finance-software version of that bug, and it came from the exact same mistake.

Two documents, one number

The Liquidation Tracker issues check request forms (CRFs) with a number like CRF-YYYYMM-NNN: the year, the month, and a running count. Then someone noticed that two CRFs had the same number. One was a reimbursement CRF. The other, I assumed, was a recurring bill.

I was wrong about that. When I went to the data, the second holder was a cash advance, not a recurring bill. And it was not a one-off: two different numbers in the same month had each been issued twice. Neither was a race condition. The code already takes a database lock to prevent that.

Five copies of the same idea

The cause turned out to be embarrassingly simple. Five different parts of the app issue CRF numbers: recurring bills, invoices, cash advances, reimbursements and reimbursement CRFs. Each one had its own private copy of the same routine:

  1. Take a shared lock so nobody else issues a number at the same moment.
  2. Look through the tables for the highest number used this month.
  3. Add one.

The lock was shared. The list of tables in step 2 was not. Three of the five looked at only three tables and could not see reimbursement CRFs at all. One looked at four. Only one looked at all five. So once a reimbursement CRF took, say, number 13, a cash advance created afterwards looked at its own three tables, saw 12 as the highest, and happily issued 13 again.

It is the two-ingest-stations bug exactly. The lock worked. Each station just kept its own log.

Fixing the data first

Before touching any code, I wanted the two duplicates sorted out, because real documents were attached to them. The reimbursement CRFs were already transmitted to accounting, so those numbers had to stay. The cash advances were issued later and were still only approved, so those were the ones to renumber.

I took a backup of the table, then ran the change as a single transaction that would abort unless exactly two rows changed and no number was left shared between modules afterwards. Each cash advance got the next free number, a note in its remarks explaining what changed and why, and an entry in the audit trail. That last part mattered: a renumbered document with no explanation is how the next investigation starts.

The assumption that bit us

We assumed CRF PDFs were rendered from the database each time, so nothing stored would carry the old number. That was wrong. The app saves the PDF the first time a CRF is generated, and it was still sitting there with the old number in it.

We had searched the storage folder for files named after the old number and found nothing, which we read as confirmation. The saved files are stored under random names, so my search could never have worked. The real answer was one query away, in the attachments table.

It got worse, because one of those cash advances was already in a transmittal that had been emailed, and the app only lets you send a transmittal while it is still marked completed. We had to regenerate the PDFs through the app and reopen the transmittal before a corrected email could go out. A reminder that a data fix is not finished until you have followed the data to every place a copy of it lives.

The actual fix

The fix itself is small, which is the point. One function, nextCrfNumber, takes the lock, checks every table from one single list, and returns the next number. All the modules now call it, and the private copies are deleted:

export const CRF_NUMBER_TABLES = [
  'operations.cash_advances',
  'operations.invoices',
  'operations.recurring_bills',
  'operations.reimbursement_crfs',
  'operations.reimbursements',
] as const;

A list in code is only as good as the person who remembers to update it, so I added a check to the build. After applying every database migration to an empty database, it asks the database which tables have a crf_no column and compares that to the list. If a future module adds one and forgets the list, the build fails and says which table is missing. I tested it both ways: it passes on the real schema, and it fails with a clear message when I remove a table from the list.

I also tested the real SQL, not just the unit tests. On a throwaway database I put a reimbursement CRF at number 7, then asked for the next number. It returned 8. The old code from the other modules would have returned 1.

One button nobody had used

While tracing where numbers came from, I found a second route that could create CRFs: a "Create CRF" button on a summary page. It stamped one number on every approved reimbursement at once, let you type the number yourself with no check that it was unused, and created no CRF record, so the result could not be sent to accounting anyway. I checked the data: every reimbursement CRF in the system had come from the other screen. Nobody had ever used it. It is gone now, and the summary page keeps everything else.

My own test "failed", and it was telling the truth

When I tested on the staging copy, the numbers came out wrong again: one module issued 33 while another issued 64. For a moment that looked like my fix had failed. It had not been deployed. Staging was still running the previous version, which means the test had just reproduced the original bug on a bigger scale. The lesson is an old one from the engineering room: before you debug a failing test, check which version actually ran it.

What is next

The fix is built and waiting on staging checks as I write this. After that, I want a registry table that makes each CRF number unique in the database itself, so the guarantee does not depend on any application code being right. A shared function makes the bug hard to write. A database constraint makes it impossible to store.

I worked through this with an AI coding assistant, and the part I want to be honest about is where it was and wasn't helpful. It was very good at reading five near-identical blocks of code and seeing that the table lists differed. It was wrong about the stored PDFs until the data corrected it. The habit that saved us both times was the same: look at the actual data before believing the explanation.