DPF Query API

openapi: 3.2.0
info:
  title: DPF Query API
  version: 1.1.0
  description: '## Common error responses


    Unless otherwise noted, any authenticated endpoint may also return the following

    responses, each with the standard `ErrorResponse` body. To avoid repetition they are

    not listed individually on each operation:


    - **400 Bad Request** — malformed JSON, unknown action, or missing/invalid fields

    - **401 Unauthorized** — missing or invalid JWT bearer token

    - **402 Payment Required** — the account has insufficient credits or an inactive subscription

    - **403 Forbidden** — authenticated but not permitted (no access to the resource, or a higher access level / plan tier is required)


    Operation-specific responses (e.g. `404`, `409`) are documented on each endpoint.'
  contact:
    name: Contact DPF
    url: mailto:contact@dpf-it.com
  license:
    name: Proprietary
    url: https://dpf-it.com/#terms
servers:
- url: https://api.dpf-it.com
  description: Production API
security: []
tags:
- name: Query
  description: 'DuckDB-powered SQL query interface for executing analytical queries against Iceberg tables.

    Queries are scoped to a single authorized namespace per request.'
paths:
  /query:
    post:
      tags:
      - Query
      summary: Execute SQL query against Iceberg tables via DuckDB
      description: 'Lightweight SQL query interface powered by DuckDB with the Iceberg extension.

        Executes queries directly against Iceberg tables for a single authorized namespace.


        ## How It Works


        1. User specifies a `namespace` (derived from workspaceId, last 12 chars)

        2. The service validates the user has workspace access to that namespace

        3. DuckDB attaches ONLY that namespace via the Iceberg REST endpoint

        4. The SQL query executes with table references scoped to that namespace

        5. Results are returned as JSON (columns + rows)


        ## Security Model


        - Only the requested namespace is attached — no cross-namespace access is possible

        - Dangerous statements (`ATTACH`, `DETACH`, `CREATE SECRET`, `DROP SECRET`, `INSTALL`, `LOAD`, `USE`, `SET`) are blocked

        - The service''s role provides read access to the catalog and storage; user authorization is handled at the application layer


        ## Query Syntax


        Since the namespace is pre-selected via `USE`, table references are unqualified:


        ```sql

        SELECT * FROM my_table LIMIT 10

        SELECT COUNT(*) FROM customers WHERE status = ''active''

        DESCRIBE orders

        SHOW TABLES

        ```


        Table DML (`INSERT`/`UPDATE`/`DELETE`/`MERGE`) is also permitted for

        non-read-only members; each is committed durably to the Iceberg catalog.

        Table lifecycle (`CREATE`/`DROP`/general `ALTER`) is intentionally NOT

        supported here — use the Iceberg REST Catalog API instead.


        **Exception:** `ALTER TABLE ... ADD/DROP PRIMARY KEY` IS supported, as a

        special case handled outside DuckDB entirely (neither DuckDB nor Iceberg

        has a native primary-key concept). It sets or removes the same

        `dpf.primary-keys` table property documented on the iceberg-rest-catalog''s

        `setPrimaryKey`/`removePrimaryKey` examples:


        ```sql

        ALTER TABLE customers ADD PRIMARY KEY (customer_id)

        ALTER TABLE order_lines ADD PRIMARY KEY (order_id, line_no)

        ALTER TABLE customers DROP PRIMARY KEY

        ```


        `ADD PRIMARY KEY` overwrites any key already set and rejects unknown

        column names; `DROP PRIMARY KEY` is safe to run even if no key is set.


        ## Limitations


        - **Read-only**: Only SELECT, DESCRIBE, SHOW queries are meaningful (data modification is blocked by IAM)

        - **Max query length**: 10,000 characters

        - **Timeout**: 60 seconds

        - **Cold start**: First invocation may take 10-15s to download DuckDB extensions

        - **Result size**: Large result sets may exceed response limits (6MB)


        ## DuckDB Features Available


        Full DuckDB SQL syntax is available for analytical queries:

        - Aggregations, window functions, CTEs

        - JOINs across tables within the same namespace

        - LIMIT, ORDER BY, GROUP BY

        - Iceberg time-travel: `SELECT * FROM table AT (VERSION => snapshot_id)`

        - Iceberg metadata: `SELECT * FROM iceberg_snapshots(''catalog.ns.table'')`'
      operationId: executeQuery
      security:
      - BearerAuth: []
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/QueryRequest'
            examples:
              selectRows:
                summary: Select rows from a table
                value:
                  namespace: 633def9656c1
                  query: SELECT * FROM detail LIMIT 10
              countRows:
                summary: Count rows
                value:
                  namespace: 633def9656c1
                  query: SELECT COUNT(*) as total FROM detail
              describeTable:
                summary: Describe table schema
                value:
                  namespace: 633def9656c1
                  query: DESCRIBE detail
              showTables:
                summary: List all tables
                value:
                  namespace: 633def9656c1
                  query: SHOW TABLES
              aggregation:
                summary: Aggregation query
                value:
                  namespace: 633def9656c1
                  query: SELECT dpf_filename, COUNT(*) as rows FROM detail GROUP BY dpf_filename
              addPrimaryKeyViaQuery:
                summary: Add or replace a table's primary key
                description: 'Special-cased outside DuckDB: sets the `dpf.primary-keys` table

                  property (comma-separated for a composite key), the same

                  property the `setPrimaryKey` example on the iceberg-rest-catalog

                  endpoint sets directly. Overwrites any key already set; rejects

                  column names that don''t exist on the table.

                  '
                value:
                  namespace: 633def9656c1
                  query: ALTER TABLE customers ADD PRIMARY KEY (customer_id)
              dropPrimaryKeyViaQuery:
                summary: Remove a table's primary key
                description: 'Removes the `dpf.primary-keys` property entirely. Safe to run

                  even if the table has no primary key set.

                  '
                value:
                  namespace: 633def9656c1
                  query: ALTER TABLE customers DROP PRIMARY KEY
      responses:
        '200':
          description: Query executed successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/QueryResponse'
        '500':
          description: 'Internal server error. Error codes:

            - `QUERY_SETUP_FAILED`: DuckDB initialization or catalog attachment failed

            - `INTERNAL_SERVER_ERROR`: Unexpected error

            '
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ErrorResponse'
  /query-large:
    post:
      tags:
      - Query
      summary: Execute SQL query (large compute)
      description: 'High-resource variant of the `/query` endpoint. Identical request/response format and behavior,

        but runs with 4× the memory and CPU allocation (7076 MB / ~4 vCPU) and a 120-second timeout.


        Use this endpoint for queries that are too resource-intensive for the standard `/query` endpoint,

        such as large table scans, complex joins, or heavy aggregations.


        **Billing:** Each query executed on this endpoint consumes 4× the credits of a standard `/query` request.


        See the /query endpoint for full documentation on request format,

        security model, query syntax, and available DuckDB features.'
      operationId: executeQueryLarge
      security:
      - BearerAuth: []
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/QueryRequest'
            examples:
              heavyAggregation:
                summary: Large aggregation query
                value:
                  namespace: 633def9656c1
                  query: SELECT dpf_filename, COUNT(*) as rows, MIN(dpf_ts) as first_load, MAX(dpf_ts) as last_load FROM detail GROUP BY dpf_filename ORDER BY rows DESC
      responses:
        '200':
          description: Query executed successfully
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/QueryResponse'
components:
  schemas:
    QueryResponse:
      type: object
      properties:
        success:
          type: boolean
          example: true
        data:
          type: object
          properties:
            columnTypes:
              type: array
              items:
                type: object
                properties:
                  name:
                    type: string
                  duckdbType:
                    type: string
                    example: INTEGER
                  typeOid:
                    type: integer
                    description: PostgreSQL type OID (for wire-protocol/JDBC consumers)
                    example: 23
              description: Typed column descriptors for the result set.
              example:
              - name: id
                duckdbType: VARCHAR
                typeOid: 25
              - name: amount
                duckdbType: DOUBLE
                typeOid: 701
            rows:
              type: array
              items:
                type: object
                additionalProperties: true
              description: 'Array of row objects (column name → value). Capped at 1000 rows — see `truncated`/`resultUrl` when the true result is larger.

                '
              example:
              - id: CUST001
                name: Acme Corp
                amount: 1500.0
                dpf_filename: customers.csv
              - id: CUST002
                name: Beta Inc
                amount: 2300.5
                dpf_filename: customers.csv
            rowCount:
              type: integer
              description: 'Number of rows in `rows` (the returned/capped set, at most 1000). When `truncated` is true this is NOT the true total — see `totalRowCount`.

                '
              example: 2
            truncated:
              type: boolean
              description: True when the query's true result exceeded the 1000-row inline cap.
              example: false
            totalRowCount:
              type:
              - integer
              - 'null'
              description: True total row count. Present only when `truncated` is true.
            resultUrl:
              type:
              - string
              - 'null'
              description: 'Presigned S3 GET URL to the full (uncapped) result as a gzip-compressed CSV (`.csv.gz`; `Content-Type: application/gzip` on GET). The response body is the raw gzip bytes — it is not auto-decompressed via `Content-Encoding`, so it must be explicitly gunzipped. Present only when `truncated` is true, and only if the spillover write succeeded — a truncated result can still come back without a resultUrl.

                '
            executionTimeMs:
              type: integer
              description: Total execution time in milliseconds (includes setup)
              example: 1234
    ErrorResponse:
      type: object
      required:
      - error
      properties:
        error:
          type: object
          required:
          - code
          - message
          properties:
            code:
              type: string
              description: Machine-readable error code
              example: INVALID_EMAIL
            message:
              type: string
              description: Human-readable error message
              example: Invalid email format
    QueryRequest:
      type: object
      required:
      - namespace
      - query
      properties:
        namespace:
          type: string
          description: 'Namespace to query (last 12 characters of workspaceId).

            Must be a namespace the authenticated user has access to.

            '
          example: 633def9656c1
          pattern: ^[a-zA-Z0-9_]+$
        query:
          type: string
          description: 'SQL query to execute. Table references are unqualified (just the table name)

            since the namespace is pre-selected. Maximum 10,000 characters.

            '
          example: SELECT * FROM detail LIMIT 10
          maxLength: 10000
  securitySchemes:
    BearerAuth:
      type: http
      scheme: bearer
      bearerFormat: JWT
      description: JWT token obtained from login or registration