| name | sql-manything |
| description | A* source-code search over SQL-ManyThing SQLite index. Four canonical SQL templates (DISCOVER→TRACE_DEPS→EXTRACT→EXTRACT_BLOCK) + Abstraction Frame with budget tracking. No substr guessing, no whole-file reads. |
| version | 5.4.0 |
| author | Hermes Agent |
| license | MIT |
| metadata | {"hermes":{"tags":["code-search","sqlite","fts5","ast","a-star","source-index"],"related_skills":[]}} |
SQL-ManyThing
Indexes a source tree into a SQLite DB. Query model: Abstraction Frame → DISCOVER → EXTRACT → EXTRACT_BLOCK, with optional TRACE_DEPS for candidate disambiguation.
Reading order is execution order. Start at §0, follow steps in sequence. Do not jump to SQL templates before writing a Frame.
§0 When to Use
Use when:
- A project has
.srcidx/source.db (if not, see §1.3 to create one). Query via /manything/<project>/source.db.
- The user asks for code search, tracing, implementation lookup, or architecture inspection.
- You need precise, offset-based extraction without whole-file reads.
Do NOT use when:
- No index exists for the project.
- You're looking at <100 files total —
code_search / code_extract is faster.
- The target is logs, config, markdown, or non-code text — use
search_files / read_file.
- You need a single function from a known file —
code_extract is 1 call.
§1 Pre-flight — Know Your DB Before You Query
Before ANY query, check what tables are available. Not every project is fully enriched.
1.1 Schema check
sqlite3 /manything/<project>/source.db "
SELECT name FROM sqlite_master
WHERE type='table' AND name IN ('files','files_fts','v_enriched','enrich_file_deps','enrich_file_refs','enrich_depth_segments')
ORDER BY name;"
| Tables present | Capabilities |
|---|
files + files_fts only | FTS5 search only. No v_enriched — skip EXTRACT/EXTRACT_BLOCK. Use files.content or fall back to read_file. |
+ v_enriched | Full DISCOVER → EXTRACT → EXTRACT_BLOCK chain. |
+ enrich_file_deps | TRACE_DEPS available. |
+ enrich_depth_segments | block_content_full + scope_end_offset for complete body extraction. |
+ enrich_file_refs (no deps) | Raw #include/import strings — query refs table directly. |
1.2 File count
sqlite3 /manything/<project>/source.db "SELECT count(*) AS file_count FROM files;"
| File count | Strategy |
|---|
| <10K | Fast LIKE scans OK |
| 10K–50K | Prefer FTS5 for discovery; narrow LIKE with file_path |
| >50K | FTS5 only for discovery; NEVER run unfiltered block_content LIKE '%X%' without file_path |
1.3 Setup: Phase 1 → 2 → 3
A fully enriched project goes through three phases. After Phase 1 each phase is optional — stop when you have the tables you need (§1.1).
Phase 1 — Create the index (references/phase1/phase1-setup.md):
python3 scripts/phase1/manything_build_db.py /path/to/project
This creates .srcidx/source.db with files + files_fts. Enough for FTS5-only search.
Phase 2 — Enrich with structure + dependencies (references/phase2/enrich-covercheck-workflow.md):
python3 scripts/phase2/enrich_depth_segments.py /path/to/project --batch 500
python3 scripts/phase2/enrich_file_refs.py /path/to/project --batch 500
python3 scripts/phase2/flatten_file_deps.py /path/to/project
python3 scripts/phase2/create_enriched_view.py /path/to/project
This adds v_enriched, enrich_file_deps, enrich_file_refs, enrich_depth_segments — the full DISCOVER→EXTRACT→EXTRACT_BLOCK chain. If re-running on a previously enriched DB, see references/old-format-cleanup.md.
Optional enrichments (not needed for most queries):
enrich_cymbal.py — symbol definitions via cymbal CLI (Python/Go/JS only; needs cymbal binary)enrich_graphify.py — AST graph for Python + Markdown (does not cover TSX/JSX)enrich_java_build.py — Java-specific
On Windows, scripts/phase2/run_phase2_universal_windows.bat runs all 4 steps. On Linux/WSL/macOS, run the Python scripts directly as shown above.
Phase 3 — Install the sqlite3 wrapper (enables /manything/ paths + :trace):
python3 scripts/phase3/install.py
./install.sh
This places a sqlite3 wrapper in ~/.local/bin/ that intercepts /manything/<project>/source.db and :trace. On Windows, install.py also copies sqlite3-real.exe and creates a .cmd wrapper. Ensure ~/.local/bin is before /usr/bin in PATH.
After Phase 3, register the project:
echo 'MANYTHING_<project>="/path/to/project"' >> ~/.hermes/manything/aliases.sh
Then proceed with §1.1 schema check.
See references/phase3/phase3-design-rationale.md for architecture details.
1.4 Trace — search past queries (:trace)
The sqlite3 wrapper logs every /manything/ query to ~/.hermes/manything/query_log.db. Use :trace as a virtual database name to search past queries — it resolves to the global query log automatically.
Search by project + keyword:
sqlite3 :trace "
SELECT id, sql_text, tag FROM query_trace
WHERE project = '<project>'
AND sql_text LIKE '%<keyword>%'
ORDER BY id DESC LIMIT 10;"
Tag a useful query pattern (agent discovers a reusable SQL shape):
INSERT INTO query_notes(log_id, note, tag, created_at)
VALUES (<id>, 'brief description of the pattern', '<short_tag>', strftime('%s','now'));
Tagged queries accumulate over time — future agents can SELECT * FROM query_trace WHERE tag IS NOT NULL to discover proven patterns without repeating discovery work. This is how the system gets faster and deeper with use.
§2 Quick Start — Minimal Path (4 queries)
Goal: find and extract SubstrateToonBSDF in UE 5.8.
Step 0 — Pre-flight (already done: ue58 has all tables, 89K files, >50K → FTS5 only).
Step 1 — Write the Frame:
═══ A* BUDGET FRAME #1 ═══
QUERY: UE5.8 Substrate Toon BSDF — shader implementation entry point
LAYERS:
shader: SubstrateToonBSDF.ush — BSDF eval + integrate functions
DISCOVER: FTS5 MATCH 'SubstrateToonBSDF'
EXTRACT: v_enriched depth≤1 structure map
EXTRACT_BLOCK: SubstrateEvaluateToon body → TERMINAL
Step 2 — DISCOVER:
SELECT f.path, rank FROM files_fts, files f
WHERE files_fts MATCH 'SubstrateToonBSDF'
AND files_fts.rowid = f.id AND f.path NOT LIKE '%test%'
ORDER BY rank LIMIT 10;
Step 3 — EXTRACT (probe structure):
SELECT depth_level, start_offset,
length(block_content) AS bytes,
length(block_content_full) AS full_bytes,
substr(block_content, 1, 100) AS preview
FROM v_enriched
WHERE file_path = 'Shaders/Private/Substrate/SubstrateToonBSDF.ush'
AND depth_level <= 1
ORDER BY depth_level, start_offset;
Step 4 — EXTRACT_BLOCK (terminal):
SELECT length(block_content_full) AS full_len FROM v_enriched
WHERE file_path = 'Shaders/Private/Substrate/SubstrateToonBSDF.ush'
AND block_content LIKE '%SubstrateEvaluateToon%'
AND depth_level = 1;
SELECT block_content_full FROM v_enriched
WHERE file_path = 'Shaders/Private/Substrate/SubstrateToonBSDF.ush'
AND depth_level = 1 AND start_offset = <from_EXTRACT>;
That's it. 1 Frame + 3 queries (2 probes + 1 terminal) = 4 queries total for a single-file question. Multi-layer investigations scale linearly: ~3-4 queries per layer.
§3 Abstraction Frame — Write Before ANY SQL
The Frame is mandatory. Skipping it is the #1 cause of budget waste. Going straight to DISCOVER without a Frame fragments attention and collapses layers.
3.1 Template
═══ A* BUDGET FRAME #<N> ═══
QUERY: <one sentence — what are we looking for>
LAYERS:
<label>: <role description>
DISCOVER: candidate files via FTS5 MATCH
TRACE_DEPS: [optional] validate candidates via deps
EXTRACT: read file structure map via v_enriched
EXTRACT_BLOCK: retrieve block_content_full → TERMINAL
NO: anti-pattern (e.g. "no whole-file read")
3.2 Rules
- Every layer = DISCOVER → (TRACE_DEPS when helpful) → EXTRACT → EXTRACT_BLOCK. Never skip to EXTRACT_BLOCK without a PROBE first.
- EXTRACT_BLOCK always uses
block_content_full. block_content is for EXTRACT previews only.
- Total EXTRACT_BLOCK output ≤ 6000 chars per feature.
- Budget annotation on EVERY query —
[budget] L<N>-P/D/E: g=<N> above each SQL. Use -P for probes (DISCOVER/EXTRACT), -D for TRACE_DEPS, -E for EXTRACT_BLOCK.
- Goal test:
block_content_full returns complete target → stop. New question = new frame.
- Pruning: if h(n) too high (FTS5 rank >10, no LIKE hits) → downgrade (OR synonyms) or switch intent. Layer exceeds 12 queries → report unsearchable, move to next layer. Multi-layer investigations have independent budgets per layer; 40+ queries total is normal for full subsystem exploration.
- TRACE_DEPS is a search accelerator, not just a call-chain tool. Use when DISCOVER returns 2+ candidates. Skip only when deps table is empty or DISCOVER returned a single unambiguous hit.
3.3 Budget tracking format
→ [budget] L1-P: g=1 — FTS5 MATCH 'SubstrateToonBSDF' → SubstrateToonBSDF.ush (rank -9.2)
→ [budget] L1-E: g=2 — block_content_full depth=1 → 4821 bytes ✓
§4 Four Canonical Templates
Four templates. Variants exist for edge cases — keep the canonical shape as default.
4.1 DISCOVER — find candidate files via FTS5
SELECT f.path, rank
FROM files_fts, files f
WHERE files_fts MATCH '{QUERY}'
AND files_fts.rowid = f.id
AND f.path NOT LIKE '%test%'
ORDER BY rank LIMIT 15;
{QUERY} = FTS5 terms (space-separated, boolean operators OK).
4.2 TRACE_DEPS — disambiguate candidates via dependency graph
Deps are a search accelerator: use when DISCOVER returns 2+ candidates, or when you need to validate which file is the core target.
TRACE_DEPS is NOT a call-chain tracing tool. If the user asks "trace how X calls Y," write a Frame with DISCOVER→EXTRACT→EXTRACT_BLOCK per layer. TRACE_DEPS answers "which of these 3 files is the real implementation?" — not "what is the full call chain." For deep cross-file tracing, use §6 Dependency Chain Tracing as a reference, not as your primary search strategy.
SELECT f.path AS imported_file, d.depth
FROM enrich_file_deps d JOIN files f ON f.id = d.dep_file_id
WHERE d.file_id = (SELECT id FROM files WHERE path = '{FILE}')
AND d.direction = 'upstream'
ORDER BY d.depth;
SELECT f.path AS importer, d.depth
FROM enrich_file_deps d JOIN files f ON f.id = d.file_id
WHERE d.dep_file_id = (SELECT id FROM files WHERE path = '{FILE}')
AND d.direction = 'downstream'
ORDER BY d.depth;
{FILE} = candidate path from DISCOVER.
Heuristic interpretation:
| Signal | Meaning | Action |
|---|
| Many downstream deps (depth=1) | Core module, likely correct target | Proceed to EXTRACT |
| Few deps + high FTS5 rank | Leaf file; check upstream for real implementation | Switch to imported file |
| Upstream deps match your intent | Target is the dependency, not this file | Switch layer to that file |
| No deps (empty table) | Deps not enriched | Skip TRACE_DEPS, EXTRACT on top candidate |
Budget: TRACE_DEPS costs g=+1 per step (upstream + downstream). Limit to depth <= 2 — beyond that fan-out explodes.
Raw refs (use when resolved deps are sparse, common on C/C++):
SELECT target_raw, line_num
FROM enrich_file_refs
WHERE file_id = (SELECT id FROM files WHERE path = '{FILE}')
ORDER BY line_num;
4.3 EXTRACT — file structure map (PROBE before extraction)
SELECT depth_level, start_offset,
length(block_content) AS bytes,
length(block_content_full) AS full_bytes,
substr(block_content, 1, 100) AS preview
FROM v_enriched
WHERE file_path = '{FILE}'
AND depth_level <= {N}
ORDER BY depth_level, start_offset;
{FILE} = relative path from DISCOVER. {N} = max depth. Use bytes and preview to pick the right depth level and offset for EXTRACT_BLOCK.
Depth by language — check this before EXTRACT_BLOCK:
| Language family | depth=0 | depth=1 | depth=2 |
|---|
| Brace-based (JS,TS,Go,Rust,Java,C++,C#) | File envelope | Full function body | Nested blocks |
| Indent-based (Python,Ruby,YAML) | File envelope | Signature only | Function body |
- Brace: extract bodies at
depth=1.
- Python: extract bodies at
depth=2. depth=1 is signatures only.
- C++
.ush / .usf shader files: brace-based, depth=1 for function bodies.
Variant — find symbol across all files (EXPENSIVE, use only when FTS5 fails):
SELECT file_path, depth_level, start_offset,
length(block_content_full) AS full_bytes,
substr(block_content, 1, 100) AS preview
FROM v_enriched
WHERE block_content LIKE '%{SYMBOL}%'
AND depth_level <= {N}
ORDER BY depth_level, start_offset;
⚠️ On >50K-file indexes, NEVER run this without a file_path filter — it scans the entire VIEW and can time out.
4.4 EXTRACT_BLOCK — terminal extraction
SELECT block_content_full FROM v_enriched
WHERE file_path = '{FILE}'
AND depth_level = {N}
AND start_offset = {OFFSET}
LIMIT 1;
{FILE}, {N}, {OFFSET} all from EXTRACT probe. Never guess offset.
Variant — extract by symbol name (when offset unknown):
SELECT block_content_full FROM v_enriched
WHERE file_path = '{FILE}'
AND block_content LIKE '%{SYMBOL}%'
AND depth_level = {N}
ORDER BY start_offset LIMIT 1;
After extraction, ALWAYS check length(block_content_full). If <200 bytes for a function you know is large → split-signature edge case.
block_content vs block_content_full
| Column | Scope | Use for |
|---|
block_content | Immediate depth segment | EXTRACT previews only |
block_content_full | Full enclosing scope (to next same-or-shallower segment) | EXTRACT_BLOCK — always |
block_content_full eliminates manual stitching. One query gets the complete body.
Detect truncation without extracting: scope_end_offset - start_offset > end_offset - start_offset → block_content_full has more content.
Split-signature edge case (~15% of large functions)
Two causes:
- Multi-line signatures (Python params, TSX generics) — the def-line segment's
block_content_full is only the signature.
- Nested-brace split on large functions — a function body with 4000+ bytes may span multiple depth=1 segments because inner brace-blocks also get indexed.
block_content_full from the first depth=1 segment only reaches the next same-depth boundary.
Detection: full_bytes from EXTRACT probe looks plausible (1000-3000 bytes) but the function should be larger — check if the depth=0 parent has much larger full_bytes, or if multiple adjacent depth=1 segments share the same function name prefix in their previews.
Workaround for nested-brace split: use the depth=0 parent segment instead:
SELECT block_content_full FROM v_enriched
WHERE file_path = '{FILE}'
AND depth_level = 0 AND start_offset = {PARENT_OFFSET};
For multi-line signatures:
SELECT start_offset, length(block_content_full) AS full_len,
substr(block_content_full, 1, 80) AS head
FROM v_enriched
WHERE file_path = '{FILE}'
AND block_content LIKE '%def {FUNC}%'
AND depth_level = {N}
ORDER BY start_offset;
SELECT block_content_full FROM v_enriched
WHERE file_path = '{FILE}'
AND depth_level = {N}
AND start_offset = (
SELECT MIN(start_offset) FROM v_enriched ve2
WHERE ve2.file_path = v_enriched.file_path
AND ve2.depth_level = {N}
AND ve2.start_offset > {STEP1_OFFSET}
);
Escape hatch (very rare)
When block_content_full is truncated by SQLite/transport limits despite correct offsets:
SELECT substr(f.content, ve.start_offset + 1, ve.scope_end_offset - ve.start_offset)
FROM v_enriched ve JOIN files f ON f.id = ve.file_id
WHERE ve.file_path = '{FILE}' AND ve.start_offset = {OFFSET};
This is the ONLY valid reason to leave v_enriched for raw files.content. Offsets MUST come from v_enriched columns — never guess.
§5 Full-Stack Example (4 layers, 8 queries)
═══ A* BUDGET FRAME #1 ═══
QUERY: Dashboard TUI — how does the browser connect to the agent?
LAYERS:
frontend: xterm.js terminal + WebSocket client (ChatPage.tsx)
api: /api/pty WebSocket endpoint (web_server.py)
backend: PtyBridge PTY spawn (pty_bridge.py)
transport: tui_gateway WSTransport (ws.py)
Layer 1 — frontend:
SELECT f.path, rank FROM files_fts, files f
WHERE files_fts MATCH 'xterm Terminal ChatPage'
AND files_fts.rowid = f.id AND f.ext IN ('.ts','.tsx')
AND f.path NOT LIKE '%test%'
ORDER BY rank LIMIT 15;
SELECT block_content_full FROM v_enriched
WHERE file_path = 'web/src/pages/ChatPage.tsx'
AND block_content LIKE '%function ChatPage%'
AND depth_level = 0
ORDER BY start_offset LIMIT 1;
Layer 2 — API endpoint:
SELECT file_path, depth_level, length(block_content) AS bytes,
substr(block_content, 1, 80) AS preview
FROM v_enriched
WHERE block_content LIKE '%async def pty_ws%'
AND depth_level = 0;
SELECT block_content_full FROM v_enriched
WHERE file_path = 'hermes_cli/web_server.py'
AND block_content LIKE '%async def pty_ws%'
AND depth_level = 0
ORDER BY start_offset LIMIT 1;
Layer 3 — backend:
SELECT file_path, depth_level, length(block_content) AS bytes
FROM v_enriched
WHERE block_content LIKE '%class PtyBridge%'
AND depth_level <= 1;
SELECT block_content_full FROM v_enriched
WHERE file_path = 'hermes_cli/pty_bridge.py'
AND block_content LIKE '%def spawn%'
AND depth_level = 1
ORDER BY start_offset LIMIT 1;
Layer 4 — transport:
SELECT f.path, rank FROM files_fts, files f
WHERE files_fts MATCH 'handle_ws tui_gateway'
AND files_fts.rowid = f.id AND f.path NOT LIKE '%test%'
ORDER BY rank LIMIT 15;
SELECT block_content_full FROM v_enriched
WHERE file_path = 'tui_gateway/ws.py'
AND block_content LIKE '%class WSTransport%'
AND depth_level = 1
ORDER BY start_offset LIMIT 1;
→ ✓ goal: block_content_full returns complete body for all 4 layers (8 queries total).
§6 Dependency Chain Tracing (Reference)
For deep cross-file tracing, use enrich_file_deps and enrich_file_refs. For routine candidate disambiguation, use §4.2 TRACE_DEPS — this section is for complex multi-hop tracing.
Upstream deps (what does this file import?)
SELECT d.dep_file_id, f.path AS imported_file, d.depth
FROM enrich_file_deps d
JOIN files f ON f.id = d.dep_file_id
WHERE d.file_id = (SELECT id FROM files WHERE path = '{FILE}')
AND d.direction = 'upstream'
ORDER BY d.depth;
Downstream deps (what imports this file?)
SELECT d.file_id, f.path AS importer, d.depth
FROM enrich_file_deps d
JOIN files f ON f.id = d.file_id
WHERE d.dep_file_id = (SELECT id FROM files WHERE path = '{FILE}')
AND d.direction = 'downstream'
ORDER BY d.depth;
Cross-file references (symbol-level)
SELECT f.path, r.line_num, r.target_raw
FROM enrich_file_refs r
JOIN files f ON f.id = r.file_id
WHERE r.target_file_id = (SELECT id FROM files WHERE path = '{FILE}')
ORDER BY f.path, r.line_num;
BUG ALERT — JOIN direction: upstream joins files f ON f.id = d.dep_file_id with WHERE d.file_id = target. Downstream joins files f ON f.id = d.file_id with WHERE d.dep_file_id = target. Swapping these gives empty results.
Coverage note: On C/C++ projects, bare #include "file.h" patterns often fail path resolution. Basename fallback exists but resolved deps are always sparser than raw refs. enrich_file_refs.target_raw has full coverage; use it when resolved deps come up empty.
§7 Domain Knowledge Banks (references/)
Pre-indexed architectural summaries built from A* traversal. Load with skill_view(name="sql-manything", file_path="references/<name>.md") before querying a known domain — they cut discovery cost to near-zero.
See references/INDEX.md for full catalog.
§8 Common Violations (Checklist)
If something goes wrong, scan this list before debugging:
- Skipping the Frame. Write the Abstraction Frame before any SQL.
- No budget annotation. Every query needs
[budget] L<N>-P/D/E: g=<N>.
- Using
block_content for EXTRACT_BLOCK. Use block_content_full.
- Blind
substr(content, N, M). Offsets only from v_enriched columns.
path LIKE for function names. Symbols live in block_content.- depth=1 for Python bodies. Use depth=2; depth=1 is signature only.
- Skip PROBE → straight to EXTRACT_BLOCK. Always DISCOVER/EXTRACT first.
read_file after EXTRACT_BLOCK returned content. block_content is the answer.- Wrapping SQL in execute_code/Python.
sqlite3 CLI is the transport.
- Stitching depth segments manually.
block_content_full spans levels.
- Confusing layer budget with investigation budget. >12 queries per layer = unsearchable. Multi-layer investigations have independent budgets per layer.
- Not checking
length(block_content_full). <200 bytes = classic split-signature. But also watch for 1000-3000 bytes on a known-large function — nested-brace split means the depth=0 parent may have 2×+ the content. Always cross-check full_bytes against the EXTRACT preview's expected scope.
- Accepting degraded
block_content_full silently. Check scope_end_offset IS NULL if consistently truncated.
- Only checking deps for call chains. TRACE_DEPS is a search accelerator — use whenever DISCOVER returns 2+ candidates.
- Broad
block_content LIKE without file_path filter on large indexes. >50K files: always narrow with file_path from DISCOVER.
- FTS5 query with dots or special chars. FTS5 syntax errors on
. in search terms (e.g. MATCH '"file" query.ush'). Drop dots or use LIKE on pre-filtered files instead.
- Old table remnants from previous buggy versions. Before re-running Phase 2, check
sqlite_master for file_enrich, file_enrich_blocks, file_enrich_xref — these are old-format tables that won't be cleaned up by the new scripts and will clutter inspection output. Drop them first (see §1.0).
