Immutable SQLite Readers in a Mutable Memory Store

After a peer committed a second memory row, the original immutable SQLite connection still counted one row. Calling MemPalace's public vector query returned the new row. The wrapper retired its immutable connection, opened a reader that could see the WAL, and rebuilt the cached vector matrix.

That distinction is the thesis of this note: read-only access and snapshot freshness are separate contracts. An immutable connection can be useful inside a mutable memory service, but only while another layer tracks when its assumptions stop holding.

I examined revision 4f7745afbd9d0197c6475b4c7ac846d4932eaeda and ran the unchanged SQLite backend and its dependencies with Python 3.12.14, SQLite 3.53.1, and NumPy 2.3.5 on Linux. The replay used real temporary databases, synthetic two-dimensional vectors, and raw SQL fixture writes. No embedding model, Chroma service, MCP server, full ingest pipeline, or application writer-lock path was run.

Cache Vectors Without Pinning an Obsolete View

MemPalace organizes stored text into named locations and exposes memory through CLI and MCP interfaces. Its implementation includes pluggable storage, lexical and vector retrieval, metadata filters, mining, and repair code. The inspected package configuration declares version 3.12.0 and Python >=3.10. I used the source revision as the reproduction identity, rather than inferring a version from an installed distribution.

The sqlite_exact backend stores float32 vector blobs and performs exact cosine ranking with NumPy. It caches a collection's IDs, vector matrix, norms, and limited metadata on the connection handle. It hydrates selected records after ranking. This is an exact-scan backend, not an approximate-nearest-neighbor index.

The connection strategy depends on database state:

State when opening	Connection mode	Relevant behavior
Neither WAL sidecar exists	mode=ro&immutable=1	Avoid creating sidecars for a clean database
Both -wal and -shm exist	mode=ro	Read committed data still held in the WAL
Only one sidecar exists	Refuse a fresh open	Report an incomplete WAL sidecar set

The backend also sets PRAGMA query_only=ON. That adds a connection-level write guard, but it does not make an immutable snapshot observe later commits.

A Peer Commit Changes the Reader's Job

The fixture initialized the database using the backend's schema initializer, then inserted collection metadata and row a through SQLite. Row a contained text alpha and vector [1,0]. The seed connection closed before the reader opened, leaving a clean database without a WAL.

The public read-only collection opened an immutable handle. Querying [0,1] returned a, the only candidate. A repeated query reused the same cached matrix object; this was an identity check, not a timing benchmark.

A second SQLite connection then inserted row b, text bravo, vector [0,1], and committed while remaining open. A nonempty WAL and shared-memory sidecar existed. Direct SQL on the original immutable connection still counted one row. Through the collection's normal query path, the result changed to b at cosine distance 0.0.

The old handle was marked retired and closed. The new handle had immutable=False. The same collection wrapper survived the transition.

I then changed b's vector to [0,-1] through the peer connection and committed again. Querying [0,1] returned a. The matrix object changed, confirming that the backend invalidated its cache on the peer commit rather than retaining the previous ranking inputs.

The source uses PRAGMA data_version to notice other connections' commits on the ordinary read-only path. For immutable handles, it checks the database signature and WAL state instead. Its query code also checks an index version before and after ranking and hydration, retrying the complete query if the snapshot changes. That retry path is bounded to three attempts; this sequential replay did not exercise concurrent changes inside a single query.

Empty Sidecars Are Not Committed Data

I fabricated an empty -wal file with no matching -shm, while the clean immutable reader was already warm. The wrapper retained its existing handle and matrix. A fresh backend opening the same fixture refused it as an incomplete sidecar set.

These outcomes follow different evidence. The warm reader still had a matching database signature, and the lone empty file contained no committed frames. The cold reader had no established snapshot to preserve. The code distinguishes an incomplete opening state from evidence that its cached view is obsolete.

This was a constructed filesystem fixture, not a claim about how often a real writer leaves such a state. The probe removed that empty file before opening the actual SQL writer.

A Generation Counter Covers Completed Write Cycles

A writer may commit, checkpoint, and remove its sidecars between reader queries. Checking only for a live WAL would miss that completed cycle. The backend's signature combines file identity, size and timestamps with five SQLite header pragmas and a value from meta.write_generation.

The header helper reads those values through a temporary immutable SQLite connection. The backend's normal write cursor increments the generation inside an open write transaction. My raw fixture writer explicitly called the same increment helper before committing; the application mutation path itself was not tested.

For this scenario I first closed the reader, explicitly checkpointed with a writable connection, and closed that connection. A newly opened reader was immutable again. Closing the earlier connections alone had left sidecars in this environment, so the explicit checkpoint/cleanup step is part of the replay.

Next, a real SQL transaction changed alpha to the same-length text omega, incremented the generation, and closed. I then simulated unchanged filesystem metadata by making the Python stat probe return its previously captured result for that database only. The simulation was removed in a finally block.

The observed signature comparison was:

Signature component	Changed in this fixture?
Device, inode, size, mtime, ctime	No, deliberately frozen for the simulation
schema_version, page_count, freelist_count, user_version, application_id	No
meta.write_generation	Yes

The public query returned omega. The generation value distinguished this write even when the other compared fields did not.

This does not make arbitrary external SQL edits safe. A writer that bypasses the generation protocol also bypasses that evidence. The result demonstrates the implemented generation check under a stated simulation, not universal change detection on every filesystem or writer.

Retired and Closed Are Different Lifetimes

Automatic retirement permits an existing wrapper to acquire a fresh handle. Explicit close_palace() ends its lifetime. In the replay, querying the old wrapper after that close raised BackendClosedError; it did not silently reopen the collection.

A direct SQL update through the read-only connection was also refused. That verifies the connection's write restriction independently of its freshness behavior. I did not use the collection's mutation methods, which enter the application writer-lock path.

Reproduce the Reader Scenarios

The probe checks SHA-256 digests for 14 pinned implementation files before importing them. Namespace containers bypass package initializers that load unrelated integrations; the imported backend and dependency implementations are unchanged. NumPy and Python's SQLite module do the actual ranking and database I/O.

With the source checkout at the pinned revision, run python snapshot-probe.py /path/to/mempalace. My actual cloud command was:

python public/blog/immutable-memory-reader/snapshot-probe.py /tmp/mp-note-nov2

It exited 0. Selected terminal output:

clean_database: immutable=True; top=a; warm matrix reused
empty_lone_wal: warm snapshot retained; cold reader rejected
direct_old_immutable_connection: rows=1 after peer commit
public_query_after_wal_commit: old handle retired; immutable=False; top=b; distance=0.0
second_peer_commit: data-version invalidates matrix; top=a
after_explicit_checkpoint_and_close: clean database reopens immutable=True
simulated_unchanged_stat: five header pragmas unchanged; generation changed; document=omega
read_only_connection: SQL update refused
explicit_close: old collection remains closed
PASS: 9 reader scenarios; raw SQL fixture writes; no full ingest, embeddings model, or MCP execution

I also inspected the backend tests, including active-WAL visibility, reader retirement, and unchanged-stat generation coverage. I did not run that upstream suite or measure memory-retrieval quality.

The reader's correctness depends on two checks moving together: whether its connection can see the current database, and whether its cached vectors belong to that connection's current view. Read-only permissions answer neither question by themselves.