Upstream quirk: libsql /dump does not escape single quotes in sqlite_schema INSERTs for virtual tables (FTS5) — cold-store reaper works around it #1391

Summary

Turso/libsql GET /dump output cannot always be replayed by a stock SQLite parser. When a database contains a virtual table whose CREATE statement itself contains single quotes — the common case is an FTS5 external-content table, e.g. content='chunks' — the dump registers the virtual table inside a PRAGMA writable_schema=ON; ... OFF; block with a statement like:

INSERT INTO sqlite_schema(type,name,tbl_name,rootpage,sql) VALUES('table','chunks_fts','chunks_fts',0,'CREATE VIRTUAL TABLE chunks_fts
      USING fts5(text, content='chunks', content_rowid='id')');

The embedded SQL text is emitted verbatim: its single quotes are not doubled (''), so the string literal terminates early and every stock SQLite parser fails with near "chunks": syntax error.

This is an upstream libsql dump-generation bug (the same class of bug stock sqlite3 .dump avoids by escaping). It is filed here only to document the quirk and the workaround; nothing is filed upstream.

Impact

Found live 2026-10-27 on the atlas database: pkgs/cold-store-reaper/scripts/export-turso.sh downloads /dump and rebuilds it with sqlite3 -bail, which hard-fails on this statement — so any Turso database with a quoted virtual-table definition (FTS5 content= tables in practice) can never be filed into the cold store.

Workaround (this repo)

export-turso.sh runs the downloaded dump through a streaming repair filter before the rebuild. The filter passes every line through byte-identical except INSERT INTO sqlite_schema(...) statements inside a writable_schema block whose final SQL literal is verifiably mis-escaped (a lone, undoubled single quote in the literal body): for those it rewrites the literal with proper '' doubling so the statement parses and registers the virtual table exactly as authored. Correctly-escaped statements and all other lines pass through unchanged.

Notes

• Detection is parse-based and conservative: a statement is only rewritten when its final literal cannot be a valid SQL string literal as emitted.
• The truncation guard (dump must end in COMMIT;) applies to the filtered stream.

Acceptance criteria

• pkgs/cold-store-reaper/scripts/export-turso.sh (or a helper it calls) contains the streaming repair filter the body describes: only INSERT INTO sqlite_schema(...) statements inside a writable_schema block whose final SQL literal is verifiably mis-escaped are rewritten, with '' doubling; every other line passes byte-identical
• A fixture dump containing an FTS5 external-content virtual table (quoted content= definition) rebuilds cleanly through the export path with sqlite3 -bail
• Correctly-escaped dumps are unchanged by the filter (byte-identical output), and the COMMIT; truncation guard still applies to the filtered stream
• A test/fixture covers both the mis-escaped and correctly-escaped cases so the workaround cannot silently rot