# 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 v2: `match_key` leads with the
  fold key and strips only the brand's own prefix from the name (`identity_rules_version`
  says which rules a row was last resolved under; `app.cli rederive` brings old rows forward).
- **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.
- **Sizes are stated, then derived.** `size_value` and `size_unit` are the size as the shop
  states it (70 cl, 100 ml, 75 g); `size_ml` is derived for volumes and stays the comparable
  figure. A stored size no single item comes in is set to unknown, never guessed.
- **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` holds the pairs the
  rules could not settle (two barcodes, two concentrations, a set beside a bottle) for a
  human. `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`. 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?
- **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?, 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?
- **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?, 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.  
  *id, product_id->products, location_id->locations, source_sku, url?, 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 products the rules suspect are one, queued for a decision.  
  *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_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?, abv?, attributes, identity_rules_version, merged_into_id->products?, 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?
- **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?
- **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 -->