# Ranked Search on SQLite

Deutsche Post does not hire Postboten. Not in its job titles, anyway — the postal-delivery listings are titled "Zusteller" or "Fachkraft Postdienstleistungen", and the word every German speaker would actually type into a search box, *Postbote*, appears only somewhere in the body of the description. In the corpus behind my [job-search engine](https://ubeyd.dev/the-job-finder-that-reads-every-listing), 511 listings match "Postbote" through their descriptions, every one of them a real postal job, and not a single one would surface if search only looked at titles. When I wrote about that project before, I stopped at the pipeline that reads the listings; the sentence "before a custom search engine ever ranks it" was a promissory note. This post is the search engine.

## Turning down the sensible option

The sensible option was Meilisearch. It was on the table, seriously — an open-source faceted search engine that hands you multilingual stemming and typo tolerance for free. It lost the decision for a reason I stand behind: the parts of search I most wanted to build — the ranking, the scoring, the ability to explain a result's position — are exactly the parts Meilisearch would have absorbed into a black box, and it would have added a service to a stack that otherwise fits in a single SQLite file. Also considered and rejected: FTS5 alone (throws away the structured filters the relational schema gives me) and building an inverted index from scratch in PHP (two to three weeks of reinventing what FTS5 does well, with no extra learning in the parts that matter). What shipped is a hybrid: SQL owns the filters, FTS5 owns the free text, and a scorer in PHP owns the ranking.

## Two layers, one query

Every search resolves through two layers that compose. Layer 1 is plain SQL over indexed columns — country, language, years of experience, role taxonomy, tech stack, canonical-only, not-polluted — and it always runs, cutting the 13,000-listing corpus down to a slice before any text matching happens. Layer 2 is SQLite's FTS5 virtual table, which indexes exactly three columns:

```sql
CREATE VIRTUAL TABLE listings_fts USING fts5(
    title,             -- short, high-signal
    description_text,  -- the full HTML-stripped body
    indexed_org_name,  -- the employer
    tokenize = 'trigram'
);
```

A worked example from the calibration notes: a candidate filtered to Germany, English-speaking, at most five years of experience, software roles, types `postgres backend`. Layer 1 narrows 13,158 records to roughly 600. Layer 2 runs `MATCH 'postgres backend'` over those 600 and returns about 30 with BM25 scores. The scorer ranks the 30. No cluster, no sync job, no second store — the search index lives in the same database file as the rows it indexes.

## Betting on trigrams

The tokenizer choice is where the corpus pushed back on the defaults. About half the listings are in German, and German welds its words together — *Postbote* is `Post` + `Bote`, and a candidate who types `bote` is asking a question that word-boundary tokenizers cannot answer. In a head-to-head spike on 2,000 records, FTS5's default `unicode61` tokenizer found 0 matches for `bote` and 0 for `kube`; the `trigram` tokenizer — which indexes overlapping three-character windows and does not care where words begin — found 477 and 29. That substring property covers compound nouns and partial tech terms in one move, and it is script-agnostic, so Greek and Czech and Polish queries work with zero per-language code.

The bet has a bill attached. The trigram index is 2.35× the size of the default. There is no typo tolerance — `developr` returns nothing, a limitation documented and accepted rather than papered over. And a three-letter query like "SAP" happily matches inside "ASAP", "WhatsApp", and "Hansaplast", because trigrams have no concept of a word boundary. That last one grew teeth after launch: word-bounded matching eventually came back as a *second* FTS index (`unicode61` alongside `trigram`), blended in one query, with a rank bonus for word-bounded hits — because a user searching "Git" deserves better than every listing containing "digital".

## A title is worth twenty descriptions

Searching descriptions is what makes Postbote findable, and it is also where the noise lives: for a common word like "Manager", description-only matches run about 80% irrelevant. The answer is not to stop searching descriptions — it is to make the ranking opinionated about where a match landed. FTS5's `bm25()` accepts per-column weights:

```sql
ORDER BY bm25(listings_fts, 20.0, 1.0, 5.0)
-- title ×20, description ×1, org name ×5
```

A title match outweighs a description match twenty to one. Jobs actually titled "React Developer" rank far above jobs that mention React once in a nice-to-have list, while the description-only Postbote matches still surface below anything title-relevant. The held-out term analysis that justified this found that tech terms almost never appear in titles at all — a title-only search would lose 77–98% of real matches per term — so the weighting keeps both worlds: recall from descriptions, precision from titles.

## Eight signals, and a scorer that shows its work

BM25 is one voice in the final ranking, not the verdict. The composite scorer in PHP computes eight normalized components per result: the free-text score, recency (an exponential decay anchored to the corpus snapshot's newest record, not to "now" — the product is honest about being a snapshot), description quality, a canonical-cluster bonus, role match graded by classifier confidence, language match, tech-stack match, and a visa-sponsorship signal where unknown gets a neutral 0.5 rather than a punishment. The weights live in one config file, and PHP is the deliberate seam: scoring pushed into SQL would lock it to the substrate, pushed into FTS5 would lock it to BM25.

Alongside every score, the scorer emits a per-component breakdown — weighted contribution, raw value, and a human-readable note like *"posted 12 days ago"* or *"en+de required, you speak en"* — designed to power an explainer panel where any result can answer "why this rank?". Writing this post forced an honest audit: the breakdown is computed on every request, unit-tested, and consumed by a benchmarking command — and no UI component renders it yet. The panel is now a filed issue rather than a quiet assumption, which is its own small lesson about the distance between an architecture document and a screen.

## What launch taught

The biggest post-launch fix was in how typed queries become FTS5 expressions. The first version joined words with an implicit AND, which turned multi-word queries into word-bag noise — every listing containing both words anywhere. The replacement is two modes selected by syntax: a plain multi-word query becomes a phrase-or-proximity search, `"data engineer" OR NEAR("data" "engineer", 100)`, and a comma becomes an explicit opt-in to keyword mode where each comma-separated chunk must match as its own phrase. Depending on the query, that dropped 22–98% of the noise.

The companion decision matters as much: when a query finds nothing, the engine does not silently degrade to sloppier matching to avoid an empty screen. It shows the empty state — and makes it smart. `laravel developer` can match zero listings as text while the corpus is full of Laravel jobs, because the tech extractor understands concepts that exact tokens miss; so the empty state offers clickable recovery tips — "try the Tech: Laravel filter (n jobs)", "try comma-separated keywords" — instead of quietly showing worse results. Honest zero over engagement-driven noise.

Would embeddings make this better? Probably — "postgres" never matching "PostgreSQL DBA" is a real gap, and semantic search sits in the V2 notes next to German morphology and learned per-user weights. But the version that exists ranks thirteen thousand listings in two dozen languages on a database engine most people call a toy, and every interesting decision in it — the tokenizer, the column weights, the eight signals, the refusal to fake results — is mine to explain rather than a vendor's to hide.

---

*Earlier posts from the same project: [The Job Finder That Reads Every Listing](https://ubeyd.dev/the-job-finder-that-reads-every-listing) covers the pipeline that turns raw postings into the structured corpus this engine searches, and [Fuzzy Search Without Elasticsearch](https://ubeyd.dev/fuzzy-search-without-elasticsearch) is this post's sibling: the same do-it-with-the-database philosophy applied to a restaurant's client book.*