Loomdraft: SQLite, Plain Markdown, and the Local-First Model

Local-first software is a set of tradeoffs, not a moral position. You give up effortless sync across devices and gain complete ownership of your data. You give up the server's ability to index everything for you and gain the guarantee that your words exist independently of any company's continued operation.

Loomdraft makes those tradeoffs consciously. This post is about the specific technical decisions that implement the local-first model, and where they required more work than the cloud alternative would have.

01. PLAIN FILES AS THE CANONICAL FORMAT

The project directory is the database. This is a deliberate choice with a specific failure mode in mind: vendor lock-in through proprietary format.

Scrivener stores projects in a binary .scriv bundle. Notion's export is lossy. Obsidian's format is close to plain Markdown but depends on specific plugin behaviors for some features. If any of these companies shut down tomorrow, recovery ranges from difficult to impossible.

Loomdraft's canonical format is a folder of .md files with YAML frontmatter. The application reads and writes this format directly. The SQLite database at .app/index.db is explicitly a derivative artifact, a cache built from the source files, always rebuildable from scratch.

# .app/index.db is a derived index, not source of truth
# Proof: delete it and run:
 
SELECT count(*) FROM documents;  -- 0, it's gone
 
# Run rebuild:
invoke("rebuild_index", { projectPath })
 
SELECT count(*) FROM documents;  -- 47, back

The practical consequence: you can open a Loomdraft project in VS Code, edit the Markdown directly, and the next time you open the project in the app, your changes are reflected. The app detects file modification timestamps on startup and reconciles any external edits.

02. THE SQLITE FTS5 INDEX

Full-text search across a long-form project requires an index. Scanning every file on every keystroke would be too slow, and a proper search engine like Elasticsearch would be absurdly heavy for a desktop app. SQLite's FTS5 extension is exactly the right size.

The schema is minimal:

CREATE TABLE documents (
    id TEXT PRIMARY KEY,        -- relative path from project root
    title TEXT NOT NULL,
    type TEXT NOT NULL,
    content TEXT NOT NULL,
    modified_at INTEGER NOT NULL,
    wordcount INTEGER DEFAULT 0
);
 
CREATE VIRTUAL TABLE documents_fts USING fts5(
    title,
    content,
    content='documents',
    content_rowid='rowid',
    tokenize='unicode61 remove_diacritics 2'
);

The content= parameter makes documents_fts a content table, which means it stores tokens but retrieves actual content from documents on query. Storage is more efficient and the index stays automatically in sync when the base table is updated via triggers.

Search queries use BM25 ranking (FTS5's default) with a boost on title matches:

// src-tauri/src/search.rs
pub fn search(conn: &Connection, query: &str) -> Result<Vec<SearchResult>> {
    let sql = "
        SELECT
            d.id,
            d.title,
            d.type,
            snippet(documents_fts, 1, '<mark>', '</mark>', '...', 20) AS excerpt,
            bm25(documents_fts, 3.0, 1.0) AS rank
        FROM documents_fts
        JOIN documents d ON documents_fts.rowid = d.rowid
        WHERE documents_fts MATCH ?1
        ORDER BY rank
        LIMIT 50
    ";
    // bm25 column weights: title = 3x, content = 1x
    let mut stmt = conn.prepare(sql)?;
    let results = stmt.query_map([query], |row| {
        Ok(SearchResult {
            id: row.get(0)?,
            title: row.get(1)?,
            doc_type: row.get(2)?,
            excerpt: row.get(3)?,
        })
    })?
    .collect::<Result<Vec<_>, _>>()?;
    Ok(results)
}

The snippet() function returns context around the match with configurable highlight markers. The frontend renders these as styled HTML, with <mark> elements picking up a blue background from the design system.

03. THE BACKUP SYSTEM

Every save writes a backup. The backup path is derived from the source file path and a timestamp:

// src-tauri/src/backup.rs
pub fn write_backup(
    project_path: &Path,
    relative_path: &str,
    content: &str,
) -> Result<()> {
    let stem = Path::new(relative_path)
        .file_stem()
        .and_then(|s| s.to_str())
        .ok_or("invalid path")?;
 
    let timestamp = SystemTime::now()
        .duration_since(UNIX_EPOCH)?
        .as_secs();
 
    let backup_name = format!("{}-{}.md", stem, timestamp);
    let backup_dir = project_path
        .join(".app/backups")
        .join(relative_path)
        .parent()
        .unwrap()
        .to_owned();
 
    fs::create_dir_all(&backup_dir)?;
    fs::write(backup_dir.join(&backup_name), content)?;
 
    // Prune: keep most recent 20, delete the rest
    prune_backups(&backup_dir, stem, 20)?;
    Ok(())
}

Backups are browsable from the UI as a version timeline. Each entry shows a timestamp and the word count delta since the previous version. Restoration replaces the current document content with the backup version and triggers a new save. The restored version becomes the new head, and the restoration is itself backed up.

04. WHAT LOCAL-FIRST ACTUALLY COSTS

The honest answer is index management.

In a cloud-first application, the server owns the index. Full-text search, backlinks, word counts, document relationships are all computed once and served to all clients. The application developer writes a few API calls.

In a local-first application, every client builds and maintains its own index. Loomdraft handles several failure cases that a cloud app would never encounter.

Stale index after external edits. A writer opens the project folder in their text editor, edits three files, then returns to Loomdraft. The app compares modification timestamps on startup and rebuilds index entries for changed files. For large projects (200+ documents) this adds about 300ms to project load time.

Concurrent writes. On some platforms, the OS may deliver file change notifications while the app is writing. Loomdraft serializes all writes through a Tokio async runtime with a single-writer mutex on the project directory, avoiding races.

Index corruption. SQLite is remarkably resilient but .app/index.db can be corrupted by a force-quit during a write. The app checks database integrity on startup with PRAGMA integrity_check and offers to rebuild from source files if corruption is detected.

None of these problems are hard. But they're problems you own entirely, with no support from a platform.

The application is open source under MIT. The full source is at github.com/H3kk3/Loomdraft and the live demo runs at h3kk3.github.io/Loomdraft.