Governed SQL API and CLI reference

Use the /api/v1 prefix for integrations. Root paths and unversioned /api/* paths are not public routes. This table mirrors docs/QUERY_API.md, which is the canonical contract; request/response shapes and error codes live there.

Most routes return ok, status, api_version, request_id, package_id and timing_ms alongside route-specific payloads, plus warnings, errors and recovery_hints, each only when it has entries. Runtime calls may also include request_context when trusted semantic context headers are supplied.

Integrations can pass X-Request-ID, X-Semantic-Actor, X-Semantic-Tenant, X-Semantic-Project, X-Semantic-Roles, X-Semantic-Environment, and X-Semantic-Audience. The default resolver reads who is asking from these headers only; a policy_context in the request body is refused with IDENTITY_CLAIM_REFUSED.

Authentication is off by default. Set SEMANTIC_RAILS_API_KEYS or SEMANTIC_RAILS_API_KEY_FILE to require a key on protected routes, sent as Authorization: Bearer <key>, X-API-Key or X-Semantic-API-Key; GET /api/v1/health stays public. This is the self-hosted engine's runtime shim. Semantic Rails Cloud uses its own API keys and the ApiKey scheme; see Semantic Rails Cloud runtime API.

validate, compile and query take a query call: the question, naming the package's ids. Over HTTP the body is the call under query, with request options beside it in options: now (the instant relative windows resolve against), verbosity and sql_profile. Any other key beside query and options is refused. This is the call shape of engine 0.3.2; the full reference is QUERY_CALL.md and the JSON Schema is schemas/query.v2.json.

{
  "query": {
    "select": ["measure.jaffle.order_count", {"expression": "metric.sales.aov_usd", "as": "aov_usd"}],
    "group_by": ["dimension.jaffle_store_name"],
    "where": [{"field": "dimension.jaffle_store_name", "op": "not", "value": "Brooklyn"}],
    "having": [{"field": "order_count", "op": "gt", "value": 0}],
    "time": {
      "temporal_role": "temporal_role.jaffle_order_time",
      "grain": "month",
      "start": "2016-09-01",
      "end": "2016-12-01"
    },
    "order_by": [{"field": "order_count", "direction": "desc"}],
    "limit": 25
  },
  "options": {"now": "2017-09-04T12:00:00Z", "verbosity": "compact"}
}

• Filter words (op): is, not, gt, gte, lt, lte, contains, starts_with, ends_with, their not_ forms, and missing (true or false). not keeps rows whose value is unknown. There is no between: a range is two conditions, gte and lt.
• time.start is inclusive and time.end exclusive: the window is [start, end), so the example covers September through November 2016. "last": {"unit", "value"} and "this": "<unit>" are relative windows that resolve against now.
• With time.grain the answer has one row per period in a period column. A window without a grain is one total per group, with no time column.
• The previous call shape is refused at its first retired key, with where it went: {"measure": …} and {"metric": …} objects become the id string, metric_filters becomes having or an entity filter, time.range becomes time.last or start/end, and "DESC" becomes "desc". version is ignored.

POST /api/v1/compile returns rendered SQL plus semantic plan metadata. At compact verbosity, detailed plans live under explain; at full verbosity, physical_plan and performance_plan are also top-level response fields.

When a package declares exact physical rollups through model.variants: or explicit aggregate relations, the compile response shows whether a query routed to a rollup:

• logical_plan.measure_plans[].aggregate_relation_id
• physical_plan.nodes[].details.selected_relation_type
• physical_plan.nodes[].details.aggregate_relation_id
• performance_plan.aggregate_routing.selected

Missing dimensions, unsupported time grains, non-exact equivalence, non-default sources, or unsupported rollups fall back to the raw relation.

For the authoring-side root cause of each error and the field that typically triggers it, see the error code → root cause table in the Authoring Reference.

Recovery hint contracts

Errors ship structured recovery_hints so callers can repair the query without re-reading docs. The contracts below are stable.

• USE_OBJECT_SHAPE — emitted on INVALID_EXPRESSION_AST when a rolling/conversion window or prior_period offset is passed as an int instead of a {unit, value} object.
• drop_time_start then widen_time_window — emitted on WINDOWED_TIME_FILTER_UNSUPPORTED in that order. drop_time_start is the always-safe first hint; widen_time_window ships a computed suggested_start.
• FIX_QUERY_CALL — emitted on INVALID_QUERY when the call has an unknown or retired key. An unknown key is named with the key it most likely meant (filters→where, sort→order_by, measures→select); a retired key, such as metric_filters, with where it went.

Warning codes

Non-fatal signals returned in the response warnings[] array.

• PARTIAL_DATA — a window total, or a row of a day-or-coarser series, spans a whole day after window.data_through and before now's day.
• UNKNOWN_ROWS_KEPT — a not condition kept rows whose value is unknown; the message gives the missing: false condition that leaves them out.
• QUERY_SHORTHAND_NORMALIZED — a dimension. id in select was moved to group_by.
• EXPRESSION_NORMALIZED_AWAY — an input expression kind did not survive normalization, or the as: alias is missing from the compiled output. details.dropped_expression and details.compiled_kind help decide whether to retry with a different shape.
• SEMANTIC_CAVEAT_APPLIED — package-authored advisory context matched the compiled query. Caveats do not change SQL, rows, access, discovery, or policy behavior; agents should use them only when interpreting affected results.

Continue to Getting Started if you want the exact serve-and-query steps, or Guardrails for runtime limits.

Semantic Rails Cloud serves the same calls for each workspace environment at https://cloud.semantic-rails.com/v1/tenants/<workspace id>/runtime/<env>/<call>, where <env> is dev, staging or prod and <call> is discover, inspect, build-options, valid-values, plan, validate, compile or query, each a POST. Bodies are the engine's, plus package: the project name, required when the environment serves more than one project.

Send a Cloud API key as Authorization: ApiKey <your key>. Cloud reads Bearer as an OAuth sign-in token, so an API key sent as Bearer is refused with 401, and Cloud ignores X-API-Key. A key bound to an environment is refused in any other. Every call needs query:execute. Without query:sql, answers come back at minimal verbosity with no SQL, and compile or a request for SQL (another verbosity, or an sql_profile) is refused with 403.

export WORKSPACE_ID="your-workspace-id"
# Set CLOUD_API_KEY privately to a prod API key with query:execute.
curl --fail-with-body --request POST \
  "https://cloud.semantic-rails.com/v1/tenants/$WORKSPACE_ID/runtime/prod/query" \
  --header "Authorization: ApiKey $CLOUD_API_KEY" \
  --header "Content-Type: application/json" \
  --data '{"package": "my-project", "query": {"select": ["measure.jaffle.order_count"], "group_by": ["dimension.jaffle_store_name"], "time": {"grain": "month", "start": "2017-01-01", "end": "2017-04-01"}}}'

Replace my-project and the ids with your project's; discover lists them.

Limits

Runtime calls are limited per workspace, per minute, by plan, and REST and MCP calls share the same limits. On the trial and Builder plans, a workspace can make 6 validate, compile or query calls a minute (over MCP, every execute call counts here), and 60 discover, inspect, build-options, valid-values and plan calls a minute. Higher plans allow more.

Over a limit, Cloud answers HTTP 429 with code rate.limited; retry after the number of seconds in details.retry_after_seconds. A 429 can also mean too many of the workspace's calls are running at once; retry the same way.