pi-duckdb-search

DuckDB-powered session transcript search for the Pi coding agent.

Searches your Pi session JSONL files using three modes:

• BM25 keyword — exact term matching with Porter stemming
• Semantic — ONNX embeddings (all-MiniLM-L6-v2) with cosine similarity
• Hybrid — Reciprocal Rank Fusion combining both for the best results

Self-contained: no external APIs, no cloud accounts, no network after first model download (~23 MB).

Install

pi install npm:pi-duckdb-search

Tools

Five tools are registered, callable by the LLM during conversation:

How It Works

~/.pi/agent/sessions/**/*.jsonl
        │
        ▼  read_json_objects(format='newline_delimited', ignore_errors=true)
   DuckDB database (~47 MB at 137 sessions)
   ├── session_entries table (content_text + embedding FLOAT[384])
   ├── FTS index (Porter stemmer, BM25 ranking)
   └── _metadata table (entry_count, has_embeddings, last_built)
        │
        ▼  connection.run(sql, [params]) — prepared statements
   BM25:  fts_main_session_entries.match_bm25(entry_id, ?)
   Cosine: array_cosine_similarity(embedding, ?::FLOAT[384])
   Hybrid: RRF fusion (k=60)

The extension opens the database in read-only mode — no WAL, no locks. A standalone cron build script creates and maintains the database offline.

Setup

1. Install dependencies

cd ~/.pi/agent && npm install @duckdb/node-api @huggingface/transformers

2. Set up the cron build (recommended)

The cron script builds the database offline (FTS + embeddings) daily at 4am:

# Add to crontab:
0 4 * * * /path/to/node ~/.pi/agent/duckdb-search/duckdb-search-build.cjs >> ~/.pi/agent/duckdb-search/build.log 2>&1

Manual build (first time or after changes):

node ~/.pi/agent/duckdb-search/duckdb-search-build.cjs

First build takes ~4 minutes (includes 23 MB model download + ONNX inference on ~17K entries). Subsequent incremental builds take ~2 seconds if no new sessions.

3. Restart Pi

pi  # or /reload in an existing session

The extension auto-loads the pre-built database on first search. No rebuild on restart.

Performance

Content Extraction

content_text extracts from JSON message content arrays:

• text — assistant/user text
• thinking — reasoning blocks (captured since v2.2; 60% of previously "empty" entries had thinking content)
• name — tool call names

Truncated to 2000 characters. Only error responses with content: [] have no embedding.

Security

• All search queries use prepared statements (connection.run(sql, [params]) with ? placeholders)
• Database opened in read-only mode when pre-built by cron
• SQL injection resistant — tested with '; DROP TABLE -- payloads
• 34-assertion test suite covers injection, edge cases, and read-only enforcement

Test Suite

NODE_PATH=~/.pi/agent/node_modules node ~/.pi/agent/duckdb-search/duckdb-search-test.cjs

34 assertions across 12 test groups:

• Schema & content extraction
• BM25 keyword search
• Semantic cosine search
• Hybrid RRF search
• session_read by path + line
• Metadata persistence
• SQL injection resistance
• Edge cases (empty query, missing file, limit clamping, line beyond EOF)
• FTS overwrite without drop
• ignore_errors with malformed JSON
• Content truncation (5000 chars → 2000 chars)
• Read-only mode enforcement

Architecture Decisions

Configuration

Dependencies

Pi-supplied (declared as peerDependencies, not bundled):

• @earendil-works/pi-coding-agent
• typebox

Limitations

1. FTS full rebuild on incremental builds — DuckDB v1.5.6 FTS doesn't support incremental index updates. The cron script rebuilds FTS (~1 second) but preserves embeddings.
2. No snippet windowing — Snippets start from the beginning of content_text, not the matching term.
3. Active session excluded — Files modified in last 5 minutes are skipped to avoid indexing while JSONL is being written.
4. HNSW not used — Brute-force cosine is 13 ms at 17K vectors. HNSW would matter at 100K+ vectors but DuckDB's vss extension has experimental persistence (data loss risk).

License

MIT