indranil
← Writing

Two Stores, One Truth

30 Jul 2026 · 3 min read · engineering local-first agents

91.7%
file paths in the database pointing at missing files
169 to 75 MB
database size after the fixes
156 to 36 s
sync time

A long-running pipeline of mine keeps its state in two places: a SQLite database and daily JSON files. That was a convenience at the start. Files were easy to write from the ingest step, and the database was easy to query from the dashboard.

When I audited the system, four of the five worst bugs were the same bug.

How one record went wrong
1. Ingest to Files: write record 2. Sync to Files: read record 3. Sync to Database: copy it, status skipped 4. Retention to Files: read the wrong store 5. Retention to Files: trash the outputIngestFilesSyncDatabaseRetention1. write record2. read record3. copy it, status skipped4. read the wrong store5. trash the output
The database never learns that the output is gone, so it keeps pointing at a missing file.
Where the two stores drift
  1. 1Ingest writes daily JSON files
  2. 2A sync copies them into SQLite
  3. 3Retention job reads one of them
  4. 4Dashboard reads the other
Every arrow between stores is a place for them to disagree.

What went wrong

None of these is clever. Each one is a reasonable bit of code that assumed the other store agreed with it.

It kept getting worse until I measured

Later I found that 91.7% of the file paths in the database pointed at files that were gone. The list endpoint took about 1.4 seconds, and I had been ready to optimise it. It was slow because it was defending itself against a database that lied to it.

The first reconcile pass cleared 823 stale source paths and 210 stale output paths. After that the endpoint did not need optimising.

A related bug: the event log had 216,798 rows for 6,965 items. One item was duplicated 154 times, because every sync replayed history. Making the insert idempotent got it to 31,556 rows, but a second sync still added 3,454. The fix was to stop replaying at all. The database went from 169 MB to 75 MB and a sync from 156 seconds to 36.

Event rows for 6,965 items
  • Before fixes216,798
  • After idempotent insert31,556
A second sync still added 3,454 rows, so I removed the replay entirely.

What I do now

  1. Every column has one owner. Either the machine writes it or a human does, never both.
  2. Reconcile before you optimise. If a query is slow, first check that the data it reads is true.
  3. Make syncs idempotent, then make them smaller. Idempotent is not the same as cheap.

If you have a “temporary” second store in a project, this is your sign to pick one.

Related articles