Download platform/harness/amazon_schema.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 45.4 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/harness/amazon_schema.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable/platform/harness/amazon_schema.py
-
curl -L -o amazon_schema.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/main/platform/harness/amazon_schema.py
45.4 kB
| """amazon_schema.py β the pinned field catalogue for the Amazon connector, and the honest map of | |
| which tables actually have one. | |
| python platform/harness/amazon_schema.py --refresh re-pin from amzn/selling-partner-api-models | |
| python platform/harness/amazon_schema.py --report what each table's columns come from | |
| python platform/harness/amazon_schema.py --selftest prove the flattener and the provenance map | |
| ββ **WHY THIS FILE EXISTS: R6, AND THE TEST IT CHANGED.** Owner ruling R6 (wave 52) gives Amazon | |
| the onboarding disclosure screen fed by Amazon's PUBLISHED report schemas, and amends | |
| [[connector-onboarding-schema]]'s R4 so the test is *"is there a real schema artifact"* rather than | |
| *"is there a live `fields_get`"*. SP-API has no introspection endpoint at all, so a probe is not | |
| available at any price; a versioned, vendor-authored schema file is the artifact. | |
| ββ **AND THE ENUMERATION SAYS ONLY TWO OF SEVEN TABLES HAVE ONE.** Measured 2026-09-04 against | |
| `schemas/reports` at commit `3659f96`: 22 artifacts exist and they are all JSON-format analytics | |
| reports. **Every flat-file (TSV) report we depend on β orders, listings, settlements, the FBA | |
| ledger β has NO schema artifact anywhere in the repository.** That is not a gap to paper over: a | |
| transcribed column list is exactly what W31's R8 forbids, and `meta_store.py`'s header spends three | |
| paragraphs on why (Graph's 285 columns were ASKED-AND-ACCEPTED, never read off a doc). | |
| β **THE RESOLUTION, AND IT NEEDS NO CREDENTIAL TO BE CORRECT: A FLAT-FILE REPORT CARRIES ITS OWN | |
| HEADER ROW.** Line 1 of the downloaded document IS the column list, straight from Amazon. So those | |
| tables are declared with **no columns at all** until the first sync, and `amazon_store._measure` | |
| reads the header. That is genuinely measured rather than transcribed, it is the same discipline as | |
| Meta's, and it means this connector ships zero invented columns. | |
| **Every table therefore declares its PROVENANCE, and the disclosure screen prints it:** | |
| | provenance | meaning | tables | | |
| |---|---|---| | |
| | `schema_artifact` | columns derived mechanically from a pinned vendor JSON Schema | pricing, brand_analytics | | |
| | `measured_at_sync` | no artifact exists; columns are read from the report document's own header | orders, order_items, listings, settlements, inventory | | |
| β **THERE IS NO THIRD STATE, AND `transcribed` IS NOT ONE OF THEM.** It was considered and refused: | |
| a column list copied out of documentation prose looks identical on the screen to one that was | |
| measured, which is [[a-seeds-upload-date-is-not-its-cut-date]] wearing a different hat. A table | |
| with no artifact shows an empty column list and says why. | |
| β **THE CATALOGUE IS A FILE, NOT A LIVE CALL, so it carries `fetched_utc` and the blob sha of every | |
| artifact it was built from** β R6's own warning, and the screen must show that date. | |
| β NO NETWORK AT IMPORT. `--refresh` is the only path that reaches GitHub; everything else reads | |
| the pinned JSON off disk, so a gate, a screen and a sync all work with no connectivity. | |
| """ | |
| from __future__ import annotations | |
| import argparse | |
| import json | |
| import sys | |
| import urllib.request | |
| from datetime import datetime, timezone | |
| from pathlib import Path | |
| _HERE = Path(__file__).resolve().parent | |
| CATALOGUE = _HERE / "proto" / "amazon-report-schemas.json" | |
| if str(_HERE.parent) not in sys.path: | |
| sys.path.insert(0, str(_HERE.parent)) | |
| from harness.amazon_client import GROUP_A_TABLES, DECLARED_DATASETS | |
| from core import connector_spec | |
| _inbound_spec = connector_spec.spec_for("ut_amzn_inbound_shipments") | |
| #: The vendor repository, pinned. β `main` is what `--refresh` reads, and the resulting file | |
| #: records the exact commit and per-file blob shas it saw, so a later refresh that changes a column | |
| #: list is VISIBLE as a sha change rather than a silent redefinition. | |
| REPO = "amzn/selling-partner-api-models" | |
| RAW = "https://raw.githubusercontent.com/" + REPO + "/{ref}/{path}" | |
| API = "https://api.github.com/repos/" + REPO | |
| #: table -> (artifact path in the vendor repo, the root property holding the row array). | |
| #: β ONLY TABLES WITH A REAL ARTIFACT APPEAR HERE. The others are declared in PROVENANCE below | |
| #: with an empty column list on purpose; adding a row here without an artifact is the one edit | |
| #: this file exists to prevent. | |
| ARTIFACTS = { | |
| "pricing": ("schemas/reports/sellerSalesAndTrafficReport.json", "salesAndTrafficByAsin"), | |
| "brand_analytics": ("schemas/reports/sellingPartnerSearchTermsReport.json", | |
| "dataByDepartmentAndSearchTerm"), | |
| } | |
| #: Every table the connector ships, and where its columns come from. The seven are owner ruling | |
| #: R4; the provenance split is the enumeration above. | |
| PROVENANCE = { | |
| "orders": "measured_at_sync", | |
| "order_items": "measured_at_sync", | |
| "listings": "measured_at_sync", | |
| "settlements": "measured_at_sync", | |
| "inventory": "measured_at_sync", | |
| "pricing": "schema_artifact", | |
| "brand_analytics": "schema_artifact", | |
| "economics": "measured_at_sync", | |
| "transactions": "measured_at_sync", | |
| "fba_fee_estimates": "measured_at_sync", | |
| "referral_fees": "measured_at_sync", | |
| "storage_fees": "measured_at_sync", | |
| "search_query_performance": "measured_at_sync", | |
| "sku_keywords": "derived", | |
| "keyword_ranks": "apify_dataset", | |
| "competitor_snapshots": "competitor_run", | |
| "competitor_images": "competitor_run", | |
| "fee_estimates_api": "measured_at_sync", | |
| } | |
| PROVENANCE.update(dict.fromkeys(GROUP_A_TABLES, "measured_at_sync")) | |
| PROVENANCE.update(dict.fromkeys(DECLARED_DATASETS, "declared")) | |
| PROVENANCE.update(dict.fromkeys((t + "_history" for t in connector_spec.AMAZON_HISTORY_SOURCES), "history")) | |
| REVIEW_UNAVAILABLE_NOTE = ( | |
| "Amazon's API does not supply written reviews, average star rating or review count. " | |
| "Read them on the product page or in Seller Central.") | |
| SQP_LIMIT_NOTE = "Amazon lists at most 100 searches a month for each product." | |
| #: Human copy for the disclosure screen, per provenance. β NO EM DASH OR EN DASH: this reaches a | |
| #: screen and standing rule 2 governs it. | |
| PROVENANCE_COPY = { | |
| "competitor_run": ("Daily competitor facts saved by the competitor run. Every snapshot day is kept. " | |
| "Image rows keep each distinct variant and link, dated when first saved."), | |
| "apify_dataset": ("Apify Amazon search dataset joined to each SKU's latest matching keyword row on or before the capture date. " | |
| "Sponsored rank uses the vendor rank or ordered sponsored placements in the captured dataset. " | |
| "Global position is separate. Unmatched observations remain in the raw mirror."), | |
| "derived": ("Derived from the saved listing items and Search Query Performance databases. " | |
| "ASIN query metrics are shared across matching SKUs without allocation. " | |
| "Aggregate distinct SQP source grains for monthly totals."), | |
| "declared": "This dataset is listed for completeness; its named reason explains why no rows are loaded.", | |
| "schema_artifact": "Columns come from Amazon's published report schema, pinned below.", | |
| "measured_at_sync": ("Columns are measured from the document header or JSON record keys " | |
| "when this database syncs; no column artifact is pinned here."), | |
| "history": ("Every change to the source table. Each row is one version, " | |
| "valid from one date until the next change."), | |
| } | |
| _JSON_SCALAR = {"string": "VARCHAR", "integer": "BIGINT", "number": "DOUBLE", "boolean": "VARCHAR"} | |
| def _snake(path): | |
| """`salesByAsin.orderedProductSales.amount` -> `sales_by_asin_ordered_product_sales_amount`. | |
| A dotted JSON path becomes one flat snake_case column, because the mirror is a table and the | |
| grid that reads it has no notion of nesting. Collisions are impossible by construction: the | |
| full path is preserved, only its punctuation changes. | |
| """ | |
| out = [] | |
| for part in str(path).split("."): | |
| buf = "" | |
| for i, ch in enumerate(part): | |
| if ch.isupper() and i and not part[i - 1].isupper(): | |
| buf += "_" | |
| buf += ch.lower() | |
| out.append(buf) | |
| return "_".join(x for x in out if x) | |
| def _resolve(node, defs, seen=()): | |
| """Follow a local `$ref` chain to its definition. -> `(node, names_followed)`. | |
| β CYCLE-GUARDED, AND THE GUARD HAD TO MOVE ONCE. A first version tracked visited names inside | |
| this function alone, which is useless: `walk` calls it afresh at every level, so `A.a -> A` | |
| resolved cleanly each time and recursed 1000 deep before Python stopped it. **NC-AS4 caught it | |
| on the first run** β the check discriminates, which is the whole reason it was written before | |
| the code was trusted. The names followed are now RETURNED so the caller can carry them down | |
| its own recursion. | |
| """ | |
| names = [] | |
| n = node or {} | |
| while isinstance(n, dict) and n.get("$ref"): | |
| name = str(n["$ref"]).rsplit("/", 1)[-1] | |
| if name in seen or name in names: | |
| return {}, names | |
| names.append(name) | |
| n = defs.get(name) or {} | |
| return n or {}, names | |
| def flatten(schema, root): | |
| """One vendor JSON Schema -> an ordered list of `(column, duckdb_type)`. | |
| β MECHANICAL, WITH NO HAND-WRITTEN COLUMN ANYWHERE. That is the whole point of preferring an | |
| artifact to a doc: the list cannot drift from what Amazon published, and a refresh that changes | |
| it changes the pinned sha too. | |
| β An array of objects nested INSIDE a row is stored as compact JSON text, the same call | |
| `meta_store._cell` makes. Dropping it would violate "every field the source returns"; exploding | |
| it would silently multiply the row count. | |
| """ | |
| defs = (schema or {}).get("definitions") or {} | |
| top = ((schema or {}).get("properties") or {}).get(root) or {} | |
| node, first = _resolve(top.get("items") or top, defs) | |
| cols = [] | |
| def walk(n, prefix, seen): | |
| n, names = _resolve(n, defs, seen) | |
| seen = seen + tuple(names) | |
| kind = n.get("type") | |
| props = n.get("properties") or {} | |
| if kind == "object" or props: | |
| for name, child in props.items(): | |
| walk(child, f"{prefix}.{name}" if prefix else name, seen) | |
| return | |
| if kind == "array": | |
| cols.append((_snake(prefix), "VARCHAR")) # nested rows -> JSON text | |
| return | |
| if not prefix: | |
| return # an unresolvable root yields nothing | |
| cols.append((_snake(prefix), _JSON_SCALAR.get(str(kind), "VARCHAR"))) | |
| walk(node, "", tuple(first)) | |
| seen, out = set(), [] | |
| for c, t in cols: | |
| if c and c not in seen: | |
| seen.add(c) | |
| out.append((c, t)) | |
| return out | |
| def vendor_examples(table): | |
| """AMAZON'S OWN example rows for a `schema_artifact` table, from the pinned artifact. -> [row] | |
| ββ THIS IS THE ONLY FIXTURE THIS CONNECTOR IS ALLOWED TO HAVE, AND THAT IS THE POINT. A | |
| hand-authored fixture agrees with the parser that reads it by construction, which makes the | |
| gate's ruler the module under test [[a-gate-whose-ruler-is-the-module-under-test]]. These rows | |
| are Amazon's: they ship inside the vendor schema's own `examples` blocks, pinned by the same | |
| blob sha as the column list, so a change to either is visible as a sha change. | |
| β RETURNS [] FOR A `measured_at_sync` TABLE, and that is correct rather than a gap. Those | |
| reports publish no schema and therefore no examples, and inventing a flat-file header here | |
| would be the transcription the whole design refuses. | |
| """ | |
| spec = (catalogue().get("tables") or {}).get(str(table)) or {} | |
| if spec.get("provenance") != "schema_artifact": | |
| return [] | |
| return [dict(r) for r in (spec.get("examples") or [])] | |
| def _fetch(url, timeout=45): | |
| req = urllib.request.Request(url, headers={"User-Agent": "AIOS-Loopable/1.0 (+read-only)", | |
| "Accept": "application/json"}) | |
| with urllib.request.urlopen(req, timeout=timeout) as r: | |
| return json.loads(r.read().decode("utf-8")) | |
| def refresh(log=print): | |
| """Re-pin the catalogue from the vendor repository. The ONLY path here that uses the network.""" | |
| head = _fetch(f"{API}/commits/main") | |
| ref = str(head["sha"]) | |
| log(f"{REPO} @ {ref[:7]} ({head['commit']['committer']['date']})") | |
| listing = {e["path"]: e for e in _fetch(f"{API}/contents/schemas/reports")} | |
| tables = {} | |
| for table, (path, root) in ARTIFACTS.items(): | |
| schema = _fetch(RAW.format(ref=ref, path=path)) | |
| cols = flatten(schema, root) | |
| blob = (listing.get(path) or {}).get("sha") | |
| # β THE VENDOR'S OWN EXAMPLE ROWS, pinned beside the columns they belong to. They are | |
| # what `verify_amazon`'s pipeline section drives, so the end-to-end proof runs on Amazon's | |
| # data rather than on a fixture written by the person who wrote the parser. | |
| defs = schema.get("definitions") or {} | |
| top = ((schema.get("properties") or {}).get(root) or {}) | |
| node, _refs = _resolve(top.get("items") or top, defs) | |
| examples = [e for e in (node.get("examples") or []) if isinstance(e, dict)] | |
| tables[table] = { | |
| "provenance": "schema_artifact", | |
| "artifact": path, | |
| "artifact_sha": blob, | |
| "root": root, | |
| "columns": [{"name": c, "type": t} for c, t in cols], | |
| "examples": examples, | |
| } | |
| log(f" {table:<16} {len(cols):>3} columns {path.rsplit('/', 1)[-1]}") | |
| for table, prov in PROVENANCE.items(): | |
| if prov == "measured_at_sync": | |
| tables[table] = {"provenance": prov, "artifact": None, "artifact_sha": None, | |
| "root": None, "columns": []} | |
| log(f" {table:<16} 0 columns NO ARTIFACT PUBLISHED: measured at first sync") | |
| out = { | |
| "_comment": ("Pinned Amazon report field catalogue. Rebuild with " | |
| "`python platform/harness/amazon_schema.py --refresh`. " | |
| "Never hand-edit: every column here is derived from a vendor schema."), | |
| "repo": REPO, | |
| "commit": ref, | |
| "fetched_utc": datetime.now(timezone.utc).strftime("%Y-%m-%dT%H:%M:%SZ"), | |
| "artifacts_in_repo": len(listing), | |
| "tables": tables, | |
| } | |
| CATALOGUE.parent.mkdir(parents=True, exist_ok=True) | |
| CATALOGUE.write_text(json.dumps(out, indent=2) + "\n", encoding="utf-8") | |
| log(f"\nwrote {CATALOGUE}") | |
| return out | |
| def catalogue(): | |
| """The pinned catalogue off disk, or an empty shell when it has never been refreshed.""" | |
| try: | |
| result = json.loads(CATALOGUE.read_text(encoding="utf-8")) | |
| except Exception: # noqa: BLE001 | |
| result = {"repo": REPO, "commit": None, "fetched_utc": None, "tables": {}} | |
| from core.connector_spec import AMAZON_FIELDS | |
| descriptors = {row.key: row for row in connector_spec.tables_for("amazon")} | |
| for name, provenance in PROVENANCE.items(): | |
| key = "ut_amzn_" + name | |
| table = result.setdefault("tables", {}).setdefault(name, {}) | |
| table["provenance"] = provenance | |
| if name in DECLARED_DATASETS: | |
| declared = DECLARED_DATASETS[name] | |
| label = declared["label"] | |
| native = ", ".join(declared.get("report_types") or declared.get("operations") or ()) | |
| else: | |
| descriptor = descriptors[key] | |
| label, native = descriptor.label, descriptor.native | |
| table[key] = {"label": label, "native": native} | |
| if provenance in {"derived", "apify_dataset", "competitor_run"}: | |
| table["columns"] = [dict(field) for field in AMAZON_FIELDS.get(key, ()) | |
| if field["name"] != STORE] | |
| table["note"] = PROVENANCE_COPY[provenance] | |
| table["local_columns"] = [dict(field) for field in AMAZON_FIELDS.get(key, ()) | |
| if field["name"] == STORE] | |
| return result | |
| def columns_for(table): | |
| """The pinned column list for one table, as `[(name, type)]`. Empty when measured at sync.""" | |
| if PROVENANCE.get(str(table)) in {"derived", "apify_dataset", "competitor_run"}: | |
| return [] | |
| spec = (catalogue().get("tables") or {}).get(str(table)) or {} | |
| return [(c["name"], c.get("type") or "VARCHAR") for c in (spec.get("columns") or [])] | |
| # --------------------------------------------------------------------------- contract C1 | |
| #: The one translation from C1's short report keys to the authored database namespace. | |
| #: A-C1-KEYS preserves stored enablement keys and route paths while lane D adopts this map. | |
| DATABASE_PREFIX = "ut_amzn_" | |
| # Internal mirror metadata preserves legacy duplicate ordinals across SQL pages. | |
| IDENTITY_OCCURRENCE = "_amazon_identity_occurrence" | |
| IDENTITY_PID = "_amazon_identity_pid" | |
| CONNECTION_ID = "_connection_id" | |
| STORE = "store" | |
| MIRROR_INTERNAL_COLUMNS = frozenset((IDENTITY_OCCURRENCE, IDENTITY_PID, CONNECTION_ID)) | |
| def _table_spec(table, identity, relationships=(), *, grain="entity", line_discriminator=None): | |
| """Build the common descriptor fields from the LOCAL pin, never a vendor call.""" | |
| from core.connector_spec import AMAZON_FIELDS | |
| spec = { | |
| "columns": [{"name": name, "type": kind} for name, kind in columns_for(table)], | |
| "columns_source": { | |
| "path": "platform/harness/proto/amazon-report-schemas.json", | |
| "reader": "amazon_schema.columns_for", | |
| "provenance": PROVENANCE[table], | |
| "assumption": ( | |
| "A-QI14: This wave declares from REPORT_TYPES and the pinned artifacts. " | |
| "Measured-at-sync columns are re-measured against the first " | |
| "production sync during the deferred QI13 hotfix (intake RDA6)."), | |
| }, | |
| "row_identity": identity, | |
| "row_identity_mode": "composite", | |
| "row_identity_missing": "require_all", | |
| "row_identity_alternatives": (), | |
| "row_identity_status": "declared_pending_first_production_sync", | |
| "grain": grain, | |
| "line_discriminator": line_discriminator, | |
| "line_discriminator_source": ({ | |
| "kind": "declared_mirror_metadata", | |
| "document_column": "_report_document_id", | |
| "note": ( | |
| "Sync must retain the report document ID and the one-based parsed row position " | |
| "before filtering or deduplication, including identical repeated lines. These " | |
| "are proposed mirror metadata, not measured Amazon header columns. Replaying " | |
| "one document keeps its positions; reconciling overlapping documents is a " | |
| "separate sync responsibility. Never fall back to the order or settlement ID."), | |
| } if line_discriminator else None), | |
| "relationships": relationships, | |
| "provenance": PROVENANCE[table], | |
| "read_through": True, | |
| "row_cap": None, | |
| "read_through_status": "declared_pending_consumer_wiring", | |
| "read_through_reason": ( | |
| "Standing rule 1: these are connector records with records_mutable=False. " | |
| "Serve history read-through from the mirror without a record cap."), | |
| } | |
| fields = AMAZON_FIELDS.get(DATABASE_PREFIX + table, ()) | |
| spec["columns"].extend(dict(field) for field in fields if field["name"] != STORE) | |
| spec["local_columns"] = [dict(field) for field in fields if field["name"] == STORE] | |
| local_sources = { | |
| "derived": ("listing_items", "search_query_performance"), | |
| "apify_dataset": ("apify:khadinakbar/amazon-search-scraper", "sku_keywords"), | |
| "competitor_run": ("amazon_rivals.run_quotes", "amazon_quotes_loop.run_tenant"), | |
| } | |
| provenance = PROVENANCE[table] | |
| if provenance in local_sources: | |
| spec["columns_source"] = { | |
| "path": "platform/core/connector_spec.py", "reader": "connector_spec.AMAZON_FIELDS", | |
| "provenance": provenance, "assumption": PROVENANCE_COPY[provenance], | |
| "sources": local_sources[provenance], | |
| } | |
| return spec | |
| #: ββ TABLE_SPECS IS CONTRACT C1'S SINGLE SOURCE OF TRUTH. One entry per REPORT_TYPES key, | |
| #: checked strictly by verify_amazon. Vendor columns come through columns_for's pinned catalogue. | |
| #: Local projections declare their attribute source through connector_spec.AMAZON_FIELDS. | |
| #: columns_source carries A-QI14 in the | |
| #: descriptor itself, so a consumer cannot mistake this wave's declaration for production evidence. | |
| #: | |
| #: β IDENTITIES ARE COMPOSITES, NEVER FALLING PREFERENCES. Every component participates and no | |
| #: proper subset is an alternative identity FOR THAT TABLE. orders is the deduplicated header | |
| #: (_dedupe_orders), while order_items and settlements retain source lines. An order item ID is | |
| #: not guaranteed in the flat-file header, and even identical settlement charges must survive: | |
| #: both line tables therefore declare document + position lineage beside their parent ID. The | |
| #: lineage columns are proposed MIRROR METADATA, not invented vendor columns in the catalogue. | |
| #: | |
| #: β Identity uniqueness, line metadata and relationship column spellings await the first real | |
| #: sync. All identities say so, including the two tables with published COLUMN artifacts. Nine | |
| #: relationships retain BOTH join-column names and short target keys; the gate compares them to | |
| #: connector_spec.tables_for("amazon"), which amazon_relational still renders. Read-through is a | |
| #: REQUIRED POLICY, not a claim that the existing materialisation consumer already implements it. | |
| TABLE_SPECS = { | |
| "orders": _table_spec("orders", ("amazon_order_id",), ( | |
| ("sku", "listings", "seller_sku", "many_to_one"), | |
| ), grain="order"), | |
| "order_items": _table_spec("order_items", ( | |
| "amazon_order_id", "_report_document_id", "_report_row_number", | |
| ), ( | |
| ("amazon_order_id", "orders", "amazon_order_id", "many_to_one"), | |
| ("sku", "listings", "seller_sku", "many_to_one"), | |
| ), grain="line", line_discriminator="_report_row_number"), | |
| "listings": _table_spec("listings", ("seller_sku",)), | |
| "settlements": _table_spec("settlements", ( | |
| "settlement_id", "_report_document_id", "_report_row_number", | |
| ), ( | |
| ("amazon_order_id", "orders", "amazon_order_id", "many_to_one"), | |
| ("sku", "listings", "seller_sku", "many_to_one"), | |
| ), grain="line", line_discriminator="_report_row_number"), | |
| "inventory": _table_spec("inventory", ("asin", "fnsku", "msku"), ( | |
| ("msku", "listings", "seller_sku", "many_to_one"), | |
| ("asin", "listings", "asin1", "many_to_one"), | |
| )), | |
| # The period, not the parent, is identity: a re-parented child replaces its own window's row. | |
| "pricing": _table_spec("pricing", ("child_asin", "sku", "_period_start", "_period_end"), ( | |
| ("child_asin", "listings", "asin1", "many_to_one"), | |
| ("sku", "listings", "seller_sku", "many_to_one"), | |
| )), | |
| # D-2675: the report period is part of the identity, so each weekly pull of a term keeps its own rows. | |
| "brand_analytics": _table_spec("brand_analytics", ( | |
| "department_name", "search_term", "clicked_asin", "click_share_rank", | |
| "_period_start", "_period_end", | |
| )), | |
| "economics": _table_spec("economics", ("start_date", "msku", "marketplace_id"), ( | |
| ("msku", "listings", "seller_sku", "many_to_one"), | |
| ("child_asin", "listings", "asin1", "many_to_one"), | |
| ), grain="day_msku"), | |
| "transactions": _table_spec("transactions", ("transaction_id", "item_position"), ( | |
| ("amazon_order_id", "orders", "amazon_order_id", "many_to_one"), | |
| ("sku", "listings", "seller_sku", "many_to_one"), | |
| ), grain="transaction_item"), | |
| # S66-T40 (lead, measured live): the fee estimate snapshot has one row per SKU per Amazon | |
| # store, and the referral fee preview spells its SKU `seller_sku`. | |
| "fba_fee_estimates": _table_spec("fba_fee_estimates", ("_snapshot_date", "sku", "amazon_store"), ( | |
| ("sku", "listings", "seller_sku", "many_to_one"), | |
| ), grain="snapshot_sku"), | |
| "referral_fees": _table_spec("referral_fees", ("_snapshot_date", "seller_sku"), ( | |
| ("seller_sku", "listings", "seller_sku", "many_to_one"), | |
| ), grain="snapshot_sku"), | |
| "storage_fees": _table_spec("storage_fees", ("month", "asin", "fnsku", "fulfillment_center"), ( | |
| ("asin", "listings", "asin1", "many_to_one"), | |
| ), grain="month_product_center"), | |
| "search_query_performance": _table_spec("search_query_performance", ( | |
| "start_date", "end_date", "asin", "search_query_data_search_query"), ( | |
| ("asin", "listings", "asin1", "many_to_one"), | |
| ), grain="month_asin_search_query"), | |
| } | |
| TABLE_SPECS.update({ | |
| "long_term_storage_fees": _table_spec("long_term_storage_fees", ("_period_start", "sku", "fnsku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="month_row"), | |
| "storage_overage_fees": _table_spec("storage_overage_fees", ("_period_start", "storage_type", "_report_row_number"), | |
| (), grain="month_row"), | |
| "reimbursements": _table_spec("reimbursements", ("_period_start", "reimbursement_id", "sku", "asin", "amazon_order_id", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"), ("amazon_order_id", "orders", "amazon_order_id", "many_to_one"),), grain="window_row"), | |
| "fba_customer_returns": _table_spec("fba_customer_returns", ("_period_start", "order_id", "sku", "fnsku", "asin", "_report_row_number"), | |
| (("order_id", "orders", "amazon_order_id", "many_to_one"), ("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="window_row"), | |
| "replacements": _table_spec("replacements", ("_period_start", "replacement_amazon_order_id", "original_amazon_order_id", "sku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="window_row"), | |
| "fba_shipped_sales": _table_spec("fba_shipped_sales", ("_period_start", "amazon_order_id", "sku", "fnsku", "asin", "_report_row_number"), | |
| (("amazon_order_id", "orders", "amazon_order_id", "many_to_one"), ("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="window_row"), | |
| "fba_shipment_promotions": _table_spec("fba_shipment_promotions", ("_period_start", "amazon_order_id", "sku", "_report_row_number"), | |
| (("amazon_order_id", "orders", "amazon_order_id", "many_to_one"), ("sku", "listings", "seller_sku", "many_to_one"),), grain="window_row"), | |
| "inventory_ledger_detail": _table_spec("inventory_ledger_detail", ("_period_start", "msku", "fnsku", "asin", "_report_row_number"), | |
| (("msku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="window_row"), | |
| "inventory_planning": _table_spec("inventory_planning", ("_snapshot_date", "sku", "fnsku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "manage_inventory": _table_spec("manage_inventory", ("_snapshot_date", "sku", "fnsku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "reserved_inventory": _table_spec("reserved_inventory", ("_snapshot_date", "sku", "fnsku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "restock_recommendations": _table_spec("restock_recommendations", ("_snapshot_date", "merchant_sku", "fnsku", "asin", "_report_row_number"), | |
| (("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "stranded_inventory": _table_spec("stranded_inventory", ("_snapshot_date", "sku", "fnsku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "inbound_noncompliance": _table_spec("inbound_noncompliance", ("_period_start", "shipment_id", "sku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="window_row"), | |
| "inventory_expiration_dates": _table_spec("inventory_expiration_dates", ("_snapshot_date", "sku", "fnsku", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"),), grain="snapshot_row"), | |
| "recommended_removals": _table_spec("recommended_removals", ("_snapshot_date", "sku", "fnsku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "removal_orders": _table_spec("removal_orders", ("_period_start", "order_id", "sku", "fnsku", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"),), grain="window_row"), | |
| "removal_shipments": _table_spec("removal_shipments", ("_period_start", "order_id", "sku", "fnsku", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"),), grain="window_row"), | |
| "subscribe_save_forecast": _table_spec("subscribe_save_forecast", ("_snapshot_date", "sku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "subscribe_save_performance": _table_spec("subscribe_save_performance", ("_period_start", "sku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="window_row"), | |
| "remote_fulfillment_eligibility": _table_spec("remote_fulfillment_eligibility", ("_snapshot_date", "sku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "search_catalog_performance": _table_spec("search_catalog_performance", ("_period_start", "asin", "_report_row_number"), | |
| (("asin", "listings", "asin1", "many_to_one"),), grain="month_row"), | |
| "market_basket": _table_spec("market_basket", ("_period_start", "asin", "purchased_with_asin", "_report_row_number"), | |
| (("asin", "listings", "asin1", "many_to_one"),), grain="month_row"), | |
| "repeat_purchase": _table_spec("repeat_purchase", ("_period_start", "asin", "_report_row_number"), | |
| (("asin", "listings", "asin1", "many_to_one"),), grain="month_row"), | |
| "promotion_performance": _table_spec("promotion_performance", ("_period_start", "promotion_id", "_report_row_number"), | |
| (), grain="window_row"), | |
| "coupon_performance": _table_spec("coupon_performance", ("_period_start", "coupon_id", "_report_row_number"), | |
| (), grain="window_row"), | |
| "account_health": _table_spec("account_health", ("_snapshot_date", "marketplace_id", "_json_root", "_report_row_number"), | |
| (), grain="snapshot_row"), | |
| "inactive_listings": _table_spec("inactive_listings", ("_snapshot_date", "seller_sku", "asin1", "_report_row_number"), | |
| (("seller_sku", "listings", "seller_sku", "many_to_one"),), grain="snapshot_row"), | |
| "cancelled_listings": _table_spec("cancelled_listings", ("_snapshot_date", "seller_sku", "asin1", "_report_row_number"), | |
| (("seller_sku", "listings", "seller_sku", "many_to_one"),), grain="snapshot_row"), | |
| "suppressed_listings": _table_spec("suppressed_listings", ("_snapshot_date", "sku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "listing_defects": _table_spec("listing_defects", ("_snapshot_date", "sku", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"),), grain="snapshot_row"), | |
| "sold_listings": _table_spec("sold_listings", ("_snapshot_date", "sku", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"),), grain="snapshot_row"), | |
| "mfn_returns": _table_spec("mfn_returns", ("_period_start", "order_id", "sku", "asin", "_report_row_number"), | |
| (("order_id", "orders", "amazon_order_id", "many_to_one"), ("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="window_row"), | |
| "b2b_recommended": _table_spec("b2b_recommended", ("_snapshot_date", "asin", "_report_row_number"), | |
| (("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "b2b_not_yet_on_amazon": _table_spec("b2b_not_yet_on_amazon", ("_snapshot_date", "ean", "upc", "part_number", "_report_row_number"), | |
| (), grain="snapshot_row"), | |
| "geo_opportunities": _table_spec("geo_opportunities", ("_snapshot_date", "asin", "_report_row_number"), | |
| (("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "out_of_stock_mfn": _table_spec("out_of_stock_mfn", ("_snapshot_date", "sku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "b2b_quotes": _table_spec("b2b_quotes", ("_snapshot_date", "sku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "fee_discounts": _table_spec("fee_discounts", ("_snapshot_date", "sku", "asin", "_report_row_number"), | |
| (("sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "order_metrics": _table_spec("order_metrics", ("_period_start", "interval", "_report_row_number"), | |
| (), grain="window_row"), | |
| "fba_inventory": _table_spec("fba_inventory", ("_snapshot_date", "seller_sku", "fn_sku", "asin", "_report_row_number"), | |
| (("seller_sku", "listings", "seller_sku", "many_to_one"), ("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "listing_items": _table_spec("listing_items", ("sku",), | |
| (("sku", "listings", "seller_sku", "many_to_one"),), grain="snapshot_row"), | |
| "catalog_items": _table_spec("catalog_items", ("_snapshot_date", "asin", "_report_row_number"), | |
| (("asin", "listings", "asin1", "many_to_one"),), grain="snapshot_row"), | |
| "fee_estimates_api": _table_spec("fee_estimates_api", ("estimate_id",), | |
| (("sku", "listings", "seller_sku", "many_to_one"),), grain="price_estimate"), | |
| "outbound_fulfillment": _table_spec("outbound_fulfillment", ("seller_fulfillment_order_id", "seller_fulfillment_order_item_id"), | |
| (("seller_sku", "listings", "seller_sku", "many_to_one"),), grain="fulfillment_order_item"), | |
| "inbound_shipments": _table_spec("inbound_shipments", _inbound_spec.identity, | |
| tuple((column, target.removeprefix(DATABASE_PREFIX), field, cardinality) | |
| for column, target, field, cardinality in _inbound_spec.links), grain="inbound_shipment_item"), | |
| "replenishment": _table_spec("replenishment", ("metric_id",), | |
| (("sku", "listings", "seller_sku", "many_to_one"),), grain="replenishment_metric"), | |
| "review_topics": _table_spec("review_topics", ("asin", "marketplace_id", "sort_by", "sentiment", "topic"), | |
| (("asin", "listings", "asin1", "many_to_one"),), grain="asin_sort_sentiment_topic"), | |
| "review_trends": _table_spec("review_trends", ("asin", "marketplace_id", "sentiment", "topic", "month"), | |
| (("asin", "listings", "asin1", "many_to_one"),), grain="asin_sentiment_topic_month"), | |
| "browse_node_review_topics": _table_spec("browse_node_review_topics", | |
| ("browse_node_id", "marketplace_id", "sort_by", "sentiment", "topic"), grain="browse_node_sort_sentiment_topic"), | |
| "sku_keywords": _table_spec("sku_keywords", ("keyword_id",), | |
| (("sku", "listing_items", "sku", "many_to_one"),), grain="sku_period_asin_query"), | |
| "keyword_ranks": _table_spec("keyword_ranks", ("rank_id",), | |
| (("keyword_id", "sku_keywords", "keyword_id", "many_to_one"),), grain="keyword_placement_per_sku"), | |
| "competitor_snapshots": _table_spec("competitor_snapshots", ("asin", "snapshot_date"), | |
| grain="asin_day"), | |
| "competitor_images": _table_spec("competitor_images", ("asin", "variant", "link"), | |
| grain="asin_variant_link"), | |
| }) | |
| # A history table is one row per version, identified by its version key and first valid date. | |
| TABLE_SPECS.update({t + "_history": _table_spec(t + "_history", ("_history_key", "valid_from"), grain="version") | |
| for t in connector_spec.AMAZON_HISTORY_SOURCES}) | |
| def row_key_columns_for(table, columns=()): | |
| """Return declared identity columns, qualifying report positions by their document.""" | |
| identity = TABLE_SPECS[table]["row_identity"] | |
| if table == "order_items" and "order_item_id" in columns: | |
| identity = ("order_item_id", *identity) | |
| if ("_report_row_number" in identity and "_report_document_id" not in identity | |
| and "_report_document_id" in columns): | |
| identity = (*identity, "_report_document_id") | |
| return (CONNECTION_ID, *identity) if CONNECTION_ID in columns else identity | |
| def row_fallback_columns_for(table): | |
| """Return source lineage used when a declared identity is incomplete.""" | |
| coordinates = ("_report_document_id", "_report_row_number") | |
| return ("order_item_id", *coordinates) if table == "order_items" else coordinates | |
| def row_identity_for(table, row): | |
| """Evaluate the declared full composite. Missing lineage is an error, never a parent ID. | |
| This is a descriptor evaluator, not a production uniqueness measurement or a sync adapter. | |
| Return a tuple so delimiters inside values cannot merge two different component sequences. | |
| """ | |
| spec = TABLE_SPECS[table] | |
| if spec["row_identity_mode"] != "composite" or spec["row_identity_missing"] != "require_all": | |
| raise ValueError(f"{table}: unsupported row identity policy") | |
| identity = spec["row_identity"] | |
| if not identity or spec["row_identity_alternatives"]: | |
| raise ValueError(f"{table}: exactly one complete row identity is required") | |
| if CONNECTION_ID in row: | |
| identity = (CONNECTION_ID, *identity) | |
| missing = [name for name in identity if row.get(name) in (None, "")] | |
| if missing: | |
| raise ValueError(f"{table}: missing declared row identity components: {', '.join(missing)}") | |
| return tuple(row[name] for name in identity) | |
| def disclosure(): | |
| """What the onboarding screen shows: one row per table, with its provenance and sync date. | |
| β `synced` IS THE CATALOGUE'S FETCH DATE, NOT TODAY. R6 is explicit that an unstamped | |
| catalogue is [[a-seeds-upload-date-is-not-its-cut-date]] waiting to happen, and the screen is | |
| the only place a stale pin becomes visible. | |
| """ | |
| cat = catalogue() | |
| rows = [] | |
| for table in PROVENANCE: | |
| spec = (cat.get("tables") or {}).get(table) or {} | |
| prov = spec.get("provenance") or PROVENANCE[table] | |
| rows.append({ | |
| "table": f"ut_amzn_{table}", | |
| "provenance": prov, | |
| "note": PROVENANCE_COPY[prov], | |
| "columns": len(TABLE_SPECS[table]["columns"] if prov == "derived" else spec.get("columns") or []), | |
| "artifact": spec.get("artifact"), | |
| }) | |
| return {"source": "Amazon", "synced": cat.get("fetched_utc"), "commit": cat.get("commit"), | |
| "repo": cat.get("repo"), "tables": rows} | |
| # --------------------------------------------------------------------------- selftest | |
| def selftest(log=print): | |
| """Prove the flattener, the provenance map and the disclosure shape. No network. | |
| β EVERY CHECK BELOW WAS DRIVEN RED BEFORE IT WAS TRUSTED. Green on a first run proves a check | |
| exists, never that it discriminates [[revert-one-file-to-see-if-a-gate-discriminates]]. | |
| """ | |
| fails = [] | |
| def ck(name, cond, detail=""): | |
| """Detail is ASCII-escaped: a failure message carrying a hostile byte must not kill the | |
| run. Same reasoning, and the same incident, as `amazon_store.selftest`'s reporter.""" | |
| if cond: | |
| log(f" ok {name}") | |
| return | |
| try: | |
| shown = str(detail).encode("ascii", "backslashreplace").decode("ascii") | |
| except Exception: # noqa: BLE001 | |
| shown = "<undisplayable>" | |
| log(f" FAIL {name} {shown}") | |
| fails.append(name) | |
| log("amazon_schema selftest") | |
| # NC-AS1..3 β the flattener, against a miniature of the real artifact shape. | |
| mini = { | |
| "properties": {"rows": {"type": "array", "items": {"$ref": "#/definitions/Row"}}}, | |
| "definitions": { | |
| "Row": {"type": "object", "properties": { | |
| "parentAsin": {"type": "string"}, | |
| "salesByAsin": {"$ref": "#/definitions/Sales"}, | |
| "tags": {"type": "array", "items": {"type": "string"}}}}, | |
| "Sales": {"type": "object", "properties": { | |
| "unitsOrdered": {"type": "integer"}, | |
| "orderedProductSales": {"$ref": "#/definitions/Amount"}}}, | |
| "Amount": {"type": "object", "properties": { | |
| "amount": {"type": "number"}, "currencyCode": {"type": "string"}}}, | |
| }, | |
| } | |
| got = dict(flatten(mini, "rows")) | |
| ck("NC-AS1 refs resolve through two levels", | |
| "sales_by_asin_ordered_product_sales_amount" in got, sorted(got)[:6]) | |
| ck("NC-AS2 scalar types map to DuckDB types", | |
| got.get("sales_by_asin_units_ordered") == "BIGINT" | |
| and got.get("sales_by_asin_ordered_product_sales_amount") == "DOUBLE" | |
| and got.get("parent_asin") == "VARCHAR", | |
| {k: got.get(k) for k in ("parent_asin", "sales_by_asin_units_ordered")}) | |
| ck("NC-AS3 a nested array becomes one JSON text column", | |
| got.get("tags") == "VARCHAR" and len(got) == 5, sorted(got)) | |
| # NC-AS4 β a cycle must terminate rather than hang. | |
| cyc = {"properties": {"rows": {"$ref": "#/definitions/A"}}, | |
| "definitions": {"A": {"type": "object", "properties": {"a": {"$ref": "#/definitions/A"}, | |
| "x": {"type": "string"}}}}} | |
| ck("NC-AS4 a self-referential schema terminates", dict(flatten(cyc, "rows")).get("x") == "VARCHAR") | |
| # NC-AS5 β ARTIFACTS may never claim a table whose provenance is measured-at-sync. This is the | |
| # check that stops the one edit this file exists to prevent. | |
| bad = [t for t in ARTIFACTS if PROVENANCE.get(t) != "schema_artifact"] | |
| ck("NC-AS5 every ARTIFACTS row is declared schema_artifact", not bad, bad) | |
| # NC-AS6 β and the converse: a schema_artifact table with no artifact is a lie on the screen. | |
| orphan = [t for t, p in PROVENANCE.items() if p == "schema_artifact" and t not in ARTIFACTS] | |
| ck("NC-AS6 every schema_artifact table has an artifact", not orphan, orphan) | |
| # NC-AS7 β the seven tables of R4, no more and no fewer. | |
| ck("NC-AS7 table and declared dataset keys cover group (a)", | |
| set(PROVENANCE) == set(TABLE_SPECS) | set(DECLARED_DATASETS), sorted(PROVENANCE)) | |
| # NC-AS8 β standing rule 2 governs PROVENANCE_COPY: it reaches the disclosure screen. | |
| dashed = [k for k, v in PROVENANCE_COPY.items() if "β" in v or "β" in v] | |
| ck("NC-AS8 no em dash or en dash in screen copy", not dashed, dashed) | |
| # NC-AS9 β disclosure() names every table and carries the sync stamp key even when unpinned. | |
| d = disclosure() | |
| ck("NC-AS9 disclosure covers all datasets and carries a synced field", | |
| len(d["tables"]) == len(PROVENANCE) and "synced" in d and all(r["table"].startswith("ut_amzn_") | |
| for r in d["tables"])) | |
| # NC-AS10 β a measured_at_sync table must ship ZERO columns. An invented column is the defect. | |
| cat = catalogue() | |
| invented = [t for t, s in (cat.get("tables") or {}).items() | |
| if s.get("provenance") == "measured_at_sync" and (s.get("columns") or [])] | |
| ck("NC-AS10 no measured_at_sync table carries invented columns", not invented, invented) | |
| log(f"\n{'GREEN' if not fails else 'RED'}: {10 - len(fails)}/10") | |
| return 0 if not fails else 1 | |
| def main(argv=None): | |
| ap = argparse.ArgumentParser(description=__doc__.splitlines()[0]) | |
| ap.add_argument("--refresh", action="store_true", help="re-pin from the vendor repository") | |
| ap.add_argument("--report", action="store_true", help="print the provenance map") | |
| ap.add_argument("--selftest", action="store_true") | |
| a = ap.parse_args(argv) | |
| if a.selftest: | |
| return selftest() | |
| if a.refresh: | |
| refresh() | |
| return 0 | |
| d = disclosure() | |
| print(f"Amazon field catalogue repo {d['repo']} commit {str(d['commit'])[:7]} " | |
| f"synced {d['synced']}") | |
| for r in d["tables"]: | |
| print(f" {r['table']:<24} {r['provenance']:<18} {r['columns']:>3} columns " | |
| f"{r['artifact'] or ''}") | |
| return 0 | |
| if __name__ == "__main__": | |
| sys.exit(main()) | |