Amazon Sync GraphQL API — LLM Reference

This document describes the GraphQL API for querying Amazon Selling Partner data synced into our system. It is optimized for consumption by AI/LLM agents that need to construct queries programmatically.

Endpoint

POST https://api.datawarehouse.jls.dev/v1/graphql

Authentication

Every request requires three auth headers (the x-hasura-* prefix is a convention of the underlying GraphQL engine):

Header	Value	Description
x-hasura-admin-secret	String	API secret. Stored in GCP Secret Manager as HASURA_ADMIN_SECRET (project nodal-seer-287420).
x-hasura-role	client	Always client. Enforces row-level security.
x-hasura-client-id	Integer	Seller/organization ID. All queries automatically scoped to this client.

Row-level security is enforced server-side. Do not include client_id filters in your GraphQL queries — the engine applies them automatically on every table.

Example cURL

curl -X POST https://api.datawarehouse.jls.dev/v1/graphql \
  -H "x-hasura-admin-secret: $API_SECRET" \
  -H "x-hasura-role: client" \
  -H "x-hasura-client-id: 7" \
  -H "Content-Type: application/json" \
  -d '{"query": "{ daily_fba_inventory(limit: 3) { seller_sku asin fulfillable } }"}'

Data Model

All data is keyed at the seller SKU level — the most granular product identifier. Each seller can have multiple SKUs mapped to the same ASIN (e.g., different conditions like New vs Refurbished, or different fulfillment methods).

Key Identifiers

Field	Description
client_id	Integer. Our internal organization/seller ID.
seller_sku	String. The seller's unique SKU for the product listing. Primary identifier for inventory data.
asin	String. Amazon Standard Identification Number. Multiple SKUs can map to the same ASIN.
fnsku	String, nullable. Fulfillment Network SKU — Amazon's internal barcode for FBA inventory.
marketplace_id	String. Amazon marketplace (e.g., ATVPDKIKX0DER for US).
condition	String, nullable. Product condition (e.g., NewItem, Refurbished).
date	Date (YYYY-MM-DD). The date the data applies to.

Tables

daily_fba_inventory

FBA (Fulfilled by Amazon) warehouse inventory. Updated every 30 minutes.

Column	Type	Description
client_id	Int	Seller ID
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
fnsku	String?	Fulfillment network SKU
condition	String?	Product condition
marketplace_id	String	Marketplace (PK)
date	Date	Sync date (PK)
quantity	Int	Total FBA supply
fulfillable	Int	Available for sale
reserved	Int	Reserved (pending orders)
unsellable	Int	Unfulfillable/damaged
inbound_working	Int	Inbound shipment being prepared
inbound_shipped	Int	In transit to FBA
researching	Int	Being received at FBA
days_of_supply	Int	Estimated days of stock remaining
updated_at	Timestamp	Last sync time

Primary Key: (client_id, seller_sku, marketplace_id, date)

daily_fbm_inventory

FBM (Fulfilled by Merchant) inventory — seller-shipped products.

Column	Type	Description
client_id	Int	Seller ID
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
fnsku	String?	Fulfillment network SKU
condition	String?	Product condition
marketplace_id	String	Marketplace (PK)
date	Date	Sync date (PK)
quantity	Int	Total quantity
fulfillable	Int	Available for sale
pending	Int	Pending quantity
updated_at	Timestamp	Last sync time

Primary Key: (client_id, seller_sku, marketplace_id, date)

daily_sc_inventory

Seller Central inventory (combined listing view with pricing).

Column	Type	Description
client_id	Int	Seller ID
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
fnsku	String?	Fulfillment network SKU
condition	String?	Product condition
marketplace_id	String	Marketplace (PK)
date	Date	Sync date (PK)
quantity	Int	Total quantity
fulfillable	Int	Available for sale
pending	Int	Pending quantity
price	Decimal	Listed price
updated_at	Timestamp	Last sync time

Primary Key: (client_id, seller_sku, marketplace_id, date)

daily_shipping_inventory

FBA inbound shipping inventory (in-transit and receiving).

Column	Type	Description
client_id	Int	Seller ID
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
fnsku	String?	Fulfillment network SKU
condition	String?	Product condition
marketplace_id	String	Marketplace (PK)
date	Date	Sync date (PK)
quantity	Int	Total quantity in pipeline
in_transit	Int	Shipped, in transit to FBA
receiving	Int	At FBA, being received
checked_in	Int	Checked in, being stowed
updated_at	Timestamp	Last sync time

Primary Key: (client_id, seller_sku, marketplace_id, date)

daily_sales

Daily sales aggregated by SKU. Covers FBA shipments.

Column	Type	Description
client_id	Int	Seller ID
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
fnsku	String?	Fulfillment network SKU
marketplace_id	String	Marketplace (PK)
date	Date	Sales date (PK)
units_sold	Int	Units shipped
units_refunded	Int	Units refunded
revenue	Decimal	Total revenue (price * qty)
refund_amount	Decimal	Total refund amount
avg_selling_price	Decimal	Revenue / units_sold
updated_at	Timestamp	Last sync time

Primary Key: (client_id, seller_sku, marketplace_id, date)

daily_orders_summary

Daily order counts aggregated by SKU.

Column	Type	Description
client_id	Int	Seller ID
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
marketplace_id	String	Marketplace (PK)
date	Date	Order date (PK)
order_count	Int	Total orders
shipped_count	Int	Orders shipped
cancelled_count	Int	Orders cancelled
pending_count	Int	Orders pending
updated_at	Timestamp	Last sync time

Primary Key: (client_id, seller_sku, marketplace_id, date)

order_items

Individual order line items with full detail.

Column	Type	Description
order_id	String	Amazon order ID (PK)
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
client_id	Int	Seller ID
marketplace_id	String	Marketplace
purchase_date	Timestamp	Order date
order_status	String	Status (Shipped, Pending, Cancelled, etc.)
fulfillment_channel	String	AFN (FBA) or MFN (FBM)
item_price	Decimal	Item price
item_tax	Decimal	Tax amount
shipping_price	Decimal	Shipping charged
quantity	Int	Quantity ordered
ship_city	String	Shipping city
ship_state	String	Shipping state
ship_postal_code	String	Shipping zip
ship_country	String	Shipping country
updated_at	Timestamp	Last sync time

Primary Key: (order_id, seller_sku)

inventory_snapshots

Point-in-time snapshots of all inventory data. Partitioned by month.

Column	Type	Description
id	BigInt	Auto-increment ID
client_id	Int	Seller ID
seller_sku	String?	Seller's SKU
asin	String	Amazon product ID
marketplace_id	String	Marketplace
synced_at	Timestamp	Snapshot timestamp
inventory_type	String	fba, fbm, sc, or shipping
data	JSONB	Full inventory record at that point in time

returns

FBA customer returns. Updated daily.

Column	Type	Description
client_id	Int	Seller ID
order_id	String	Amazon order ID (PK)
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
marketplace_id	String	Marketplace
return_date	Date	Date of return (PK)
fnsku	String?	Fulfillment network SKU
quantity	Int	Units returned
reason	String?	Return reason
disposition	String?	Item disposition after return
status	String?	Return status
updated_at	Timestamp	Last sync time

Primary Key: (client_id, order_id, seller_sku, return_date)

reimbursements

FBA reimbursements for lost/damaged inventory. Updated daily.

Column	Type	Description
client_id	Int	Seller ID
reimbursement_id	String	Reimbursement ID (PK)
seller_sku	String	Seller's SKU (PK)
approval_date	Date	Date approved
asin	String?	Amazon product ID
marketplace_id	String	Marketplace
fnsku	String?	Fulfillment network SKU
reason	String?	Reimbursement reason
quantity	Int	Units reimbursed
amount	Decimal	Reimbursement amount
currency	String?	Currency code
updated_at	Timestamp	Last sync time

Primary Key: (client_id, reimbursement_id, seller_sku)

daily_fba_fees

Estimated FBA fee breakdown per SKU. Updated daily.

Column	Type	Description
client_id	Int	Seller ID
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
marketplace_id	String	Marketplace (PK)
date	Date	Fee date (PK)
estimated_fee	Decimal	Total estimated FBA fee
estimated_referral_fee	Decimal	Referral fee
estimated_variable_closing_fee	Decimal	Variable closing fee
estimated_order_handling_fee	Decimal	Order handling fee
estimated_pick_pack_fee	Decimal	Pick & pack fee
estimated_weight_handling_fee	Decimal	Weight handling fee
currency	String?	Currency code
updated_at	Timestamp	Last sync time

Primary Key: (client_id, seller_sku, marketplace_id, date)

inventory_ledger

Inventory balance ledger showing movement in/out. Updated every 3 hours.

Column	Type	Description
client_id	Int	Seller ID
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
marketplace_id	String	Marketplace (PK)
date	Date	Ledger date (PK)
disposition	String	SELLABLE or UNSELLABLE (PK)
fnsku	String?	Fulfillment network SKU
starting_warehouse_balance	Int	Opening balance
receipts	Int	Received units
customer_shipments	Int	Shipped to customers
customer_returns	Int	Returned by customers
vendor_returns	Int	Returned to vendor
warehouse_transfer_in_out	Int	Inter-warehouse transfers
found	Int	Found during reconciliation
lost	Int	Lost during reconciliation
damaged	Int	Damaged units
disposed	Int	Disposed units
other_events	Int	Other adjustments
ending_warehouse_balance	Int	Closing balance
updated_at	Timestamp	Last sync time

Primary Key: (client_id, seller_sku, marketplace_id, date, disposition)

financial_events

Financial charges, fees, refunds per order. Updated daily. Uses auto-increment ID (append-only).

Column	Type	Description
id	BigInt	Auto-increment ID
client_id	Int	Seller ID
event_type	String	ShipmentEvent, RefundEvent, ServiceFee, Adjustment:*
posted_date	Timestamp	Date event was posted
order_id	String?	Amazon order ID
seller_sku	String?	Seller's SKU
asin	String?	Amazon product ID
marketplace_id	String?	Marketplace
amount	Decimal?	Charge/fee amount
amount_type	String?	Charge type (Principal, Tax, FBA fee type, etc.)
currency	String?	Currency code
updated_at	Timestamp	Last sync time

Primary Key: (id) — auto-increment bigserial

daily_product_pricing

Competitive pricing data per SKU. Updated daily.

Column	Type	Description
client_id	Int	Seller ID
seller_sku	String	Seller's SKU (PK)
asin	String	Amazon product ID
marketplace_id	String	Marketplace (PK)
date	Date	Pricing date (PK)
listing_price	Decimal?	Your listing price
shipping_price	Decimal?	Shipping price
buy_box_price	Decimal?	Buy Box winning price
lowest_fba_price	Decimal?	Lowest FBA offer
lowest_fbm_price	Decimal?	Lowest FBM offer
number_of_offers	Int?	Total competing offers
is_buy_box_winner	Boolean?	Whether you hold the Buy Box
currency	String?	Currency code
updated_at	Timestamp	Last sync time

Primary Key: (client_id, seller_sku, marketplace_id, date)

settlements

Settlement report line items. Updated daily. Uses auto-increment ID (append-only).

Column	Type	Description
id	BigInt	Auto-increment ID
client_id	Int	Seller ID
settlement_id	String	Settlement batch ID
settlement_start_date	Timestamp?	Settlement period start
settlement_end_date	Timestamp?	Settlement period end
order_id	String?	Related order ID
seller_sku	String?	Seller's SKU
marketplace_id	String?	Marketplace
amount_type	String?	Amount type (ItemPrice, Promotion, etc.)
amount_description	String?	Description
amount	Decimal?	Amount
currency	String?	Currency code
updated_at	Timestamp	Last sync time

Primary Key: (id) — auto-increment bigserial

removals

FBA removal/disposal orders. Updated daily.

Column	Type	Description
client_id	Int	Seller ID
order_id	String	Removal order ID (PK)
seller_sku	String	Seller's SKU (PK)
asin	String?	Amazon product ID
marketplace_id	String	Marketplace
fnsku	String?	Fulfillment network SKU
disposition	String?	Item disposition
removal_reason	String?	Reason for removal
requested_quantity	Int	Requested units
shipped_quantity	Int	Shipped units
status	String?	Order status
request_date	Date?	Date requested
updated_at	Timestamp	Last sync time

Primary Key: (client_id, order_id, seller_sku)

catalog

Product catalog data (title, brand, dimensions, images). Updated daily.

Column	Type	Description
client_id	Int	Seller ID (PK)
asin	String	Amazon product ID (PK)
marketplace_id	String	Marketplace (PK)
title	Text?	Product title
brand	String?	Brand name
category	String?	Product category
product_type	String?	Product type classification
image_url	Text?	Main image URL
height	Decimal?	Product height
width	Decimal?	Product width
length	Decimal?	Product length
weight	Decimal?	Product weight
dimension_unit	String?	Unit for dimensions
weight_unit	String?	Unit for weight
updated_at	Timestamp	Last sync time

Primary Key: (client_id, asin, marketplace_id)

daily_sales_traffic

Sales and traffic metrics per ASIN. Updated daily.

Column	Type	Description
client_id	Int	Seller ID
asin	String	Amazon product ID (PK)
marketplace_id	String	Marketplace (PK)
date	Date	Traffic date (PK)
page_views	Int	Product page views
sessions	Int	Unique visitor sessions
units_ordered	Int	Units ordered
ordered_product_sales	Decimal	Revenue from orders
buy_box_percentage	Decimal?	Buy Box win rate (%)
unit_session_percentage	Decimal?	Conversion rate (%)
updated_at	Timestamp	Last sync time

Primary Key: (client_id, asin, marketplace_id, date)

seller_performance

Seller health metrics. Updated daily.

Column	Type	Description
client_id	Int	Seller ID
marketplace_id	String	Marketplace (PK)
date	Date	Metric date (PK)
metric_name	String	Metric identifier (PK)
metric_value	Decimal?	Metric value
updated_at	Timestamp	Last sync time

Primary Key: (client_id, marketplace_id, date, metric_name)

Metric names: late_shipment_rate, pre_fulfillment_cancel_rate, on_time_delivery_rate, order_defect_rate_afn, order_defect_rate_mfn, valid_tracking_rate, invoice_defect_rate, account_health_rating

promotions

Promotion performance data. Updated daily.

Column	Type	Description
client_id	Int	Seller ID
promotion_id	String	Promotion ID (PK)
marketplace_id	String	Marketplace
promotion_type	String?	LIGHTNING_DEAL, BEST_DEAL, etc.
start_date	Date?	Promotion start
end_date	Date?	Promotion end
seller_sku	String?	Seller's SKU
asin	String	Amazon product ID (PK)
units_sold	Int	Units sold during promo
revenue	Decimal	Revenue during promo
spend	Decimal	Promotion spend
updated_at	Timestamp	Last sync time

Primary Key: (client_id, promotion_id, asin)

replacements

FBA replacement shipments. Updated daily.

Column	Type	Description
client_id	Int	Seller ID
order_id	String	Replacement order ID (PK)
seller_sku	String	Seller's SKU (PK)
asin	String?	Amazon product ID
marketplace_id	String	Marketplace
replacement_reason	String?	Reason for replacement
quantity	Int	Units replaced
replacement_date	Date?	Date of replacement
updated_at	Timestamp	Last sync time

Primary Key: (client_id, order_id, seller_sku)

Query Patterns

Basic Queries

Every table supports: filtering (where), sorting (order_by), pagination (limit, offset), and aggregation (_aggregate).

Get current FBA inventory for a client

query {
  daily_fba_inventory(
    where: { client_id: { _eq: 7 }, date: { _eq: "2026-03-28" } }
    order_by: { fulfillable: desc }
  ) {
    seller_sku
    asin
    fulfillable
    reserved
    inbound_shipped
    days_of_supply
  }
}

Get sales for a specific ASIN (aggregated across all SKUs)

query {
  daily_sales_aggregate(
    where: {
      client_id: { _eq: 7 }
      asin: { _eq: "B001234" }
      date: { _gte: "2026-03-01", _lte: "2026-03-28" }
    }
  ) {
    aggregate {
      sum {
        units_sold
        revenue
        units_refunded
        refund_amount
      }
    }
  }
}

Get sales broken down by SKU for an ASIN

query {
  daily_sales(
    where: {
      client_id: { _eq: 7 }
      asin: { _eq: "B001234" }
      date: { _gte: "2026-03-01" }
    }
    order_by: { date: desc }
  ) {
    seller_sku
    condition
    date
    units_sold
    revenue
    avg_selling_price
  }
}

Get order details for a specific order

query {
  order_items(
    where: { order_id: { _eq: "111-2345678-9012345" } }
  ) {
    order_id
    seller_sku
    asin
    purchase_date
    order_status
    item_price
    quantity
    fulfillment_channel
  }
}

Get total inventory across all warehouse types for a client

query GetTotalInventory($clientId: Int!, $date: date!) {
  fba: daily_fba_inventory_aggregate(
    where: { client_id: { _eq: $clientId }, date: { _eq: $date } }
  ) {
    aggregate { sum { fulfillable reserved unsellable } }
  }
  fbm: daily_fbm_inventory_aggregate(
    where: { client_id: { _eq: $clientId }, date: { _eq: $date } }
  ) {
    aggregate { sum { fulfillable pending } }
  }
  shipping: daily_shipping_inventory_aggregate(
    where: { client_id: { _eq: $clientId }, date: { _eq: $date } }
  ) {
    aggregate { sum { in_transit receiving checked_in } }
  }
}

Get low-stock SKUs (less than 7 days of supply)

query {
  daily_fba_inventory(
    where: {
      client_id: { _eq: 7 }
      date: { _eq: "2026-03-28" }
      days_of_supply: { _lt: 7 }
      fulfillable: { _gt: 0 }
    }
    order_by: { days_of_supply: asc }
  ) {
    seller_sku
    asin
    fulfillable
    days_of_supply
    inbound_shipped
  }
}

Get order volume trends (last 30 days, grouped by date)

query {
  daily_orders_summary(
    where: {
      client_id: { _eq: 7 }
      date: { _gte: "2026-02-26" }
    }
    order_by: { date: asc }
  ) {
    date
    seller_sku
    asin
    order_count
    shipped_count
    cancelled_count
  }
}

Aggregate orders by date across all SKUs

query {
  daily_orders_summary_aggregate(
    where: {
      client_id: { _eq: 7 }
      date: { _gte: "2026-02-26" }
    }
  ) {
    aggregate {
      sum {
        order_count
        shipped_count
        cancelled_count
      }
    }
  }
}

Get recent orders with shipping info

query {
  order_items(
    where: {
      client_id: { _eq: 7 }
      purchase_date: { _gte: "2026-03-21T00:00:00Z" }
      order_status: { _eq: "Shipped" }
    }
    order_by: { purchase_date: desc }
    limit: 50
  ) {
    order_id
    seller_sku
    asin
    purchase_date
    item_price
    quantity
    ship_state
    ship_country
  }
}

Get inventory snapshots over time for trend analysis

query {
  inventory_snapshots(
    where: {
      client_id: { _eq: 7 }
      asin: { _eq: "B001234" }
      inventory_type: { _eq: "fba" }
      synced_at: { _gte: "2026-03-25T00:00:00" }
    }
    order_by: { synced_at: asc }
  ) {
    seller_sku
    synced_at
    data
  }
}

Filtering Operators

Operator	Meaning	Example
_eq	Equals	{ date: { _eq: "2026-03-28" } }
_neq	Not equals	{ order_status: { _neq: "Cancelled" } }
_gt / _gte	Greater than / or equal	{ fulfillable: { _gt: 0 } }
_lt / _lte	Less than / or equal	{ days_of_supply: { _lt: 7 } }
_in	In list	{ asin: { _in: ["B001", "B002"] } }
_like / _ilike	Pattern match / case-insensitive	{ seller_sku: { _ilike: "WIDGET%" } }
_is_null	Is null	{ fnsku: { _is_null: false } }

Aggregation Functions

Available on all _aggregate queries:

Function	Fields
count	Any column or *
sum	Numeric columns
avg	Numeric columns
max / min	Any column

Pagination

query {
  order_items(
    where: { client_id: { _eq: 7 } }
    order_by: { purchase_date: desc }
    limit: 100
    offset: 200
  ) {
    order_id
    asin
    purchase_date
  }
}

Relationships (Joins)

From daily_fba_inventory, you can traverse to related data:

• fbm_inventory → daily_fbm_inventory (same client + ASIN + date)
• sc_inventory → daily_sc_inventory (same client + ASIN + date)
• shipping_inventory → daily_shipping_inventory (same client + ASIN + date)
• sales → daily_sales (same client + ASIN + date)
• orders_summary → daily_orders_summary (same client + ASIN + date)

From daily_sales:

• fba_inventory → daily_fba_inventory
• orders_summary → daily_orders_summary

Sync Schedule

Data Type	Cadence	Source
FBA/FBM/SC Inventory	Every 30 min	SP-API Reports
Shipping Inventory	Hourly	FBA Inventory API
Sales	Every 30 min	SP-API Reports (last 30 days)
Orders	Every 30 min	SP-API Reports (last 30 days)
Inventory Snapshots	Every sync cycle	Derived from above
Inventory Ledger	Every 3 hours	SP-API Reports
Returns	Unavailable	Pending SP-API permission approval
Reimbursements	Unavailable	Pending SP-API permission approval
FBA Fees	Daily	SP-API Reports
Financial Events	Daily	Finances API (last 30 days)
Product Pricing	Daily	Product Pricing API
Settlements	Daily	SP-API Reports (auto-generated)
Removals	Daily	SP-API Reports
Catalog	Daily	Catalog Items API
Sales Traffic	Daily	SP-API Reports
Seller Performance	Daily	SP-API Reports
Promotions	Daily	SP-API Reports
Replacements	Unavailable	Pending SP-API permission approval

Important Notes

1. SKU vs ASIN: Data is stored at SKU level. A single ASIN can have multiple SKUs (different conditions, fulfillment types). Use _aggregate queries grouped by ASIN when you need ASIN-level totals.
2. Date ranges: Sales and orders reports cover the last 30 days. Inventory reports return current-day data.
3. Inventory tables upsert same-day data on each sync — row counts don't grow intra-day, but values update.
4. Snapshots grow with every sync cycle and capture point-in-time state.
5. updated_at indicates when the row was last synced, not when the underlying Amazon data changed.
6. financial_events and settlements use auto-increment IDs — they are append-only tables. Financial events are replaced transactionally for each sync window.
7. seller_performance stores metrics as name/value pairs. Query with metric_name filter (e.g., late_shipment_rate, order_defect_rate_afn).
8. returns, reimbursements, replacements — these tables exist in the schema but are currently empty. Our SP-API application does not yet have the required permissions to access the underlying Amazon report types. Syncing is disabled until permissions are approved. Do not query these tables expecting data.