Making Eight Years of Blogging Searchable

I have been publishing on Ghost since October 2018. That is 677 posts, and until this week I had no good way to ask questions about any of it. I could search my own site, which matches titles. I could remember roughly what I wrote and when, which works for the last few months and fails completely for 2019.

What I wanted was to ask “what have I written about X” and get a real answer drawn from the full text of every post. Ghost offers an API but no MCP endpoint, so the obvious path was not available. Here is what it actually took, including the parts I got wrong.

Starting with what already exists

There are several community-built Ghost MCP servers. I tried one that supports the read-only Content API, and it worked immediately. Posts, pages, tags, authors, all queryable in conversation.

For about an hour I thought I was done.

The thing that quietly breaks everything

Ghost 6.0 removed support for ?limit=all and imposed a maximum page size of 100 on every API endpoint. That change is documented and reasonable, since unbounded queries hurt large sites.

The problem is how it fails. Requesting ?limit=all does not error. It returns 100 items and says nothing about the other 577.

I found this by running a curl command to measure my archive size and getting back exactly 100 posts. A suspiciously round number for a blog running since 2018. The same cap applies to any MCP server built on the Content API, which means every answer I had been getting was drawn from my most recent fourteen months of writing while presenting itself as complete.

That is worse than an error. An error tells you to fix something. This just quietly narrows your world.

There is a second limitation underneath it. Ghost’s NQL filter language has no like operator and no partial matching, and content fields are not filterable at all. You can retrieve plaintext in a response, but you cannot query against it. So even with correct pagination, there is no way to search article bodies through the API.

Ghost’s own site search works around this the same way I ended up doing: download the corpus and search it locally.

The local copy

The design that followed is unglamorous and took an afternoon.

A Postgres table holding one row per post, with the full body in a plaintext column and a generated tsvector column indexed with GIN. An n8n workflow pulls the archive weekly with explicit pagination, seven pages at 100 posts each with a one second delay between them, and upserts everything in a single round trip.

The schema is boring on purpose:

create table ghost.posts (
  id            text primary key,
  slug          text not null unique,
  title         text,
  plaintext     text,
  url           text,
  tags          text[],
  published_at  timestamptz,
  updated_at    timestamptz,
  synced_at     timestamptz not null,
  fts tsvector generated always as (
    setweight(to_tsvector('english'::regconfig, coalesce(title, '')),     'A') ||
    setweight(to_tsvector('english'::regconfig, coalesce(plaintext, '')), 'B')
  ) stored
);

The setweight calls mean a title match outranks a body match. Without them, a post that mentions a term once in passing can outrank the post named after it.

Two details in the sync are worth stealing if you build something similar.

Full reconcile, not incremental. My first instinct was a watermark on updated_at, which is what I use for feed-based syncs. It is the wrong tool here. It would save six HTTP calls a week and would never detect a deleted or unpublished post. Pulling everything self-heals.

A guard on the delete step. Every row gets stamped with the run’s timestamp, and anything older gets pruned, which is how deletions propagate. But the prune only runs if this pass fetched at least 80% of the rows already in the table. Without that check, one bad fetch silently wipes eight years of archive. This is the kind of thing you want in place before you need it.

I was wrong about the size

I nearly did not build this because I assumed the archive would blow past my database provider’s free tier. That assumption was off by roughly two orders of magnitude.

677 posts come to 3.2 million characters of body text. The table with its full-text index totals 11 MB against a 512 MB limit. I would need something like 32,000 posts to be in trouble.

A related surprise: my recent posts average about 8,400 characters while the archive average is 4,700. My writing has roughly doubled in length over eight years. I did not know that about myself before running the query.

The more useful lesson is that on serverless Postgres, storage is almost never the binding constraint for text. Compute hours are. Every query wakes the database for a minimum billing interval regardless of how fast it runs, so a scattered handful of ad-hoc searches costs more than the weekly bulk write does. Worth knowing before you optimise the wrong thing, which I did for a while.

The part I did not anticipate

With the table populated, I assumed I was finished. I was not, and the gap here is the least obvious thing in this whole project.

Nothing routes the question.

Each time you ask something, the assistant picks tools based on their names and descriptions. It sees a Ghost MCP whose tools are explicitly labelled as browsing and reading blog posts. It also sees a generic SQL tool pointed at a database it knows nothing about. Ask “what have I written about X” and the match is overwhelmingly toward the Ghost tools, which return the 100 most recent posts and no warning.

Building the better data source does not cause it to be used. You have to say so, explicitly, somewhere persistent.

I solved this with a Skill: a set of instructions that triggers on phrasings like “what did I write about X” and states the routing rule as a prohibition rather than a preference. Do not use the Ghost browse tools for search, here is why they fail, here is the table and the query shape to use instead.

The skill also pins the retrieval pattern, which matters more than I expected. Search first with ranked snippets, then fetch full bodies for only the three to five posts that are actually relevant. And read whole posts, never fragments. A paragraph mentioning a product without the surrounding argument is exactly how you end up having your own past position mischaracterised back to you.

Full-text is not semantic search

I conflated these two for a while, and the distinction is worth being precise about.

What I built matches words and their grammatical variants. Ask about “Apple Intelligence” and it finds posts containing those words. Ask “how has my thinking about AI subscriptions evolved” and it will find something, because the words appear somewhere, but it has no way to surface a post that discusses the same idea in entirely different vocabulary.

Semantic search needs embeddings, a vector column, and a model called at both index and query time. That is a real layer of work.

I deliberately did not build it. Full-text over 677 posts with English stemming handles the lookup questions well, and lookup is most of what I actually ask. If the thematic questions come back consistently thin, the embeddings layer drops into the same table without rearchitecting anything. Building it up front would have been solving a problem I had not yet confirmed I have.

What it does not do

Four posts have empty bodies. The unauthenticated Content API cannot see members-only content, so gated posts arrive with a title and nothing else. They are findable by name but their text is not indexed. Fixing that means switching to the Admin API with JWT signing, which is not worth it for four posts.

The copy is up to seven days stale. Irrelevant for archive questions, relevant if I ask about something published yesterday.

And the whole thing is lexical. When a thematic question comes back thin, the honest answer is that the tool is not built for it, not that the archive is silent.

Was it worth it

An afternoon, one database table, one scheduled workflow, and one skill file.

The first real test was asking what I had written about GuruShots, a photography game I covered in late 2018. Two posts came back with dates and links. Through the Ghost API alone, that question has no answer at all.

That is the whole point. Eight years of writing is only an archive if you can reach into it.