Russian queries, English titles: bilingual job search on SQLite FTS5

Our job index has a lopsided problem. Almost every job title is in English, but a large share of the people searching type in Russian. When we looked at a month of search logs, about a third of the queries were Russian role names or country names.

The original search was a substring match. A query for "менеджер платежей" (payments manager) returned nothing. "qa" matched Qatar. "seo" matched "closeout". And there was no ranking at all: a posting that mentioned a word once in the description could sit above a posting with that word in the title.

This post is how we replaced it without adding a search server. Everything runs inside the web process, on SQLite and the Python standard library.

The goal: concepts, not strings

The key decision was to stop treating the query as a string and start treating it as a list of concepts. A concept is one idea in all of its spellings:

• "менеджер платежей", "payments manager", "psp manager", "head of payments" are one concept;
• "мальта", "malta", "malte" and the Greek name are one geographic concept;
• "удаленка", "remote", "wfh", "work from home" are one format concept.

Each concept must match somewhere in a posting (logical AND). Where it matched decides how much it weighs. That one rule fixed most of the ranking complaints.

Building the dictionary

We did not want to maintain a synonym list by hand only, so the dictionary is assembled at import time from three places:

1. A role catalogue we already had for other pages: each role has a Russian title, an English title, aliases and a list of broader "match" hints.
2. The site's own tags and categories, which already existed in both languages.
3. A small manual table for the things people actually type: slang ("сейлз" for sales), transliterations ("питон" for Python, "девопс" for DevOps) and Russian words that should expand to several English ones.

The manual table is directional on purpose. A Russian "вип" expands to "vip manager" and "host", but the English "vip" does not expand back, because in English titles it is usually literal.

Countries and cities go into a separate table that maps every spelling to one canonical country. The finished dictionary has 422 role keys and 1,077 geographic keys. Building it takes a fraction of a second at startup.

Stemming without a stemmer

Russian is heavily inflected. "платежи", "платежей", "платежам" are the same word. A proper morphological analyser would be the textbook answer, but we only needed one thing: match from the start of the word.

So the stemmer is deliberately crude. For Cyrillic it strips the longest known ending if at least three letters remain. For English it handles plurals:

RU_ENDINGS = sorted({"ями", "ами", "ого", "ему", "ов", "ей", "ий", "ая",
                     "ы", "и", "а", "я", "у", "е", "о", "ь"},
                    key=len, reverse=True)

def stem(word: str) -> str:
    if CYRILLIC.search(word):
        for ending in RU_ENDINGS:
            if word.endswith(ending) and len(word) - len(ending) >= 3:
                return word[: -len(ending)]
        return word
    if word.endswith("ies") and len(word) > 4:
        return word[:-3] + "y"
    if word.endswith("s") and not word.endswith("ss") and len(word) > 3:
        return word[:-1]
    return word

"платежей" becomes "платеж", "affiliates" becomes "affiliate". The stem is never used on its own; it becomes the start of a regular expression that allows a few letters of ending back.

Short words must match whole

The "qa" matches Qatar bug is the classic one. The fix is a word boundary that understands both alphabets, plus a rule: a short English stem gets no optional tail at all.

LEFT, RIGHT = r"(?<![a-zа-яё0-9])", r"(?![a-zа-яё0-9])"

def word_pattern(word: str) -> str:
    base = stem(word)
    if CYRILLIC.search(base):
        tail = r"[а-яё]{0,4}" if len(base) >= 3 else ""
    else:
        # short words only whole; if we cut an ending, allow it back
        tail = r"(?:s|es|ed|er|ers|ing)?" if (len(base) >= 4 or base != word) else ""
    return re.escape(base) + tail

def phrase_pattern(phrase: str) -> str:
    return LEFT + r"[\s\-/]*".join(word_pattern(w) for w in tokens(phrase)) + RIGHT

The base != word clause took us a while to find. "odds" stems to "odd", which is short, so without that clause the word could not find itself.

Python's \b would not help here: with mixed scripts and words like "c++" and ".net" we wanted explicit control over what counts as a letter.

Parsing the query: longest match first

The parser walks the query left to right, drops stop words ("ищу работу", "looking for", "jobs"), and at each position tries the longest phrase from the dictionary first. That is how "head of affiliates" stays one concept instead of becoming "head" and "affiliates":

while i < len(content) and len(concepts) < MAX_CONCEPTS:
    found = None
    for n in range(min(MAX_KEY_WORDS, len(content) - i), 0, -1):
        key = " ".join(stem for _, stem, _ in content[i:i + n])
        if key in GEO_KEYS:
            found = geo_concept(key), n
            break
        if key in ROLE_KEYS:
            found = role_concept(key), n
            break
    if not found:
        found = literal_concept(content[i]), 1
    concept, used = found
    concepts.append(concept)
    i += used

Stop words are removed from the lookup key but kept in the displayed literal, so the interface can say "head of affiliates", not "head affiliates". Parsing a query takes 2 to 4 ms on a laptop, most of it compiling the regular expressions.

FTS5 only for descriptions

Titles, tags, company names and locations are short, so we keep them in memory: one light row per approved posting, no descriptions. Descriptions are long, and that is what SQLite FTS5 is for.

The FTS table is an external-content table over the jobs table, and triggers keep it in sync, so a new posting is searchable the moment it is inserted:

db.execute(text(
    "CREATE VIRTUAL TABLE jobs_fts USING fts5("
    "title, company_name, tags, location, description, "
    "content='jobs', content_rowid='id', "
    "tokenize='unicode61 remove_diacritics 2')"))
db.execute(text(
    "CREATE TRIGGER IF NOT EXISTS jobs_fts_ai AFTER INSERT ON jobs BEGIN "
    "INSERT INTO jobs_fts(rowid, title, company_name, tags, location, description) "
    "VALUES (new.id, new.title, new.company_name, new.tags, new.location, "
    "new.description); END"))
# ...same for AFTER DELETE and AFTER UPDATE with the 'delete' command
db.execute(text("INSERT INTO jobs_fts(jobs_fts) VALUES ('rebuild')"))

If FTS5 is not compiled into the SQLite build, the function logs it and search simply works without descriptions. It degrades instead of failing.

Each concept is translated into an FTS5 expression with prefix queries, reusing the same stemmer:

payments manager, psp  →  ("payment"* AND "manager"*) OR "psp"

Short tokens like "psp" and "qa" are not given a *, for the same reason as above.

Weights, and words that are too common

Every concept is scored by the best field it matched:

On top of that, a title gets +25 if it contains an exact spelling of the concept (not just a broad hint from the catalogue), +10 if the match is at the start of the title, and ties are broken by featured status and date.

The interesting part is the description rule. "support" appears in about half of all descriptions, and that does not make a posting a support role. So a description-only match counts for the AND only if the term appears in fewer than 20% of postings. Above that threshold it can still lift a posting, but it cannot qualify it on its own.

A prefix index to avoid regex on every row

The first version ran the combined regular expression of every concept against every row. It was correct and slow.

The fix was a small inverted index built together with the in-memory rows: for each token in title, tags, category, company and location we store its first three and four letters (or the whole token if it is one or two letters). Each spelling of a concept only gets tested against rows that share its prefix key, and the regex does the precise check.

The index is rebuilt when the set of approved postings changes, keyed by (count, max(id)). When the cache simply expires, the rebuild runs in a background thread and the request is served from the previous index, so only the very first build after startup blocks a request.

When nothing matches

Empty results were the other source of complaints, so there are three fallbacks, in order:

1. Drop unknown words. A concept that matches no posting at all is removed from the AND, and the page says so, like Google's "missing" note.
2. Fix typos. If everything is still empty, each unknown word is compared with difflib.get_close_matches against the dictionary plus title words that appear at least twice. A cutoff of 0.78 turns "менеджр" into "менеджер" without inventing matches.
3. Loose mode. If no posting has every concept, we show the ones with the most concepts, plus a few similar roles from the catalogue.

Show why it matched

People judge search by whether they can see the match. So the same compiled patterns are reused for highlighting: matched words in titles and tags are wrapped in <mark>, and if a posting only matched in its description, the card shows a short snippet around the first hit. Text is escaped first, markup is added after.

What we would do again

• Model the query as concepts with all their spellings. Most "ranking" bugs were actually parsing bugs.
• A crude stemmer plus a prefix regex covered Russian inflection well enough for short, keyword-like queries.
• Keep FTS5 for long text only, and do not let very common words qualify a result.
• Make every fallback visible in the UI. A note saying "we ignored X" or "we searched for Y" is cheaper than a perfect algorithm.

We use this at SpinHire, a job board for the iGaming industry, where the same engine serves the HTML pages, the JSON API and the MCP server.