Search for a command to run...
Restaurant staff search for clients and reservations with typos, missing diacritics, and half-remembered phone numbers — and the whole thing runs on PostgreSQL trigrams plus a scoring layer in TypeScript, with no search infrastructure at all.
No comments yet. Be the first to comment.
A job-search engine over 13,000 multilingual listings — structured SQL filters, FTS5 with a trigram index, title-boosted BM25, and an eight-signal scorer in PHP — built after deliberately turning down Meilisearch.
You inject a secret into a container at startup, the app runs perfectly, and then docker exec fails with an authentication error. Here is why it happens, and the one line that fixes it.
A multilingual savings app I built to make myself save. The interesting part isn't the balance creeping up. It's what happens when the plan has to change — a missed deposit, a bigger ambition, a moved date. The schedule rewrites itself, and it never throws the old one away.
A table-reservation system for an Istanbul restaurant where the real engineering wasn't the booking form but a live floor plan that read the night's reservations off the database every half hour and regrouped, recoloured, and reset every table on its own — with every staff screen kept in agreement in real time.
There is a moment, in the middle of a dinner service, when a waiter is holding a tablet in one hand and a phone in the other, and the voice on the phone says her name is Ayşe. The tablet's keyboard is set to English, the waiter is in a hurry, and what actually gets typed into the search box is ayse. In a database of Turkish names, a naive LIKE '%ayse%' returns nothing at all, because as far as PostgreSQL is concerned ş and s are strangers, and the dotless ı in Yılmaz has never once met the i the keyboard produced. The search box in my reservation system for an Istanbul restaurant had exactly one job, and this was it: find Ayşe anyway.
This is a write-up of how that search box works. The short version is that there is no Elasticsearch in it, no Meilisearch, no Algolia, nothing that would show up on an architecture diagram as a box labelled "search". There is PostgreSQL, which the application already had, and there is a few hundred lines of TypeScript, and between them they handle typos, missing diacritics, phone fragments, email addresses, and reservation notes, and they rank what they find honestly enough to show the score to the person searching.
The first design decision was that staff get a single search box, not a form with fields. Mid-service nobody wants to decide whether they are searching by name or by phone; they want to type the thing they have and get the person they mean. So the very first thing the backend does with a query is guess what kind of thing it is looking at.
"ayse yilmaz" → multi_word (contains a space)
"ayse" → single_word
"a.k@gmail.com" → email (contains @)
"0533 694 12" → phone (digits, spaces, dashes, parens)
"1284" → id (digits only)
Each kind gets its own path through the system. An ID is an exact lookup and short-circuits with full confidence. An email goes through PostgreSQL's similarity() function, because emails are typed from memory and memory is fuzzy. A phone number is stripped down to bare digits — and relieved of the +90 country code, since half the staff type it and half do not — and then matched by containment in both directions, so the last five digits of a number are as good a key as the whole thing. Names, the hard case, get the full treatment.
The name path has two layers, and the split between them is the actual architecture of the feature: the database is responsible for recall — finding every row that might plausibly be the one — and the application is responsible for precision, deciding which candidate deserves to be first. PostgreSQL's pg_trgm extension does the recall. It breaks strings into overlapping three-letter fragments and calls two strings similar when their fragments overlap enough, which is exactly the kind of matching that shrugs off a missing letter or a swapped pair.
But trigrams alone do not solve Turkish. ş and s produce different trigrams, so ayse still would not reliably reach Ayşe. The fix is to fold the alphabet down before the comparison ever happens, inside the query itself:
WHERE TRANSLATE(LOWER(name || ' ' || surname), 'şğıöüç', 'sgiouç')
% TRANSLATE(:query, 'şğıöüç', 'sgiouç')
TRANSLATE maps the Turkish letters onto their plain ASCII neighbours — ş to s, ğ to g, the dotless ı to i — on both sides of the % operator, so the trigram comparison happens in a flattened alphabet where ayse and Ayşe are finally the same word. The same folding exists as a small character map on the TypeScript side, because the application layer is about to re-score everything the database returns and both layers need to agree on what "the same letter" means.
The database query is deliberately generous. It matches against first name, surname, both concatenations in both orders, and the free-text note on the client record, and it returns up to a hundred candidates. Generosity is cheap at this scale — a restaurant's client book is thousands of rows, not millions — and every false positive it lets through is somebody the next layer gets to quietly rank into oblivion.
The hundred candidates come back into Node, and a small scorer takes over. It computes four signals for each candidate and blends them with fixed weights:
similarity =
wordMatchScore * 0.3 + // query words found in name words
wordSimilarity * 0.2 + // character-level agreement, word by word
consecutiveMatchScore * 0.3 + // longest run of matching characters
noteSimilarity * 0.2; // Levenshtein distance against the note
Each signal earns its place with a different failure mode in mind. The word-match score forgives word order, so yilmaz ayse still finds Ayşe Yılmaz. The consecutive-match score rewards long unbroken runs of agreement, which is what separates a genuine near-miss from a coincidental scatter of shared letters. And the note similarity — a full Levenshtein distance computed against the free-text note on the record — exists because staff remember people by their stories: the note might say window table, birthday in May, and "birthday" is what the waiter remembers at the till. The reservation search runs the same shape of pipeline with its own weights — names carry 60 or 70 percent of the score there, notes twenty, phone ten — joins through to the clients on each booking, and refuses to return anything scoring under ten percent.
The scored list gets sorted, the top ten survive, and that is the entire ranking engine. There is no index to keep warm, no cluster to monitor, and no second data store that can drift out of sync with the truth — the search runs against the same PostgreSQL rows the booking form writes.
One decision in the UI still pleases me: the search results show their similarity score. A result card for a client says, in small text under the phone number, similarity: 87%, and cards above seventy percent get visually marked as strong matches. Search results usually present themselves with unearned confidence — the first result simply is the answer, however weak the match was. Telling a waiter "this is 87% likely who you mean" respects both the waiter and the limits of the algorithm: a strong match gets tapped without a second look, and a page of fifty-percenters tells them to ask the caller to spell the name. The search input is debounced, results arrive grouped into a clients section and a reservations section with counts on each, and the whole exchange usually fits inside the pause where the caller says "Yılmaz. Y-ı-l…"
The production wrinkle, because there is always one: pg_trgm is an extension, and on a managed PostgreSQL instance you do not get to assume where it lands. The migration history tells the story in two files. V72_install_pg_trgm.sql is one hopeful line, CREATE EXTENSION IF NOT EXISTS pg_trgm. V73_prod_db_enable_pg_trgm_becaschema.sql exists because that was not enough on the hosted database — it drops the extension and reinstalls it with SCHEMA becaschema, pinning it into the application's own schema so the % operator and similarity() are actually reachable from the queries that need them. On a laptop, where everything lives on the default search path, the problem is invisible; it only introduces itself in production.
-- V73: the do-over
DROP EXTENSION IF EXISTS pg_trgm;
CREATE EXTENSION pg_trgm SCHEMA becaschema;
Would this design survive a million clients? Not as written — scoring a hundred candidates in JavaScript per keystroke is a luxury of restaurant-sized data, and at some point the ranking would need to move into SQL with a proper GIN trigram index behind it. But that is precisely the point. The problem was restaurant-sized, so the solution is too: one extension the database already knew how to install, one scorer small enough to read in a sitting, and a search box that finds Ayşe no matter which alphabet the keyboard thinks it is speaking.
This is the second write-up from the same project: The Restaurant Floor That Rearranged Itself covers the live floor plan that regrouped and recoloured its own tables mid-service.
A related write-up from a different project: Ranked Search on SQLite applies the same do-it-with-the-database instinct at a larger scale, ranking 13,000 multilingual job listings with SQLite FTS5 and a scorer in PHP rather than a search cluster.