Harden the Command Center

60 seconds. That was how often the scheduler triggered a radar sweep capable of freezing every user write for the full 30 second SQLite timeout.

The result was a live command center that looked healthy until someone tried to reject and replace an article or publish during the wrong half minute. Then it returned an internal server error because the database was locked.

The root cause was not mysterious SQLite behavior. It was transaction scope.

article_radar.refresh_article_candidates opened one SQLite write transaction, then ran every research collector inside it. Network work happened while the write lock sat there doing absolutely nothing useful. The scheduler invoked that sweep every 60 seconds through queue backfill, so normal user writes regularly collided with a long transaction and waited until the timeout.

I moved collector execution outside the database connection entirely. Collectors now finish their work with no connection open. Persistence and candidate selection then happen together inside one short BEGIN IMMEDIATE transaction. Per source error rows are still preserved, so shortening the critical section did not mean throwing away failure visibility.

That is the actual tradeoff. The collector sweep no longer gets to pretend it is one giant atomic database operation. In return, user writes stop waiting behind network latency. The database transaction now protects the state transition that needs protection, not the research process surrounding it.

The end to end suite had its own version of the same mistake: it treated uncontrolled external work as part of a deterministic test.

The browser fixture used real radar collectors. Those collectors performed network research, while accounts_signal launched Chrome against the temporary HOME. Teardown could remove the temporary directory while that Chrome process was still using it. Very exciting behavior if the purpose of a test suite is producing folklore.

I replaced the collectors with a deterministic local signal. The runner now waits on the raw DOM for the replacement draft assertion and reports headings plus write attempts when the assertion fails. The test exercises the dashboard flow without quietly importing network availability, browser process timing, and temporary directory cleanup into the definition of correctness.

The hardening pass also added isolated test databases through conftest, database retry and WAL settings, stricter route input validation for JSON bodies and article link verification typing, a frontend ErrorBoundary, and poll deduplication through usePoll. Regression coverage now includes test_db_isolation, test_db_hardening, and the test_hardening_* suites.

What would I do differently? I would make transaction boundaries and external process ownership explicit from the first version. No network call belongs inside a SQLite write transaction. No end to end test should depend on live collectors unless it is specifically testing those collectors. And any process launched against temporary state needs a defined shutdown contract before teardown begins.

The boring architecture rule wins again: keep scarce locks short, keep tests hermetic, and make failure output useful enough that the next bug does not require archaeology.