The riskiest moment in a database migration is the one where you switch over and find out. I'd rather find out a week earlier, while nothing depends on the new database.
One door to every database
Before any data moved, every store had to open its database through one helper. The helper decides where the writes go: SQLite only, both, or Postgres only. The store's own code doesn't know or care.
A check in CI fails any change that opens a database directly. Without it, the next feature would quietly write somewhere the migration can't see.
Write to both, read from one
In the shadow phase, SQLite is still the truth. Every write goes there first, then to Postgres. Reads stay on SQLite. If the Postgres write fails, the job carries on and the failure is logged. The shadow must never be able to break the thing it is shadowing.
The nightly compare
Every night a job walks each shadowed store and compares it with Postgres, table by table: row counts, then the rows themselves. It writes one summary line per store and fails loudly on a mismatch.
A mismatch is good news at this stage. It is a type that converted differently, a default that differs, an edge the first copy missed. Each one gets a scripted fix, never a hand edit, so the fix runs again on the day of the switch. If the two drift, a resync tool rebuilds the shadow from SQLite.
Matching data isn't the whole story. The switch also depends on every query meaning the same thing on both engines, so a read verifier runs the same reads against both and compares the answers.
What counts as done
A store is ready when a full week of real jobs has matched, every night, with no fixes in between. Then reads move to Postgres. Then SQLite stops being written. Then, after another clean run, it is retired. Each step can go back to the one before it.
Why bother
It is slower than a weekend cut-over. But a business runs on this data, and the slow way turns "I think it worked" into a week of evidence. When the switch comes, it is the least interesting day of the project.