openclaw - 💡(How to fix) Fix Feature Request: SQLite FTS index for wiki_search to improve synthesis query performance [1 participants]

Is your feature request related to a problem?

When the wiki vault grows (80+ synthesis pages), wiki_search scans Markdown files for matches. This works but becomes increasingly inefficient as an agent's knowledge base scales. Each search requires reading and parsing multiple .md files, consuming tool-call tokens and adding latency.

Additionally, session compaction and memory/main.sqlite are both lossy — compaction discards conversation detail, and the memory SQLite only indexes session transcripts and MEMORY.md (not wiki content). This means wiki is often the only durable layer that preserves decision context. When we need to trace why a decision was made, we have to pull the original conversation out of session files before compaction destroys it, then store it alongside the wiki. A structured, indexed wiki search layer would make this retrieval reliable instead of a race against compaction.

Describe the solution you'd like

Add an internal SQLite FTS5 index that wiki_search queries instead of scanning files. The index should auto-update when wiki_apply or other write operations modify synthesis pages.

Proposed behavior:

• On wiki compile or wiki_apply, update the FTS index with the page's title + body
• wiki_search queries the FTS index first, returns ranked results
• Falls back to file scan if index is stale or missing

Real-world usage: our SQLite companion approach

We run a production OpenClaw instance (daily cron tasks, 5 Ghost CMS sites, Telegram delivery) with 90 synthesis pages, 28 decision records (DECs), and 59 claims. To work around wiki_search limitations and the compaction/memory loss problem, we built a companion SQLite database (wiki-memory.db, ~124 KB).

The compaction problem (real data loss)

Out of 28 decision records, 3 had their original conversation context destroyed by session compaction before we could preserve them:

DEC-260426-02  exchange_status=lost  (Claude Code diagnostic discussion)
DEC-260426-03  exchange_status=lost  (wiki lint rule alignment)
DEC-260426-04  exchange_status=lost  (wiki-memory.db architecture decision)

These decisions still have summaries, but the reasoning and back-and-forth that led to them is gone forever. We now capture the original exchange at DEC creation time into the companion DB, before compaction can run. The remaining 25 DECs are either preserved (10, with full conversation) or none (15, legacy imports that never had conversations).

The memory/main.sqlite gap

memory/main.sqlite indexes 139 session files + 28 memory files = 1841 chunks. But it contains zero wiki content:

source    | files
----------|------
sessions  | 139
memory    | 28
wiki      | 0     ← gap

So memory_search can find "what did we discuss about Ghost" in old sessions, but cannot find "what is our confirmed Ghost CMS operational rule" in wiki syntheses — unless you use corpus=all, which bridges via file scanning (not indexed).

DB schema that solved both problems

-- Decisions with preserved conversations (survives compaction)
CREATE TABLE decisions (
    dec_id TEXT PRIMARY KEY,        -- DEC-YYMMDD-NN
    title TEXT NOT NULL,
    summary TEXT NOT NULL,
    exchange TEXT,                   -- original conversation, captured before compaction
    exchange_status TEXT NOT NULL,   -- preserved | lost | none
    origin TEXT DEFAULT 'conversation'  -- conversation | imported
);
CREATE VIRTUAL TABLE decisions_fts USING fts5(dec_id, title, summary, exchange);

-- Structured synthesis metadata (avoids parsing frontmatter YAML)
CREATE TABLE syntheses (
    synthesis_id TEXT PRIMARY KEY,
    title TEXT NOT NULL,
    status TEXT DEFAULT 'active',
    confidence REAL,
    source_ids TEXT               -- e.g. "wiki-memory:DEC-260408-10"
);

-- Provenance chain
CREATE TABLE claims (...);
CREATE TABLE evidence (...);

Query comparison

Token savings estimate

With ~3 wiki queries per day, the companion DB saves ~4200 input tokens/day (~756K tokens over 6 months). The real value is fewer tool-call round-trips and cleaner context windows.

Additional context

• SQLite FTS5 is zero-dependency on Linux/macOS (bundled with Python and Node sqlite3)
• The memory/main.sqlite already uses this pattern for session transcript search — but it does not index wiki content, creating a gap
• Wiki pages remain the source of truth; the index is a read-optimized cache
• memory_search(corpus=all) bridges wiki into memory search via corpus-supplement, but uses file scanning rather than indexed search
• Vault size at which this matters: ~50+ synthesis pages with active agent querying
• The compaction + memory loss problem makes wiki the critical durable knowledge layer, which further motivates efficient wiki search