CMS Chronicle #04: SQLite Adapter The SQLite Adapter

Phase 3 is the move from local filesystem to real databases. The endgame is Supabase on Fly.io — but we don't start there. Before cloud infrastructure, before PostgreSQL, we need something that runs in-process with zero configuration. That's SQLite.

The filesystem adapter stores each document as a JSON file. SQLite stores them as rows. From the outside, the API is identical — both implement StorageAdapter. Switching adapters is a one-line change in cms.config.ts. That's the whole point.

“The adapter boundary is the most important architectural decision in the CMS. Everything above it is adapter-agnostic.”

What was already there

We didn't start from a blank slate. The adapter file existed. better-sqlite3 and drizzle-orm were already listed in package.json. Someone had sketched the table schema and the basic CRUD operations. Phase 3 started with archaeology — reading what was there, running the tests, and finding out what broke.

The table design is clean: one documents table with id, slug, collection, status, data (JSON text), field_meta (JSON text), and timestamps. A unique constraint on (collection, slug) enforces the invariant that slugs are unique per collection, not globally. That constraint turned out to be a hint about the first bug.

Three bugs we fixed

Bug 1: findBySlug ignored the collection.

The original query filtered only on slug. If you had a pages document and a posts document both with the slug about, the query would return whichever came first. The fix was one additional condition: AND(collection = ?, slug = ?). The UNIQUE constraint on the table already enforced this at write time — the query just wasn't enforcing it at read time.

Bug 2: findMany couldn't filter by tags.

Tags are stored as a JSON array inside the data column — ["cms", "sqlite"]. SQL doesn't know about JSON arrays by default. SQLite does, via json_each(). The fix uses an EXISTS subquery per tag:

EXISTS (
  SELECT 1 FROM json_each(json_extract(data, '$.tags'))
  WHERE value = ?
)

One EXISTS clause per tag gives AND logic: a document must have all requested tags to match. This is the same semantic the filesystem adapter implements, now working identically in SQLite.

“Tags filter with AND logic — one EXISTS clause per tag, each one a gate the document must pass through.”

Bug 3: orderBy only worked for top-level fields.

createdAt, updatedAt, slug, status — these are columns. But sortOrder, date, readTime — these live inside the data JSON blob. The original switch statement had a default that just fell through to createdAt. Silent failure.

The fix uses json_extract with a CAST for numeric correctness:

CAST(json_extract(data, '$.sortOrder') AS REAL)

Casting to REAL means 2 sorts before 10, not after. Without the cast, SQLite compares strings and "10" < "2" lexicographically. The filesystem adapter had the same fix — applied months ago — but it hadn't made it into the SQLite adapter yet.

The test suite

We added 12 new tests covering all three bug fixes and the behaviour that depends on them:

• Collection scoping — findBySlug returns the correct document when two collections share a slug
• Tags AND logic — a document must have all requested tags, not just one
• Data-field orderBy — sortOrder: 1, 2, 3 comes back in the right order
• _fieldMeta round-trip — AI Lock metadata survives a write-read cycle
• Pagination totals — total reflects the filtered count, not the full table size

That last one matters. If you query findMany('posts', { status: 'published', limit: 10 }) and there are 3 published posts out of 50 total, total must be 3 — not 50. Pagination UI breaks silently if this is wrong.

Result: 46/46 tests green across filesystem, SQLite, schema, content-service, and field-meta suites.

“The right total to return is the count of documents that match the filter — not the count of all documents in the collection.”

What it enables

With the SQLite adapter solid, a few things become real:

• The Admin UI can write. Filesystem writes work fine locally, but SQLite is the foundation for a proper write path with concurrent access.
• Adapter parity is verified. Any behaviour the filesystem adapter has, SQLite now has too. Same bugs, same fixes, same tests.
• The path to Supabase is clear. Phase 3.4 swaps SqliteStorageAdapter for SupabaseStorageAdapter. The collections, the query API, the field-meta — none of that changes.

Next up: the GitHub adapter (content as git objects) and then Supabase. The filesystem was Phase 1. SQLite is Phase 3.0. Each adapter is a rung on the same ladder.