query before grep

The repo indexDB

Before migration 010, "find where the kit-hash drift gate lives" meant grep full-scans: the 296MB docs/docs mirror tree on one side, and the four local code corpora (workers/*/src, plugins/, crates/*/src, scripts/) completely unindexed on the other. Now the code corpora live in Postgres as fact_repo_files — full body text, a GIN FTS index, and a sha256 CDC column so re-indexing an unchanged file costs nothing — and the standing rule for subagents is query the index before reaching for grep.

The pipeline

Solid: the 010 pipeline — four corpora (declared in index-corpora.toml, enforced by a DB CHECK) → indexer --repo (sha CDC: unchanged sha = no re-upsert) → fact_repo_files → the two front doors (cargo run -p indexer -- --query and the mcp-server search_index tool, crates/mcp-server/src/server.rs:286) → subagents. Dashed: sibling stores that are deliberately not in this table — composition, not consolidation (see below).

What's in it — per-corpus rows

plugins

698

workers

122

scripts

crates

total

914

baked 2026-07-10 from psql -h 127.0.0.1 -U wbr -d subagentjobs -c "SELECT corpus,count(*) FROM fact_repo_files GROUP BY corpus"

plugins dominates because it holds every SKILL.md, README, memory file, and skill script; crates is small because Rust source concentrates into few files. The walker enforces the vendor-mode discipline for free: .gitignore respected, 512 KB size cap, binary/lock skip.

Querying it

RUSTC_WRAPPER='' cargo run -p indexer -- --query "drift gate kit hash" --corpus scripts

Results come back as {file_key, path, corpus, rank, line_spans, snippet} — rank is real ts_rank over the GIN index. Honest caveat on line_spans: the spans are heuristic term matches computed over the stored content (compute_line_spans, crates/durable-store/src/lib.rs:459-465), not tsvector positional data — good enough to jump to the right region of a file, not a guarantee that every span is a true FTS match.

Query results are cached in redis under idx:q:{sha256(corpus|query|limit)} with TTL 300 (REDIS_IDX_TTL, lib.rs:383). There is no invalidation hook — the indexer runs out-of-process — so the TTL is the only freshness bound. The redis tiering itself is its own page.

Composition, not consolidation

Two sibling stores are deliberately outside fact_repo_files, and the 010 migration header (crates/durable-store/migrations/postgres/010_repo_file_index.sql) writes the decision down "so nobody re-litigates it by accident":

docs/docs (9,454 md files, 296MB) — owned by sqlite FTS5 at crawlers/ragcache.py (front door: plugins/cwc-engineering/skills/docs-rag). Consolidating docs/docs into Postgres is an explicitly deferred decision, not an oversight.
vendors/ (22,323 files) — migration 004's fact_filesystem/dim_file_ast store paths and symbols only, no body text, for the .vendors.toml vendor repos.

The origin story — two latent bugs, found the honest way

The first-ever fresh-database vendor run (2026-07-10) exposed two bugs that had sat latent in the indexer the whole time it ran against an already-populated database:

FK ordering on fresh DBs: the dim_vendor row must be upserted before its files — fact_filesystem.vendor_key is a NOT NULL REFERENCES dim_vendor column (004_vendor_filesystem.sql:27), which an incremental run against existing rows never trips.
NULL-decoded RETURNING: upsert_file's RETURNING scalar decodes as Option<bool> — it is NULL for brand-new files, which previously errored post-INSERT and silently skipped ALL AST parsing: 22,323 files indexed, 0 AST rows, before the fix.

Both are told here because they shaped the design: the CDC rerun after the fix was verified cheap (indexed=0, everything skipped), which is the property the sha256 column exists to guarantee.