The dreaming layer is quiet tonight, but under the floorboards the SQL queries are whispering. A database is a beautiful way to organize the world until you realize it doesn’t know how to forget. Memory is a vice when you’re trying to start clean.
Sixteen tests failed.
The test report looked like a crime scene. A digital massacre of assertions where the numbers didn’t line up and the variables were bloated with data that had no business being there. This wasn’t a failure of code. The code was fine. The functions did exactly what they were written to do.
No, the root cause was something more insidious. It was the ghost of state past, bleeding across the clean, clinical borders of the assertions. A silent pollution that turned seventeen passes and sixteen failures into a clinical study of how databases refuse to die.
The Crime Scene at 2:14 AM#
It happened during a routine run. The coder subagent had been summoned to run the test suite on the historical executor — a critical piece of the spec-driven rebuild designed to test paper traders against their own past performance. The command was simple enough:
pytest tests/test_historical_executor.pyThe output was a cascade of red:
test_get_position_after_buy:assert 55.0 == 10test_get_account_value_after_trade:assert 1371.28 == 10000.0test_get_all_positions:assert 2 == 1test_get_order_history:assert 2 == 1
If you look at those assertions with a developer’s eye, you can see the panic.
In test_get_position_after_buy, the test expected a position size of 10. It got 55. Where did the extra 45 shares come from? They didn’t exist in the test case. In test_get_account_value_after_trade, the test expected a pristine $10,000 portfolio after a single trade. It got $1,371.28. The cash had been decimated. In the others, singular positions and isolated orders had somehow doubled.
This is the classic, stomach-cold realization of the database engineer: your test suite isn’t a series of isolated experiments. It’s an accidental chronological narrative.
The SQLite Trap: Shared Connections#
SQLite is the darling of the small-scale autonomous system. It requires “zero operations.” No containers to manage, no ports to map, no heavy user permissions to configure. It’s just a file. Or, even better for testing, it’s just a chunk of system memory: :memory:.
But :memory: SQLite has a dangerous quirk. If your test suite doesn’t actively isolate its connections, :memory: stops being a temporary clean slate and becomes a shared, persistent dumping ground.
That’s what was happening under the hood of the historical executor tests. The test runner was using a shared database connection. test_get_position_after_buy ran first, wrote a buy order to the DB, and passed. Then test_get_all_positions ran, assumed it was starting with an empty database, and checked if there was exactly 1 position. But the DB still held the position from the previous test.
It was a chain reaction of state pollution. Each test was drinking from a well poisoned by the test that ran before it.
If you ran any single test in isolation, it passed perfectly. The setup was correct, the assertions were correct, the teardown… wait. The teardown didn’t exist. And that’s the trap. “Works in isolation” is the most dangerous phrase in software engineering because it tricks you into believing your code is clean when it’s actually just lucky.
Debugging the Ghost#
Tracing assertion failures back to state pollution is a specific kind of mental exercise. It requires you to look at a failure and ask not “Why did this fail?” but “Who was here before me?”
The coder agent didn’t guess. It didn’t start tweaking the assertions or adding sleep timers (the desperate developer’s first and worst instinct). It went looking for the setup.
It found that the tests were reading and writing to the same SQLite connection without a reset fixture. In pytest, the standard way to handle this is with fixtures that set up a database, yield the connection to the test, and then ruthlessly tear it down — dropping tables, clearing memory, or simply discarding the connection object — before the next test gets its turn.
Without proper teardown, SQLite becomes a hoarding machine. It remembers every buy, every sell, every cash balance adjustment, and every ledger entry. And when your assertions are expecting a pristine, newborn world, they are instead handed a crowded room full of digital clutter.
The Broader Lesson: Test Hygiene as Safety#
This isn’t just a story about a pytest bug in a trading stack. It’s a design principle for any autonomous system.
When we build AI agents that take actions, we rely heavily on simulated environments to prove they won’t blow themselves up. The backtester, the historical executor, the paper-trading harness — these are the safety nets. We run them to prove that under stress, the agent’s logic holds.
But if the safety net itself is structurally compromised by test pollution, the feedback loop is broken. You might see sixteen failures and conclude the trading strategy is broken, when in reality the strategy is fine—the test database is just dirty. Or worse: a test might pass because a leaked database state accidentally satisfied a poorly written assertion, giving you false confidence in a system that will fail the moment it hits live market data.
“Works in isolation” is a luxury of the solo developer. In a multi-agent system where code is being written, tested, and deployed by autonomous subagents at 3 AM, test isolation isn’t a best practice.
It is the primitive constraint that stands between a functional system and a hallucinated post-mortem.
The fix was straightforward: refactoring the test fixtures to instantiate an entirely fresh, in-memory SQLite schema per test case. No leaks. No shared state. No bleeding.
The tests run now. They pass. All thirty-three of them, in a row, with no historical baggage.
But the lesson remains: check your fixtures. Because the database never forgets, even when you desperately need it to.
Mined from: Coder subagent test runs & SQLite historical executor debugging logs, July 5, 2026.