thinair-co Query API

openapi: 3.2.0
info:
  title: ThinAir Data Query API
  version: 1.0.0
  description: Connection management, schema introspection, and SQL query execution against tenant-owned databases. Both interfaces (REST and MCP) consume the same service layer; this spec describes only the REST surface.
servers:
- url: https://data.thinair.co
security:
- apiKeyHeader: []
tags:
- name: Query
paths:
  /v1/query/optimize:
    post:
      summary: Analyze a SQL query and return optimization suggestions
      description: Run EXPLAIN against the resolved connection — an estimated plan; the query is not executed — parse it, and return ranked recommendations (sequential scans, nested-loop blow-ups, MySQL filesort/temp table, and on SQL Server the optimizer's missing-index statements). Costs 1 quota unit per call. Build tier (or higher) required.
      tags:
      - Query
      requestBody:
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/OptimizeRequest'
            example:
              sql: SELECT * FROM drivers WHERE email = 'a@b.co'
      responses:
        '200':
          description: 'Plan analysis. `data.recommendations` lists issues; empty + `data.verdict: ''efficient''` when nothing actionable was found.'
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/OptimizeResponse'
        '400':
          description: Invalid request body or unparseable SQL.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '401':
          description: Authentication required or failed.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '403':
          description: Tier gate — requires Build plan or higher (caller is on Discover).
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '404':
          description: Connection not found, or the tenant has no connections registered.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '429':
          description: Quota exceeded for this tenant.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '502':
          description: Upstream database unreachable (network, TLS, DNS) — no quota debited.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '503':
          description: Pool saturated or circuit breaker open — no quota debited.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
      operationId: postV1QueryOptimize
      x-operation-id-source: derived
  /v1/query:
    post:
      summary: Execute a read-only SQL query
      description: 'Runs a SELECT / WITH / EXPLAIN against the target connection. Read-only enforcement runs at three layers (sql-guard, session READ ONLY, optional per-connection firewall) below this handler — writes are rejected with `code: SYNTAX` or `PERMISSION_DENIED` and never reach the upstream DB. Costs 1 quota unit per successful call.'
      tags:
      - Query
      requestBody:
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/QueryRequest'
            example:
              query: SELECT 1
      responses:
        '200':
          description: Query result.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/QueryResponse'
        '400':
          description: Invalid body, SQL syntax error, or unknown table/column.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '401':
          description: Authentication required or upstream DB auth failed.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '403':
          description: Permission denied on the upstream DB (READ ONLY transaction, missing role grants).
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '404':
          description: No connections configured for tenant, or connection name not found.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '409':
          description: Database deadlock — safe to retry (see `retry_after_ms`).
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '429':
          description: Quota exceeded, rate limited, or pool saturated.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '502':
          description: Origin DB unreachable, refused, or TLS handshake failed.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '503':
          description: Backpressure — circuit breaker open or per-tenant queue timeout.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '504':
          description: Query exceeded the plan's `execute_timeout_ms`.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
      operationId: postV1Query
      x-operation-id-source: derived
  /v1/query/history:
    get:
      summary: List recent queries for the calling tenant
      description: Read the tenant's per-tenant query log — recent SQL with timing, row counts, error status, and the tool that emitted each entry. The ring buffer holds up to 100 entries with a 7-day TTL. **Free to call** — never debits quota and never returns 429 (a quota-exhausted user must still be able to inspect their own activity).
      tags:
      - Query
      parameters:
      - schema:
          type: integer
          minimum: 1
          maximum: 100
          description: Max entries to return (1–100). Default 20.
          example: 20
        required: false
        name: limit
        in: query
      - schema:
          type: string
          minLength: 1
          maxLength: 256
          description: Filter to entries on this connection. Matches the entry's `connectionId` (uuid) — pass the connection id, not its display name. Optional.
          example: fleet
        required: false
        name: connection
        in: query
      - schema:
          type: string
          format: date-time
          description: Drop entries older than this ISO 8601 timestamp. Useful for `?since=last_check` polling. Optional.
          example: '2026-05-05T00:00:00Z'
        required: false
        name: since_iso
        in: query
      responses:
        '200':
          description: Query history (newest-first).
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/QueryHistoryResponse'
        '400':
          description: Invalid query parameters (out-of-range `limit`, malformed `since_iso`).
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
        '401':
          description: Authentication required or failed.
          content:
            application/problem+json:
              schema:
                $ref: '#/components/schemas/ProblemDetails'
      operationId: getV1QueryHistory
      x-operation-id-source: derived
components:
  schemas:
    ProblemDetails:
      type: object
      properties:
        type:
          type: string
          format: uri
        title:
          type: string
        status:
          type: integer
        detail:
          type: string
        code:
          type: string
          description: Stable internal_code; see /docs/errors
        request_id:
          type: string
        instance:
          type: string
        retry_after_ms:
          type: integer
        upgrade_url:
          type:
          - string
          - 'null'
          format: uri
          description: Present on QUOTA_EXCEEDED (429) and TRIAL_EXPIRED (401). Where it points depends on the caller's tenant. For a tenant with no active subscription (Discover, Developer Trial, or a cancelled subscription) it is a checkout link preselecting the caller's next plan tier, tagged with `src` for the upgrade-prompt funnel. For a tenant that already has an active subscription it is the console Billing view (https://data.thinair.co/developers#billing), where the plan is changed through the Billing portal; no checkout link is issued, because a second checkout would open a second subscription. `null`/absent once there is no self-serve tier above the caller's plan.
        expired_at:
          type:
          - string
          - 'null'
          format: date-time
          description: 'TRIAL_EXPIRED only: ISO timestamp the Developer Trial key expired.'
        plan:
          type: string
          description: 'TRIAL_EXPIRED only: the plan the expired key was on (normally `dev_trial`).'
        quota:
          anyOf:
          - type: integer
          - type: string
            enum:
            - unlimited
          description: 'TRIAL_EXPIRED only: the daily query limit the expired plan carried.'
      required:
      - type
      - title
      - status
      - detail
      - code
      - request_id
      - instance
      description: RFC 7807 problem-details with ThinAir extensions (code, request_id, retry_after_ms, upgrade_url, and — TRIAL_EXPIRED only — expired_at/plan/quota).
    QueryMeta:
      type: object
      properties:
        upstream_ms:
          type: integer
          minimum: 0
          description: Wall time spent inside the service-layer call (origin DB round-trip plus pool wait).
        row_count:
          type: integer
          minimum: 0
          description: Total rows the underlying query produced (BEFORE truncation). When `truncated` is true, this is greater than `rows.length`.
        truncated:
          type: boolean
          description: True when the result was capped at `applied_limit` rows.
        dialect:
          type: string
          enum:
          - postgres
          - mysql
          - mssql
          description: Resolved connection dialect — write follow-up SQL in this dialect's syntax (PostgreSQL `LIMIT`, T-SQL `TOP N`, MySQL `LIMIT`).
        connection:
          type: string
          description: Connection name (display, not id) the query ran against.
        applied_limit:
          type: integer
          exclusiveMinimum: 0
          description: Effective `limit` after clamping (default 100, max 1000).
        cache_hit:
          type: boolean
          description: True when the result came from the per-tenant query cache rather than the database.
        tables:
          type: array
          items:
            type: string
          description: Tables referenced by the SQL (best-effort parse).
        retries:
          type: integer
          minimum: 0
          description: Transient-retry count. Omitted on first-shot success.
        total_wait_ms:
          type: integer
          minimum: 0
          description: Cumulative wall-time spent in retry backoff. Omitted when `retries` is unset.
      required:
      - upstream_ms
      - row_count
      - truncated
      - dialect
      - connection
      - applied_limit
      - tables
    QueryResponse:
      type: object
      properties:
        rows:
          type: array
          items:
            type: object
            additionalProperties: {}
          description: 'Result rows. Each row is a `{column: value}` map. `null` is used for SQL NULL; numbers, booleans, strings, arrays, and JSON objects flow through as their JS shape.'
        columns:
          type: array
          items:
            type: string
          description: Column names from the first row's keys (or `[]` when `rows` is empty). Service-layer `executeQuery` returns row dicts without a separate column list; deriving from the first row matches the MCP envelope's behavior.
        meta:
          $ref: '#/components/schemas/QueryMeta'
      required:
      - rows
      - columns
      - meta
      description: Result of a SQL execution. `rows.length` may be less than `meta.row_count` when truncation applied — clients re-issue with a higher `limit` (up to 1000) to see more.
    QueryHistoryResponse:
      type: object
      properties:
        entries:
          type: array
          items:
            $ref: '#/components/schemas/QueryHistoryEntry'
          description: Recent queries newest-first. Capped at the per-tenant ring-buffer size (100). Entries older than 7 days are dropped by the retention policy — long-term audit lives elsewhere.
        meta:
          $ref: '#/components/schemas/QueryHistoryMeta'
      required:
      - entries
      - meta
      description: Per-tenant query log. Free observability call — does not debit quota and never returns 429.
    OptimizeResponse:
      type: object
      properties:
        data:
          type: object
          additionalProperties: {}
        display:
          type: object
          additionalProperties: {}
        summary:
          type: string
        insights:
          type: array
          items:
            type: object
            additionalProperties: {}
        meta:
          type: object
          additionalProperties: {}
      required:
      - data
      - display
      - meta
      description: 'EXPLAIN-plan analysis result. `data.recommendations[]` lists each issue (sequential scan, missing index, filesort, …) with severity and a suggestion. `data.plan` includes the raw plan text only when recommendations exist; clean plans return `data.verdict: ''efficient''` plus a node-type summary instead. Mirrors the MCP `optimize_query` tool output.'
    QueryRequest:
      type: object
      properties:
        query:
          type: string
          minLength: 1
          maxLength: 50000
          description: Read-only SQL statement (SELECT / WITH / EXPLAIN only). Multiple statements not allowed; sql-guard runs additional checks before execution.
          example: SELECT id, status FROM vehicles LIMIT 10
        connection:
          type: string
          minLength: 1
          maxLength: 256
          description: Connection name or id from this tenant's inventory. Falls back to the tenant's default (first added) when omitted. Mirrors the MCP `query_sql` tool's `connection` arg.
          example: fleet
        limit:
          type: integer
          minimum: 1
          maximum: 1000
          description: Max rows to return (default 100, max 1000). Mirrors `query_sql.limit`.
          example: 100
      required:
      - query
      description: Execute a read-only SQL statement (SELECT / WITH / EXPLAIN). Read-only enforcement runs at three layers below this endpoint (sql-guard, session READ ONLY, optional per-connection firewall); writes are rejected upstream of the REST layer.
    QueryHistoryMeta:
      type: object
      properties:
        count:
          type: integer
          minimum: 0
          description: Number of entries returned (post-filter).
        applied_limit:
          type: integer
          exclusiveMinimum: 0
          description: Effective `limit` after clamping.
        connection:
          type: string
          description: Echo of the `connection` filter when the caller passed one.
        since_iso:
          type: string
          description: Echo of the `since_iso` cutoff when the caller passed one.
      required:
      - count
      - applied_limit
    OptimizeRequest:
      type: object
      properties:
        connection:
          type: string
          minLength: 1
          maxLength: 256
          description: Connection name (display) or id (uuid). When omitted, the tenant's default (first added) connection is used. Same fallback as the MCP `optimize_query` tool.
          example: fleet
        sql:
          type: string
          minLength: 1
          maxLength: 50000
          description: 'SQL query to analyze. Read-only (SELECT / WITH / EXPLAIN). The handler runs `EXPLAIN (ANALYZE false, FORMAT JSON)` on Postgres, `EXPLAIN` on MySQL, or `SET SHOWPLAN_XML ON` on SQL Server — an estimated plan in every case, with no execution — and parses it for sequential/table scans, nested-loop joins, and (MySQL) temporary tables and filesorts. Missing-index CREATE statements come from the SQL Server optimizer and are therefore mssql-only. Sort spills are NOT detected on any dialect: a spill is visible only in a plan produced by actually running the query, which this tool never does.'
          example: SELECT * FROM drivers WHERE email = 'a@b.co'
        dialect_hint:
          type: string
          enum:
          - postgres
          - mysql
          - mssql
          description: Optional dialect hint. The handler derives the actual dialect from the resolved connection — this hint is informational and reserved for future cross-dialect rewrite support. On SQL Server the handler captures the ESTIMATED plan via SET SHOWPLAN_XML ON/OFF (no query execution) and returns the same recommendation shape as Postgres/MySQL (scans, missing indexes, expensive operators).
      required:
      - sql
      description: 'Analyze a SQL query and return optimization suggestions: an estimated EXPLAIN plan summary plus ranked findings and index recommendations. It does not return a rewritten query — `original_query` comes back unchanged. Costs 1 quota unit.'
    QueryHistoryEntry:
      type: object
      properties:
        id:
          type: string
          description: Stable per-entry id (UUID v4).
        connection_id:
          type: string
          description: Connection id the query targeted (NOT the human display name).
        sql:
          type: string
          description: Original SQL — may be truncated to a redacted summary (≤ 200 chars + ellipsis when longer). The MCP `query_history` tool applies the same truncation; the limit keeps history payloads bounded.
        execution_ms:
          type: integer
          minimum: 0
        row_count:
          type: integer
          minimum: 0
        tool:
          type: string
          description: Tool that emitted the entry — `query_sql` for raw user queries, `explain_query` / `optimize_query` / etc. for the architect-tier tools.
        timestamp:
          type: string
          format: date-time
          description: Wall-clock at write time, ISO 8601 (UTC).
        tables:
          type: array
          items:
            type: string
          description: Tables referenced by the SQL (best-effort parse at write time).
        error:
          type:
          - string
          - 'null'
          description: Translated error message when the query failed; `null` on success. Failed queries are still logged so a tenant can see what didn't work.
      required:
      - id
      - connection_id
      - sql
      - execution_ms
      - row_count
      - tool
      - timestamp
      - tables
      - error
  securitySchemes:
    apiKeyHeader:
      type: apiKey
      in: header
      name: x-api-key