hypa-loca-memo-brid-fts

name	hypa-loca-memo-brid-fts
description	Use when turning Hypatia into a local external memory layer for Hermes or another agent, especially when you need a thin CLI bridge and must verify that FTS search actually indexes newly inserted knowledge. Also use when Hypatia search returns no results after successful writes.
tags	["hypatia","memory","sqlite","fts5","bridge","debugging"]
version	1

Goal

• Stand up a minimal reusable local bridge from agent workflows into Hypatia.
• Detect and fix the common failure mode where writes succeed but search returns nothing.

When to use

• You have Hypatia source locally and want practical reuse, not just repo study.
• You want a safe sidecar memory layer before integrating into a main agent system.
• knowledge-create or direct writes succeed, but search returns No results found.

Recommended approach

1. Build Hypatia first.
   • Repo example: ~/Downloads/repo-intake/hypatia
   • Run: cargo build --release
2. Create a thin wrapper script instead of editing the main agent first.
   • Good first commands:
     • add-note
     • search
     • get-note
   • Call the Hypatia binary via subprocess, parse stdout JSON where applicable, and emit stable JSON for the caller.
3. Verify the whole path end to end.
   • Create a note.
   • Read it back with knowledge-get.
   • Search for words from name, data, and tags.
4. If read works but search fails, inspect SQLite directly before guessing.
   • Check ~/.hypatia/default/index.sqlite.
   • Compare counts in docs_meta vs docs_fts.
   • Inspect recent rows in both tables.
5. If docs_meta has rows and docs_fts exists but MATCH finds nothing, patch Hypatia schema init to rebuild the FTS index.
6. Rebuild Hypatia and rerun the end-to-end verification.

Bridge template

• Implement a small Python wrapper with:
  • configurable binary path via HYPATIA_BIN
  • configurable default shelf via HERMES_HYPATIA_SHELF
  • commands for add-note, search, get-note
• Prefer a sidecar workspace such as:
  • ~/Downloads/hermes-hacks/hermes-hypatia-bridge/

Key debugging pattern

1. Write test note:
   • hypatia knowledge-create "Hermes bridge test" --data "Hypatia bridge working" --tags hermes,memory,bridge --shelf default
2. Confirm retrieval works:
   • hypatia knowledge-get "Hermes bridge test" --shelf default
3. If search fails:
   • hypatia search bridge --limit 10 --shelf default
   • Inspect SQLite table contents directly.
4. Use Python sqlite3 for direct inspection when needed:
   • count docs_meta
   • count docs_fts
   • inspect key, fts_key, fts_data, fts_tags
   • run MATCH queries directly

Actual fix

• File: src/storage/sqlite_store.rs
• In SqliteStore::init_schema(), after recreating the triggers, run:

self.conn
    .execute_batch("INSERT INTO docs_fts(docs_fts) VALUES('rebuild');")
    .map_err(StorageError::from)?;

Why this matters

• Hypatia recreates the FTS virtual table in schema init.
• Recreating the FTS table alone does not repopulate it from docs_meta.
• Result: writes may exist in docs_meta, but docs_fts MATCH ... returns no rows.
• The rebuild command repopulates the FTS index from the content table.

Verification checklist

• cargo build --release succeeds.
• knowledge-create succeeds.
• knowledge-get returns the inserted object.
• search finds terms from title/data/tags.
• Direct SQLite MATCH query also returns rows.

Pitfalls

• Do not assume write failure just because search is empty.
• Do not trust only the CLI search path; inspect the SQLite backing store directly.
• Do not integrate deeply into Hermes first; prove the sidecar bridge works end to end.
• If a repo claims FTS works, still verify after schema migrations or virtual table recreation.