Stax Digital Garden

BSN-1023 - Why SQLite Projections Must Be Disposable and Rebuildable

3 min read

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

Why SQLite Projections Must Be Disposable and Rebuildable

Summary

Treating relational database engines as the primary source of truth for personal notes creates unavoidable state drift between disk files and database records. When external tools, Git branches, or backup utilities modify notes directly on disk, traditional databases desynchronize or overwrite user edits. Bosun PKM resolves this tension by declaring files on disk authoritative and enforcing a strict architectural invariant: SQLite projections are 100% disposable and deterministically rebuildable.

The Authority Invariant

Local-first software architectures face a dilemma when pairing text files with relational query engines:

The Source-of-Truth Dualism:
Approach A (Database as Truth):
  - Fast relational queries
  - External edits break database consistency
  - Synchronization conflicts require database-level resolution
 
Approach B (Disk Files as Truth, SQLite as Disposable Projection):
  - Plain UTF-8 Markdown files remain authoritative
  - Zero lock-in: standard UNIX tools (grep, find, git) function natively
  - SQLite database can be deleted at any moment with ZERO data loss

Under the Bosun PKM storage specification, deleting projection.db is an unprivileged, safe operation. The engine treats the SQLite database as an ephemeral read cache. If the database file is missing, corrupt, or out of sync, the runtime executes a cold rebuild directly from disk without prompting the user.

Projection Schema Architecture

The disposable projection lives in the operating system’s platform application-data directory (e.g. ~/.local/share/bosun/lockers/<locker_id>/projection.db), isolated from the user’s primary note tree. This isolation prevents heavy WAL write buffers from triggering external cloud file sync loops.

-- Ephemeral index of note frontmatter and hashes
CREATE TABLE notes_index (
    path TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    content_hash TEXT NOT NULL,
    frontmatter_json TEXT,
    indexed_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
 
-- Audit ledger for file watcher deduplication
CREATE TABLE dedup_ledger (
    content_hash TEXT PRIMARY KEY,
    first_seen_path TEXT NOT NULL,
    first_seen_op_id TEXT NOT NULL,
    duplicate_count INTEGER DEFAULT 1,
    last_seen_at DATETIME DEFAULT CURRENT_TIMESTAMP
);
 
-- Inverted index for fast bidirectional link traversal
CREATE TABLE link_edges (
    source_path TEXT NOT NULL,
    target_slug TEXT NOT NULL,
    link_type TEXT NOT NULL,
    PRIMARY KEY (source_path, target_slug),
    FOREIGN KEY (source_path) REFERENCES notes_index(path) ON DELETE CASCADE
);
 
CREATE INDEX idx_link_target ON link_edges(target_slug);

Notice the absence of synthetic auto-increment integer IDs for primary keys. Relational entities are keyed strictly by canonical POSIX relative paths (path) or cryptographic content digests (content_hash). This ensures that rebuilding the database produces deterministic relational identifiers that match across disparate machines.

Benchmark: Rebuilding 50,000 Notes

Rebuilding a disposable index must be fast enough to avoid disrupting user workflows on startup. The rebuild pipeline uses multi-threaded directory walking paired with batched SQLite transactions:

# Rebuild benchmark execution command
bosun storage rebuild --locker-id lkr_01j7m8x4k2 --timing-profile

Performance measurements across a 50,000-note vault on an NVMe storage bus:

Stage of Rebuild PipelineProcessing StrategyWall-Clock DurationThroughput Rate
Directory TraversalParallel walkdir across 8 worker threads142 ms352,100 files/sec
Frontmatter & Hash ScanStreaming UTF-8 block parser + BLAKE3/SHA-2561,480 ms33,780 notes/sec
Relational Batch InsertionSingle SQLite transaction with 5,000-row chunks620 ms80,640 records/sec
Link Graph IndexingIn-memory edge set aggregation into link_edges158 ms316,400 edges/sec
Total Rebuild TimeCold start from zero database state2,400 ms20,833 notes/sec

A complete index reconstruction across 50,000 files completes in 2.4 seconds, confirming that retaining permanent database state is an unnecessary operational risk.

  • Directus Target: bosun-pkm
  • Garden Source Reference: MOC - Bosun PKM Tools, MOC - Bosun PKM Engine, MOC - The Plain-Text Longevity Standard, [BSN-1003 - Building the Bidirectional Graph](BSN-1003 - Building the Bidirectional Graph)

Graph View