onetick-cloud

OneTick

Compose OneTick SQL via the OneTick MCP server, then execute it via the bundled onetick-py WebAPI helper. This skill is only the residue the MCP server does not cover — auth, execution mechanics, workflow orchestration, and a handful of gotchas. Everything OneTick-specific (database catalogue, schemas, field semantics, SQL function reference) is delegated to MCP tools at runtime.

---

Architecture — read this first

There are two OneTick surfaces and they do different jobs. Never mix them up.

• OneTick MCP server (OneTick-Cloud) — discovery and composition only. Use it to find databases, list tick-type variants, look up schemas, read field semantics, and search the SQL function reference. Bulk data does not flow through MCP.
• OneTick execution (REST fast path + onetick-py wheel fallback) — execution only. The bundled helper (scripts/onetick_exec.py) runs finished SQL over Arrow, writes the full result to a Feather file, and returns a bounded preview + row-count envelope (never the whole result). By default it executes via a lightweight REST call to OneTick Cloud (sub-second per-query startup) and falls back to the onetick-py WebAPI wheel only when REST is genuinely unavailable. See Execution below. Bulk data never floods the chat loop — only the preview does.

If the MCP server is not registered in the current session, the skill cannot do discovery — stop and tell the user to register it (claude mcp add --transport http OneTick-Cloud https://mcp.cloud.onetick.com/mcp).

---

Workflow

Follow this order. Do not skip ahead — wrong-database, wrong-tick-type, and skipped-field-semantics are the three most common failure modes, and steps 1–4 prevent all of them.

1. Database discovery — call list_databases with structured filters (country, equities / futures / options / fx / indices, bars, book_depth, real_time, time_zone, mic). Prefer filters over keyword search.
2. Tick-type discovery — call db_tables(tick_type=…) with the base tick type (e.g. TRD, QTE, NBBO). Returns prefix-matching variants with descriptions, so the agent picks TRD vs TRD_1M vs TRD_1D vs TRD_ODD_LOT on evidence, not memory.
3. Schema lookup — call db_table_schemas(db_name, tick_type) for authoritative field names and types.
4. Field semantics — mandatory. For any field you are about to filter on, aggregate, or project that you have not already seen described in this session, call db_field_descriptions(tick_type, field=<FIELD_NAME>) first. This catches the ACC_VOLUME vs VOLUME confusion on TRD, the COND code-set overlap between TRD and NBBO, the per-tick-type field-name aliasing on QTE/NBBO, and similar traps before they become silently-wrong SQL. Do not skip this step on the assumption that the field name is self-explanatory — the eval shows that assumption is wrong roughly two-thirds of the time the step is skipped.
5. SQL function reference — call search_sql-docs(query=…) for any built-in function the agent is about to use (CORP_ACTIONS, MKT_ACTIVITY, TICK_SHIFT, OB_SNAPSHOT, REF_DATA, TW_AVG, window functions, etc.). Always delegate — never hardcode a signature from memory.
6. Compose SQL. Mandatory guardrails:
   • Time literals must carry an explicit timezone ('2024-01-03 12:00:00 UTC', '2024-01-03 09:00:00 America/New_York'). Bare timestamps fail or silently default to a venue-inappropriate zone.
   • TIMESTAMP does not support BETWEEN. Always express ranges as TIMESTAMP >= '<lo>' AND TIMESTAMP < '<hi>'. OneTick rejects BETWEEN on TIMESTAMP with ERR_07516820CWNEE. Applies to every tick type (TRD, QTE, *_1M, *_DAILY, DAY, …).
   • Use lower-case limit to match canonical examples (upper-case also accepted).
7. Execute via the execution helper (scripts/onetick_exec.py). The helper handles auth, the WebAPI transport, the Arrow→Feather write (full result), and the JSON envelope (preview + row count). If the helper prints an error envelope (non-zero exit) or rows: 0 on a question that expected rows, apply the retry contract below — do not loop indefinitely.