openapi: 3.0.3

info:
  title: RevOpsQL API
  version: "1.0.0"
  description: |
    The RevOpsQL API lets you submit RevOpsQL queries against your HubSpot portal,
    track execution progress, and retrieve results — all programmatically.

    ## Authentication
    Every request must include an API key issued from the RevOpsQL dashboard
    (*Portals → API Keys → New API Key*):
    ```
    Authorization: Bearer hs_...
    ```

    ## Plan requirement
    API access is available on **Growth, Scale, and Enterprise plans only**.
    Starter plan accounts receive a `403 plan_restriction` error on every API call.
    The *API Keys* menu is also hidden in the dashboard for Starter accounts.

    ## Rate limits
    Limits are enforced **per portal** (not per key):

    | Dimension        | Default | Env var                      |
    |------------------|---------|------------------------------|
    | Full runs / hour | 20      | `MAX_SUBMISSIONS_PER_HOUR`   |
    | Dry runs / hour  | 60      | `MAX_DRY_RUNS_PER_HOUR`      |
    | Concurrent runs  | 3       | `MAX_CONCURRENT_PER_PORTAL`  |

    Successful submission responses include `X-RateLimit-*` headers.
    A `429` response includes a `Retry-After` header (seconds).

    ## Typical workflow
    1. `POST /queries` → get `execution_id`
    2. `GET /queries/{id}` (poll every ~2 s) until `state = completed`
    3. `GET /queries/{id}/results` → get a signed S3 URL (valid 1 hour)
    4. Download the JSON file from the URL

servers:
  - url: https://revopsql.com/api
    description: Production
  - url: http://localhost:8000/api
    description: Local development

externalDocs:
  description: Full developer reference with code examples
  url: https:/revopsql.com/docs/api-reference

security:
  - bearerAuth: []

tags:
  - name: Queries
    description: Submit and track query executions
  - name: Saved queries
    description: Manage and run saved queries
  - name: Utilities
    description: Quota and schema

# ─────────────────────────────────────────────────────────────────────────────
paths:

  # ── Query execution ─────────────────────────────────────────────────────────

  /queries:
    post:
      tags: [Queries]
      summary: Submit a query
      description: |
        Enqueues a RevOpsQL query for asynchronous execution.
        Returns immediately with an `execution_id` — poll `GET /queries/{id}` for progress.
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              required: [sql]
              properties:
                sql:
                  type: string
                  maxLength: 10000
                  description: RevOpsQL query string.
                  example: "SELECT firstname, lastname, email FROM contact WHERE jobtitle = 'CTO' LIMIT 100 RECENTLY_CREATED"
                dry_run:
                  type: boolean
                  default: false
                  description: |
                    When `true`, estimates the number of matching objects without writing results to S3.
                    Uses a separate, more generous rate-limit bucket (60/h vs 20/h).
      responses:
        "202":
          description: Query accepted and enqueued.
          headers:
            X-RateLimit-Limit:
              $ref: "#/components/headers/XRateLimitLimit"
            X-RateLimit-Remaining:
              $ref: "#/components/headers/XRateLimitRemaining"
            X-RateLimit-Reset:
              $ref: "#/components/headers/XRateLimitReset"
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/SubmitResponse"
        "401": { $ref: "#/components/responses/Unauthorized" }
        "403": { $ref: "#/components/responses/Forbidden" }
        "422": { $ref: "#/components/responses/ValidationError" }
        "429": { $ref: "#/components/responses/RateLimited" }

  /queries/{execution_id}:
    get:
      tags: [Queries]
      summary: Get execution status
      description: Returns the current state and progress metrics of an execution.
      parameters:
        - $ref: "#/components/parameters/ExecutionId"
      responses:
        "200":
          description: Execution found.
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Execution"
        "401": { $ref: "#/components/responses/Unauthorized" }
        "403": { $ref: "#/components/responses/Forbidden" }
        "404": { $ref: "#/components/responses/NotFound" }

  /queries/{execution_id}/results:
    get:
      tags: [Queries]
      summary: Get results download URL
      description: |
        Returns a pre-signed S3 URL to download the execution results (JSON).
        The URL is valid for **1 hour**.
        Only available when `state = completed`.
      parameters:
        - $ref: "#/components/parameters/ExecutionId"
      responses:
        "200":
          description: Signed download URL.
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/ResultsResponse"
        "401": { $ref: "#/components/responses/Unauthorized" }
        "403": { $ref: "#/components/responses/Forbidden" }
        "404": { $ref: "#/components/responses/NotFound" }
        "409":
          description: Execution is not yet completed.
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
              example:
                error: "Execution not completed yet"
        "410":
          description: Results have expired (deleted from S3 after retention period).
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
              example:
                error: "Results have expired"

  # ── Saved queries ───────────────────────────────────────────────────────────

  /saved-queries:
    get:
      tags: [Saved queries]
      summary: List saved queries
      description: Returns all saved queries for the portal associated with the API key.
      responses:
        "200":
          description: List of saved queries (ordered by name).
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: "#/components/schemas/SavedQuery"
        "401": { $ref: "#/components/responses/Unauthorized" }
        "403": { $ref: "#/components/responses/Forbidden" }

  /saved-queries/{id}/execute:
    post:
      tags: [Saved queries]
      summary: Execute a saved query
      description: |
        Submits a saved query for execution using its stored SQL.
        Applies the same rate limits as `POST /queries`.
        The response includes the `saved_query_id` and `saved_query_name` for traceability.
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: integer
          description: ID of the saved query (from `GET /saved-queries`).
      requestBody:
        required: false
        content:
          application/json:
            schema:
              type: object
              properties:
                dry_run:
                  type: boolean
                  default: false
                  description: Run as a dry run (cost estimation only).
      responses:
        "202":
          description: Execution enqueued.
          headers:
            X-RateLimit-Limit:
              $ref: "#/components/headers/XRateLimitLimit"
            X-RateLimit-Remaining:
              $ref: "#/components/headers/XRateLimitRemaining"
            X-RateLimit-Reset:
              $ref: "#/components/headers/XRateLimitReset"
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/SavedQueryExecuteResponse"
        "401": { $ref: "#/components/responses/Unauthorized" }
        "403": { $ref: "#/components/responses/Forbidden" }
        "404": { $ref: "#/components/responses/NotFound" }
        "429": { $ref: "#/components/responses/RateLimited" }

  # ── Utilities ───────────────────────────────────────────────────────────────

  /quota:
    get:
      tags: [Utilities]
      summary: Get rate-limit quota
      description: |
        Returns the current rate-limit state for the portal:
        remaining burst budget (full runs and dry runs) and concurrent execution count.
        Useful before submitting a batch of queries.
      responses:
        "200":
          description: Current quota state.
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Quota"
        "401": { $ref: "#/components/responses/Unauthorized" }
        "403": { $ref: "#/components/responses/Forbidden" }

  /schema:
    get:
      tags: [Utilities]
      summary: Get portal HubSpot schema
      description: |
        Returns the cached HubSpot CRM schema for the portal: object types, properties,
        data types, and available associations.
        The schema is refreshed automatically by RevOpsQL on a regular schedule.
      responses:
        "200":
          description: Schema JSON (streamed directly from S3).
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Schema"
        "401": { $ref: "#/components/responses/Unauthorized" }
        "403": { $ref: "#/components/responses/Forbidden" }
        "404":
          description: Schema not yet generated for this portal.
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
              example:
                error: schema_not_found
                message: "Schema not yet generated for this portal. Trigger a schema sync from the RevOpsQL dashboard."

# ─────────────────────────────────────────────────────────────────────────────
components:

  securitySchemes:
    bearerAuth:
      type: http
      scheme: bearer
      description: API key generated from the RevOpsQL dashboard.

  # ── Parameters ──────────────────────────────────────────────────────────────

  parameters:
    ExecutionId:
      name: execution_id
      in: path
      required: true
      schema:
        type: string
        format: uuid
      description: UUID of the query execution.

  # ── Headers ─────────────────────────────────────────────────────────────────

  headers:
    XRateLimitLimit:
      description: Maximum number of submissions allowed in the current window.
      schema: { type: integer }
    XRateLimitRemaining:
      description: Number of submissions remaining in the current window.
      schema: { type: integer }
    XRateLimitReset:
      description: Unix timestamp (seconds) when the current window resets.
      schema: { type: integer }

  # ── Schemas ──────────────────────────────────────────────────────────────────

  schemas:

    SubmitResponse:
      type: object
      properties:
        execution_id:
          type: string
          format: uuid
          description: Use this ID to poll status and retrieve results.
          example: "a1b2c3d4-e5f6-7890-abcd-ef1234567890"

    Execution:
      type: object
      properties:
        id:
          type: string
          format: uuid
        state:
          type: string
          enum: [pending, in_progress, completed, error]
          description: |
            - `pending` — queued, not yet picked up by the worker
            - `in_progress` — being processed
            - `completed` — finished, results available
            - `error` — failed (see `error_message`)
        processed_counter:
          type: integer
          nullable: true
          description: Running count of objects scanned so far (updated during `in_progress`).
        objects_processed:
          type: integer
          nullable: true
          description: Total objects scanned (set on completion).
        objects_returned:
          type: integer
          nullable: true
          description: Objects matching the query (included in results).
        objects_billed:
          type: integer
          nullable: true
          description: Objects counted against the billing quota.
        api_calls_used:
          type: integer
          nullable: true
          description: Number of HubSpot API calls consumed.
        duration_ms:
          type: integer
          nullable: true
          description: Wall-clock execution time in milliseconds.
        error_message:
          type: string
          nullable: true
          description: Human-readable error description when `state = error`.
        submitted_at:
          type: string
          format: date-time
          nullable: true
        started_at:
          type: string
          format: date-time
          nullable: true
        completed_at:
          type: string
          format: date-time
          nullable: true
        results_expires_at:
          type: string
          format: date-time
          nullable: true
          description: When the result file will be deleted from S3.
      example:
        id: "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
        state: "completed"
        processed_counter: 12450
        objects_processed: 12450
        objects_returned: 348
        objects_billed: 348
        api_calls_used: 26
        duration_ms: 4812
        error_message: null
        submitted_at: "2026-05-21T09:00:00Z"
        started_at: "2026-05-21T09:00:02Z"
        completed_at: "2026-05-21T09:00:06Z"
        results_expires_at: "2026-05-28T09:00:06Z"

    ResultsResponse:
      type: object
      properties:
        url:
          type: string
          format: uri
          description: Pre-signed S3 URL. Valid for `expires_in` seconds.
        expires_in:
          type: integer
          description: Seconds until the URL expires.
          example: 3600
      example:
        url: "https://s3.example.com/results/a1b2c3d4.json?X-Amz-Signature=..."
        expires_in: 3600

    SavedQuery:
      type: object
      properties:
        id:
          type: integer
        name:
          type: string
          example: "CTOs in France"
        description:
          type: string
          nullable: true
          example: "All contacts with jobtitle CTO in France"
        query_text:
          type: string
          example: "SELECT firstname, lastname, email FROM contact WHERE jobtitle = 'CTO' AND country = 'France' LIMIT 500 RECENTLY_CREATED"
        created_at:
          type: string
          format: date-time
        updated_at:
          type: string
          format: date-time

    SavedQueryExecuteResponse:
      type: object
      properties:
        execution_id:
          type: string
          format: uuid
        saved_query_id:
          type: integer
        saved_query_name:
          type: string
      example:
        execution_id: "a1b2c3d4-e5f6-7890-abcd-ef1234567890"
        saved_query_id: 42
        saved_query_name: "CTOs in France"

    Quota:
      type: object
      properties:
        submissions:
          type: object
          description: Full-run (dry_run=false) burst budget.
          properties:
            limit:     { type: integer, example: 20 }
            remaining: { type: integer, example: 17 }
            reset_at:  { type: integer, description: "Unix timestamp of window reset.", example: 1748812345 }
        dry_runs:
          type: object
          description: Dry-run burst budget.
          properties:
            limit:     { type: integer, example: 60 }
            remaining: { type: integer, example: 58 }
            reset_at:  { type: integer, example: 1748812345 }
        concurrent:
          type: object
          description: Concurrent execution slots.
          properties:
            limit:     { type: integer, example: 3 }
            active:    { type: integer, example: 1, description: "Executions currently pending or in_progress." }
            available: { type: integer, example: 2 }
      example:
        submissions:  { limit: 20, remaining: 17, reset_at: 1748812345 }
        dry_runs:     { limit: 60, remaining: 58, reset_at: 1748812345 }
        concurrent:   { limit: 3,  active: 1,    available: 2 }

    Schema:
      type: object
      description: |
        HubSpot CRM schema for the portal. Top-level keys are object type names
        (e.g. `contact`, `company`, `deal`). Each entry lists available properties
        with their data types and labels.
      additionalProperties:
        type: object
        properties:
          label:
            type: string
          properties:
            type: object
            additionalProperties:
              type: object
              properties:
                label: { type: string }
                type:  { type: string }

    Error:
      type: object
      properties:
        error:
          type: string
          description: Machine-readable error code or short message.
        message:
          type: string
          description: Human-readable explanation (present on some errors).

    RateLimitError:
      type: object
      properties:
        error:
          type: string
          enum: [rate_limit_exceeded, concurrent_limit_exceeded]
        message:
          type: string
        retry_after:
          type: integer
          nullable: true
          description: Seconds to wait before retrying (burst limit only).
        active_count:
          type: integer
          nullable: true
          description: Current concurrent execution count (concurrent limit only).
        limit:
          type: integer
          nullable: true
          description: Concurrent execution limit (concurrent limit only).

    ValidationError:
      type: object
      properties:
        message:
          type: string
          example: "The sql field is required."
        errors:
          type: object
          additionalProperties:
            type: array
            items: { type: string }
          example:
            sql: ["The sql field is required."]

  # ── Responses ────────────────────────────────────────────────────────────────

  responses:

    Unauthorized:
      description: Missing or invalid API key.
      content:
        application/json:
          schema: { $ref: "#/components/schemas/Error" }
          examples:
            missing:
              value: { error: "Authorization: Bearer <key> required" }
            invalid:
              value: { error: "Invalid or expired API key" }

    Forbidden:
      description: |
        Valid key but insufficient access. Three possible causes:
        - Portal is inactive
        - Account is inactive or suspended
        - Account plan does not include API access (Starter plan)
      content:
        application/json:
          schema: { $ref: "#/components/schemas/Error" }
          examples:
            inactive_portal:
              value: { error: "Portal is inactive" }
            inactive_account:
              value: { error: "Account is inactive" }
            plan_restriction:
              value:
                error: plan_restriction
                message: "API access requires a Growth plan or higher. Upgrade at https://revopsql/billing."

    NotFound:
      description: Resource not found or not accessible with this API key.
      content:
        application/json:
          schema: { $ref: "#/components/schemas/Error" }

    ValidationError:
      description: Invalid request body.
      content:
        application/json:
          schema: { $ref: "#/components/schemas/ValidationError" }

    RateLimited:
      description: |
        Rate limit exceeded. Two possible causes:
        - `rate_limit_exceeded` — hourly burst limit reached. Retry after `retry_after` seconds.
        - `concurrent_limit_exceeded` — too many simultaneous executions. Wait for one to complete.
      headers:
        Retry-After:
          description: Seconds to wait (burst limit only).
          schema: { type: integer }
        X-RateLimit-Limit:
          $ref: "#/components/headers/XRateLimitLimit"
        X-RateLimit-Remaining:
          $ref: "#/components/headers/XRateLimitRemaining"
        X-RateLimit-Reset:
          $ref: "#/components/headers/XRateLimitReset"
      content:
        application/json:
          schema: { $ref: "#/components/schemas/RateLimitError" }
          examples:
            burst:
              value:
                error: rate_limit_exceeded
                message: "Submission rate limit reached for this portal. Try again later."
                retry_after: 1847
            concurrent:
              value:
                error: concurrent_limit_exceeded
                message: "Too many concurrent executions for this portal. Wait for a running query to complete."
                active_count: 3
                limit: 3
