sqlite-cache-schema.md

SQLite Cache Schema

This schema provides a ready-to-use structure for caching different data types.

Types

Immutable

It efficiently stores and retrieves immutable binary and textual data, accessed by a textual key. Use the cache_blob and cache_text tables for this purpose.

Versioned:

For slowly changing data, use the cache_rev table. It is an append-only table that preserves every revision but is optimized to look up only the most recent one for a given key.

Versioned and paged:

HTTP APIs often returned chunked data in pages. When this data must be preserved exactly as-is—where pre-processing is undesirable or even forbidden—the cache needs to maintain its paged nature.

The cache_rev_paged table is designed for this. Like the cache_rev table, it appends each new revision of a record. It is optimized to quickly retrieve the most recent set of paged objects for a key. This requires all pages for a given key to share the same inserted_at timestamp.

Why SQLite

SQLite is a serverless, file-based relational database that runs within your application's process. It is fast and requires zero configuration, unlike traditional client-server RDBMS. This makes it an ideal solution for a durable, on-disk application cache.

Key-value stores like Redis are the standard for most caching solutions. They are optimized for the key-based access patterns that caches typically require.

However, for (web) applications with low to medium concurrent users, SQLite is also a well-suited key-value store. These applications rarely hit SQLite's performance limits. The benefits are even greater if you already use SQLite, prefer simple infrastructure, and write concurrency is not your bottleneck.

Schema

-- stores immutable text values by key (e.g. HTML, JSON from HTTP APIs)
CREATE TABLE IF NOT EXISTS cache_text (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key TEXT NOT NULL UNIQUE,
    inserted_at DATETIME NOT NULL, -- ISO 8601 in UTC
    data TEXT NOT NULL
);

-- stores immutable binary data by key. (e.g. images, audio)
CREATE TABLE IF NOT EXISTS cache_blob (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    key TEXT NOT NULL UNIQUE,
    inserted_at DATETIME NOT NULL, -- ISO 8601 in UTC
    data BLOB NOT NULL
);

-- stores versioned text values, preserving every revision while optimizing access for the latest one
CREATE TABLE IF NOT EXISTS cache_rev (
    key TEXT NOT NULL,
    inserted_at DATETIME NOT NULL, -- ISO 8601 in UTC
    data TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_cache_rev_key_inserted_at ON cache_rev (key, inserted_at DESC);

-- stores versioned, paginated text data, preserves every revision of each page and is optimized for retrieving the latest complete set
-- ideal for caching raw API responses that are returned page-by-page
CREATE TABLE IF NOT EXISTS cache_rev_paged (
    key TEXT NOT NULL,
    page INTEGER NOT NULL,
    -- used to group pages from a single fetch run
    -- requirement:
    -- different pages that have been fetched in one run must have the same timestamp
    -- despite fetching them one after the other and at slightly different times
    -- good enough approximation when minutes of difference do not matter
    inserted_at DATETIME NOT NULL, -- ISO 8601 in UTC
    data TEXT NOT NULL
);

CREATE INDEX IF NOT EXISTS idx_cache_rev_paged_key_inserted_at_page ON cache_rev_paged (key, inserted_at DESC, page ASC);

PRAGMA journal_mode = WAL;
PRAGMA user_version = 1;