# pgdocs-mcp — PostgreSQL 18 + PostGIS docs as an MCP tool

An [MCP](https://modelcontextprotocol.io) server that gives Claude Code (or any
MCP client) fast, local, ranked search over the
[PostgreSQL 18](https://www.postgresql.org/docs/18/index.html) and
[PostGIS 3.5](https://postgis.net/docs/manual-3.5/) manuals. Scrape the official
HTML once, index it locally with TF-IDF + fuzzy + exact matching, expose it over
MCP. **No API keys, no network calls at query time.**

Built by [FloreData](https://floredata.com). Companion code for the write-up
*"Stop Pasting Docs Into Claude. Build a Docs MCP Instead."*

## The four moving parts

| File | Role |
|------|------|
| [`scrape_docs.py`](scrape_docs.py) | BFS-crawls each manual, writes one markdown file per page (`pg__*.md`, `postgis__*.md`). The **only** source-aware part. |
| [`search_index.py`](search_index.py) | Generic TF-IDF + fuzzy + exact search over any directory of markdown. Pickled cache, auto-invalidated on change. |
| [`server.py`](server.py) | FastMCP server exposing `search_docs`, `get_function_signature`, and `pgdocs://` resources. |
| [`setup.sh`](setup.sh) | Installs deps, scrapes, smoke-tests, prints the `claude mcp add` line. |

The boundary between the scraper and everything downstream is the point: the
engine is source-agnostic. Point the `SOURCES` list in `scrape_docs.py` at a
different manual and the rest moves across untouched.

## Quick start

**Prerequisites:** Python 3.10+

```bash
git clone https://github.com/FloreData/pgdocs-mcp
cd pgdocs-mcp
./setup.sh
```

`setup.sh` creates a project-local `.venv`, installs into it, scrapes both
manuals (~5–10 min, resumable), smoke-tests, and prints a ready-to-paste
registration command that points at the venv's Python. It never touches your
global Python. Restart Claude Code afterwards and `pgdocs` appears under `/mcp`.

### Manual setup

Same steps, by hand — still in a venv:

```bash
python3 -m venv .venv
.venv/bin/pip install -e ".[scrape]"
.venv/bin/python scrape_docs.py     # ~5–10 min, resumable
claude mcp add pgdocs -- "$(pwd)/.venv/bin/python" "$(pwd)/server.py"
```

## Usage in Claude Code

```text
"Search PostgreSQL docs for window functions"
"What does ST_Intersects return?"          # signature extraction
"Show me pgdocs://docs/pg/sql-select"      # direct resource
"Search PostGIS docs for 'buffer'"
"Search pgdocs for 'index' but only PostgreSQL"   # source='pg'
```

### Search strategies

Default `hybrid` is right for almost everything:

- **`hybrid`** (default): all three combined, exact matches boosted — best for identifiers.
- **`semantic`**: TF-IDF cosine similarity — best for conceptual queries.
- **`fuzzy`**: typo-tolerant matching on titles/filenames.
- **`exact`**: literal substring count.

See [How the search works](#how-the-search-works) for what each ranker
actually does and when it wins.

## How the search works

[`search_index.py`](search_index.py) runs up to three rankers per query and
merges the results. Each one is good at a different kind of query, none needs
a model or the network, and the whole index is a pickle on disk. The quick
intuition:

| You type | Winning ranker | Why |
|----------|----------------|-----|
| `ST_Intersects` | exact | the literal token is on the page |
| `ST_Intersect` (typo) | fuzzy | closest page *title* within edit distance |
| `spatial buffer with negative distance` | semantic | no literal match anywhere; shared distinctive vocabulary |

### Exact: substring count

Lowercased substring search over full page content. Score is
`min(1.0, occurrences / 10)`, so a page that mentions the query ten or more
times saturates at 1.0. In `hybrid` mode exact scores get a **×1.5 boost**:
if you typed a real identifier, the page that literally contains it should
beat everything else, and does.

### Fuzzy: typo tolerance on titles only

[rapidfuzz](https://github.com/rapidfuzz/RapidFuzz) `WRatio` — a weighted
blend of Levenshtein-based similarity measures (full-string, best partial
window, token-sorted) — compared against page **titles and filenames only**,
never page content. That restriction is deliberate: titles are short and
few, so scoring all of them takes microseconds, and when you typo an
identifier (`ST_Intersect` for `ST_Intersects`) the thing you mistyped is
almost always the title of the page you wanted. Matches scoring under 60/100
are discarded; the rest are normalised to 0–1 and get a **×1.2 boost** in
`hybrid` mode.

### Semantic: TF-IDF cosine similarity

Every page is turned into a sparse vector of at most 5,000 weighted terms
(unigrams and bigrams, so `window function` is a term of its own). A term's
weight is its frequency in the page multiplied by its rarity across the
corpus — that's TF-IDF: words that appear on most pages (English stop words,
plus anything on more than 80% of pages, via `max_df=0.8`) weigh nearly
nothing, while distinctive terms dominate. The query is vectorised the same
way and pages are ranked by cosine similarity between the two vectors.
Scores under 0.05 are dropped as noise.

This is *lexical*, not neural: two words only match if they are literally
the same token. That's the point. For technical manuals the vocabulary is
controlled and consistent, so a concept query like
`spatial buffer with negative distance` lands on the `ST_Buffer` page
through the shared distinctive terms `buffer`, `negative`, and `distance`,
without an embedding model blurring `ST_GeomFromText` into
`ST_GeomFromGeoJSON`. A real ranking, with the noise floor visible:

```
search_docs("window function frame clause", strategy="semantic")
  tutorial-window   0.52   # the concept, explained
  functions-window  0.44   # the reference page
  sql-expressions   0.33   # the frame-clause grammar
  ...               <0.1   # noise
```

### The hybrid merge

All three rankers run, each page keeps its best score across them, and
`match_type` records which rankers agreed (e.g. `exact+semantic`). The
boosts (exact ×1.5, fuzzy ×1.2, semantic ×1.0) encode one opinion:
identifier hits should outrank thematic hits when both fire.

Snippets are picked per result by scanning the page for the line that best
fuzzy-matches the query, returning it with two lines of context and the
section heading it sits under — so the model sees the relevant paragraph,
not the top of the file.

The whole index (parsed pages, vectoriser, TF-IDF matrix) is pickled to
`.search_cache.pkl` and rebuilt automatically whenever any markdown file is
newer than the cache. Cold build over 1,832 files takes a few seconds;
warm queries are sub-50 ms.

## Development

```bash
.venv/bin/pip install -e ".[dev]"
.venv/bin/pytest test_pgdocs.py -v

# Re-scrape (resumable — already-scraped pages are skipped)
.venv/bin/python scrape_docs.py

# Force-rebuild one page: delete the file and re-run
rm docs/pg__sql-select.md && .venv/bin/python scrape_docs.py
```

## The scraped docs are not in this repo

`scrape_docs.py` downloads the PostgreSQL and PostGIS manuals into `docs/`
(gitignored) and pickles a search index. Those manuals belong to their
respective projects — **regenerate them locally with the scraper** rather than
expecting them in the repo. See [LICENSE](LICENSE): the code is MIT; the
documentation it fetches is not, and remains under its upstream terms.

## License

Code: [MIT](LICENSE). Scraped documentation is not included and remains under
its upstream license.