Blog search with one Postgres trigger: tsvector, Anorm and snippets that never trust HTML

This blog has a search page now, and it has no search engine behind it. No Elasticsearch, no Lucene index on disk, no sync job. Postgres does the matching, the ranking and the snippets. The Scala side is a thin Anorm repository plus a small parser that makes the highlighted snippets safe to render. Here's how the pieces fit, and why each one sits where it does.

The vector lives in a side table

Every post gets a weighted tsvector: the title at weight A, the excerpt at B, and the body at C. The obvious home for it is a column on blog.blog_post. I didn't put it there.

The previous runtime checks blog.blog_post's exact column set at startup. If I add a column and then need to roll back, the old image refuses to boot. An unknown table, on the other hand, is ignored. So migration V11 creates blog.blog_post_search(post_id, search_vector), with a cascading foreign key and a GIN index on the vector.

A trigger owns the definition

CREATE TRIGGER blog_post_search_sync
    AFTER INSERT OR UPDATE OF title, excerpt, body_html ON blog.blog_post
    FOR EACH ROW EXECUTE FUNCTION blog.blog_post_search_sync();

The function upserts setweight(to_tsvector('english', title), 'A') || ... into the side table. Two details matter.

• Column-scoped trigger. The view counter on a post is bumped every minute. UPDATE OF title, excerpt, body_html means that bump never recomputes a vector.
• Any writer works. The repository's save is a plain INSERT ... ON CONFLICT and never mentions search. A runtime that has never heard of search still leaves the index correct. Even the backfill goes through the trigger (UPDATE blog.blog_post SET title = title), so there's exactly one definition of the vector.

The body is sanitized HTML. The trigger turns tags into spaces with a regex and deliberately leaves entities like < encoded. Postgres's default parser classifies those as entity tokens and indexes none of them. Decoding first would turn List<String> into a fake <String> tag, and the word would be lost.

The query, in Anorm

BlogService.search cleans the raw query first. If nothing is left, it returns an empty page without touching the database. Otherwise BlogRepository.search runs two statements: a count, and one page of ten results.

select ..., ts_headline('english', body_text, hit.tsq, {options}) as snippet
from (select s.post_id, tsq, ts_rank(s.search_vector, tsq, 1) as rank, p.published_at
      from blog.blog_post_search s join blog.blog_post p on p.id = s.post_id,
           websearch_to_tsquery('english', {q}) tsq
      where p.published and s.search_vector @@ tsq
      order by rank desc, p.published_at desc, p.id
      limit 10 offset {offset}) hit
join blog.blog_post p on p.id = hit.post_id

• websearch_to_tsquery accepts what people type, like "quoted phrase", -exclude and or, and never raises a syntax error. The user's text goes in as a bound parameter, unmodified.
• ts_rank(..., 1) divides the rank by the log of the document length, so a long post doesn't win just by being long. The tie-breakers keep paging stable.
• ts_headline runs outside the LIMIT. It re-parses the whole body, which makes it the expensive call. Inside the subquery it would run for every match; outside it runs for ten rows.

The headline reads the same tag-stripped expression the trigger indexed. If the two texts drift apart, highlights stop lining up with what matched. Anorm parses each row as parser ~ str("snippet"), and the repository returns the page plus a Map[UUID, String] of raw snippets.

Highlights without trusting HTML

By default ts_headline wraps hits in <b>. Rendering that raw would mean trusting HTML built from the database, which the site doesn't do anywhere except for sanitized article bodies. So the options ask for two control characters instead:

val Start = '\u0002'
val Stop  = '\u0003'
val options = s"""StartSel=$Start, StopSel=$Stop, MaxFragments=2, MaxWords=24, MinWords=10, FragmentDelimiter=" … """"

The OWASP sanitizer strips every C0 control character from body_html, so these sentinels can never appear in real content. Highlight.parse walks the string, switches "hit" on and off at each sentinel, and decodes the entities in each run. The result is a Seq[HighlightPart(text, hit)]. Twirl escapes every part and adds its own <mark> around the hits. If no sentinel appears at all, which happens when only the title matched, the parser returns an empty sequence and the card falls back to the excerpt.

Bonus: "Keep reading" uses the same vectors

Under each post, related articles are ranked with the same side table. The repository builds a query from the current post's title and excerpt with plainto_tsquery, then rewrites & to |, so any shared word counts instead of all of them.

The score is the number of shared tags plus ts_rank(..., 32). Normalization 32 maps the rank into the range 0 to 1, so one shared tag always beats any amount of text similarity, and recency breaks ties. A left join keeps a post without a vector eligible.

What I'd keep

The split feels right. The database derives and ranks, and Scala cleans input, binds parameters and decides what is safe to render. If I change the vector expression later, existing rows won't be recomputed on their own, so that change ships with a migration that reruns the backfill. That's the one rule worth writing down.