Storage Model

Storage Model

graft stores everything in one SQLite file per profile: ~/.graft/profiles/<name>/graft.db. WAL mode. Optionally encrypted with SQLCipher (set GRAFT_DB_KEY).

Pragmas

PRAGMA journal_mode  = WAL;       -- readers don't block writers
PRAGMA synchronous   = NORMAL;    -- safe + fast on WAL
PRAGMA foreign_keys  = ON;        -- cascades from node deletes
PRAGMA mmap_size     = 268435456; -- 256 MiB mmap window

Tables

nodes

┌──────────────┬─────────────────┬─────────────────────────────────────────────┐
│    Column    │      Type       │                    Notes                    │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ id           │ BLOB(16) PK     │ UUIDv7 — sortable by time, dense.           │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ content_hash │ BLOB(32) UNIQUE │ BLAKE3(title || body || sorted keywords).   │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ title        │ TEXT NOT NULL   │ Short headline.                             │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ body         │ TEXT NOT NULL   │ Full content.                               │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ author       │ TEXT NULL       │ Provenance.                                 │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ created_at   │ INT64           │ Unix epoch seconds.                         │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ expires_at   │ INT64           │ 0 = never.                                  │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ last_access  │ INT64           │ Updated on STRONG hit.                      │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ access_count │ INT64           │ Bumped on STRONG hit.                       │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ state        │ INT             │ 0=ACTIVE, 1=STALE, 2=SUPERSEDED.            │
├──────────────┼─────────────────┼─────────────────────────────────────────────┤
│ origin       │ INT             │ 0=LOCAL, 1=REMOTE, 2=PUSHED (profile sync). │
└──────────────┴─────────────────┴─────────────────────────────────────────────┘

Indexes: idx_nodes_hash (content dedup), idx_nodes_state (filter SUPERSEDED out of retrieval), idx_nodes_expires_at (consolidate), idx_nodes_origin (sync).

node_fts (VIRTUAL, FTS5)

Mirror of (title, body). Triggers on nodes keep it in sync. Tokenizer: unicode61 remove_diacritics 2. Used for BM25 ranking.

keywords

┌──────────────┬────────────────────────────┐
│    Column    │            Type            │
├──────────────┼────────────────────────────┤
│ id           │ INT PK                     │
├──────────────┼────────────────────────────┤
│ text         │ TEXT UNIQUE COLLATE NOCASE │
├──────────────┼────────────────────────────┤
│ canonical_id │ INT NULL FK (keywords.id)  │
├──────────────┼────────────────────────────┤
│ embedding    │ BLOB NULL                  │
└──────────────┴────────────────────────────┘

canonical_id and embedding are reserved for future dedup / synonym work. Unused today.

node_keywords

Linking table. PK (node_id, keyword_id). Cascades on node delete.

edges

┌────────────┬─────────────┬─────────────────────────────────────────────────────┐
│   Column   │    Type     │                        Notes                        │
├────────────┼─────────────┼─────────────────────────────────────────────────────┤
│ src        │ BLOB(16) FK │ Source node.                                        │
├────────────┼─────────────┼─────────────────────────────────────────────────────┤
│ dst        │ BLOB(16) FK │ Destination node.                                   │
├────────────┼─────────────┼─────────────────────────────────────────────────────┤
│ kind       │ INT         │ 0=KEYWORD, 1=SEMANTIC, 2=CONTRADICTS, 3=SUPERSEDES. │
├────────────┼─────────────┼─────────────────────────────────────────────────────┤
│ keyword_id │ INT NULL FK │ Set only for KEYWORD kind.                          │
├────────────┼─────────────┼─────────────────────────────────────────────────────┤
│ weight     │ REAL        │ Typically [0, 1], normalized.                       │
└────────────┴─────────────┴─────────────────────────────────────────────────────┘

Unique: (src, dst, kind, COALESCE(keyword_id, -1)). Indexes on src and dst.

similarity_samples

(ts INT64, kind INT, cosine REAL). Kind 0 = insert_topk, kind 1 = query_top1. Powers graft stats percentile output.

node_vec (sqlite-vec VIRTUAL TABLE)

CREATE VIRTUAL TABLE node_vec USING vec0(
  id BLOB PRIMARY KEY,
  embedding FLOAT[1024]
);

Cosine top-k via vec_distance_cosine(). Falls back to a regular (id BLOB, embedding BLOB) table if sqlite-vec isn't loaded — same correctness, just slower top-k.

Migrations

Tracked via PRAGMA user_version. Each migration is idempotent.

- v2: summary→title, detail→body; added author, expires_at; rebuilt FTS5 + triggers.
- v3: added origin column to nodes + index. Pure ALTER TABLE ADD COLUMN — online, no table rebuild.

A newer daemon upgrading an older DB applies migrations in sequence before accepting connections.

Idempotency

Inserts hash (title || '\0' || body || '\0' || sorted_keywords) with BLAKE3, store in content_hash UNIQUE. A duplicate insert returns the existing id with duplicate: true and pays zero embedding cost (the
check happens before encoding).

Supersession

insert --supersedes <old_id> atomically:
1. Inserts the new node.
2. Sets old.state = SUPERSEDED.
3. Creates a SUPERSEDES edge from new → old.

Retrieval filters state != ACTIVE by default. The viewer shows superseded nodes in muted gray with the SUPERSEDES edge in red, so the history stays visible.

Encryption at rest

Build with SQLCipher and set GRAFT_DB_KEY env var. The daemon applies PRAGMA key = '<key>' and PRAGMA cipher_compatibility = 4 at open. Without SQLCipher in the build, the env var is a no-op (logged at
startup).

Backup

Two options:

1. Live backup: sqlite3 graft.db ".backup '/path/to/backup.db'" — online, safe with WAL.
2. Cold copy: stop the daemon (pkill graftd), copy graft.db + graft.db-wal + graft.db-shm together, restart.