This page details the "Smarter retrieval" tentative card on Roxy's status page. It explains why plain word search will eventually fail Bob quietly, and the three small upgrades — all inside Bob's existing Supabase — that fix it.
Status (Roxy’s report, August 16, 2026): Phases 1–2 shipped. The gold test set is frozen; author/date columns are indexed (author populated on 27 of 78 documents, no invented people); and 999 passages are embedded via openai/text-embedding-3-small, dedupe holding (re-apply inserted 0). Hybrid search is built but not the default — live answers still use word search until Phases 3–4 turn hybrid on and calibrate the honest-miss threshold. No new database, no new service, pennies of embedding cost. The plan below is the build being followed.
Today, when Bob asks Roxy a question, Roxy finds documents by matching the words in the question against the words in the library (full-text search). This works well when the words line up — a ticker, a company name, an author's phrase.
It fails silently when they don't. If Bob asks about "datacenter power constraints" and the newsletter said "grid interconnect queues," word search finds nothing. Roxy then honestly reports a miss — on material the library actually contains. The answer looks trustworthy and is wrong about coverage. That is the most dangerous failure a research librarian can have, because nobody notices it happening.
Two smaller problems ride along: a search hit today returns a whole newsletter (thousands of words) instead of the paragraph that matters, and there is no clean way to ask time-and-author questions like "what was this writer saying in June versus now?"
Alongside the existing word search, store an embedding for each passage — a numeric "fingerprint" of what the passage is about. Two passages about the same idea get similar fingerprints even when they use different words. At question time, Roxy runs both searches:
Supabase supports this natively via the pgvector extension — one column and one index on the existing tables. No second database, no external search service.
The central risk — adding meaning search changes what a "miss" is. Today word search misses by returning zero rows: nothing matched, so Roxy says "not in the library," and that honest-miss guarantee is cheap and reliable. Vector search never returns zero rows — it always returns the least-distant passage. So the moment embeddings go in, an honest miss stops being "FTS found nothing" and becomes "the top hit's similarity is below a threshold." That threshold must be calibrated and validated against known out-of-corpus questions before this ships, or hybrid search will start dressing genuine misses up as near-hits — the exact failure this plan exists to prevent, reintroduced from the other side. This is the plan's central technical risk, not an acceptance checkbox.
Pin the embedding model and version, and store it alongside each vector. Changing embedding models later means re-embedding the whole corpus; recording the model per row now makes that a controlled migration instead of a silent inconsistency between old and new fingerprints.
Store each document's text as ordered passages (roughly paragraph-sized) that point back to their parent document. Benefits:
The schema already anticipates this (documents → document_chunks); this fills that layer in for the Substack corpus. Chunks carry an ordinal and parent-document reference so full context is always one join away.
Chunking and embedding are one unit of work, not two phases: passages are embedded at the chunk grain, so the migration that creates chunks is the same migration that embeds them. Author/date metadata (part 3) is the only genuinely independent piece — it is nearly free and can land first on its own.
Investment research is dated claims by named people. Publisher, author, published date, title, and source URL should be queryable columns (most already arrive free in the mail headers), so that questions like these are real database queries, not text-matching luck:
| Part | Change | Cost |
|---|---|---|
| Author/date metadata | Ensure existing columns are populated and used as retrieval filters | Mostly free — data already arrives in mail headers |
| Chunking | Small migration filling the existing document_chunks layer; re-chunk current corpus | One-time script; cheapest now while the corpus is small |
| Hybrid search | pgvector column + index; embed passages at ingest; merge FTS + vector results at query time | Pennies to embed the current corpus; negligible ongoing |
This is a staged, reversible build. Nothing is a big-bang cutover: word search stays live and primary the entire way, each new part is added alongside it, and Roxy advances to the next phase only after she has run her own tests and shown the receipts. A phase that fails its gate rolls back to the previous phase with no loss — the corpus and the working FTS path are never at risk.
Roxy proves it — with receipts, not claims. At every gate Roxy runs the test herself against Bob's live Supabase and reports the actual evidence: the SQL she ran, the row or chunk returned, the verbatim cited passage with author/date/link, and the similarity score where relevant. "It worked" is not a pass. A pass is a result James (or Bob) can read and re-run. Roxy's own summary is never the proof; the returned row is.
Before any schema change, Roxy assembles and freezes a small fixed evaluation set drawn from the real corpus. This set is the yardstick for every later phase, so it is built once, on the FTS-only system, and not edited afterward:
Roxy records this set (question, expected source or "out-of-corpus," expected author/date) as a small fixture in Bob's Supabase or the rebuild kit, and captures the current FTS-only result for each as the before/after baseline.
| Phase | What Roxy builds | How Roxy proves it (test) | Gate to advance |
|---|---|---|---|
| 1. Author/date metadata | Populate and index the queryable columns (author, publisher, published_at, title, source_url) on the existing documents table from the mail headers. Additive only — no retrieval change yet. |
Roxy runs each author + date-window question from the gold set as a real SQL query and returns exactly the expected document set — no more, no fewer — with author and date shown per row. | All gold author/date queries return the expected set. FTS answers unchanged. |
| 2. Chunk & embed (one migration) | Fill the existing document_chunks layer: split each document into ordered, paragraph-sized passages with an ordinal and parent-document reference, and embed each chunk (pinned model + version stored per row). FTS still queries whole documents. |
Roxy shows a citation resolving to a single passage (verbatim text + parent doc + ordinal), then re-runs the ingest on the same corpus and shows row counts are unchanged at both document and chunk level. | Citations resolve to a passage, not a whole post; re-ingest is a verified no-op (dedupe holds at document and chunk level). |
| 3. Hybrid query + threshold calibration | Add the vector search alongside FTS and merge results (reciprocal-rank fusion). Sweep the honest-miss similarity threshold against the gold out-of-corpus questions and record the chosen cutoff. | Roxy runs the full gold set through hybrid retrieval: vocabulary-mismatch questions now retrieve the mapped source and cite it; exact-term questions still resolve (no regression); out-of-corpus questions return "not in the library" — no near-miss passage dressed up as an answer. She reports the score of the top hit for each out-of-corpus miss to show it fell below the cutoff. | Every gold-set category passes, including zero out-of-corpus false hits. This is the hard gate — see the acceptance suite below. |
| 4. Make hybrid the default | Flip hybrid retrieval on as Roxy's standing search path; FTS remains as the exact-term component inside it. | Roxy re-runs the full acceptance suite once more on the live default path and confirms the before/after baseline improved on vocabulary-mismatch questions with no regression elsewhere. | Acceptance suite green on the live path. Build complete. |
Rollback: because every phase is additive and FTS stays live, rollback is always "stop using the new part." Phase 1 metadata is inert until queried. Phases 2–3 add tables/columns and a parallel query path that can be switched off, leaving the proven FTS answer path exactly as it is today. No phase deletes or rewrites the corpus.
This is the fixed suite Roxy must pass — run against the frozen gold set, reported with receipts — before hybrid retrieval becomes her default. It is the concrete form of the gates above:
Not now. At about 74 documents, word search plus a capable model is still good enough. Cited answers are proven. The right trigger is either of:
Build order: author/date metadata first — free, independent, and immediately useful. Then chunk-and-embed as a single migration; embeddings are computed at the chunk grain, so splitting them into a separate later phase is artificial. Chunking is cheapest while the corpus is small, so if a build decision comes, do it before the library holds hundreds of documents.