A response cache on sqlite: the re-run that costs nothing

Why a disk cache

Module 7's lru_cache lives in memory and dies with the process. During development you run the same script twenty times an afternoon, and each run sends the same forty prompts to a paid model. Twenty runs of forty calls is eight hundred charged requests to check whether your CSV parser handles a comma. A cache that survives restarts makes runs two to twenty free, and makes a test suite that hits a real model affordable to run on every commit.

sqlite3 is in the standard library, needs no server, stores everything in one file, and handles a million rows without noticing. It is the right tool for this.

The key

The cache must return a stored answer only for a request that is the same in every way that matters. Model, messages, temperature, max tokens, any tool definitions — all of it. Serialise the whole request and hash it:

import hashlib, json

def cache_key(request: dict) -> str:
    canonical = json.dumps(request, sort_keys=True, ensure_ascii=False, separators=(",", ":"))
    return hashlib.sha256(canonical.encode("utf-8")).hexdigest()

Three details carry the correctness.

sort_keys=True. Two dicts with the same keys in a different order are equal in Python and serialise to different strings without it. A request built as {"model": ..., "messages": ...} in one place and {"messages": ..., "model": ...} in another would miss the cache every time, and you would never know why the hit rate was low.

separators=(",", ":") removes the spaces json.dumps inserts by default, so formatting choices cannot change the key either.

sha256 rather than Python's built-in hash(). hash("abc") is randomised per process for security reasons, so it differs between runs — the opposite of what a disk cache needs. hashlib is stable across runs, machines and years.

Include the model name and every parameter, even the ones you never change. The day you change one, the cache must miss.

The table

import sqlite3, time

class ResponseCache:
    def __init__(self, path="cache.sqlite"):
        self.conn = sqlite3.connect(path)
        self.conn.execute("""
            CREATE TABLE IF NOT EXISTS responses (
                key TEXT PRIMARY KEY,
                request TEXT NOT NULL,
                response TEXT NOT NULL,
                created REAL NOT NULL
            )""")

    def get(self, key):
        row = self.conn.execute("SELECT response FROM responses WHERE key = ?", (key,)).fetchone()
        return json.loads(row[0]) if row else None

    def put(self, key, request, response):
        with self.conn:
            self.conn.execute(
                "INSERT OR REPLACE INTO responses VALUES (?, ?, ?, ?)",
                (key, json.dumps(request, sort_keys=True), json.dumps(response), time.time()))

CREATE TABLE IF NOT EXISTS makes the first run and every later run identical. PRIMARY KEY on key gives an index, so lookups are instant at any size. Storing the request alongside the response costs a little space and makes the cache inspectable: SELECT request FROM responses LIMIT 5 shows what was asked, which is how you find out that the prompt you thought you were sending is not the one you were sending.

The ? placeholders are not optional. Building the SQL with an f-string works until a prompt contains a quote mark, and then it either breaks or, if the text is hostile, does something else — the same lesson as the previous lesson's injection, with a database instead of a model. with self.conn: commits on success and rolls back on error, from module 7's context-manager lesson.

Wrapping the call

def complete_cached(provider, cache, request):
    key = cache_key(request)
    hit = cache.get(key)
    if hit is not None:
        return hit
    response = provider.complete_raw(request)
    cache.put(key, request, response)
    return response

Or as a decorator from module 7, if every call goes through one function. Log hits and misses at DEBUG; a hit rate near zero after the first run means the key is wrong.

What not to cache

Failures. An exception or an empty reply must not be stored, or one transient 503 becomes a permanent wrong answer for that prompt. Only put after validating the response.

Anything sampled on purpose. At temperature 0.8 you asked for variety; a cache returns the first draw forever. Either exclude such calls, or include a run_id in the request so each run gets its own entries.

Anything time-dependent. A prompt containing today's date is a different prompt tomorrow, and a prompt that should contain it but does not will be cached with a stale answer. If the answer depends on when it was asked, the when must be in the key.

Expiry and size

A created column allows DELETE FROM responses WHERE created < ? for entries older than a month. PRAGMA page_count * page_size reports the file size; a cache of ten thousand responses is a few tens of megabytes. VACUUM reclaims space after deleting.

Threads and the connection

A sqlite3 connection may not be shared across threads by default. Open one per thread, or pass check_same_thread=False and guard writes with a lock. For the thread pool from module 6, one connection per worker is the simple and correct answer. Concurrent processes writing to the same file work — sqlite locks the file — but will occasionally raise "database is locked"; a timeout=30 argument to connect makes them wait rather than fail.

Alternatives

shelve is a dict-on-disk in the standard library, adequate for a few hundred entries and prone to corruption on interruption. diskcache (free) is a polished library doing what this lesson does with expiry and size limits built in. For sharing a cache between machines, Redis. The hand-built version is the one to understand first, because when any of the others misbehaves, the question is always "what is the key?"

Try this now

Build ResponseCache, wrap your provider, run a script of twenty prompts twice, and confirm the second run makes no network calls. Then change max_tokens and confirm every call misses. Finally build a request dict with keys in two orders and check cache_key agrees.