BSN-1013 - Plain Text as a Durable Substrate

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

Plain Text as a Durable Substrate

Summary

Knowledge bases stored inside proprietary database tables lose their utility when software engines become obsolete. Binary file formats and complex object relational mappings introduce long-term serialization vulnerabilities across decades of storage.

Bosun pairs raw UTF-8 CommonMark files with embedded SQLite relational caches. The filesystem retains the canonical data payload, while SQLite functions as an ephemeral query acceleration layer that can be purged and regenerated without data loss.

The Dual-Layer Persistence Pattern

Flat files provide longevity, yet flat-file directories perform poorly when executing relational graph traversals or full-text searches across tens of thousands of notes. Scanning a folder of 40,000 files to resolve inbound backlinks creates unsustainable disk I/O latency.

To resolve this performance limitation, Bosun uses a dual-layer persistence pattern. The durable layer consists of Markdown files with structured YAML frontmatter. The transient layer consists of an embedded SQLite database configured with full-text search (FTS5) and foreign key constraints.

       Canonical Storage Layer                     Ephemeral Cache Layer
+------------------------------------+      +----------------------------------+
|  vault/01-Fleeting/note-01.md      | ---> | SQLite Cache (vault.db)          |
|  - UTF-8 CommonMark Body           |      | - documents (id, path, hash)     |
|  - Strict YAML Frontmatter Header  |      | - backlinks (source_id, target)  |
+------------------------------------+      | - fts_index (content, title)     |
                                            +----------------------------------+
                                                             |
                                                    Index Generation Pass
                                                             v
                                                    Disposable Artifact

If the SQLite database file corrupts or experiences schema divergence after an engine upgrade, the remediation procedure is deterministic: delete vault.db and execute an ingest sweep across the Markdown files.

Cache Relational Schema

The ephemeral SQLite schema models document paths, SHA-256 content digests, and parsed edge relations. Foreign keys map backlinks between distinct notes while handling orphan references gracefully.

PRAGMA journal_mode = WAL;
PRAGMA synchronous = NORMAL;
PRAGMA foreign_keys = ON;
 
CREATE TABLE documents (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    rel_path TEXT NOT NULL UNIQUE,
    title TEXT NOT NULL,
    frontmatter_json TEXT NOT NULL,
    sha256_hash TEXT NOT NULL,
    mtime_epoch INTEGER NOT NULL,
    word_count INTEGER NOT NULL
);
 
CREATE TABLE backlinks (
    id INTEGER PRIMARY KEY AUTOINCREMENT,
    source_doc_id INTEGER NOT NULL REFERENCES documents(id) ON DELETE CASCADE,
    target_slug TEXT NOT NULL,
    anchor_text TEXT,
    line_number INTEGER NOT NULL
);
 
CREATE VIRTUAL TABLE document_fts USING fts5(
    title,
    body,
    content='documents',
    content_rowid='id'
);
 
CREATE INDEX idx_backlinks_target ON backlinks(target_slug);
CREATE INDEX idx_documents_path ON documents(rel_path);

When an edit modifies an existing note, an in-memory difference check compares the document’s new hash against the stored sha256_hash. If unchanged, the parsing pass aborts immediately.

Storage Substrate Trade-Off Analysis

Evaluating storage models requires balancing accessibility against query efficiency. The table below details operational trade-offs across four storage engines evaluated during the Bosun benchmark runs.

Metric	Flat UTF-8 Markdown	SQLite FTS5 Cache	Embedded RocksDB	DuckDB Columnar
Durability Horizon	Decades (Standard ASCII/UTF-8)	High (Single-file binary)	Medium (SSTable format drift)	High (Analytical single file)
Human Inspectability	Direct terminal inspection (cat)	Requires sqlite3 CLI	Requires custom decoder	Requires DuckDB CLI
Backlink Lookup Latency	$O(N)$ linear file scan (~1.8s)	$O(1)$ index lookup (<0.4ms)	$O(\log N)$ LSM search (<0.6ms)	$O(1)$ indexed scan (<0.5ms)
Cold Rebuild Time	Source of truth (0s)	4.2s for 20,000 files	6.8s for 20,000 files	3.9s for 20,000 files
Concurrent Write Safety	POSIX file locking	WAL multi-reader single-writer	Multi-threaded compaction	Appender locks

The combination of flat files and an ephemeral SQLite cache provides microsecond query latencies while preserving total independence from specialized database runtimes.

Vault Cache Rebuilding Protocol

Rebuilding the ephemeral cache verifies frontmatter consistency, parses outgoing references, and populates the search tables.

pub fn rebuild_vault_index(vault_root: &Path, db: &rusqlite::Connection) -> Result<usize, EngineError> {
    let tx = db.unchecked_transaction()?;
    tx.execute("DELETE FROM backlinks;", [])?;
    tx.execute("DELETE FROM documents;", [])?;
 
    let mut processed_count = 0;
    for entry in walkdir::WalkDir::new(vault_root).into_iter().filter_map(|e| e.ok()) {
        if entry.path().extension().and_then(|s| s.to_str()) == Some("md") {
            let relative = entry.path().strip_prefix(vault_root)?;
            let raw_bytes = std::fs::read(entry.path())?;
            let parsed = parse_markdown_document(&raw_bytes)?;
            
            insert_document_record(&tx, relative, &parsed)?;
            insert_backlink_records(&tx, relative, &parsed.links)?;
            processed_count += 1;
        }
    }
    tx.commit()?;
    Ok(processed_count)
}

The Substrate Invariant

The architectural invariant governing document storage states that unlinking the SQLite cache file must never result in permanent data degradation. All relations, backlinks, and tags must remain completely recoverable from the plain-text headers.

Execute the cache regeneration command to verify database parity against the physical files on disk:

bosun-index rebuild --vault-path ./02\ Review/bosun-pkm --verify-parity

---

• Directus Target: bosunpkm-blog
• Garden Source Reference: MOC - Bosun PKM Engine, MOC - Bosun PKM Tools, MOC - The Plain-Text Longevity Standard