Every Test Passed. It Never Worked Once.

A unit test that mocks the database cannot fail on SQL. Mocks prove the code calls the database. They do not prove the database accepts the call. Any code path that writes to a database needs one test that runs the real statement on a real Postgres engine. We hold that position because of a defect in a production system we built and run on the SyscallAI platform. Its tests passed, its review passed, and its write step did not succeed a single time.

Here is what happened, why five separate layers missed it, and the rule we enforce now.

What the auto-close step was supposed to do

The system includes an order reconciler. One of its steps is auto-close. When the system of record shows that an order is already finished, the reconciler closes the matching row in the app and appends a note explaining why.

That step shipped in late August 2026 behind a feature flag. The flag stayed off for about two months. In late October we switched it on. From that run onward, the close statement failed every time it ran. It closed zero rows. We found and fixed it on November 8, 2026, about eleven days after its first real execution.

The step looked healthy for that whole period. No test went red and no alert fired. The run summary reported zero rows closed, which reads like a quiet day.

Why Postgres refused the statement

The statement appended the note with the Postgres function concat_ws and passed the note text in as a bound query parameter. Simplified to the part that matters, it had this shape:

-- before
concat_ws(' ', notes, $5)

-- after
concat_ws(' ', notes, $5::text)

concat_ws is declared VARIADIC "any". That signature tells Postgres to accept arguments of any type, so it gives the planner no type to assign to an untyped bound parameter. Postgres resolves parameter types when it prepares the statement. With nothing to infer from, it refuses to run the statement at all and returns:

could not determine data type of parameter $5

The fix was one explicit cast of that parameter to text. Everything else in the statement was correct.

This class of error only exists inside the database engine. The SQL is syntactically valid, and to a human reader it is semantically obvious. Only the Postgres planner, holding that function's actual signature, can tell you it won't run.

Five layers, and none of them fired

A one-line defect running for eleven days needs more than one gap. It went through five of them.

1. Review read a correct-looking line

The line reads correctly. You pass a separator, the existing notes, and a new note into a concatenation function. A diff reviewer cannot see a type-inference failure by reading, whether that reviewer is a person or a model. The failure appears only when Postgres plans the statement, and reading a diff never plans anything.

The change also predates the automated pull-request reviewer we run today. We don't think that reviewer would have caught it either, for the reason above. We've written separately about the limits of review as a gate in Why AI Code Review Alone Can't Keep Up With AI-Written Code. This defect is a clean example: review checks intent, and the intent here was fine.

2. The tests mocked the database

The reconciler had unit tests, and they passed. They mocked the database module. A mocked database accepts whatever string you hand it, returns whatever result the test author scripted, and lets the test assert that the code called it with the expected arguments.

Under those conditions the SQL never executed in any test. A mock has no planner, so it cannot raise a Postgres planning error. The mocked database tests in this case confirmed that the reconciler built a statement and handed it off. They had no way to confirm that Postgres would run it, and Postgres was the only thing that mattered.

This is the center of our argument. Mocks have a real job: isolating pure logic so you can test branching, edge cases and state transitions quickly. But "the code called the database" and "the database accepted the call" are two different facts. A mocked test proves the first one and says nothing about the second.

3. The feature flag kept it dormant

The flag held the code path off for about two months after it shipped. Its first real execution came long after the change had been reviewed, merged and forgotten. When it started failing, no one had reason to connect a quiet reconciler to a change from late summer.

A flag flip is a deploy. The code had technically been in production since August, but it ran for the first time in late October. Nobody treated that moment as a first run, so nobody watched it.

4. The failure went to the wrong log

Each failure was written to a pipeline stage log and nowhere else. It never reached the app's error table, and the error table is what our new-error-signature detector watches. A brand-new error signature appearing on every run is exactly what that detector exists to catch. It never saw this one.

Meanwhile the run summary kept reporting zero rows closed, with no cause attached. Zero is a plausible number for a reconciler on any given run. Zero for eleven days straight is a symptom, but only if something reads it that way.

5. Alerting recorded and told no one

At the time, fleet alerting ran in observe-only mode. The signals it did record were stored and never sent to anyone. So whatever partial evidence reached the alerting layer sat in storage, accurate and unread.

Several of these layers, working as intended, would have shortened the incident. All five failed together, and the step never worked once.

The fix, and the test that would have caught it

The code fix was the cast. The more important change was the test that shipped with it.

The new test runs the reconciler through the real database driver against a real Postgres engine, using temporary tables. It does not mock the database module. It executes the actual close statement with real bound parameters, so Postgres plans it exactly the way it does in production.

We checked the test in both directions. Against the old code, it fails with the exact production error: could not determine data type of parameter $5. Against the fixed code, it passes. A test that can't fail on the bug it guards against proves nothing, so we confirm the red before we trust the green.

This is the same principle behind Replay the Pipeline, Not the Function. Exercise the thing that runs in production, through the layers it runs through. A substitute that skips the hard part will agree with you every time.

The proof we ran in production

We also wanted proof against the production database itself, without writing anything to it. So we ran both versions of the statement inside a read-only transaction.

The old statement failed at bind time with SQLSTATE 42P18, Postgres's code for an indeterminate data type. That matches the production failure exactly.

The fixed statement got further. It reached Postgres's read-only refusal, SQLSTATE 25006. That refusal only happens after Postgres has accepted and bound the statement and then declined to write because the transaction is read-only. Reaching 25006 means the bind succeeded. We proved the fix against the real engine, with the real schema, and changed no data.

The rule we hold now

Since November 2, 2026, our engineering rule has been this: any new or changed SQL, raw or through an ORM, gets a test that executes it on a real Postgres. Never a mock. A harness that runs only pure logic does not count, however thorough it is.

The ORM clause matters. An ORM generates SQL you didn't write and may not have read, and Postgres judges that SQL the same way it judges handwritten statements. If the ORM emits a statement Postgres won't plan, only a real engine will tell you.

We hold a second rule alongside it. Failures on write paths persist an error row that carries the cause. A stage log line alone doesn't satisfy it. The error table is where our detection looks, so that is where a write failure has to land, with enough detail that the first person to read it knows what broke.

Where could we be wrong? Real-Postgres tests are slower than mocked ones, and a team with thousands of statements will feel that cost. We'd still pay it on every write path. If a test suite grows too slow, we'd shard it or run the database tests on a separate track before we'd go back to mocking the engine on a statement that writes.

Checklist for engineering leads

If you run order, fulfillment or operations pipelines that write to a database, start here:

• Find tests that mock the database on write paths. For every statement that inserts, updates or deletes, add one test that runs it on a real Postgres engine through the real driver. Temporary tables keep it fast and isolated.
• Confirm each new test fails on the bug. Break the statement on purpose and watch the test go red with the production error before you trust it.
• Cast parameters passed into variadic or polymorphic SQL functions. concat_ws, format and any function declared with "any" or polymorphic argument types give the planner nothing to infer from. Give every bound parameter an explicit type.
• Treat a feature-flag flip as a first deploy. The code's first real run is the deploy that counts. Check its output the day it flips.
• Treat "zero rows affected" for days as a signal. A write step that touches nothing, run after run, deserves an alert with a threshold, not a quiet summary line.
• Make sure the error table records the cause. Write-path failures should persist an error row with the cause, in the place your detectors actually watch.
• Check that alerts reach a person. Observe-only mode is fine while you tune thresholds. Know which signals are in it and when it ends.

None of this needs new tooling. It needs a test that talks to the engine, an error that lands where someone looks, and a first run that somebody watches. We'd rather find the next one of these in a test suite than eleven days into production.

If your operation runs on pipelines that write to a system of record and you want them tested against the engine they actually run on, Book a discovery call.