Skip to content

Why SQLite + LanceDB (not one or the other)

ZettelForge v2.8.0 uses SQLite as its primary store and LanceDB as its vector index. You get structured notes and relationships from SQLite, plus semantic search from LanceDB, without running a separate database server.

Read this page when you want to understand the trade-off. You do not need it to complete the Quickstart. For exact settings, use the Configuration reference.

One note serves two retrieval needs

Threat-intelligence memory combines two kinds of evidence:

  • Structured evidence includes notes, entities, directed relationships, confidence, TLP markings, timestamps, and source-note provenance.
  • Semantic evidence includes passages that mean something similar even when they use different words.

SQLite can store an embedding as data, but ordinary SQL indexes do not provide the vector-nearest-neighbor search that semantic recall needs. LanceDB can attach metadata to vectors, but ZettelForge needs stronger constraints and direct lookups for notes, graph edges, and entity-to-note mappings.

ZettelForge therefore gives each engine one clear role:

Engine Role in ZettelForge Why it fits
SQLite Primary record for notes, graph nodes and edges, entity mappings, and enrichment jobs Transactions, uniqueness constraints, indexed lookups, and embedded deployment
LanceDB Search index for note vectors, split into one table per domain Vector similarity search and embedded deployment

The split is architectural, not a packaging distinction. ZettelForge remains one self-hosted open-source product.

SQLite owns the record

MemoryManager constructs a SQLiteBackend when it starts. The backend creates these core tables:

  • notes stores the full note, its embedding, metadata, links, and provenance.
  • kg_nodes and kg_edges store the directed knowledge graph. Each edge can carry the note_id that supports it.
  • entity_index maps an entity type and value to the notes that mention it.
  • enrichment_jobs records deferred enrichment work.

SQLite enforces uniqueness for entity nodes, graph edges with the same provenance, and entity-to-note mappings. Those constraints prevent common duplicate-write problems without a separate graph service.

The backend enables write-ahead logging (WAL). WAL lets readers continue while a writer commits, which suits a local application that may ingest in the background while you query it. ZettelForge also sets SQLite synchronous=NORMAL: application crashes preserve committed transactions, but an operating-system crash can lose the latest transaction. This is a durability trade-off, not full power-loss protection.

ZettelForge creates a LanceDB table for each memory domain and writes a note ID, vector, and bounded search metadata into that table. During recall, LanceDB compares the query vector with stored note vectors. ZettelForge then resolves each matching ID back to the full note in SQLite.

This makes LanceDB an index rather than the authoritative note store. The default fastembed provider generates 768-dimensional nomic-ai/nomic-embed-text-v1.5-Q embeddings in process. The storage design does not depend on the configured LLM provider; language-model extraction and synthesis sit above the persistence layer.

Both engines run in the Python process. By default, SQLite writes zettelforge.db and LanceDB writes vectordb/ under ~/.amem; AMEM_DATA_DIR moves that shared parent directory.

A write crosses an intentional consistency boundary

When you call remember(), ZettelForge performs the fast-path writes in sequence:

  1. It constructs the note and embedding.
  2. It commits the note to SQLite.
  3. It indexes the vector in LanceDB.
  4. It writes entity mappings and graph edges to SQLite.
  5. It queues slower enrichment work.

These steps do not share one transaction. Each SQLite storage method commits independently, and LanceDB has its own write path. If LanceDB indexing fails after the first SQLite commit, the note remains in the primary store while semantic recall lacks that index entry. If a later graph write fails, the note can exist without every derived relationship.

That boundary is the main cost of the design. Send writes through MemoryManager.remember() or POST /api/remember so ZettelForge can update both engines. Direct edits to either database bypass the coordination path.

Recall blends vector and graph evidence

Recall does more than run a vector query. ZettelForge v2.8.0 follows this read path:

  1. IntentClassifier classifies the query as factual, temporal, relational, causal, or exploratory.
  2. VectorRetriever asks LanceDB for similar notes.
  3. GraphRetriever traverses SQLite graph edges from entities found in the query.
  4. BlendedRetriever min-max normalizes the vector and graph scores, applies the intent's vector and graph weights, and adds the two signals when both find the same note.
  5. Entity augmentation, causal traversal, filtering, and optional cross-encoder reranking refine the result.

The intent policy changes emphasis. For example, a relational query gives graph evidence more weight than vector evidence, while an exploratory query does the reverse. A classification error changes that mix, so it can make an answer incomplete even when every retrieved note is valid.

Retrieval-policy integration is in progress

The v2.8.0 policy map also defines numeric entity_index and temporal weights, but BlendedRetriever currently consumes only the vector and graph weights. Entity and temporal logic runs in separate stages instead of participating in the same weighted fusion. Treat the four-signal policy as an incomplete integration.

Why this trade-off is reasonable

The hybrid design gives you three practical advantages:

  • Local operation. SQLite, LanceDB, and the default embedding provider run without database or embedding servers.
  • Traceable retrieval. Vector hits resolve to full SQLite notes, while graph edges retain source-note provenance.
  • Independent repair. The primary record and semantic index have separate responsibilities, so an index problem does not erase the stored note.

You also accept three costs:

  • You must keep two engines consistent.
  • A complete backup must capture both zettelforge.db and vectordb/ from a stable write state.
  • Retrieval quality depends on entity extraction, intent classification, and vector indexing as well as the stored content.

For a self-hosted memory system, this favors simple operation and evidence traceability over a single, more ambitious storage engine.

TypeDB product boundary

TypeDB backend selection is in progress

ZettelForge v2.8.0 exposes backend and typedb configuration fields, but MemoryManager uses the storage factory that returns SQLiteBackend. Selecting TypeDB does not replace the primary SQLite path in this release. Do not treat the TypeDB fields as a working ZettelForge backend switch.

ThreatRecall.ai is the separate SaaS product. This page describes ZettelForge's self-hosted storage path and does not imply that both products share the same deployment architecture.