# 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.** `product_variants` 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 variant at one shop.** `listings` carries the retailer's URL and
  SKU; the size is the product variant's (`quantity_stated_value`, `quantity_stated_unit`, `quantity_ml` derived), so a
  multi-size family is several product variants with a listing each, never one tile's cheapest.
- **Retailer and shop are separate.** One operator runs many airport storefronts, and a
  retailer-wide catalogue (no IATA code) can exist for identity only. `shops.visible`
  decides what the public site shows; collectors keep running for hidden shops.
- **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 brand is one row; `name` is the
  spelling shown (mixed case over capitals; a human value is never overwritten by the
  backfill); `alias_of_id` points an alias row at **the brand** it stands for (the row every
  spelling resolves to; the word "house" is retired, `VOCABULARY.md`). `product_variants.brand` keeps
  the text as collected; `product_variants.brand_id` is the fold. Identity rules v3 (migration #6):
  `match_key` is `brand | line | attribute | quantity`, with a fifth part for a set, a refill
  or a pack, so "same brand, same line, same canonical attribute, same quantity" is the key
  itself (`services/keying.py` resolves the brand through the alias and the attribute 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.** `product_variants.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 variant. Since v3 the
  canonical `attribute` is in the key and subsumes the parsed concentration in the veto
  (two Elixirs parsed as parfum and edp are one attribute); an unknown attribute 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 `product_variants.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 ProductVariant fields, never trimmed or
  cased. **Standard** is what the rules made of it, recomputable at any time, and lives on
  the product variant: 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 attribute with its `attribute_kind` in `attributes` (concentration, color,
  flavor; `attribute_aliases.kind`), and `name_key`. **Decided** is a human decision that
  survives every recomputation, and since Stream K2 **every decision at every level is one
  row in the append-only `decisions` ledger** (`overrides` is gone; see "The decisions
  ledger" below): on the product variant (`name`, `product_line`, `attribute:<kind>`, which
  every rekey and rederive reads first), on a brand or a line (`alias_of`, `name`, `hidden`,
  `indexed`), on an attribute wording (`meaning`, `display`, `kind`), on a listing
  (`pinned_to`, `ignored`). A decision about a PAIR is on the suggestion row itself
  (`suggestions.decision`, `decision_id`), and a product variant merge is a `merges` row
  carrying `decision_id` back to the ledger row that made it. The entity's own column still
  carries the effective value the readers use (`brands.alias_of_id`,
  `listings.pinned_variant_id`, `listings.ignored_at`; ingest honours a pin every sighting,
  the site's readers skip an ignored listing); the ledger row is the record, with who, when,
  why, what the rules had (`rule_value`) and what was in force immediately before
  (`prior_value`). A backfill may rewrite any standard column no decision guards
  (`services/attributes.GUARDED_COLUMNS`, derived from the attribute registry, never
  hand-kept) and never one a decision guards. **Provenance of a standard field** is read
  from two places now: a rule (`product_variants.identity_rules_version` says which rules
  last resolved the row) or a decision (the effective `decisions` row and its
  `decided_by`/`decided_at`/`reason`, read through `services/decisions/effective.py`); a
  merge additionally carries `merges.merged_by`/`merged_at`. 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. `quantity_stated_value` and `quantity_stated_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;
  `quantity_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 what a shopper searches for; the product variant is one quantity of one attribute
  of it** (migration #6, rian's decision of 12 Sep: one page per line). `product_lines`
  holds one row per brand and line key, the key a pure function of the collected
  name (`services/product_lines.py`: the name minus the brand, the sizes, the strength, the pack and
  edition tokens; beauty also minus the concentration 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"). **Identity rules v5**
  (15 Sep, the catalogue decisions §2.3 to §2.6): an attribute a reader finds never stays in
  the line key, so the shade a beauty shop marked after " / " and the flavour a confectionery
  shop marked leave the line as the concentration does (59 CHANEL Rouge Allure lines were one
  line in 59 shades; the rule is the vertical's, never the category's, and a skin-type tail
  stays); an attribute is read only from what the shop marked, never from a word in the name;
  "triple", "tri" and "twin" are the expression ("Triple Cask", "Triple Serum") unless the
  quantity parser read a pack or a set; `rederive` re-derives every row's line as well as its
  key, tombstones included; a person's line decisions follow the product variants when a rule empties
  their row; and a line row the rules emptied is pruned when nothing references it (`backfill
  prune_lines`). Every kind a reader produces carries a display setting (`product_lines.ATTRIBUTE_DISPLAY`:
  picked, shown or fact).
  `product_variants.product_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 attribute is `attributes.attribute`,
  canonical ("edt", "parfum", "elixir", "edp intense"), read from the name by rule and
  then through `attribute_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 attribute. Backfills: `lines`, `attributes`; each safe to repeat.
- **Aliases at every level are a person's decision, and readers follow them.** A brand
  alias is `brands.alias_of_id` (migration #3), a line alias `product_lines.alias_of_id`,
  an attribute alias an `attribute_aliases` row; each is recorded in the `decisions` ledger
  with who, when and why, and the row it points at may take a preferred `name`. An alias is
  never called a merge: only product variants merge. 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 product variants), re-keys the affected
  product variants 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 (`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 variant keeps its row with
  `merged_into_id` set; its listings and awards move to the survivor; `merges`
  records each move with both sides as they were; `suggestions` is the queue for a
  person at three levels (`level` brand | line | product, the pair in `left_id` and
  `right_id`): the product variant 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 brand's name within another's with line
  names in common, one line's words within another's under one brand, the same line and
  size where one side names no attribute), 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_variant_id` follows the chain so old ids answer
  under the survivor. `reverifications` is the disagreement queue (Decision 7), columns
  only for now.
- **The decisions ledger** (migration `d2e3f4a5b6c7`, Stream K2; the catalogue refactor
  plan W15, W16; `.logs/planning/decisions-model-spec-2026-09-16.md`, read through the K2
  brief where the two differ). `overrides` is gone (dropped by the migration after
  asserting it held 0 rows on every host), and `services/overrides.py` is a shim for one
  release: the same public names (`decide`, `clear`, `move`, `review`, `hidden_ids`, the pin
  and ignore routes) delegate to `services/decisions/writer.py`, with the pre-ledger field
  names mapped in one table (`line_id` -> `product_line`, `quantity` -> `attribute:quantity`,
  `canonical_id` -> `alias_of`, `pinned_variant_id` -> `pinned_to`, `review` -> `hidden`).
  `checked` is retired outright, refused `VALUE_INVALID`: an approved sheet is the quality
  fact now. Five tables:
  - **`decisions`** is the one ledger, one row per act. Never updated or deleted (a Postgres
    trigger, `decisions_append_only`, refuses `UPDATE` and `DELETE`); a later row on the
    same `(entity_type, entity_id, field)` supersedes an earlier one, newest wins by
    `(decided_at, id)` so a replayed row with an older `decided_at` never overrides a newer
    native one; undo is a row (`reverses_id`, `restores_id`), never a delete. `entity_id` is
    this host's read key (no FK, so the ledger outlives the row); `uid` and `natural_key`
    are the transport across hosts. `value` is what was decided (a reference travels as a
    natural-key string, resolved to a local row in `value_ref_id`, which every key path
    reads); `rule_value` is what the rules had at that write; `prior_value` is what was
    effective immediately before. `origin` is `person` or `proposal`; `mode` is
    `individual` or `bulk` (a filter for a person, never a strength a machine reads);
    `caused_by_id` marks a consequence row (a brand alias's cascaded line aliases, a
    merge's carried fields), whose `uid` is a `uuid5` of the cause so the same consequence
    regenerated on another host gets the same uid.
  - **`decision_batches`** is the unit of approval and of undo (W15, W16): every decision
    belongs to a batch (an individual act is a batch of one), inserted at open so
    `decisions.batch_id` is a plain NOT NULL FK, updated exactly once at close (`closed_at`,
    `summary`; a second trigger, `decision_batches_guard`, refuses anything else). `uid`
    crosses hosts, so a batch undone on staging is undone on production by the same uid;
    `reverses_batch_id` marks an undo batch.
  - **`proposal_passes`** is one row per AI pass, rule-generator run or the standing
    ingest arrival pass: the unit a proposals file loads under (`name` is the pass id the
    file carries, e.g. `claude/2026-09-18/chanel-1`) and a bad pass is withdrawn by.
  - **`proposals`** is what the review sheet reads: one row per proposed ruling over one
    value, in the ledger's own shape (`entity_type`, `natural_key`, `field`, `value`), so
    approval is a copy with provenance and nothing is re-interpreted. Mutable by design: a
    work item, not a decision, `status` `open | approved | rejected | withdrawn | stale |
    parked`, upserted on `(pass_id, entity_type, natural_key, field)`; `decision_id` names
    the row it became.
  - **`redirects`** is a slug's forwarding address, shared by brands, product lines and
    places (brief decision 7, replacing the spec's `line_slugs`): chains are flattened at
    write (A to B then B to C stores A to C), so an old slug always points at the live row;
    the replay resolver reads it last, after uid and slug/key matching.

  **Natural keys** (`services/decisions/natural_keys.py`) are the portable address of every
  row a decision can name, so a decision written on staging resolves to the right row on
  production and back, and vice versa on a refresh. One grammar, `build`/`parse` round-trip
  tested, `resolve` reading `natural_key_detail` (JSON) as the authority; the key string
  itself is a label for audit and replay, never compared on the hot path. Per entity:
  `brand:<slug>` (stable, a slug is never rewritten); `line:<uid>` (a line born before the
  production/staging fork resolves by uid; one born after resolves by slug only when the
  brand and the line's `key` agree, or the row holds a `name` decision and the names agree,
  else parked `LINE_MISMATCH`; `redirects` is the last fallback; `line:new:<slug>` names a
  line that does not exist yet, minted or adopted at approval); `variant:<uid>` (uid, then
  its own barcode, never one gained by merge, then its listings resolved through the merge
  chain: all agree is a hit, more than one live variant parks `VARIANT_SPLIT`, none found
  parks `LISTING_MISSING`); `listing:<retailer_slug>/<shop_code>/<source_sku>`;
  `wording:<vertical>|<raw>`; `pair:<level>:<keyA>||<keyB>` (sides sorted as strings; a pair
  with no rule-offered suggestion is minted on first use, reason `decided`); `place:<slug>`.
  A component containing its level's delimiter is refused at `build`, so a key is always
  parseable.

  **The effective read** (`services/decisions/effective.py`) is the one function every
  other reader calls, the sheet's "already decided" cell, the writer's own conflict check,
  `prune-lines`, the audit, so no caller can forget the rules: the newest `set` row per
  `(entity_id, field)` by `(decided_at, id)`, an effective `release` row emptying the field
  from the result. `keying.load_maps` fills `Maps.decided` from the same query in one pass;
  `Maps.separated` is every variant pair kept separate, each side chased through
  `merged_into_id` to its live survivor, so a veto follows a side that merged elsewhere.

  **The attribute registry** (`services/attributes.py`; plan W7, W8) is the one accessor
  every reader uses (`of`, `get`, `set`), so a new attribute kind is a registry line, never
  a migration or a code change. Each `Kind` carries `identity` (does it decide sameness:
  quantity always does, country of origin never does) and `display` (picked, shown or
  fact) and names where its value lives, the migration valve: `quantity` in its seven typed
  columns; `abv`, `country_of_origin` and `is_exclusive` in their own typed columns; the
  marked kinds (`concentration`, `color`, `flavor`) under `attributes.attribute` +
  `attributes.attribute_kind` (K1's shape, kept rather than folded: on the copy 1,745
  variants carry both the canonical and the parsed base value and 294 differ, so folding
  them would change the merge veto); every other kind under `attributes.<kind>`. A shop's
  own option field becomes a kind on sight (`option:<name>`), certain because the shop
  stated it, so a rule reads it without review. No GIN index on `attributes`: the
  containment query scans 17,373 rows in 4.7 ms on the copy, measured rather than assumed;
  `GUARDED_COLUMNS` is derived from the registry's storage, never hand-kept, and both
  `ingest`'s enrich guard and `merges._ENRICH_FIELDS` read it, so a decided empty (an
  explicit null) is never refilled by a machine sighting.

  **Places** (`app/models/places.py`, `services/places.py`; plan W19) is where a shop is,
  of any kind. `places` holds one row per place, `kind` a registry entry (airport, mall,
  port, a ship that moves, a border crossing, in-flight with no pages, the online catalogue
  with no primary place needed) rather than a migration; `identifiers` is an open list of
  `{scheme, value}` (IATA, UN/LOCODE, none) instead of a column per scheme. `shop_places` is
  the join, giving a shop its primary place (`role = 'primary'`, at most one per shop, a
  partial unique index) or an additional one (`role = 'also'`). The comparison unit every
  count uses is "shops counted once per primary place": a shop with a primary place counts
  as that place, a shop without one counts as its airport code (`shops.iata`), and a shop
  with neither counts as itself, the same three-way fallback in SQL (`places.unit()` /
  `unit_count()` / `unit_join()`) and in Python (`count_units()`), so every count in
  `catalog_queries.py` reads the same before `app.cli backfill places` has run (the test
  kit) and after it (every host). `backfill places` is idempotent: one airport place per
  IATA code from `shops.iata`, mirrored into `identifiers`, every shop of that airport
  given its `shop_places` row with role `primary`; a second run changes nothing.

  **uid, hidden, indexed, and the merge record beside the ledger.** `brands`, `product_lines`,
  `product_variants`, `suggestions` and `places` each carry a `uid UUID NOT NULL UNIQUE`
  (server-defaulted on Postgres, a Python default everywhere else), born at insert: since
  production is a copy of staging at launch, every row that exists then carries the same
  uid on both hosts forever, and only a row born after the fork falls back to a barcode, its
  listings, or a slug for resolution. `hidden` and `indexed` are materialised booleans on
  `brands`, `product_lines` and `places` (a partial index on each), the decided value behind
  W18's publish and search-indexing questions; the old `review` state and `hidden_ids()`
  read these columns now, and `checked` is retired. `suggestions` gains `closed_reason`
  (`superseded`, `withdrawn`, the algorithm's own closures, kept apart from `decision`,
  which is now a person's ruling only, `same` or `separate`) and `decision_id` (the ledger
  row that made it so), with a partial index on `separate` pairs for the automatic-fold
  veto. `merges` gains `decision_id` (the ledger row, when a person's; a rules fold keeps
  it NULL), `batch_id` (so batch undo reverses the tail fold its own batch made),
  `reversed_by_id` and `reversed_at`: the merge record carries its own reversal pointer, so
  a second `merges` row is never needed to undo one.

- **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). The `decisions` ledger (Stream K2, replacing
  `overrides`) is the single record of human decision now; its first writer was 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`.

## Relationships
One diagram per area, generated from the SQLAlchemy metadata by `main/scripts/docmap.py`; do
not edit by hand. A line joins the table a key names to the table that holds it, labelled with
the column; `||` at the named end is a key that must be filled, `|o` one that may be empty. The
who-columns that point at `accounts` are listed under each diagram rather than drawn. The same
metadata, with every column and key on one page for a person, is `main/scripts/dbmap.py`.

<!-- docmap:relations:start -->
### Catalogue
```mermaid
erDiagram
  retailers ||--o{ shops : "retailer_id"
  shops ||--o{ airport_hours : "shop_id"
  brands |o--o{ brands : "alias_of_id"
  brands ||--o{ product_lines : "brand_id"
  product_lines |o--o{ product_lines : "alias_of_id"
  brands |o--o{ product_variants : "brand_id"
  product_variants |o--o{ product_variants : "merged_into_id"
  product_lines |o--o{ product_variants : "product_line_id"
  product_variants ||--o{ listings : "variant_id"
  shops ||--o{ listings : "shop_id"
  raw_records |o--o{ listings : "listed_record_id"
  product_variants |o--o{ listings : "pinned_variant_id"
  product_variants ||--o{ awards : "variant_id"
```
Who-columns, to `accounts`: `airport_hours.entered_by_id`, `brands.decided_by`, `product_lines.decided_by`, `attribute_aliases.decided_by`, `listings.pinned_by`, `listings.ignored_by`.
### Collection
```mermaid
erDiagram
  sources ||--o{ collection_runs : "source_id"
  listings ||--o{ raw_records : "listing_id"
  collection_runs |o--o{ raw_records : "run_id"
  listings ||--o{ price_observations : "listing_id"
  collection_runs |o--o{ price_observations : "run_id"
  collection_runs |o--o{ rejected_observations : "run_id"
```
### Decisions and merges
```mermaid
erDiagram
  decisions |o--o{ suggestions : "decision_id"
  product_variants ||--o{ merges : "from_id"
  product_variants ||--o{ merges : "to_id"
  decisions |o--o{ merges : "decision_id"
  decision_batches |o--o{ merges : "batch_id"
  decisions |o--o{ merges : "reversed_by_id"
  product_variants |o--o{ reverifications : "variant_id"
  listings |o--o{ reverifications : "listing_id"
```
Who-columns, to `accounts`: `suggestions.decided_by`, `merges.merged_by`.
### Quality
```mermaid
erDiagram
  verification_runs ||--o{ verification_checks : "run_id"
  listings ||--o{ verification_checks : "listing_id"
  price_observations |o--o{ verification_checks : "observation_id"
  sources |o--o{ verification_checks : "source_id"
```
Who-columns, to `accounts`: `verification_checks.cleared_by`.
### Accounts
```mermaid
erDiagram
  accounts |o--o{ accounts : "invited_by_id"
  account_levels ||--o{ account_members : "level"
  account_levels ||--o{ account_grants : "level"
```
Who-columns, to `accounts`: `account_credentials.account_id`, `account_members.username`, `account_grants.username`, `sessions.account_id`, `sessions.revoked_by_id`, `sessions.acting_as`, `account_tokens.account_id`, `account_tokens.created_by_id`, `audit_log.account_id`, `audit_log.acting_as_id`, `account_preferences.account_id`.
### Discussion
```mermaid
erDiagram
  threads |o--o{ discussion_comments : "thread_id"
  discussion_items |o--o{ discussion_comments : "item_id"
  threads |o--o{ discussion_comments : "moved_from_thread_id"
  threads ||--o{ thread_asks : "thread_id"
  threads ||--o{ thread_reads : "thread_id"
  discussion_comments ||--o{ comment_acks : "comment_id"
  discussion_comments ||--o{ comment_flags : "comment_id"
```
Who-columns, to `accounts`: `threads.resolved_by_id`, `threads.followup_by_id`, `threads.archived_by_id`, `discussion_comments.author_id`, `discussion_comments.moved_by_id`, `thread_asks.for_id`, `thread_asks.by_id`, `thread_asks.done_by_id`, `thread_reads.account_id`, `comment_acks.account_id`, `comment_flags.account_id`, `notifications.recipient_id`, `notifications.actor_id`.
### Client and owner surfaces
```mermaid
erDiagram
  client_todos ||--o{ client_uploads : "todo_id"
```
Who-columns, to `accounts`: `feature_priorities.author_id`, `quote_selections.author_id`, `quote_requests.author_id`, `client_todos.completed_by_id`, `client_uploads.uploaded_by_id`, `owner_item_states.acted_by_id`.
### Editorial
```mermaid
erDiagram
  brands |o--o{ articles : "brand_id"
  client_uploads |o--o{ articles : "source_upload_id"
```
Who-columns, to `accounts`: `articles.author_id`.
### Other (decisions)
```mermaid
erDiagram
  proposal_passes |o--o{ decision_batches : "pass_id"
  decision_batches |o--o{ decision_batches : "reverses_batch_id"
  decisions |o--o{ decisions : "supersedes_id"
  decisions |o--o{ decisions : "reverses_id"
  decisions |o--o{ decisions : "restores_id"
  decisions |o--o{ decisions : "caused_by_id"
  proposals |o--o{ decisions : "proposal_id"
  proposal_passes |o--o{ decisions : "pass_id"
  decision_batches ||--o{ decisions : "batch_id"
  proposal_passes ||--o{ proposals : "pass_id"
  decisions |o--o{ proposals : "against_decision_id"
  decisions |o--o{ proposals : "decision_id"
  decisions |o--o{ redirects : "decision_id"
```
Who-columns, to `accounts`: `decision_batches.by_account_id`, `decision_batches.acting_as_id`, `decisions.decided_by`, `proposal_passes.loaded_by`, `proposal_passes.withdrawn_by`, `proposals.resolved_by`.
### Other (places)
```mermaid
erDiagram
  places |o--o{ places : "parent_id"
  shops ||--o{ shop_places : "shop_id"
  places ||--o{ shop_places : "place_id"
```
<!-- docmap:relations:end -->

## 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, shop_id->shops, 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
- **attribute_aliases**: One wording of a attribute and the canonical one it means, per vertical.  
  *id, vertical, raw, canonical, display?, kind?, decided_by->accounts?, decided_at?, created_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, variant_id->product_variants, 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, uid, hidden, indexed, name, alias_of_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
- **decision_batches**: The unit of approval and of undo (spec §1.2): inserted at open, updated exactly once at  
  *id, uid, opened_at, closed_at?, origin_host, kind, mode, by_account_id->accounts?, by_username?, acting_as_id->accounts?, pass_id->proposal_passes?, scope?, reverses_batch_id->decision_batches?, replayed_from?, note?, summary
- **decisions**: One act of a person, or of a person approving a proposal (spec §1.1). Never updated or  
  *id, uid, decided_at, recorded_at, origin_host, entity_type, entity_id, natural_key, natural_key_detail?, field, effect, value?, value_ref_id?, rule_value?, prior_value?, supersedes_id->decisions?, reverses_id->decisions?, restores_id->decisions?, caused_by_id->decisions?, rules_version, origin, proposal_id->proposals?, pass_id->proposal_passes?, batch_id->decision_batches, mode, decided_by->accounts?, decided_by_username?, reason?, replayed_from?, detail?
- **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 shop, in three layers (rian, 14 Sep; Stream L).  
  *id, variant_id->product_variants, shop_id->shops, 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_variant_id->product_variants?, pinned_by->accounts?, pinned_at?, ignored_at?, ignored_by->accounts?, ignore_reason?, created_at, updated_at
- **merges**: The record of one merge: `from_id` now forwards to `to_id`.  
  *id, from_id->product_variants, to_id->product_variants, merged_by->accounts?, decision_id->decisions?, batch_id->decision_batches?, reversed_by_id->decisions?, reversed_at?, merged_at, reason, detail?
- **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
- **owner_item_states**  
  *item_id, status, decision?, acted_by?, acted_by_id->accounts?, acted_at?, updated_at
- **places**  
  *id, uid, slug, kind, name, parent_id->places?, identifiers, attributes, hidden, indexed, created_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, uid, hidden, indexed, name, slug, alias_of_id->product_lines?, decided_by->accounts?, decided_at?, created_at, updated_at
- **product_variants**: A canonical product, keyed on GTIN where we have one.  
  *id, uid, gtin?, match_key, brand?, brand_id->brands?, name, vertical, category?, quantity_stated_value?, quantity_stated_unit?, quantity_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->product_variants?, product_line_id->product_lines?, gtin_source?, country_of_origin?, is_exclusive, image_url?, thumb_url?, image_source?, image_checked, created_at, updated_at
- **proposal_passes**: One AI pass, rule-generator run or ingest's standing arrival pass (spec §1.3): the unit a  
  *id, name, kind, process_version, rules_version, generator, scope_brand_slug?, loaded_at, loaded_by->accounts?, loaded_by_username?, source_file?, file_sha256?, counts, note?, withdrawn_at?, withdrawn_by->accounts?, withdrawn_reason?
- **proposals**: What the review sheet reads (spec §1.4): one row per proposed ruling over one value, in the  
  *id, uid, pass_id->proposal_passes, brand_slug, sheet_line_ref?, position, entity_type, natural_key, natural_key_detail?, entity_id?, resolution, field, value, rule_value?, current_value?, against_decision_id->decisions?, generator, reason?, evidence, confidence?, spot_check, attention?, status, corrected_value?, decision_id->decisions?, resolution_note?, resolved_at?, resolved_by->accounts?, detail, 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
- **redirects**: A slug's forwarding address (brief decision 7; plan W18): brands, product lines and places  
  *from_slug, kind, to_slug, since, decision_id->decisions?
- **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, variant_id->product_variants?, 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
- **shop_places**  
  *shop_id->shops, *place_id->places, role
- **shops**: 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
- **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?
- **suggestions**: Two things the rules suspect are one, queued for a person: two product variants, two  
  *id, level, uid, closed_reason?, decision_id->decisions?, left_id?, right_id?, reason, score?, detail?, detected_at, decided_at?, decided_by->accounts?, decision?
- **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?
- **verification_checks**  
  *id, run_id->verification_runs, listing_id->listings, observation_id->price_observations?, source_id->sources?, live_price?, live_currency?, live_quantity_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 -->