Spaces:
Running on CPU Upgrade
Running on CPU Upgrade
File size: 93,165 Bytes
82cdb59 2eae9f0 82cdb59 8cbc29f 82cdb59 ce9586e 82cdb59 ce9586e 82cdb59 2eae9f0 07a68d0 82cdb59 07a68d0 82cdb59 07a68d0 82cdb59 dec006b 82cdb59 07a68d0 82cdb59 8873406 82cdb59 d27064e 82cdb59 2eae9f0 82cdb59 8cbc29f 8729c1d 8cbc29f 82cdb59 971c29d 82cdb59 816fa8a 82cdb59 816fa8a 82cdb59 8cbc29f 82cdb59 971c29d 82cdb59 971c29d 82cdb59 8cbc29f 92127d3 82cdb59 92127d3 8873406 92127d3 8873406 82cdb59 8873406 82cdb59 2eae9f0 8cbc29f 2eae9f0 8873406 2eae9f0 ce9586e 8873406 ce9586e 92127d3 2eae9f0 92127d3 8873406 ce9586e 2eae9f0 8873406 2eae9f0 8873406 2eae9f0 8873406 2eae9f0 8cbc29f 8873406 2eae9f0 8873406 2eae9f0 103a492 82cdb59 dec006b 8cbc29f 82cdb59 2eae9f0 82cdb59 2eae9f0 8cbc29f 82cdb59 8cbc29f dec006b 82cdb59 8873406 82cdb59 8729c1d 95fb175 8cbc29f 82cdb59 2eae9f0 dec006b 82cdb59 dec006b 8873406 d27064e 8873406 103a492 82cdb59 8cbc29f d27064e 8cbc29f d27064e 8cbc29f 82cdb59 dec006b 8cbc29f 82cdb59 2eae9f0 dec006b d27064e 95fb175 2eae9f0 92127d3 2eae9f0 8133df9 92127d3 2eae9f0 816fa8a e1ea5a0 816fa8a 8729c1d dec006b 8729c1d dec006b 8729c1d e1ea5a0 dec006b e1ea5a0 2eae9f0 812ec21 dec006b 8873406 dec006b 812ec21 dec006b 8873406 dec006b 812ec21 8873406 812ec21 8873406 812ec21 8873406 812ec21 2eae9f0 dec006b 2eae9f0 dec006b 2eae9f0 8cbc29f 816fa8a 8cbc29f 816fa8a 2eae9f0 92127d3 2eae9f0 812ec21 8729c1d 92127d3 812ec21 92127d3 8729c1d 92127d3 8729c1d 2eae9f0 971c29d 2eae9f0 971c29d 2eae9f0 8729c1d 2eae9f0 971c29d 2eae9f0 dec006b 07a68d0 dec006b 07a68d0 dec006b 82cdb59 2eae9f0 82cdb59 2eae9f0 971c29d dec006b 971c29d 8cbc29f 2eae9f0 8873406 dec006b 8873406 82cdb59 2eae9f0 971c29d 2eae9f0 82cdb59 ce9586e 2eae9f0 ce9586e 8873406 ce9586e 2eae9f0 ce9586e 2eae9f0 ce9586e 2eae9f0 82cdb59 ce9586e 92127d3 ce9586e 2eae9f0 ce9586e 2eae9f0 ce9586e 8873406 92127d3 ce9586e 2eae9f0 ce9586e 92127d3 8873406 92127d3 ce9586e 92127d3 ce9586e 92127d3 8873406 92127d3 8873406 92127d3 8873406 92127d3 ce9586e 2eae9f0 8873406 2eae9f0 82cdb59 2eae9f0 82cdb59 8133df9 2eae9f0 103a492 82cdb59 8cbc29f 82cdb59 2eae9f0 82cdb59 8cbc29f 8729c1d 8cbc29f 82cdb59 2eae9f0 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 8873406 dec006b 82cdb59 2eae9f0 dec006b 2eae9f0 dec006b 8873406 07a68d0 8729c1d 2eae9f0 dec006b 2eae9f0 dec006b 2eae9f0 dec006b 07a68d0 dec006b 2eae9f0 82cdb59 2eae9f0 dec006b 8133df9 8cbc29f dec006b 8cbc29f dec006b 82cdb59 8cbc29f 103a492 dec006b 8873406 dec006b 2eae9f0 103a492 2eae9f0 dec006b 2eae9f0 82cdb59 8cbc29f 2eae9f0 971c29d 8cbc29f 971c29d 8729c1d 82cdb59 07a68d0 8729c1d 95fb175 dec006b 8873406 82cdb59 816fa8a 2eae9f0 82cdb59 2eae9f0 82cdb59 2eae9f0 82cdb59 971c29d 82cdb59 2eae9f0 82cdb59 | 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 266 267 268 269 270 271 272 273 274 275 276 277 278 279 280 281 282 283 284 285 286 287 288 289 290 291 292 293 294 295 296 297 298 299 300 301 302 303 304 305 306 307 308 309 310 311 312 313 314 315 316 317 318 319 320 321 322 323 324 325 326 327 328 329 330 331 332 333 334 335 336 337 338 339 340 341 342 343 344 345 346 347 348 349 350 351 352 353 354 355 356 357 358 359 360 361 362 363 364 365 366 367 368 369 370 371 372 373 374 375 376 377 378 379 380 381 382 383 384 385 386 387 388 389 390 391 392 393 394 395 396 397 398 399 400 401 402 403 404 405 406 407 408 409 410 411 412 413 414 415 416 417 418 419 420 421 422 423 424 425 426 427 428 429 430 431 432 433 434 435 436 437 438 439 440 441 442 443 444 445 446 447 448 449 450 451 452 453 454 455 456 457 458 459 460 461 462 463 464 465 466 467 468 469 470 471 472 473 474 475 476 477 478 479 480 481 482 483 484 485 486 487 488 489 490 491 492 493 494 495 496 497 498 499 500 501 502 503 504 505 506 507 508 509 510 511 512 513 514 515 516 517 518 519 520 521 522 523 524 525 526 527 528 529 530 531 532 533 534 535 536 537 538 539 540 541 542 543 544 545 546 547 548 549 550 551 552 553 554 555 556 557 558 559 560 561 562 563 564 565 566 567 568 569 570 571 572 573 574 575 576 577 578 579 580 581 582 583 584 585 586 587 588 589 590 591 592 593 594 595 596 597 598 599 600 601 602 603 604 605 606 607 608 609 610 611 612 613 614 615 616 617 618 619 620 621 622 623 624 625 626 627 628 629 630 631 632 633 634 635 636 637 638 639 640 641 642 643 644 645 646 647 648 649 650 651 652 653 654 655 656 657 658 659 660 661 662 663 664 665 666 667 668 669 670 671 672 673 674 675 676 677 678 679 680 681 682 683 684 685 686 687 688 689 690 691 692 693 694 695 696 697 698 699 700 701 702 703 704 705 706 707 708 709 710 711 712 713 714 715 716 717 718 719 720 721 722 723 724 725 726 727 728 729 730 731 732 733 734 735 736 737 738 739 740 741 742 743 744 745 746 747 748 749 750 751 752 753 754 755 756 757 758 759 760 761 762 763 764 765 766 767 768 769 770 771 772 773 774 775 776 777 778 779 780 781 782 783 784 785 786 787 788 789 790 791 792 793 794 795 796 797 798 799 800 801 802 803 804 805 806 807 808 809 810 811 812 813 814 815 816 817 818 819 820 821 822 823 824 825 826 827 828 829 830 831 832 833 834 835 836 837 838 839 840 841 842 843 844 845 846 847 848 849 850 851 852 853 854 855 856 857 858 859 860 861 862 863 864 865 866 867 868 869 870 871 872 873 874 875 876 877 878 879 880 881 882 883 884 885 886 887 888 889 890 891 892 893 894 895 896 897 898 899 900 901 902 903 904 905 906 907 908 909 910 911 912 913 914 915 916 917 918 919 920 921 922 923 924 925 926 927 928 929 930 931 932 933 934 935 936 937 938 939 940 941 942 943 944 945 946 947 948 949 950 951 952 953 954 955 956 957 958 959 960 961 962 963 964 965 966 967 968 969 970 971 972 973 974 975 976 977 978 979 980 981 982 983 984 985 986 987 988 989 990 991 992 993 994 995 996 997 998 999 1000 1001 1002 1003 1004 1005 1006 1007 1008 1009 1010 1011 1012 1013 1014 1015 1016 1017 1018 1019 1020 1021 1022 1023 1024 1025 1026 1027 1028 1029 1030 1031 1032 1033 1034 1035 1036 1037 1038 1039 1040 1041 1042 1043 1044 1045 1046 1047 1048 1049 1050 1051 1052 1053 1054 1055 1056 1057 1058 1059 1060 1061 1062 1063 1064 1065 1066 1067 1068 1069 1070 1071 1072 1073 1074 1075 1076 1077 1078 1079 1080 1081 1082 1083 1084 1085 1086 1087 1088 1089 1090 1091 1092 1093 1094 1095 1096 1097 1098 1099 1100 1101 1102 1103 1104 1105 1106 1107 1108 1109 1110 1111 1112 1113 1114 1115 1116 1117 1118 1119 1120 1121 1122 1123 1124 1125 1126 1127 1128 1129 1130 1131 1132 1133 1134 1135 1136 1137 1138 1139 1140 1141 1142 1143 1144 1145 1146 1147 1148 1149 1150 1151 1152 1153 1154 1155 1156 1157 1158 1159 1160 1161 1162 1163 1164 1165 1166 1167 1168 1169 1170 1171 1172 1173 1174 1175 1176 1177 1178 1179 1180 1181 1182 1183 1184 1185 1186 1187 1188 1189 1190 1191 1192 1193 1194 1195 1196 1197 1198 1199 1200 1201 1202 1203 1204 1205 1206 1207 1208 1209 1210 1211 1212 1213 1214 1215 1216 1217 1218 1219 1220 1221 1222 1223 1224 1225 1226 1227 1228 1229 1230 1231 1232 1233 1234 1235 1236 1237 1238 1239 1240 1241 1242 1243 1244 1245 1246 1247 1248 1249 1250 1251 1252 1253 1254 1255 1256 1257 1258 1259 1260 1261 1262 1263 1264 1265 1266 1267 1268 1269 1270 1271 1272 1273 1274 1275 1276 1277 1278 1279 1280 1281 1282 1283 1284 1285 1286 1287 1288 1289 1290 1291 1292 1293 1294 1295 1296 1297 1298 1299 1300 1301 1302 1303 1304 1305 1306 1307 1308 1309 1310 1311 1312 1313 1314 1315 1316 1317 1318 1319 1320 1321 1322 1323 1324 1325 1326 1327 1328 1329 1330 1331 1332 1333 1334 1335 1336 1337 1338 1339 1340 1341 1342 1343 1344 1345 1346 1347 1348 1349 1350 1351 1352 1353 1354 1355 1356 1357 1358 1359 1360 1361 1362 1363 1364 1365 1366 1367 1368 1369 1370 1371 1372 1373 1374 1375 1376 1377 1378 1379 1380 1381 1382 1383 1384 1385 1386 1387 1388 1389 1390 1391 1392 1393 1394 1395 1396 1397 1398 1399 1400 1401 1402 1403 1404 1405 1406 1407 1408 1409 1410 1411 1412 1413 1414 1415 1416 1417 1418 1419 1420 1421 1422 1423 1424 1425 1426 1427 1428 1429 1430 1431 1432 1433 1434 1435 1436 1437 1438 1439 1440 1441 1442 1443 1444 1445 1446 1447 1448 1449 1450 1451 1452 1453 1454 1455 1456 1457 1458 1459 1460 1461 1462 1463 1464 1465 1466 1467 1468 1469 1470 1471 1472 1473 1474 1475 1476 1477 1478 1479 1480 1481 1482 1483 1484 1485 1486 1487 1488 1489 1490 1491 1492 1493 1494 1495 1496 1497 1498 1499 1500 1501 1502 1503 1504 1505 1506 1507 1508 1509 1510 1511 1512 1513 1514 1515 1516 1517 1518 1519 1520 1521 1522 1523 1524 1525 1526 1527 1528 1529 1530 1531 1532 1533 1534 1535 1536 1537 1538 1539 1540 1541 1542 1543 1544 1545 1546 1547 1548 1549 1550 1551 1552 1553 1554 1555 1556 1557 1558 1559 1560 1561 1562 1563 1564 1565 1566 1567 1568 1569 1570 1571 1572 1573 1574 1575 1576 1577 1578 1579 1580 1581 1582 1583 1584 1585 1586 1587 1588 1589 1590 1591 1592 1593 1594 1595 1596 1597 1598 1599 1600 1601 1602 1603 1604 1605 1606 1607 1608 1609 1610 1611 1612 1613 1614 1615 1616 1617 1618 1619 1620 1621 1622 1623 1624 1625 1626 1627 1628 1629 1630 1631 1632 1633 1634 1635 1636 1637 1638 1639 1640 1641 1642 1643 1644 1645 1646 1647 1648 1649 1650 1651 1652 1653 1654 1655 1656 1657 1658 1659 1660 1661 1662 1663 1664 1665 1666 1667 1668 1669 1670 1671 1672 1673 1674 1675 1676 1677 1678 1679 1680 1681 1682 1683 1684 1685 1686 1687 1688 1689 1690 1691 1692 1693 1694 1695 1696 1697 1698 1699 1700 1701 1702 1703 1704 1705 1706 1707 1708 1709 1710 1711 1712 1713 1714 1715 1716 1717 1718 1719 1720 1721 1722 1723 1724 1725 1726 1727 1728 1729 1730 1731 1732 1733 1734 1735 1736 1737 1738 1739 1740 1741 1742 1743 1744 1745 1746 1747 1748 1749 1750 1751 1752 1753 1754 1755 1756 1757 | """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 _mirror_schemas(cur, tables):
rows = cur.execute(
'SELECT c.table_name, c.column_name, c.data_type, t.table_type '
'FROM information_schema.columns c JOIN information_schema.tables t '
'USING (table_catalog, table_schema, table_name) '
'WHERE c.table_catalog = current_database() AND c.table_schema = current_schema() '
'AND c.table_name IN (SELECT unnest(?::VARCHAR[])) ORDER BY c.table_name, c.ordinal_position',
[list(tables)]).fetchall()
schemas = {}
for table, name, kind, table_type in rows:
columns, _view = schemas.setdefault(table, ([], table_type == 'VIEW'))
columns.append((name, kind))
return schemas
def _missing_occurrence(source, partition, complete, predicate=''):
return ('(WITH "_amazon_missing_identity" AS MATERIALIZED '
f'(SELECT "_amazon_identity_peer".rowid AS "_identity_rowid", '
f'row_number() OVER (PARTITION BY {partition} ORDER BY "_amazon_identity_peer".rowid) - 1 '
f'AS "_identity_occurrence" FROM {source} AS "_amazon_identity_peer" '
f'WHERE {predicate}NOT ({complete})) '
'SELECT CAST("_identity_occurrence" AS VARCHAR) FROM "_amazon_missing_identity" '
f'WHERE "_identity_rowid" = {source}.rowid)')
def _grid_identity(cur, key, names, prepared, is_view):
table = key.removeprefix(PREFIX)
identity_names = names | prepared
identity = _identity_columns(table, identity_names)
source = _quoted(SOURCE[key])
peer = '"_amazon_identity_peer"'
ranks = []
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)})))"
def identity_sql(keys, label, legacy=None):
if table in amazon_store.DERIVED_TABLES:
hashed = digest([value(name) for name in keys])
return f"greatest(1, CAST(('0x' || substr(({hashed}), 1, 13)) AS BIGINT))"
complete = " AND ".join(f"{value(name)} IS NOT NULL" for name in keys)
fallback = sorted(set((*keys, *_fallback_columns(table))))
partition = ", ".join(value(name, peer) for name in fallback)
if is_view:
if legacy:
partition += f', ({peer}."_connection_id" = {legacy})'
ranks.append(f'row_number() OVER (PARTITION BY {partition} '
f'ORDER BY {peer}."_amazon_publication_order") - 1 AS {_quoted(label)}')
occurrence = f'CAST({source}.{_quoted(label)} AS VARCHAR)'
else:
peer_complete = " AND ".join(f"{value(name, peer)} IS NOT NULL" for name in keys)
predicate = f'{peer}."_connection_id" = {legacy} AND ' if legacy else ''
occurrence = _missing_occurrence(source, partition, peer_complete, predicate)
if not legacy and amazon_schema.IDENTITY_OCCURRENCE in prepared:
occurrence = f'CAST({source}.{_quoted(amazon_schema.IDENTITY_OCCURRENCE)} AS VARCHAR)'
if is_view:
ranks.pop()
fallback_digest = digest(["'__amazon_missing_identity__'", *(value(name) for name in fallback), occurrence])
hashed = digest([value(name) for name in keys])
hashed = f'CASE WHEN {complete} THEN {hashed} ELSE {fallback_digest} END'
return f"greatest(1, CAST(('0x' || substr(({hashed}), 1, 13)) AS BIGINT))"
if amazon_schema.IDENTITY_PID in prepared:
ident = f'{source}.{_quoted(amazon_schema.IDENTITY_PID)}'
else:
ident = identity_sql(identity, '_amazon_publication_occurrence')
legacy_owner = amazon_store.legacy_identity_owner(cur, table) if amazon_schema.CONNECTION_ID in prepared else None
if legacy_owner:
literal = "'" + str(legacy_owner).replace("'", "''") + "'"
old_identity = _identity_columns(table, identity_names - {amazon_schema.CONNECTION_ID})
old_ident = identity_sql(old_identity, '_amazon_publication_legacy_occurrence', literal)
ident = f'CASE WHEN {source}."_connection_id"={literal} THEN {old_ident} ELSE {ident} END'
spec = {'id': ident}
if ranks:
spec['from_sql'] = (f'(SELECT *, {", ".join(ranks)} FROM '
f'(SELECT *, row_number() OVER () - 1 AS "_amazon_publication_order" '
f'FROM {source}) AS {peer}) AS {source}')
if not is_view and table not in amazon_store.DERIVED_TABLES:
spec['page_key'] = f'{source}.rowid'
return spec
def grid_source(cur, key, *, columns=None, allow_lookups=True, status=_UNRESOLVED, schema=None):
"""Bind the canonical grid identity and fields to the admitted table or publication view."""
raw, is_view = schema if schema is not None else _mirror_schemas(cur, [SOURCE[key]]).get(SOURCE[key], ([], False))
columns = raw 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}
prepared = {name for name, _kind in raw} & amazon_schema.MIRROR_INTERNAL_COLUMNS
table = key.removeprefix(PREFIX)
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": "", "needs_excluded": False, "cols": cols,
**_grid_identity(cur, key, names, prepared, is_view)}
# 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 publication_sources(cur, keys):
"""Resolve completed raw publications once without request readiness or store discovery."""
keys = [key for key in keys if key in SOURCE]
schemas = _mirror_schemas(cur, [SOURCE[key] for key in keys])
result = {}
for key in keys:
schema = schemas.get(SOURCE[key])
if schema is None:
continue
spec = grid_source(cur, key, allow_lookups=False, status=None, schema=schema)
if amazon_schema.CONNECTION_ID in dict(schema[0]):
spec['cols'][amazon_schema.CONNECTION_ID] = ('"_connection_id"', _text)
result[key] = spec
return result
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())
|