mammoth-io SQL Generation API

openapi: 3.2.0
info:
  title: Mammoth Io SQL Generation API
  version: 0.1.0
  contact:
    name: Mammoth Analytics
    url: https://mammoth.io
    email: support@mammoth.io
  description: 'Operations tagged SQL Generation across 2 of this provider''s published API definitions: app.mammoth.io-openapi.json, mammoth-io-openapi.json. Each path carries the servers of the definition it was published in.'
servers:
- url: /api/v2
security:
- bearerAuth: []
- apiKey: []
  apiSecret: []
  workspaceId: []
tags:
- name: SQL Generation
paths:
  /workspaces/{workspace_id}/projects/{project_id}/sql_generation/condition:
    post:
      tags:
      - SQL Generation
      summary: Generate SQL condition based on intent
      description: Given the intent, generate a SQL WHERE condition for a sequence number in the pipeline (async)
      operationId: GenerateCondition
      parameters:
      - name: workspace_id
        in: path
        schema:
          type: integer
          minimum: 1.0
          title: Workspace ID
          description: Workspace refers to a collection of projects. Workspace ID is unique identifier for workspace.
          examples:
          - 4
        description: Workspace refers to a collection of projects. Workspace ID is unique identifier for workspace.
        required: true
        deprecated: false
        examples:
          workspace_id-example-1:
            summary: Sample workspace ID
            value: 4
      - name: project_id
        in: path
        schema:
          type: integer
          minimum: 1.0
          title: Project ID
          description: Project ID of the workspace
          examples:
          - 4
        description: Project ID of the workspace
        required: true
        deprecated: false
        examples:
          project_id-example-1:
            summary: Sample project ID
            value: 4
      - name: dataset_id
        in: query
        schema:
          type: integer
          minimum: 0.0
          title: Dataset ID
          description: Id of the dataset
          examples:
          - 121
        description: Id of the dataset
        required: true
        deprecated: false
        allowEmptyValue: false
        allowReserved: false
        examples:
          dataset_id-example-1:
            summary: Sample dataset ID
            value: 121
      - name: dataview_id
        in: query
        schema:
          type: integer
          minimum: 1.0
          title: Dataview ID
          description: Dataview ID of the dataset
          default: 4
          examples:
          - 4
        description: Dataview ID of the dataset
        required: false
        deprecated: false
        allowEmptyValue: false
        allowReserved: false
        examples:
          dataview_id-example-1:
            summary: Sample dataview ID
            value: 4
      requestBody:
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/ConditionGenerationSpec'
        required: true
      responses:
        '201':
          description: Document created, URL follows
          headers: {}
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ResponseJobSchema'
        '400':
          description: Failed to generate condition
        '403':
          description: Request forbidden -- authorization will not help
          content:
            application/json:
              schema:
                properties:
                  status_code:
                    type: integer
                  detail:
                    type: string
                  extra:
                    additionalProperties: {}
                    type:
                    - 'null'
                    - object
                    - array
                type: object
                required:
                - detail
                - status_code
                description: Authorization Exception
                examples:
                - status_code: 403
                  detail: Forbidden
                  extra: {}
        '200':
          description: Condition generated successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ConditionGenerationResponseEnvelope'
      deprecated: false
    servers:
    - url: /api/v2
  /workspaces/{workspace_id}/projects/{project_id}/sql_generation/expression:
    post:
      tags:
      - SQL Generation
      summary: Generate SQL expression based on intent
      description: Given the intent and mode (math/metric), generate a SQL expression for a sequence number in the pipeline (async)
      operationId: GenerateExpression
      parameters:
      - name: workspace_id
        in: path
        schema:
          type: integer
          minimum: 1.0
          title: Workspace ID
          description: Workspace refers to a collection of projects. Workspace ID is unique identifier for workspace.
          examples:
          - 4
        description: Workspace refers to a collection of projects. Workspace ID is unique identifier for workspace.
        required: true
        deprecated: false
        examples:
          workspace_id-example-1:
            summary: Sample workspace ID
            value: 4
      - name: project_id
        in: path
        schema:
          type: integer
          minimum: 1.0
          title: Project ID
          description: Project ID of the workspace
          examples:
          - 4
        description: Project ID of the workspace
        required: true
        deprecated: false
        examples:
          project_id-example-1:
            summary: Sample project ID
            value: 4
      - name: dataset_id
        in: query
        schema:
          type: integer
          minimum: 0.0
          title: Dataset ID
          description: Id of the dataset
          examples:
          - 121
        description: Id of the dataset
        required: true
        deprecated: false
        allowEmptyValue: false
        allowReserved: false
        examples:
          dataset_id-example-1:
            summary: Sample dataset ID
            value: 121
      - name: dataview_id
        in: query
        schema:
          type: integer
          minimum: 1.0
          title: Dataview ID
          description: Dataview ID of the dataset
          default: 4
          examples:
          - 4
        description: Dataview ID of the dataset
        required: false
        deprecated: false
        allowEmptyValue: false
        allowReserved: false
        examples:
          dataview_id-example-1:
            summary: Sample dataview ID
            value: 4
      requestBody:
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/ExpressionGenerationSpec'
        required: true
      responses:
        '201':
          description: Document created, URL follows
          headers: {}
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ResponseJobSchema'
        '400':
          description: Failed to generate expression
        '403':
          description: Request forbidden -- authorization will not help
          content:
            application/json:
              schema:
                properties:
                  status_code:
                    type: integer
                  detail:
                    type: string
                  extra:
                    additionalProperties: {}
                    type:
                    - 'null'
                    - object
                    - array
                type: object
                required:
                - detail
                - status_code
                description: Authorization Exception
                examples:
                - status_code: 403
                  detail: Forbidden
                  extra: {}
        '200':
          description: Expression generated successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ExpressionGenerationResponseEnvelope'
      deprecated: false
    servers:
    - url: /api/v2
  /workspaces/{workspace_id}/projects/{project_id}/sql_generation:
    post:
      tags:
      - SQL Generation
      summary: Generate SQL query based on intent
      description: Given the intent, generate SQL query for a sequence number in the pipeline (async)
      operationId: GenerateSql
      parameters:
      - name: workspace_id
        in: path
        schema:
          type: integer
          minimum: 1.0
          title: Workspace ID
          description: Workspace refers to a collection of projects. Workspace ID is unique identifier for workspace.
          examples:
          - 4
        description: Workspace refers to a collection of projects. Workspace ID is unique identifier for workspace.
        required: true
        deprecated: false
        examples:
          workspace_id-example-1:
            summary: Sample workspace ID
            value: 4
      - name: project_id
        in: path
        schema:
          type: integer
          minimum: 1.0
          title: Project ID
          description: Project ID of the workspace
          examples:
          - 4
        description: Project ID of the workspace
        required: true
        deprecated: false
        examples:
          project_id-example-1:
            summary: Sample project ID
            value: 4
      - name: dataset_id
        in: query
        schema:
          type: integer
          minimum: 0.0
          title: Dataset ID
          description: Id of the dataset
          examples:
          - 121
        description: Id of the dataset
        required: true
        deprecated: false
        allowEmptyValue: false
        allowReserved: false
        examples:
          dataset_id-example-1:
            summary: Sample dataset ID
            value: 121
      - name: dataview_id
        in: query
        schema:
          type: integer
          minimum: 1.0
          title: Dataview ID
          description: Dataview ID of the dataset
          default: 4
          examples:
          - 4
        description: Dataview ID of the dataset
        required: false
        deprecated: false
        allowEmptyValue: false
        allowReserved: false
        examples:
          dataview_id-example-1:
            summary: Sample dataview ID
            value: 4
      requestBody:
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/SQLGenerationSpec'
        required: true
      responses:
        '201':
          description: Document created, URL follows
          headers: {}
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ResponseJobSchema'
        '400':
          description: Failed to generate SQL query
        '403':
          description: Request forbidden -- authorization will not help
          content:
            application/json:
              schema:
                properties:
                  status_code:
                    type: integer
                  detail:
                    type: string
                  extra:
                    additionalProperties: {}
                    type:
                    - 'null'
                    - object
                    - array
                type: object
                required:
                - detail
                - status_code
                description: Authorization Exception
                examples:
                - status_code: 403
                  detail: Forbidden
                  extra: {}
        '200':
          description: SQL query generated successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/SQLGenerationResponseEnvelope'
      deprecated: false
    servers:
    - url: /api/v2
  /workspaces/{workspace_id}/projects/{project_id}/sql_generation/retention_policy:
    post:
      tags:
      - SQL Generation
      summary: Generate or test a retention condition WHERE clause
      description: 'mode=''generate'': LLM generates a DuckDB WHERE clause from natural language (async, returns a job). mode=''test'': runs the given WHERE clause against the dataset and returns per-batch row counts (sync).'
      operationId: RetentionCondition
      parameters:
      - name: workspace_id
        in: path
        schema:
          type: integer
          minimum: 1.0
          title: Workspace ID
          description: Workspace refers to a collection of projects. Workspace ID is unique identifier for workspace.
          examples:
          - 4
        description: Workspace refers to a collection of projects. Workspace ID is unique identifier for workspace.
        required: true
        deprecated: false
        examples:
          workspace_id-example-1:
            summary: Sample workspace ID
            value: 4
      - name: project_id
        in: path
        schema:
          type: integer
          minimum: 1.0
          title: Project ID
          description: Project ID of the workspace
          examples:
          - 4
        description: Project ID of the workspace
        required: true
        deprecated: false
        examples:
          project_id-example-1:
            summary: Sample project ID
            value: 4
      - name: dataset_id
        in: query
        schema:
          type: integer
          minimum: 0.0
          title: Dataset ID
          description: Id of the dataset
          examples:
          - 121
        description: Id of the dataset
        required: true
        deprecated: false
        allowEmptyValue: false
        allowReserved: false
        examples:
          dataset_id-example-1:
            summary: Sample dataset ID
            value: 121
      requestBody:
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/RetentionConditionSpec'
        required: true
      responses:
        '201':
          description: Document created, URL follows
          headers: {}
          content:
            application/json:
              schema:
                oneOf:
                - $ref: '#/components/schemas/ResponseJobSchema'
                - $ref: '#/components/schemas/TestConditionResult'
        '400':
          description: Bad request syntax or unsupported method
          content:
            application/json:
              schema:
                oneOf:
                - properties:
                    status_code:
                      type: integer
                    detail:
                      type: string
                    extra:
                      additionalProperties: {}
                      type:
                      - 'null'
                      - object
                      - array
                  type: object
                  required:
                  - detail
                  - status_code
                  description: Client Error
                  examples:
                  - status_code: 400
                    detail: Bad Request
                    extra: {}
                - properties:
                    status_code:
                      type: integer
                    detail:
                      type: string
                    extra:
                      additionalProperties: {}
                      type:
                      - 'null'
                      - object
                      - array
                  type: object
                  required:
                  - detail
                  - status_code
                  description: Validation Exception
                  examples:
                  - status_code: 400
                    detail: Bad Request
                    extra: {}
        '403':
          description: Request forbidden -- authorization will not help
          content:
            application/json:
              schema:
                properties:
                  status_code:
                    type: integer
                  detail:
                    type: string
                  extra:
                    additionalProperties: {}
                    type:
                    - 'null'
                    - object
                    - array
                type: object
                required:
                - detail
                - status_code
                description: Authorization Exception
                examples:
                - status_code: 403
                  detail: Forbidden
                  extra: {}
      deprecated: false
    servers:
    - url: /api/v2
components:
  schemas:
    RetentionConditionSpec:
      properties:
        mode:
          type: string
          enum:
          - generate
          - test
          title: Mode
          description: '''generate'' to create a WHERE clause via LLM; ''test'' to preview matching rows.'
        intent:
          oneOf:
          - type: string
          - type: 'null'
          title: Intent
          description: Natural language description of rows to delete. Required when mode='generate'.
          examples:
          - Remove all completed payments older than 90 days
        condition_sql:
          oneOf:
          - type: string
          - type: 'null'
          title: Condition SQL
          description: WHERE clause predicate to test. Required when mode='test'.
          examples:
          - status = 'completed' AND payment_date < CURRENT_DATE - INTERVAL '90 days'
      type: object
      required:
      - mode
      title: RetentionConditionSpec
      description: Retention condition operation — generate a WHERE clause from intent, or test an existing one.
      examples:
      - mode: generate
        intent: Remove all completed payments older than 90 days
        condition_sql: null
      - mode: test
        intent: null
        condition_sql: status = 'completed' AND payment_date < CURRENT_DATE - INTERVAL '90 days'
    ExpressionGenerationSpec:
      properties:
        params:
          $ref: '#/components/schemas/ExpressionGenerationParams'
      type: object
      required:
      - params
      title: ExpressionGenerationSpec
      description: Parameters required for intent-based expression generation
      examples:
      - params:
          sequence_number: 1
          intent: Calculate total sales tax at 10%
          mode: math
    SQLGenerationSpec:
      properties:
        params:
          $ref: '#/components/schemas/SQLGenerationParams'
      type: object
      required:
      - params
      title: SQLGenerationSpec
      description: Parameters required for intent-based SQL generation
      examples:
      - params:
          sequence_number: 1
          intent: Get all customers who made purchases in the last month
          current_query: null
    SQLGenerationParams:
      properties:
        sequence_number:
          oneOf:
          - type: integer
          - type: 'null'
          title: Sequence Number
          description: The sequence order in the pipeline
        intent:
          type: string
          title: Intent
          description: The user's intent for SQL generation
        current_query:
          oneOf:
          - type: string
          - type: 'null'
          title: Current Query
          description: The SQL the rule holds now. When given, the intent asks to change this query rather than write a new one.
      type: object
      required:
      - intent
      title: SQLGenerationParams
      description: Encapsulated SQL generation parameters
    ConditionGenerationSpec:
      properties:
        params:
          $ref: '#/components/schemas/ConditionGenerationParams'
      type: object
      required:
      - params
      title: ConditionGenerationSpec
      description: Parameters required for intent-based condition generation
      examples:
      - params:
          sequence_number: 1
          intent: Sales greater than 100 and region is West
    ExpressionGenerationParams:
      properties:
        sequence_number:
          oneOf:
          - type: integer
          - type: 'null'
          title: Sequence Number
          description: The sequence order in the pipeline
        intent:
          type: string
          title: Intent
          description: The user's intent for expression generation
        mode:
          type: string
          enum:
          - math
          - metric
          title: Mode
          description: math = row-level numeric expr, metric = aggregate 1-row-1-col expr
      type: object
      required:
      - intent
      - mode
      title: ExpressionGenerationParams
      description: Encapsulated expression generation parameters
    TestConditionResult:
      properties:
        total_rows:
          type: integer
        batches:
          items:
            $ref: '#/components/schemas/BatchConditionResult'
          type: array
      type: object
      required:
      - batches
      - total_rows
      title: TestConditionResult
    ConditionGenerationParams:
      properties:
        sequence_number:
          oneOf:
          - type: integer
          - type: 'null'
          title: Sequence Number
          description: The sequence order in the pipeline
        intent:
          type: string
          title: Intent
          description: The user's intent for condition generation
      type: object
      required:
      - intent
      title: ConditionGenerationParams
      description: Encapsulated condition generation parameters
    SQLGenerationResponseEnvelope:
      properties:
        response:
          $ref: '#/components/schemas/SQLGenerationResponseModel'
      type: object
      required:
      - response
      title: SQLGenerationResponseEnvelope
    SQLGenerationResponseModel:
      properties:
        status_code:
          type: string
        detail:
          type: string
        result:
          oneOf:
          - type: string
          - type: 'null'
        extracted_condition:
          oneOf:
          - type: string
          - type: 'null'
        interpretation:
          oneOf:
          - type: string
          - type: 'null'
      type: object
      required:
      - detail
      - status_code
      title: SQLGenerationResponseModel
    ConditionGenerationResponseEnvelope:
      properties:
        response:
          $ref: '#/components/schemas/SQLGenerationResponseModel'
      type: object
      required:
      - response
      title: ConditionGenerationResponseEnvelope
    ResponseJobSchema:
      properties:
        id:
          type: integer
        status:
          type: string
          enum:
          - success
          - failure
          - processing
        response:
          type: object
        last_updated_at:
          type: string
          format: date-time
        created_at:
          oneOf:
          - type: string
            format: date-time
          - type: 'null'
        path:
          oneOf:
          - type: string
          - type: 'null'
        operation:
          oneOf:
          - type: string
          - type: 'null'
      type: object
      required:
      - id
      - last_updated_at
      - response
      - status
      title: ResponseJobSchema
    BatchConditionResult:
      properties:
        batch_id:
          type: integer
        name:
          type: string
        row_count:
          type: integer
      type: object
      required:
      - batch_id
      - name
      - row_count
      title: BatchConditionResult
    ExpressionGenerationResponseEnvelope:
      properties:
        response:
          $ref: '#/components/schemas/SQLGenerationResponseModel'
      type: object
      required:
      - response
      title: ExpressionGenerationResponseEnvelope
  securitySchemes:
    bearerAuth:
      type: http
      description: 'Primary credential. Authorization: Bearer mm_<token>. The token is issued server-side and shown once at creation.'
      scheme: bearer
    apiKey:
      type: apiKey
      description: Deprecated — use bearerAuth.
      name: X-API-KEY
      in: header
    apiSecret:
      type: apiKey
      description: Deprecated — use bearerAuth.
      name: X-API-SECRET
      in: header
    workspaceId:
      type: apiKey
      description: Required with X-API-KEY and X-API-SECRET. Deprecated with them — use bearerAuth.
      name: X-WORKSPACE-ID
      in: header
x-refined-from:
- app.mammoth.io-openapi.json
- mammoth-io-openapi.json