SQLite compressed text-history prototypes

New research details an efficient way to keep a text document’s full edit history inside a SQLite database. The central concept bundles every past version into one JSON array and uses strong compression methods like Zstandard to remove repeated data. Early results indicate this method can shrink 20.4 MB of raw revision data to a mere 80.3 KB.

Key takeaways

• The research prototypes keep every past text version in a SQLite database using compressed JSON arrays.
• Tests compared two storage models: a WholeBlobHistoryStore and a more scalable ChunkedHistoryStore.
• Compression proves extremely effective, cutting 1,000 document revisions from 20.4 MB to 80.3 KB.
• AI support helped develop the concept, moving from a spoken conversation to a 38-minute coding session with GPT-5.6 Sol Pro.
• This method tackles the persistent problem of storing edit histories in relational databases without wasting space.

The Core Idea: Compressing Full Revision Histories

This work examines how to keep all earlier versions of a frequently edited text in a SQLite database with maximum efficiency. The basic plan stores a compressed JSON array holding the complete text of each past version, using compression to erase duplicate content.

A straightforward proposed system employs a BLOB column for a Zlib or Zstandard-compressed JSON array of all old documents. A separate column holds an uncompressed JSON array of matching Unix timestamp integers. The theory states that applying a strong compression algorithm to the bundle of all document versions should remove vast quantities of repeated text.

First experimental findings confirmed this strategy works exceptionally well. One test with 1,000 simulated edits to a document produced 20.4 MB of raw revision text. Storing that data as a Zstandard-compressed JSON array shrank it to just 80.3 KB.

Two Prototype Storage Models

Two specific storage prototypes were compared: the WholeBlobHistoryStore and the ChunkedHistoryStore. The WholeBlobHistoryStore functions by rewriting one compressed historical blob containing every document edit each time a change is made. This blob is a Zstandard-compressed JSON array of all prior text versions.

The ChunkedHistoryStore was created to handle scaling issues with very long histories. This model boosts performance by finalizing compressed chunks of history instead of rewriting one enormous blob. It caps each database row at a maximum of either 128 revisions or 3MB of uncompressed JSON text before initiating a new chunk. This method prevents the cost of decompressing and recompressing a whole, continuously expanding array on every single edit.

Implementation and Performance Results

The concept was built as a prototype using Python, with an AI model aiding development. After talking through the idea with the ChatGPT app’s voice mode, the researcher later entered a precise text prompt for GPT-5.6 Sol Pro directing it to “Use Python and Build experimental prototypes around this idea.” The AI operated for 38 minutes to produce the prototype code.

Core Implementation Details

Developers built two prototype stores: the WholeBlobHistoryStore and the ChunkedHistoryStore. Both keep the earlier text and timestamps and are built to skip identical replacements by default. To guarantee atomic updates and serialize writers, the prototypes employ SQLite’s BEGIN IMMEDIATE transaction.

Compression Performance

Initial testing showed striking compression efficiency. Simulating 1,000 edits to a document created 20.4 MB of raw revision text. Storing this data as a Zstandard-compressed JSON array reduced its size to only 80.3 KB.

Origin and Development Process

The idea for these SQLite compressed text-history prototypes began on a dog walk, driven by Simon Willison’s long-standing curiosity about keeping edit histories in relational databases. He pondered a fresh tactic: placing every prior version of a text in a large JSON array and using compression algorithms like zlib or Zstandard on the whole structure, expecting powerful compression from repeated strings.

The concept was first discussed and improved using the GPT-Live voice mode in the ChatGPT iPhone app. Willison used this mode to express a stream-of-consciousness outline for the scheme, suggesting a table with a BLOB column for a compressed JSON array of all document versions and a second column for an uncompressed JSON array of matching Unix timestamp integers.

After the voice conversation, Willison ended voice mode and typed a comprehensive text prompt for the GPT-5.6 Sol Pro model, directing it to “Use Python and Build experimental prototypes around this idea.” The AI model then processed this request for 38 minutes before delivering the working prototype code and files.