Why SQLite + LanceDB (not one or the other)¶

ZettelForge uses two storage engines where most systems use one. SQLite handles structured data (notes, knowledge graph, entity index) with ACID guarantees. LanceDB handles vector search. This is a deliberate architectural choice, not accidental complexity.

You don't need to understand this page to use ZettelForge. Read it if you want to know why queries behave the way they do, or if you're evaluating whether this architecture fits your environment.

Prerequisites: Quickstart, Configuration reference.

The problem with one database¶

CTI analysis operates on two fundamentally different data types:

Structured intelligence — entities with typed relationships, confidence scores, temporal validity, and traceable provenance. "APT28 uses Cobalt Strike" is a typed relationship between an actor and a tool, with a confidence score and a source note ID.

Unstructured context — raw text, analyst notes, news articles, conversation logs. "The ransomware encrypted all files on the domain controller using AES-256" is a passage that needs to be retrievable by semantic similarity. It doesn't decompose cleanly into typed entities.

No single database handles both well:

Capability	Graph DB alone	Vector DB alone
Typed relationships	Strong	None
Multi-hop traversal	Strong	None
Semantic search	None	Strong
Unstructured text	Poor fit	Natural fit
Schema enforcement	Strong	None
Temporal validity	Via edge properties	Via metadata only
ACID writes	Strong (SQLite)	Append-only

ZettelForge assigns each capability to the engine that handles it well, then bridges the two through the entity index.

SQLite: the graph and note layer¶

SQLite is the default primary store (configured via storage.backend: sqlite or ZETTELFORGE_BACKEND=sqlite). It holds three tables:

notes — every ingested document or analyst note. Full text, metadata, TLP marking, confidence, timestamps.

kg_nodes and kg_edges — the knowledge graph. Nodes store (entity_type, entity_value) pairs with a JSON properties blob. Edges store directed relationships between nodes, with relationship, edge_type, and a note_id back-reference to the note that produced the edge.

-- kg_edges schema (src/zettelforge/sqlite_backend.py:95-110)
CREATE TABLE IF NOT EXISTS kg_edges (
    edge_id TEXT PRIMARY KEY,
    from_node_id TEXT NOT NULL,
    to_node_id TEXT NOT NULL,
    relationship TEXT NOT NULL,
    edge_type TEXT DEFAULT 'heuristic',
    note_id TEXT DEFAULT '',
    ...
    UNIQUE(from_node_id, to_node_id, relationship, note_id)
);

The note_id column on kg_edges is the bridge. Every relationship in the KG traces back to the note that asserted it.

entity_index — a fast lookup from (entity_type, entity_value) to a set of note_id values. When a note is ingested, entity extraction runs and writes one row per entity detected. A query for "all notes mentioning APT28" hits this table in O(1), then retrieves note content from the notes table or LanceDB.

Why SQLite specifically¶

SQLite was chosen for the graph layer for four reasons:

Zero operational overhead. No server, no port, no daemon, no connection pool. The database is a single file on disk. This fits the offline-first design intent: a CTI analyst in a disconnected environment can run ZettelForge with no infrastructure beyond a Python environment.
ACID guarantees without configuration. SQLite's journal mode provides crash-safe writes. A note ingestion either completes fully (notes row + kg_edges rows + entity_index rows) or rolls back entirely. Partial writes that corrupt the KG are not possible.
Adequate scale for the target workload. ZettelForge's CTI memory model is bounded: an analyst typically accumulates thousands to tens of thousands of notes over a career, not billions of rows. SQLite is a practical fit for that range and avoids a graph database server in the default OSS install. TypeDB's advantages (inference rules, schema-level constraints) matter when you need stricter STIX ontology enforcement or larger multi-tenant graph operations.
WAL mode compatibility with LanceDB's columnar I/O. Both backends write to the same data directory (~/.amem by default). SQLite WAL mode and LanceDB's columnar append pattern do not conflict under concurrent ingest load.

LanceDB: the vector layer¶

LanceDB holds vector embeddings of every note — 768-dimensional dense vectors produced by the fastembed nomic-ai/nomic-embed-text-v1.5-Q model (ONNX, runs in-process, no server).

When a query arrives, the VectorRetriever computes cosine similarity between the query embedding and the stored note vectors, returning the top-k closest matches. These are notes whose text is semantically close to the query, regardless of whether any structured entity in the KG explicitly links them to the query topic.

Why LanceDB specifically¶

Embedded deployment — no separate server process; runs in-process via the Python lancedb package.
Columnar storage — efficient for the append-heavy write pattern of note ingestion.
IVF_PQ indexing — accurate approximate nearest-neighbor search in the 768-dimensional embedding space.
Zero external infrastructure — pip install lancedb is the entire installation.

Local embeddings and configurable LLM¶

ZettelForge v2.7.0 keeps embeddings local by default and routes language-model work through the configured LLM provider:

Embeddings — fastembed runs nomic-ai/nomic-embed-text-v1.5-Q as an in-process ONNX model (768-dim, downloads on first use). No embedding server is required in the default configuration.

LLM — the source configuration defaults to Ollama (llm.provider: ollama, http://localhost:11434) with a 180-second timeout. The shipped source default still contains an unresolved Ollama model tag, so set llm.model explicitly to a model installed on your host, such as qwen2.5:3b. If the configured provider is unavailable, fact extraction and synthesis degrade instead of crashing: entity extraction keeps regex-matched CTI entities, and synthesize() returns a direct-answer result with confidence: 0.0.

You can substitute any supported provider (litellm, OpenAI, Anthropic, local llama-cpp-python) via llm.provider in config.yaml. See Configuration.

TypeDB: the optional graph layer¶

TypeDB 3.x is supported as an alternative to SQLite for the graph layer. You activate it by setting storage.backend: typedb and running a TypeDB server on localhost:1729 (configurable via typedb.host / typedb.port).

TypeDB's advantages over SQLite for graph storage:

STIX 2.1 schema enforcement — TypeDB's type system enforces that every node and edge conforms to the STIX ontology at write time. SQLite's schema is permissive by comparison.
Transitive inference — TypeDB computes alias chains transitively without storing explicit edges. If "Fancy Bear" → aliases → "APT28" and "Strontium" → aliases → "APT28", a TypeDB query for Strontium's aliases returns Fancy Bear automatically.
Multi-hop queries via TypeQL — complex traversals that would require multiple SQLite queries can often be expressed as a single TypeQL query with inference enabled.

TypeDB has operational costs that SQLite does not: it requires a running server (Docker or native install), does not support embedded deployment, and introduces a network hop on every KG write.

ThreatRecall.ai SaaS

ThreatRecall.ai uses TypeDB for its tenant-scoped knowledge graphs, providing STIX 2.1 schema enforcement and transitive alias resolution at scale without the operational overhead of self-hosting a TypeDB cluster.

Intent-guided blended retrieval¶

The BlendedRetriever does not run all retrievers at fixed weights. It consults the IntentClassifier first, which classifies the query into one of five intents, then applies an intent-specific traversal policy.

The five intents and their traversal policy weights¶

Intent	vector	entity_index	graph	temporal	top_k	Typical query
`FACTUAL`	0.3	0.7	0.2	0.0	3	"What CVE does APT28 exploit?"
`TEMPORAL`	0.2	0.1	0.2	0.5	5	"What changed since last week?"
`RELATIONAL`	0.2	0.2	0.5	0.1	10	"What infrastructure does APT28 use?"
`CAUSAL`	0.1	0.1	0.6	0.2	10	"Why did the attacker pivot to the DC?"
`EXPLORATORY`	0.5	0.2	0.2	0.1	10	"Tell me about APT28"

Source: src/zettelforge/intent_classifier.py:191-236.

These weights are applied during score normalization: each retriever produces a set of (note, raw_score) pairs, the scores are min-max normalized within each retriever's result set, and then multiplied by the intent weight before accumulation into the final blended score.

Why FACTUAL queries carry a non-zero graph weight¶

A zero graph weight for FACTUAL seems intuitive: if you want a specific fact, why traverse the graph? In practice, CTI factual queries frequently span a graph hop. "What CVE does APT28 exploit?" is factual in intent but requires an (APT28) -[targets]-> (CVE) traversal. Setting graph=0.2 allows graph results to supplement the entity index answer without dominating it.

Why misclassification degrades rather than breaks¶

A misclassified query does not produce a wrong answer; it produces a degraded answer because the wrong traversal policy routes to the wrong combination of retrievers. A relational query misclassified as FACTUAL returns entity index results instead of graph traversal. The answer may be plausible but incomplete. Classification accuracy is therefore a retrieval quality gate, not a correctness gate.

The classifier uses keyword matching first, with LLM fallback when keyword confidence is below threshold. See Retrieval policies for keyword lists and the merge algorithm.

The cost of two databases¶

The hybrid architecture adds operational complexity:

Two data stores to back up.
The entity index must stay consistent with both the notes table and the LanceDB vectors. ZettelForge manages this within the remember() call; manual direct writes to either store bypass the consistency guarantees.
Cache invalidation spans both systems.

ZettelForge mitigates the backup concern by collocating both stores in ~/.amem by default. A single directory backup captures the complete state of both SQLite and LanceDB.

The consistency concern is the real risk. All writes should go through MemoryManager.remember() or the web API (POST /api/remember), not direct database manipulation.