939 — Travel from/to search: paging, word matching, trigram indexes¶
Bug¶
GET /locations/search?query=be&transport_mode=train&limit=10&page=3
returned the same rows as page=1. The route never declared page, and
FastAPI drops undeclared query params without an error. The from/to
q-selects asked for one page of 20 and nothing more, so a short query such
as be (5 422 train matches) could never reach most stations.
The search also matched the whole query as one accent-sensitive
substring. genève aéroport found nothing while genève-aéroport found
the Genève-Aéroport stations, and zurich found no Zürich station.
History¶
The issue comment has the full account. In short:
| date | commit | change |
|---|---|---|
| 2026-01-23 | 6d93a467c |
ICU collations ch_*_ci_ai: case- and accent-insensitive |
| 2026-05-05 | 54114853f |
pg_trgm GIN on 4 columns, raw SQL; collations dropped, accent folding lost; LOWER(col) % could not use the indexes |
| 2026-06-15 | 7c7423b48 |
ILIKE per column, so the indexes became usable (paris → CDG, #1439) |
| 2026-06-15 | 72d56d583 |
migration collapse rebuilt 3 of the 4 trigram indexes as btree; only keywords survived; every search scanned the table since |
Design¶
Paging (shipped, confirmed)¶
page: int = Query(1, ge=1, le=10_000). The service turns it intooffset=(page - 1) * limit, the repo applies.offset().limit(). The asyncpg dialect bindsOFFSET $n::INTEGER, so the cap keeps a huge page from overflowing int4 into a 500; 10 000 pages of 20 is past any result set.queryis capped at 255, the searched columns' length.Location.idcloses both ORDER BY branches:namerepeats, and a non-total order lets consecutive pages overlap or skip rows.- The response stays
list[LocationRead]. No COUNT: a page shorter thanlimitis the last one. - Frontend:
composables/useLocationSearch.tsreplaces the duplicatedfilterFrom/filterToinCO2DestinationInput.vue, andmolecules/LocationSelectField.vueholds one from/to q-select with its search, menu states and pick handling (499 → 331 + 110 lines). The atom keeps the from/to values, the swap and the plain-input fallback.filterloads page 1 for the trimmed query;onVirtualScrollappends the next page when the last option renders, thenref.refresh()(Quasar's infinite-scroll pattern). A request counter, bumped when a search, a page load or a reset starts, lets only the latest request touch the list, the loading flag and the error, as inVirtualSelectField.vue. - The menu says why it is empty or short, with the keys
VirtualSelectFieldalready uses:common_type_to_searchunder 2 characters,common_no_search_resultsfor no match, andmodule_options_load_errorwhen page 1 fails. A failed page-N fetch keeps the rows shown, stops paging until the next search, and shows the same error after the last row.
Matching¶
- The query splits into words (
[^\W_]+). Every word must match (AND), in any ofname,iata_code,municipality,keywords(OR). Separators and word order no longer matter.%/_are no longer wildcards; a query with no letter or digit returns[]without a query. - Words of 3+ letters match anywhere:
bahnhoffinds Hauptbahnhof,hausenfinds Oberhausen. Words of 1–2 letters match a word start:befinds Bern, not Oberhausen;hbfinds Zürich HB. A short word is never refused, sincebe→ Bern is the bug itself. Prefix for short words, infix for longer ones is what Sphinx (min_prefix_len/min_infix_len) and Elasticsearch (edge-ngram + ngram) do. - Accents are folded on both sides (
zurichfinds Zürich,évéfindsevenement). Ties after the folded relevance go to the rawsimilarity(name, query), so the spelling typed comes first:neuchâtellists Neuchâtel before Neuchatel. A mistyped accent never empties the list.
Indexes¶
search_text(text): one IMMUTABLE SQL function,' ' || regexp_replace(public.unaccent('public.unaccent', $1), '[[:space:][:punct:]]+', ' ', 'g').- IMMUTABLE because
unaccent()is only STABLE and cannot be indexed. - Schema-qualified because pg_restore runs with an empty
search_path. - Separators become one space and a space is prepended, so a word start
is always
' ' + word.'% be%'then extracts the trigrams" b"and" be", and the index serves it. - The separator classes are ASCII-safe:
[^[:alnum:]]would erase non-ASCII letters under a C ctype. The databases are libcen_US.utf8. - Four GIN
gin_trgm_opsindexes,ix_locations_<column>_trgm, onsearch_text(column), one per entry ofLOCATION_SEARCH_COLUMNSinmodels/location.py. The repo builds its WHERE from the same tuple. The expression is spelledsearch_text((name)::text), the way Postgres stores it, so autogenerate compares it equal and proposes nothing. They replaceix_locations_keywords. The btree oniata_codestays forget_by_iata. - The pattern side is
unaccent('%word%')orunaccent('% wo%'), notsearch_text(), which would prepend a space. - Why
LIKE, not a regex: in(^|[^[:alnum:]])be, pg_trgm cannot expand the negated class, so the GIN scan matches every row.\mis ignored. Checked intrgm_regexp.candtrgm_op.c. - Cost: four GIN indexes slow the location seed somewhat.
Migration¶
- Revision
c69b88dfd949.make db-revision message="..."(on a local DB) autogenerates thedrop_index("ix_locations_keywords")and the fourcreate_index; theuq_emission_recalc_active_scopedre-create it also emits is autogenerate noise and was pruned. - Hand-added before them:
CREATE EXTENSION IF NOT EXISTS unaccent SCHEMA public(search_textcallspublic.unaccent; withoutSCHEMAthe extension lands in a role-named schema if one exists) andCREATE OR REPLACE FUNCTION public.search_text.downgrade()drops the function and the extension after the autogenerated index changes. Both are listed inbackend/alembic/CUSTOM_DB_OBJECTS.md. - The data_ingestion Postgres fixture builds the schema with
create_all, so it createsunaccentand the function first, as it already does forpg_trgm. Function DDL lives once inSEARCH_TEXT_FUNCTION_SQL; the migration keeps its own frozen copy.
Train reference data¶
The trainline-eu dump is multimodal, and the builder kept every suggestable
row. The reference held ~20 000 Swiss bus and tram stops and coach stops
abroad. Its is_airport != t filter dropped the airport railway stations
(Genève Aéroport, Zürich Flughafen), and nothing answered "Cornavin".
backend/scripts/build_train_seed_from_trainline.py now keeps:
- in CH, a row whose stop number, or failing that its name, is a
TRAINorRACK_RAILWAYservice point in the official list (opentransportdata.swiss "Service Points (actual date)"). The name catches the Schaffhausen line, which trainline numbers the German way. A city row stays when one of its stations does. - elsewhere, a row with a UIC code or a railway carrier, unless its name
says bus (
BUS_NAME). A row sold only by coach goes only in a country where trainline sells trains: elsewhere (BG, UA, BA, LT, GR) coach is all trainline sells, so it says nothing about the stop. This still drops the Dublin DART halts (Raheny, Sydney Parade) and PL Kłodzko and Piła; a national list, as for CH, would recover them. - airports under the same rules.
Keywords hold the name, the Swiss official name, the de/en/fr/it names and
ALIASES (Cornavin, Hauptbahnhof, Sankt Gallen, Basle). The build fails
when an alias's station is no longer in the output.
Against the current reference: 51 299 → 25 660 rows. 25 559 natural keys stay; 25 740 go (CH 20 268, ES 2 999, HR 572, IT 503, NO 299, IE 213, PL 169, FR 128); 101 arrive, all airport railway stations.
Open before the upload: saved trips. A train entry stores
origin_natural_key and destination_natural_key. If the upload removes
one, pre_compute logs a warning and returns {}, and the trip's emission
becomes zero without an error. Run the orphan query on each environment
first; remap or block.
Prod, 2026-10-27: 53 locations, 226 trips. 50 stay; 3 go, all bus stops in the official list, 8 trips: Ecublens VD EPFL Piccard (4), St-Sulpice VD Chantres (2), Zürich Flughafen REGA (2). Tracked in #3142; dev and stage not checked yet.
Not done¶
stores/unitFilters.ts(backoffice reporting filters) has the same infinite-scroll shape, but the scroll never fires:total_pagesis pinned to 1 andcurrentPage*is never incremented. Out of scope.- Language variants need the names in
keywords; folding accents does not bridge them. The train builder now writes the de/en/fr/it names (Geneva,Bâle, #1830); for planes,LondresandMoscoustill come from hand edits to the reference CSV. - Typo tolerance (
zurch→ Zürich) went with the 2026-06-15 switch toILIKE; not restored. -
2791 (explicit "no airport found" for plane) and #1436 (travel table¶
filter) are separate issues.
Tests¶
backend/tests/unit/v1/test_locations_search_endpoint.pygoes through the HTTP layer, so a mis-wiredQueryfails it too:page=3&limit=10bindsLIMIT 10 OFFSET 20, the default page bindsOFFSET 0, the order ends onlocations.idfor train and plane.page=10001and a 256-charqueryare 422s that never reach the DB.genève aéroportandaéroport-genèvebind the same patterns;zurich hbbinds a word-start% hb%. Every column the query searches has its_trgmindex on the samesearch_text()expression.backend/tests/integration/services/data_ingestion/test_location_search_pg.py(Docker Postgres), the permanent regression suite. It seeds the reference rows, their keywords included, plus 300 fillers with repeated names. It checks every searchable QA case below, the DATA ones through the seeded keywords; a DATA case passes in the app only once the reference CSV carries the same keywords. It walks every page of a 300-row result: pages are disjoint and complete. An EXPLAIN ANALYZE checks that each word is served by all four_trgmbitmap index scans, each returning fewer rows than the fillers. A plan label alone is not enough: a match-all GIN scan is also a bitmap index scan.backend/tests/unit/seed/test_build_train_seed_from_trainline.py: on a small dump, Swiss railway and rack-railway stops stay, Swiss bus stops go, an airport railway station stays, a city stays for its station, and a bus-named row abroad goes. A coach-only row goes in a country where trainline sells trains and stays elsewhere. Genève carries "Cornavin"; a missing alias station fails the build.frontend/tests/unit/location-search-paging.spec.ts: page 1 returns a full page of 20, which leads to a request for page 2. Page 2 is short, so no page 3 is requested.asends no request and asks for 2 characters; no match says so; a failed page 1 shows the load error instead of a blank; a failed page 2 keeps the rows and shows the error; a late page-2 failure of the previous query does not flag the new list; a pick emits the name and the location.
QA cases¶
Mined from all 1 071 issues; 17 travel-search issues read. "DATA" depends
on keywords/Trainline rows, not on the search code.
| # | mode | type | expect | issues |
|---|---|---|---|---|
| 1 | train | ge, scroll |
pages load; CH stations first; every row has a word starting with "ge" | #939, #429 |
| 2 | train | be |
Bern ranks high, reachable by scrolling | #939, #915 |
| 3 | train | ber |
Bern | #939 |
| 4 | train | Milan |
Milano Centrale | #941, #915 |
| 5 | train | Stuttgart |
German stations, usable as origin and destination | #915 |
| 6 | train | Strasbourg, Paris, Bonn |
stations found | #941 |
| 7 | train | zurich |
Zürich HB | #429, #939 |
| 8 | train | zurch (typo) |
no result, shown as an empty state, no hang | #429 |
| 9 | train | zu |
Zürich stations, no "No result" flash | #942 |
| 10 | train | zurich hb |
Zürich HB first | #429 |
| 11 | train | hb zurich |
same as 10 | rules |
| 12 | train | St Gallen, st. gallen, st-gallen; sankt gallen |
St. Gallen for the first three; sankt DATA |
#429 |
| 13 | train | geneve, Genève, Geneve |
Genève (main station; Cornavin is a keyword) | #2791, #939 |
| 14 | train | Geneva |
Genève (en name in keywords) | #331, #1436 |
| 15 | train | geneve aeroport |
Genève Aéroport railway station, no ICC/WTC bus stops | #939 |
| 16 | train | Genève Cornavin, Geneve-Cornavin |
Genève first | #939 |
| 17 | train | Lausanne |
Lausanne (CH) first | #2791, #429 |
| 18 | train | Bern, Berne |
CH Bern first; DE "Berne" after, country shown | #1183, #1124 |
| 19 | train | Berne: DE as origin, CH as destination |
both selectable, saves without HTTP 500 | #1124 |
| 20 | train | Basel/Bâle, Lugano, Luzern/Lucerne, Biel/Bienne, Thun, Sion, Fribourg, Neuchâtel/Neuchatel |
CH station on top; FR/DE variants DATA | #1830, #429 |
| 21 | train | sai |
European/CH rows before JP, IN, BR | #941 |
| 22 | train | g |
the select waits for 2 characters; no hang, no flash | #939 |
| 23 | train | zu, switch tab, come back |
list stays, no sticky "No result" | #942 |
| 24 | train | type fast g,ge,gen,gene |
final list matches gene |
#939, #942 |
| 25 | train | ge, scroll to the end |
no duplicates or gaps; last page ends, no endless spinner | #939 |
| 26 | train | scroll past page 1, then edit the query | list restarts at page 1 | rules |
| 27 | plane | Paris |
CDG and ORY, large airports first | #331, #1439 |
| 28 | plane | Lausanne |
empty state (explicit message is #2791) | #2791 |
| 29 | plane | Geneve, Genève |
GVA | #2791 |
| 30 | plane | cdg, CDG, Cdg |
CDG first | #1439 |
| 31 | plane | GVA, SFO, ZRH |
exact IATA first | #368, #18 |
| 32 | plane | Charles de Gaulle |
CDG first | #1439 |
| 33 | plane | roissy |
CDG (keywords) | #1439 |
| 34 | plane | Paris, matched on municipality only |
CDG via "Paris (Roissy-en-France, Val-d'Oise)" | #1439 |
| 35 | plane | London; Londres |
Heathrow, Gatwick…; Londres DATA |
#1436 |
| 36 | plane | Moscow, Moscou, Moskva |
DATA; record the result | #331 |
| 37 | plane | a query matching a large airport and a small airfield | large airport first | #331 |
| 38 | plane | zzzzzz |
empty state, EN and FR | #2791, #939 |
| 39 | both | same query, FR then EN UI | same results; messages translated | #2516, #2791 |
| 40 | both | select a result, reopen the select | label shows name + country; entry saved with its natural_key | #1124, #1183 |
| 41 | both | round trip, then change origin | both ends resolve to the right places | #18, #1124 |
| 42 | both | every keystroke | < 300 ms deployed, ideally ~100 ms | #429 |
| 43 | both | travel table loupe filter London |
out of scope here, open as #1436 | #1436, #2516 |
| 44 | train | names with non-Latin letters | displayed without mojibake | #331 |
| 45 | train | Bern HB, Bern |
no near-duplicate rows for one station (DATA) | #1183, #1305 |
Performance (local DB, be/train)¶
| request | seq scan (before) | trigram indexes |
|---|---|---|
page 1, limit=10 |
51–82 ms | to measure |
page 3, limit=10 |
52–55 ms | to measure |
page 30, limit=10 |
50 ms | to measure |
page 272 (last), limit=20 |
73 ms | to measure |
Walking all 272 pages returned 5 422 rows with 5 422 unique ids (contains matching; word-start matching returns fewer).