pal-e-docs

Concept: Database-Side Intelligence (Why tsvector)

concept-phase5-database-side-intelligence Doc

active concept
Project

The Problem

pal-e-docs stores 246 notes as HTML blobs. There is no search — not in the API, not in MCP tools, not in the frontend. When an AI agent needs to find knowledge, it does this:
  • list_notes(tags="sop,active") — get a list of slugs
  • get_note(slug=...) — fetch full HTML content (1-6KB per note)
  • Repeat 5-12 times, reading each document to determine relevance
  • Reason over all that content to answer the original question
This is like searching a library by pulling every book off the shelf and reading the first chapter. It works at 50 notes. At 246 it's painful. At 1,000 it's unusable. Every query scales linearly — more notes, more tokens, more calls, more cost.

The Insight: Let the Database Be Smart

The traditional approach would be to build search in the application layer — add a Python text extraction pipeline, build an inverted index in memory or Redis, write sync logic to keep it updated. That's a lot of moving parts to maintain.
Postgres already has a built-in full-text search engine. It's not a bolt-on — it's a first-class feature with 20+ years of refinement. The key components:
Component What it does Why it matters
<code>tsvector</code> A column type that stores a pre-computed, stemmed, normalized representation of text Search is instant — no parsing at query time
<code>tsquery</code> Parses search terms into a structured query with AND/OR/NOT logic and stemming "managing secrets" matches "secrets management" because both stem to the same roots
GIN index Generalized Inverted Index — maps each word to the rows that contain it O(1) lookup instead of scanning every row
Trigger A function that fires automatically on INSERT/UPDATE The app never has to think about search — write HTML, get searchability for free
<code>ts_rank()</code> Scores results by relevance, respecting weights (title &gt; content &gt; slug) Best matches come first
<code>ts_headline()</code> Extracts a snippet around the matching terms Agent sees <em>why</em> a note matched without fetching the whole document

The Pattern: Database-Side Intelligence

The architecture pattern here is: the database holds the intelligence, the application asks questions, the MCP tool exposes those questions to agents.
Each layer is thin:
  • Postgres — does the real work: tokenizes, stems, indexes, ranks, extracts snippets
  • API — translates HTTP params into a SQL query, returns JSON
  • MCP tool — translates tool params into an HTTP request
  • Agent — asks a question in natural language terms
The app doesn't extract text from HTML. The app doesn't build indexes. The app doesn't rank results. Postgres does all of that inside a trigger that fires automatically. The app's only job is to ask: "what matches this query?"

Why This Pattern Scales Into the Full Plan

This isn't just a Phase 5 decision — it's the architectural foundation for the entire Act 2 knowledge engine:
Phase Same pattern, different intelligence
<strong>Phase 5 — tsvector</strong> Database builds a keyword index via trigger. Agent asks "find notes containing these words." Deterministic, precise.
<strong>Phase 6 — pgvector</strong> Database stores embedding vectors. Agent asks "find notes <em>similar to</em> this concept." Fuzzy, semantic. Same thin-layer architecture — embeddings computed on write, similarity search on read.
<strong>Phase 7 — Block content</strong> Database stores typed blocks instead of HTML blobs. Agent asks "give me the Decisions table from this plan" — not the whole 6KB document. Same pattern: structured data in Postgres, thin query layer on top.
<strong>Phase 8 — MCP optimization</strong> Compound queries that combine keyword search + semantic search + block-level retrieval in a single call. The intelligence compounds because it's all in one database.
If we built search in Python, we'd have to rebuild for embeddings, rebuild again for blocks, and somehow coordinate across three separate systems. By putting the intelligence in Postgres, each phase adds to what's already there. One database, one trigger pipeline, one query engine — progressively smarter.

The Token Economics

The real payoff is what this does to AI agent efficiency:
Before (brute force) After (search-first)
Calls per lookup 12+ (list + get loop) 1 (search)
Tokens per lookup ~5,000-15,000 (full HTML blobs) ~200-500 (summaries + snippets)
Scaling Linear — more notes = more tokens Constant — more notes, same query cost
Relevance Agent decides (expensive reasoning) Database decides (ts_rank, free)
This is why the plan's vision says "80-90% token reduction." It's not an optimization — it's a fundamentally different access pattern. The agent stops reading documents and starts asking questions.

The Trigger Is the Key

The most important design decision is the Postgres trigger. When a note is created or updated:
  • The app writes html_content exactly as it does today — no code changes
  • The trigger fires automatically (BEFORE INSERT OR UPDATE)
  • The trigger strips HTML tags with regex, splits title/content/slug into weighted components
  • The trigger builds the tsvector and stores it in the search_vector column
The app never touches search_vector. It's server-managed. This means:
  • Zero application code to keep the index in sync
  • Zero risk of the index going stale
  • The existing create_note and update_note endpoints work unchanged
  • The existing MCP tools that write notes automatically make those notes searchable
The trigger is the invisible bridge between "note storage app" and "knowledge engine." Every note that exists today — and every note written in the future — becomes searchable without any writer knowing or caring about search.
  • phase-postgres-5-fulltext-search — the implementation phase this concept supports
  • plan-2026-02-26-tf-modularize-postgres — the parent plan (Act 2 vision)