The thing that clicked today: when two independent workers share one status column, the system does not settle, it oscillates, and the only way to know the real state is to look at the evidence instead of the column that is itself under suspicion.
Prospect is my local-first job-application tracker. A browser extension captures a posting into structured fields, and two background workers enrich each listing afterwards: one asks a language model to parse the description into fields, and one computes an embedding (a vector that captures what the listing is about) for similarity search. Both ran fine on their own. Together they were fighting.
Built / shipped
A diagnosis that refused to inherit the handoff's severity. The section arrived scoped as "one row has a wrong status, cosmetic." Reading the two workers' code side by side showed four things. First, the parse worker's startup backfill re-queued every listing with a description on every service restart. Second, an already-parsed row took the no-op path, which wrote a terminal "skipped" straight over the embedding worker's "embedded" in the shared column. Third, the embedding worker's own startup backfill selects every row whose status is not "embedded," so every row the parser had clobbered got handed back for a needless re-embed on the next restart: real repeat traffic to the model server on the GPU box, contending with the model already resident there. Fourth, separately, the parser's save-the-old-status-and-restore-it-on-success dance raced the embedder: a roughly 36-second model call could restore a stale value over a concurrent "embedded" write.
Ground truth, established through the evidence rather than the column. The vector table cannot be read from the command-line SQLite client (it needs an extension that the client does not load), so the check ran through the app's own code with the extension loaded. Result: all 15 listings had a real vector row and an embedding model recorded. Every one was genuinely embedded. Only 6 said so. The column was lying about 9 rows, not 1.
The fix: one lifecycle column per worker. The parse worker got its own status column, backfilled from evidence already in each row (its parse hash matching the description hash), and it now writes only that column, never the shared one. The restore dance was deleted along with its race. An already-parsed row now re-asserts "parsed" instead of degrading itself, because "skipped" for a row that is parsed was simply untrue. A repair script, dry-run by default and safe to re-run, re-marks "embedded" only where a vector row and a model name both exist.
A test that locks the real cost. The new test runs both workers' startup backfills against fixture rows, asserts the rows come back "embedded," and then asserts that the embedding worker's actual selector returns zero rows. It encodes the thing that cost money, not just the thing that looked wrong, so the next schema change cannot silently reintroduce it. Suite 90 of 90.
Problems & fixes
The order of repair and restart mattered. Repairing the 9 rows before restarting meant the embedding worker's startup backfill selected zero rows, so the restart cost zero GPU work. Restarting first would have burned 9 needless embeds. The restart itself was the proof: under the old code this exact restart would have re-stamped all 15 rows, and the "enriched at" timestamps were byte-identical before and after.
A stray file I created by trusting stale documentation. The project note gave the database path as one thing; the live database lives somewhere else; the SQLite client creates a file on open, so I left a zero-byte database in the wrong place. It was ignored by version control and therefore invisible to status. Deleted, and the note corrected so the next session does not repeat it.
A 404 that was not an outage. A read endpoint returned 404 because no such route exists (only the write form does). Checked the route table rather than declaring a regression.
The same collision, prevented twice more. Two later workers the same week (skill extraction and an advisory pass) each got their own lifecycle column from the start.
Decisions
One worker, one column. A worker's status column is the only column that worker is allowed to write. That single rule removes the restore dance, the race, and the oscillation together.
Establish ground truth from evidence, not from the column under suspicion. The vector rows and model names were the facts; the status was the claim.
Repair before restart, so the restart proves the fix instead of paying for the bug.
Encode the cost in a test, not just the symptom.
Learned
A shared status column between independent workers is a race by construction. It does not matter how careful each worker is.
"Skipped" was a lie as well as a collision. Write the true state, not the state that is convenient for the code path.
A restart is a test. If it re-does work, something is wrong with the work's idea of done.
The severity in a handoff is a claim like any other. Reading the code against itself was what turned "one cosmetic row" into "recurring compute cost on every restart."
Still open / next
The working tree carried the fix uncommitted for a day, which is its own kind of drift; committing and pushing is a separate, explicit step in this project. And the similarity search that the embeddings exist for still has to earn its place in the UI.