Dataville Export and SQL API

openapi: 3.2.0
info:
  title: Dataville Export and SQL API
  version: 1.0.0
  description: 'Dataville is a unified data API: a single REST surface over Wikipedia, arXiv,

    Project Gutenberg, the US Census, USDA FoodData, Papers with Code, SEC EDGAR,

    OpenAlex, PyPI and Stack Exchange — plus bulk CSV/Parquet/DuckDB export, a

    SQL endpoint, and "bring your own data" Google Sheets connections.


    ## Authentication


    Data, export and query endpoints never hard-reject at the middleware level — an

    unauthenticated caller simply gets the anonymous tier (50 requests per day per

    IP).


    ### API key


    ```

    Authorization: Bearer dataville_xxxxxxxxxxxxxxxx

    ```


    Create and manage keys from the **API Keys** page in the

    Dataville dashboard. Keys start with `dataville_`

    (legacy keys start with `datax_`). For clients that cannot set headers — DuckDB

    WASM, the DuckDB CLI''s `httpfs` reader — the same key may be passed as the

    `?token=` query parameter.


    The Dataville web app authenticates with its own session tokens; those routes

    are internal and not part of the public API surface.


    ## Credits and metering


    Authenticated usage is metered against a monthly **Dataville credit** balance in

    USD, shared across an account''s sessions and API keys. Each tier is granted

    credit every month — Free **$5**, Developer **$29**, Pro **$99** — and each

    query deducts a per-source, per-tier amount:


    | Class | Free | Developer | Pro |

    | --- | --- | --- | --- |

    | Open/public datasets | $0.0050 | $0.0050 | $0.0040 |

    | Wikipedia (premium) | $0.0250 | $0.0150 | $0.0125 |


    A `POST /api/v1/query` call is charged per row returned. When the balance

    reaches zero the API returns **402 Payment Required** until the monthly reset,

    a plan upgrade, or a credit top-off. Every metered response carries:


    - `X-Credits-Remaining` — remaining balance, USD

    - `X-Credit-Cost` — amount charged for this request, USD


    Check your request quota any time via `GET /api/v1/auth/usage`; your credit

    balance is reported on every metered response in `X-Credits-Remaining` and on

    the dashboard''s **Billing** page.


    ## Rate limiting


    Anonymous callers are limited by IP (50 requests per 24 hours) and receive

    **429** when they exceed it, with `X-RateLimit-Limit`, `X-RateLimit-Remaining`

    and `X-RateLimit-Reset` headers. Authenticated callers are governed by credits

    rather than a request count. Export and SQL routes carry their own stricter

    limit (1,000 per day per account), as does support submission (5 per hour).


    Every route also shares a burst limit of 100 requests per 15 minutes per IP,

    checked before authentication. It answers **429** with `data.code:

    RATE_LIMITED`, `RateLimit-*` headers and `Retry-After`. The anonymous daily

    limit answers with `data.code: REQUEST_LIMIT_EXCEEDED`. Branch on the code:

    the first clears in minutes; the second needs an API key or the daily reset.


    ## Errors


    Every error body carries a human-readable `error`/`message` string that is safe

    to show a user verbatim, and data-source errors additionally carry a stable

    `data.code` (`UPSTREAM_RATE_LIMITED`, `UPSTREAM_UNAVAILABLE`,

    `UPSTREAM_FORBIDDEN`, `UPSTREAM_BAD_RESPONSE`, `SOURCE_UNAVAILABLE`,

    `INTERNAL_ERROR`) that clients should branch on instead of parsing prose. When

    an upstream tells us how long to wait, the response sets `Retry-After`.

    Rate-limit 429s carry a code too (`RATE_LIMITED`, `REQUEST_LIMIT_EXCEEDED`;

    see Rate limiting).


    ## Stale results


    A live source that is down is answered from Dataville''s own stored copy where

    one exists. Those responses include a `data.stale` object naming the source and

    when the copy was taken — the answer is real, but may be out of date.'
  contact:
    name: Dataville Support
    email: support@dataville.com
  license:
    name: MIT
    url: https://opensource.org/licenses/MIT
servers:
- url: https://api.dataville.com
  description: Production
security:
- ApiKeyAuth: []
tags:
- name: Export and SQL
  description: SELECT-only SQL over the datasets — as JSON, or as a Parquet/CSV result a DuckDB client reads directly.
paths:
  /api/v1/export:
    get:
      tags:
      - Export and SQL
      operationId: listExports
      summary: List the query endpoints
      description: Where to send SQL, as JSON or from a DuckDB client. No auth required.
      security: []
      responses:
        '200':
          description: The query endpoints.
          content:
            application/json:
              schema:
                type: object
                properties:
                  status:
                    type: string
                    example: success
                  query_endpoint:
                    type: string
                    example: /api/v1/query
                  duckdb_endpoints:
                    type: object
                    properties:
                      parquet:
                        type: string
                        example: /api/v1/query.parquet?sql=<url-encoded SELECT>&token=<api key>
                      csv:
                        type: string
                        example: /api/v1/query.csv?sql=<url-encoded SELECT>&token=<api key>
                  note:
                    type: string
  /api/v1/query:
    post:
      tags:
      - Export and SQL
      operationId: runSqlQuery
      summary: Run a SQL SELECT across the datasets
      description: 'Execute a read-only SQL query over the datasets in DuckDB. Only `SELECT`

        statements are accepted; anything else is rejected by the validator.


        - Table names are the source names (`gutenberg`, `arxiv`, `edgar`, …).

        - Connected Google Sheets can be joined in by passing their ids in

        `datasetIds` and referencing them by their **alias** (never the internal

        view name).

        - Queries are capped at 10,000 characters, 1,000 rows and 30 seconds.

        - Charged **per row returned**.


        ```bash

        curl -X POST https://api.dataville.com/api/v1/query \

        -H "Authorization: Bearer $DATAVILLE_API_KEY" \

        -H "Content-Type: application/json" \

        -d ''{"sql":"SELECT title, author FROM gutenberg LIMIT 10"}''

        ```'
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              required:
              - sql
              properties:
                sql:
                  type: string
                  maxLength: 10000
                  description: A single SELECT statement.
                  example: SELECT title, author FROM gutenberg LIMIT 10
                datasetIds:
                  type: array
                  description: Ids of the caller's connected sheets to make available to the query.
                  items:
                    type: string
      responses:
        '200':
          description: Query results.
          content:
            application/json:
              schema:
                type: object
                properties:
                  status:
                    type: string
                    example: success
                  row_count:
                    type: integer
                    example: 10
                  rows:
                    type: array
                    items:
                      type: object
                      additionalProperties: true
                  sql_executed:
                    type: string
                  available_tables:
                    type: array
                    items:
                      type: string
                  usage:
                    type: object
                    properties:
                      rows_charged:
                        type: integer
                      request_cost:
                        type: number
                        format: float
                      credits_remaining:
                        type: number
                        format: float
        '400':
          description: Missing/oversized SQL, a rejected statement, or a DuckDB error.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/SimpleError'
              example:
                status: error
                message: Only SELECT statements are allowed.
        '401':
          $ref: '#/components/responses/ExportUnauthorized'
        '402':
          $ref: '#/components/responses/InsufficientCredit'
        '403':
          description: A dataset id in `datasetIds` is not owned by the caller.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/SimpleError'
        '408':
          description: Query exceeded the 30-second limit.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/SimpleError'
        '429':
          $ref: '#/components/responses/ExportRateLimited'
  /api/v1/query.parquet:
    get:
      tags:
      - Export and SQL
      operationId: runSqlQueryParquet
      summary: Run a SQL SELECT from DuckDB, result as Parquet
      description: 'The same query as `POST /api/v1/query` — same tables, validator and

        limits (10,000 characters, 1,000 rows, 30 seconds) — with the result

        returned as a Parquet file, so DuckDB can read it directly:


        ```sql

        SELECT * FROM read_parquet(

        ''https://api.dataville.com/api/v1/query.parquet?sql=SELECT%20title%2C%20url%20FROM%20wiki%20LIMIT%2010&token=YOUR_KEY''

        );

        ```


        The SQL runs on Dataville and only its result is returned; whole

        datasets cannot be downloaded.


        **Billing** is the same as `POST /api/v1/query`: each row returned at

        its source''s rate, so a query returning no rows costs nothing. DuckDB

        reads one URL with several requests (a `HEAD`, then range `GET`s); the

        first runs and charges the query, and every request for the same query

        in the next 5 minutes is served the same result uncharged

        (`X-Dataville-Cached: true`, `X-Credit-Cost: 0`). After that, reading it

        again runs — and bills — the query again.'
      parameters:
      - $ref: '#/components/parameters/SqlQuery'
      - $ref: '#/components/parameters/DatasetsQuery'
      - $ref: '#/components/parameters/TokenQuery'
      responses:
        '200':
          description: 'The query result as a Parquet file. A `Range` request returns `206`

            with `Content-Range`.


            <h4>Response headers</h4>


            | Header | Example |

            | --- | --- |

            | `X-Credit-Cost` | `0.045` (`0` on a cached read) |

            | `X-Credits-Remaining` | `28.95` |

            | `X-Dataville-Cached` | `false` |

            | `X-Dataville-Query-Id` | `6654ea55-9e0f-49e4-a4da-78fe80b24c1d` |

            | `X-Dataville-Rows-Charged` | `3` |

            | `X-Dataville-Usage` | `{"rows_charged":3,"request_cost":0.045,"by_source":[{"source":"wiki","rows":3,"cost_per_row":0.015,"cost":0.045}]}` |

            '
          content:
            application/vnd.apache.parquet:
              schema:
                type: string
                format: binary
        '206':
          description: The requested byte range of the result.
        '400':
          $ref: '#/components/responses/QueryInvalid'
        '401':
          $ref: '#/components/responses/ExportUnauthorized'
        '402':
          $ref: '#/components/responses/InsufficientCredit'
        '403':
          description: A dataset id in `datasets` is not owned by the caller.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/SimpleError'
        '408':
          description: Query exceeded the 30-second limit (`QUERY_TIMEOUT`). Not charged.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/SimpleError'
        '413':
          description: The result is over 64 MB (`QUERY_RESULT_TOO_LARGE`). Not charged.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/SimpleError'
        '429':
          $ref: '#/components/responses/ExportRateLimited'
        '503':
          description: '`QUERY_BACKEND_UNAVAILABLE` (our side; not charged) or `QUERY_BUSY`

            (the same query is still running from an earlier request). Honour

            `Retry-After`.

            '
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/SimpleError'
  /api/v1/query.csv:
    get:
      tags:
      - Export and SQL
      operationId: runSqlQueryCsv
      summary: Run a SQL SELECT from DuckDB, result as CSV
      description: '`GET /api/v1/query.parquet` with a CSV result, for `read_csv` or any

        HTTP client. Same limits, billing and 5-minute uncharged re-reads.


        ```sql

        SELECT * FROM read_csv(''https://api.dataville.com/api/v1/query.csv?sql=SELECT%20name%2C%20version%20FROM%20pypi%20LIMIT%2010&token=YOUR_KEY'');

        ```'
      parameters:
      - $ref: '#/components/parameters/SqlQuery'
      - $ref: '#/components/parameters/DatasetsQuery'
      - $ref: '#/components/parameters/TokenQuery'
      responses:
        '200':
          description: The query result as CSV with a header row. Same headers as the Parquet form.
          content:
            text/csv:
              schema:
                type: string
        '400':
          $ref: '#/components/responses/QueryInvalid'
        '401':
          $ref: '#/components/responses/ExportUnauthorized'
        '402':
          $ref: '#/components/responses/InsufficientCredit'
        '429':
          $ref: '#/components/responses/ExportRateLimited'
components:
  schemas:
    InsufficientCreditResponse:
      type: object
      properties:
        status:
          type: string
          enum:
          - error
        account_state:
          type: string
          example: authenticated
        error:
          type: string
          example: insufficient_credit
        data:
          type: object
          properties:
            error:
              type: string
            code:
              type: string
              enum:
              - INSUFFICIENT_CREDIT
        credits:
          type: object
          properties:
            remaining_usd:
              type: number
              format: float
            request_cost_usd:
              type: number
              format: float
    SimpleError:
      type: object
      properties:
        status:
          type: string
          enum:
          - error
        message:
          type: string
  responses:
    InsufficientCredit:
      description: Monthly credit exhausted — upgrade, top off, or wait for the reset.
      content:
        application/json:
          schema:
            $ref: '#/components/schemas/InsufficientCreditResponse'
    ExportRateLimited:
      description: Export/query limit exceeded (1,000 per day per account).
      content:
        application/json:
          schema:
            $ref: '#/components/schemas/SimpleError'
          example:
            status: error
            message: Export rate limit exceeded. Maximum 1,000 exports per day per account.
    QueryInvalid:
      description: Missing/oversized SQL, a rejected statement, or a DuckDB error in the query (`QUERY_INVALID`). Not charged.
      content:
        application/json:
          schema:
            $ref: '#/components/schemas/SimpleError'
          example:
            status: error
            code: QUERY_INVALID
            message: 'Binder Error: Referenced column "nope" not found in FROM clause!'
    ExportUnauthorized:
      description: Authentication required.
      content:
        application/json:
          schema:
            $ref: '#/components/schemas/SimpleError'
          example:
            status: error
            message: Authentication required. Include your API key in the Authorization header.
  parameters:
    DatasetsQuery:
      name: datasets
      in: query
      required: false
      description: Comma-separated ids of the caller's connected sheets to make available, as `datasetIds` in `POST /api/v1/query`.
      schema:
        type: string
    TokenQuery:
      name: token
      in: query
      required: false
      description: 'API key as a query parameter, for clients that cannot set an

        `Authorization` header (DuckDB WASM, the DuckDB CLI). Prefer the header

        wherever possible — query strings end up in logs.

        '
      schema:
        type: string
    SqlQuery:
      name: sql
      in: query
      required: true
      description: A single SELECT statement, URL-encoded (max 10,000 characters).
      schema:
        type: string
        example: SELECT title, url FROM wiki LIMIT 10
  securitySchemes:
    ApiKeyAuth:
      type: http
      scheme: bearer
      description: 'A Dataville API key, sent as `Authorization: Bearer dataville_...`. Clients

        that cannot set headers may pass the same key as `?token=`.

        '