loopable / api /amazon_relational.py
fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
daae64a verified
Raw History Blame
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}