openapi.yaml

openapi: 3.0.3
info:
  title: Mesa API
  version: 0.1.0
servers:
  - url: https://api.example.com
paths:
  /connections:
    get:
      operationId: ListConnections
      tags:
        - Connections
      summary: List Connections
      responses:
        "200":
          description: Connections
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: "#/components/schemas/Connection"
    post:
      operationId: CreateConnection
      tags:
        - Connections
      summary: Create Connection
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/CreateConnectionRequest"
      responses:
        "201":
          description: Created
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Connection"

  /connections/{connectionID}:
    get:
      operationId: FindConnection
      summary: Retrieves a connection by ID
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
      responses:
        "200":
          description: Connection
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Connection"
        "404":
          description: Not Found

  /connections/{connectionID}/ping:
    get:
      operationId: PingConnection
      summary: Checks the connection status
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
      responses:
        "204":
          description: OK (No Content)
        "502":
          description: Bad Gateway (Connection Failed)
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /connections/{connectionID}/databases:
    get:
      operationId: ListDatabases
      summary: List databases from a connection
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
      responses:
        "200":
          description: Databases
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: "#/components/schemas/Database"
        "500":
          description: Internal Server Error
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
    post:
      operationId: CreateDatabase
      summary: Create a new database
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/CreateDatabaseRequest"
      responses:
        "201":
          description: Created
  /connections/{connectionID}/overview:
    get:
      operationId: GetConnectionOverview
      summary: Get Server Health & Overview
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
      responses:
        "200":
          description: Overview
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/OverviewResponse"

  /connections/{connectionID}/databases/{databaseName}/tables:
    get:
      operationId: ListTables
      summary: List tables from a database
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
        - $ref: "#/components/parameters/DatabaseName"
      responses:
        "200":
          description: Tables
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: "#/components/schemas/Table"
        "500":
          description: Internal Server Error
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
    post:
      operationId: CreateTable
      summary: Create a table in a database
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
        - $ref: "#/components/parameters/DatabaseName"
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/CreateTableRequest"
      responses:
        "201":
          description: Created
  /connections/{connectionID}/databases/{databaseName}/tables/{tableName}/columns:
    get:
      operationId: ListColumns
      summary: ListColumns
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
        - $ref: "#/components/parameters/DatabaseName"
        - $ref: "#/components/parameters/TableName"
      responses:
        "200":
          description: Columns
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: "#/components/schemas/Column"

  /connections/{connectionID}/databases/{databaseName}/tables/{tableName}/indexes:
    get:
      operationId: ListIndexes
      summary: ListIndexes
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
        - $ref: "#/components/parameters/DatabaseName"
        - $ref: "#/components/parameters/TableName"
      responses:
        "200":
          description: Indexes
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: "#/components/schemas/Index"

  /connections/{connectionID}/databases/{databaseName}/tables/{tableName}/rows:
    get:
      operationId: QueryTableRows
      summary: QueryTableRows
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
        - $ref: "#/components/parameters/DatabaseName"
        - $ref: "#/components/parameters/TableName"
        - name: limit
          in: query
          schema:
            type: integer
            default: 50
            minimum: 1
            maximum: 500
        - name: offset
          in: query
          schema:
            type: integer
            default: 0
            minimum: 0
        - name: sort_by
          in: query
          schema:
            type: string
        - name: sort_order
          in: query
          schema:
            type: string
            enum: [asc, desc]
            default: asc
      responses:
        "200":
          description: Paginated table rows
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/TableRowsResponse"
    put:
      operationId: UpdateTableRow
      summary: Update a row in a table
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
        - $ref: "#/components/parameters/DatabaseName"
        - $ref: "#/components/parameters/TableName"
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/UpdateTableRowRequest"
      responses:
        "204":
          description: Updated (No Content)
        "400":
          description: Bad Request
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "404":
          description: Not Found
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "500":
          description: Internal Server Error
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /connections/{connectionID}/users:
    get:
      operationId: ListUsers
      summary: List database users (roles)
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
      responses:
        "200":
          description: Users
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: "#/components/schemas/DBUser"
    post:
      operationId: CreateUser
      summary: Create a new database user
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/CreateUserRequest"
      responses:
        "201":
          description: Created
  /connections/{connectionID}/sessions:
    get:
      operationId: ListSessions
      summary: List active sessions
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
      responses:
        "200":
          description: Sessions
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: "#/components/schemas/Session"
  /connections/{connectionID}/sessions/{pid}:
    delete:
      operationId: KillSession
      summary: Kill a specific session
      tags:
        - Connections
      parameters:
        - $ref: "#/components/parameters/ConnectionId"
        - in: path
          name: pid
          required: true
          schema:
            type: integer
      responses:
        "204":
          description: Terminated

components:
  parameters:
    ConnectionId:
      in: path
      name: connectionID
      required: true
      schema:
        type: string
        format: uuid
      description: The unique identifier for the connection
    DatabaseName:
      in: path
      name: databaseName
      required: true
      schema:
        type: string
      description: Database name
    TableName:
      in: path
      name: tableName
      required: true
      schema:
        type: string
      description: Table Name
  schemas:
    Connection:
      type: object
      required: [id, name, driver, host, port, username, updated_at, created_at]
      properties:
        id:
          type: string
        name:
          type: string
          maxLength: 255
          minLength: 3
        driver:
          type: string
          enum: [postgres, mysql]
        host:
          type: string
          maxLength: 255
          minLength: 1
        port:
          type: integer
          description: A port value between 0 and 65535
          maximum: 65535
          minimum: 0
        username:
          type: string
          maxLength: 255
          minLength: 1
        createdAt:
          type: string
          format: date-time
        updatedAt:
          type: string
          format: date-time
        status:
          type: string
          enum: [ok, error]
        status_error:
          type: string
    Database:
      type: object
      required: [name, owner, encoding, size_formatted]
      properties:
        name:
          type: string
        owner:
          type: string
        encoding:
          type: string
        size_formatted:
          type: string
    Table:
      type: object
      required: [name, type, row_count, size]
      properties:
        name:
          type: string
        type:
          type: string
        row_count:
          type: integer
          format: int64
        size:
          type: integer
          format: int64
    Column:
      type: object
      required: [name, type, nullable, primary]
      properties:
        name:
          type: string
        type:
          type: string
        primary:
          type: boolean
        nullable:
          type: boolean
        default_value:
          type: string

    Index:
      type: object
      required: [name, columns, method, unique, size]
      properties:
        name:
          type: string
        columns:
          type: array
          items:
            type: string
        method:
          type: string
        unique:
          type: boolean
        size:
          type: integer
          format: int64

    TableRowsResponse:
      type: object
      required: [columns, rows, total, limit, offset]
      properties:
        columns:
          type: array
          items:
            type: string
        rows:
          type: array
          items:
            type: array
            items: {}
        total:
          type: integer
          format: int64
        limit:
          type: integer
        offset:
          type: integer

    Error:
      type: object
      required: [message]
      properties:
        message:
          type: string
    CreateConnectionRequest:
      type: object
      required: [name, driver, host, port, username, password]
      properties:
        name:
          type: string
        driver:
          type: string
          enum: [postgres, mysql]
        host:
          type: string
        port:
          type: integer
        username:
          type: string
        password:
          type: string
    OverviewResponse:
      type: object
      required: [status, latency_ms]
      properties:
        status:
          type: string
          enum: [ONLINE, UNREACHABLE]
        version:
          type: string
        uptime:
          type: string
        sessions:
          type: string
          example: "45/100"
        latency_ms:
          type: integer
    Session:
      type: object
      required: [pid, user, database, state, query, duration, is_slow]
      properties:
        pid:
          type: integer
        user:
          type: string
        database:
          type: string
        state:
          type: string
        query:
          type: string
        duration:
          type: string
        is_slow:
          type: boolean
    DBUser:
      type: object
      required: [name, is_superuser, can_login, conn_limit]
      properties:
        name:
          type: string
        is_superuser:
          type: boolean
        can_login:
          type: boolean
        conn_limit:
          type: integer
    CreateUserRequest:
      type: object
      required: [username, password]
      properties:
        username:
          type: string
        password:
          type: string
        is_superuser:
          type: boolean
        can_login:
          type: boolean
        conn_limit:
          type: integer
    CreateDatabaseRequest:
      type: object
      required: [name, owner]
      properties:
        name:
          type: string
        owner:
          type: string
    CreateTableRequest:
      type: object
      required: [name, columns]
      properties:
        name:
          type: string
        columns:
          type: array
          minItems: 1
          items:
            $ref: "#/components/schemas/CreateTableColumn"
        indexes:
          type: array
          items:
            $ref: "#/components/schemas/CreateTableIndex"
    CreateTableColumn:
      type: object
      required: [name, type]
      properties:
        name:
          type: string
        type:
          $ref: "#/components/schemas/ColumnDataType"
        length:
          type: integer
          minimum: 1
        precision:
          type: integer
          minimum: 1
        nullable:
          type: boolean
        primary_key:
          type: boolean
        default_value:
          type: string
    CreateTableIndex:
      type: object
      required: [name, columns]
      properties:
        name:
          type: string
        columns:
          type: array
          minItems: 1
          items:
            type: string
        unique:
          type: boolean
        method:
          type: string
          enum: [btree, hash, gin, gist, brin, spgist]
    ColumnDataType:
      type: string
      enum:
        - smallint
        - integer
        - bigint
        - serial
        - bigserial
        - numeric
        - decimal
        - real
        - double_precision
        - boolean
        - text
        - varchar
        - char
        - uuid
        - date
        - time
        - timestamp
        - timestamptz
        - json
        - jsonb
        - bytea
    UpdateTableRowRequest:
      type: object
      required: [where, set]
      properties:
        where:
          type: object
          additionalProperties: true
          description: Primary key column(s) and their values to identify the row
        set:
          type: object
          additionalProperties: true
          description: Column(s) and new values to apply