--- type: plan version: 1 status: documented 2026-09-15 from rian's reading of the database map; deliberately NO plan yet — he folds this into a database refactor plan once he has been through the other issues. Nothing here is scheduled and nothing is built. supersedes: nothing; absorbs two open running-list issues (terminals within one airport, Extime is Paris) which stay open until the refactor lands --- # Places and shops: what the location model cannot say Rian, 15 Sep, reading the database map: *"I think there can be multiple shops per airport... Adam is talking about cruise ports and maybe from there outlet malls. Calling it airport is boxing us in. I think we need a location and a shop, where shops belong to a location. Also, I wonder if shops could belong to multiple locations... are things like Avolta actually a chain of shops that appear in multiple airports?"* This document records **what the tables can and cannot express today, and what each gap costs**. It proposes no schema, picks no names and sequences no work: that is the refactor plan, written later against every issue rian has open, not just this one. ## 1. What the tables hold today Two tables carry the whole of it (`main/app/models/catalog.py`): - **`retailers`** — the chain: `slug`, `name`, `operator`, `homepage`. - **`locations`** — one retailer's storefront, `retailer_id` + `code`, unique together, with `iata`, `name`, `city`, `country`, `currency`, `is_catalogue_only`, `visible`. Every other place-shaped thing hangs off those two by the airport code, not by a key: `airport_hours.location_id` points at a shop row; `articles.airport_code` and `subscribers.home_airport` are four-character strings; the public address is `/airports/--`; the shopper's saved airports live in their browser as codes. **So rian's Starbucks question is already answered by the data, and the answer is yes.** A chain with many shops is exactly what `retailers` → `locations` is, and two chains use it that way: | Chain | Shop rows | Where | |---|---|---| | Avolta | 10 | ATH BCN EZE HKG JFK LHR MAD MEX YYZ ZRH | | Motta (Attenza) | 3 | BOG PTY SAL | | every other retailer | 1 | one shop each | What is missing is not the chain. **It is the place.** An airport has no row anywhere in the database. It is a four-character column on each shop row, and everything about the airport — its hours, its write-up, its page, its name in an address — is reached by matching that string. ## 2. The gaps, each with what it costs ### 2.1 A place is not a thing, so only airports can be places `locations.iata` is `String(4)`, and the model's own docstring says "An airport storefront." A place that has no IATA code cannot be represented, because a place *is* an IATA code. **This already bites, today, in the live data.** Two shop rows carry no airport code: | Code | What it is | Listings | Shown? | |---|---|---|---| | `HEINEMANN-GLOBAL` | the online catalogue, no physical place at all | 1,035 | hidden, `is_catalogue_only` | | `SCA` | **BorderShop, Puttgarden, Germany** — a real shop at a real place | 1,806 | hidden, not catalogue-only | The BorderShop is a genuine duty-free shop on the Denmark/Germany border with 1,806 collected listings, and it is invisible on the site. It is not hidden because anyone decided it should be; it is hidden because **there is nowhere to say where it is.** The first non-airport place is already collected and already stuck. Cruise ports would land in exactly the same hole. Adam's cruise-port material (the `cruise-ports` topic on `/discuss`, three comments plus a Google Doc) names Caribbean, Mediterranean and Middle East, and Asia-Pacific terminals — St Maarten, St Thomas, Nassau, Cozumel, Mina Rashid, Gibraltar, Livorno, Kai Tak, Marina Bay, Jeju. None has an IATA code. Rian's reply on that topic: *"Definitely a great idea, but will need some time and planning after we get through this initial site."* Outlet malls, border shops and ferry terminals are the same shape again. ### 2.2 Two shops at one place cannot be told apart — and the counting is already inconsistent The unique constraint is `(retailer_id, code)`, so a second Heathrow shop for the same retailer is *permitted* (a different `code`, the same `iata`). What is not defined is what it means. No airport in the data has two shops today, which is why nothing has broken yet. **The latent defect.** "How many airports stock this product" is asked seventeen times across the services, and it is asked two different ways: - **once** by place: the brand-page floor counts `distinct(Location.iata)`, and its docstring says why — "so two shops at one airport are one airport" (`catalog_queries.py`). - **sixteen times** by shop row: `distinct(Listing.location_id)` — the product list, the savings table, search results, the collectors page's `min_airports` filter, the image-sourcing rank. Today both give the same number. The day a second shop lands at one airport they diverge silently: a product carried by two Heathrow shops counts as "at 2 airports", which makes it eligible for the savings table and the "compare across airports" rails with **no cross-airport comparison in it at all**. Nothing errors. The site simply starts making a claim that is not true. This is the substance of the open issue *"Shops and terminals within one airport are not modelled"* (raised from Mark's SEO review, 7 Sep; Mark cites a specialty shop on the JFK terminal map, Adam the specialty liquor locations on Changi's second floor). Prices do genuinely differ between terminals, and the "where to buy" line on a product page cannot say which shop. ### 2.3 One storefront can serve several places Extime is one website for **Paris Charles de Gaulle and Paris Orly together**. There is no per-airport signal in its pages and no per-airport sitemap, so the shop row is named for both and given the code `CDG`. Orly does not exist in our data, and CDG's 7,036 listings are really Paris's. This is the open issue *"Extime is Paris (CDG and Orly together), not CDG alone"* (Stream A, 3 Sep). It matters for the model because it is the case that **breaks "a shop belongs to one place"**, and it answers rian's second question: > *"I wonder if shops could belong to multiple locations and how that works?"* A *physical* shop is at one place. But the row we collect is not a physical shop — it is a **storefront**: a retailer's sales channel with its own catalogue, prices and currency. A storefront maps to places three ways, and all three are in the data already: | Storefront | Places | Example | |---|---|---| | one | one | World Duty Free at Heathrow | | one | several | Extime: CDG and Orly | | one | none | the Heinemann online catalogue, identity only | So the honest relationship between the collected thing and the place is many-to-many, and prices belong to the storefront rather than to the place. Whether a physical shop (or a terminal counter) is a *further* level beneath the storefront is a separate question, and the one Mark and Adam are actually asking in 2.2. ### 2.4 The chain is the `operator` text, not the retailer row Rian's Avolta question exposes an inconsistency in how chains are already recorded. Avolta is one retailer row with ten shops. **Gebr. Heinemann is four retailer rows** — `heinemann`, `heinemann-iceland`, `heinemann-sydney`, `bordershop` — that share nothing but the `operator` string "Gebr. Heinemann". They are separate rows because each is a separate website with its own collector, not because Heinemann is four companies. So "which chain is this shop part of" is answered by `retailers.slug` for Avolta and by the free-text `retailers.operator` for Heinemann. A brand page, a chain page, or "every Heinemann shop" cannot be queried reliably. The collected-storefront framing in 2.3 explains why: `retailers` is currently doing double duty as *the company* and *the website we collect*. ### 2.5 The word "airport" is in the contract, not just in the code Renaming is the easy half; the airport code as **identity** is the expensive half. Measured on this tree: | Where | Files | Lines mentioning `iata` | |---|---|---| | `main/app/` | 40 | 236 | | `main/web/src/` | 25 | 121 | | `main/tests/` | 17 | 76 | plus 40 more lines on `articles.airport_code` and `subscribers.home_airport`. Of those, the ones that are **public contracts** and cannot be changed casually: - the addresses `/airports/--` and `/airports//`, settled with Mark and Adam on 9 Sep in the structure proposal, live and in the sitemap; - the API paths `/api/airports/{iata}` and `/api/airports/{iata}/hours`; - `/savings?from=`, and the shopper's saved airports, which are IATA codes in their browser; - the navigation's "Airports" department and the airport picker. Everything else — the column names, the service names, the internal joins — is ours to change behind a migration. ### 2.6 Hours prove the model is wrong, mechanically `airport_hours.location_id` points at a shop row because there is no airport row to point at. `hours/store.py` writes a reading to **"the first shop row by id"** at that airport and reads the newest across all of them. The docstring says so plainly. It works only because every airport currently has exactly one shop. It is the clearest single demonstration that a fact about a *place* has been attached to a *shop* for want of anywhere else to put it — and the same will be true of an address, a map pin, a terminal map, opening times per terminal, and a cruise port's sailing season. ## 3. The questions a refactor plan has to answer Recorded as questions, not answers. This is what rian decides when he writes the plan. 1. **What is a place?** One table with a `kind` (airport, cruise port, border shop, outlet mall, ferry terminal), or a table per kind? A kind column keeps one address space and one page type; separate tables let each kind carry its own facts without nullable columns. 2. **Is IATA an identity or an attribute?** Everything above says attribute: a place needs a slug of our own, and IATA becomes one of several external identifiers (a UN/LOCODE for a port, nothing at all for a mall). 3. **Is a storefront one shop or many?** Does the refactor keep one row per collected storefront (today's `locations`, renamed) and link it to places many-to-many, or introduce a physical shop beneath it so a terminal counter can exist? 2.2 and 2.3 pull in different directions and both are real. 4. **What is the comparison unit?** "At two or more airports" must become "at two or more places", and the sixteen queries counting shop rows must be corrected in the same change (2.2), or the savings table starts lying. 5. **Is a chain a first-class row?** If Heinemann's four websites should be one chain, `retailers` splits into the company and the collected storefront (2.4). 6. **What happens to the airport addresses?** Keep `/airports/...` as the airport kind's address and give ports their own shape, or move to one shape for every place. The first keeps Mark's settled structure intact; the second is cleaner and costs a set of redirects. 7. **Do places nest?** Paris (CDG + Orly), a port city with two terminals, an airport with four terminals. One parent column would cover all three; whether the site ever shows the parent is a separate question. ## 4. What the refactor must not break Standing rules from `agents.md` and the build plan that constrain any answer: - **A migration is schema-only** (create tables, add nullable or server-defaulted columns); every backfill is an idempotent CLI command (`app.cli backfill `). No repair in a migration. - **Merges are recorded, never deleted**, and **a human value is never overwritten by a machine.** Any place-splitting (CDG out of Paris, a terminal out of an airport) has to be re-runnable and has to keep what a person decided. - **A price is an observation.** `price_observations` is append-only and joins through `listings` to the storefront; moving a listing between places must not rewrite history. - **Currency belongs to the storefront, not the country.** BOG, EZE, ICN and MEX all price in US dollars because those shops chose to. A place-level currency would be wrong. - **`locations.visible` is the publication switch** and collectors keep running for hidden rows; whatever replaces it needs the same separation between "collected" and "shown". ## 5. Where this is tracked - `decide-the-database-structure-changes-you-want-after-the-listin` — the refactor decision, links this document. - `issue-shops-and-terminals-within-one-airport-are-not-modelled` — §2.2, owner A, blocks P6. - `issue-extime-paris-not-cdg` — §2.3, blocks P6. - `issue-places-that-are-not-airports-cannot-exist-and-one-is-alr` — §2.1. - `issue-the-comparison-counts-shop-rows-not-places-so-a-second-s` — §2.2's latent defect. The database map rian read this from: `.memory/database-map-artifact.md`. The generated relationship diagrams and the table-by-table reference: `main/docs/DATA-MODEL.md`.