# Query the warehouse The same SQL you would run against a database connection runs over HTTPS through one endpoint. Your credential resolves server-side to your own tenant database on a read-only connection, so the isolation is structural: there is no way to query someone else's data, and no way to write. ## The endpoint ``` curl -X POST https://mcp.mixshift.io/api/query \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json" \ -d '{"sql": "SELECT COUNT(*) AS rows FROM mws_orders WHERE PurchaseDate >= ?", "params": ["2026-07-01"]}' ``` Success returns `{ "ok": true, "rows": [...], "rowCount": n, "durationMs": n }`. Failures return `{ "ok": false, "kind": "...", "friendly": "..." }`; branch on `kind`, and show `friendly` to humans. Parameters can be positional (`?` with a `params` array) or named. Pass `queryTimeoutMs` to raise the statement timeout up to the ceiling. ## Discover the schema - `GET /api/tables` lists your tables. - `GET /api/table/[name]` describes one: columns and types. AI tools connected over MCP get the same two as tools, which is why they can find their way around your schema unprompted. ## Scope `/api/query`, `/api/tables`, and `/api/table/[name]` all need the `sql:query` scope, in addition to the domain read scopes for whatever data you are touching. A service credential minted with no explicit scope list gets `sql:query` by default; if you request a specific scope list instead, add `sql:query` to it explicitly, or these three endpoints return an insufficient-scope error. Named queries below check only the domain read scopes, so you can hand a credential named-query and `/v1` access without giving it license to write its own SQL. ## Named queries: ask by id instead of carrying SQL MixShift also keeps a catalog of vetted queries on the server. You send an id and parameters; the SQL itself stays on our side. ``` curl -X POST https://mcp.mixshift.io/api/named-query \ -H "Authorization: Bearer $TOKEN" \ -H "Content-Type: application/json" \ -d '{"id": "sbd-01", "sellerIds": [123]}' ``` You get the same envelope as `/api/query`, plus two extra fields on success: - `revision`, a content hash of the query text that ran. Because the SQL can be corrected or tuned on our side without you shipping anything, this is how a result stays attributable to the exact query behind it. - `applied_params`, the names of the parameters that execution actually bound. `GET /api/named-query/ids` returns the deployed catalog with each entry's current revision, so you can diff against the ids you depend on before you ship rather than finding out through a failed call. The ids alone are also published without authentication at `/.well-known/mixshift-query-pack`. An id that is not in the deployed catalog answers `404` with kind `unknown_query`, which is the one named-query failure worth branching on: it means the id itself is wrong or has not deployed yet, not that your parameters are. ### Check `applied_params` before you call a result filtered Entry parameter schemas are deliberately tolerant. If you send a parameter the deployed entry does not declare, it is dropped and the query still runs and still returns `"ok": true`. That tolerance is what lets your integration and the platform deploy on separate schedules, but on its own it means an ignored filter is indistinguishable from an applied one. `applied_params` closes that gap. Check that a parameter you sent came back in the list before you treat the result as filtered; if it is missing, your rows are unfiltered on that dimension. It carries names only, never values, and only on success, because a failed call did not run anything. ### Sub-brand labels Some sellers run several distinct brands under a single Amazon account, separated only by a brand label on the retail and ads records. The catalog carries discovery queries that report the distinct labels on each side, how much of the catalog or ad spend carries no label yet, and how well the retail and ads labels agree with each other. The queries behind brand context accept optional retail and ads label filters, so you can read one brand on its own rather than the whole account. Both filters are optional and both default to no filter, which is exactly why `applied_params` matters here: it is what separates one brand's numbers from the whole account's. ### Sub-brand economics Three more catalog entries rank labels by dollars instead of by count: trailing 365-day revenue or ad spend per label (retail, ads, and vendor sides), a trailing 90-day slice of it, and the last date the label sold or spent, one row per label per seller. Every label is included, including ones you may go on to treat as dormant, so totals built from these entries still reconcile against Seller Central, Amazon Ads, or Vendor Central. ## The limits, and how to live with them - Results cap at **50,000 rows** or **10 MB** per response, whichever hits first, and a query can run at most **120 seconds** (60 by default). - For bigger pulls, paginate with `LIMIT`/`OFFSET` or chunk by date window. The error kinds (`too_many_rows`, `response_too_large`) tell you which cap you hit. ## Filter on the integer seller id Most seller-scoped tables carry two columns for the same seller: an integer `SellerID` and a varchar `AmazonSellerID` (the merchant token assigned by Amazon). They return the same rows, but the indexes are generally built on the integer, so filtering on `AmazonSellerID` can leave a date range with no index to seek on and scan every row for that seller. Default to `SellerID`. On `spapi_settlement`, for example, the integer is indexed with `Posted-date-time` while the token is indexed with `Settlement-id`, and on one account the identical one-month count took 13.4 seconds filtered by token versus 0.37 seconds filtered by the integer. Same answer either way, which is why a slow query here reads as a missing index rather than a filter-shape problem. If something is slower than you expect, `EXPLAIN` it and check whether the chosen key covers your date predicate. Details and the translation from token to integer: [Identify a merchant](/knowledge-base/builder-platform/reference/merchant-identifiers). ## Migrating from direct MySQL access If you used to query the warehouse over a direct MySQL connection with database credentials and IP allowlists, this endpoint is the replacement: the same SQL, without passwords to rotate or allowlists to maintain. Point your existing queries at `/api/query` and swap the connection for a Bearer token. ## Related - [Get access](/knowledge-base/builder-platform/getting-started/get-access) - [Identify a merchant: sellerId and legacySellerId](/knowledge-base/builder-platform/reference/merchant-identifiers) - [Use the /v1 data endpoints](/knowledge-base/builder-platform/how-to/use-the-v1-endpoints) (pre-shaped alternatives to raw SQL) - [Limits and guardrails](/knowledge-base/builder-platform/reference/limits-and-guardrails)