# Data model

Sources of truth: `main/app/models/`, `main/alembic/versions/`.

## Why it is shaped this way
- **A price is an observation.** `price_observations` is append-only: each row is what one
  listing showed at one moment, in the currency the page declared, with the context it was
  seen under. Nothing stores "the price"; every read picks the latest observation and shows
  its date.
- **Facts, not expression.** There is no column for retailer marketing copy and no rehosted
  photography; imagery comes from an openly licensed source and is attached to the product,
  never to the retailer's listing.
- **Identity is the GTIN.** `products` is keyed on barcode where one exists; the normalised
  brand+name+size `match_key` is the fallback. Cross-shop comparison is therefore a join, and
  a shop without barcodes only matches itself.
- **A listing is one product at one location.** `listings` carries the retailer's URL and
  SKU; the size is the product's (`size_value`, `size_unit`, `size_ml` derived), so a
  multi-size family is several products with a listing each, never one tile's cheapest.
- **Retailer and location are separate.** One operator runs many airport storefronts, and a
  retailer-wide catalogue (no IATA code) can exist for identity only. `locations.visible`
  decides what the public site shows; collectors keep running for hidden locations.
- **Sources are configuration with a kill switch.** `sources.enabled` stops a collector
  without a deploy; `collection_runs` records every run and what it wrote.
- **Client surfaces are tables too.** Discussion items and their comments are an
  append-only list and thread; feature priorities and quote selections are one row per key
  with "latest choice wins" semantics; the owner's decisions on the running list are
  `owner_item_states` (`CLIENT-SURFACES.md`).
- **Editorial text is rows, not files** (migration #5, Decision 8). `articles` holds three
  kinds under one shape, `article` (its own page), `airport_writeup` (keyed by `airport_code`)
  and `category_intro` (keyed by `category`), each `draft` or `published`; every public read
  filters on `published`, so a draft is invisible even at its own address. The body is Markdown
  as handed in (`body_md`); HTML is rendered on read by one renderer (`services/markdown.py`),
  so the SPA and the crawler-facing page cannot disagree. Intake is `app.cli articles import`
  (Markdown, text, or Word via `mammoth`), idempotent on `slug`: a re-import replaces the text
  and never changes the status. `author_id` is the who-column; `source_upload_id` is the
  client hand-in the row came from.
- **Subscribers are personal data, stored to the minimum.** `subscribers` keeps the fields
  the client asked for, the consent sentence shown and when it was ticked; no IP, no user
  agent; email lowercased and unique. A withdrawal sets `unsubscribed_at` and keeps the row
  as the record. The list leaves the database only through `app.cli subscribers export`, run
  by the owner in the container; there is no read route.

- **Brands are a table** (migration #3). `brands.slug` is the fold key
  (`normalize.brand_key`, hyphenated): every spelling of a house is one row; `name` is the
  spelling shown (mixed case over capitals; a human value is never overwritten by the
  backfill); `canonical_id` points an alias at its house. `products.brand` keeps the text as
  collected; `products.brand_id` is the fold. Identity rules v3 (migration #6): `match_key`
  is `house | line | variation | size`, with a fifth part for a set, a refill or a pack, so
  "same house, same line, same canonical variation, same size" is the key itself
  (`services/keying.py` resolves the house through the alias and the variation through the
  alias table before `normalize.match_key` composes it); v2 led with the fold key and the
  name with only the brand prefix stripped, and v1 used the brand text as written.
  `identity_rules_version` says which rules a row was last resolved under; `app.cli rederive`
  brings old rows forward and `backfill merges` folds whatever then shares a key.
- **Per-vertical attributes veto and never key.** `products.attributes` (JSONB) holds
  facts such as a fragrance's concentration. Two declared values that differ keep two rows
  apart even under one match key; a missing value never splits a product. Since v3 the
  canonical `variation` is in the key and subsumes the parsed concentration in the veto
  (two Elixirs parsed as parfum and edp are one variation); an unknown variation keys
  apart from every known one and is only ever a suggestion for a person, never a guess.
- **Three layers for every value** (rian, 14 Sep; migration `b3c4d5e6f7a8`, Stream L).
  **Listed** is the words exactly as the shop showed them, on `listings`: `listed_brand`,
  `listed_name`, `listed_variant`, `listed_quantity_text`, `listed_category`, `listed_gtin`,
  with two folds for SQL (`listed_brand_key`, slug-shaped like `brands.slug`;
  `listed_name_key`, like `products.name_key`), the parsed `listed_quantity_value`/`_unit`,
  and `listed_record_id`, the fragment every listed column came from. One reader
  (`services/collected.listed_fields`) writes them as one unit, at ingest and in `backfill
  listed`; NULL where no fragment exists; never from Product fields, never trimmed or
  cased. **Standard** is what the rules made of it, recomputable at any time, and lives on
  the product: the quantity (`quantity_value` in its canonical unit ml | g | pcs,
  `pack_count` and `pack_unit_value` for a pack, `form` single | pack | set | refill,
  `set_contents`, `quantity_state` stated | none | unparsed; `services/quantity.py`), the
  line, the variation with its `variation_kind` in `attributes` (concentration, color,
  flavor; `variation_aliases.kind`), and `name_key`. **Decided** is a human override of a
  rule that survives every recomputation: on the product through `overrides` (entity_type
  `product`, the field `name`, `line_id`, `variation` or `quantity`), which every rekey and
  rederive reads first; at listing level `pinned_product_id` (ingest honours it every
  sighting) and `ignored_at` (prices keep collecting; the site's readers skip it). Each
  carries who and when. **Provenance of a standard field** is read from three places: a
  rule (`products.identity_rules_version` says which rules last resolved the row), a merge
  (`product_merges.merged_by`/`merged_at`, `merge_candidates.decided_by`/`decided_at`), an
  override (`overrides.set_by`/`set_at`/`reason`). A tombstone keeps the key of the current
  rules (`rederive` rekeys merged rows too) so ingest can follow a human merge across the
  next collection; a rule's own fold is never followed.
- **The quantity, not the size** (identity rules v4). The key slot is a value with its unit
  in every dimension (`100ml`, `50g`, `200pcs`; a pack on its total, a set on its sorted
  contents, `unknown` when nothing was read), so 100 ml never equals 100 g and an unknown
  never agrees with anything. `size_value` and `size_unit` are the stated volume pair from
  before v4 (a unit of ml, l or cl and nothing else: grams were never stored there, whatever
  the earlier text said; a 75 g stick sat there as 75.00 ml) and stay as the record;
  `size_ml` is the derived millilitre figure the public site and the verification read,
  NULL for a weight or a count. A stored size no single item comes in is set to unknown,
  never guessed.
- **The line is the real product; the product is one size of one variation of it**
  (migration #6, rian's decision of 12 Sep: one page per real product). `product_lines`
  holds one row per house and line key, the key a pure function of the collected name
  (`services/lines.py`: the name minus the brand, the sizes, the strength, the pack and
  edition tokens; beauty also minus the concentration and variation words, with the
  audience kept because "for Men" and "for Women" are different bottles; drinks keep every
  expression word, a 12 and an 18 being different lines, and fold "12 years old" to "12").
  `products.line_id` is the join; `name` is the preferred spelling, the most common
  product name with only the brand, measures and format words removed, and a person's
  value is never overwritten by the backfill. The variation is `attributes.variation`,
  canonical ("edt", "parfum", "elixir", "edp intense"), read from the name by rule and
  then through `variation_aliases`, where every wording seen ("elixir parfum intense")
  is a row a person can point elsewhere; the parsed `concentration` stays as the raw
  record. A drink has no variation. Backfills: `lines`, `variations`; each safe to repeat.
- **Aliases at every level are a person's decision, and readers follow them.** A brand
  alias is `brands.canonical_id` (migration #3), a line alias `product_lines.canonical_id`,
  a variation alias a `variation_aliases` row; each carries `decided_by` (FK accounts) and
  `decided_at`, and the canonical row may take a preferred `name`. No rule ever sets one:
  the suggestion queue offers the pair and a person confirms it in the merge session (the
  first waiting is Paco Rabanne to Rabanne). Applying an alias (`merges.apply_*_alias`)
  moves what hung off it (a brand's lines, a line's products), re-keys the affected
  products through the same call ingest uses and folds whatever then agrees on all four
  fields. Every reader resolves alias to canonical: the key (`services/keying.py`), the
  brand page (`catalog_queries.brand_by_slug`), the listings table and the merge queue.
- **What a merge inherits (M7).** The barcode: the survivor keeps its own, or gains the
  merged row's when it has none, and then `gtin_source` says `merge` so markup can tell a
  barcode the survivor's own shops never published from one they did (NULL or `collected`);
  two different barcodes are a veto no rule overrides, and when a person confirms such a
  pair the record (`product_merges.detail.barcode`) says which barcode won, which was
  dropped and why, from the note the merge session requires; a barcode is never invented,
  and a listing never carries one its shop did not publish (the raw record keeps what was
  published). The image: the survivor inherits one it lacks, with its provenance
  (`image_source`, found by barcode or by name) and the record says which row it came
  from; a survivor with its own image keeps it. Everything else the survivor lacks (size,
  strength, origin, category) it takes from the merged row, and a human value on the
  survivor is never overwritten.
- **Merges are recorded, never deletes.** A merged product keeps its row with
  `merged_into_id` set; its listings and awards move to the survivor; `product_merges`
  records each move with both sides as they were; `merge_candidates` is the queue for a
  person at three levels (`level` brand | line | product, the pair in `left_id` and
  `right_id`): the product pairs the rules could not settle (two barcodes, two
  concentrations), and the suggestions `app.cli suggest` derives by rule
  (`services/suggest.py`: a known rebrand, one house's name within another's with line
  names in common, one line's words within another's under one house, the same line and
  size where one side names no variation), each with a `score` and one line a person
  reads (`detail.why`). A decided pair is never touched again, so a rejection never
  resurfaces; a pair one side of which is merged or aliased away is closed as
  superseded. `catalog_queries.resolve_product_id` follows the chain so old ids answer
  under the survivor. `reverifications` is the disagreement queue (Decision 7), columns
  only for now.

- **The account system** (migration #4, `ACCOUNTS.md`): `accounts` extended (username, status),
  `account_credentials` (the hash off the principal row), the kit's `account_levels`,
  `account_members` and `account_grants`, `sessions` (opaque tokens hashed at rest),
  `account_tokens`, `audit_log`, and the seven who-columns (`*_id FK accounts NULL` beside the
  typed text, which stays as the raw record). `overrides` rides with it as the single record of
  human decision (build plan §4 and §9 #7); its first writer is the award pin.
- **Discussions** (migration #6, `app/models/discussion.py`, `CLIENT-SURFACES.md` Comments):
  `threads` (one conversation per `(subject_type, subject_id)`, unique; the keys are frozen),
  `discussion_comments` with `thread_id`, `edited_at` and `deleted_at` (soft delete only; the
  legacy `item_id` and `feature_key` stay for one release), `notifications` (the Interaction
  Standard's event with the who-columns as account ids and a unique `dedupe_key`),
  `email_sends` (the mail caps' ledger) and `account_preferences`. Migration `e9f0a1b2c3d4`
  (the side panel) adds `thread_reads` (one row per thread and account, `read_at` moved on
  every open: what is unread is computed from it, never stored), the needs-follow-up mark on
  `threads` (`followup_note`, `followup_by_id`, `followup_at`, NULL together when there is
  none) and, on `discussion_comments`, where a re-filed comment came from
  (`moved_from_thread_id`, `moved_by_id`, `moved_at`); a `notifications` row the legacy-mention
  backfill emits is dated by its comment, not by the run. Migration `f0a1b2c3d4e5` (the
  discussion workflow, `.logs/planning/discussion-workflow-2026-09-14.md`) adds on `threads` the
  resolution's `outcome` (`done` or `later`, NULL while open), `closing_comment_id` (the comment
  posted as the closing word, a plain integer so the two tables do not reference each other in a
  cycle) and `archived_at`, `archived_by_id`; `thread_asks` (one hand-off, for one person, a note,
  done by whom and when) and `comment_acks` ("Got it", one row per person and comment);
  `f1a2b3c4d5e6` adds `comment_flags` (a personal flag, one row per person and comment). Every
  table people write is listed in
  `CLIENT_WRITTEN_TABLES` (`app/services/discussion.py`), which the staging refresh reads
  (`RUNBOOK.md`).

## Migrations
One Alembic head. Since build plan section 2 (4 Sep) a migration is schema-only (create
tables, add nullable or server-defaulted columns) and every backfill is an idempotent CLI
command; the earlier revisions in the chain repaired data in place and stay as history.
Procedure: `RUNBOOK.md`.

## Tables
Generated from the SQLAlchemy metadata by `main/scripts/docmap.py`; do not edit by hand.

<!-- docmap:tables:start -->
- **account_credentials**: One row per account that can sign in (or is waiting to): the argon2id hash, the  
  *account_id->accounts, password_hash?, password_set_at?, must_change_password, failed_logins, failed_window_started_at?, locked_until?, google_sub?
- **account_grants**: A per-instance level (the kit's `grants`). Instances are brands, read-only; unused  
  *username->accounts, *instance_id, level->account_levels
- **account_levels**: A named set of permissions and the levels its holders may assign (the kit's `levels`).  
  *name, permissions, assignable, created_at, updated_at
- **account_members**: An account's app-wide level (the kit's `members`). `active` false is the kit's soft  
  *username->accounts, level->account_levels, all_instances, active, added_at
- **account_preferences**: Per-person settings the inbox reads: whether notification mail goes out at all.  
  *account_id->accounts, mail_notifications, updated_at
- **account_tokens**: A welcome or reset link's token, hashed, single use, 48 hours. The token itself travels  
  *token_hash, account_id->accounts, mode, created_at, expires_at, used_at?, created_by_id->accounts?
- **accounts**: Who did something: the one identity table, the FK target for every who-column.  
  *id, username?, email?, display_name, status, created_at, last_login_at?, disabled_at?, invited_by_id->accounts?
- **airport_hours**  
  *id, location_id->locations, source_kind, entered_by_id->accounts?, observed_at, source_url?, text, detail?, created_at
- **articles**: Editorial text of one of three kinds; `published` is the only state the site shows.  
  *id, slug, title, standfirst?, body_md, kind, category?, airport_code?, brand_id->brands?, author_id->accounts?, status, published_at?, hero_image?, source_upload_id->client_uploads?, created_at, updated_at
- **audit_log**: Every state change that names a person: who really acted (`account_id`), who they were  
  *id, at, account_id->accounts?, acting_as_id->accounts?, session_hash?, action, entity_type?, entity_key?, detail?, ip?
- **audit_snapshots**  
  *id, taken_at, metrics
- **awards**: A competition medal attached to a product.  
  *id, product_id->products, competition, competition_slug?, year?, medal?, score?, is_own_competition, source?, created_at, updated_at
- **brands**: One row per brand, whatever the shops call it (migration #3, Decision 6).  
  *id, slug, name, canonical_id->brands?, decided_by->accounts?, decided_at?, created_at, updated_at
- **client_todos**  
  *id, owner, kind, accepts_files, title, detail?, due?, status, sort, completed_by?, completed_by_id->accounts?, completed_at?, created_at
- **client_uploads**  
  *id, todo_id->client_todos, original_name, stored_name, content_type, kind, bytes, uploaded_by, uploaded_by_id->accounts?, note?, created_at
- **collection_runs**  
  *id, source_id->sources, started_at, finished_at?, status, products_seen, prices_written, skipped_no_price, skip_counts, error?, fx_source?, fx_fetched_at?
- **comment_acks**: "Got it" on a comment: seen and agreed, one row per person and comment, no words. It  
  *id, comment_id->discussion_comments, account_id->accounts, created_at
- **comment_flags**: A personal flag on a comment (rian's card design): one row per person and comment, seen  
  *id, comment_id->discussion_comments, account_id->accounts, created_at
- **discussion_comments**: A comment in a thread. `author_id` is the account it was written from (the effective one  
  *id, thread_id->threads?, item_id->discussion_items?, feature_key?, author, author_id->accounts?, body, edited_at?, deleted_at?, moved_from_thread_id->threads?, moved_by_id->accounts?, moved_at?, created_at, updated_at
- **discussion_items**: A decision card raised with the client. Deliberately terse: a headline and a line of  
  *id, title, note?, reality?, recommendation?, theme?, needs_decision, resolved, sort_order, created_at, updated_at
- **email_sends**: One row per message sent, keyed by the cap bucket it counts against (`auth:<account  
  *id, bucket, sent_at
- **feature_priorities**: The client's demo-scope ranking: one row per feature, latest choice wins.  
  *id, feature_key, priority, author, author_id->accounts?, created_at, updated_at
- **listings**: One product as carried by one location, in three layers (rian, 14 Sep; Stream L).  
  *id, product_id->products, location_id->locations, source_sku, url?, listed_brand?, listed_name?, listed_variant?, listed_quantity_text?, listed_category?, listed_gtin?, listed_brand_key?, listed_name_key?, listed_quantity_value?, listed_quantity_unit?, listed_record_id->raw_records?, last_seen_at?, pinned_product_id->products?, pinned_by->accounts?, pinned_at?, ignored_at?, ignored_by->accounts?, ignore_reason?, created_at, updated_at
- **locations**: An airport storefront. `iata` is null for a retailer-wide catalogue.  
  *id, retailer_id->retailers, code, iata?, name, city?, country?, currency, is_catalogue_only, visible, created_at, updated_at
- **merge_candidates**: Two things the rules suspect are one, queued for a person: two products, two  
  *id, level, left_id?, right_id?, product_id->products?, candidate_id->products?, reason, score?, detail?, detected_at, decided_at?, decided_by->accounts?, decision?
- **notifications**: The Interaction Standard's event, stored: "you personally need to know this". One row  
  *id, recipient_id->accounts, actor_id->accounts?, app, kind, category, context_label, body, url, source_type, source_id, project_id?, dedupe_key, read, resolved, delivered_at?, created_at
- **overrides**: One record of every human decision over a collected value (build plan §4 item 1):  
  *id, entity_type, entity_key, field, value?, collected_value?, set_by->accounts?, set_at, reason?, collector_disagrees_since?
- **owner_item_states**  
  *item_id, status, decision?, acted_by?, acted_by_id->accounts?, acted_at?, updated_at
- **price_observations**: A price seen at a point in time, with the context it was seen under.  
  *id, listing_id->listings, price, currency, price_usd?, was_price?, price_type, in_stock?, observed_at, run_id->collection_runs?, source_kind, fx_rate?
- **product_lines**: The real product above the priced one: "1 Million" over its EDT, Parfum and Elixir  
  *id, brand_id->brands, key, name, slug, canonical_id->product_lines?, decided_by->accounts?, decided_at?, created_at, updated_at
- **product_merges**: The record of one merge: `from_id` now forwards to `to_id`.  
  *id, from_id->products, to_id->products, merged_by->accounts?, merged_at, reason, detail?
- **products**: A canonical product, keyed on GTIN where we have one.  
  *id, gtin?, match_key, brand?, brand_id->brands?, name, vertical, category?, size_value?, size_unit?, size_ml?, quantity_value?, quantity_unit?, pack_count?, pack_unit_value?, form?, set_contents?, quantity_state?, name_key?, abv?, attributes, identity_rules_version, merged_into_id->products?, line_id->product_lines?, gtin_source?, country_of_origin?, is_exclusive, image_url?, thumb_url?, image_source?, image_checked, created_at, updated_at
- **quote_requests**: A submitted "please quote this" with the basket frozen at that moment.  
  *id, author, author_id->accounts?, note?, items, total_usd, created_at
- **quote_selections**: The client's yes/no on one quote line item; latest choice wins.  
  *id, item_key, included, author, author_id->accounts?, created_at, updated_at
- **raw_records**: The parsed fragment a listing was derived from, kept per run.  
  *id, listing_id->listings, run_id->collection_runs?, payload, parser_version, created_at
- **rejected_observations**: A listing refused at the collector or at ingest, with why and what it was.  
  *id, stage, reason, source_sku?, url?, payload?, run_id->collection_runs?, created_at
- **retailers**  
  *id, slug, name, operator?, homepage?, created_at, updated_at
- **reverifications**: A product or listing queued for a fresh read because something disagreed.  
  *id, product_id->products?, listing_id->listings?, reason, score?, queued_at, done_at?, result?, detail?
- **sessions**: A signed-in browser: the sha256 of an opaque token, absolute expiry, revocation, and  
  *token_hash, account_id->accounts, created_at, expires_at, last_seen_at?, revoked_at?, revoked_by_id->accounts?, revoke_reason?, amr?, acting_as->accounts?, acting_mode?, ip?, user_agent?, data
- **sources**: A collector definition, with the per-source kill switch and politeness caps.  
  *id, slug, name, enabled, delay_seconds, max_concurrency, notes?, identity_mode, permission_record?, created_at, updated_at
- **subscribers**: Someone who asked for the newsletter: the fields the client asked for, and their consent.  
  *id, email, first_name?, last_name?, home_airport?, interests, consent_at, consent_text, source, created_at, unsubscribed_at?
- **thread_asks**: The one hand-off: on a thread, for one person, a line saying what (confirm, respond,  
  *id, thread_id->threads, for_id->accounts, by_id->accounts?, note, kind?, created_at, done_at?, done_by_id->accounts?
- **thread_reads**: When an account last opened a thread (T12): one row per (thread, account), `read_at`  
  *id, thread_id->threads, account_id->accounts, read_at
- **threads**: One conversation per subject. `resolved` propagates to the notifications the thread  
  *id, subject_type, subject_id, label?, created_at, resolved, resolved_by_id->accounts?, resolved_at?, followup_note?, followup_by_id->accounts?, followup_at?, outcome?, closing_comment_id?, archived_at?, archived_by_id->accounts?
- **variation_aliases**: One wording of a variation and the canonical one it means, per vertical.  
  *id, vertical, raw, canonical, display?, kind?, decided_by->accounts?, decided_at?, created_at
- **verification_checks**  
  *id, run_id->verification_runs, listing_id->listings, observation_id->price_observations?, source_id->sources?, live_price?, live_currency?, live_size_ml?, live_name?, live_gtin?, live_in_stock?, live_was_price?, verdict, detail?, checked_at, url?, cleared_at?, cleared_by->accounts?, cleared_note?
- **verification_runs**  
  *id, started_at, finished_at?, seed, n, per_source, mode
<!-- docmap:tables:end -->