The importer said every row matched. Three tables were missing.

An FTS5 virtual table named notes has shadow tables. SQLite creates them for you, they are called notes_config, notes_data, notes_idx and so on, and an importer has to skip them, because recreating the virtual table recreates its shadows. Empty ones, note: the index itself does not come across with the CREATE VIRTUAL TABLE, it gets rebuilt from the rows you feed the table afterwards.

Ours skipped them by prefix. Anything starting with notes_ was a shadow.

Which meant it also skipped notes_meta. And notes_archive. Ordinary tables, full of a user's rows, quietly dropped from the copy.

Then it printed this:

Row counts verified: every table matches the source

That message was not a lie, exactly. The verification pass walked the migration plan and compared each planned table against the source. A table that never entered the plan could not turn up as a mismatch. The copier and the checker were reading the same map, so the checker could only ever confirm the map.

We thought we had seven bugs

We built these importers to move databases out of Cloudflare D1 and Turso, and over the course of it we logged seven separate data-fidelity defects. Integers coming back rounded. A REAL that turned into an INTEGER. A stored infinity that aborted the run. Text that came back with replacement characters. Tables copied in an order that tripped foreign keys. Triggers that fired mid-copy and double-wrote a table. And the missing tables above.

They read like seven unrelated problems. They are not. They are two problems wearing seven costumes, and neither is really about SQLite.

Rule one: a value is only as faithful as the least expressive thing it passes through

Not the source. Not the destination. The narrowest hop in between.

SQLite integers are 64-bit. A JavaScript number is a double, so it carries 53 bits of integer precision. Route the first through the second and the arithmetic is decided for you.

SQLite distinguishes a REAL holding 1.0 from an INTEGER holding 1. A JSON number does not. Serialize through JSON and the distinction is gone before it reaches your code.

SQLite TEXT is a byte sequence that is usually, but not required to be, valid UTF-8. A JSON string is Unicode. Decode arbitrary bytes into one and every invalid sequence becomes U+FFFD, irreversibly.

SQLite stores infinity. JSON cannot represent it at all: JSON.stringify(Infinity) is null.

Four different-looking bugs, one shape. Every time, a value crossed a layer that could not describe it, and the layer resolved the ambiguity silently rather than failing.

The fix follows directly. Do the type-preserving encoding inside the engine that has the types, so the transport carries nothing but ASCII text and NULL, and reconstruct on the far side in SQL. Never hand a typed value to something with fewer types than you have.

Rule two: a check built from the copier's plan can only confirm the plan

Every one of those bugs shipped with a report saying it had worked. Not a silent failure, an actively reassuring one.

The row-count check walked the plan, so tables missing from the plan were invisible to it. The byte-for-byte fidelity claim was printed by the same code path that had already replaced bytes with U+FFFD. An int64 assertion was gated on the column's declared type matching /int/i, which meant an untyped column, exactly the one with no INTEGER affinity and the most likely to surprise you, was never sampled at all.

In each case the verification inherited an assumption from the thing it was verifying. A checker that shares the copier's blind spot is not a second opinion, it is an echo.

The fix is equally mechanical. Derive your verification from a different source than your copy. Read the table list from the source database, not from the plan you built. Determine a value's type from what came over the wire at runtime, not from what the schema declared. If both halves agree because they consulted the same input, you have learned nothing.

The SQLite case study

Everything below is one of those two rules applied to a real edge SQLite actually has. This is the part worth keeping open in a tab if you are writing a migration yourself.

Value fidelity

Encode every value inside SQLite, one tagged column per source column:

'i' || CAST(col AS TEXT)      -- integers keep their exact digits
'r' || printf('%!.20g', col)  -- reals keep full precision
't' || hex(col)               -- text crosses as hex
'b' || hex(col)               -- blobs cross as hex
                              -- NULL stays SQL NULL

One design constraint is worth stating because it is easy to hit late. Cloudflare documents a maximum of 100 columns per D1 table. That is a limit on the table. What we hit when we built this was our own reader failing on a query that returned 101 columns, which is an observation about that code path against that API on that day rather than a documented D1 rule. Either way the working ceiling was 100, which is why each source column becomes exactly one tagged column rather than a typeof plus value pair, since a legal 100-column table would otherwise need 200 output columns and be unreadable. (Vendor limits move. Check the current D1 limits before you rely on the exact number.)

Structure and ordering

A SQLite database is not a bag of tables, and the copy order is load-bearing.

The reserved suffixes, probed against SQLite 3.49.2 rather than recalled:

Exact-suffix matching is safe where prefix matching is not, because SQLite refuses CREATE TABLE <vtab>_<reserved> with "object name reserved for internal use" in both creation orders, so an ordinary table cannot occupy one of those names while the virtual table exists. Splitting at the last underscore also correctly finds shadows of a virtual table whose own name contains underscores.

Verification that is not an echo

• Read the table list from the source database, not the plan, so a table the plan never mentioned gets named in a warning instead of vanishing.
• Cross-check the shadow verdict against PRAGMA table_list, advisory only so a build lacking it degrades to skipping the check. Run against the original bug with the old logic still in place, it independently named all three lost tables.
• Determine integer-ness from the storage class observed at runtime, not the declared type, and compare exact decimal min and max with BigInt.
• Count rows before and after the copy, compared as the decimal strings SQLite produced rather than through Number(), since a count can in principle exceed 2^53. A source that changed size is positive proof of a concurrent write, which is the user's problem, and needs different wording from a short copy, which is ours.

What we did not fix

A paged read is not a point-in-time snapshot. LIMIT/OFFSET pagination can skip or duplicate rows when the source is written mid-copy, and ORDER BY rowid does not fix it. Keyset pagination is the better tool, though it narrows the window rather than closing it: it is still a sequence of reads, not a snapshot. On the D1 side we do not have it, because our reader for that source does not bind parameters. So the before-and-after counts are a mitigation, not a cure: we cannot always prevent the race, but we detect it and say so.

Equal counts also do not prove nothing was written. An UPDATE, or an insert balanced by a delete, is invisible to a count. We say that too, because the alternative is a report that overclaims, which is the bug this whole post is about.

What transfers

Almost none of this is really SQLite knowledge. Swap the specifics and the two rules hold anywhere data moves between systems.

Find the narrowest hop. Write down every layer a value crosses and ask what each one cannot represent. A CSV cell has no type. Protobuf has no arbitrary-precision integer. A JSON number is a double. Your ORM has its own opinion about dates. The narrowest hop decides your fidelity, no matter how good the two endpoints are.

Make the checker consult a different source than the copier. If your verification reads the same manifest, the same schema introspection, or the same type map that the copy did, it will agree with the copy and tell you nothing. The check has to be able to see something the copier could not.

The tell that you have gotten it wrong is a green result you cannot explain the mechanism of. Ours printed that every table matched, and it was right about every table it knew about.

The importers here are the ones behind Layerbase Cloud's direct migration flow, so if you are leaving Cloudflare D1 or Turso you can point it at a source and let it do all of the above for you. Starting fresh instead, you can create a SQLite database or create a libSQL database in about a minute.

And if you would rather run the copy yourself, everything here is a property of SQLite rather than of our tooling. The encoding rule is worth stealing.