---
type: plan
version: 1
status: the specification of the decisions ledger and the proposals store, synthesised 2026-09-16 by a multi-agent think (three designs from three angles, each attacked through two lenses, one synthesis); the brief of stream K2 builds it and K3, K4 and K6 read through it. The plan's escalation 2. Nothing here is built.
supersedes: the 15 Sep decisions §2.2 (the current-state `overrides` table)
---
# The decisions ledger and the proposals store: specification

Status: synthesised 2026-09-16 from three designs and their attacks, for the S1 stream brief. Serves plan §0 and decisions W7, W9, W10, W13, W14, W15, W16, W17, W18. Every table, column and rule below is written in the plan's W2 words. Nothing here is built.

Facts it is designed against: 0 rows in `overrides`, 0 aliases, 0 human pair decisions, 0 pins, 0 ignores on staging; 16,761 live product variants (9,865 with a barcode, 6,896 without, of which 2,389 hold two or more listings and 24 hold none); 13,772 product lines (276 whose slug differs from the deterministic form, 115 with a `-N` suffix); 2,365 brands; 23,771 listings; `merge_candidates` holds 3 machine-closed rows (`superseded`, `withdrawn`) that `suggest.generate` keeps writing; one merge chain of length two exists (7055 -> 3502 -> 11373); no `source_sku` contains `/`, `,` or `|` (1,451 contain `:`); the 23 shop codes are distinct and no slug contains `|`; the alembic head is `b3c4d5e6f7a8`; production becomes a copy of staging at launch (W17) and afterwards every sequence continues from the same value on both hosts, so local ids collide across hosts from the first post-launch write.

## 0. The five rules every section obeys

1. **One append-only table of decisions.** A row is never updated or deleted; a later row on the same field supersedes an earlier one; undo is a row; batch undo is rows in one transaction. A Postgres trigger refuses `UPDATE` and `DELETE`; the service exposes no path for either, and the SQLite suites pin that.
2. **Two identities per row, for two jobs.** `entity_id` (this host's row id) is the read key: the effective decision is the newest row per `(entity_type, entity_id, field)`, and every conflict check, lock and materialisation uses it. `uid` plus `natural_key` are the transport: replay resolves the natural key on the target host and matches rows by uid. A natural key string is never compared for equality on the hot path, because a barcode-less variant's listing set and a derived product line's slug both change under the events the ledger records.
3. **Every catalogue row that a decision can name carries a uid born at insert** (`brands`, `product_lines`, `products`, `merge_candidates`). Production is a copy of staging, so every row that exists at launch has the same uid on both hosts forever; only rows born after the fork fall back to gtin, listing keys or slug.
4. **Newest wins by `(decided_at, id)`, not by `id` alone.** A native write always has `decided_at = now()`, so locally this is id order. A replayed row keeps its source `decided_at`; it becomes effective only if nothing newer stands on that field on the target, and it is never materialised over a newer decision. The writer refuses a `decided_at` in the future.
5. **A machine never writes over a person.** A `mode = bulk` write (a sheet's approve-all, a replay of one) refuses when the effective row on the field holds a different value and is `origin = person` or `mode = individual`. The check runs inside the writer, after the lock, never from a stored flag. The automatic merge never folds a group that contains a Keep separate pair or a variant holding any effective decision on an identity field.

## 1. Tables

Every JSON column is `JSON().with_variant(JSONB(), "postgresql")` (`JSONVariant`, as `accounts.py` already defines it); every uid is `sqlalchemy.Uuid(as_uuid=True)` (native on Postgres, CHAR(32) on SQLite) with `server_default=gen_random_uuid()` on Postgres and a Python `uuid4` default everywhere.

### 1.1 `decisions`

The one ledger (W15). One row per act.

| column | type | constraint | meaning |
|---|---|---|---|
| `id` | BIGSERIAL | PK | local order of writing; never exported |
| `uid` | UUID | NOT NULL UNIQUE | the decision's identity on every host; a replayed row keeps the source's uid; a consequence row's uid is `uuid5(cause.uid, entity_type + "|" + natural_key + "|" + field)` so the same consequence regenerated on another host gets the same uid |
| `decided_at` | TIMESTAMPTZ | NOT NULL, set by the writer as `datetime.now(UTC)` per row (never the transaction clock) | when the act happened; carried as-is on replay |
| `recorded_at` | TIMESTAMPTZ | NOT NULL DEFAULT now() | when this host inserted it; differs from `decided_at` only on replay |
| `origin_host` | VARCHAR(20) | NOT NULL | `settings.app_env` of the writing host (`staging`, `production`); for the ledger page and the refresh export, never for matching |
| `entity_type` | VARCHAR(20) | CHECK IN (`brand`, `product_line`, `product_variant`, `listing`, `attribute_wording`, `suggestion`) | |
| `entity_id` | BIGINT | NOT NULL | this host's row id (`brands.id`, `product_lines.id`, `products.id`, `listings.id`, `variation_aliases.id`, `merge_candidates.id`); no FK on purpose (the ledger outlives the row); never NULL (an unresolvable replay row is parked in the remainder file, never inserted) |
| `natural_key` | VARCHAR(500) | NOT NULL | the portable address at write time (section 2); a label for replay and audit, never grouped on |
| `natural_key_detail` | JSON | NULL | the resolution hints (section 2): the full listing list, the gtin and its source, the line's brand slug, slug, key and rules version, a pair's two sides as a JSON array |
| `field` | VARCHAR(80) | CHECK `~ '^[a-z_]+(:[a-z0-9_-]{1,40}){0,2}$'` | section 1.7; two colon segments allow `attribute:option:talla` |
| `effect` | VARCHAR(8) | CHECK IN (`set`, `release`) | `set`: `value` is decided; `release`: the field returns to the rules (readers treat an effective `release` row as no decision) |
| `value` | JSON | NULL | the decided value; a reference is a natural-key string (`line:<uid>`, `brand:<slug>`, `variant:<uid>`), never an id; a pair decision's value is the object in section 5; NULL only on a `release` |
| `value_ref_id` | BIGINT | NULL | the local row a reference value resolved to at write time (`product_line` -> `product_lines.id`, `alias_of` -> the target's id, `merged_into` / `pinned_to` -> `products.id`); recomputed on replay; the key path reads this, never the string |
| `rule_value` | JSON | NULL | what the rules had for the field at this write (section 4, step d); `keying.keyed_name` reads it for `name` |
| `prior_value` | JSON | NULL | the effective value on the field immediately before this row (the previous effective decision's value, else the rule's); what an undo restores when no earlier decision exists |
| `supersedes_id` | BIGINT | NULL FK `decisions.id` | the row that was effective on this field before this one; NULL when the rules were |
| `reverses_id` | BIGINT | NULL FK `decisions.id` | set on an undo row: the row it reverses (structured, never parsed out of `reason`) |
| `restores_id` | BIGINT | NULL FK `decisions.id` | set on an undo row: the earlier row that is effective again (`= reverses.supersedes_id`); NULL with `effect = release` |
| `caused_by_id` | BIGINT | NULL FK `decisions.id` | a consequence row: the cause whose materialisation produced it (a brand alias's line aliases, a merge's carried rows, a preferred name typed at confirm) |
| `rules_version` | VARCHAR(8) | NOT NULL | `IDENTITY_RULES_VERSION` at write |
| `origin` | VARCHAR(10) | CHECK IN (`person`, `proposal`) | who ruled: a person's own act, or a person approving a proposal |
| `proposal_id` | BIGINT | NULL FK `proposals.id` (named `fk_decisions_proposal`, `use_alter=True`; the cycle with `proposals.decision_id`) | the proposal this row approved, or the proposal a person's correction answers |
| `pass_id` | BIGINT | NULL FK `proposal_passes.id` | denormalised from the proposal so "every decision of pass X" is one index scan; CHECK `(origin = 'proposal') <= (pass_id IS NOT NULL)` (a person's correction may carry the pass it corrects) |
| `batch_id` | BIGINT | NOT NULL FK `decision_batches.id` | every decision is in a batch; an individual act is a batch of one |
| `mode` | VARCHAR(10) | CHECK IN (`individual`, `bulk`) | the request's own word; a filter for a person, never a strength a machine reads (W15) |
| `decided_by` | BIGINT | NULL FK `accounts.id` | NULL only on a replayed row whose approver has no account on this host |
| `decided_by_username` | VARCHAR(64) | NULL | the portable who; survives account deletion and id drift |
| `reason` | TEXT | NULL | the sentence a person typed, or the proposal's reason |
| `replayed_from` | VARCHAR(60) | NULL | `<source host>@<replay stamp>` when the row arrived by replay |
| `detail` | JSON | NULL | writer-specific record: a merge's `merges` row id; an alias's lines created and pointed; `carried_from` on a carried row; `adopted_from_key` on a sheet line; `conflict` on a replayed row that did not become effective |

Constraints beyond the column CHECKs:

- CHECK `(effect = 'release') = (value IS NULL)`.
- CHECK `(reverses_id IS NULL) = (restores_id IS NULL AND effect = 'set' AND reason NOT LIKE 'undo of %')` is too clever; state it as: `reverses_id IS NULL OR origin = 'person'` (an undo is always a person's act) and `restores_id IS NULL OR reverses_id IS NOT NULL`.
- UNIQUE `(reverses_id)` WHERE `reverses_id IS NOT NULL`: a row is reversed once; undoing an undo reverses the undo row.
- Postgres trigger `decisions_append_only` BEFORE UPDATE OR DELETE FOR EACH ROW: `RAISE EXCEPTION`. Created by `op.execute` guarded by `bind.dialect.name == "postgresql"`.

Indexes:

- `ix_decisions_effective (entity_type, entity_id, field, decided_at DESC, id DESC)`: the newest-wins read and the key path's one load.
- `ix_decisions_natural (entity_type, natural_key)`: audit by address; replay diagnostics.
- `ix_decisions_batch (batch_id, id)`.
- `ix_decisions_pass (pass_id, id)` WHERE `pass_id IS NOT NULL`.
- `ix_decisions_proposal (proposal_id)` WHERE `proposal_id IS NOT NULL`.
- `ix_decisions_caused_by (caused_by_id)` WHERE `caused_by_id IS NOT NULL`: an undo walks consequences first.
- `ix_decisions_reverses (reverses_id)` WHERE `reverses_id IS NOT NULL`.
- `ix_decisions_decided_at (decided_at)`: the tracked-changes view and `--since` exports.
- `ix_decisions_by (decided_by, decided_at)`.

### 1.2 `decision_batches`

The unit of approval and of undo (W15, W16).

| column | type | constraint | meaning |
|---|---|---|---|
| `id` | BIGSERIAL | PK | |
| `uid` | UUID | NOT NULL UNIQUE | crosses hosts: a batch undone on staging is undone on production by the same uid |
| `opened_at` | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| `closed_at` | TIMESTAMPTZ | NULL | written once at close |
| `origin_host` | VARCHAR(20) | NOT NULL | |
| `kind` | VARCHAR(12) | CHECK IN (`sheet`, `desk`, `route`, `cli`, `undo`, `replay`) | |
| `mode` | VARCHAR(10) | CHECK IN (`individual`, `bulk`) | copied onto each row |
| `by_account_id` | BIGINT | NULL FK `accounts.id` | |
| `by_username` | VARCHAR(64) | NULL | |
| `acting_as_id` | BIGINT | NULL FK `accounts.id` | the View As trail, as `audit_log` keeps it |
| `pass_id` | BIGINT | NULL FK `proposal_passes.id` | the pass a sheet approval drew from; NULL when mixed |
| `scope` | JSON | NULL | `{brand_slug, sheet_line_ref, proposal_uids | "all"}` for a sheet; `{candidate_ids}` for the desk; `{command, args}` for the CLI; `{source_batch_uid, file}` for a replay |
| `reverses_batch_id` | BIGINT | NULL FK `decision_batches.id` | CHECK `(kind = 'undo') = (reverses_batch_id IS NOT NULL)` |
| `replayed_from` | VARCHAR(60) | NULL | |
| `note` | TEXT | NULL | |
| `summary` | JSON | NOT NULL DEFAULT `{}` | counts written at close: decisions, held, refused, parked, variants rekeyed, groups merged, merges made by the tail fold |

The batch row is inserted at open (so `decisions.batch_id` is a plain NOT NULL FK), and updated exactly once at close. The Postgres trigger `decision_batches_guard` refuses DELETE and refuses UPDATE unless `OLD.closed_at IS NULL AND NEW.closed_at IS NOT NULL` and every column other than `closed_at` and `summary` is unchanged (compared as `row_to_json` minus those two).

Indexes: `ix_decision_batches_opened (opened_at)`, `ix_decision_batches_pass (pass_id)` WHERE NOT NULL, `ix_decision_batches_reverses (reverses_batch_id)` WHERE NOT NULL, `ix_decision_batches_by (by_account_id, opened_at)`.

### 1.3 `proposal_passes`

One row per AI pass, rule-generator run, or ingest's standing arrival pass (W13). The unit a proposals file loads under and a bad pass is withdrawn by.

| column | type | constraint | meaning |
|---|---|---|---|
| `id` | BIGSERIAL | PK | |
| `name` | VARCHAR(120) | NOT NULL UNIQUE | the pass id the file carries, e.g. `claude/2026-09-18/chanel-1`; `rule:<list>:<rules_version>`; `arrival:<rules_version>`; the natural key across hosts |
| `kind` | VARCHAR(12) | CHECK IN (`session`, `rule`, `arrival`) | |
| `process_version` | VARCHAR(20) | NOT NULL | the `REVIEW-PROCESS.md` version followed (W16) |
| `rules_version` | VARCHAR(8) | NOT NULL | `IDENTITY_RULES_VERSION` the proposals were computed against |
| `generator` | VARCHAR(80) | NOT NULL | `claude-session`, `rule:<list>`, `ingest`; the file's own statement; no key, no provider config |
| `scope_brand_slug` | VARCHAR(160) | NULL | the brand when the pass is one sheet; NULL for a fleet-wide rule pass |
| `loaded_at` | TIMESTAMPTZ | NOT NULL DEFAULT now() | |
| `loaded_by` | BIGINT | NULL FK `accounts.id` | |
| `loaded_by_username` | VARCHAR(64) | NULL | |
| `source_file` | TEXT | NULL | |
| `file_sha256` | CHAR(64) | NULL | a re-load of an identical file is a no-op by content |
| `counts` | JSON | NOT NULL DEFAULT `{}` | what the last load reported |
| `note` | TEXT | NULL | |
| `withdrawn_at` | TIMESTAMPTZ | NULL | CHECK `(withdrawn_at IS NULL) = (withdrawn_by IS NULL)` |
| `withdrawn_by` | BIGINT | NULL FK `accounts.id` | |
| `withdrawn_reason` | TEXT | NULL | |

Mutable only in `counts` and `withdrawn_*`; never deleted. Indexes: `ix_proposal_passes_kind_loaded (kind, loaded_at)`, `ix_proposal_passes_scope (scope_brand_slug)`.

### 1.4 `proposals`

What the review sheet reads (W10, W13). One row per proposed ruling over one value, in the ledger's own shape, so approval is a copy with provenance and nothing is re-interpreted.

| column | type | constraint | meaning |
|---|---|---|---|
| `id` | BIGSERIAL | PK | |
| `uid` | UUID | NOT NULL UNIQUE | |
| `pass_id` | BIGINT | NOT NULL FK `proposal_passes.id` | |
| `brand_slug` | VARCHAR(160) | NOT NULL | the sheet (one sheet per brand); a brand alias proposal sits under the alias's slug |
| `sheet_line_ref` | VARCHAR(500) | NULL | the proposed product line's natural key this row renders under; NULL for a brand-level row; itself for a line-level row |
| `position` | INTEGER | NOT NULL DEFAULT 0 | the order the pass wrote the sheet in |
| `entity_type` | VARCHAR(20) | same CHECK as `decisions` | |
| `natural_key` | VARCHAR(500) | NOT NULL | never an id: a file written on staging loads on production unchanged (the `propose --file` trap, W13) |
| `natural_key_detail` | JSON | NULL | |
| `entity_id` | BIGINT | NULL | resolved on this host at load; re-resolved at read and at approve; NULL when `resolution` is not `resolved` |
| `resolution` | VARCHAR(12) | CHECK IN (`resolved`, `creatable`, `unresolved`) | `creatable` only for `product_line` / `name` (a line the approval will mint or adopt) |
| `field` | VARCHAR(80) | same CHECK as `decisions` | |
| `value` | JSON | NOT NULL | the proposed value, same shapes as `decisions.value` |
| `rule_value` | JSON | NULL | what the rules had at load; shown beside the proposal; informational, never compared for staleness |
| `current_value` | JSON | NULL | the entity's column at load; the sheet re-reads the column and flags drift |
| `against_decision_id` | BIGINT | NULL FK `decisions.id` | the effective decision on this field at load, when one existed; history only, the sheet and the writer recompute (section 6) |
| `generator` | VARCHAR(80) | NOT NULL | `ai`, `rule:<list>`, `arrival` |
| `reason` | TEXT | NULL | the one line a person reads: "removed 'blended scotch whisky' as a category word" |
| `evidence` | JSON | NOT NULL DEFAULT `[]` | `[{listing: "listing:<retailer>/<code>/<sku>", source: listed_name | listed_variant | listed_quantity_text | listed_category | option:<name>, span: [start, end], text: "..."}]`; every value cites the spans it was read from |
| `confidence` | NUMERIC(4,3) | NULL, CHECK 0..1 | NULL for a rule proposal |
| `spot_check` | BOOLEAN | NOT NULL DEFAULT false | set at load by the process rule; seeded by the pass name so a re-load re-derives the same sample; never cleared on an open row that carries it |
| `status` | VARCHAR(12) | CHECK IN (`open`, `approved`, `rejected`, `withdrawn`, `stale`, `parked`) | |
| `corrected_value` | JSON | NULL | a cell a person edited before approval; approval writes it as `origin = person`, `proposal_id` kept |
| `decision_id` | BIGINT | NULL FK `decisions.id` | the decision it became; CHECK `(status = 'approved') = (decision_id IS NOT NULL) OR status = 'rejected'` (a rejected pair proposal carries its Keep separate decision) |
| `resolution_note` | TEXT | NULL | why parked or stale; a rejection sentence |
| `resolved_at` | TIMESTAMPTZ | NULL | |
| `resolved_by` | BIGINT | NULL FK `accounts.id` | |
| `detail` | JSON | NOT NULL DEFAULT `{}` | `previously_rejected: <proposal uid>` when an identical (entity, field, value) was rejected in an earlier pass; `drift: true` at read |
| `created_at`, `updated_at` | TIMESTAMPTZ | NOT NULL | |

Constraints: UNIQUE `(pass_id, entity_type, natural_key, field)` (the idempotency key, W13). Mutable by design: a proposal is a work item, not a decision; its permanent record is the decision it became plus `audit_log`.

Indexes: `ix_proposals_sheet (brand_slug, status, sheet_line_ref, position)`, `ix_proposals_pass_status (pass_id, status)`, `ix_proposals_target (entity_type, natural_key, field)`, `ix_proposals_entity (entity_type, entity_id, field)` WHERE `entity_id IS NOT NULL`, `ix_proposals_spot (brand_slug)` WHERE `spot_check AND status = 'open'`.

### 1.5 `line_slugs`

The slug history W18 needs for the 301s and the replay resolver reads as a last fallback.

| column | type | constraint |
|---|---|---|
| `slug` | VARCHAR(240) | PK |
| `product_line_id` | BIGINT | NOT NULL FK `product_lines.id` |
| `since` | TIMESTAMPTZ | NOT NULL DEFAULT now() |
| `decision_id` | BIGINT | NULL FK `decisions.id` |

A row is written whenever a product line's slug changes (a sheet approval fixing it from the chosen name, an alias). Chains are flattened at write: every old slug points at the live row.

### 1.6 Existing tables touched (additive, same revision)

| table | columns added | why |
|---|---|---|
| `brands`, `product_lines`, `products`, `merge_candidates` | `uid UUID NOT NULL UNIQUE` (server default) | rule 3 |
| `brands`, `product_lines` | `hidden BOOLEAN NOT NULL DEFAULT false`, `indexed BOOLEAN NOT NULL DEFAULT false`, partial indexes WHERE `hidden` and WHERE `indexed` | the materialised columns for W18's two decisions; `hidden_ids()` and the `review` override retire; `checked` is retired (an approved sheet is the quality fact) |
| `product_lines` | `decided_key` is NOT added; instead the writer stamps `key = 'decided:' || slug` on an approved line (section 2) | a namespace `lines.line_key` never produces, so `keying.line_for` never lands an arrival on an approved line (W10) |
| `merge_candidates` | `closed_reason VARCHAR(16) NULL` (`superseded`, `withdrawn`), `decision_id BIGINT NULL FK decisions.id`; partial index `ix_merge_candidates_separate (level, left_id, right_id)` WHERE `decision = 'separate'` | `decision` becomes a person's ruling only (`same`, `separate`); the algorithm's closures move to their own column; NO CHECK on `decision` in this revision (section 9) |
| `product_merges` | `decision_id BIGINT NULL FK decisions.id`, `batch_id BIGINT NULL FK decision_batches.id`, `reversed_by_id BIGINT NULL FK decisions.id`, `reversed_at TIMESTAMPTZ NULL`; partial indexes on `decision_id` and `batch_id` | the merge record beside the decision (W15); the tail fold of a batch stamps `batch_id` so batch undo reverses its own folds |
| `overrides` | DROP TABLE | 0 rows on every host; the revision asserts `count(*) = 0` and aborts otherwise |

### 1.7 The fields, per entity type

| entity_type | fields | materialised column(s) | rule_value |
|---|---|---|---|
| `product_variant` | `name`, `product_line`, `attribute:<kind>` (quantity is `attribute:quantity`, W7) | `products.name`; `products.line_id`; the registry's storage for the kind (typed columns for `quantity`, `abv`, `country_of_origin`, `is_exclusive`; else `attributes[kind]`) | `name`: keep-first (the previous effective row's `rule_value`, else the column); `product_line`: `keying.line_for(create=False)` on the batch's Maps; `attribute:<kind>`: the registry accessor's rule reading |
| `brand` | `name`, `alias_of`, `hidden`, `indexed` | `brands.name`; `brands.canonical_id` (renamed `alias_of_id` in the rename pass); `hidden`; `indexed` | `name`: keep-first; others NULL |
| `product_line` | `name`, `alias_of`, `hidden`, `indexed` | `product_lines.name`, `canonical_id`, `hidden`, `indexed` | as brand |
| `attribute_wording` | `meaning`, `display`, `kind` | `variation_aliases.canonical`, `display`, `kind` | the rule's canonical wording |
| `listing` | `pinned_to`, `ignored` | `listings.pinned_product_id` (+ `product_id = survivor_of`), `ignored_at/by/reason` | NULL |
| `suggestion` | `decision` | `merge_candidates.decision` (+ `decision_id`) | NULL |
| `product_variant` | `merged_into` | `products.merged_into_id` through `merges.merge_products`; `product_merges.decision_id` | NULL |

`attribute:<kind>` is accepted for any kind the registry knows or a shop published (`attribute:option:<name>`); the writer validates the value type against the registry. `FIELDS` in code is the registry of this table; a new attribute kind needs no migration and no code.

## 2. Natural keys and their stability

One module owns the grammar: `services/decisions/natural_keys.py` with `build(entity)` and `parse(key)` per entity type, split on `||` first then by the entity's grammar, refusing any component that contains its level's delimiters, property-tested round-trip on real slugs, SKUs and shop codes. `natural_key_detail` (JSON) is the authority for resolution; the string is a label.

| entity | key | detail | stability and resolution on the target |
|---|---|---|---|
| brand | `brand:<slug>` | `{name}` | STABLE: a slug is never rewritten; an alias row keeps its own slug (a forwarding row that stays, W4). Resolve by slug. |
| product line | `line:<uid>` | `{brand_slug, slug, key, rules_version, name}` | STABLE for any line born before the fork (uid copied). A line born after the fork on one host resolves by slug only when the found row's brand matches and its `key` agrees (or the row holds a `name` decision and the names agree); a slug hit whose key differs is parked `LINE_MISMATCH`, never adopted; `line_slugs` is consulted last. A sheet's new line is minted on the target from the `name` decision (section 6.3). |
| product variant | `variant:<uid>` | `{gtin (only when gtin_source != 'merge'), listings: ["listing:..."], name, brand_slug}` | uid first; else the variant's own barcode; else every listing key resolved to its live variant with `merged_into_id` followed to the END of the chain (a chain of two exists today): all agree -> that variant; disagree -> parked `VARIANT_SPLIT` with the candidate set; none -> parked `LISTING_MISSING`. A variant with no uid match, no barcode and no listing parks. The listing list includes ignored listings (an ignored listing still has a product_id and a stable key), so an ignore never leaves a variant keyless. |
| listing | `listing:<retailer_slug>/<location_code>/<source_sku>` | `{retailer, code, sku}` (the triple, authoritative) | STABLE while the shop keeps its SKU (`uq_listing_location_sku`, `uq_location_retailer_code`); `/` verified absent from every SKU; a re-platformed shop orphans its keys, reported by replay, never silent. |
| attribute wording | `wording:<vertical>|<raw>` | `{kind}` | STABLE (`uq_variation_alias_vertical_raw`; raw is the shop's own text lowercased). Minted on the target if absent. |
| suggestion (a pair) | `pair:<level>:<keyA>||<keyB>`, sides sorted as strings | `{level, left: <key>, right: <key>}` | As stable as its sides. The `merge_candidates` row is host-local: the writer mints one (reason `decided`, score NULL) when no rule offered the pair, inside a savepoint with re-read on IntegrityError (`suggest.generate` runs concurrently); an existing `(level, left_id, right_id)` row is reused whatever its `closed_reason`. |
| person | `decided_by_username` | | STABLE; `cli_refresh` already maps accounts by username. |
| proposal | `(pass name, entity_type, natural_key, field)` | | STABLE where the pass file was loaded; else `proposal_id` NULL on the target and `pass_id` still names the pass (resolved by name). |
| decision, batch, pass | `uid` / `uid` / `name` | | STABLE by construction. |

Line identity, the three rules that keep it stable:

- **A derived line is a cache.** Its `key` is a rule function and its slug can carry a `-N` suffix by mint order. A decision names it by uid; `backfill_lines` and `rederive` never re-point a variant that holds a `product_line` decision (as today for `line_id`) and never re-key a line that holds an effective `name` or `alias_of` decision.
- **An approved line is a record.** The first `name` decision on a line (from a sheet, or a preferred name at confirm) fixes `slug = slug_of(brand, decided name)` and rewrites `key = 'decided:' || slug`; the old slug goes to `line_slugs`. Arrivals can never compute a `decided:` key, so they land on rule lines and become arrival proposals (W10).
- **A sheet line adopts before it mints.** If a line of the brand already holds the wanted slug or the rule key of the chosen name, the approval adopts that row (records `detail.adopted_from_key`), never a `-2` sibling; if the slug is held by a line of another brand, the row is refused `SLUG_TAKEN` naming the holder. Every absorbed derived line gets an `alias_of` consequence row pointing at the approved line, so its address 301s and `prune-lines` skips it.

## 3. The read path

Three readers; each one lookup; nothing on a GET writes.

**3.1 The key path.** `keying.load_maps` runs ONE query for `entity_type = 'product_variant'`:

```
SELECT ... FROM decisions WHERE entity_type = 'product_variant'
ORDER BY entity_id, field, decided_at ASC, id ASC
```

and fills `Maps.decided[product_id][field] = row` letting a later row overwrite an earlier one (newest wins by construction, dialect-neutral; `DISTINCT ON` compiles to plain `DISTINCT` on SQLite and inverts the order). A row whose `effect = 'release'` deletes the field from the dict. `Maps.decided` keeps today's shape: `keyed_name` reads `row.rule_value`; `product_quantity` reads `decided["attribute:quantity"].value`; `product_key` reads `decided["product_line"].value_ref_id` (an int, the resolved line id) and the attribute accessor for `attribute:<kind>`. The shim exposes the legacy field names (`quantity`, `line_id`, `variation`) as aliases on the dict for one stream; ingest's enrich guard (`ingest.py` line 447) switches to `attribute:quantity` in the same commit with a kit test.

The same load fills `Maps.separated: set[frozenset[int]]` from `merge_candidates WHERE level = 'product' AND decision = 'separate'` (the partial index), each side chased through `merged_into_id` to the live survivor using a `{id: merged_into_id}` map loaded in the same call. `merges._conflicts(members, maps)` (signature gains `maps`; `duplicate_groups` and `merge_duplicates` pass it) appends `kept_separate` when any two members form a separated pair, and `decided_member` when any member holds an effective `set` decision on `name`, `product_line`, or any `attribute:*`. Both make the group a suggestion for a person, never a fold: a machine never undoes a person (W9 part 1) and never silently loses a decided loser's rows. `merges._fold` loads the separated set fresh on every call (one partial-index scan) rather than from the cached Maps, so the 300 s cache window cannot fold a pair kept separate seconds ago.

**3.2 Pages, JSON-LD, the sitemap, the desk, ingest** read the entity's own column only (`products.name/line_id/attributes/quantity_*`, `brands.name/canonical_id/hidden/indexed`, `product_lines` likewise, `listings.pinned_product_id/ignored_at`, `merge_candidates.decision`, `variation_aliases.canonical/display`). The ledger is never on a page's request path. The materialised value of `product_line` and `alias_of` is the decided target with aliases followed at read; the drift check and the audit compare alias-followed values, so an alias applied after a membership decision is not drift.

**3.3 `decisions.effective(db, entity_type, entity_ids=None, fields=None)`** is the one function every other reader calls (the sheet's "already decided" cell, the writer's conflict check, `prune-lines`, the audit): the same ordered query narrowed by ids, the `release` rule applied, so no caller can forget it. The tracked-changes views (`/review` history, `app.cli decisions list`) read by `(entity_type, entity_id)`, by `batch_id` or by `pass_id`, newest first, each row printing what, who, when, why, which pass, which mode, and what it reverses or restores.

## 4. The write path

One writer, and nothing else inserts a row: `services/decisions/writer.py`.

```
with decisions.batch(db, kind="sheet", mode="bulk", by=account, scope={...}, pass_id=...) as b:
    row = decisions.record(b, entity_type=..., entity=<row or natural key>, field=..., value=...,
                           origin="proposal", proposal=..., reason=..., decided_at=None)
```

`batch()` opens the transaction, inserts the `decision_batches` row (id known), resolves and locks (step b) every entity the caller pre-declares, and owns one `Maps` loaded at open (`maps.remember_line(row)` for rows it creates, so `rule_value` inside the batch never reads a stale cache). At exit, once: `keying.invalidate()`; `merges.rekey_products(touched)`; `merges._fold(touched)` with every merge it makes stamped `product_merges.batch_id`; the approved proposals flipped; the batch closed (`closed_at`, `summary`); ONE commit; `keying.invalidate()`; `suggest.generate` AFTER the commit when a brand or line was touched (derived data, outside the transaction); one `audit_log` row per batch plus the existing per-action rows. A `Refused` inside a sheet batch skips that proposal and reports it; any other exception rolls the whole batch back.

`record()` does, in order, inside the batch's transaction:

- (a) **Validate**: `field` in `FIELDS[entity_type]` (an `attribute:<kind>` against the registry, value type checked); a reference value resolved to a live local row (a `product_line` must be a line of the variant's brand with aliases followed, as `decide_product` checks today); the entity itself live (a merged-away variant is re-targeted to its survivor when the caller passed a natural key, refused `PRODUCT_MERGED` when it passed an id; an aliased brand or line refused `ENTITY_ALIASED`); `decided_at` never in the future.
- (b) **Lock**: `pg_advisory_xact_lock(hashtext(entity_type || ':' || entity_id))`, taken for every entity of the batch in sorted order at open (a pair or a merge locks both sides). No `FOR UPDATE` on product or listing rows, so a collector never waits on the ledger beyond a single UPDATE statement. `SET LOCAL lock_timeout = '5s'`; the route retries once. SQLite: a no-op.
- (c) **Read the effective row** for `(entity_type, entity_id, field)`: `supersedes_id`, `prior_value`. **Human-first**: if `mode = 'bulk'` and the effective row is a `set` with a different value and (`origin = 'person'` or `mode = 'individual'`), refuse `DECISION_HELD` naming it. An individual act may supersede anything.
- (d) **`rule_value`** per section 1.7, through the rule function on the batch's Maps, never from the column (which may already hold an earlier decision), except `name`, which is keep-first by construction.
- (e) **INSERT** the row: uid (uuid4, or uuid5 for a consequence), `decided_at`, `origin_host`, provenance, `batch_id`, `mode`, `rules_version`, `value_ref_id`.
- (f) **Re-read the effective row.** Materialise only if the inserted row is now effective (it always is for a native write; a replayed older row is not). Materialise through `APPLIERS[(entity_type, field)]`, each split in two: `materialise(row)` writes this row's own column(s) and nothing else; `consequences(row)` mints the cascade through `record(cause=row)` (same batch, mode, origin, pass). The appliers are today's code moved behind one registry: `merges.apply_brand_alias` / `apply_line_alias` (each cascaded line `alias_of` and the preferred `name` become consequence rows; the products the alias moves are recorded in `detail.products_moved` as `(id, old line_id)` so undo can put them back; where a moved variant holds a `product_line` decision naming the alias line, a carried `product_line` consequence names the target); `apply_variation_alias`; the pin and ignore column writes; `merge_candidates.decision` + `decision_id`; `merges.merge_products` for `merged_into` (section 5).
- (g) **Queue** the touched variant ids on the batch.

The three existing writers move behind this: `overrides.decide_product` -> `record` in a batch of one (`kind = route`); `merge_session.confirm` / `reject` -> section 5; `overrides.move` (called by `merge_products` and `backfill_lines`) -> the carry in section 5 (rows are appended on the survivor, never re-keyed); `review(state)` -> `hidden` and `indexed` boolean decisions, `checked` refused `VALUE_INVALID`. Machine enrichers (`ingest._resolve_product`, `merge_products._ENRICH_FIELDS`) consult `GUARDED_COLUMNS` derived from the registry's storage field, so an `attribute:<kind>` decision guards its column and a decided empty (`attributes.abv = null`, an explicit null) is never refilled.

`app.cli decisions verify` (read-only) compares every effective row to its column (alias-followed) and reports drift; it joins `check.sh`'s after-deploy chain. Nothing prevents a future bypass except review; the trigger protects the ledger, not the columns.

## 5. Pair decisions and merges as rows

**A pair decision** is `entity_type = 'suggestion'`, `entity_id = merge_candidates.id`, `field = 'decision'`, `value = {"decision": "same" | "separate", "survivor": <variant key> | null, "name": <typed> | null, "note": <text> | null}`. The same object is the proposals file's shape, so the sheet's compare is between equal shapes and the survivor travels with the ruling. `materialise` writes `merge_candidates.decision = value.decision` and `decision_id`. The desk, the queue, `progress()` and `_decided_pairs` read `decision` with the words changed (`merged` -> `same`, `kept_apart` -> `separate`; the route `reject` -> `keep-separate`) and filter open pairs as `decision IS NULL AND closed_reason IS NULL`. `suggest.generate` writes `closed_reason` only, inside a savepoint per new row with re-read on IntegrityError (a pre-minted `decided` pair must not choke it).

**Keep separate** writes the one suggestion row. The veto reads it through `Maps.separated` (survivor-chased). It binds machines only: a person's later Confirm same on the same pair supersedes it by being newer, and the applier merges. A rejected pair proposal on the sheet IS a Keep separate: `origin = person`, `proposal_id` and `pass_id` kept (the CHECK allows it), `proposals.status = rejected` with `decision_id` set.

**Confirm same at variant level** writes, in one batch: the suggestion row (`same`, the survivor named), then a `product_variant` row `field = merged_into` on the loser (`natural_key` = the loser's key, `value` = the survivor's key, `value_ref_id` = survivor id, `caused_by_id` = the pair row) whose `materialise` is `merges.merge_products(survivor, [loser], reason="confirmed", merged_by, note)` with `product_merges.decision_id` = this row and `detail.merge_id` pointing back; a typed name becomes a `name` consequence on the survivor (`rule_value` = the survivor's own collected spelling, keep-first, so the key never follows a typed name). **Carry**: for every effective `set` decision the loser holds on a field the survivor has no effective `set` row for, a consequence row is appended on the survivor (`caused_by` = the merge row, `detail.carried_from` = the loser's key, `rule_value` recomputed on the survivor); fields the survivor already decided are listed in `product_merges.detail.overrides.kept`, as today; the loser's rows stay as its history. For every `separate` pair the loser was in, a carried `suggestion` row `separate` is minted for `(survivor, counterpart)`. A `merged_into` refuses `NOTE_REQUIRED` for two different barcodes without a note, `PAIR_SIDE_MERGED` when a side is already merged elsewhere (a rules fold is unmerged by a person first through `app.cli merges unmerge <id>`, itself recorded as a person's batch), and is never refused by the attribute veto (plan §8: the veto binds machines only).

**Confirm same at brand or line level** writes the suggestion row plus an `alias_of` consequence on the alias row (value the target's key) and, when typed, a `name` consequence on the target; `apply_brand_alias`'s cascade of line aliases are consequence rows of the brand alias row. One batch, all provenance shared.

**A sheet's "these three variants are one"** is two `merged_into` rows on the two losers with no suggestion row (no pair was queued; a suggestion row is minted only where a pair needs to be vetoed), survivor = the barcode holder else the most listed (`pick_survivor`).

**A rules fold** (`merge_duplicates`, reason `duplicate_match_key`, `merged_by` NULL) writes NO decision row: it is a machine, its record stays `product_merges` with `decision_id` NULL, and `ingest._follow_tombstone` keeps distinguishing human merges by reason. With `decided_member` in `_conflicts` a fold can never take a decided variant as its loser, so no carry is needed on the machine path. A fold that runs at a batch's tail is stamped `product_merges.batch_id` so batch undo reverses it.

## 6. Proposals: the file, the load, the sheet, the approval

### 6.1 The file (W13)

`app.cli proposals load --file <json> [--check] [--as <username>] [--partial]` reads one brand per file:

```json
{"pass": {"name": "claude/2026-09-18/chanel-1", "kind": "session", "process_version": "1",
          "rules_version": "6", "generator": "claude-session", "note": "..."},
 "brand": "chanel",
 "proposals": [
  {"sheet_line_ref": "line:new:chanel-rouge-allure", "position": 0,
   "entity": {"type": "product_line", "key": "line:new:chanel-rouge-allure",
              "detail": {"brand_slug": "chanel", "slug": "chanel-rouge-allure", "name": "Rouge Allure",
                         "absorbs": ["line:<uid>", "line:<uid>"]}},
   "field": "name", "value": "Rouge Allure", "confidence": 0.97,
   "reason": "17 derived lines share the head words; the shop's own option field carries the shade",
   "evidence": [{"listing": "listing:attenza/PTY1/12345", "source": "listed_name", "span": [7, 19], "text": "Rouge Allure"}]},
  {"sheet_line_ref": "line:new:chanel-rouge-allure", "position": 1,
   "entity": {"type": "product_variant", "key": "variant:<uid>",
              "detail": {"gtin": "3145891612301", "listings": ["listing:attenza/PTY1/12345", "listing:extime/CDG1/778"]}},
   "field": "product_line", "value": "line:new:chanel-rouge-allure", "confidence": 0.95, "reason": "...", "evidence": [...]},
  {"sheet_line_ref": "line:new:chanel-rouge-allure", "position": 2,
   "entity": {"type": "product_variant", "key": "variant:<uid>"},
   "field": "attribute:shade", "value": "99 Pirate", "confidence": 0.99,
   "evidence": [{"listing": "listing:attenza/PTY1/12345", "source": "option:shade", "span": [0, 9], "text": "99 Pirate"}]},
  {"sheet_line_ref": "line:new:chanel-rouge-allure", "position": 3,
   "entity": {"type": "suggestion", "key": "pair:product:variant:<uidA>||variant:<uidB>",
              "detail": {"level": "product", "left": "variant:<uidA>", "right": "variant:<uidB>"}},
   "field": "decision", "value": {"decision": "same", "survivor": "variant:<uidA>", "name": null, "note": null},
   "confidence": 0.9, "reason": "same shade, same 3.5 g, no barcode on either", "evidence": [...]},
  {"entity": {"type": "brand", "key": "brand:joh-walker"}, "field": "alias_of", "value": "brand:johnnie-walker",
   "confidence": 0.98, "reason": "...", "evidence": [...]}
 ]}
```

`line:new:<slug>` is the one key form that names a line that does not exist yet; the loader marks it `creatable` and the approval adopts or mints (section 2). A rule generator (the retired word lists, W14 option D) writes the same file under a pass of `kind = rule` with `generator = "rule:<list>"` and `reason` = what the list would have removed; the sheet joins its rows to the AI's on `(entity, field)` and shows both. Ingest's arrival proposals (W10: a new variant that matches nothing is queued as a proposed addition to the nearest approved line) land under the standing pass `arrival:<rules_version>` with `generator = arrival`, get-or-create in a savepoint; that writer belongs to the certain-key stream, but the table admits it now.

### 6.2 The load

`--check` runs in one transaction rolled back at the end with the resolvers in `mint=False` mode (reporting `WOULD_MINT`), so a check leaves every table's count unchanged (a test asserts it). On load: pass upserted by name (`file_sha256` recorded; an identical file is a no-op); every proposal upserted on `(pass_id, entity_type, natural_key, field)`, an open row taking the new value, evidence, confidence, reason and position, a resolved row left untouched and counted as kept; open rows missing from the file marked `stale` (the file is the pass's whole statement) unless `--partial`; every natural key resolved on this host (`entity_id`, `resolution`; an unresolvable one loads `parked` with the reason in `resolution_note`, visible, never approvable); `rule_value` and `current_value` captured; `against_decision_id` set from the effective row (history); `spot_check` by the written process rule (every `same` or `merged_into`, every `product_line` change that alters a cross-shop comparison, the five lowest-confidence rows per brand, a 5 percent sample seeded by the pass name); `detail.previously_rejected` when an identical rejected row exists; a proposal whose field already holds an effective decision from the same pass is marked `approved` on load (it reached this host by replay), so the sheet never offers it twice.

`proposals withdraw --pass <name> --reason <text>` flips open rows to `withdrawn`, stamps the pass, and PRINTS the batch uids of that pass's approved decisions (`ix_decisions_pass` grouped by batch) with the `undo-batch` command for each; it never touches the ledger.

### 6.3 The sheet read

`GET /api/review/sheets/<brand>` resolves the brand through its alias chain and reads proposals for `brand_slug IN (the brand, its aliases)`, grouped by `sheet_line_ref` in `position` order: brand-level rows first; each line header (name, confidence, evidence, the lines it absorbs); each member with its listings (from `natural_key_detail`) and one cell per field: proposed value, `corrected_value`, rule value, current column value (re-read now, `drift` flagged when it differs from the load snapshot), confidence, evidence spans, spot-check mark, and the effective decision on that field computed NOW (who, when, mode, origin, pass, process version) with the flag "a newer pass disagrees with a bulk decision" (`effective.mode = bulk AND effective.value <> proposal.value`); the arrival rows and previously-rejected rows collapsed; the sheet's history: batches with `scope.brand_slug = brand` (by, at, mode, undone or not). Nothing on this GET writes.

**Staleness is a change in what was read, not in what a rule derived.** A row is stale when a cited listing no longer exists, is ignored, or the listed text at the cited span no longer equals `evidence.text`. `rule_value` is shown, never compared, so a rederive between load and approval does not stale a whole sheet.

### 6.4 The approval

`POST /api/review/sheets/<brand>/approve {pass, scope: "all" | {sheet_line_ref} | {proposal_uids: [...]}, corrections: {uid: value}, reject: [uids], note}` and `app.cli proposals approve --brand <slug> --pass <name> [--line <ref>] [--uids ...] [--all]`:

1. Refuse `COLLECTION_RUNNING` while a collection runs (`cli._collection_running`, `--force` to override), exactly as `rederive` does; the desk batch refuses likewise. Advisory locks per entity in sorted order at open (section 4).
2. One batch (`kind = sheet`, `mode = bulk` for all or a line, `individual` for uids; `scope` recorded; `pass_id`), one brand per batch, one transaction.
3. Selection: `all` takes every open proposal of the brand and pass that is NOT `spot_check`, not `parked`, not stale, and whose field's effective row would not hold it (the writer's rule 5, recomputed here); a line ref narrows to that `sheet_line_ref`; explicit uids take exactly those, spot-checks and held rows included (an individual approval is a person's look). "Approve all except spot-checks" is `all`; "approve all" in the UI is `all` plus the spot-check uids listed explicitly, so the person has seen them by construction (they lead the sheet).
4. Order within the batch, so each step's entities exist for the next: brand-level rows; line headers (adopt or mint per section 2; `SLUG_TAKEN` refuses the row and every row under it); `product_variant` / `product_line` memberships (each re-resolved from its natural key with the replay resolver, so a variant merged since load re-targets to its survivor; a split parks the row `VARIANT_SPLIT`); `attribute:*` and `name` cells; `alias_of` on the absorbed lines (derived by the loader unless the file gave them: one per existing line whose every live member is proposed into one target); `merged_into` and `suggestion` rows last (they change ids the earlier rows resolved); listing pins and ignores.
5. Per row: skip unless `status = open`; stale check (6.3); `record(origin = proposal, proposal, pass, reason = the proposal's reason, decided_by = the approver)`, or with a correction `record(origin = person, mode = individual, proposal_id kept, detail.corrected = true, detail.proposed_value)`; the proposal flips to `approved` with `decision_id`, `resolved_at/by`. A `Refused` (`DECISION_HELD`, `SLUG_TAKEN`, `ENTITY_ALIASED`, `NOTE_REQUIRED`, `VARIANT_SPLIT`, a side gone) leaves the row open, names it in the response with its code, and the rest proceed. `reject` flips to `rejected` with the note and writes no decision, except a pair proposal (a Keep separate, section 5).
6. Tail once (section 4). Response: the batch uid, counts (`approved`, `held`, `refused`, `stale`, `parked`), the refusals, the undo command. The sheet shows "N approved, M held" loudly; a person who does not read the response must still see it.

When `REVIEW-PROCESS.md` changes version, a new pass may propose against decisions of an older pass; the sheet shows the disagreement and nothing applies until a person acts. One strength, with `mode` recorded, as W15 settled.

## 7. Undo and batch undo

**`decisions.undo(b, decision_id, reason)`** writes a reversing row, never deletes. Let X be the target. Refuse `DECISION_SUPERSEDED` (naming the newer row and its batch) unless X is the effective row on its `(entity_type, entity_id, field)`; refuse `ENTITY_MERGED` naming the carried row when X's entity has since merged away (the carried row on the survivor is what to undo). Then, in one transaction: undo X's consequences first (`caused_by_id = X`, newest first, recursively; a consequence that is no longer effective is skipped and reported, not refused); insert R with `reverses_id = X`, `supersedes_id = X`, `restores_id = X.supersedes_id`, `effect = set` and `value = restores.value` when a predecessor exists, else `effect = release` and `value = NULL`, `origin = person`, `mode` = the caller's, `reason = "undo of <uid>" + the person's sentence; materialise R through the same appliers: a restored value goes back on the column; a release writes the rules' value computed NOW (a rekey for a variant field; NULL for `alias_of` and `pinned_to`; false for `hidden`, `indexed`, `ignored`; the rule's canonical for a wording; NULL `decision` and `decision_id` on the pair row so it resurfaces).

So undo restores what was in force before the reversed act, which is the rules' value only when nothing was; undo of an undo is the same operation on the undo row (`restores_id` = the original) and reinstates the sheet's values with their provenance reachable through `restores_id`. The chain L1 -> L2 -> L3 -> undo(L3) ends on L2, pinned by a test beside `test_a_second_decision_keeps_what_the_rules_had_first`.

Compound reversals: a `merged_into` undo is an unmerge from the `product_merges` record (the listed listing ids back to the loser, `awards_moved` back, `awards_dropped` recreated from the recorded fields, a gained barcode returned with `gtin_source` cleared, `merged_into_id` cleared, `reversed_by_id`/`reversed_at` set; a second `product_merges` row is never needed because the record itself carries the reversal pointer); it refuses `UNMERGE_CHAIN` naming the later merge when the loser or survivor has since merged in a merge that is not in the same batch. An `alias_of` undo clears the pointer, moves products back by `detail.products_moved`, and rekeys. Rule folds that happened after the decision and outside its batch are not reversed; the report lists them and `app.cli merges unmerge <id>` handles each, deliberately.

**`decisions.undo_batch(batch_uid)`** (CLI `decisions undo-batch <uid>` and `POST /review/batches/<uid>/undo`): refuse `COLLECTION_RUNNING`; one transaction; one new batch of `kind = undo` with `reverses_batch_id`; first reverse the batch's own tail folds (`product_merges.batch_id = N`, newest first, from their records); then walk N's `set` rows in DESCENDING id order (a merge is unmerged before the membership that preceded it is reversed) applying `undo` to each row still effective; a row superseded by a later batch is skipped and named in the result with the newer batch uid, because reversing under it would silently change what a later person decided; one rekey and fold at the tail; one commit; any exception rolls the whole undo back and records nothing. A half-undone sheet is worse than an un-undone one. The batch's `scope` makes "undo what I approved on the CHANEL sheet from pass X" one query.

A pass withdrawal is not a batch undo (section 6.2).

## 8. Replay by natural key

**Direction.** At launch production becomes a copy of staging (W17): every decision, batch, pass, proposal and every uid travels in the dump and nothing is replayed. After the fork both hosts write (routes on production, the review on staging), ids diverge, and every transfer is by uid and natural key: staging -> production after a review; production -> staging on every refresh. Both are the same two commands.

**`app.cli decisions export [--since <ts> | --batch <uid> | --pass <name> | --all] [--out <file>]`** writes JSON lines in `(decided_at, id)` order: the passes referenced (by name), the batches (by uid, with scope and kind), and every decision with `caused_by_id IS NULL` (consequences are derived, never carried: the cause's `value` carries every input its applier needs, and a consequence regenerated on the target gets the same uuid5 uid), each carrying uid, `decided_at`, `origin_host`, entity_type, natural_key, natural_key_detail, field, effect, value (natural-key strings, never ids), rule_value, prior_value, origin, mode, reason, rules_version, `decided_by_username`, `batch_uid`, `pass_name`, and for an undo row `reverses_uid` plus the reversed row's `(entity_type, natural_key, field, value)`. No local id appears anywhere in the payload (a test greps the file for an integer id field and fails on one). `--batch` also exports every row that reverses or supersedes a row of the batch, or refuses `BATCH_UNDONE` naming the undo batch. The refresh export selects `origin_host = staging`.

**`app.cli decisions replay --file <jsonl> [--check] [--label <source host>]`** on the target, refusing `COLLECTION_RUNNING`: per source batch, one `decision_batches` row with the SAME uid (`kind = replay`, `replayed_from`, `scope.source_batch_uid`; present already -> reused, so a retry of parked rows opens no duplicate); per row in file order, each in its own savepoint: (1) uid present -> skipped, counted `already`; (2) the entity resolved per section 2 (a `name` decision on `line:new:<slug>` mints or adopts per section 2; a pair's row minted); (3) every natural-key value resolved, `value_ref_id` written; (4) `decided_by` by username (NULL if unknown), `reverses_uid` by uid, else by the effective row on the resolved entity and field whose value equals the recorded one, else parked `REVERSAL_TARGET_MISSING`; (5) the writer's own checks: rule 5 human-first (`DECISION_HELD` parks it), and `decided_at` older than the target's effective row on that field where that row's uid is not in the file -> inserted for the record (the ledger must say the review reached this host) with `detail.conflict = {target_uid, kept: "target"}` but NOT materialised, reported `SUPERSEDED_ON_TARGET`; (6) INSERT with the source uid and `decided_at`, `recorded_at` now, `rule_value` and `prior_value` recomputed HERE (the source's ride in `detail.replayed_from_values`); (7) `materialise` and `consequences` through the same appliers (a replayed `same` merges, a replayed `separate` vetoes; a `merged_into` whose sides resolve to one live row is inserted and applied as already satisfied; a loser that is live while the survivor is its own tombstone is refused `UNMERGE_CHAIN`); (8) a row whose proposal resolves by `(pass name, entity_type, natural_key, field)` flips that proposal to `approved` with `decision_id`. A parked row leaves nothing behind (its savepoint rolls back) and is written to `replay-remainder.jsonl` with its code; the next replay of the same file retries parked rows first, in original order. At the end: one rekey of every variant touched, one fold, one commit. `--check` runs the whole thing in one transaction rolled back, resolvers in `mint=False` mode, and prints resolved, unresolved, already, held and superseded counts per entity type; a replay with parked rows exits non-zero.

**Proposals** replay by re-running `proposals load --file` with the pass file on the target (the file is the artefact; the unique key makes it idempotent); the loader resolves `entity_id` there and parks what it cannot.

**`staging-refresh.py`**: the guard query counts `decisions`, `proposals`, the alias, pin and ignore columns, runs with `-v ON_ERROR_STOP=1` and fails hard on a non-zero psql exit (today a dropped `overrides` would make the refusal silently stop firing); the preserved set gains the ledger through this export and replay pair, not through `TableSpec` row copies (a decision must be re-applied, not re-inserted, and the trigger would refuse the natural-key UPDATE path): `decisions export --all` (origin_host = staging) before the drop, `decisions replay` after `alembic upgrade head`, `proposals load` of the staging pass files after that; `--discard-decisions` keeps its meaning.

**Acceptance (the S3 rehearsal, then a permanent kit test):** copy A decides about fifty things across every entity type including a sheet approval, a variant merge, a brand alias and one undo; export; replay onto copy B whose ids were shifted and which holds one extra derived line under the alias brand and one line with a `-2` slug; every row lands (0 parked), every materialised column on B equals A's (alias-followed), `count(*)` on B equals A's and every uid on B exists on A; a second replay applies 0; `undo-batch` on B of a batch made on A reverses it.

## 9. The migration from `overrides` (0 rows)

One Alembic revision, `down_revision = "b3c4d5e6f7a8"`, schema-only, before the rename stream (it writes the W2 words in its own tables and touches existing tables only by adding columns):

1. Assert `SELECT count(*) FROM overrides = 0` and abort otherwise; `DROP TABLE overrides`. Downgrade recreates it exactly as migration #4 defined it, with `uq_overrides_entity_field`.
2. Add `uid` to `brands`, `product_lines`, `products`, `merge_candidates` (NOT NULL, UNIQUE, server default `gen_random_uuid()`; SQLite gets the Python default).
3. Create in FK order: `proposal_passes`, `decision_batches`, `proposals` (with `decision_id` and `against_decision_id` as plain BIGINT), `decisions` (with `proposal_id` FK `use_alter=True`, named `fk_decisions_proposal`), then `op.create_foreign_key` for `proposals.decision_id` and `proposals.against_decision_id` -> `decisions.id`; `line_slugs`. Downgrade drops the two named FKs first to break the cycle, then the tables in reverse.
4. The two trigger functions and triggers, and nothing else, by `op.execute` guarded by `bind.dialect.name == "postgresql"`; downgrade drops them first.
5. Add `brands.hidden/indexed`, `product_lines.hidden/indexed` (BOOLEAN NOT NULL `server_default 'false'`) with the partial indexes; `merge_candidates.closed_reason`, `merge_candidates.decision_id`, the `separate` partial index; `product_merges.decision_id`, `batch_id`, `reversed_by_id`, `reversed_at` with their partial indexes. Nothing on `products` beyond `uid`, nothing on `listings`.
6. NO CHECK on `merge_candidates.decision` in this revision. `app.cli backfill suggestion-decisions` (idempotent) moves `superseded` and `withdrawn` to `closed_reason` (decision NULL) and renames `merged` -> `same`, `kept_apart` -> `separate`; a later, unconditional revision adds `CHECK (decision IS NULL OR decision IN ('same', 'separate'))` once the backfill reports 0 legacy values on every host. No other data step exists (0 aliases, 0 human merges, 0 pins, 0 ignores, 0 person-decided pairs); `backfill decisions-from-columns` ships anyway, idempotent, for a database that held hand decisions before the revision (it writes 0 rows on staging and a test asserts that).

Code in the same stream:

- `models/decisions.py`: `ProposalPass`, `DecisionBatch`, `Proposal`, `Decision`, `LineSlug`; `LEDGER_TABLES` exported for the suites' TABLES lists; `Override = Decision` as a module alias with `collected_value` (-> `rule_value`), `set_by` (-> `decided_by`), `set_at` (-> `decided_at`) as read-only properties for one release.
- `services/decisions/` (`writer.py`, `natural_keys.py`, `appliers.py`, `undo.py`, `replay.py`, `effective.py`); `services/proposals.py` (load, withdraw, sheet, approve); `services/overrides.py` reduced to a shim with the same public names (`read`, `fields_of`, `guarded_columns`, `table_present`, `decide`, `ledger`, `clear`, `move`, `review`, `hidden_ids`, the pin and ignore routes) delegating to the writer with the field renames in one table (`line_id` -> `product_line`, `variation` -> `attribute:variation`, `quantity` -> `attribute:quantity`, `canonical_id` -> `alias_of`, `review` -> `hidden`/`indexed`, `pinned_product_id` -> `pinned_to`, `canonical`/`display` -> `meaning`/`display`); `clear` -> undo of the effective row; `move` -> the carry.
- `keying.load_maps` (section 3.1, `Maps.separated`, the `{id: merged_into_id}` map); `merges._conflicts(members, maps)`, `_fold` loading the separated set fresh and stamping `batch_id`; `merge_session.confirm`/`reject`, `merge_desk.batch` and `propose` as batches; `cli.backfill_lines` carrying by consequence rows instead of `overrides.move`; `cli.backfill_prune_lines` keeping any line named by ANY decision row (value, `value_ref_id`, `rule_value` or entity), not only effective ones, and any line with a `line_slugs` row; `ingest`'s enrich guard on `attribute:quantity` and the registry's `GUARDED_COLUMNS`; every other `Override.` reader (`listings_table.py`, `cli.py`, `award_picker`) grepped and routed through the shim, and any field they use that `FIELDS` lacks registered before S1 closes.
- Rehearsal on `dfp-devdb`: `alembic upgrade head`, `downgrade -1`, `upgrade head`; every existing test green through the shim (`test_decided.py` changes only its TABLES list, its `collected_value` assertions reading `rule_value`, and the two keep-first assertions, which still hold because `name` is keep-first by construction); then the section 8 acceptance.

## 10. The tests the stream must write

Pure logic and the SQLite kit, seconds, no network. Each docstring records the failure it pins.

1. **Append-only**: on Postgres (the devdb rehearsal) an ORM UPDATE or DELETE of a decision is refused by the trigger; on the kit the module exposes no update or delete path and `clear`, `move`, `review(None)` all append.
2. **Newest wins by (decided_at, id) and the kit does not invert it**: two rows on one field resolve to the newer whichever order they load; a row with an older `decided_at` and a higher id is not effective; `effect = release` empties the field.
3. **Keep-first `name`**: `rule_value` on a second name decision equals the first row's `rule_value`; `keyed_name` keeps the collected spelling (the "1 Million" incident).
4. **Undo restores the predecessor**: L1 -> L2 -> L3 -> undo(L3) leaves L2 effective and materialised; undo(L2) then releases to the rules; undo of an undo reinstates.
5. **Batch undo in one transaction**: a batch with one row superseded by a later batch skips that row and names it; an exception mid-undo leaves nothing recorded; the batch's own tail fold is reversed first; undo of an undo batch reinstates every approved value and the proposals' `decision_id` points at the reinstating rows.
6. **Consequences**: a brand alias's line aliases and preferred name are consequence rows with uuid5 uids in the cause's batch; undoing the alias undoes them and moves products back by `detail.products_moved`; a consequence individually superseded is skipped and reported, not refused.
7. **Human-first in the writer**: a `bulk` write over an effective `person` or `individual` row with a different value is refused `DECISION_HELD`; an individual write supersedes; the check is recomputed inside the batch after a concurrent individual decision, not from `against_decision_id`.
8. **Keep separate is honoured by the fold**: a separated pair shares a key and is not folded; after one side merges into C, {C, other} is still refused (survivor chase); the carried `separate` row exists on the survivor.
9. **A decided variant never auto-folds**: two variants at one key, one with a name decision; `merge_duplicates` records a candidate and merges nothing.
10. **A human merge carries**: the loser's decisions the survivor lacks are consequence rows on the survivor with `rule_value` recomputed there; a field the survivor decided is kept and listed; the loser's rows are unchanged.
11. **`suggest.generate` and a pre-minted `decided` pair**: generate writes `closed_reason` only, tolerates an existing pair row through its savepoint, and a withdrawn pair can later be decided on the same row.
12. **Natural keys round-trip** on real slugs, SKUs with `:` and shop codes; a component containing its level's delimiter is refused; `attribute:option:talla` parses with `partition`.
13. **Resolution**: a variant resolves by uid, then own barcode (not one gained by merge), then agreeing listing keys through a two-hop chain; a split parks `VARIANT_SPLIT`; a line by uid, then slug only when brand and key agree, else `LINE_MISMATCH`; `line_slugs` last.
14. **The sheet line adopts before it mints**: an approved "Rouge Allure" adopts the derived row holding the slug, rewrites `key` to `decided:`, aliases the absorbed lines; `keying.line_for(create=True)` for a new arrival computing the old rule key creates a fresh rule line and never joins the approved one; `SLUG_TAKEN` on a slug held by another brand.
15. **Proposals load is idempotent**: an identical file is a no-op by sha; a corrected file updates open rows only; missing rows go stale unless `--partial`; `--check` leaves every table's count unchanged; `spot_check` is deterministic per pass name and never cleared; a replayed decision marks its proposal approved on load.
16. **Staleness by evidence**: a rederive between load and approval stales nothing; a changed listed text at the cited span stales the row.
17. **Approval order and re-resolution**: a variant merged between load and approval receives its decision on the survivor; a `merged_into` row runs after the memberships it depends on; a rejected pair proposal writes a Keep separate with `pass_id` kept.
18. **`prune-lines`** keeps a line named by any decision row's `value_ref_id`, `rule_value` or entity, and a line with a `line_slugs` row.
19. **Enrich never writes over a guarded column**, now derived from the registry: a decided empty `attribute:abv` (explicit null) survives a sighting that states 43.
20. **Replay**: onto shifted ids with the extra derived line and the `-2` slug (section 8 acceptance); an older replayed row over a newer target row is inserted, not materialised, and reported; a `bulk` replayed row over a person's row parks `DECISION_HELD`; a retry of a remainder file opens no second batch; the export contains no integer id; `--batch` of an undone batch refuses.
21. **`staging-refresh.py`'s guard** fails hard when the count query errors and counts `decisions` and `proposals`.
22. **Gates**: `proposals approve`, `undo-batch` and `replay` refuse while a collection runs.

## 11. Known limits

- A variant born after the fork with no barcode and no listing has a uid the other host never saw and cannot resolve; it parks, and `prune` is what should drop it. Every variant born before the fork resolves by uid regardless.
- A shop that renumbers its SKUs re-mints listing rows and orphans the listing keys naming the old ones (pins carry this exposure today); detectable, not self-healing; a `listing_renames` table is the fix if it happens.
- Replay across runs: a parked row applied on a later run lands after decisions that originally followed it; within a run original order holds. The `(decided_at, id)` ordering makes the effective value correct anyway; only the audit's `recorded_at` order differs.
- A merge undo restores the recorded snapshot: listings ingest landed on the survivor after the merge stay where the rules put them; enriched columns gained from the loser are not un-enriched; the record keeps both snapshots for a hand revert.
- A release materialises the rules' value NOW, not the `rule_value` stored at decision time; after a rules bump, undoing a decision yields the new rule's answer. Correct by the plan's model; one sentence on the ledger page.
- The effective read is an ordered scan over an index; fine to the hundreds of thousands of rows a few reviews produce; past that a maintained `decisions_effective` table is the escape hatch, and the index shape already serves it.
- Materialised columns drift if any writer bypasses `record` (a hand SQL, a backfill written around the writer); `decisions verify` detects, nothing prevents. The trigger protects the ledger, not the columns.
- The SQLite kit cannot exercise the trigger, the advisory lock or a real race; tests pin the service's behaviour and the Postgres rehearsal covers the trigger; the two-writer race is proven in reasoning only.
- A bulk approval silently narrows to the approvable subset; the response and the sheet say "N approved, M held", but nothing forces a person to read it.
- `proposals` and `proposal_passes` are mutable by design; the history of what a pass proposed across re-loads is only the files' sha256s.
- The pair row on `merge_candidates` stays the materialisation point until the rename stream; the legacy `(product_id, candidate_id)` unique must be filled for product pairs until then, and the desk's read model is not yet "literally one place".
- A consequence's uuid5 uid is identical across hosts only when its entity's natural key is; a derived line born after the fork gets a host-local consequence, which is what the target needs, but an undo row from the source naming that consequence resolves by value on the target and can park `REVERSAL_TARGET_MISSING`.
- Advisory locks serialise ledger writers; a batch's own UPDATE statements still hold product row locks until its commit, which is why approve, undo and replay refuse while a collection runs rather than promising to coexist with one.
- The `checked` review state is retired without a migration path because 0 rows carry it; a database that held one gets `VALUE_INVALID` from the shim and the backfill reports it.


## Open questions the synthesis left

- Newest-wins by (decided_at, id) rather than id alone is a refinement of W15's wording ("supersedes the earlier by being newer"); it changes nothing for native writes and is what makes a replayed older row harmless. Confirm rian is content that an older staging decision replayed onto production never overrides a newer production decision (it is recorded, reported, not applied).
- Undo restores the previous decision rather than releasing to the rules (W15 says `value = rule_value`); rian's tracked-changes picture supports restore, and both attacks required it. Confirm the wording change to the plan.
- Quantity as `attribute:quantity` (W7 registers quantity as the one kind with structured storage) versus keeping the bare `quantity` field name; the spec chooses the registry form and the shim carries both for one stream.
- Whether a sheet approval should be one transaction per brand (as specified, gated on no collection running) or commit per product line group under one batch uid; the spec chooses one transaction because batch undo is one transaction and the gate removes the deadlock case.
- Whether ingest's arrival proposals (W10: a new variant queued as a proposed addition to the nearest approved line) are written in S1 with the table or in the certain-key stream (S2); the spec admits the shape now and leaves the writer to S2.
- Whether the automatic fold's new `decided_member` veto (a group containing any variant with an effective identity decision never auto-folds) is acceptable at launch scale, given 612 lifetime rule merges; the alternative, carrying a decided loser's rows on a machine fold, needs a person-less batch the ledger otherwise forbids.
- Whether `line_slugs` (the slug history table W18's 301s need) belongs in this revision or the line-page stream; the resolver reads it as a last fallback either way.