"""amazon_ads_store.py: the Amazon Ads loader into the Amazon tenant mirror (S79-T12, contract C6). python platform/harness/amazon_ads_store.py --sync --tenant royal-imports python platform/harness/amazon_ads_store.py --status --tenant royal-imports One table per ad type and report, `amzn_ads__`, spawned as `ut_amzn_ads_*`. ⛔ HISTORY IS KEPT FROM THE CONNECT DAY. The first sync stamps the connect day once and reaches back the longest window Amazon documents per table (`connector_spec.AMAZON_ADS_BACKFILL_DAYS`). A later sync re-reads the table's daily or intraday cadence window and upserts by row identity. No sync deletes a day an earlier sync stored, so a day Amazon has aged out stays here. ⛔ STANDING RULE 1. Every report is read whole, every file of it. The limits that cannot be removed (one report at a time per client ID, about 100 a day, a report v1 does not offer) are written to `_sync_state` as `amzn_limit_ads_` with the reason a person reads, and stated on the table description. Receipts use the Amazon mirror's markers: `amzn_sync_mark_ads_
` is the newest landed day, `amzn_sync_floor_ads_
` the oldest, `amzn_ads_
` the table state. """ import argparse import hashlib import importlib import json import logging import sys import tempfile import threading import time import uuid from datetime import date, datetime, timedelta, timezone from pathlib import Path _HERE = Path(__file__).resolve().parent if str(_HERE.parent) not in sys.path: sys.path.insert(0, str(_HERE.parent)) from core import connector_spec # noqa: E402 from harness import amazon_client # noqa: E402 from harness import amazon_ads_client as client # noqa: E402 from harness import amazon_ads_schema as schema # noqa: E402 from harness import amazon_store # noqa: E402 from harness import datastore # noqa: E402 #: C6: called as `fn(tenant_key, synced_tables)` after a sync lands rows. Imported lazily; a #: failure is one log line, never a failed sync. ADS_AFTER_SYNC = ("modules.amazon_operator:on_ads_sync",) TABLES = tuple(schema.TABLE_SPECS) #: Keep the exported default compatible. Requests use each table's own cadence. RESTATEMENT_DAYS = amazon_client.CADENCE["ads_sp_campaigns"]["lookbackDays"] CONNECT_DAY_KEY = amazon_store.entity_key("ads_connect_day") BUDGET_KEY = amazon_store.entity_key("ads_report_budget") INSERT_BATCH_ROWS = 500 ORIGIN_COLUMNS = ("_connection_id", "store") #: One `_sync_state` entity per (connection, table) holds a report Amazon is still preparing. PENDING_PREFIX = "ads_pending_" PREPARING_REASON = "Amazon is still preparing this report. The next sync reads it." TIME_LIMIT_REASON = "This sync reached its time limit. The next sync continues." #: A partial pass asks the caller to run the next pass after this many seconds. CONTINUE_AFTER_SECONDS = 600.0 #: The pass clock for `max_seconds`. A gate patches it to stand in for Amazon's preparing time. _clock = time.monotonic def table_name(table): """`sp_campaigns` -> `amzn_ads_sp_campaigns`, the mirror table. One place, so a rename lands whole.""" stream = schema.stream_spec(table) if stream is not None: return stream["source"] return schema.TABLE_SPECS[schema.short_name(table)]["source"] def _marker_table(table): """The `
` the Amazon mirror markers name: `ads_sp_campaigns`.""" return table_name(table)[len(amazon_store.PREFIX):] def limit_key(table): return amazon_store.entity_key("limit_" + _marker_table(table)) def table_description(table, con=None): """The description a person reads, plus any limit the last sync recorded. Never raises.""" stream = schema.stream_spec(table) if stream is not None: return stream["description"] try: key = schema.TABLE_SPECS[schema.short_name(table)]["key"] except KeyError: return None text = connector_spec.amazon_ads_description(key) or "" if con is None: return text try: state = amazon_store._read_state(con, limit_key(table)) reason = str(json.loads(state["detail"]).get("reason") or "") if state and state.get("detail") else "" except Exception: # noqa: BLE001 reason = "" return f"{text} {reason}" if reason and reason not in text else text def _utc_today(now=None): now = now or datetime.now(timezone.utc) return now.date() if isinstance(now, datetime) else now def _utc_now(now=None): """Normalize datetime and date callers to the same UTC cadence clock.""" now = now or datetime.now(timezone.utc) if not isinstance(now, datetime): now = datetime(now.year, now.month, now.day, tzinfo=timezone.utc) return now.replace(tzinfo=timezone.utc) if now.tzinfo is None else now.astimezone(timezone.utc) def _state_key(key, connection_id=None): """Keep the public table markers and give each connection its own continuation markers.""" return f"{key}::connection::{connection_id}" if connection_id else key def _cadence_due(con, table, now, connection_id=None): """Only this connection's completed pass can delay its next cadence interval.""" cadence = amazon_client.CADENCE["ads_" + table] tick = amazon_store._read_state(con, _state_key( amazon_store.entity_key("sync_tick_" + _marker_table(table)), connection_id)) if not tick or tick.get("state") != "done": return True try: stamp = amazon_store._utc(tick.get("detail") or tick["updated_at"]) except (ValueError, TypeError, AttributeError, OverflowError): return True return now >= stamp + timedelta(days=1) / cadence["perDay"] def _intraday(con, table, today, connection_id=None): """Retry the daily lookback until every request for this connection succeeds.""" day = amazon_store._read_state(con, _state_key( amazon_store.entity_key("lookback_day_" + _marker_table(table)), connection_id)) return bool(day and day.get("state") == "done" and day.get("detail") == today.isoformat()) def _record_cadence(con, table, now, connection_id=None): """Persist successful empty and nonempty passes with their daily lookback completion.""" tick = _state_key(amazon_store.entity_key("sync_tick_" + _marker_table(table)), connection_id) stamp = now.isoformat() amazon_store._record_state(con, tick, "done", detail=stamp) con.execute("UPDATE _sync_state SET updated_at = ? WHERE entity = ?", [stamp, tick]) amazon_store._record_state(con, _state_key( amazon_store.entity_key("lookback_day_" + _marker_table(table)), connection_id), "done", detail=now.date().isoformat()) def _connect_day(con, today, connection_id=None, *, legacy=False): """The connect day, stamped once on the first sync and never moved.""" key = _state_key(CONNECT_DAY_KEY, connection_id) state = amazon_store._read_state(con, key) inherited = False if state is None and legacy and connection_id: state = amazon_store._read_state(con, CONNECT_DAY_KEY) inherited = state is not None if state and state.get("detail"): try: day = date.fromisoformat(str(state["detail"])[:10]) if inherited: amazon_store._record_state(con, key, "done", detail=day.isoformat()) return day except ValueError: pass amazon_store._record_state(con, key, "done", detail=today.isoformat()) return today def _day_marker(con, key): state = amazon_store._read_state(con, key) try: return date.fromisoformat(str(state["detail"])[:10]) if state and state.get("detail") else None except ValueError: return None def windows(con, table, connect_day, today, connection_id=None): """The report windows one sync asks for, oldest first, each at most 120 days (`client.MAX_DAYS_PER_REQUEST`).""" table = schema.short_name(table) spec = schema.TABLE_SPECS[table] cadence = amazon_client.CADENCE["ads_" + table] lookback = cadence["intradayLookbackDays" if _intraday(con, table, today, connection_id) else "lookbackDays"] last = today - timedelta(days=1) target_floor = max(connect_day, today) - timedelta(days=spec["backfill_days"]) mark_name = _marker_table(table) newest = _day_marker(con, _state_key(amazon_store.entity_key(amazon_store.WATERMARK_PREFIX + mark_name), connection_id)) oldest = _day_marker(con, _state_key(amazon_store.entity_key(amazon_store.BACKFILL_FLOOR_PREFIX + mark_name), connection_id)) spans = [] if newest is None or oldest is None: spans.append((target_floor, last)) else: if oldest > target_floor: spans.append((target_floor, oldest - timedelta(days=1))) spans.append((max(target_floor, min(newest, last - timedelta(days=lookback - 1))), last)) out = [] for start, end in spans: cursor = start while cursor <= end: stop = min(cursor + timedelta(days=client.MAX_DAYS_PER_REQUEST - 1), end) out.append((cursor, stop)) cursor = stop + timedelta(days=1) return out def _columns_for(table): if schema.stream_spec(table) is not None: return schema.stream_spec(table)["columns"] return tuple(dict.fromkeys(schema.columns_for(table) + schema.ORIGIN_COLUMNS + ORIGIN_COLUMNS)) def _ensure_table(con, table): if schema.stream_spec(table) is not None: return _ensure_stream_tables(con) name = table_name(table) cols = _columns_for(table) have = {r[0] for r in con.execute( "SELECT column_name FROM information_schema.columns WHERE table_name = ?", [name]).fetchall()} if not have: con.execute(f'CREATE TABLE IF NOT EXISTS "{name}" (' + ", ".join(f'"{c}" {schema.coltype(c) if not c.startswith("_") else "VARCHAR"}' for c in cols) + ")") return for c in cols: if c not in have: con.execute(f'ALTER TABLE "{name}" ADD COLUMN "{c}" ' f'{schema.coltype(c) if not c.startswith("_") else "VARCHAR"}') def _upsert(con, table, rows): """Delete-then-insert by `_row_key` only. ⛔ Never by window: a day absent from a re-read stays.""" if not rows: return 0 name = table_name(table) cols = _columns_for(table) by_key = {r["_row_key"]: r for r in rows} keys = list(by_key) one = f'({", ".join("?" for _ in cols)})' for i in range(0, len(keys), INSERT_BATCH_ROWS): part = keys[i:i + INSERT_BATCH_ROWS] con.execute(f'DELETE FROM "{name}" WHERE "_row_key" IN ({",".join("?" for _ in part)})', part) # One multi-row INSERT per batch: DuckDB's executemany is about 5 times slower at 24 months a pass. con.execute(f'INSERT INTO "{name}" ({", ".join(chr(34) + c + chr(34) for c in cols)}) ' f'VALUES {", ".join(one for _ in part)}', [by_key[k].get(c) for k in part for c in cols]) return len(by_key) def _budget(con, today, connection_id=None, *, legacy=False): state = amazon_store._read_state(con, _state_key(BUDGET_KEY, connection_id)) if state is None and legacy and connection_id: state = amazon_store._read_state(con, BUDGET_KEY) try: detail = json.loads(state["detail"]) if state and state.get("detail") else {} except (ValueError, TypeError): detail = {} return int(detail.get("used") or 0) if detail.get("day") == today.isoformat() else 0 def _spend_budget(con, today, used, connection_id=None): amazon_store._record_state(con, _state_key(BUDGET_KEY, connection_id), "done", rows=used, detail=json.dumps({"day": today.isoformat(), "used": used})) def _record_limit(con, table, reason, connection_id=None, **extra): amazon_store._record_state(con, _state_key(limit_key(table), connection_id), "done", detail=json.dumps({"reason": reason, **extra})) def _clear_limit(con, table, connection_id=None): con.execute("DELETE FROM _sync_state WHERE entity = ?", [_state_key(limit_key(table), connection_id)]) def _count(con, table, connection_id=None): where = ' WHERE "_connection_id" = ?' if connection_id is not None else '' return con.execute(f'SELECT count(*) FROM "{table_name(table)}"' + where, [connection_id] if connection_id is not None else []).fetchone()[0] def _runtime_for(key): try: from harness import runtime return runtime.get_runtime(key) except Exception: return None def _connections(rt, key, profile_id, report): """Read named business credentials without changing process state or the client's resolver.""" from core import keychain if rt is None and client.fixture_mode(): return [("", "Amazon Ads", profile_id, True)] try: entries = sorted((row for row in keychain.list_entries(rt) if row.get("type") == "amazon_ads"), key=lambda row: (int(row.get("slot") or 1), str(row["id"]))) default = keychain.resolve_connection_id(rt, "amazon_ads") except Exception: report["problems"].append("The Amazon Ads connections could not be read. Retry this sync.") return [] out = [] for row in entries: cid = str(row.get("connection_id") or row["id"]) label = str(row.get("label") or cid) try: fields = keychain.amazon_ads_creds(rt, connection_id=cid) except Exception: report["problems"].append(f"{label}: the Amazon Ads credential could not be read. Test this connection again.") continue if fields is None: continue # The public resolver excludes personal credentials. if not all(fields.get(name) for name in client.CREDENTIAL_FIELDS): report["problems"].append(f"{label}: the Amazon Ads credential is incomplete. Check its four fields.") continue out.append((cid, label, str(fields["profile_id"]), cid == default)) return out def _row_key(connection_id, key): if not connection_id: return key payload = json.dumps([connection_id, key], separators=(",", ":")).encode("utf-8") return hashlib.sha256(payload).hexdigest() def _refresh_origin(con, table, connection_id, label, legacy): """Migrate the former default's rows once and refresh labels across retained history.""" name = table_name(table) changed = con.execute(f'SELECT count(*) FROM "{name}" WHERE ' '("_connection_id" = ? AND store IS DISTINCT FROM ?) ' 'OR ("_connection_id" IS NULL AND ?)', [connection_id, label, legacy]).fetchone()[0] if legacy: rows = con.execute(f'SELECT "_row_key" FROM "{name}" WHERE "_connection_id" IS NULL').fetchall() for (key,) in rows: con.execute(f'UPDATE "{name}" SET "_row_key" = ?, "_connection_id" = ?, store = ? ' 'WHERE "_row_key" = ? AND "_connection_id" IS NULL', [_row_key(connection_id, key), connection_id, label, key]) con.execute(f'UPDATE "{name}" SET store = ? WHERE "_connection_id" = ?', [label, connection_id]) return changed def _pending_key(table, connection_id=None): return _state_key(amazon_store.entity_key(PENDING_PREFIX + _marker_table(table)), connection_id) def _read_pending(con, table, connection_id=None): """The report this connection left preparing for `table`, or None.""" state = amazon_store._read_state(con, _pending_key(table, connection_id)) if not state or state.get("state") != "pending" or not state.get("detail"): return None try: entry = json.loads(state["detail"]) return {"report_id": str(entry["report_id"]), "start": date.fromisoformat(entry["start"]), "end": date.fromisoformat(entry["end"]), "created": amazon_store._utc(entry["created"])} except (ValueError, TypeError, KeyError, AttributeError, OverflowError): return None def _write_pending(con, table, connection_id, report_id, start, end, created): amazon_store._record_state(con, _pending_key(table, connection_id), "pending", detail=json.dumps( {"report_id": report_id, "start": start.isoformat(), "end": end.isoformat(), "created": created.isoformat()})) def _clear_pending(con, table, connection_id=None): """Keep the row with no detail, so a store that merges state rows cannot bring the entry back.""" amazon_store._record_state(con, _pending_key(table, connection_id), "done") def _land(con, table, rows, report_id, start, end, profile, connection_id, label): """Upsert one finished report window and advance this connection's markers. -> rows stored.""" stored = [] for raw in rows: row = schema.row_from_vendor(table, raw, profile_id=profile, report_id=report_id) row.update(_connection_id=connection_id, store=label, _row_key=_row_key(connection_id, row["_row_key"])) stored.append(row) pulled = _upsert(con, table, stored) mark = _marker_table(table) # Only this connection's landed window advances its continuation. for prefix, day, later in ((amazon_store.WATERMARK_PREFIX, end, True), (amazon_store.BACKFILL_FLOOR_PREFIX, start, False)): public = amazon_store.entity_key(prefix + mark) for marker in dict.fromkeys((_state_key(public, connection_id), public)): previous = _day_marker(con, marker) if previous is None or (day > previous if later else day < previous): amazon_store._record_state(con, marker, "done", _count(con, table), day.isoformat()) return pulled def _run_report(key, table, start, end, rt, profile_id, connection_id, **extra): """Run the group report that fills `table` under this connection's own credential. -> GroupReport.""" options = {"rt": rt, "profile_id": profile_id, **extra} if connection_id: options["connection_id"] = connection_id return client.run_group_report(key, schema.group_of(table), start, end, **options) class _Pass: """One connection's sync pass: its budget, clock, and what its group reports already covered. `eligible` holds the tables a group report may land into this pass: requested, offered, due and not yet finished. `covered` holds each table's spans landed this pass, `pulled` the rows a sibling's report landed, `outcome` a group's failure, `notes` Amazon's text per table. """ def __init__(self, con, key, rt, connection, report, *, today, used, deadline, stamp, checkpointed): self.con, self.key, self.rt, self.report = con, key, rt, report self.cid, self.label, self.profile = connection[:3] self.today, self.used, self.deadline, self.stamp = today, used, deadline, stamp self.checkpointed = checkpointed self.eligible, self.covered, self.pulled, self.outcome, self.notes = set(), {}, {}, {}, {} self.blocked = False def wait(self, spec, reason): self.report["waits"].append(f"{self.label}: {spec['label']}: {reason}") def checkpoint(self, table): if table_name(table) not in self.checkpointed: self.checkpointed.append(table_name(table)) def covers(self, table, start, end): """True when reports this pass already read every day from `start` to `end` for `table`.""" spans = self.covered.get(table, ()) return all(any(lo <= start + timedelta(days=i) <= hi for lo, hi in spans) for i in range((end - start).days + 1)) def bounds(self): """The poll limit for one new report: none, or the time left in this bounded pass.""" return {} if self.deadline is None else {"max_wait": max(0.0, self.deadline - _clock()), "now": _clock} def fail(self, table, exc): """Record a group report's failure for every member. -> (state, reason) for `table`.""" self.outcome[schema.group_of(table).name] = exc return self.failure(table, exc) def failure(self, table, exc): spec = schema.TABLE_SPECS[table] if isinstance(exc, client.AdsReportUnavailable): return "not_offered", _not_offered_reason(spec, exc) reason = str(exc)[:client.ERROR_TEXT_LIMIT] self.report["problems"].append(f"{self.label}: {spec['label']}: {reason}") return "failed", reason def land(self, table, result, start, end): """Land one group report into `table` and each eligible sibling, covering the span. -> rows into `table`.""" own = 0 for member in schema.group_of(table).members: if member != table and member not in self.eligible: continue _ensure_table(self.con, member) rows = _land(self.con, member, result.rows.get(member, []), result.report_id, start, end, self.profile, self.cid, self.label) self.covered.setdefault(member, []).append((start, end)) self.notes[member] = list(dict.fromkeys(self.notes.get(member, []) + list(result.notes))) self.checkpoint(member) if member == table: own = rows else: self.pulled[member] = self.pulled.get(member, 0) + rows for note in result.notes: if f"{self.label}: {note}" not in self.report["problems"]: self.report["problems"].append(f"{self.label}: {note}") return own def _resume(p): """Read every report this connection left preparing before it creates another. -> {table: outcome}.""" resumed = {} for table in TABLES: pending = _read_pending(p.con, table, p.cid) if pending is None: continue spec = schema.TABLE_SPECS[table] _ensure_table(p.con, table) p.checkpoint(table) expired = f"Amazon Ads report {pending['report_id']} did not finish in 3 hours" age = (p.stamp() - pending["created"]).total_seconds() if age >= client.REPORT_MAX_WAIT_SECONDS: _clear_pending(p.con, table, p.cid) resumed[table] = (*p.failure(table, client.AdsError(expired)), 0) continue limit = client.REPORT_MAX_WAIT_SECONDS - age if p.deadline is not None: limit = min(limit, max(0.0, p.deadline - _clock())) try: result = _run_report(p.key, table, pending["start"], pending["end"], p.rt, p.profile, p.cid, max_wait=limit, report_id=pending["report_id"], now=_clock) except client.AdsReportPending: if (p.stamp() - pending["created"]).total_seconds() < client.REPORT_MAX_WAIT_SECONDS: p.blocked = True p.wait(spec, PREPARING_REASON) resumed[table] = ("deferred", PREPARING_REASON, 0) continue _clear_pending(p.con, table, p.cid) resumed[table] = (*p.failure(table, client.AdsError(expired)), 0) continue except client.AdsError as exc: _clear_pending(p.con, table, p.cid) resumed[table] = (*p.fail(table, exc), 0) continue _clear_pending(p.con, table, p.cid) resumed[table] = ("done", "", p.land(table, result, pending["start"], pending["end"])) return resumed def _run_windows(p, table, connect_day): """Run the group report for each window of `table` no report covered yet. -> (state, reason, rows).""" spec = schema.TABLE_SPECS[table] failed = p.outcome.get(schema.group_of(table).name) if failed is not None: return (*p.failure(table, failed), 0) pulled = 0 for start, end in windows(p.con, table, connect_day, p.today, p.cid): if p.covers(table, start, end): continue if p.used >= client.REPORTS_PER_DAY: reason = (f"{connector_spec.AMAZON_ADS_THROUGHPUT} Today's {client.REPORTS_PER_DAY} " f"reports are used, so days from {start.isoformat()} load on the next sync.") p.report["problems"].append(f"{p.label}: {spec['label']}: waiting for tomorrow's report limit") p.report["waits"].append(p.report["problems"][-1]) return "deferred", reason, pulled if p.deadline is not None and _clock() >= p.deadline: p.wait(spec, TIME_LIMIT_REASON) return "deferred", TIME_LIMIT_REASON, pulled p.used += 1 _spend_budget(p.con, p.today, p.used, p.cid) created = p.stamp() try: result = _run_report(p.key, table, start, end, p.rt, p.profile, p.cid, **p.bounds()) except client.AdsReportPending as exc: if (p.deadline is None or not exc.report_id or (p.stamp() - created).total_seconds() >= client.REPORT_MAX_WAIT_SECONDS): return (*p.failure(table, exc), pulled) _write_pending(p.con, table, p.cid, exc.report_id, start, end, created) p.checkpoint(table) p.blocked = True p.wait(spec, PREPARING_REASON) return "deferred", PREPARING_REASON, pulled except client.AdsError as exc: return (*p.fail(table, exc), pulled) pulled += p.land(table, result, start, end) return "done", "", pulled def sync(tenant_key="royal-imports", *, rt=None, con=None, tables=None, now=None, log=print, profile_id=None, max_seconds=None): """Pull every offered Ads report into the tenant's Amazon mirror. -> a report dict. When this call opens the connection and has a runtime, it saves landed windows and cadence through `amazon_store.persist_mirror`. A caller that lends `con` persists itself. `max_seconds` bounds the pass's wait on Amazon. A report still preparing then waits in the pending ledger, and the next pass reads it before it creates any other report. One group report fills every requested member table, so a sibling's covered days spend no report. """ key = amazon_store._tenant_key(tenant_key) report = {"tenant": key, "tables": {}, "problems": [], "limits": {}, "connections": [], "pendingReports": 0, "partial": False, "continueAfterSeconds": None, "waits": []} if rt is None and not client.fixture_mode(): rt = _runtime_for(key) if rt is not None and str(getattr(rt, "key", "") or "").strip().lower() != key: report["problems"].append("The runtime does not match the requested tenant. No Amazon Ads rows were pulled.") return report connections = _connections(rt, key, profile_id, report) if not connections: report["problems"].append("No Amazon Ads credential is stored for this tenant. Open Amazon Ads Setup to add a connection.") return report own = con is None if own: con, _path = amazon_store._connect_tenant_mirror(key, rt) started = _clock() deadline = None if max_seconds is None else started + float(max_seconds) synced, checkpointed = [], [] try: datastore._ensure_sync_state(con) now = _utc_now(now) today = _utc_today(now) def stamp(): return now + timedelta(seconds=max(0.0, _clock() - started)) requested = [schema.short_name(t) for t in (tables or TABLES)] for connection in connections: cid, label, _profile, legacy = connection connect_day = _connect_day(con, today, cid, legacy=legacy) report.setdefault("connectDay", connect_day.isoformat()) entry = {"id": cid, "label": label, "connectDay": connect_day.isoformat(), "tables": {}} report["connections"].append(entry) p = _Pass(con, key, rt, connection, report, today=today, used=_budget(con, today, cid, legacy=legacy), deadline=deadline, stamp=stamp, checkpointed=checkpointed) p.eligible = {t for t in requested if schema.TABLE_SPECS[t]["offered"] and _cadence_due(con, t, now, cid)} resumed = _resume(p) for table in list(dict.fromkeys(requested + list(resumed))): landed = _sync_table(p, table, entry, connect_day, now, legacy, table in requested, resumed.get(table), log) p.eligible.discard(table) if landed and table_name(table) not in synced: synced.append(table_name(table)) _record_tables(con, report, connections) if own and rt is not None and (synced or checkpointed): _persist(key, rt, con, list(dict.fromkeys(synced + checkpointed)), report, log) finally: if own: con.close() _after_sync(key, synced, log) return report _SEVERITY = {"skipped": -1, "done": 0, "not_offered": 1, "deferred": 2, "failed": 3} def _sync_table(p, table, entry, connect_day, now, legacy, requested, prior, log): """One table's turn in one connection's pass: run or skip it, record its state. -> True if it changed.""" con, cid, label, report = p.con, p.cid, p.label, p.report spec = schema.TABLE_SPECS[table] _ensure_table(con, table) origin_changed = _refresh_origin(con, table, cid, label, legacy) state, reason, pulled = prior or ("done", "", 0) pulled += p.pulled.pop(table, 0) if state != "done" or not requested: pass elif not spec["offered"]: state, reason = "not_offered", spec["not_offered_reason"] elif not _cadence_due(con, table, now, cid): state = "skipped" elif p.blocked: state, reason = "deferred", PREPARING_REASON p.wait(spec, reason) else: state, reason, rows = _run_windows(p, table, connect_day) pulled += rows if state == "done": _record_cadence(con, table, now, cid) if state == "done" and not reason and p.notes.get(table): reason = " ".join(p.notes[table]) if state == "deferred" and reason in (PREPARING_REASON, TIME_LIMIT_REASON): report["partial"] = True if reason: _record_limit(con, table, reason, cid, state=state) shown = f"{label}: {reason}" if cid else reason report["limits"][table] = " ".join(filter(None, (report["limits"].get(table), shown))) elif state == "done": _clear_limit(con, table, cid) total = _count(con, table) entry["tables"][table] = {"state": state, "pulled": pulled, "in_mirror": _count(con, table, cid)} if state != "skipped": amazon_store._record_state(con, _state_key(amazon_store.entity_key(_marker_table(table)), cid), "done" if state == "done" else "partial", _count(con, table, cid), state) previous = report["tables"].get(table, {}) report["tables"][table] = {"state": max((state, previous.get("state", state)), key=_SEVERITY.get), "pulled": previous.get("pulled", 0) + pulled, "in_mirror": total} log(f" {table_name(table):<36} pulled {pulled:>7} mirror total {total:>7} {state}") return bool(pulled or origin_changed) def _record_tables(con, report, connections): """Write each table's aggregate state and limit across connections, and the pass's pending count.""" for table, state in report["tables"].items(): if state["state"] == "skipped": continue amazon_store._record_state(con, amazon_store.entity_key(_marker_table(table)), "done" if state["state"] == "done" else "partial", state["in_mirror"], state["state"]) if table in report["limits"]: _record_limit(con, table, report["limits"][table], state=state["state"]) else: _clear_limit(con, table) report["pendingReports"] = sum(_read_pending(con, table, cid) is not None for cid, *_ in connections for table in TABLES) if report["partial"]: report["continueAfterSeconds"] = CONTINUE_AFTER_SECONDS def _not_offered_reason(spec, exc): return (f"Amazon Reporting v1 does not offer {spec['label']} to this advertiser " f"({str(exc)[:client.ERROR_TEXT_LIMIT]}). The table keeps the days it has. To ask for it, " f"contact Amazon Ads support and quote Amazon's message.") def _persist(tenant_key, rt, con, synced, report, log): """Save the Ads tables that landed rows, with their markers. A failure is a problem, never a raise.""" try: amazon_store.persist_mirror(tenant_key, rt, con, tuple(synced), report=report, log=log) except Exception as exc: # noqa: BLE001 report["problems"].append(f"The Amazon Ads rows landed here but could not be saved to the " f"store ({type(exc).__name__}). The next sync saves them again.") log(report["problems"][-1]) def _after_sync(tenant_key, synced, log): """C6: each callback is imported lazily; any failure is one log line and changes nothing.""" if not synced: return for target in ADS_AFTER_SYNC: try: module_name, _, name = target.partition(":") getattr(importlib.import_module(module_name), name)(tenant_key, tuple(synced)) except Exception as exc: # noqa: BLE001 log(f"Amazon Ads after sync callback {target} failed ({type(exc).__name__}); " f"the sync result is unchanged.") def status(tenant_key="royal-imports", rt=None): """Rows per Ads table in the mirror now, None when never synced. Never fetches.""" con, _path = amazon_store._connect_tenant_mirror(tenant_key, rt) out = {} try: for table in TABLES: try: out[table_name(table)] = _count(con, table) except Exception: # noqa: BLE001 out[table_name(table)] = None finally: con.close() return out STREAM_TABLE = schema.STREAM_TABLE_SPECS["sp_hourly"]["source"] STREAM_EVENTS = STREAM_TABLE + "_events" STREAM_STATE_ENTITIES = ("amzn_sync_mark_ads_sp_hourly", "amzn_ads_sp_hourly") _STREAM_LOCK = threading.Lock() _STREAM_STORES = {} def _stream_store(tenant_key, rt): """Keep stream checkpoints and locks separate from daily report publications and other tenants.""" from harness.mirror_store import MirrorStore if getattr(rt, "key", None) != tenant_key: raise ValueError("The stream runtime belongs to another tenant") key = (tenant_key, str(Path(rt.datastore_path).resolve())) with _STREAM_LOCK: if key not in _STREAM_STORES: spec = connector_spec.ConnectorMirrorSpec( tables=(STREAM_EVENTS, STREAM_TABLE), state_prefix="amzn_ads_stream_", state_entities=STREAM_STATE_ENTITIES) _STREAM_STORES[key] = MirrorStore("amazon_marketing_stream", spec) return _STREAM_STORES[key] def restore_stream(tenant_key, rt): """Restore the hourly aggregate and event ledger together before accepting new deltas.""" store = _stream_store(tenant_key, rt) result = store.restore_mirror(tenant_key, rt, log=lambda *_: None) if result.get("state") == "refused" or result.get("problems") or result.get("refused_tables"): raise RuntimeError("The stream checkpoint could not be restored") return result def _ensure_stream_tables(con): datastore._ensure_sync_state(con) for name, columns in ((STREAM_EVENTS, schema.STREAM_COLUMNS), (STREAM_TABLE, schema.STREAM_TABLE_SPECS["sp_hourly"]["columns"])): con.execute(f'CREATE TABLE IF NOT EXISTS "{name}" (' + ", ".join( f'"{column}" {schema.stream_coltype(column)}' for column in columns) + ")") con.execute(f'CREATE UNIQUE INDEX IF NOT EXISTS amzn_ads_stream_event_id ' f'ON "{STREAM_EVENTS}" (dataset_id, idempotency_id)') def _apply_stream_rows(con, rows): """Deduplicate immutable deltas before summing them at the complete common traffic and conversion grain.""" columns = schema.STREAM_COLUMNS for row in rows: previous = con.execute(f'SELECT * FROM "{STREAM_EVENTS}" WHERE dataset_id=? AND idempotency_id=?', [row["dataset_id"], row["idempotency_id"]]).fetchone() if previous: # A reused identity with changed content is corruption, not an additive correction. old = dict(zip(columns, previous)) if any(old.get(column) != row.get(column) for column in columns if column != "received_at"): raise ValueError("The stream event identity was reused with different content") continue con.execute(f'INSERT INTO "{STREAM_EVENTS}" VALUES ({",".join("?" for _ in columns)})', [row.get(column) for column in columns]) grain = schema.STREAM_GRAIN + ("_connection_id",) # Rebuild only affected grains in SQL, without joining raw traffic to raw conversions. affected = {tuple(row[column] for column in grain) for row in rows} predicate = " AND ".join(f'"{column}" IS NOT DISTINCT FROM ?' for column in grain) for values in affected: con.execute(f'DELETE FROM "{STREAM_TABLE}" WHERE {predicate}', list(values)) expressions = {column: f'"{column}"' for column in grain} expressions.update(target_id='MAX(keyword_id)', keyword_text='MAX(keyword_text)', match_type='MAX(match_type)', _row_key='?', spend='SUM(cost)', sales='SUM(attributed_sales_14d)', orders='SUM(attributed_conversions_14d)', attribution_window="'14d'", event_count='COUNT(*)', last_received_at='MAX(received_at)') expressions.update({name: f'SUM("{name}")' for name in schema.STREAM_METRICS}) key = hashlib.sha256(json.dumps(values, separators=(",", ":")).encode()).hexdigest() aggregate_columns = schema.STREAM_TABLE_SPECS["sp_hourly"]["columns"] con.execute(f'INSERT INTO "{STREAM_TABLE}" SELECT ' + ", ".join(expressions[c] for c in aggregate_columns) + f' FROM "{STREAM_EVENTS}" WHERE {predicate} GROUP BY ' + ", ".join(f'"{column}"' for column in grain), [key, *values]) def write_stream_events(tenant_key, rt, binding, rows, *, mirror_store=None): """Publish the ledger and aggregate durably before returning an acknowledgement receipt.""" from harness.amazon_marketing_stream import Binding from harness import mirror_store as mirrors if (not isinstance(binding, Binding) or tenant_key != binding.tenant or getattr(rt, "key", None) != tenant_key or not rows or any(row.get("profile_id") != binding.profile_id or row.get("advertiser_id") != binding.advertiser_id or row.get("marketplace_id") != binding.marketplace_id or row.get("_connection_id") != binding.connection_id for row in rows)): raise ValueError("Stream rows do not belong to the requested runtime and authoritative binding") store = mirror_store or _stream_store(tenant_key, rt) with store.lock: restored = store.restore_mirror(tenant_key, rt, log=lambda *_: None) if restored.get("state") == "refused" or restored.get("problems") or restored.get("refused_tables"): raise RuntimeError("Restore the durable stream checkpoint before ingesting events") published, _path = store._connect(tenant_key, rt) con, installed = None, False report = {"problems": []} try: # An event batch is a complete incremental source cohort, staged with the shared engine's primitives. con, started = mirrors._open_candidate(store, tenant_key, rt, published) _ensure_stream_tables(con) con.execute("BEGIN TRANSACTION") try: _apply_stream_rows(con, rows) stamp = datetime.now(timezone.utc).isoformat() activation = amazon_store._read_state(con, "amzn_ads_stream_first_received") if activation is None: amazon_store._record_state(con, "amzn_ads_stream_first_received", "done", detail=stamp) checkpoint = uuid.uuid4().hex amazon_store._record_state(con, "amzn_ads_stream_checkpoint", "done", detail=checkpoint) for entity in STREAM_STATE_ENTITIES: amazon_store._record_state(con, entity, "done", detail=stamp) con.execute("COMMIT") except Exception: con.execute("ROLLBACK") raise metadata = {"snapshot_id": checkpoint, "started_at": started, "completed_at": stamp, "state": "complete"} with tempfile.TemporaryDirectory(prefix="stream-publish-") as tmp: paths = mirrors._export_tables(store, con, tmp) manifest = store.persist_mirror(tenant_key, rt, con, (STREAM_EVENTS, STREAM_TABLE), report=report, log=lambda *_: None, _publication=metadata) if (report["problems"] or not isinstance(manifest, dict) or manifest.get("tenant") != tenant_key or manifest.get("snapshot_id") != checkpoint or not {STREAM_EVENTS, STREAM_TABLE} <= set(manifest.get("tables", {})) or not manifest.get("sync_state")): raise RuntimeError("The stream ledger and hourly rows did not reach a durable checkpoint") state_rows = store._state_rows(con) try: store._install_snapshot(published, manifest, state_rows, paths) installed = True store._after_publish(tenant_key, manifest) except BaseException: store.cache.pop(store._mirror_cache_key(tenant_key, rt), None) raise for listener in list(mirrors._PUBLISH_LISTENERS): try: listener(store.connector, tenant_key, manifest, state_rows, paths) notify_state = "ok" except Exception as exc: # Durable data remains recoverable when a web handoff needs retry. notify_state = "failed:" + type(exc).__name__ if getattr(store, "stream_handoff_state", "ok") != notify_state: logging.getLogger(__name__).log(logging.INFO if notify_state == "ok" else logging.WARNING, "Stream handoff tenant=%s state=%s", tenant_key, notify_state) store.stream_handoff_state = notify_state coverage = con.execute(f'SELECT MIN(time_window_start), MAX(time_window_start), COUNT(*) ' f'FROM "{STREAM_TABLE}" WHERE profile_id=?', [binding.profile_id]).fetchone() return {"durable": True, "tenant": tenant_key, "profile_id": binding.profile_id, "checkpoint": checkpoint, "coverage_start": coverage[0], "coverage_end": coverage[1], "hourly_rows": coverage[2], "first_received_at": (activation or {}).get("detail", stamp)} finally: if con is not None: con.close() published.close() if installed: Path(rt.datastore_path).with_suffix(f".{store.connector}.candidate.duckdb").unlink(missing_ok=True) def main(argv=None): ap = argparse.ArgumentParser(description="Pull Amazon Ads reports into the tenant's mirror.") ap.add_argument("--sync", action="store_true") ap.add_argument("--status", action="store_true") ap.add_argument("--tenant", default="royal-imports") a = ap.parse_args(argv) if a.status: for name, rows in status(a.tenant).items(): print(f" {name:<36} {'(never synced)' if rows is None else rows}") return 0 if not a.sync: ap.print_help() return 2 rep = sync(a.tenant) for problem in rep["problems"]: print(" PROBLEM:", problem) return 1 if rep["problems"] else 0 __all__ = ["ADS_AFTER_SYNC", "TABLES", "RESTATEMENT_DAYS", "table_name", "limit_key", "table_description", "windows", "sync", "status", "write_stream_events", "restore_stream", "STREAM_TABLE", "STREAM_EVENTS"] if __name__ == "__main__": sys.exit(main())