Written with Claude.
Claude Said SQLite Would Work Fine, and I Pushed Back
This has happened more than once. A storage problem comes up during MEAP’s development (sync conflicts, history, recovery) and the suggestion arrives: use a database. SQLite is the usual recommendation. It’s the right answer for most teams, and it’s the first tool in the context window for good reason.
I keep saying no. And then I lose a little sleep wondering if I’m being obstinate.
The honest question underneath it: am I refusing good advice, or am I refusing the default SWE path that the AI was trained on? Claude’s training data skews toward software teams with dedicated infrastructure and specialists to manage it. The correct answer in that context probably does involve a database. I’m not in that context. I’m a mechanical engineer building software inline with delivering real projects: field data capture, cost modeling, review dashboards for building condition assessments. There’s no infrastructure sprint, no specialist to hand the database to. Every complexity layer I add is a layer I personally maintain, usually the night before a site visit.
My current position: until MEAP is broadly adopted and the case for a database is genuinely undeniable, I’m holding the line. When something is so obviously the right call that I can’t argue against it, that’s when it comes in. Not before.
The rule that operationalizes this: if I can’t click the file and open it in Notepad, I don’t want it. Not as philosophy, as triage. The SvelteKit field app, the AI-assisted cost modeling, the review dashboards: those earn their complexity. A storage format that requires a separate binary to read doesn’t, unless I genuinely have no alternative. The one exception is IndexedDB, the field app’s offline storage on the iPad. You can’t cat an IDB database, and offline-first capture on an iPad leaves no text-file substitute. Everything else in the stack is a file I can open and read.
What I needed was a technical grounding for that “no” — not just “it feels complicated,” but something that held up when pressed. That grounding is what this post is about. The conversation that surfaced it: Claude claimed SQLite satisfies “I want to read the raw state without special software.” It doesn’t, and the reason why is the actual distinction worth naming.
The motivation behind MEAP’s substrate decision was never “no separate server process.” It was something more specific: I want to SSH into any machine, any container, any backup snapshot, and read what’s in my project files with cat. No install step, no library import. The bytes on disk should be the thing itself.
SQLite doesn’t do that. The bytes on disk are B-tree pages with a file header, index pages, and a WAL (write-ahead log) sitting next to the main file. You need the sqlite3 binary or a language binding to decode them. That’s special software. It’s excellent software, widely available software, but it’s still a dependency you have to resolve.
The two properties are independent: “no daemon” means no separate server process listening on a socket. “No special software” means the raw file is readable with standard text tools. SQLite satisfies the first. Append-only JSONL (a file format where each line is a complete JSON object, readable with cat or any text editor) satisfies both.
Why the Old Approach Broke First
The substrate decision wasn’t made against SQLite. The actual rejected alternative was MEAP’s previous approach: a mutable building.json file that was the single source of truth, mutated in place under a file lock, with an embedded _audit field holding history inside the same view file. State and history were conflated in one file, and when that file got corrupted or partially synced, the only history was inside the corrupt file. Two writable copies on workstation and dev-pi kept producing Syncthing conflicts, and the fix list was growing.
Think of it like a bank ledger. events.jsonl is the transaction ledger: it proves what happened, and every entry is immutable once written. building.json is the current displayed balance: fast to look up, fully derived from the ledger, replaceable if it ever goes wrong. A bank doesn’t rewrite the ledger every time you buy coffee. It appends an entry and updates the displayed balance. If the display glitches, you recalculate from the ledger.
The Write Path
Every write to a building’s state goes through a single gateway:
Workflow command
│
▼
tools/lib/writeBuilding.ts
│
├── acquire lock (O_EXCL sentinel file)
│
├── read events.jsonl ──────────────────────────────────┐
│ (readFileSync, split on \n, JSON.parse each line) │
│ │
├── replay: project(events) → current view │
│ (pure for-loop reducer, no query planner) │
│ │
├── validate new event can apply │
│ │
├── append to events.jsonl │
│ O_APPEND + fsync ← chattr +a enforced by kernel │
│ (append-only even for the file owner) │
│ │
└── replace building.json ─────────────────────────────┘
write to building.json.tmp
rename(2) over building.json ← directory op,
chmod 0444 works even on 0444 target
chattr +a (a Linux filesystem attribute that lets you append to a file but blocks overwriting, truncating, or deleting it, enforced by the kernel and applying even to the file’s own owner) was added in commit 5fe62dfe after a red-team audit found the prior chown meap-web:meap-web protection was a silent no-op: dev-pi has no meap-web user, so the chown never applied.
chmod 0444 (read-only permissions for everyone, but a file can still be replaced atomically via rename, which is a directory operation rather than a file write) on building.json means the displayed balance is never corrupted by a partial write. The gateway always writes to a temp file first, then renames.
The gateway does a cold-start replay on every write. Every write reads the full events.jsonl, replays all events, validates, then appends. That’s O(N) in event count. For MEAP’s workload, 50-250 items per building at property-condition-assessment cadence (one building at a time, not continuous ingestion), it’s fine. For thousands of events per aggregate or high write throughput, it would be the wrong choice.
The Read Path
Field app (SvelteKit)
│
▼
HTTP GET /api/*
│
├── fast path: read building.json (0444 file, O_RDONLY)
│ works with: cat, jq, curl, any HTTP client
│
└── cold-start / recovery path:
readEventsFromDisk() reads events.jsonl
│
▼
project(events) reducer → building view
│
▼
regenerate-view.ts writes fresh building.json
Both files are readable without any installed tooling beyond what ships with a base Linux install. But they’re readable for different reasons, and the enforcement mechanisms are different.
The Two-File Asymmetry
┌─────────────────────────────┬──────────────────────────────┐
│ events.jsonl │ building.json │
├─────────────────────────────┼──────────────────────────────┤
│ permissions: 0644 │ permissions: 0444 │
│ │ │
│ enforcement: chattr +a │ enforcement: chmod 0444 │
│ kernel-level, binds │ owner can replace via │
│ the file owner too │ rename(2), not file write │
│ │ │
│ who writes: gateway only │ who writes: gateway only │
│ (O_APPEND + fsync) │ (tmp + rename) │
│ │ │
│ partial sync: last N lines │ partial sync: corrupt JSON │
│ missing; earlier lines │ possible; discard and │
│ still valid and parseable │ regenerate from events │
│ │ │
│ readable with cat: yes │ readable with cat: yes │
│ each line is self- │ pretty-printed JSON, │
│ contained JSON │ full building view │
└─────────────────────────────┴──────────────────────────────┘
A partial sync that touches events.jsonl at the tail loses a few lines; earlier lines are still valid. A partial sync that touches building.json produces garbage JSON, but that’s recoverable: discard it, replay the events.
Where This Doesn’t Hold
Fleet queries, cross-building searches of the kind “find all equipment of type X across all projects,” can’t be answered with an index lookup. The fleet search tool does a sequential HTTP fan-out to dev-pi across 50-250 buildings. On a LAN connection that’s around 5-7 seconds end to end. SQLite with a shared database across buildings would solve this at the cost of a binary format and a required query tool.
And the property that matters here, raw readability with cat, only matters given MEAP’s context: single developer, audit-heavy work, debugging from whatever shell is closest. For production systems at scale where operators already have tooling installed and indexed queries are load-bearing, the tradeoff inverts. The “no special software” property is worth paying for when you’re also the person who needs to SSH in at 11pm and read what happened.