fsanyoto's picture
Deploy AIOS web (React glide grid + FastAPI slice)
816fa8a verified
Raw History Blame Contribute Delete
7.96 kB
# Amazon knowledge pack: what the assistant reads before it queries an Amazon database.
# loopy_skills.py serves it, filtered to the databases the caller can open.
# Amazon columns are measured from each report header at sync (amazon_schema PROVENANCE), so no
# static column list exists. Field keys here are names the code already types: amazon_schema
# TABLE_SPECS identities and relationships, connector_spec links, and amazon_relational's
# date, money and quantity name sets. verify_agent_knowledge.py gates this.
# Plain ASCII only. The assistant repeats this text to users.
source: amazon
connector: amazon_sp
title: Amazon
scope:
orders:
database: ut_amzn_orders
date_field: purchase_date
rule: "For orders by day, week or month, count rows of ut_amzn_orders grouped by the day of purchase_date. One row is one order."
cancelled:
field: order_status
values: [Cancelled]
rule: "Exclude rows whose order_status is Cancelled when you count orders or sum sales."
read_only:
rule: "Amazon data is read only. Never create, change or send anything to Amazon."
traps:
- id: field_list_first
text: "Read the database's field list before a query. A field missing from the list does not exist in this workspace yet, because Amazon columns appear at the first sync."
- id: dates_are_text
text: "Amazon dates such as purchase_date are ISO 8601 text in UTC. The day is the first 10 characters. Say the days are UTC days."
- id: currency
text: "Amounts are in the currency field of each row. Do not add amounts across currencies."
- id: sku_keys
text: "The seller SKU is sku on orders, order items, settlements and fee estimates, msku on inventory, and seller_sku on listings and referral fees. Join on these, not on the product name."
- id: amazon_vs_odoo
text: "Odoo also carries the Amazon channel as the GIFTWARE DEALS partner. For an Amazon order or fee question use these databases, not Odoo."
databases:
ut_amzn_orders:
label: Amazon orders
row: "One Amazon order, from the all orders report. The first line of the order supplies its item columns."
fields:
money: [item_price, item_tax, shipping_price]
quantity: [quantity]
date: [purchase_date, last_updated_date]
status: [order_status]
joins:
- {database: ut_amzn_order_items, from: amazon_order_id, to: amazon_order_id}
- {database: ut_amzn_settlements, from: amazon_order_id, to: order_id}
- {database: ut_amzn_listings, from: sku, to: seller_sku}
traps:
- id: orders_by_day
text: "This is the database for how many orders arrived per day. Count rows by the day of purchase_date and exclude order_status Cancelled."
- id: first_line_only
text: "item_price, quantity and sku here belong to the order's first line only. For units or sales of a whole order use ut_amzn_order_items."
- id: pending_price
text: "A Pending order has no item_price yet. Do not read a blank price as zero sales."
ut_amzn_order_items:
label: Amazon order items
row: "One line of one Amazon order: one SKU with its quantity and price."
fields:
money: [item_price, item_tax, shipping_price, item_promotion_discount]
quantity: [quantity]
date: [purchase_date, last_updated_date]
status: [order_status, item_status]
joins:
- {database: ut_amzn_orders, from: amazon_order_id, to: amazon_order_id}
- {database: ut_amzn_listings, from: sku, to: seller_sku}
traps:
- id: line_total
text: "item_price is the total for the line's quantity, not a unit price. Sum item_price for sales and quantity for units."
- id: count_distinct
text: "amazon_order_id repeats on every line of an order. Count distinct amazon_order_id for an order count, or use ut_amzn_orders."
ut_amzn_settlements:
label: Amazon settlements
row: "One line of an Amazon settlement report: one charge, fee or payment inside one payout."
fields:
money: [amount, total_amount]
quantity: [quantity_purchased]
date: [posted_date, settlement_start_date, settlement_end_date, deposit_date]
status: [amount_type, amount_description]
joins:
- {database: ut_amzn_orders, from: order_id, to: amazon_order_id}
- {database: ut_amzn_listings, from: sku, to: seller_sku}
traps:
- id: signed_amounts
text: "amount is signed. Fees and refunds are negative, so the sum of amount is net proceeds. Group by amount_type and amount_description to split sales from fees."
- id: summary_line
text: "The first line of each settlement carries total_amount and deposit_date and no order. Never add total_amount to amount."
- id: payout_date
text: "posted_date is when a line was posted. deposit_date is when the payout reached the bank. Use posted_date for activity by day."
ut_amzn_inventory:
label: Amazon inventory
row: "One product at one disposition and location for one day, from the FBA inventory ledger summary."
fields:
money: []
quantity: [starting_warehouse_balance, receipts, customer_shipments, customer_returns, ending_warehouse_balance]
date: [date]
status: []
joins:
- {database: ut_amzn_listings, from: msku, to: seller_sku}
- {database: ut_amzn_listings, from: asin, to: asin1}
traps:
- id: one_day_only
text: "Each day repeats the stock. Sum ending_warehouse_balance for one date only, never across dates."
- id: sellable
text: "Filter disposition SELLABLE for stock a customer can buy. Other dispositions are damaged or held stock."
ut_amzn_fba_fee_estimates:
label: Amazon FBA fee estimates
row: "One SKU in one Amazon store on one snapshot day, with Amazon's estimated fees per unit."
fields:
money: [estimated_fee_total, estimated_referral_fee_per_unit, expected_fulfillment_fee_per_unit, your_price]
quantity: []
date: [_snapshot_date]
status: []
joins:
- {database: ut_amzn_listings, from: sku, to: seller_sku}
- {database: ut_amzn_listings, from: asin, to: asin1}
traps:
- id: latest_snapshot
text: "Each sync adds a new _snapshot_date. Use the latest _snapshot_date for current fees."
- id: estimate_not_charge
text: "These are estimates per unit, not fees Amazon charged. Charged fees are in ut_amzn_settlements."
ut_amzn_referral_fees:
label: Amazon referral fee preview
row: "One seller SKU on one snapshot day, with its price and Amazon's referral fee preview."
fields:
money: [price]
quantity: []
date: [_snapshot_date]
status: []
joins:
- {database: ut_amzn_listings, from: seller_sku, to: seller_sku}
- {database: ut_amzn_listings, from: asin, to: asin1}
traps:
- id: latest_snapshot
text: "Each sync adds a new _snapshot_date. Use the latest _snapshot_date for current fees."
ut_amzn_storage_fees:
label: Amazon storage fees
row: "One product at one fulfillment center for one month, with its estimated monthly storage fee."
fields:
money: [estimated_monthly_storage_fee]
quantity: []
date: [month]
status: []
joins:
- {database: ut_amzn_listings, from: asin, to: asin1}
traps:
- id: per_center
text: "A product has one row per fulfillment_center each month. Sum across centers for its monthly fee."
ut_amzn_listings:
label: Amazon listings
row: "One active or inactive listing for one seller SKU."
fields:
money: [price]
quantity: [quantity]
date: [open_date]
status: []
joins:
- {database: ut_amzn_orders, from: seller_sku, to: sku}
- {database: ut_amzn_inventory, from: seller_sku, to: msku}
traps:
- id: one_snapshot
text: "Listings keeps one copy per snapshot day. Use the latest day for the current catalogue."