Building a Local-First Knowledge Base with SQLite FTS5 and Markdown

The Problem with Cloud Knowledge Bases

In 2024, Omnivore shut down. It had 40,000 GitHub stars. Sixty days after announcing its sunsetting, it was gone - along with every bookmark, highlight, and note its users had stored.

This wasn't an anomaly. It was the inevitable outcome of cloud-dependent knowledge tools:

For developers who curate hundreds of bookmarks over years, data longevity matters more than fancy features. Geneziz's architecture is built around this principle.

The Storage Layer: Markdown + YAML Frontmatter

Every item in your Geneziz knowledge base is a plain Markdown file with a YAML frontmatter header:

---
title: React Performance Optimization Techniques
type: tool
date_added: 2026-05-15
source: https://dev.to/react-perf
tags:
  - react
  - performance
  - frontend
stars: 1420
via: @dan_abramov
---

# React Performance Optimization Techniques

## Content here...

Why Markdown?

Interoperability. Markdown is the most widely supported markup format on Earth. Every editor, IDE, and viewer can read it. If Geneziz ever disappears, your knowledge files are still readable - no proprietary format lock-in.

Git-friendly. Markdown diffs are human-readable. When you edit a tool's description or add tags, git diff shows you what changed, not a JSON blob.

AI-friendly. LLMs are trained on more Markdown than any format except code. Feeding your knowledge to an AI assistant is trivial when it's already in structured text.

The Frontmatter Schema

The YAML block between the --- delimiters carries structured metadata that Geneziz uses for sorting, filtering, and display:

interface ViewerIndexEntry {
  file: string;        // path relative to knowledge/
  path: string;        // URL slug
  title: string;       // display name
  type: 'tool' | 'article';
  date_added: string;  // ISO date
  source: string;      // original URL
  tags: string[];       // searchable tags
  stars: number;        // GitHub star count (for tools)
  via: string;          // who shared it
  // Enrichment fields (computed at index time):
  display_title: string;
  category: string;
  priority: number;
  rating: number;
}

This schema is deliberately flat - no nested objects, no arrays of complex types. It makes the data easy to query, easy to migrate, and easy to inspect with a text editor.

The Search Layer: SQLite FTS5

Full-text search is where most knowledge bases fail. Either they don't have it at all (grep through files), or they use an external service (Elasticsearch, Meilisearch, Algolia) that requires a running server.

Geneziz uses SQLite FTS5 - the same database engine that powers every iPhone and Android device. Here's why:

What is FTS5?

FTS5 (Full-Text Search 5) is SQLite's built-in search extension. It creates a tokenized inverted index of your content, enabling:

• Instant prefix/phrase search ("react perf" matches "React Performance")
• Boolean operators (AND, OR, NOT)
• Relevance ranking with BM25-like scoring
• Near-instant results even on 100k+ documents

How Geneziz Builds the Index

When you run geneziz index, here's what happens under the hood:

1. Scan knowledge/tools/*.md and knowledge/articles/*.md
2. Parse YAML frontmatter → structured entries
3. Enrich each entry:
   - Generate display_title (cleaned, shortened)
   - Classify category from tags/content
   - Compute priority & rating scores
4. Write viewer-index.json (camelCase, React-ready)
5. Create .state/search.db (SQLite + FTS5 virtual table):
   - CREATE VIRTUAL TABLE USING fts5(...)
   - INSERT all entries with title + description + tags + content preview

The resulting search.db is typically under 5MB even for thousands of entries. It opens instantly - no server to start, no indexing delay.

Query Example

When you run geneziz search "react hooks", the flow is:

# 1. Open SQLite connection to .state/search.db
# 2. SELECT * FROM fts5_main WHERE knowledge MATCH 'react hooks'
# 3. Return ranked results with highlighted snippets
# 4. Format for CLI or web display

FTS5 handles tokenization, stemming (English), and ranking automatically. No need for a separate tokenizer service or relevance tuning pipeline.

The Viewer Index: camelCase for the Web

One architectural choice worth discussing: Geneziz writes its index in camelCase, not snake_case.

{
  "displayTitle": "React Perf Techniques",
  "dateAdded": "2026-05-15",
  "category": "frontend",
  "priority": 8,
  "rating": 4.7
}

Why? Because the consumer is a React web app written in TypeScript/JavaScript. Keeping the index in camelCase means zero transformation layer - the frontend reads the JSON and renders directly. No snake_to_camel conversion, no mismatch risk.

The Python backend generates camelCase via _to_camel_dict() utility. It's a deliberate choice: shape the data for the consumer, not the producer.

Atomic Writes: Crash Safety

Knowledge files use atomic write patterns (write-to-temp + rename) to prevent corruption:

def atomic_write(path: str, content: str) -> None:
    tmp = path + '.tmp'
    with open(tmp, 'w', encoding='utf-8') as f:
        f.write(content)
    os.replace(tmp, path)  # Atomic on POSIX, near-atomic on Windows

If the process crashes mid-write, you get either the old file or the new file - never a half-written corrupt file. This pattern is used everywhere: bookmarks.md, viewer-index.json, state files.

The Complete Data Flow

X.com / GitHub
    │
    ▼
geneziz fetch / geneziz sync
    │
    ▼
Raw data → AI processing (optional)
    │
    ▼
Markdown files + YAML frontmatter
    │  (knowledge/tools/*.md)
    │  (knowledge/articles/*.md)
    │
    ▼
geneziz index
    │
    ├──→ viewer-index.json (camelCase, enriched)
    └──→ .state/search.db (SQLite FTS5)
    │
    ▼
Web viewer (React) reads both files
    │
    ▼
User searches, browses, reads articles

Every step in this pipeline is inspectable with a text editor. You can open viewer-index.json and see exactly what the web app sees. You can open search.db with any SQLite browser and run queries. You can open any .md file and read your notes.

Why This Architecture Wins for Developers

1. Zero Dependencies at Runtime

No Elasticsearch cluster. No Redis cache. No background worker process. The search DB is a single SQLite file that opens in milliseconds. The entire knowledge base works offline.

2. Git-Native

Since everything is Markdown + JSON, your knowledge base is a git repository. Version history, branching, PR reviews - all the tooling you already use for code.

3. AI-Ready

LLMs can read Markdown natively. The MCP server feeds your knowledge to Claude/GPT/Cursor without any format conversion. When you ask "what did I save about Rust?", the answer comes directly from your files.

4. Migration-Proof

Plain text doesn't rot. A Markdown file from 2020 works identically in 2030. There's no schema migration needed, no ORM version conflicts, no "we upgraded and now your old data is incompatible."

The Tradeoffs

This architecture isn't magic. It has intentional limitations:

• No collaborative editing - one writer at a time (fine for personal KBs)
• No real-time sync - manual geneziz fetch then geneziz process (intentional, not a bug)
• Search is English-only - FTS5 doesn't handle CJK tokenization well (acceptable for most developer audiences)
• Large datasets - if you have 100k+ entries, FTS5 gets slower (but ChromaDB vector search is included with every license to handle meaning-based lookups at scale)

These aren't bugs. They're design constraints that keep the system simple, fast, and owned.

Sharing Notes

This post is part of the Geneziz blog series. If you found it useful, share it with a developer who's building their own knowledge base.