The market, in one schema.

Quickstart

Create a key in the terminal (https://ai.strasmore.com → API keys), then send a query. The key is displayed once: we store only its SHA-256 hash and cannot show it to you again.

The response is JSON. Rows come back as objects keyed by column name.

Try it — no key

Run read-only SQL against global_markets right here. This posts to https://ai.strasmore.com/api/demo/sql, the public demo endpoint: no key, no signup, 500 rows, 20s, 1 year of history, no tick trades, and 5 days of the NBBO quote tapes. It is the same gate and the same warehouse your key would query.

Authentication

Every endpoint except /v1/health takes a bearer token:

Authorization: Bearer sk_live_…

Keys resolve to an organization, and limits are applied per organization, not per key. Minting extra keys does not raise your rate limit. Revoking a key takes effect immediately.

Run a query

Execute one read-only SELECT (or WITH … SELECT) against global_markets.

Body

Response

Fetch the schema

Every table and column you can query, read from the warehouse itself at request time — so it can never drift from what your query will actually see. It reflects your tier: tables you cannot query are flagged, each table names the column its history window filters on, columns with known data defects carry a caveat, and columns whose meaning the name does not give away carry a note. plan_name beside your_tier is the pricing-page name of the slug (quotes is Pro).

Health

Liveness only. Returns {"status": "ok"}. Does not check the warehouse.

SQL restrictions

Your SQL runs under a read-only database user, behind a gate that requires:

• Exactly one statement, and it must be a SELECT or a WITH … SELECT. Multiple statements are rejected with a 400 — we will not silently execute the first and drop the rest.
• No SETTINGS clause. It is stripped: it could otherwise override the server-side caps.
• No table functions (remote, url, merge, file, …), anywhere, including inside subqueries.
• No system.*, and no database other than global_markets.
• No DDL or DML of any kind.

A semicolon inside a string literal is fine (WHERE name = 'Smith; Jones'), and so is a single trailing semicolon. The engine is ClickHouse, so its SQL dialect and functions apply.

Limits

Rate limits

Requests per minute, applied per organization. Exceeding it returns 429. Separately, each organization can run a limited number of queries at the same time; see Concurrency.

Writing fast queries

Your account can run a limited number of queries at the same time, so a query that finishes sooner gives you more throughput. The tick-level tables are very large (the options quote tape alone holds about 7.5 trillion rows), so on them the way a query is written matters most. The advice below comes from measuring real customer queries against the warehouse.

Filter on the sort order

The tick and minute tables (cache_options_quotes, cache_stocks_quotes, stocks_trades, options_trades, options_minute_aggs) are stored in one partition per day and sorted by ticker, then time. A query that names its tickers and a time range reads only the rows it needs:

WHERE ticker IN ('O:SPY230207P00400000', 'O:SPY230207P00401000')
  AND sip_timestamp >= '2023-02-03 14:30:00'
  AND sip_timestamp <  '2023-02-03 21:00:00'

• Name tickers exactly (= or IN), or by a prefix (LIKE 'O:SPY230207P%' or startsWith(ticker, 'O:SPY230207P')). On one day of options quotes, the two exact tickers above read 14 blocks of rows and the prefix reads 978.
• Do not wrap ticker in a function (substring(ticker, …)) or match it with a pattern that does not start at the beginning (LIKE '%P004%'). Either one reads every contract that traded that day: 828,045 blocks, about 6.8 billion rows.
• Always give a time range. A plain start and end on the timestamp column is the tightest form; toDate(sip_timestamp) = '2023-02-03' also skips the other days.

Ask for more in each query

Every query has a fixed start-up cost, so many small queries are slower than a few larger ones. Put all the contracts you need for a day in one query, up to the 20,000-row result limit. A full US session of 1-minute bars is 390 rows per contract, so about 50 contracts fit in one query. In our tests, one query for 20 contracts ran 3 times faster than 20 one-contract queries, and one query for 100 contracts ran about 1.5 times faster than five queries of 20.

Keep the per-row work simple

Work done for every row adds up over millions of rows. Prices are exact decimals and sizes are integers, so checks such as isFinite(toFloat64(bid_price)) or toUInt64OrNull(toString(bid_size)) never fail and only cost time; compare the columns directly. When you need several fields from the same row, such as the last quote in each minute, take them in one argMax over a tuple instead of one argMax per field. On a customer's 1-minute NBBO query, these two changes returned identical results about 20% faster:

WITH bid_price >= 0 AND ask_price >= bid_price AND bid_size >= 0 AND ask_size >= 0 AS valid
SELECT ticker,
       toStartOfMinute(sip_timestamp) AS minute,
       argMaxIf((sip_timestamp, bid_price, ask_price, bid_size, ask_size),
                (sip_timestamp, sequence_number), valid) AS last_quote,
       countIf(valid) AS quotes
FROM global_markets.cache_options_quotes
WHERE ticker IN ('O:SPY230207P00400000', 'O:SPY230207P00401000')
  AND sip_timestamp >= '2023-02-03 14:30:00' AND sip_timestamp < '2023-02-03 21:00:00'
GROUP BY ticker, minute
ORDER BY ticker, minute

Concurrency

Besides the per-minute rate limit, each organization can run a limited number of queries at the same moment. The number is not fixed: it rises and falls with how busy the warehouse is, and larger plans get a larger share. A query over the limit waits a few seconds for a free slot; if none opens, it returns 429 THROTTLED with a Retry-After header.

• Throughput is queries at once divided by seconds per query: 5 at once at 0.5 seconds each is 600 per minute, and 5 at once at 10 seconds each is 30. Faster queries raise it; more parallel requests do not.
• Start with 4 or 5 workers. More workers than your share only produce 429s.
• On a 429, wait the Retry-After seconds, then send the same request again.

Error contract

Every error is a JSON object with detail, a stable error_code and a request_id. A query that returns zero rows is a 200, not a 404 — unless it asks only for dates before your plan's history window, which is a 402.

History windows

Each plan sees a uniform depth of history across all datasets. The window is enforced server-side, on the column named by history_column in the schema catalogue.

Windows count whole calendar years, not a rolling number of days. An N-year window starts on 1 January, N years before the current year: in 2026, a 1-year window starts on 1 January 2025 and a 3-year window on 1 January 2023. The start stays fixed all year and moves forward by one year each 1 January. /v1/schema reports your exact start as history_window_start.

Schema catalogue

36 tables in global_markets, read live from the warehouse. Query them as global_markets.<table>. Tables marked paid are unavailable on the free tier.

Trade tape field guide

What the columns of stocks_trades mean, and the four things a point-in-time simulator has to get right. Every statement here was measured on the warehouse itself (mid-March sessions of every year 2003–2026, plus two full sessions), not copied from a vendor page. The same facts ride on each column as a note in GET /v1/schema.

Coverage

The consolidated SIP tape (CTA and UTP) for exchange-listed US stocks and ETFs, from 2003-09-10, every trading session since, current to the prior session with a one to two day ingest lag. Delisted names are present through their last listed session. OTC and pink-sheet prints are not included, so a post-delisting symbol such as SIVBQ returns nothing. To read a zero-row result: if stocks_daily_aggs has a bar with volume for that ticker and session and the tape has no rows, that is a gap — tell us. No bar either means the symbol did not trade on an exchange that day.

Timestamps

The SIP stamp is dissemination time: it trails the exchange stamp by a fraction of a millisecond on exchange prints and by up to seconds on late off-exchange reports. The execution time to use is therefore participant_timestamp from 2015-07-27 and sip_timestamp before — one expression covers both:

if(toUnixTimestamp64Nano(participant_timestamp) = 0, sip_timestamp, participant_timestamp) AS executed_at

Venue and tape

exchange is the reporting venue (join stocks_exchanges): 10 NYSE, 12 Nasdaq, 11 NYSE Arca, 1 NYSE American; every off-exchange (TRF) print carries 4, in every year. tape is the SIP feed that carried the print — 1 for CTA, 3 for UTP — and CTA covers both Tape A and Tape B, so SPY, IWM and GLD are stored as 1. Use exchange, not tape, to find the listing venue.

Opening and closing auctions

The condition codes are one set for the whole history (stocks_condition_codes carries the CTA/UTP letter for each and an update_rules column saying whether a code counts toward open, close, high, low and volume). What varies by year is whether the SIP sent the flag.

Two rules follow. Codes 15 and 16 are messages, not executions — they repeat the auction print's price and size — so exclude them from executions and from volume. And before mid-2009 the NYSE auction prints carry no flag at all: on exchange 10 they are the first print at or after 09:30 and the largest print after 16:00, both unflagged. Through 2020 the NYSE closing print was regularly reported one to two and a half minutes after the bell, and from 2015-07-27 the exchange's own stamp confirms that is when the auction completed, not a dissemination delay.

-- The closing auction print, NYSE-listed name, one session (the 9 and 41 flags ride along, so test with has()).
SELECT toString(toDateTime64(sip_timestamp, 3, 'America/New_York'))         AS printed_et,
       toString(toDateTime64(participant_timestamp, 3, 'America/New_York')) AS exchange_stamp_et,
       price, size, exchange, conditions
FROM global_markets.stocks_trades
WHERE ticker = 'IBM' AND exchange = 10
  AND sip_timestamp >= toDateTime('2023-03-15 15:55:00', 'America/New_York')
  AND sip_timestamp <  toDateTime('2023-03-15 16:15:00', 'America/New_York')
  AND has(conditions, 8) AND correction = 0
ORDER BY sip_timestamp

Corrections and cancellations

A 10, 11 or 12 record carries the original print's sequence_number, exchange and participant stamp, with a later sip_timestamp: the moment the amendment was disseminated. Sequence numbers restart each session, so the key is (ticker, session, sequence_number). Amendments are rare — roughly 0.001% to 0.03% of prints on a session — and the counts pair exactly. For a point-in-time replay keep the originals and apply the 10 or 12 at its own SIP time.

-- The final tape for one symbol-session: drop amended and cancelled originals, keep corrections, drop the open/close messages.
SELECT sip_timestamp, participant_timestamp, sequence_number, price, size, exchange, conditions, correction
FROM global_markets.stocks_trades
WHERE ticker = 'AAPL'
  AND sip_timestamp >= toDateTime('2023-03-15 04:00:00', 'America/New_York')
  AND sip_timestamp <  toDateTime('2023-03-15 20:00:00', 'America/New_York')
  AND correction IN (0, 12)
  AND NOT hasAny(conditions, [15, 16])
ORDER BY sip_timestamp, sequence_number

Symbol identity

ticker is the symbol as it traded on that day, never renamed after the fact: FB prints through 2022-06-08 and META from 2022-06-09. Symbols are reused across issuers with nothing on the tape to tell them apart — TWTR was Tweeter Home Entertainment until 2007-06-20 and Twitter from 2013-11-07; GM and DELL both have gaps between two different companies. There is no permanent security identifier on the tape (a CIK exists only on the SEC-derived tables). For a multi-year study, build a (ticker, first date, last date, security id) map from an external master and join on ticker and session.

Data caveats

Known defects in the underlying data. We publish them rather than let you discover them: a silently wrong number is worse than a missing one. They are also attached to the affected columns in GET /v1/schema.

Data usage