Download api/amazon_relational.py from fsanyoto/loopable: direct link, hf CLI and curl.
- Browser
- Download file 92.3 kB
-
https://huggingface.co/spaces/fsanyoto/loopable/resolve/9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/api/amazon_relational.py
- Command line
-
hf download hf://spaces/fsanyoto/loopable@9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/api/amazon_relational.py
-
curl -L -o amazon_relational.py https://huggingface.co/spaces/fsanyoto/loopable/resolve/9291c0100f4e7b8da2efcbbf1ebdea72ee1663e7/api/amazon_relational.py
92.3 kB
"""amazon_relational.py β the Amazon mirror becomes LOCKED DATABASES under Data (wave 52, R14).
`harness/amazon_store.py` pulls SP-API report documents into the tenant's DuckDB mirror; this reads
that mirror and spawns the `ut_amzn_*` grids a person opens. It is `meta_relational.py`'s shape
with one source swapped, which is itself `odoo_relational.py`'s shape.
plan(cur) the rows that WOULD be written β no store write at all
apply_plan(rt, built) create-or-merge every table and its rows, in ONE sync write
refresh(rt, why) both, with the mirror opened for this tenant
ββ **THE COLUMNS ARE DERIVED FROM THE MIRROR, NEVER TYPED OUT HERE**, exactly as Meta's are. For
Amazon that is doubly load-bearing: five of the seven tables have their columns MEASURED from the
report document's own header row at sync time (`amazon_schema.PROVENANCE`), so a hand-written field
list here would not merely rot, it would contradict the only measurement that exists. Add a column
to the report and it appears in the grid with no edit in this file.
ββ **R14 β THIS FILE IS WHERE THE CONNECTOR ENDS.** *"this is just database from Amazon, all its
fields mapped into our app so it can be seen under Data module and later be pulled into Sheet. All
database connector has this exact flow."* No dashboard, no module, no metric page. `Data` lists
these tables because they are `ut_*` tables; `sheet/BindingEditor.tsx:61` binds them on the `ut_`
prefix. Both are prefix-driven, so **neither needs a line of work here** β step 5 of `/connector` is
a verification, not a build.
β **R12 β AMAZON IS A STANDALONE DATABASE SET.** Owner: *"Its all different. Assume that its a
whole new databases which they are."* `LINKS` below therefore joins Amazon tables only to OTHER
AMAZON TABLES: order lines to their order, pricing to its listing. **Nothing here joins to
`product_data`, to `ut_odoo_*`, or to the hand-maintained ASIN mastersheet.** β That is what
removes the [[amazon-conversion-k-is-inverted]] defect class outright: no Odoo unit is ever
converted to an Amazon unit, because the two never meet.
History stays in the mirror. Small snapshots may be materialised; a row or byte budget switches
them to read-through too. The shared document holds their schemas, never a truncated population.
"""
import hashlib
import json
import os
import re
import sys
from collections import OrderedDict
from pathlib import Path
from threading import RLock
_API = Path(__file__).resolve().parent
if str(_API.parent.parent / "platform") not in sys.path:
sys.path.insert(0, str(_API.parent.parent / "platform"))
import odoo_relational as _odoo # noqa: E402
from core import connector_spec # noqa: E402
from harness import amazon_client, amazon_schema, amazon_store, datastore # noqa: E402
from harness import amazon_ads_store # noqa: E402
Refused = _odoo.Refused
#: β THE PREFIX IS `ut_amzn_`, AND IT MUST ALSO BE IN `routes_connectors.CONNECTED_PREFIXES` β
#: otherwise `is_connected` falls through and every Amazon table is silently re-capped at
#: `MAX_ROWS`, breaking standing rule 1 with nothing raising. `verify_amazon` asserts the pair.
PREFIX = "ut_amzn_"
#: `ut_*` key -> the mirror table `amazon_store` writes. Derived from ONE list so a table cannot
#: exist on one side and not the other.
#: S79-T13: the Ads tables (`ut_amzn_ads_*`, connector `amazon_ads`) live in the same mirror.
_ADS_SPEC = {s.key: s for s in connector_spec.tables_for("amazon_ads")}
SOURCE = {**{PREFIX + t: amazon_store.table_name(t) for t in amazon_store.DATABASE_TABLES},
**{k: amazon_ads_store.table_name(s.source) for k, s in _ADS_SPEC.items()}}
#: ββ **THE TABLE SHAPE IS NOW READ FROM `platform/core/connector_spec.py`, NOT TYPED HERE**
#: (W53-T37, contract C2). `LABELS`, `PINNED`, `IDENTITY` and `LINKS` keep their module-level
#: names because `verify_amazon`, `verify_meta.section_connector_spec` and the connector routes all
#: read them BY NAME β but each is a DERIVED VIEW over ONE declaration now, so the shape can no
#: longer be stated twice and say two things. Every comment that used to justify a literal here
#: (the two spines, R12, the report-vs-API spellings, the cardinalities) travelled with the values
#: into `connector_spec.AMAZON_TABLES`; read it there.
_SPEC = {**{s.key: s for s in connector_spec.tables_for("amazon")}, **_ADS_SPEC}
def _identity_columns(table, columns=()):
"""An Ads row is identified by its stored `_row_key`; every other table by amazon_schema."""
if PREFIX + table in _ADS_SPEC:
return ("_row_key",)
return amazon_schema.row_key_columns_for(table, columns)
def _fallback_columns(table):
if PREFIX + table in _ADS_SPEC:
return ("_report_id",)
return amazon_schema.row_fallback_columns_for(table)
#: β A `ut_*` KEY THAT `amazon_store` SPAWNS BUT THE SPEC DOES NOT DECLARE IS ABSENT FROM ALL FOUR
#: DICTS BELOW, ON PURPOSE. `verify_amazon.section_relational` asserts the four key sets equal
#: `SOURCE`'s, so the drift reddens a gate rather than raising at import inside a live Space, and
#: it names the missing table instead of a KeyError deep in `apply_plan`.
LABELS = {k: _SPEC[k].label for k in SOURCE if k in _SPEC}
#: The column each grid shows first. β Every one is a name Amazon's own reports use, so a table
#: whose header does not carry it simply has no pinned column rather than a broken one.
PINNED = {k: _SPEC[k].pinned for k in SOURCE if k in _SPEC}
#: Legacy descriptor view. Row keys use amazon_schema's complete identity and source lineage.
IDENTITY = {k: tuple(_SPEC[k].identity) for k in SOURCE if k in _SPEC}
#: `{ut_ key: [(this table's column, target ut_ key, the column it matches there), ...]}`.
#: β THE CARDINALITY IS DROPPED HERE AND KEPT IN THE SPEC. This view stays a 3-tuple because that
#: is the shape `verify_amazon.section_relational` unpacks; `connector_spec.edges_for` is what
#: `_fields_for` actually renders from, and it carries all four.
#: β AMAZON TO AMAZON ONLY (R12), and a link is DECLARED in the spec but VERIFIED AT SPAWN: five of
#: seven tables are `measured_at_sync`, so every entry is a hypothesis about a report column name
#: until `_fields_for` confirms BOTH sides carry it. A wrong guess here is cheap; a wrong guess in a
#: JOIN would not be. A table with no links is absent rather than present-and-empty, matching the
#: dict this replaced.
LINKS = {k: [(c, t, o) for c, t, o, _cardinality in _SPEC[k].links]
for k in SOURCE if k in _SPEC and _SPEC[k].links}
def source_attribution(key):
"""Durable origin metadata for one materialised Amazon database.
DPP 1.8 permits a database separation strategy or a mechanism that tags and identifies the
origin of data. The DuckDB mirror uses the reserved ``amzn_*`` namespace already; this stamp
is the corresponding mechanism in the shared ``user_tables`` document. It is written on
every refresh so a user-facing label change cannot erase the Amazon origin or the exact raw
table/report that supplied the rows.
"""
if key not in SOURCE or key not in _SPEC:
raise KeyError(f"unknown Amazon database {key!r}")
return {
"key": "amazon_ads" if key in _ADS_SPEC else "amazon_sp",
"origin": ("Amazon Ads API" if key in _ADS_SPEC
else "Apify Amazon search dataset" if key == PREFIX + "keyword_ranks"
else "Amazon Services API"),
"rawTable": SOURCE[key],
"native": _SPEC[key].native,
"readOnly": True,
}
def credential_table_owner(_key, _rt):
"""Combined Amazon tables have row owners and never belong to one credential."""
return None
CONNECTION_REVOCATIONS_KEY = "amazon_connection_revocations"
def record_connection_revocation(rt, connection_id, *, entries_before):
"""Keep metadata for retrying scoped cleanup after the credential itself has been removed."""
cid = str(connection_id or "")
entries = [{key: e[key] for key in ("id", "connection_id", "type", "slot", "label") if key in e}
for e in entries_before if e.get("type") == "amazon_sp"]
if not cid or not any(e.get("connection_id") == cid for e in entries):
raise ValueError("Amazon revocation requires the original connection metadata")
def save(doc):
doc[cid] = {"state": "pending", "entries": entries}
return doc
rt.update(CONNECTION_REVOCATIONS_KEY, save, flush="sync")
def pending_connection_revocation(rt, connection_id):
"""Return retry metadata only while this connection's cleanup remains unfinished."""
row = (rt.get(CONNECTION_REVOCATIONS_KEY) or {}).get(str(connection_id)) or {}
return row.get("entries") if row.get("state") == "pending" else None
def connection_footprint(rt, connection_id, *, entries_before=None):
"""Disclose this connection's rows without claiming ownership of a combined table."""
from contextlib import closing
from core import keychain
entries = amazon_store._ordered_account_entries(
entries_before if entries_before is not None else keychain.list_entries(rt))
cid = str(connection_id or "")
if not cid or not any(e["connection_id"] == cid for e in entries):
raise ValueError("Amazon footprint requires an explicit stored connection")
path = amazon_store._tenant_mirror_path(rt.key, rt)
out = {"type": "amazon_sp", "connectionId": cid, "action": "purge", "purge": [], "keep": [],
"rows": 0, "rowsKnown": path.is_file(), "why": "Only this connection's rows are deleted. Shared tables remain."}
projection = getattr(rt, "get_projection", None)
definitions = projection("user_tables", drop=("rows",)) if callable(projection) else rt.get("user_tables")
if definitions is None:
definitions = {}
if not isinstance(definitions, dict):
raise RuntimeError("Amazon database definitions are unreadable")
if not path.is_file():
out["purge"] = [{"key": key, "label": defn.get("label") or key, "rows": 0, "rowsKnown": False}
for key, defn in definitions.items() if key in SOURCE and key not in _ADS_SPEC]
return out
import duckdb
cursor = datastore.ro_cursor() if path.resolve() == Path(datastore.DB_PATH).resolve() else datastore.bounded_connection(
duckdb.connect(str(path), read_only=True))
with closing(cursor) as cur:
bound = amazon_store._read_state(cur, "amzn_account_binding")
legacy_owner = bound["detail"] if bound else entries[0]["connection_id"]
for key in SOURCE.keys() - _ADS_SPEC.keys():
defn = definitions.get(key) or {}
columns = amazon_store._columns_of(cur, SOURCE[key])
if not columns and key not in definitions:
continue
if not columns:
count = 0
elif amazon_schema.CONNECTION_ID in columns:
count = cur.execute(f'SELECT count(*) FROM {SOURCE[key]} WHERE "_connection_id"=?', [cid]).fetchone()[0]
else:
count = cur.execute(f"SELECT count(*) FROM {SOURCE[key]}").fetchone()[0] if cid == legacy_owner else 0
out["purge"].append({"key": key, "label": defn.get("label") or LABELS.get(key) or key,
"rows": count, "rowsKnown": True})
out["rows"] += count
out["purge"].sort(key=lambda table: table["key"])
return out
def purge_connection(rt, connection_id, *, entries_before=None):
"""Remove this account's stored snapshots and observations while retaining shared definitions."""
cid = str(connection_id or "")
if entries_before is None:
entries_before = pending_connection_revocation(rt, cid)
if not entries_before:
raise ValueError("Amazon scoped cleanup requires saved revocation metadata")
record_connection_revocation(rt, cid, entries_before=entries_before)
footprint = connection_footprint(rt, cid, entries_before=entries_before)
from contextlib import closing
con, _path = amazon_store._connect_tenant_mirror(rt.key, rt)
with closing(con):
binding = amazon_store._read_state(con, "amzn_account_binding")
entries = amazon_store._ordered_account_entries(entries_before)
legacy_owner = binding["detail"] if binding else entries[0]["connection_id"]
def sweep(doc):
for key, table in doc.items():
if key not in SOURCE or key in _ADS_SPEC or not isinstance(table, dict):
continue
rows = table.get("rows") or {}
for rid, row in list(rows.items()):
if row.get(amazon_schema.CONNECTION_ID, legacy_owner) == cid:
rows.pop(rid)
return doc
rt.update("user_tables", sweep, flush="sync")
removed = amazon_store.purge_connection(rt.key, cid, rt=rt, entries_before=entries_before)
def complete(doc):
doc[cid] = {"state": "done", "snapshotId": removed["snapshotId"]}
return doc
rt.update(CONNECTION_REVOCATIONS_KEY, complete, flush="sync")
return {"purged": [table["key"] for table in footprint["purge"]], "refused": [], "failed": [],
"rows": removed["rows"], "rowsKnown": True, "footprint": footprint}
#: Columns whose NAME makes them a date whatever they are stored as. Amazon reports ISO-8601.
_DATEISH = ("purchase_date", "last_updated_date", "payments_date", "settlement_start_date",
"settlement_end_date", "deposit_date", "posted_date", "open_date", "date",
"report_date", "start_date", "end_date", "_snapshot_date", "month", "_time",
"valid_from", "valid_to")
#: β MONEY IS `currency`, NEVER `int`. An Amazon `item-price` is fractional in every marketplace,
#: and an int column would round every row of a settlement. The grid shows what the report said and
#: converts nothing.
_MONEY = {"item_price", "item_tax", "shipping_price", "shipping_tax", "gift_wrap_price",
"gift_wrap_tax", "item_promotion_discount", "ship_promotion_discount", "price",
"amount", "total_amount", "principal", "shipping", "tax", "promotion", "fee",
"your_price", "landed_price", "listing_price", "open_date_price",
"sales_by_asin_ordered_product_sales_amount",
"sales_by_asin_ordered_product_sales_b2_b_amount"}
_MONEY.update({"estimated_fee_total", "estimated_referral_fee_per_unit",
"estimated_variable_closing_fee", "estimated_order_handling_fee_per_order",
"estimated_pick_pack_fee_per_unit", "estimated_weight_handling_fee_per_unit",
"expected_fulfillment_fee_per_unit", "estimated_monthly_storage_fee"})
#: S78-T01: money the report stores as text or names without an `_amount` / `_fee` suffix.
#: β No `_price` suffix rule: `attributes_list_price` and the `*_median_*_price` details are JSON.
_MONEY.update({"business_price", "label_cost", "fee_total", "sales_price", "item_price_per_unit",
"featuredoffer_price", "lowest_price_new_plus_shipping", "lowest_price_used",
"recommended_sales_price", "estimated_cost_savings_of_recommended_actions",
"estimated_storage_cost_next_month", "sales_last_30_days", "amount_charged",
"rate_surcharge", "estimated_referral_fee_per_item", "amount_per_unit",
"amount_total", "base_rate", "est_base_msf", "est_sus", "utilization_surcharge_rate",
"estimated_future_fee_current_selling_on_amazon_future_fulfillment_fees",
"estimated_future_order_handling_fee_per_order",
"estimated_future_pick_pack_fee_per_unit",
"estimated_future_weight_handling_fee_per_unit",
"expected_future_fulfillment_fee_per_unit", "lost_revenue_due_to_oos",
"non_subscriber_average_revenue", "subscriber_average_revenue",
"total_subscriptions_revenue"})
_MONEY_PREFIX = ("cost_", "sales_shipped_last_", "estimated_ais_", "sns_sale_price_week_",
"revenue_from_")
_MONEY_INFIX = ("_life_time_value_",)
#: β S78-T01: rates and shares are `pct`, and the grid shows a `pct` value as the number plus
#: "%" with no scaling. So the scale is MEASURED per column on the staging mirror (2026-10-20):
#: `_PERCENT` columns already hold 0 to 100, `_FRACTION_PERCENT` columns hold 0 to 1 and are
#: multiplied by 100 in the read path (`grid_source`), so 0.15 reads "15%" and never "0.15%".
#: β A rate whose scale was not measured stays out of both sets (see `oos_rate_week_*`).
_PERCENT_SUFFIX = ("_percentage", "_percentage_b2_b")
_PERCENT = {"browse_node_metrics_occurrence_percentage_all_products",
"sales_by_asin_refund_rate", "sales_by_asin_refund_rate_b2_b",
"cart_add_data_asin_cart_add_share", "click_data_asin_click_share",
"impression_data_asin_impression_share", "purchase_data_asin_purchase_share",
"cart_add_data_total_cart_add_rate", "click_data_total_click_rate",
"purchase_data_total_purchase_rate", "revenue_penetration",
"coupons_revenue_penetration", "share_of_coupon_subscriptions",
"subscriber_retention_for30_days", "subscriber_retention_for90_days",
*(f"{metric}_for{n}_percent_seller_funding" for metric in
("revenue_penetration", "signup_conversion") for n in (0, 5, 10))}
_FRACTION_PERCENT = {"click_share", "conversion_share", "click_data_click_rate",
"purchase_data_conversion_rate", "combination_pct"}
#: Account health reports each rate metric and its target as a 0 to 1 fraction.
_FRACTION_PERCENT_RE = re.compile(r"[a-z_]+_rate(?:_afn|_mfn)?_(?:rate|target_value)")
#: Non-money numbers stored as text. With the DOUBLE columns that are not money, they take
#: `currency` (the grid's only fractional type) with an EMPTY currency symbol, so no "$" shows.
_NUMBER = {"storage_utilization_ratio"}
def _is_money(col):
return (col in _MONEY or col.endswith(("_amount", "_fee", "_fees")) or col.startswith(_MONEY_PREFIX)
or any(part in col for part in _MONEY_INFIX))
def _is_fraction_percent(col):
return col in _FRACTION_PERCENT or bool(_FRACTION_PERCENT_RE.fullmatch(col))
def _is_percent(col):
return col in _PERCENT or col.endswith(_PERCENT_SUFFIX) or _is_fraction_percent(col)
# Inventory reports remain VARCHAR when a quantity column mixes numbers and blanks.
# Names identify units; identifiers such as SKU and FNSKU must stay text.
_QUANTITY = {"quantity", "quantity_purchased", "starting_warehouse_balance",
"in_transit_between_warehouses", "receipts", "customer_shipments",
"customer_returns", "vendor_returns", "warehouse_transfer_in",
"warehouse_transfer_out", "found", "lost", "damaged", "disposed",
"other_events", "ending_warehouse_balance", "unknown_events"}
def _label(col):
return connector_spec._label(col)
def _ftype(col, duck_type):
"""The grid field type for one mirror column. Name first, storage second.
β NAME BEATS STORAGE FOR DATES ON PURPOSE. Every flat-file column arrives as text, so a
purchase date is a VARCHAR in the mirror and would render as a string in the grid unless the
name is allowed to override. The store's `_coltype` already refuses to guess a type for text
it cannot parse; this is the presentation half of the same judgement.
"""
if col == "per_unit_volume":
return "int"
if any(col == s or col.endswith(s) for s in _DATEISH):
return "date"
if _is_percent(col):
return "pct"
if _is_money(col) or col in _NUMBER:
return "currency"
base = str(duck_type or "").upper().split("(")[0]
if base in ("DOUBLE", "FLOAT", "DECIMAL"):
return "currency"
if col in _QUANTITY or base in ("BIGINT", "INTEGER", "HUGEINT"):
return "int"
return "text"
def _format(col, duck_type):
"""The display format a field ships with, or None for the grid's default."""
kind = _ftype(col, duck_type)
if col == "per_unit_volume":
return {"decimals": 4}
if kind == "pct":
return {"decimals": 2}
if kind == "currency" and not _is_money(col):
return {"currencySymbol": "", "decimals": 2}
return None
def mirror_columns(cur, table, *, strict=False):
"""[(name, type)] for a mirror table, or [] when it has never been synced."""
if strict:
# A successful empty metadata query proves absence on this cursor only.
return [(name, kind) for name, kind in cur.execute(
"SELECT column_name, data_type FROM information_schema.columns "
"WHERE table_catalog = current_database() AND table_schema = current_schema() "
"AND table_name = ? ORDER BY ordinal_position", [table]).fetchall()
if name not in amazon_schema.MIRROR_INTERNAL_COLUMNS]
try:
return [(r[1], r[2]) for r in cur.execute(f"PRAGMA table_info('{table}')").fetchall()
if r[1] not in amazon_schema.MIRROR_INTERNAL_COLUMNS]
except Exception: # noqa: BLE001
return []
#: ββ S67-T30 (instr 13, R29 / O23) β THE PRODUCT LOOKUPS. Every Amazon database with an ASIN
#: shows its "Parent ASIN" and "SKU" beside it, and every one with a SKU its "ASIN" and "Parent
#: ASIN", with no configuration. They are PLAIN TEXT fields whose value the SERVER derives, not
#: link fields, on purpose: a link renders as a count bubble, is not groupable
#: (`types.ts:isGroupableField`) and has no SQL binding for a filter (`routes_odoo_tables.
#: _filter_columns` and `_group_pin` read `spec["cols"]` only). A text field with a `cols`
#: expression groups, filters and sorts through the machinery every other column already uses.
#: β The ASIN / SKU column of a table is the `from_col` of its declared listings link (on `asin1`
#: / `seller_sku`), so the spec's links are the one statement of which column is which. A table
#: that carries its own `parent_asin` (pricing, economics) gains no derived Parent ASIN; one that
#: carries its own SKU or ASIN gains no duplicate of it; listings gains Parent ASIN only.
#: β Parent comes from `amazon_store.asin_parents_sql` ALONE (latest catalog, else economics, else
#: the ASIN itself), never from `amzn_catalog_items` directly: that table keeps older snapshot days
#: (D-3031) and five own ASINs have no catalog row. SKU and ASIN come from the LATEST listings day
#: only, because listings keeps one copy per snapshot date.
LISTINGS = PREFIX + "listings"
LOOKUP_PARENT, LOOKUP_SKU, LOOKUP_ASIN = "lookup_parent_asin", "lookup_sku", "lookup_asin"
LOOKUP_KEYS = frozenset((LOOKUP_PARENT, LOOKUP_SKU, LOOKUP_ASIN))
#: Native columns that already show a row's ASIN (orders, order items, replenishment carry `asin`).
_NATIVE_ASIN = frozenset(("asin", "asin1", "child_asin"))
_LOOKUP_LABELS = {LOOKUP_PARENT: "Parent ASIN", LOOKUP_SKU: "SKU", LOOKUP_ASIN: "ASIN"}
_LOOKUP_DESCRIPTIONS = {
LOOKUP_PARENT: ("The parent ASIN of this row's product, from Amazon's catalog, else the "
"economics report, else the ASIN itself. Click it to open the listing."),
LOOKUP_SKU: ("Your seller SKUs for this row's ASIN in the latest listings report. "
"Click it to open the listing."),
LOOKUP_ASIN: ("The ASIN of this row's SKU in the latest listings report. "
"Click it to open the listing."),
}
def _product_columns(key):
"""`(asin_col, sku_col, asin_link_key, sku_link_key)` as the spec declares them for `key`."""
if key == LISTINGS:
return "asin1", "seller_sku", None, None # the row IS the listing: Parent ASIN only
asin = sku = asin_via = sku_via = None
for edge in connector_spec.edges_for(key):
if edge["target"] != LISTINGS:
continue
if edge["to_col"] == "asin1" and asin is None:
asin, asin_via = edge["from_col"], edge["key"]
elif edge["to_col"] == "seller_sku" and sku is None:
sku, sku_via = edge["from_col"], edge["key"]
return asin, sku, asin_via, sku_via
def _latest_listings_sql(listing_names):
"""`SELECT sku, asin` over the LATEST listings snapshot day, or None without both columns."""
if not {"seller_sku", "asin1"} <= set(listing_names):
return None
table = SOURCE[LISTINGS]
if amazon_schema.CONNECTION_ID in listing_names:
latest = (', max("_snapshot_date") OVER (PARTITION BY "_connection_id") AS latest_date'
if "_snapshot_date" in listing_names else "")
date = ', "_snapshot_date"' if latest else ""
predicate = ' AND "_snapshot_date" IS NOT DISTINCT FROM latest_date' if latest else ""
return ('SELECT DISTINCT "_connection_id", sku, asin FROM (SELECT "_connection_id", '
'TRIM(CAST(seller_sku AS VARCHAR)) AS sku, TRIM(CAST(asin1 AS VARCHAR)) AS asin'
f'{date}{latest} FROM {table}) WHERE sku <> \'\' AND asin <> \'\'{predicate}')
latest = (f' AND "_snapshot_date" = (SELECT MAX("_snapshot_date") FROM {table})'
if "_snapshot_date" in listing_names else "")
return ("SELECT DISTINCT sku, asin FROM (SELECT TRIM(CAST(seller_sku AS VARCHAR)) AS sku, "
f"TRIM(CAST(asin1 AS VARCHAR)) AS asin FROM {table} "
f"WHERE seller_sku IS NOT NULL AND asin1 IS NOT NULL{latest}) "
"WHERE sku <> '' AND asin <> ''")
# One current resolution per tenant mirror, with a small process-wide tenant budget.
_PARENT_CACHE = OrderedDict()
_PARENT_CACHE_MAX = 8
_PARENT_CACHE_LOCK = RLock()
_PARENT_SOURCES = ("listings", "catalog_items", "pricing", "economics")
def _parent_revision(cur):
"""Tenant mirror identity and the resolver's source sync markers, or no safe memo key.
The cursor's actual file identifies the tenant, never the mutable global DB_PATH.
Unnamed in-memory mirrors and mirrors without sync bookkeeping stay uncached.
"""
try:
database = cur.execute("SELECT current_database()").fetchone()[0]
path = next((row[2] for row in cur.execute("PRAGMA database_list").fetchall()
if row[1] == database and row[2]), None)
if not path:
return None
entities = [amazon_store.entity_key(prefix + source)
for source in _PARENT_SOURCES
for prefix in ("", "sync_tick_", amazon_store.WATERMARK_PREFIX)]
entities.extend(amazon_store.ENTITY_STATE_KEYS[source] for source in _PARENT_SOURCES
if source in amazon_store.ENTITY_STATE_KEYS)
revision = tuple(cur.execute(
"SELECT entity, phase, cursor_wd, rows, updated_at FROM _sync_state "
f"WHERE entity IN ({','.join('?' for _ in entities)}) ORDER BY entity",
entities).fetchall())
return (str(Path(path).resolve()), revision) if revision else None
except Exception: # noqa: BLE001
return None
def _parent_lookup_sql(cur):
"""Resolve parents once per source revision; pages join the resolved data, not its ranks.
JSON is a read-only SQL relation here, with SQL quotes escaped separately from JSON.
No temporary table or tenant mirror write is needed, and the complete mapping is retained.
"""
sources = {"amzn_" + source: amazon_store._columns_of(cur, "amzn_" + source)
for source in _PARENT_SOURCES}
scoped = [table for table, names in sources.items() if amazon_schema.CONNECTION_ID in names]
if scoped:
base = amazon_store.asin_parents_sql(cur)
if base is None:
return None
ids = cur.execute(" UNION ".join(f'SELECT DISTINCT "_connection_id" FROM {table}'
for table in scoped)).fetchall()
parts = []
for (cid,) in ids:
literal = "NULL::VARCHAR" if cid is None else "'" + str(cid).replace("'", "''") + "'"
ctes = []
for table, names in sources.items():
if not names:
continue
where = (f'"_connection_id" IS NOT DISTINCT FROM {literal}'
if amazon_schema.CONNECTION_ID in names else "FALSE")
ctes.append(f'{table} AS (SELECT * FROM main.{table} WHERE {where})')
parts.append(f'(WITH {", ".join(ctes)} SELECT {literal} AS "_connection_id", p.* FROM ({base}) p)')
return " UNION ALL ".join(parts) if parts else None
identity = _parent_revision(cur)
if identity is None:
return amazon_store.asin_parents_sql(cur)
tenant, revision = identity
with _PARENT_CACHE_LOCK:
cached = _PARENT_CACHE.get(tenant)
if cached is not None and cached[0] == revision:
_PARENT_CACHE.move_to_end(tenant)
return cached[1]
sql = amazon_store.asin_parents_sql(cur)
resolved = None
if sql is not None:
parents = {asin: parent for asin, parent, _source in cur.execute(sql).fetchall()}
payload = json.dumps(parents, ensure_ascii=True, separators=(",", ":"))
resolved = ("SELECT key AS asin, json_extract_string(value, '$') AS parent_asin "
f"FROM json_each('{payload.replace(chr(39), chr(39) * 2)}')")
# Never publish a resolution if a sync advanced while it was being built.
if _parent_revision(cur) == identity:
_PARENT_CACHE[tenant] = (revision, resolved)
_PARENT_CACHE.move_to_end(tenant)
while len(_PARENT_CACHE) > _PARENT_CACHE_MAX:
_PARENT_CACHE.popitem(last=False)
return resolved
def lookups(cur, key, names=None, *, allowed=True):
"""`[{key, label, after, expr, opens, description}]`: the product lookups `key` gains.
`expr` is ONE correlated scalar subquery per lookup, qualified by the mirror table's own name
(every reader selects `FROM <table>` unaliased), so it binds to the outer row and never to the
resolver's own `asin`. DuckDB decorrelates it into a single hash join per query, which is what
keeps a grouped page of a read-through grid set-based rather than per-row. No match is NULL,
which every reader serialises as a blank cell. Nothing is capped (standing rule 1).
"""
if names is None:
names = {name for name, _dtype in mirror_columns(cur, SOURCE[key])}
asin, sku, asin_via, sku_via = _product_columns(key)
asin = asin if asin in names else None
sku = sku if sku in names else None
if not asin and not sku:
return []
if not allowed:
# Keep declared cells blank without inspecting a denied lookup dependency.
keys = []
if asin:
if "parent_asin" not in names:
keys.append((LOOKUP_PARENT, asin, asin_via))
if not sku:
keys.append((LOOKUP_SKU, asin, asin_via))
elif sku:
if not names & _NATIVE_ASIN:
keys.append((LOOKUP_ASIN, sku, sku_via))
if "parent_asin" not in names:
keys.append((LOOKUP_PARENT, sku, sku_via))
return [{"key": name, "label": _LOOKUP_LABELS[name], "after": after,
"expr": "CAST(NULL AS VARCHAR)", "opens": {"via": via} if via else None,
"description": _LOOKUP_DESCRIPTIONS[name]}
for name, after, via in keys if name not in names]
listing_names = amazon_store._columns_of(cur, SOURCE[LISTINGS])
parents = None if "parent_asin" in names else _parent_lookup_sql(cur)
latest = _latest_listings_sql(listing_names)
table = SOURCE[key]
out = []
scoped = amazon_schema.CONNECTION_ID in amazon_store._columns_of(cur, table)
parent_scoped = any(amazon_schema.CONNECTION_ID in amazon_store._columns_of(cur, "amzn_" + source)
for source in _PARENT_SOURCES)
def account_match(alias):
tagged = parent_scoped if alias == "_p" else amazon_schema.CONNECTION_ID in listing_names
if scoped and tagged:
return f' AND {alias}."_connection_id" IS NOT DISTINCT FROM {table}."_connection_id"'
if scoped:
return " AND FALSE"
return f' AND {alias}."_connection_id" IS NULL' if tagged else ""
def add(lookup_key, after, expr, via):
if lookup_key in names:
return
out.append({"key": lookup_key, "label": _LOOKUP_LABELS[lookup_key], "after": after,
"expr": expr, "opens": {"via": via} if via else None,
"description": _LOOKUP_DESCRIPTIONS[lookup_key]})
if asin:
ref = f"TRIM(CAST({table}.{_quoted(asin)} AS VARCHAR))"
via = asin_via if "asin1" in listing_names else None
if parents:
add(LOOKUP_PARENT, asin,
f"(SELECT _p.parent_asin FROM ({parents}) AS _p WHERE _p.asin = {ref}{account_match('_p')})", via)
if latest and not sku:
add(LOOKUP_SKU, asin,
f"(SELECT string_agg(DISTINCT _l.sku, ', ' ORDER BY _l.sku) "
f"FROM ({latest}) AS _l WHERE _l.asin = {ref}{account_match('_l')})", via)
elif sku and latest:
ref = f"TRIM(CAST({table}.{_quoted(sku)} AS VARCHAR))"
via = sku_via if "seller_sku" in listing_names else None
# S78-T01: a table that carries its own ASIN column gains no second "ASIN".
if not names & _NATIVE_ASIN:
add(LOOKUP_ASIN, sku,
f"(SELECT string_agg(DISTINCT _l.asin, ', ' ORDER BY _l.asin) "
f"FROM ({latest}) AS _l WHERE _l.sku = {ref}{account_match('_l')})", via)
if parents:
add(LOOKUP_PARENT, sku,
f"(SELECT string_agg(DISTINCT _p.parent_asin, ', ' ORDER BY _p.parent_asin) "
f"FROM ({latest}) AS _l JOIN ({parents}) AS _p ON _p.asin = _l.asin "
+ ('AND _p."_connection_id" IS NOT DISTINCT FROM _l."_connection_id" '
if parent_scoped and amazon_schema.CONNECTION_ID in listing_names else "")
+ f"WHERE _l.sku = {ref}{account_match('_l')})", via)
return out
def _fields_for(cur, key, *, read_through=False):
"""Derive source fields and include the synthetic row stamp only for materialized definitions."""
cols = mirror_columns(cur, SOURCE[key])
if not cols:
return []
declared = {field["name"]: field for field in connector_spec.AMAZON_FIELDS.get(key, ())}
written = connector_spec.AMAZON_LABELS.get(key, {})
shown = default_columns(key)
names = {c[0] for c in cols}
pinned = PINNED.get(key) if PINNED.get(key) in names else None
# S67-T30: each lookup lands IMMEDIATELY after the column it derives from, shown by default,
# and the ASIN / SKU column it sits beside is shown by default too (inventory's `msku` was not).
derived = {}
for lookup in lookups(cur, key, names):
derived.setdefault(lookup["after"], []).append(lookup)
product = {c for c in _product_columns(key)[:2] if c in names}
out = []
# The identity column goes FIRST, the rest keep the report's own order. A record reached
# through a link is titled by its first field (the record drawer's frame label), and a
# report's first column is whatever Amazon put there (`item_name`, `_data_origin`), so a
# listing opened from an order must lead with its seller SKU, as Odoo's pinned fields do.
for name, dtype in sorted(cols, key=lambda col: col[0] != pinned):
fmt = _format(name, dtype)
out.append(_odoo._preset({
"key": name,
"label": declared.get(name, {}).get("label") or written.get(name) or _label(name),
"type": _ftype(name, dtype),
"source": "overlay",
"default": name == pinned or name in shown or name in product or name == amazon_schema.STORE,
"pinned": name == pinned,
**({"description": amazon_schema.REVIEW_UNAVAILABLE_NOTE}
if name == "asin" and key in (PREFIX + "review_topics", PREFIX + "review_trends") else {}),
**({"description": amazon_schema.SQP_LIMIT_NOTE}
if name == "asin" and key == PREFIX + "search_query_performance" else {}),
**({"format": fmt} if fmt else {}),
}, flow="amazon_relational"))
for lookup in derived.get(name, ()):
field = {"key": lookup["key"], "label": lookup["label"], "type": "text",
"source": "overlay", "default": True,
"description": lookup["description"]}
if lookup["opens"]:
field["opens"] = dict(lookup["opens"])
out.append(_odoo._preset(field, flow="amazon_relational"))
live_columns = {key: names}
for edge in connector_spec.edges_for(key):
target = edge["target"]
if target not in live_columns:
columns = mirror_columns(cur, SOURCE.get(target, ""))
if columns:
live_columns[target] = {name for name, _dtype in columns}
out.extend(_odoo._preset(field, flow="amazon_relational")
for field in connector_spec.system_link_fields(key, live_columns).get(key, ()))
if amazon_schema.STORE in names:
excess = max(0, sum(bool(field.get("default")) for field in out) - 12)
for field in reversed(out):
if excess and field.get("default") and field["key"] not in {
pinned, amazon_schema.STORE, *product, *LOOKUP_KEYS, *_HISTORY_SHOWN}:
field["default"] = False
excess -= 1
if not read_through and "refreshed" not in names:
out.append(_odoo._preset(_odoo._refreshed_field(), flow="amazon_relational"))
return out
def default_columns(key):
"""The columns `key` opens on beyond the pinned, product, lookup and link fields."""
declared = connector_spec.AMAZON_DEFAULT_COLUMNS.get(key)
if declared is None and key.removeprefix(PREFIX) in amazon_store.HISTORY_TABLES:
return _UNDECLARED_DEFAULT_COLUMNS | _HISTORY_SHOWN
return frozenset(declared) if declared is not None else _UNDECLARED_DEFAULT_COLUMNS
#: A history table always opens on the dates each version held.
_HISTORY_SHOWN = frozenset(("valid_from", "valid_to"))
#: The shared fallback for a table `connector_spec.AMAZON_DEFAULT_COLUMNS` does not declare yet
#: (one the staging mirror has never synced). A declared table reads only its own list.
_UNDECLARED_DEFAULT_COLUMNS = frozenset({
"amazon_order_id", "purchase_date", "order_status", "sku", "seller_sku", "asin", "asin1",
"quantity", "quantity_purchased", "item_price", "item_status", "product_name", "item_name",
"settlement_id", "amount", "amount_type", "amount_description", "posted_date",
"parent_asin", "child_asin", "search_term", "search_frequency_rank",
"traffic_by_asin_buy_box_percentage", "sales_by_asin_units_ordered",
"backend_keywords",
"month", "search_query", "impressions", "clicks", "cart_adds", "purchases", "source_basis",
"keyword", "date", "organic_rank", "sponsored_rank", "sponsored_rank_basis",
})
#: Every name any Amazon table may open on (`verify_agent_knowledge` reads it by name).
_DEFAULT_COLUMNS = _UNDECLARED_DEFAULT_COLUMNS.union(*connector_spec.AMAZON_DEFAULT_COLUMNS.values())
#: The original seven used first-field identities. Retained for legacy-id migration
#: and for distinguishing history from the original small snapshots.
_LEGACY_IDENTITY_TABLES = frozenset(("orders", "order_items", "listings", "settlements",
"inventory", "pricing", "brand_analytics"))
# The original seven do not declare snapshot/window metadata. Keep their four small
# tables materialised; every newer non-snapshot report/API is history, including monthly ones.
READ_THROUGH = frozenset(
PREFIX + table for table, spec in amazon_client.REPORT_TYPES.items()
if table in {"settlements", "orders", "order_items", "transactions", "economics", "storage_fees"}
or (table not in _LEGACY_IDENTITY_TABLES and not spec.get("snapshot")
and (spec.get("report") or spec.get("api") or spec.get("monthly")))) | frozenset(
PREFIX + table for table in amazon_store.DERIVED_TABLES) | frozenset(
# Change history grows without a cap (standing rule 1), so it is never stored.
PREFIX + table for table in amazon_store.HISTORY_TABLES) | frozenset((
PREFIX + "competitor_snapshots", PREFIX + "competitor_images"))
def _quoted(name):
return '"' + name.replace('"', '""') + '"'
#: ββ HOTFIX 2026-09-27 (D-3284's cost): an Amazon snapshot table larger than this is served
#: READ-THROUGH, never stored in the tenant document. The per-table `MAX_TABLE_BYTES` (48 MB) let
#: catalog items (32.9 MB for 2,317 rows), pricing (11.7 MB) and FBA inventory (7.5 MB) in once
#: connector databases stopped counting toward MAX_TABLES, and `user_tables` doubled to 118 MB.
#: Every whole-document read and write doubled with it: the 2 GB DigitalOcean staging node ran
#: out of memory and PostgreSQL restarted (2026-09-27 16:36Z), and on Neon each read is billed at
#: the document's text size. Small tables stay stored; nothing is capped (standing rule 1).
AMAZON_STORED_BYTES = 4 * 1024 * 1024
def _read_through(cur, key, count, *, columns=None):
"""Choose storage before reading rows; estimate wide snapshots with a bounded sample."""
ut = _odoo._ut()
if key in READ_THROUGH or count > ut.MAX_ROWS:
return True
if not count:
return key == PREFIX + "inventory"
names = [name for name, _dtype in (columns if columns is not None
else mirror_columns(cur, SOURCE[key]))]
sample = cur.execute(f"SELECT * FROM {SOURCE[key]} LIMIT 32").fetchall()
# Include the stored id, refreshed stamp and row-object punctuation, with the same
# ASCII escaping as the document writer. The largest sampled row is conservative.
size = max((len(json.dumps(dict(zip(names, row)), ensure_ascii=True,
default=str).encode("utf-8")) + 96 for row in sample), default=0)
return count * size > min(ut.MAX_TABLE_BYTES, AMAZON_STORED_BYTES)
#: Legacy resident-loop diagnostic. Tenant readiness is read through restore_pending.
RESTORE_PENDING = {"since": None}
def restore_pending(rt=None):
"""Read the Amazon portion of the tenant's shared catch-up state."""
from harness import mirror_store
try:
if rt is None:
from harness import runtime
tenant = runtime.tenant_for_store_path()
if not tenant:
return True
rt = runtime.get_runtime(tenant)
sources = mirror_store.connected_sources(rt)
return "amazon" in sources and mirror_store.source_catching_up(rt, "amazon")
except Exception:
return True
_UNRESOLVED = object()
def source_status(cur, key):
"""D-2643: `{empty, code, reason}` from `amazon_store.table_empty_status` for a present table.
An empty table always answers. A table with rows answers only beside a recorded Amazon
refusal, with `empty` False. None otherwise, and for keys outside the declared databases.
"""
table = key.removeprefix(PREFIX)
if key not in SOURCE or table not in amazon_store.DATABASE_TABLES:
return None
present = cur.execute(f"SELECT 1 FROM {_quoted(SOURCE[key])} LIMIT 1").fetchone() is not None
# `rows=1` marks a table that holds rows. The producer then answers a refusal only.
status = amazon_store.table_empty_status(cur, table, rows=int(present))
if not status.get("code") or (present and status["code"] != "refused_by_amazon"):
return None
return {"empty": not present, "code": status["code"], "reason": status.get("reason") or ""}
def missing_source(key, rt=None, *, pending=None, cur=None):
"""The ONE sentence every door gives for an Amazon table this server's mirror does not hold yet.
`pending` is a `restore_pending()` answer the caller already holds, so a listing reads the keychain once.
`cur` is the admitted mirror cursor. With it, a saved refusal or a measured empty report names
its own reason instead of the loading sentence.
"""
table = key.removeprefix(PREFIX)
# Schema-only probe first: a mirror without sync bookkeeping keeps the loading sentences.
if (cur is not None and key != PREFIX + "inventory"
and table in amazon_store.DATABASE_TABLES and mirror_columns(cur, "_sync_state")):
status = amazon_store.table_empty_status(cur, table, rows=None)
if (status.get("code") and status.get("reason")
and status["reason"] != amazon_store.loading_reason(table)):
return {"missing": status["reason"],
"sourceStatus": {"empty": True, "code": status["code"], "reason": status["reason"]}}
if key == PREFIX + "inventory":
if rt is None:
from harness import runtime
tenant = runtime.tenant_for_store_path()
rt = runtime.get_runtime(tenant) if tenant else None
definitions = rt.get_projection("user_tables", drop=("rows",)) if rt is not None else {}
status = ((definitions or {}).get(key) or {}).get("sourceStatus") or {}
if status.get("empty") is True and isinstance(status.get("reason"), str) and status["reason"].strip():
return {"missing": status["reason"]}
return {"missing": "Inventory request metadata is unavailable, "
"so retry Amazon sync to record the FBA ledger request."}
label = key.removeprefix(PREFIX).replace("_", " ")
if restore_pending(rt) if pending is None else pending:
return {"missing": f"Amazon {label} is still loading after a server restart, "
"so ask again in 5 minutes."}
return {"missing": f"Amazon {label} is still loading from Amazon, "
"so ask again after the next Amazon sync finishes."}
def _text(value):
return "" if value is None else str(value)
def _number(value):
return None if value in (None, "") else float(value)
def _integer(value):
return None if value in (None, "") else int(value)
def grid_source(cur, key, *, columns=None, allow_lookups=True, status=_UNRESOLVED):
"""One binding from the current mirror schema, or an explicit unloaded-mirror reason.
`status` is a `source_status` answer the caller already holds.
"""
columns = mirror_columns(cur, SOURCE[key]) if columns is None else columns
columns = [(name, kind) for name, kind in columns if name not in amazon_schema.MIRROR_INTERNAL_COLUMNS]
if not columns:
return missing_source(key, cur=cur)
names = {name for name, _dtype in columns}
table = key.removeprefix(PREFIX)
prepared = {row[0] for row in cur.execute(
"SELECT column_name FROM information_schema.columns WHERE table_catalog = current_database() "
"AND table_schema = current_schema() AND table_name = ? AND column_name IN (?, ?, ?)",
[SOURCE[key], amazon_schema.IDENTITY_OCCURRENCE, amazon_schema.IDENTITY_PID,
amazon_schema.CONNECTION_ID]).fetchall()}
identity_names = names | prepared
identity = _identity_columns(table, identity_names)
fallback = sorted(set((*identity, *_fallback_columns(table))))
source = _quoted(SOURCE[key])
def value(name, owner=source):
return (f"NULLIF(CAST({owner}.{_quoted(name)} AS VARCHAR), '')"
if name in identity_names else "NULL::VARCHAR")
def digest(values):
return f"sha256(to_json(list_value({', '.join(values)})))"
complete = " AND ".join(f"{value(name)} IS NOT NULL" for name in identity)
peer = '"_amazon_identity_peer"'
peer_complete = " AND ".join(f"{value(name, peer)} IS NOT NULL" for name in identity)
partition = ", ".join(value(name, peer) for name in fallback)
# Unprepared mirrors rank once in a materialized relation, outside the row lookup.
occurrence = (
'(WITH "_amazon_missing_identity" AS MATERIALIZED '
f'(SELECT {peer}.rowid AS "_identity_rowid", '
f'row_number() OVER (PARTITION BY {partition} ORDER BY {peer}.rowid) - 1 '
f'AS "_identity_occurrence" FROM {source} AS {peer} WHERE NOT ({peer_complete})) '
'SELECT CAST("_identity_occurrence" AS VARCHAR) FROM "_amazon_missing_identity" '
f'WHERE "_identity_rowid" = {source}.rowid)')
if amazon_schema.IDENTITY_OCCURRENCE in prepared:
occurrence = f'CAST({source}.{_quoted(amazon_schema.IDENTITY_OCCURRENCE)} AS VARCHAR)'
fallback_digest = digest(["'__amazon_missing_identity__'",
*(value(name) for name in fallback), occurrence])
digest_sql = (f"CASE WHEN {complete} THEN {digest([value(name) for name in identity])} "
f"ELSE {fallback_digest} END")
if table in amazon_store.DERIVED_TABLES:
# Derived views construct complete keys and have no physical rowid for fallback order.
digest_sql = digest([value(name) for name in identity])
# The shared grid, relation and selection contracts all use numeric pids. Thirteen
# hex digits retain a stable 52-bit identity that JavaScript represents exactly.
ident = f"greatest(1, CAST(('0x' || substr(({digest_sql}), 1, 13)) AS BIGINT))"
if amazon_schema.IDENTITY_PID in prepared:
ident = f'{source}.{_quoted(amazon_schema.IDENTITY_PID)}'
elif amazon_schema.CONNECTION_ID in prepared:
legacy_owner = amazon_store.legacy_identity_owner(cur, table)
if legacy_owner:
literal = "'" + str(legacy_owner).replace("'", "''") + "'"
old_identity = _identity_columns(table, identity_names - {amazon_schema.CONNECTION_ID})
old_fallback = sorted(set((*old_identity, *_fallback_columns(table))))
old_complete = " AND ".join(f"{value(name)} IS NOT NULL" for name in old_identity)
old_peer_complete = " AND ".join(f"{value(name, peer)} IS NOT NULL" for name in old_identity)
old_partition = ", ".join(value(name, peer) for name in old_fallback)
old_occurrence = (
'(WITH "_amazon_legacy_identity" AS MATERIALIZED '
f'(SELECT {peer}.rowid AS "_identity_rowid", '
f'row_number() OVER (PARTITION BY {old_partition} ORDER BY {peer}.rowid) - 1 '
f'AS "_identity_occurrence" FROM {source} AS {peer} '
f'WHERE {peer}."_connection_id"={literal} AND NOT ({old_peer_complete})) '
'SELECT CAST("_identity_occurrence" AS VARCHAR) FROM "_amazon_legacy_identity" '
f'WHERE "_identity_rowid" = {source}.rowid)')
old_missing = digest(["'__amazon_missing_identity__'", *(value(name) for name in old_fallback),
old_occurrence])
old_digest = (f"CASE WHEN {old_complete} THEN {digest([value(name) for name in old_identity])} "
f"ELSE {old_missing} END")
old_ident = f"greatest(1, CAST(('0x' || substr(({old_digest}), 1, 13)) AS BIGINT))"
ident = f'CASE WHEN {source}."_connection_id"={literal} THEN {old_ident} ELSE {ident} END'
cols = {}
personal = set(amazon_store.personal_columns(table))
for name, dtype in columns:
if name in personal:
cols[name] = (_quoted(name), _text)
continue
kind = _ftype(name, dtype)
expr = _quoted(name)
fractional = kind in ("currency", "pct") or name == "per_unit_volume"
if kind in ("currency", "pct", "int"):
expr = f"TRY_CAST({expr} AS {'DOUBLE' if fractional else 'BIGINT'})"
if kind == "pct" and _is_fraction_percent(name):
expr = f"ROUND({expr} * 100, 8)" # 8 places drops float noise, keeps every digit
cols[name] = (expr, _number if fractional else {"int": _integer}.get(kind, _text))
# S67-T30: the product lookups bind like any mirror column, so a filter (`=` a parent), a
# group (count by parent) and a sort compile against them in SQL with no per-row Python.
for lookup in lookups(cur, key, names, allowed=allow_lookups):
cols[lookup["key"]] = (lookup["expr"], _text)
spec = {"table": SOURCE[key], "where": "", "id": ident,
"needs_excluded": False, "cols": cols}
if table not in amazon_store.DERIVED_TABLES:
spec["page_key"] = f"{source}.rowid"
# D-2643: an empty table carries its code and reason to every door that reads this binding.
status = source_status(cur, key) if status is _UNRESOLVED else status
if status is not None:
spec["sourceStatus"] = status
return spec
def page_source(cur, key, definition=None, *, allow_lookups=True):
"""Resolve one target's storage policy on the admitted cursor without provider discovery."""
columns = mirror_columns(cur, SOURCE[key], strict=True)
mandatory = key in READ_THROUGH or bool((definition or {}).get("readThrough"))
if not columns:
return missing_source(key, cur=cur) if mandatory else None
status = _UNRESOLVED
if not mandatory:
count = cur.execute(f"SELECT count(*) FROM {SOURCE[key]}").fetchone()[0]
# D-2643: an empty table, or one beside a recorded refusal, reads through, so its
# binding names its reason in the Data grid.
if count and not _read_through(cur, key, count, columns=columns):
status = source_status(cur, key)
if status is None:
return None
return grid_source(cur, key, columns=columns, allow_lookups=allow_lookups, status=status)
def grid_sources(cur=None):
"""Only Amazon read-through grids; no keys at all on an Odoo-only mirror."""
own_cursor = cur is None
cur = datastore.ro_cursor() if own_cursor else cur
try:
present = {key for key, table in SOURCE.items() if mirror_columns(cur, table)}
if not present:
return {}
keys = set(READ_THROUGH)
for key in present - keys:
count = cur.execute(f"SELECT count(*) FROM {SOURCE[key]}").fetchone()[0]
# D-2643: an empty or refused table registers too, so the grid opens it on the
# read-through door that carries its reason.
if _read_through(cur, key, count) or source_status(cur, key) is not None:
keys.add(key)
return {key: grid_source(cur, key) for key in SOURCE if key in keys}
finally:
if own_cursor:
cur.close()
def _row_id(row, key, occurrence=0):
"""Return a legacy row key from declared identity, lineage and equal-key occurrence."""
table = key.removeprefix(PREFIX)
columns = _identity_columns(table, row)
identity = tuple(row.get(name) for name in columns)
if all(value not in (None, "") for value in identity):
if table in _LEGACY_IDENTITY_TABLES and len(identity) == 1:
return str(identity[0])
payload = json.dumps(identity, ensure_ascii=True, default=str)
else:
fallback = sorted(set((*columns, *_fallback_columns(table))))
values = tuple(None if row.get(name) in (None, "") else str(row[name])
for name in fallback)
payload = json.dumps(("__amazon_missing_identity__", *values, str(occurrence)),
ensure_ascii=True, default=str)
return hashlib.sha256(payload.encode("utf-8")).hexdigest()[:32]
def _read(cur, key):
"""Small snapshots use the same numeric identity as their read-through grid.
A SKU or hex digest used as the stored row key is silently skipped by
perm_scope._read_user_table, and cannot be opened by the record drawer.
"""
cols = [c[0] for c in mirror_columns(cur, SOURCE[key])]
if not cols:
return []
spec = grid_source(cur, key)
# Stored snapshots and mirror windows share their numeric casts. Empty report cells
# become null; decimals remain numbers instead of text that defeats aggregation.
quoted = ", ".join(spec["cols"][c][0] for c in cols)
personal = set(amazon_store.personal_columns(key))
numeric = {name for name, dtype in mirror_columns(cur, SOURCE[key])
if name not in personal and _ftype(name, dtype) in ("currency", "pct", "int")}
# S67-T30: a stored snapshot carries the same product lookups, computed by the SAME SQL
# expressions the read-through binding uses, in this one SELECT (never per row).
derived = [(lookup["key"], lookup["expr"]) for lookup in lookups(cur, key)]
extra = "".join(f", {expr}" for _k, expr in derived)
owner_columns = ([amazon_schema.CONNECTION_ID]
if amazon_schema.CONNECTION_ID in amazon_store._columns_of(cur, SOURCE[key]) else [])
extra += "".join(f", {_quoted(name)}" for name in owner_columns)
rows = cur.execute(f'SELECT {spec["id"]}, {quoted}{extra} FROM {SOURCE[key]} '
f'ORDER BY {_quoted(SOURCE[key])}.rowid').fetchall()
names = cols + [k for k, _expr in derived] + owner_columns
out = []
for r in rows:
row = {}
for name, val in zip(names, r[1:]):
if name in numeric:
row[name] = spec["cols"][name][1](val)
continue
# Postgres JSON cannot hold U+0000, and one such byte refused the whole Amazon write.
row[name] = "" if val is None else (val if isinstance(val, (int, float))
else str(val).replace("\x00", ""))
row["_id"] = str(r[0])
out.append(row)
def read_targets(target, matches):
target_spec = grid_source(cur, target)
columns = sorted(matches)
clauses, params = [], []
for column in columns:
values = sorted(matches[column])
if values:
clauses.append(f'CAST({_quoted(column)} AS VARCHAR) IN '
f'({",".join("?" for _ in values)})')
params.extend(values)
if amazon_schema.CONNECTION_ID in amazon_store._columns_of(cur, SOURCE[target]):
columns.append(amazon_schema.CONNECTION_ID)
projection = ", ".join(_quoted(column) for column in columns)
found = cur.execute(
f'SELECT {target_spec["id"]}, {projection} FROM {SOURCE[target]} '
f'WHERE {" OR ".join(clauses) or "FALSE"}', params).fetchall()
return [dict(zip(["pid", *columns], row)) for row in found]
fill_link_cells(out, _fields_for(cur, key), read_targets)
return out
def link_edges(fields):
"""`[(field, target, from, on)]` for every derived Amazon link `fill_link_cells` fills.
Shared by the page filler and scoped reader; manual/inverse/through edges stay separate."""
out = []
for field in fields:
bag = field.get("link") or {}
target, frm, on = bag.get("table"), bag.get("from"), bag.get("on")
if (field.get("type") != "link" or target not in SOURCE or not frm or not on
or bag.get("inverse") or bag.get("through") or bag.get("selectionMode") == "manual"):
continue
out.append((field, target, frm, on))
return out
def wall_link_expression(source, predicate, identity):
"""The pre-window SQL form of fill_link_cells' sorted, distinct numeric pids.
The caller supplies a tenant-bound target source and its permission predicate.
An unreadable target is denied by the caller's answerability guard, never supplied
as an empty set that an `isEmpty` wall could mistake for evidence.
"""
return (f"(SELECT string_agg(CAST(_wall_link_pid AS VARCHAR), ',' ORDER BY _wall_link_pid) "
f"FROM (SELECT DISTINCT {identity} AS _wall_link_pid FROM {source} "
f"WHERE {predicate}) AS _wall_links)")
def fill_link_cells(rows, fields, read_targets, *, max_bytes=None, capacity_refusal=None):
"""Populate derived Amazon links with one read per distinct target on this page.
The snapshot planner reads its tenant mirror; the HTTP reader supplies a scoped
target reader taking `(target, {column: set of keys})`. OR the column predicates
inside the target's ordinary row wall, then fan out by each edge's own column.
Neither path stores history rows or caps the matching target set.
"""
edges = link_edges(fields)
scoped = {target for field, target, _frm, _on in edges
if (field.get("link") or {}).get("connectionScope") == amazon_schema.CONNECTION_ID}
def match_key(target, row, value):
if target not in scoped:
return value
cid = row.get(amazon_schema.CONNECTION_ID)
return (str(cid), value) if cid not in (None, "") else None
requests, weights = {}, {}
for _field, target, frm, on in edges:
values = {str(row[frm]) for row in rows if row.get(frm) not in (None, "")}
requests.setdefault(target, {}).setdefault(on, set()).update(values)
for row in rows:
if row.get(frm) not in (None, ""):
slot = (target, on, match_key(target, row, str(row[frm])))
weights[slot] = weights.get(slot, 0) + 1
indexes = {}
remaining = max_bytes
for target, matches in requests.items():
indexes[target] = {column: {} for column in matches}
iterator = iter(read_targets(target, matches) if any(matches.values()) else ())
try:
for row in iterator:
pid = int(row["pid"])
for column, wanted in matches.items():
value = str(row[column]) if row.get(column) not in (None, "") else None
if value not in wanted:
continue
matched = match_key(target, row, value)
if matched is None or not weights.get((target, column, matched)):
continue
bucket = indexes[target][column].get(matched)
if bucket is not None and pid in bucket:
continue
if remaining is not None:
# Charge every output occurrence before allocating its distinct ID.
cost = (len(str(pid)) + (1 if bucket else 2)) * weights.get((target, column, matched), 0)
if cost > remaining:
raise capacity_refusal()
remaining -= cost
if bucket is None:
bucket = indexes[target][column][matched] = set()
bucket.add(pid)
finally:
close = getattr(iterator, "close", None)
if close is not None:
close()
for field, target, frm, on in edges:
for row in rows:
row[field["key"]] = ",".join(str(pid) for pid in sorted(
indexes[target][on].get(match_key(target, row, str(row.get(frm))), ())))
def available(tenant_key="royal-imports"):
"""Has this tenant's mirror ever been fed by `amazon_store`? Cheap; never fetches."""
try:
counts = amazon_store.status(tenant_key)
except Exception: # noqa: BLE001
return False
return any(bool((v or {}).get("rows")) for v in counts.values())
def plan(cur, rt=None):
"""Rows for small snapshots, schema/count stubs for history or oversized tables."""
built = {"problems": []}
for key in SOURCE:
# β COUNT BEFORE READ. `_read` materialises the whole table as python dicts, so checking
# the cap afterwards means the one table too big to keep is the one built in memory first.
try:
n = cur.execute(f"SELECT count(*) FROM {SOURCE[key]}").fetchone()[0]
except Exception: # noqa: BLE001
continue # absent = never synced
if key == PREFIX + "inventory" and not n:
# A measured empty inventory report remains a declared database with a source reason.
status = amazon_store.inventory_table_status(cur)
built[key] = {"readThrough": True,
"fields": _fields_for(cur, key, read_through=True), "count": n,
"sourceStatus": {name: status[name] for name in ("empty", "code", "reason")}}
continue
if _read_through(cur, key, n):
built[key] = {"readThrough": True,
"fields": _fields_for(cur, key, read_through=True), "count": n}
continue
if not n:
# S78-T02: an emptied mirror table is an empty population, never a skip, or the stored
# definition keeps rows the source no longer holds. `apply_plan` creates no table for it.
built[key] = []
continue
rows = _read(cur, key)
if rows:
built[key] = rows
return built
def plan_fingerprint(cur, built):
"""Digest the exact derived Amazon content that would be published.
`amazon_store.sync()` counts fetched rows, not changed rows, so it is not equality evidence.
Row order out of DuckDB is arbitrary and therefore non-semantic, so rows are canonicalised
before hashing. Callers fail safe to a refresh if this raises.
"""
tables = []
for key in SOURCE:
if isinstance(built.get(key), dict) and built[key].get("readThrough"):
tables.append({"key": key, **built[key]})
continue
rows = built.get(key) or []
canonical = sorted(rows, key=lambda row: json.dumps(
row, sort_keys=True, ensure_ascii=True, separators=(",", ":"), default=str))
# S78-T02: an empty population differs from an absent table, so a stale copy is rewritten.
# D-2643: a changed empty reason is a changed publication too.
empty = ({"empty": True, "sourceStatus": source_status(cur, key)
if mirror_columns(cur, SOURCE[key]) else None} if built.get(key) == [] else {})
tables.append({"key": key, "fields": _fields_for(cur, key), "rows": canonical, **empty})
payload = json.dumps(tables, sort_keys=True, ensure_ascii=True,
separators=(",", ":"), default=str)
return hashlib.sha256(payload.encode("utf-8")).hexdigest()
def _place_lookups(stored, fields):
"""An EXISTING table's fields with each NEW product lookup placed right after its source.
`_ensure_table_inplace` only appends, so a lookup first planned for a table stored before
S67-T30 would land at the far end of the grid instead of beside the ASIN or SKU it derives
from. A lookup already stored keeps its place (a person may have moved it) and only has its
`opens` bag brought up to date, which `_ensure_table_inplace` does not forward-migrate.
"""
out = list(stored)
have = {f.get("key"): i for i, f in enumerate(out)}
anchor = None
for field in fields:
fkey = field.get("key")
if fkey not in LOOKUP_KEYS:
anchor = fkey
continue
if fkey in have:
old = out[have[fkey]]
if (old.get("automation") or {}).get("flowId") == "amazon_relational" \
and not _odoo._ut().user_edited(old):
if field.get("opens"):
old["opens"] = dict(field["opens"])
else:
old.pop("opens", None)
continue
keys = [f.get("key") for f in out]
if anchor not in keys:
continue # no source column stored yet: the append places it
at = keys.index(anchor) + 1
while at < len(keys) and keys[at] in LOOKUP_KEYS:
at += 1 # after any lookup already beside the same source
out.insert(at, dict(field))
have = {f.get("key"): i for i, f in enumerate(out)}
anchor = fkey
return out
def _lead_with_identity(stored, report, pinned):
"""An EXISTING table's fields with its identity column moved first, while the order is still
the report's own.
`_ensure_table_inplace` keeps a stored table's field order and only appends, so the
identity-first order `_fields_for` gives a NEW table never reaches one stored before it. The
move happens only when the stored mirror columns sit exactly in the report's order: any other
order is a person's arrangement, and theirs wins.
"""
keys = [f.get("key") for f in stored]
if not pinned or pinned not in keys or keys[0] == pinned:
return stored
in_report = set(report)
if [k for k in keys if k in in_report] != [k for k in report if k in set(keys)]:
return stored
return ([f for f in stored if f.get("key") == pinned]
+ [f for f in stored if f.get("key") != pinned])
def _listing_identity_rows(cur):
"""Capture connection/SKU targets and recoverable numeric IDs from earlier listing declarations."""
key = PREFIX + "listing_items"
names = {name for name, _dtype in mirror_columns(cur, SOURCE[key])}
owner = (_quoted(amazon_schema.CONNECTION_ID)
if amazon_schema.CONNECTION_ID in amazon_store._columns_of(cur, SOURCE[key])
else "NULL::VARCHAR")
old = ["_snapshot_date", "sku", "_report_row_number"]
if "_report_document_id" in names:
old.append("_report_document_id")
values = [f"NULLIF(CAST({_quoted(name)} AS VARCHAR), '')" if name in names
else "NULL::VARCHAR" for name in old]
complete = " AND ".join(f"{value} IS NOT NULL" for value in values)
digest = f"sha256(to_json(list_value({', '.join(values)})))"
legacy = f"CASE WHEN {complete} THEN greatest(1, CAST(('0x' || substr({digest}, 1, 13)) AS BIGINT)) END"
sku = _quoted("sku") if "sku" in names else "NULL::VARCHAR"
unscoped = (f"greatest(1, CAST(('0x' || substr(sha256(to_json(list_value(CAST({sku} AS VARCHAR)))), "
"1, 13)) AS BIGINT))")
spec = grid_source(cur, key)
return cur.execute(f'SELECT {owner}, {sku}, {spec["id"]}, {legacy}, {unscoped} '
f'FROM {SOURCE[key]}').fetchall()
def _migrate_listing_identity(rt, current, identities, source_fields, read_through, legacy_owner=None):
"""Copy authored listing cells only when connection/SKU or retained lineage proves their target."""
from core import shared_overlay
key = PREFIX + "listing_items"
targets, aliases, target_ids, by_sku, candidates = {}, {}, set(), {}, {}
version = "connection_sku" if any(row[0] not in (None, "") for row in identities) else "sku"
previous_version = current.get("amazonListingIdentity")
migrating = previous_version != version
legacy_owner = str(legacy_owner) if legacy_owner not in (None, "") else None
def refuse():
raise Refused("Amazon listing SKU migration is ambiguous. Existing cells were retained.")
def alias(old, target):
old, target = str(old), str(target)
if old in aliases and aliases[old] != target:
refuse()
aliases[old] = target
for owner, sku, pid, previous, unscoped in identities:
if sku in (None, ""):
continue
owner = str(owner) if owner not in (None, "") else None
identity = (owner, str(sku))
if identity in targets or str(pid) in target_ids:
refuse()
targets[identity] = str(pid)
by_sku.setdefault(str(sku), []).append(identity)
target_ids.add(str(pid))
alias(pid, pid)
old_ids = []
if migrating and previous_version not in ("sku", "connection_sku") and previous is not None:
old_ids.append(previous)
if migrating and owner is not None:
old_ids.append(unscoped)
for old in old_ids:
candidates.setdefault(str(old), set()).add(identity)
authored = {field["key"] for field in current.get("fields", ())
if (field.get("automation") or {}).get("flowId") != "amazon_relational"
or _odoo._ut().user_edited(field)}
stored = current.get("rows") or {}
source_names = {*source_fields, "refreshed", amazon_schema.CONNECTION_ID}
moved, notes, seen = {}, {}, set()
for old, row in stored.items():
sku = str(row.get("sku") or "")
values = {name: value for name, value in row.items()
if name != amazon_schema.CONNECTION_ID and (name in authored or name not in source_names)}
owner = row.get(amazon_schema.CONNECTION_ID)
if owner not in (None, ""):
target = targets.get((str(owner), sku))
elif str(old) in target_ids:
target = str(old) if any(targets[identity] == str(old) for identity in by_sku.get(sku, ())) else None
elif legacy_owner is not None and version == "connection_sku" and previous_version != "connection_sku":
target = targets.get((legacy_owner, sku))
else:
matches = by_sku.get(sku, ())
target = targets[matches[0]] if len(matches) == 1 else None
if target is None:
if values:
refuse()
continue
if target in seen:
refuse()
seen.add(target)
alias(old, target)
moved[target] = dict(row)
if read_through and values:
notes[target] = values
# Stored ownership is stronger evidence than an old numeric alias shared by two accounts.
for old, matches in candidates.items():
if old in aliases:
continue
owned = {identity for identity in matches if identity[0] == legacy_owner} if legacy_owner else set()
resolved = owned if legacy_owner is not None and version == "connection_sku" else matches
if len(resolved) == 1:
alias(old, targets[next(iter(resolved))])
# Read strictly before declaring migration complete. A failed read is never an empty overlay.
captured = dict((rt.get(shared_overlay.bucket(key)) or {}).get("cells") or {})
if migrating and any(
values and str(pid) not in aliases for pid, values in captured.items()):
refuse()
for old, target in aliases.items():
for name, value in (captured.get(old) or {}).items():
dest = notes.setdefault(target, {})
if name in dest and dest[name] != value:
refuse()
dest[name] = value
for target, values in notes.items():
held = captured.get(target) or {}
if any(name in held and held[name] != value for name, value in values.items()):
refuse()
class Writer:
def update(self, bucket, updater, flush=None):
def guarded(data):
if ((data or {}).get("cells") or {}) != captured:
refuse()
return updater(data)
return rt.update(bucket, guarded, flush="sync")
# Copy before publishing new IDs. Retain old overlay cells for recovery and retries.
delta = {target: {name: value for name, value in values.items()
if name not in (captured.get(target) or {})}
for target, values in notes.items()}
if any(delta.values()):
shared_overlay.put_rows(key, delta, st=Writer())
current["rows"] = moved
current["amazonListingIdentity"] = version
def apply_plan(rt, built, username="amazon", today=None):
"""Create-or-merge every table in `built`, in ONE `flush="sync"` write.
β ONE write for all seven, not seven. `Store.update` is a full download plus a full upload of
the tenant document, so seven would be seven round trips and seven commits per resync. It is
also ATOMIC: a `Refused` at MAX_TABLES aborts before anything persists, rather than leaving the
half-spawn `plan()` exists to prevent.
"""
if built.get("problems"):
raise Refused("; ".join(built["problems"]))
stamp = today or _odoo._iso_today()
written = {}
cur_mirror = datastore.ro_cursor()
try:
plans = [(key, LABELS[key], (built[key]["fields"] if isinstance(built[key], dict)
else _fields_for(cur_mirror, key)), built[key])
for key in SOURCE if key in built]
legacy_ids = {}
candidate_counts = {}
report_order = {key: [name for name, _dtype in mirror_columns(cur_mirror, SOURCE[key])]
for key, _label, _fields, _rows in plans}
listing_ids = (_listing_identity_rows(cur_mirror)
if PREFIX + "listing_items" in built else ())
listing_owner = None
if PREFIX + "listing_items" in built:
from core import keychain
binding = amazon_store._read_state(cur_mirror, "amzn_account_binding")
listing_owner = ((binding or {}).get("detail")
or keychain.default_connection_id(rt, "amazon_sp"))
# S79-T13: each Ads table's description states its history window and any recorded limit.
ads_descriptions = {key: amazon_ads_store.table_description(SOURCE[key], cur_mirror)
for key, *_rest in plans if key in _ADS_SPEC}
# D-2643: an emptied stored table keeps the code and reason its mirror answers with.
empty_status = {key: source_status(cur_mirror, key) for key, _label, _fields, rows in plans
if isinstance(rows, list) and not rows}
for key, _label, _fields, rows in plans:
if isinstance(rows, dict):
continue
names = report_order[key]
occurrences = {}
for row in rows:
raw = {name: row.get(name) for name in names}
# Content hashes are legacy migration candidates, never new row identities.
payload = json.dumps(raw, sort_keys=True, ensure_ascii=True,
separators=(",", ":"), default=str)
base_id = _row_id(raw, key)
occurrence = occurrences.get(base_id, 0)
occurrences[base_id] = occurrence + 1
candidates = (
_row_id(raw, key, occurrence),
hashlib.sha256(payload.encode("utf-8")).hexdigest()[:32])
declared = (("_row_key",) if key in _ADS_SPEC
else amazon_schema.TABLE_SPECS[key.removeprefix(PREFIX)]["row_identity"])
if all(raw.get(name) not in (None, "") for name in declared):
old_composite = json.dumps(tuple(raw[name] for name in declared),
ensure_ascii=True, default=str)
candidates = (*candidates,
hashlib.sha256(old_composite.encode("utf-8")).hexdigest()[:32])
if key.removeprefix(PREFIX) in _LEGACY_IDENTITY_TABLES:
first = next((str(raw[name]) for name in IDENTITY[key]
if raw.get(name) not in (None, "")), None)
if first is not None:
candidates = (*candidates, first)
legacy_ids[(key, row["_id"])] = tuple(dict.fromkeys(candidates))
for candidate in legacy_ids[(key, row["_id"])]:
slot = (key, candidate)
candidate_counts[slot] = candidate_counts.get(slot, 0) + 1
finally:
cur_mirror.close()
not_stored = {}
def _apply_all(doc):
doc = doc if isinstance(doc, dict) else {}
not_stored.clear()
live_columns = {key: {field["key"] for field in table.get("fields", ())}
for key, table in doc.items() if key in SOURCE and isinstance(table, dict)}
# Empty materialized snapshots reconcile existing definitions and create none.
unborn = {key for key, _label, _fields, rows in plans
if isinstance(rows, list) and not rows and not isinstance(doc.get(key), dict)}
live_columns.update({key: {field["key"] for field in fields}
for key, _label, fields, _rows in plans if key not in unborn})
generated = connector_spec.system_link_fields("amazon", live_columns)
for key, label, fields, rows in plans:
if key in unborn:
continue
stub = isinstance(rows, dict) and rows.get("readThrough") is True
def unbacked_refreshed(field):
return (stub and "refreshed" not in report_order[key]
and field.get("key") == "refreshed"
and (field.get("automation") or {}).get("flowId") == "amazon_relational"
and (field.get("automation") or {}).get("preset")
and not _odoo._ut().user_edited(field))
# Retire only the machine row stamp that read-through SQL cannot read.
fields = [field for field in fields if not unbacked_refreshed(field)]
if key == PREFIX + "listing_items":
current = doc.get(key) or {}
_migrate_listing_identity(rt, current, listing_ids, report_order[key],
isinstance(rows, dict) and rows.get("readThrough") is True,
legacy_owner=listing_owner)
listing_version = ("connection_sku" if any(row[0] not in (None, "") for row in listing_ids)
else "sku")
fields = [field for field in fields
if not (field.get("type") == "link"
and (field.get("automation") or {}).get("flowId") == "amazon_relational")]
at = next((i for i, field in enumerate(fields) if field.get("key") == "refreshed"),
len(fields))
fields[at:at] = [_odoo._preset(field, flow="amazon_relational")
for field in generated.get(key, ())]
# ββ D-3284 (owner ruling 2026-09-27): S66-T42/T43 created a NEW Amazon table only while
# the workspace had room under MAX_TABLES and reported the rest `not_stored`; ten Amazon
# databases stayed missing that way. Connector databases now take no MAX_TABLES slot
# (`user_tables.counts_toward_table_cap`), so every planned Amazon table is created.
# `not_stored` stays as bookkeeping: empty, it clears any earlier record.
# Corrected/removed measured joins retire their machine-owned old fields.
# Preserve fields a person created or explicitly took over.
current = doc.get(key) or {}
wanted = {f["key"] for f in fields}
# S67-T30: a product lookup the mirror no longer supports retires the same way.
current["fields"] = _lead_with_identity(
[f for f in current.get("fields", [])
if not (unbacked_refreshed(f)
or ((f.get("type") == "link" or f.get("key") in LOOKUP_KEYS)
and (f.get("automation") or {}).get("flowId") == "amazon_relational"
and not _odoo._ut().user_edited(f)
and f.get("key") not in wanted))],
report_order.get(key) or [], PINNED.get(key))
if current["fields"]:
current["fields"] = _place_lookups(current["fields"], fields)
if not stub:
stored = current.get("rows") or {}
for row in rows:
if row["_id"] in stored:
continue
for old_id in legacy_ids.get((key, row["_id"]), ()):
if old_id in stored and candidate_counts[(key, old_id)] == 1:
# Carry authored cells along when replacing an unusable string id.
stored[row["_id"]] = stored.pop(old_id)
break
removed = len((doc.get(key) or {}).get("rows") or {}) if stub else 0
written[key] = _odoo._ensure_table_inplace(doc, key, label, fields, None if stub else rows,
username, stamp)
# S78-T01: `_ensure_table_inplace` does not forward `format`, so a stored preset field
# would keep showing "$" on a weight or 0 decimals on a percent.
formats = {f["key"]: f["format"] for f in fields if f.get("format")}
for stored_field in doc[key].get("fields", []):
if (stored_field.get("key") in formats
and (stored_field.get("automation") or {}).get("flowId") == "amazon_relational"
and (stored_field.get("automation") or {}).get("preset")
and not _odoo._ut().user_edited(stored_field)):
stored_field["format"] = dict(formats[stored_field["key"]])
if stub:
doc[key]["readThrough"] = True
doc[key]["rows"] = {}
doc[key].pop(_odoo._ut().OVERSIZE_FIELD, None)
written[key].update(rows=rows["count"], removed=removed, readThrough=True)
else:
doc[key].pop("readThrough", None)
# The shared merger compares string spellings, so an old "2" and a new
# integer 2 otherwise compare equal and leave the old cell untyped.
numeric = {f["key"] for f in doc[key].get("fields", [])
if f.get("type") in ("currency", "pct", "int")
and (f.get("automation") or {}).get("flowId") == "amazon_relational"
and not _odoo._ut().user_edited(f)}
stored = doc[key].get("rows") or {}
for row in rows:
saved = stored.get(row["_id"])
if numeric and isinstance(saved, dict):
saved.update({name: row[name] for name in numeric if name in row})
status = rows.get("sourceStatus") if isinstance(rows, dict) else empty_status.get(key)
if status is not None:
doc[key]["sourceStatus"] = dict(status)
else:
doc[key].pop("sourceStatus", None)
# DPP 1.8 attribution is explicit in the stored definition, not inferred from a
# mutable display label or from the process having imported this module.
doc[key]["connector"] = source_attribution(key)
if ads_descriptions.get(key):
doc[key]["description"] = ads_descriptions[key]
if key in (PREFIX + "review_topics", PREFIX + "review_trends"):
doc[key]["description"] = amazon_schema.REVIEW_UNAVAILABLE_NOTE
if key == PREFIX + "search_query_performance":
doc[key]["description"] = amazon_schema.SQP_LIMIT_NOTE
if key == PREFIX + "listing_items":
doc[key]["amazonListingIdentity"] = listing_version
return doc
rt.update(_odoo._ut().STORE_KEY, _apply_all, flush="sync")
from core import shares
shares.repair_published_database_grants(rt, table_keys=tuple(written))
_record_not_stored({key for key, *_rest in plans}, dict(not_stored))
return written
def _record_not_stored(planned, not_stored):
"""Durable per-table marker in the Amazon mirror, read by `connectors_amazon.sync_state`.
A table refused at the store's table cap gets `not_stored` with its reason; a table that was
stored clears any earlier marker. Best effort: the store write above already happened, and a
marker failure must not turn it into an error.
"""
try:
con = datastore.connect()
for key in planned:
table = key[len(PREFIX):]
entity = amazon_store.entity_key("not_stored_" + table)
if key in not_stored:
amazon_store._record_state(con, entity, "not_stored", 0, not_stored[key])
else:
con.execute("DELETE FROM _sync_state WHERE entity = ?", [entity])
except Exception: # noqa: BLE001
pass
def refresh_state(rt, why="boot", tenant_key=None, previous_fingerprint=None, log=print):
"""Open, plan and conditionally apply the Amazon mirror.
Returns `{}` and says why when the mirror holds no Amazon tables β a tenant that never
connected Amazon is a normal state, not a failure.
"""
state = {"present": False, "known": False, "fingerprint": None,
"changed": False, "applied": False, "written": {}}
tenant = tenant_key or getattr(getattr(rt, "tenant", None), "key", None) or "royal-imports"
path = datastore.path_for(tenant)
if Path(datastore.DB_PATH) != Path(path):
datastore.use_path(path)
cur = datastore.ro_cursor()
try:
return _refresh_from_cursor(cur, rt, why, tenant, previous_fingerprint, log, state)
finally:
cur.close()
def _refresh_from_cursor(cur, rt, why, tenant, previous_fingerprint, log, state):
if not any(mirror_columns(cur, t) for t in SOURCE.values()):
log(f"[amazon_relational] {why}: no Amazon tables in {tenant}'s mirror, nothing to spawn "
f"(run `python platform/harness/amazon_store.py --sync --tenant {tenant}` first)")
return state
state["present"] = True
# S70-T12 / D-3495: publish immediately from the restored cursor, before planning or
# the unchanged-fingerprint return. Registry readiness must not wait for a store write.
import routes_odoo_tables
routes_odoo_tables._learn_amazon_sources(cur)
built = plan(cur, rt)
problems = built.get("problems") or []
for p in problems:
log(f"[amazon_relational] PROBLEM {p}")
applicable = {k: v for k, v in built.items() if k != "problems"}
if not problems:
try:
state["fingerprint"] = plan_fingerprint(cur, applicable)
state["known"] = True
except Exception as e: # noqa: BLE001
log(f"[amazon_relational] {why}: plan fingerprint unavailable; forcing refresh "
f"({type(e).__name__}: {e})")
if state["known"] and state["fingerprint"] == previous_fingerprint:
log(f"[amazon_relational] {why}: Amazon plan unchanged; skipped full user_tables write")
return state
state["changed"] = True
state["written"] = apply_plan(rt, applicable)
state["applied"] = True
import routes_odoo_tables
routes_odoo_tables.invalidate_sources()
for key, counts in state["written"].items():
log(f"[amazon_relational] {why}: {key:<28} {counts}")
return state
def refresh(rt, why="boot", tenant_key=None, log=print):
"""Backward-compatible facade. Use `refresh_state` for egress-aware state."""
return refresh_state(rt, why, tenant_key, log=log)["written"]
def main(argv=None):
"""`python aios-web/api/amazon_relational.py --refresh [--tenant X]` β the manual door."""
import argparse
ap = argparse.ArgumentParser()
ap.add_argument("--refresh", action="store_true")
ap.add_argument("--plan", action="store_true")
ap.add_argument("--tenant", default="royal-imports")
a = ap.parse_args(argv)
os.environ.setdefault("AIOS_PREWARM", "0")
from harness import runtime as _rt
rt = _rt.get_runtime(a.tenant)
if a.plan:
path = datastore.path_for(a.tenant)
if Path(datastore.DB_PATH) != Path(path):
datastore.use_path(path)
built = plan(datastore.ro_con(), rt)
for k, v in built.items():
print(f" {k:<28} {len(v) if isinstance(v, list) else v}")
return 0
if a.refresh:
refresh(rt, why="cli", tenant_key=a.tenant)
return 0
ap.print_help()
return 2
if __name__ == "__main__":
sys.exit(main())
"""amazon_relational.py β the Amazon mirror becomes LOCKED DATABASES under Data (wave 52, R14).
`harness/amazon_store.py` pulls SP-API report documents into the tenant's DuckDB mirror; this reads
that mirror and spawns the `ut_amzn_*` grids a person opens. It is `meta_relational.py`'s shape
with one source swapped, which is itself `odoo_relational.py`'s shape.
plan(cur) the rows that WOULD be written β no store write at all
apply_plan(rt, built) create-or-merge every table and its rows, in ONE sync write
refresh(rt, why) both, with the mirror opened for this tenant
ββ **THE COLUMNS ARE DERIVED FROM THE MIRROR, NEVER TYPED OUT HERE**, exactly as Meta's are. For
Amazon that is doubly load-bearing: five of the seven tables have their columns MEASURED from the
report document's own header row at sync time (`amazon_schema.PROVENANCE`), so a hand-written field
list here would not merely rot, it would contradict the only measurement that exists. Add a column
to the report and it appears in the grid with no edit in this file.
ββ **R14 β THIS FILE IS WHERE THE CONNECTOR ENDS.** *"this is just database from Amazon, all its
fields mapped into our app so it can be seen under Data module and later be pulled into Sheet. All
database connector has this exact flow."* No dashboard, no module, no metric page. `Data` lists
these tables because they are `ut_*` tables; `sheet/BindingEditor.tsx:61` binds them on the `ut_`
prefix. Both are prefix-driven, so **neither needs a line of work here** β step 5 of `/connector` is
a verification, not a build.
β **R12 β AMAZON IS A STANDALONE DATABASE SET.** Owner: *"Its all different. Assume that its a
whole new databases which they are."* `LINKS` below therefore joins Amazon tables only to OTHER
AMAZON TABLES: order lines to their order, pricing to its listing. **Nothing here joins to
`product_data`, to `ut_odoo_*`, or to the hand-maintained ASIN mastersheet.** β That is what
removes the [[amazon-conversion-k-is-inverted]] defect class outright: no Odoo unit is ever
converted to an Amazon unit, because the two never meet.
History stays in the mirror. Small snapshots may be materialised; a row or byte budget switches
them to read-through too. The shared document holds their schemas, never a truncated population.
"""
import hashlib
import json
import os
import re
import sys
from collections import OrderedDict
from pathlib import Path
from threading import RLock
_API = Path(__file__).resolve().parent
if str(_API.parent.parent / "platform") not in sys.path:
sys.path.insert(0, str(_API.parent.parent / "platform"))
import odoo_relational as _odoo # noqa: E402
from core import connector_spec # noqa: E402
from harness import amazon_client, amazon_schema, amazon_store, datastore # noqa: E402
from harness import amazon_ads_store # noqa: E402
Refused = _odoo.Refused
#: β THE PREFIX IS `ut_amzn_`, AND IT MUST ALSO BE IN `routes_connectors.CONNECTED_PREFIXES` β
#: otherwise `is_connected` falls through and every Amazon table is silently re-capped at
#: `MAX_ROWS`, breaking standing rule 1 with nothing raising. `verify_amazon` asserts the pair.
PREFIX = "ut_amzn_"
#: `ut_*` key -> the mirror table `amazon_store` writes. Derived from ONE list so a table cannot
#: exist on one side and not the other.
#: S79-T13: the Ads tables (`ut_amzn_ads_*`, connector `amazon_ads`) live in the same mirror.
_ADS_SPEC = {s.key: s for s in connector_spec.tables_for("amazon_ads")}
SOURCE = {**{PREFIX + t: amazon_store.table_name(t) for t in amazon_store.DATABASE_TABLES},
**{k: amazon_ads_store.table_name(s.source) for k, s in _ADS_SPEC.items()}}
#: ββ **THE TABLE SHAPE IS NOW READ FROM `platform/core/connector_spec.py`, NOT TYPED HERE**
#: (W53-T37, contract C2). `LABELS`, `PINNED`, `IDENTITY` and `LINKS` keep their module-level
#: names because `verify_amazon`, `verify_meta.section_connector_spec` and the connector routes all
#: read them BY NAME β but each is a DERIVED VIEW over ONE declaration now, so the shape can no
#: longer be stated twice and say two things. Every comment that used to justify a literal here
#: (the two spines, R12, the report-vs-API spellings, the cardinalities) travelled with the values
#: into `connector_spec.AMAZON_TABLES`; read it there.
_SPEC = {**{s.key: s for s in connector_spec.tables_for("amazon")}, **_ADS_SPEC}
def _identity_columns(table, columns=()):
"""An Ads row is identified by its stored `_row_key`; every other table by amazon_schema."""
if PREFIX + table in _ADS_SPEC:
return ("_row_key",)
return amazon_schema.row_key_columns_for(table, columns)
def _fallback_columns(table):
if PREFIX + table in _ADS_SPEC:
return ("_report_id",)
return amazon_schema.row_fallback_columns_for(table)
#: β A `ut_*` KEY THAT `amazon_store` SPAWNS BUT THE SPEC DOES NOT DECLARE IS ABSENT FROM ALL FOUR
#: DICTS BELOW, ON PURPOSE. `verify_amazon.section_relational` asserts the four key sets equal
#: `SOURCE`'s, so the drift reddens a gate rather than raising at import inside a live Space, and
#: it names the missing table instead of a KeyError deep in `apply_plan`.
LABELS = {k: _SPEC[k].label for k in SOURCE if k in _SPEC}
#: The column each grid shows first. β Every one is a name Amazon's own reports use, so a table
#: whose header does not carry it simply has no pinned column rather than a broken one.
PINNED = {k: _SPEC[k].pinned for k in SOURCE if k in _SPEC}
#: Legacy descriptor view. Row keys use amazon_schema's complete identity and source lineage.
IDENTITY = {k: tuple(_SPEC[k].identity) for k in SOURCE if k in _SPEC}