Stax Digital Garden

DAT-1006 - Flat Markdown vs Embedded SQLite

3 min read

Living Document Notice
Published 2026-09-15. The evolving architecture, revisions, and connected notes for this dispatch live in the Stax Digital Garden.

Flat Markdown vs Embedded SQLite

Flat Markdown vs Embedded SQLite: Electric lime P1 vector CRT macro showing dual-pane comparison of plain-text markdown line vectors versus relational database table grid

Summary

Technical trade-offs deciding whether liberated data belongs in plain-text markdown or single-file SQLite databases.

This technical dispatch explores the underlying architecture, data structures, and concrete implementation boundaries required for local-first data sovereignty.

The Scalability Ceiling of Flat File Vaults

The primary appeal of flat Markdown files is archival permanence. A directory of .md text files can be read by terminal utilities, backed up via rsync, and inspected decades later without proprietary database drivers.

However, operating system filesystems are not optimized to treat 100,000 individual 2-kilobyte files as a coherent relational graph. When a knowledge tool opens a vault of this magnitude, it must issue thousands of stat system calls to read file metadata, inspect timestamps, and parse backlinks.

Filesystem Traversals at Scale:
100,000 files * 4 KB inode allocation = 400 MB raw disk footprint
Directory scan time (cold cache): ~1.4 - 3.8 seconds on NVMe
Operating system file watcher overhead: ~180 MB RAM resident memory

On Linux systems, monitoring this volume of files exceeds default fs.inotify.max_user_watches limits (typically 8,192). On Windows, NTFS directory tree scans experience path lookup latency due to master file table (MFT) fragmentation.

Performance Benchmarks: Filesystem Traversal vs Embedded SQLite

To quantify this boundary, we benchmarked cold directory scans, link resolution, and full-text search across two storage topologies containing 100,000 documents (average size: 2.4 KB, 15 backlinks per document):

OperationFlat Markdown (Filesystem)SQLite 3.45 (Single DB File)Performance Delta
Cold Index Initialization4,210 ms (100k open/read)118 ms (B-tree index scan)35x faster
Exact Title Lookup14.2 ms (path traversal)0.04 ms (indexed primary key)355x faster
Full-Text Search (Term Query)1,840 ms (multithreaded ripgrep)6.2 ms (FTS5 BM25 search)296x faster
Bulk Link Graph Traversal3,120 ms (AST parse in RAM)24 ms (Recursive SQL CTE)130x faster
Memory Overhead at Rest240 MB (in-memory AST graph)18 MB (page cache footprint)13x reduction

The data confirms that while flat files excel at portability, querying relationships between records requires relational indexing primitives.

The Hybrid Architecture: Markdown on Disk, SQLite in the Sidecar

The solution to this dilemma is not abandoning Markdown in favor of a binary blob, but deploying an embedded SQLite database as an ephemeral, rebuildable sidecar cache.

Vault Root Directory:
├── 2026-09-15.md            <── Source of Truth (Git-tracked, human-readable)
├── projects/
│   └── compiler.md          <── Source of Truth
└── .bosun/
    └── cache.sqlite         <── Ephemeral Index (Derived state, gitignored)

In this hybrid topology:

  1. The file system remains the definitive source of truth: Edits are written directly to .md files on disk.
  2. The database acts as an operational acceleration cache: A background daemon monitors file modification timestamps (mtime) and updates an internal SQLite schema containing document bodies, frontmatter attributes, and directed graph edges.
  3. Cache invalidation is deterministic: If the SQLite file is corrupted or deleted, the system regenerates it from scratch by reading the directory tree.
-- Ephemeral Sidecar Schema Definition
CREATE TABLE documents (
    id TEXT PRIMARY KEY,
    filepath TEXT UNIQUE NOT NULL,
    mtime INTEGER NOT NULL,
    content_hash BLOB NOT NULL,
    frontmatter_json TEXT
);
 
CREATE TABLE edges (
    source_id TEXT REFERENCES documents(id) ON DELETE CASCADE,
    target_id TEXT NOT NULL,
    anchor TEXT,
    PRIMARY KEY (source_id, target_id, anchor)
);
 
CREATE VIRTUAL TABLE fts_content USING fts5(
    document_id UNINDEXED,
    body_text,
    tokenize = 'porter unicode61'
);
 
-- Optimization and verification command
PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA integrity_check;
  • Directus Target: freemydata
  • Garden Source Reference: DAT-1003 - Deterministic Lossless Conversion, BSN-1002 - Bosun Schema Specifications, MOC - Data Liberation Workbenches, MOC - The Plain-Text Longevity Standard, MOC - Bosun PKM Tools

Graph View