GAL-1006 - Local Storage Boundaries with SQLite and Open JSON

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

Local Storage Boundaries with SQLite and Open JSON

Summary

Why Galley stores vault entries on-disk in transparent, parseable formats rather than opaque proprietary databases or mandatory vendor cloud sync.

Your kitchen notes should outlive any single software service. Many commercial recipe applications trap your culinary archives in proprietary cloud databases, locking your personal collection behind subscription paywalls or exporting data only in lossy formats. When that company pivots or shuts down, decades of personal recipe tweaks disappear.

Galley enforces a local-first storage boundary. All data lives on your device in open formats: SQLite for transactional indexing and human-readable Markdown/JSON for cold export.

The Dual-Layer Storage Pattern

To balance fast querying with future-proof durability, Galley implements a dual-layer persistence model:

1. Transactional Layer (SQLite): Provides fast full-text search, relational ingredient queries, and atomic transactions during recipe edits.
2. Document Layer (Open JSON / CommonMark): Every recipe can be serialized directly into a portable Markdown card with structured frontmatter, ready to be read by any plain text editor.

Vault Storage Architecture
├── galley.db             <-- SQLite (FTS5 search, indexes, staging)
├── recipes/
│   ├── gal-0001.json     <-- Structured interchange format
│   ├── gal-0001.md       <-- Human-readable Markdown recipe card
│   └── gal-0002.md
└── attachments/          <-- Local images, no remote CDN dependencies
    └── roasted-duck.webp

This separation ensures that even if you choose to stop using Galley tomorrow, your recipes remain formatted as plain Markdown files organized on your local filesystem.

SQLite Configuration for Long-Term Durability

Galley configures SQLite with safe operational defaults designed to prevent corruption even during unexpected device shutdowns:

-- Enforce write-ahead logging for concurrent reads and crash safety
PRAGMA journal_mode = WAL;
 
-- Balance performance with immediate disk flush
PRAGMA synchronous = NORMAL;
 
-- Enforce foreign key constraints
PRAGMA foreign_keys = ON;
 
-- Store temporary tables in memory
PRAGMA temp_store = MEMORY;

With WAL mode enabled, readers never block writers, allowing background ingestion workers to process staged items without lagging the user interface.

Full-Text Search with FTS5

Finding recipes in a large personal archive requires responsive full-text querying across both ingredient lists and preparation steps. Galley builds an internal SQLite FTS5 index that tokenizes ingredient terms and culinary tags:

CREATE VIRTUAL TABLE recipe_fts USING fts5(
    title,
    ingredients,
    instructions,
    content='canonical_recipes',
    content_rowid='id'
);

Because FTS5 operates entirely on local disk pages, search queries return in under 2 milliseconds across collections containing thousands of detailed recipe entries.

Automated Vault Export and Backup Hygiene

In addition to live SQLite transactions, Galley maintains an export loop that syncs verified database rows back to the plain text file tree. Whenever a recipe record is modified, the local background handler writes an updated CommonMark file to disk.

This continuous projection means your standard operating system backup tools (such as Time Machine or rsync scripts) naturally capture complete, readable copies of your culinary notes without requiring specialized database dump utilities.

Storage Architecture Comparison

The following table contrasts Galley’s open storage with common proprietary alternatives:

Storage Characteristic	Cloud-Locked Recipe Apps	Galley Local Storage
Physical File Location	Remote vendor servers	User’s local file system
Offline Availability	Cached or unavailable	100% offline, zero network reliance
Inspection Tools	None (opaque API)	Standard SQLite CLI, text editors
Export Portability	Rate-limited or proprietary	Open CommonMark + JSON schema
Vendor Longevity Risk	High (service shutdown risk)	Zero (plain text files endure indefinitely)

By keeping files local and transparent, your kitchen records remain under your sovereignty forever.

---

• Directus Target: galley
• Garden Source Reference: Storage Engines, Data Sovereignty, Open Formats, MOC - Culinary & Domain Workspaces, MOC - The Kitchen Chaos Factor, MOC - Bosun PKM Tools