When Every Screen Looks Right but the Numbers Don't: Three Days Untangling the Liquidation Tracker

Every broadcast engineer knows the feeling: the rundown looks right, the playlist looks right, and yet something on air is off. Nothing is "broken". The problem lives in the gaps between the systems. That's exactly what I ran into this week with the Liquidation Tracker, the app I built to follow our cash advances from request to liquidation. My complaint going in was simple: "it seems there are inconsistencies with the data." Three days later I had a list of fixes, a redesigned workflow, and a few lessons I want to write down before I forget them.

Step one: don't guess, look

Before touching any code I had Claude Code read the whole cash advance → expense → liquidation flow, then run read-only checks against the live database, each one wrapped in a transaction that rolls back. Checking the real data first changed the conversation completely. Instead of "maybe the totals are wrong", we had specific records. There was a cash advance with the same receipt entered twice, once as a draft and once as completed. Another CA had been bumped back to "Approved" by a direct database edit, which left an orphan liquidation behind. Seven older cash advances had no liquidation record at all, because they had moved to For Liquidation before the app started creating those records automatically.

My favourite bug was the quietest one. The liquidation report Excel sorts every receipt into a column: Meals, Gas and Oil, Accommodation, and so on. The lookup table behind it was copied from our utility-bills module, so it knew "DIESEL" and "ELECTRICITY" but not "GAS_OIL" or "MEALS", which are the categories our expenses actually use. The database total was right. The printed sheet showed ₱0 in every column. It looked correct, and it was completely wrong.

The fixes

Some were one-liners. Add the missing categories. Stop counting cancelled expenses in a CA's "assigned" total. Actually apply a company filter that was being computed and then ignored. Others were cleanups. We backfilled the missing liquidation records, cancelled the orphan, and rebuilt the broken report file under its original number.

One finding scared me more than any wrong number. The app's migration runner thought 21 migrations had never run, even though their changes were all in production. Running the "safe" setup script would have replayed all of them, including one that re-seeds role permissions. We recorded them as applied without executing anything. The same week taught me a smaller lesson too: the migrations table stores names as VARCHAR(50). A 55-character filename ran its changes and then failed to record itself, so migration names now stay short.

Making the connection visible

Once the data was clean, the next complaint was fair: the Liquidations page had two lists that said the same thing, and neither showed which cash advance a liquidation belonged to. We merged them into one table with one row per cash advance: a link straight to the CA, a progress bar of completed expenses against the amount released, what's left, and a plain status. The status is one of Waiting for expenses, Ready to liquidate, Liquidated or Needs attention. The Cash Advances page got the same bar, so you can jump either way. The old "New Liquidation" button came out too. Every liquidation now starts from a released cash advance, and hand-made ones were the source of the duplicates.

Liquidation reports got the same treatment. You no longer tick loose expenses from anywhere. You pick liquidations, and only fully spent ones are offered. A report still holds 50 rows, so a big CA can fill one on its own, and several small ones can be combined, with a live "Rows used: N / 50" counter. The Excel now ends with a short summary per liquidation: CA number, AP voucher, amount, expenses, balance.

Who sees what

The next two requests were about people, not numbers. First, proof of payment on recurring bills became a switch in Roles & Permissions. Uploading a proof, deleting one, and marking a bill as paid are now separate toggles instead of something anyone on the page could do. The permission guard deliberately fails closed on a permission it doesn't recognise, so the order of rollout mattered: database first, then restart. Otherwise everyone, admins included, would have been locked out.

Second, cash advances were visible to every CRF creator. Now a CRF creator sees only the ones they created, requested, or that a supervisor shared with them. That rule runs everywhere a cash advance shows up: the CA list, liquidations, expenses, the transmittal picker and the report picker. Supervisors get a small Shared With panel to grant or remove access. Every existing cash advance had its creator in the audit log, so back-filling a proper "created by" field was exact rather than a guess.

Small things users actually feel

Two changes came straight from day-to-day friction. An expense needs a receipt before it can be completed, but once it was submitted the upload button disappeared. Submitted expenses are now editable again until they're completed. And instead of scanning receipts first, you can now press 📷 Take Photo, capture the receipt with a laptop webcam or phone camera, and it uploads like any other attachment. That meant relaxing one security header we had deliberately locked down after an OWASP ZAP scan. The camera went from blocked everywhere to allowed only on the app's own pages. Microphone and location stay blocked.

What I'm taking away

I wrote a one-page summary for the CRF creators and supervisors who use the app every day. It's plain language, with none of the admin plumbing. That page is short, and it should be, because the people doing liquidations don't care about migration tables. What I care about is the method that got us there. Look at the real data before you theorise. Make every change reversible: every file was backed up before it was touched, and every production write was confirmed first. And listen when a user says the numbers "feel" wrong, even when every individual screen looks fine. In broadcast we call that trusting the monitor over the rundown. It works for software too.