Abstract & Executive Summary
For over a decade, software productivity tools followed an aggressive centralization paradigm, migrating user documents into multi-tenant cloud databases, proprietary JSON document stores, and subscription-gated SaaS platforms. While cloud architectures enabled real-time collaboration and simplified multi-device synchronization, they introduced catastrophic trade-offs: pervasive vendor lock-in, recurring subscription fatigue, unexpected service discontinuations, and systemic privacy exposure.
In response, a growing movement of systems engineers, researchers, and privacy-conscious professionals is embracing local-first software architecture—a design philosophy prioritizing data sovereignty, offline autonomy, and long-term file durability.
In this foundational technical analysis, we evaluate the architectural landscape of note-taking and knowledge management software. We analyze the seven core tenets of local-first computing formalized by Kleppmann et al., contrast the physical durability of POSIX filesystem Markdown against embedded SQLite databases, evaluate the trade-offs of conflict-free replicated data types (CRDTs), and detail the storage architecture powering Oyma.
1. The Seven Principles of Local-First Computing
In their seminal 2019 research paper, Local-First Software: You Own Your Data, in spite of the Cloud, Martin Kleppmann, Adam Wiggins, Peter van Hardenberg, and Mark McGranaghan established seven fundamental criteria that define local-first applications:
- No Spinning Wheels (All Operations are Local): User interactions read and write to local disk instantly without blocking on remote HTTP network roundtrips.
- Multi-Device Accessibility: Data can synchronize across multiple personal endpoints without requiring centralized server custody.
- Network Optionality: The application maintains 100% operational fidelity indefinitely while disconnected from the internet.
- Seamless Collaboration: Multi-user editing reconciles gracefully using mathematical convergence models.
- The Long Now (Decadal Durability): User files remain accessible and editable 20 to 50 years into the future without relying on the original vendor’s servers.
- Security and Privacy by Default: Data is stored locally under user-controlled encryption with zero unsolicited cloud egress.
- Ultimate User Ownership: Users retain unconditional custody of their data files and can inspect, copy, or migrate them using standard filesystem tools.
2. Storage Architecture Comparison: Files vs. Databases
The fundamental architectural division in modern knowledge software centers on the underlying persistence layer:
Comparative Storage Layers:
┌────────────────────────────────────────────────────────┐
│ Architecture A: Plain Filesystem Markdown │
│ • Directory of individual .md files │
│ • Ephemeral local SQLite index for search │
│ • Zero binary serialization overhead │
│ • 100% human-readable via cat, grep, or vim │
├────────────────────────────────────────────────────────┤
│ Architecture B: Embedded Binary Database (SQLite) │
│ • Single monolithic .sqlite or .realm file │
│ • Proprietary schema or Protocol Buffers │
│ • Requires vendor migration scripts to extract data │
│ • Single point of corruption risk │
├────────────────────────────────────────────────────────┤
│ Architecture C: Remote Cloud Document Store │
│ • DynamoDB / MongoDB / Firestore │
│ • Notes exist only as remote database records │
│ • Zero offline functionality without cache │
└────────────────────────────────────────────────────────┘2.1 The Durability Paradox of Plain Text
Plain text Markdown is deliberately minimalist. It lacks binary indexing, embedded relationship pointers, and structured relational constraints. Yet, this simplicity is its greatest engineering strength:
- Universal Tooling Compatibility: Plain text files can be searched, transformed, and backed up using fifty years of battle-tested Unix utilities (
grep,awk,rsync,git). - Zero Translation Friction: If a note-taking application disappears tomorrow, Markdown files require zero export conversion. They can be opened instantly in TextEdit, VS Code, Obsidian, or Neovim.
- Corrupt-Resistant Granularity: In a database architecture, a single corrupted disk sector or interrupted transaction can corrupt an entire multi-gigabyte
.sqlitefile. In a filesystem architecture, an interrupted write impacts only a single individual note, leaving the rest of the vault intact.
3. The Role of SQLite: Ephemeral Index vs. Authoritative Source
A frequent misconception is that local-first software must choose strictly between Markdown files and relational databases. In modern high-performance architectures, the optimal solution utilizes both—but with a strict hierarchy of authority.
3.1 The Derived Index Architecture of Oyma
In Oyma, plain CommonMark .md files on the local macOS disk are the sole authoritative source of truth. SQLite is utilized exclusively as an ephemeral, disposable search accelerator:
Authoritative Source vs. Disposable Cache:
[User Edits Note] ──(Atomic POSIX Write)──> [notes/architecture.md]
│
(File Watcher Event)
│
▼
[Ephemeral SQLite FTS5 Index]
│
(Instant ⌘O Full-Text Search)- On-Disk Primacy: If the SQLite index file is deleted or corrupted, Oyma completely regenerates the full-text search index from the Markdown files in under 3 seconds for a 10,000-note vault.
- Zero Proprietary Encodings: Notes never contain proprietary metadata headers, opaque binary tags, or encrypted fields.
- External Edit Safety: If you edit a Markdown file in Neovim or Obsidian while Oyma is running, macOS file system events (
FSEvents) detect the change and update the search index in real time.
4. Evaluation of Popular Knowledge Management Architectures
To assist software architects in evaluating data longevity, we assessed fifteen popular note-taking and documentation applications across local-first criteria:
| Application | Primary Persistence Engine | Works 100% Offline? | Plain Markdown Files? | Zero Cloud Dependency? | Long-Term Durability Score |
|---|---|---|---|---|---|
| Oyma | POSIX Filesystem (.md) + SQLite FTS5 | Yes (100%) | Yes | Yes (Local-only mode) | A+ (Decadal Durability) |
| Obsidian | POSIX Filesystem (.md) | Yes (100%) | Yes | Yes (Unless using Sync) | A+ (Decadal Durability) |
| Logseq | Filesystem (.md / .org) | Yes (100%) | Yes | Yes | A (Moving to DB in 2.0) |
| Bear | SQLite (Core Data / CloudKit) | Yes | No (Export required) | No (Apple iCloud lock) | B- (Proprietary SQLite) |
| Apple Notes | SQLite (Protobuf / CloudKit) | Yes | No (Single export) | No (iCloud lock) | C (Proprietary SQLite) |
| Notion | Cloud Database (PostgreSQL) | No | No (JSON/HTML export) | No (100% Cloud SaaS) | F (Complete Lock-In) |
| Granola | Cloud Database (Supabase) | No (Draft cache only) | No (Web portal) | No (100% Cloud SaaS) | F (Complete Lock-In) |
| Otter.ai | Cloud Database (AWS) | No | No (Web app only) | No (100% Cloud SaaS) | F (Complete Lock-In) |
5. Synchronization & Conflict Resolution: Files vs. CRDTs
A central challenge in local-first architecture is multi-device data synchronization. Two primary methodologies exist in the industry today:
5.1 Operational Transformation & CRDTs
Conflict-free Replicated Data Types (CRDTs) such as Yjs and Automerge treat documents as operational event logs, allowing character-level collaborative editing across network nodes without central coordination.
- Advantages: Enables Google Docs-style simultaneous multi-user typing without merge conflicts.
- Disadvantages: Significant memory overhead, file bloat from append-only tombstone records, and complex serialization formats that undermine plain text readability.
5.2 Atomic File-Based Synchronization
For personal knowledge management, atomic file synchronization (via Git, Syncthing, or macOS FileVault folders) provides superior operational stability:
- Documents remain pristine CommonMark files without operational transaction logs.
- Version history is tracked cleanly through standard Git commits.
- File-level conflicts are resolved using standard three-way visual merge tools rather than algorithmic guess-work.
6. Building for the Next Fifty Years
Software vendors inevitably face commercial pressures to pivot, alter pricing structures, introduce telemetry, or sunset legacy products. When your personal thoughts, client meeting records, and intellectual research are stored in proprietary cloud databases, you are effectively renting your own mind.
Adopting a local-first architecture built on plain Markdown files guarantees that:
- Your notes will remain readable in 2075 on any computing device capable of rendering ASCII or UTF-8 text.
- No remote server outage or corporate bankruptcy can deny access to your knowledge vault.
- Your sensitive spoken deliberations remain protected behind hardware encryption rather than corporate trust policies.
To start building a sovereign personal knowledge base, explore our getting started guide, read about our local SQLite search engine, or download our curated PKM Markdown templates.
7. Mathematical Foundations: CRDT Convergence vs. Flat Files
A fundamental architectural inquiry in local-first systems design is whether to structure documents using Conflict-free Replicated Data Types (CRDTs) or transparent plain-text filesystems.
Architectural Tradeoff:
CRDT Tree Structure (Automerge / Yjs):
┌────────────────────────────────────────────────────────┐
│ Character Insertion Nodes + Lamport Logical Clocks │
│ Advantage: Mathematically proven real-time convergence │
│ Disadvantage: Opaque binary bloat, CLI incompatibility │
└────────────────────────────────────────────────────────┘
POSIX APFS File Hierarchy (Oyma):
┌────────────────────────────────────────────────────────┐
│ UTF-8 CommonMark Document on Local Disk │
│ Advantage: 100% compatible with git, ripgrep, fzf, vim │
│ Advantage: Multi-decade durability with zero lock-in │
└────────────────────────────────────────────────────────┘While CRDT algorithms excel for multi-user collaborative word processing, personal notes and architectural meeting records do not require real-time character-by-character concurrent typing. Storing deliberations as discrete CommonMark files on local APFS storage ensures instant interoperability with Unix command-line utilities, version control via standard Git commits, and perpetual readability regardless of future software landscape shifts.
7.2 The Longevity Principle: Why Plain Text Outlives Software Platforms
Over the past three decades of personal computing, dozens of proprietary note-taking systems and productivity formats have vanished. From early desktop organizers to cloud-era knowledge bases, closed proprietary databases inevitably face deprecation, acquisition, or server shutdown.
The local-first philosophy is rooted in the Longevity Principle:
- Format Durability: Plain UTF-8 text structured with CommonMark is platform-agnostic and will remain readable for centuries.
- Tool Interchangeability: A vault composed of Markdown files can be opened in Obsidian today, queried with command-line tools like
grepandripgreptomorrow, and edited in any text editor that may exist in 2050. - Sovereign Custody: Your knowledge assets cannot be revoked by a terms-of-service update or held hostage behind a subscription paywall.
Frequently Asked Questions
Questions
What defines a true 'local-first' software application?
A local-first app guarantees that all user data is stored locally in open formats, works completely offline without network calls, and never holds data hostage behind remote servers.
Why is plain text Markdown superior to proprietary database storage for notes?
Markdown files are human-readable, portable across any operating system, verifiable via standard Unix command-line tools, and guaranteed to remain readable decades into the future.
How does SQLite FTS5 complement plain Markdown files?
A local SQLite FTS5 index acts as a fast, ephemeral search cache that can be regenerated on-demand from disk, delivering sub-millisecond queries without compromising file durability.
What happens to notes stored in cloud-first systems when a company shuts down?
Cloud-first notes are vulnerable to permanent loss or painful export friction when services discontinue operations, alter pricing, or deprecate proprietary APIs.
