The Approval Queue Was Lying
The principle: a green pipeline is not proof the data is right. When your query joins two systems on an ID, the rows that don't match don't raise an error. They just vanish.
Every job was green. Every pipeline run succeeded. No alerts fired, no errors logged, no one paged.
And about 15% of the purchase orders waiting for approval were invisible to the people supposed to approve them.
That is the failure mode this post is about: not a crash, not a timeout, not a bad deploy — a query that ran perfectly and returned the wrong answer, quietly, every day, for weeks.
The symptom
We run an internal procurement app. A purchase order gets raised, and it lands in an approver's queue — first a project manager, then a project admin. Approvers open their queue, see their work, approve it, and the order moves on.
Except some approvers opened their queue and saw nothing. Their orders existed. They were assigned. They were simply not there.
Nobody reported it as a bug at first — from the approver's side it looked like having no pending work. It surfaced only when someone asked why an order had sat unapproved for days, and the approver, quite reasonably, said they'd never seen it.
The mechanism
The approval queue is a query. It joins the purchase order table to the job reference table — the dimension that maps a job code to the people responsible for it.
The join was an INNER JOIN.
That one keyword is the whole story. An INNER JOIN only returns rows where both sides match. If the job reference table has no row for a given job code, the purchase order doesn't error out. It disappears from the result set. The queue renders fine. The count looks plausible. The missing rows are simply gone.
And there were two reasons rows failed to match.
First, the job code wasn't the same string in both systems. The code originates in the ERP, passes through the warehouse, and lands in the job reference table — and along the way it picks up format variations. One system stores the code as JOB-123-100. Another stores it as JOB-123-100 | 2026 — the same code, with a year suffix after a pipe separator. An exact-match join treats those as two different jobs. They are the same job.
Second, the reference table itself had gaps. Some job codes had never been synced into it — the sync only knew how to extract one format, so records in the pipe format failed extraction and never landed. And some records carried a company name field filled with a short fragment — a word like "Mug" — instead of a real identifier, which the sync then trusted as the job ID. So the reference table was missing rows and held wrong ones, and the inner join silently dropped everything that touched either problem.
Two systems, one shared concept, three formats, no shared validation. The queue was the place where all of that surfaced — as absence.
The fix
The fix was four small changes to the same query, plus a rebuild of the sync that feeds the reference table.
1. INNER JOIN became LEFT JOIN. The purchase order is the thing the approver needs to see. The job reference is enrichment. A missing reference row should degrade the row — show it with a fallback approver — not delete it. The query now keeps every order and fills in the approver from the pricing table when the reference table has nothing.
2. Boundary-aware fuzzy matching replaced exact matching. The join now tries the exact code first, then the code followed by a pipe boundary, then the code inside a pipe-delimited string. The boundaries matter: a plain "contains" match would link orders to the wrong jobs.
3. A COALESCE fallback for the approver email. If the reference table is missing, use the email stored on the pricing record. The approver is identified from whichever source actually has the data.
4. An explicit deleted filter. Deleted requisitions had been appearing in queues because nothing filtered them out. IS_DELETED = false on every queue query.
The sync got the same treatment: extraction that handles both formats, validation that rejects a company name that can't plausibly be an identifier, and three tiers of fuzzy matching — exact, boundary-aware, then contains — with the match type recorded on every row, so you can see how a record matched, not just that it did.
What it cost, and what it bought
Before the fix, roughly 85% of orders were visible in the approval queue. After: 100%. The job-code match rate across the two systems went from about 70% to over 95%.
It cost a little speed: the queue query went from about 200ms to about 250ms, the price of trying three match patterns instead of one, and the sync from about two minutes to about two and a half. Both are rounding errors against a workflow that was silently dropping one order in seven.
The whole fix took about nine hours, touched around 150 lines in the queue endpoint, and replaced the sync with a 393-line stored procedure. Five validation tests cover the format cases.
None of the existing monitoring caught any of it. The jobs were green, the dashboards were green. The only signal was a human asking why an order hadn't moved.
What to check in your own system
If you run anything that joins two systems on an ID — and you do — these are worth an afternoon:
- Find every
INNER JOINon a cross-system ID in a user-facing query. For each one, ask: what happens to the row when the other side has no match? If the answer is "it disappears," that join is a silent filter. - Check whether the ID is the same format on both sides. Same code, different separators, trailing years, different casing, leading zeros. Pull a sample from each system and compare them as strings, not as concepts.
- Count what the queue shows against what should be there. Reconcile against the source of truth, not the query's own output — a query cannot audit itself.
- Give every join a fallback. A
LEFT JOIN, aCOALESCE, a default. A missing dimension should degrade a row, never delete it. - Filter deleted rows explicitly. If your tables carry a deleted flag and your queries don't check it, deleted rows will show up as ghosts — or worse, live rows will be hidden by the same logic.
- Track match rate as a metric, not a one-time check. The day a format changes upstream, your match rate is the first thing that moves. If nobody is watching it, the queue just gets quietly shorter.
The lesson
Jobs lie by omission. A pipeline can succeed, a query can run, and the result can still be wrong — not because anything failed, but because the query was written to assume the data was clean, and the data was never clean.
The fix isn't more monitoring on the job. It's a parity check on the result: count what should be there, and make the gap visible.