feat(node): cap the media cache and evict least-recently-used entries

`thumbs` holds every generated thumbnail, every TMDB poster and backdrop, every
Cover Art Archive image and every cached audio transcode. Rows were removed only
when their source file left every group's index (`prune_file`), so a library
that merely changes over years grew this database with nothing to bound it.
Nothing in it is precious — every row is keyed off a value the node can
re-derive — which is what makes eviction the right answer rather than a bigger
disk.

512 MB, evicted on write (a cache only grows when written to; a timer is one
more thing to own and get wrong). `used_at` is marked on every read, including
the lookup by synthetic id that `_fetch_and_cache_poster` makes on every visit
to a poster grid — without that, the images shown most often would be the
coldest rows in the table. A single blob larger than the cap does not empty the
table for nothing.

The migration is the part that touches deployed nodes. `CREATE TABLE IF NOT
EXISTS` adds missing tables and never missing columns, so `used_at` would have
reached a fresh test database and never a real one. `_migrate()` does the
ALTER TABLE and seeds existing rows with "now" rather than 0 — otherwise the
first write after an upgrade evicts the whole cache, a correct-but-hostile
reading of "least recently used" for rows whose age nothing recorded.

The index on that column lives in `_migrate()`, not in `_SCHEMA`: run from the
schema script it executes before the ALTER on an existing database and fails,
which would have been every deployed node refusing to open its cache on the
first start after upgrading. Found by the migration test.

Verified against a real node's database, rebuilt into its pre-migration shape:
rows preserved, column present, seeded, index created, reopening harmless.

Co-Authored-By: Claude Opus 5 <noreply@anthropic.com>
Claude-Session: https://claude.ai/code/session_01HCGdheDLxGReuKHga3BtST

2 files changed, 270 insertions, 5 deletions

diff --git a/packages/meshbay-node/src/meshbay_node/media_cache.py b/packages/meshbay-node/src/meshbay_node/media_cache.py
index 8233270..2898dea 100644
--- a/packages/meshbay-node/src/meshbay_node/media_cache.py
+++ b/packages/meshbay-node/src/meshbay_node/media_cache.py
@@ -52,9 +52,18 @@ CREATE TABLE IF NOT EXISTS tmdb_meta (
 CREATE TABLE IF NOT EXISTS thumbs (
     thumb_hash  TEXT PRIMARY KEY,
     file_id     TEXT NOT NULL,
-    jpeg        BLOB NOT NULL
+    jpeg        BLOB NOT NULL,
+    -- Last time these bytes were served or written. The only thing that makes
+    -- eviction possible: without it the cache had no notion of "least useful"
+    -- and so no way to have a ceiling at all.
+    used_at     REAL NOT NULL DEFAULT 0
 );
 CREATE INDEX IF NOT EXISTS idx_thumbs_file ON thumbs(file_id);
+-- idx_thumbs_used is NOT here: on a database that predates `used_at`, this
+-- script runs before the ALTER TABLE that adds the column, and CREATE INDEX on
+-- a column that does not exist yet fails -- which would have been every
+-- existing node refusing to open its cache on the first start after upgrading.
+-- It is created in _migrate(), after the column is guaranteed to be there.
 CREATE TABLE IF NOT EXISTS season_meta (
     tmdb_id     TEXT NOT NULL,
     season      INTEGER NOT NULL,
@@ -115,6 +124,21 @@ TMDB_META_TTL_SECS = 30 * 86400
 MUSICBRAINZ_META_TTL_SECS = 30 * 86400
 
 
+# The blob store's ceiling.
+#
+# `thumbs` holds every generated thumbnail, every TMDB poster and backdrop,
+# every Cover Art Archive image and every cached audio transcode. Rows were only
+# ever removed when their source file left every group's index, so a library
+# that merely *changes* over years — films watched once, albums added and
+# removed, posters re-fetched after a rename — grew this database without any
+# bound. Nothing here is precious: every row is keyed off a value the node can
+# re-derive, which is what makes evicting the least recently used ones safe.
+#
+# 512 MB holds many thousands of posters and thumbnails; the audio transcodes
+# are what actually consume it, at a few MB apiece.
+MAX_THUMB_CACHE_BYTES = 512 * 1024 * 1024
+
+
 class MediaCache:
     """Async SQLite cache for TMDB/MusicBrainz lookups and generated thumbnails/cover art."""
 
@@ -126,8 +150,35 @@ class MediaCache:
         self._db_path.parent.mkdir(parents=True, exist_ok=True)
         self._db = await aiosqlite.connect(str(self._db_path))
         await self._db.executescript(_SCHEMA)
+        await self._migrate()
         await self._db.commit()
 
+    async def _migrate(self) -> None:
+        """Add columns to databases that predate them.
+
+        `CREATE TABLE IF NOT EXISTS` creates missing *tables* and never a
+        missing *column*, so a new column reaches a fresh test database and
+        never reaches a deployed node — the lesson `CLAUDE.md` records against
+        `create_all()`. Every existing node has a `thumbs` table without
+        `used_at`, and the eviction below reads it on every write.
+        """
+        async with self._db.execute("PRAGMA table_info(thumbs)") as cur:
+            columns = {row[1] for row in await cur.fetchall()}
+        if "used_at" not in columns:
+            await self._db.execute(
+                "ALTER TABLE thumbs ADD COLUMN used_at REAL NOT NULL DEFAULT 0")
+            # Existing rows get "now" rather than 0: the alternative is that the
+            # first write after an upgrade evicts the entire cache at once,
+            # which is a correct-but-hostile reading of "least recently used"
+            # for rows whose real age nothing recorded.
+            await self._db.execute("UPDATE thumbs SET used_at = ?", (time.time(),))
+            log.info("media_cache: added thumbs.used_at and seeded it")
+        # Unconditional, and after the column is certain to exist: this is also
+        # where a brand-new database gets the index, since _SCHEMA deliberately
+        # does not carry it.
+        await self._db.execute(
+            "CREATE INDEX IF NOT EXISTS idx_thumbs_used ON thumbs(used_at)")
+
     async def close(self) -> None:
         if self._db:
             await self._db.close()
@@ -301,7 +352,18 @@ class MediaCache:
             "SELECT jpeg FROM thumbs WHERE thumb_hash = ?", (thumb_hash,),
         ) as cur:
             row = await cur.fetchone()
-        return bytes(row[0]) if row else None
+        if row is None:
+            return None
+        await self._touch_thumb(thumb_hash)
+        return bytes(row[0])
+
+    async def _touch_thumb(self, thumb_hash: str) -> None:
+        """Record that these bytes were wanted, so eviction can tell what is
+        still in use from what was cached once and never looked at again."""
+        await self._db.execute(
+            "UPDATE thumbs SET used_at = ? WHERE thumb_hash = ?",
+            (time.time(), thumb_hash))
+        await self._db.commit()
 
     async def get_thumb_hash_by_file_id(self, file_id: str) -> str | None:
         """
@@ -315,14 +377,65 @@ class MediaCache:
             "SELECT thumb_hash FROM thumbs WHERE file_id = ?", (file_id,),
         ) as cur:
             row = await cur.fetchone()
-        return row[0] if row else None
+        if row is None:
+            return None
+        # A poster resolved through its synthetic id is in use just as much as
+        # one fetched by hash — this is the lookup `_fetch_and_cache_poster`
+        # makes on every visit to a grid, and missing it would let the images a
+        # busy library shows most often look like the coldest rows here.
+        await self._touch_thumb(row[0])
+        return row[0]
 
     async def put_thumb(self, thumb_hash: str, file_id: str, jpeg: bytes) -> None:
         await self._db.execute(
-            "INSERT OR REPLACE INTO thumbs (thumb_hash, file_id, jpeg) VALUES (?, ?, ?)",
-            (thumb_hash, file_id, jpeg),
+            "INSERT OR REPLACE INTO thumbs (thumb_hash, file_id, jpeg, used_at) "
+            "VALUES (?, ?, ?, ?)",
+            (thumb_hash, file_id, jpeg, time.time()),
         )
         await self._db.commit()
+        await self._evict_thumbs()
+
+    async def thumb_bytes(self) -> int:
+        """Total size of the blob store, as SQLite reports it."""
+        async with self._db.execute(
+            "SELECT COALESCE(SUM(LENGTH(jpeg)), 0) FROM thumbs") as cur:
+            return int((await cur.fetchone())[0])
+
+    async def _evict_thumbs(self, cap: int = MAX_THUMB_CACHE_BYTES) -> int:
+        """Drop least-recently-used rows until the store is back under `cap`.
+
+        Run on write rather than on a timer: a cache only grows when something
+        is written to it, and a timer is one more thing to own and to get wrong.
+        Writes are rare — one per new thumbnail, poster or transcode.
+
+        The row just written is never the one evicted: it carries the newest
+        `used_at` by construction. A single blob larger than the whole cap would
+        otherwise evict everything and then itself, so the loop stops when only
+        it is left rather than emptying the table for nothing.
+
+        Note the database file does not shrink; SQLite reuses the freed pages.
+        The point is the plateau, not the file size.
+        """
+        total = await self.thumb_bytes()
+        if total <= cap:
+            return 0
+        removed = 0
+        async with self._db.execute(
+            "SELECT thumb_hash, LENGTH(jpeg) FROM thumbs ORDER BY used_at ASC"
+        ) as cur:
+            rows = await cur.fetchall()
+        for thumb_hash, size in rows:
+            if total <= cap or len(rows) - removed <= 1:
+                break
+            await self._db.execute(
+                "DELETE FROM thumbs WHERE thumb_hash = ?", (thumb_hash,))
+            total -= int(size)
+            removed += 1
+        if removed:
+            await self._db.commit()
+            log.info("media_cache: evicted %d cached image(s), now %.1f MB",
+                     removed, total / 1048576)
+        return removed
 
     # ── photo technical/EXIF fields (Photos app) ─────────────────────────────
 
diff --git a/packages/meshbay-node/tests/test_media_cache_eviction.py b/packages/meshbay-node/tests/test_media_cache_eviction.py
new file mode 100644
index 0000000..587063a
--- /dev/null
+++ b/packages/meshbay-node/tests/test_media_cache_eviction.py
@@ -0,0 +1,152 @@
+"""
+The media cache has a ceiling, and reaching it drops the least useful rows.
+
+`thumbs` holds every generated thumbnail, every TMDB poster and backdrop, every
+Cover Art Archive image and every cached audio transcode. Rows were removed only
+when their source file left every group's index (`prune_file`), so a library
+that merely *changes* over years grew this database with nothing to bound it.
+Nothing in it is precious — every row is keyed off a value the node can
+re-derive — which is what makes eviction the right answer rather than a bigger
+disk.
+
+The migration is the part worth pinning hardest: `CREATE TABLE IF NOT EXISTS`
+adds missing tables and never missing columns, so `used_at` would have reached a
+fresh test database and never a deployed node — `CLAUDE.md`'s standing lesson
+about `create_all()`. Every existing node has a `thumbs` table without it.
+"""
+
+import sqlite3
+
+import pytest
+
+from meshbay_node.media_cache import MediaCache
+
+
+def _blob(n: int) -> bytes:
+    return b"x" * n
+
+
+@pytest.mark.asyncio
+async def test_the_cache_stays_under_its_cap(tmp_path):
+    cache = MediaCache(db_path=tmp_path / "media_cache.db")
+    await cache.open()
+    try:
+        cap = 40_000
+        for i in range(20):
+            await cache.put_thumb(f"hash{i:03d}", f"file{i:03d}", _blob(5_000))
+            await cache._evict_thumbs(cap=cap)
+        assert await cache.thumb_bytes() <= cap
+    finally:
+        await cache.close()
+
+
+@pytest.mark.asyncio
+async def test_what_is_evicted_is_what_nobody_asked_for(tmp_path):
+    """
+    Least *recently used*, not least recently written: a poster fetched a year
+    ago and shown on every visit to a grid must outlive one cached last week and
+    never looked at again.
+    """
+    cache = MediaCache(db_path=tmp_path / "media_cache.db")
+    await cache.open()
+    try:
+        for i in range(8):
+            await cache.put_thumb(f"hash{i}", f"file{i}", _blob(5_000))
+        # The oldest row by write time, read now — so it is the newest by use.
+        assert await cache.get_thumb("hash0") is not None
+        await cache._evict_thumbs(cap=20_000)
+        assert await cache.get_thumb("hash0") is not None, (
+            "evicted a row that had just been served")
+        assert await cache.get_thumb("hash1") is None, (
+            "kept a row nothing had asked for since it was written")
+    finally:
+        await cache.close()
+
+
+@pytest.mark.asyncio
+async def test_a_lookup_by_synthetic_id_counts_as_use(tmp_path):
+    """
+    `_fetch_and_cache_poster` finds an already-cached poster through
+    `get_thumb_hash_by_file_id`, which is the lookup a poster grid makes on
+    every visit. If that did not count as use, the images shown most often
+    would look like the coldest rows in the table.
+    """
+    cache = MediaCache(db_path=tmp_path / "media_cache.db")
+    await cache.open()
+    try:
+        for i in range(8):
+            await cache.put_thumb(f"hash{i}", f"tmdb:/poster{i}.jpg", _blob(5_000))
+        assert await cache.get_thumb_hash_by_file_id("tmdb:/poster0.jpg") == "hash0"
+        await cache._evict_thumbs(cap=20_000)
+        assert await cache.get_thumb("hash0") is not None
+    finally:
+        await cache.close()
+
+
+@pytest.mark.asyncio
+async def test_one_oversized_blob_does_not_empty_the_table(tmp_path):
+    """
+    A single audio transcode larger than the whole cap would otherwise evict
+    everything and then itself, leaving an empty cache and the same problem.
+    """
+    cache = MediaCache(db_path=tmp_path / "media_cache.db")
+    await cache.open()
+    try:
+        await cache.put_thumb("big", "file-big", _blob(50_000))
+        removed = await cache._evict_thumbs(cap=10_000)
+        assert await cache.get_thumb("big") is not None
+        assert removed == 0
+    finally:
+        await cache.close()
+
+
+@pytest.mark.asyncio
+async def test_an_existing_database_gains_the_column(tmp_path):
+    """
+    The migration, against a database shaped exactly like a deployed node's:
+    `thumbs` with no `used_at`, holding a row that must survive.
+    """
+    db_path = tmp_path / "media_cache.db"
+    con = sqlite3.connect(db_path)
+    con.executescript("""
+        CREATE TABLE thumbs (
+            thumb_hash  TEXT PRIMARY KEY,
+            file_id     TEXT NOT NULL,
+            jpeg        BLOB NOT NULL
+        );
+        CREATE INDEX idx_thumbs_file ON thumbs(file_id);
+    """)
+    con.execute("INSERT INTO thumbs VALUES (?, ?, ?)", ("old", "file-old", b"abc"))
+    con.commit()
+    con.close()
+
+    cache = MediaCache(db_path=db_path)
+    await cache.open()
+    try:
+        assert await cache.get_thumb("old") == b"abc", "the migration lost a row"
+        # Seeded with "now", not 0: an upgrade must not make every existing row
+        # look infinitely old and evict the whole cache on the next write.
+        con = sqlite3.connect(db_path)
+        used_at = con.execute(
+            "SELECT used_at FROM thumbs WHERE thumb_hash = 'old'").fetchone()[0]
+        con.close()
+        assert used_at > 0, "existing rows were left at 0 and are first to go"
+    finally:
+        await cache.close()
+
+
+@pytest.mark.asyncio
+async def test_opening_twice_is_harmless(tmp_path):
+    """The migration must be idempotent — a node opens this on every start."""
+    db_path = tmp_path / "media_cache.db"
+    for _ in range(3):
+        cache = MediaCache(db_path=db_path)
+        await cache.open()
+        await cache.put_thumb("h", "f", b"xyz")
+        await cache.close()
+    cache = MediaCache(db_path=db_path)
+    await cache.open()
+    try:
+        assert await cache.get_thumb("h") == b"xyz"
+    finally:
+        await cache.close()