openapi: 3.2.0
info:
  title: DBaaS API
  version: "1.0"
  license:
    name: Proprietary
    url: https://dbaas.dev/terms-of-service
  description: |
    Managed PostgreSQL as a Service. Two modes:

    **Ephemeral (no auth)** — spin up a throwaway database in seconds, auto-deleted after 1–5 minutes.
    Use these when you want a quick scratchpad without an account.

    **Persistent (API key required)** — production-grade databases tied to your account.
    Generate an API key at https://dbaas.dev/account/api-keys and pass it as `Authorization: Bearer <key>`.

servers:
  - url: https://api.dbaas.dev
    description: Production

components:
  securitySchemes:
    bearerAuth:
      type: http
      scheme: bearer
      description: |
        API key generated at https://dbaas.dev/account/api-keys
        Format: `dbaas_live_<32 chars>`

  schemas:
    Error:
      type: object
      properties:
        status:
          type: string
          enum: [error]
        message:
          type: string
      required: [status, message]
      example:
        status: error
        message: "Database not found"

    Success:
      type: object
      properties:
        status:
          type: string
          enum: [success]
        message:
          type: string
      required: [status]

    EphemeralCreateResponse:
      type: object
      properties:
        status:
          type: string
          enum: [success]
        message:
          type: string
        data:
          type: object
          properties:
            id:
              type: string
              description: Unique database identifier. Use this to poll GET /v1/ephemeral/{id}.
            status:
              type: string
              enum: [provisioning]
            ttlMinutes:
              type: integer
              description: Time-to-live in minutes (1–5). Database is deleted after this.
            pollUrl:
              type: string
              description: Relative URL to poll for connection details.
            username:
              type: string
            password:
              type: string
            database:
              type: string
          required: [id, status, ttlMinutes, pollUrl, username, password, database]

    EphemeralStatusResponse:
      type: object
      properties:
        status:
          type: string
          enum: [success]
        data:
          type: object
          properties:
            id:
              type: string
            status:
              type: string
              enum: [provisioning, running, stopped, error]
              description: Poll until status=running before connecting.
            expiresAt:
              type: string
              format: date-time
            ttlMinutes:
              type: integer
            secondsRemaining:
              type: integer
            host:
              type: string
              description: Hostname (present only when status=running)
            port:
              type: integer
              description: Port (present only when status=running)
            username:
              type: string
              description: (present only when status=running)
            password:
              type: string
              description: (present only when status=running)
            database:
              type: string
              description: (present only when status=running)
            connectionString:
              type: string
              description: Ready-to-use PostgreSQL connection string (present only when status=running)
          required: [id, status, expiresAt, ttlMinutes, secondsRemaining]

    SQLRequest:
      type: object
      properties:
        sql:
          type: string
          description: SQL statement to execute. SELECT, INSERT, UPDATE, DELETE, CREATE, DROP supported.
        confirmed:
          type: boolean
          default: false
          description: |
            For persistent databases only. Set to true to confirm destructive statements
            (DROP, TRUNCATE, DELETE/UPDATE without WHERE). The first request returns a
            confirmation prompt; re-send with confirmed=true to execute.
      required: [sql]

    SQLResponse:
      type: object
      properties:
        status:
          type: string
          enum: [success, error, confirm]
        data:
          type: object
          properties:
            columns:
              type: array
              items:
                type: string
              description: Column names (present for SELECT results)
            rows:
              type: array
              items:
                type: array
              description: Row values (present for SELECT results)
            rowCount:
              type: integer
              description: Number of rows returned or affected
            commandTag:
              type: string
              description: PostgreSQL command tag (e.g. "INSERT 0 1", "CREATE TABLE")
            durationMs:
              type: number
              description: Execution time in milliseconds
        requiresConfirm:
          type: boolean
          description: True when a destructive statement requires confirmation (persistent DBs only)
        message:
          type: string

    DatabaseInfo:
      type: object
      properties:
        id:
          type: string
        name:
          type: string
        status:
          type: string
          enum: [Provisioning, Running, Stopped, Error]
        plan:
          type: string
        createdAt:
          type: string
          format: date-time

    DatabaseAddress:
      type: object
      properties:
        status:
          type: string
          enum: [success]
        data:
          type: object
          properties:
            dnsName:
              type: string
            port:
              type: integer
            username:
              type: string
            databaseName:
              type: string
            service:
              type: string
            connectionString:
              type: string
            createdAt:
              type: string
              format: date-time

    Backup:
      type: object
      properties:
        id:
          type: string
        resourceId:
          type: string
        status:
          type: string
          enum: [pending, success, failed, expired]
        triggeredBy:
          type: string
          enum: [scheduler, manual]
        sizeBytes:
          type: integer
        createdAt:
          type: string
          format: date-time
        completedAt:
          type:
            - string
            - "null"
          format: date-time
        expiresAt:
          type: string
          format: date-time

    Metrics:
      type: object
      properties:
        status:
          type: string
          enum: [success]
        data:
          type: object
          properties:
            connActive:
              type: integer
            connIdle:
              type: integer
            connTotal:
              type: integer
            connMax:
              type: integer
            memUsageBytes:
              type: integer
            memLimitBytes:
              type: integer
            cpuUsageUsec:
              type: integer
            storageBytes:
              type: integer
            recordedAt:
              type: string
              format: date-time

  responses:
    Unauthorized:
      description: Missing or invalid API key
      content:
        application/json:
          schema:
            $ref: "#/components/schemas/Error"
          example:
            status: error
            message: "invalid API key"

paths:
  # ── Ephemeral endpoints (no auth) ────────────────────────────────────────────

  /v1/ephemeral/create:
    post:
      operationId: createEphemeralDatabase
      security: []
      summary: Create an ephemeral database
      description: |
        Provisions a throwaway PostgreSQL database. No account or API key required.
        Returns credentials immediately. Poll GET /v1/ephemeral/{id} until status=running
        before connecting (usually 30–60 seconds).

        Limit: 2 active ephemeral databases per IP address.
      tags: [Ephemeral]
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                ttl:
                  type: integer
                  minimum: 1
                  maximum: 5
                  description: Time-to-live in minutes. Database is deleted automatically after this.
              required: [ttl]
            example:
              ttl: 5
      responses:
        "201":
          description: Provisioning started
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/EphemeralCreateResponse"
              example:
                status: success
                message: "Ephemeral database provisioning started. Poll pollUrl until status=running before connecting."
                data:
                  id: "dep-hc-a1b2c3d4"
                  status: provisioning
                  ttlMinutes: 5
                  pollUrl: "/v1/ephemeral/dep-hc-a1b2c3d4"
                  username: "dbuser"
                  password: "s3cr3t"
                  database: "mydb"
        "400":
          description: Invalid ttl
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "429":
          description: Too many active ephemeral databases from this IP
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /v1/ephemeral/{id}:
    get:
      operationId: getEphemeralDatabase
      security: []
      summary: Get ephemeral database status
      description: |
        Poll this endpoint until `status=running`. Once running, the response includes
        `host`, `port`, `username`, `password`, `database`, and `connectionString`.
        The connectionString uses `sslmode=require` and works from any client.
      tags: [Ephemeral]
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
          description: Database ID returned by POST /v1/ephemeral/create
      responses:
        "200":
          description: Database status
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/EphemeralStatusResponse"
              example:
                status: success
                data:
                  id: "dep-hc-a1b2c3d4"
                  status: running
                  expiresAt: "2025-01-01T12:05:00Z"
                  ttlMinutes: 5
                  secondsRemaining: 293
                  host: "abc123.db.dbaas.dev"
                  port: 31234
                  username: "dbuser"
                  password: "s3cr3t"
                  database: "mydb"
                  connectionString: "postgresql://dbuser:s3cr3t@abc123.db.dbaas.dev:31234/mydb?sslmode=require"
        "404":
          description: Database not found or already expired
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /v1/ephemeral/{id}/sql:
    post:
      operationId: execEphemeralSQL
      security: []
      summary: Execute SQL on an ephemeral database
      description: |
        Run any SQL statement on the ephemeral database. The database must be in `running` status.
        SELECT returns columns + rows. INSERT/UPDATE/DELETE returns rowCount + commandTag.
        DDL (CREATE TABLE, etc.) returns commandTag.
      tags: [Ephemeral]
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                sql:
                  type: string
              required: [sql]
            examples:
              select:
                summary: SELECT query
                value:
                  sql: "SELECT * FROM users LIMIT 10"
              createTable:
                summary: Create table
                value:
                  sql: "CREATE TABLE users (id SERIAL PRIMARY KEY, email TEXT NOT NULL, created_at TIMESTAMPTZ DEFAULT NOW())"
              insert:
                summary: Insert rows
                value:
                  sql: "INSERT INTO users (email) VALUES ('alice@example.com'), ('bob@example.com') RETURNING id, email"
      responses:
        "200":
          description: SQL executed
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/SQLResponse"
              example:
                status: success
                data:
                  columns: [id, email, created_at]
                  rows: [[1, "alice@example.com", "2025-01-01T12:00:00Z"]]
                  rowCount: 1
                  commandTag: "SELECT 1"
                  durationMs: 4.2
        "400":
          description: Empty SQL or invalid request
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "503":
          description: Database not yet running
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  # ── Persistent database endpoints (API key required) ──────────────────────

  /v1/services/create:
    post:
      operationId: createDatabase
      summary: Create a persistent database
      description: |
        Provisions a production PostgreSQL database tied to your account.
        The database persists until you delete it. Returns immediately — poll
        GET /v1/resources/{id}/address until `connectionString` is populated (~60s).

        Plans: `free` (128Mi RAM, 50m CPU, 1Gi storage).
      tags: [Databases]
      security:
        - bearerAuth: []
      requestBody:
        required: true
        content:
          application/json:
            schema:
              type: object
              properties:
                plan:
                  type: string
                  enum: [free]
                  description: Database plan/tier
                service:
                  type: string
                  default: postgresql
                resourceType:
                  type: string
                  default: database
                location:
                  type: string
                  default: ""
                  description: Preferred node location label (optional)
              required: [plan]
            example:
              plan: free
              service: postgresql
      responses:
        "201":
          description: Database created
          content:
            application/json:
              schema:
                type: object
                properties:
                  status:
                    type: string
                    enum: [success]
                  message:
                    type: string
                  data:
                    type: object
                    description: Public configuration values (plan-dependent)
        "400":
          description: Invalid plan
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "403":
          description: Per-user instance limit reached
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "401":
          description: Missing or invalid API key
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /v1/resources/list:
    get:
      operationId: listDatabases
      summary: List your databases
      description: Returns all databases belonging to the authenticated user, with live status.
      tags: [Databases]
      security:
        - bearerAuth: []
      parameters:
        - name: page
          in: query
          schema:
            type: integer
            default: 1
        - name: limit
          in: query
          schema:
            type: integer
            default: 20
            maximum: 100
      responses:
        "200":
          description: List of databases
          content:
            application/json:
              schema:
                type: object
                properties:
                  status:
                    type: string
                    enum: [success]
                  data:
                    type: array
                    items:
                      $ref: "#/components/schemas/DatabaseInfo"
                  total:
                    type: integer
              example:
                status: success
                total: 1
                data:
                  - id: "dep-abc12345"
                    name: "svc-dep-abc12345"
                    status: Running
                    plan: free
                    createdAt: "2025-01-01T10:00:00Z"
        "401":
          description: Unauthorized
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /v1/resources/{id}/address:
    get:
      operationId: getDatabaseInfo
      summary: Get database connection info
      description: |
        Returns hostname, port, username, database name, and a ready-to-use connection string.
        Poll until `connectionString` is non-empty (takes ~60s after create).
      tags: [Databases]
      security:
        - bearerAuth: []
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
          description: Database ID from listDatabases or createDatabase
      responses:
        "200":
          description: Connection details
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/DatabaseAddress"
              example:
                status: success
                data:
                  dnsName: "xyz789.db.dbaas.dev"
                  port: 31567
                  username: "dbuser"
                  databaseName: "mydb"
                  service: postgresql
                  connectionString: "postgresql://dbuser@xyz789.db.dbaas.dev:31567/mydb?sslmode=require"
                  createdAt: "2025-01-01T10:00:00Z"
        "401":
          $ref: "#/components/responses/Unauthorized"
        "404":
          description: Database not found
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /v1/resources/{id}/sql:
    post:
      operationId: execSQL
      summary: Execute SQL on a persistent database
      description: |
        Run SQL against a persistent database. The database must be in `Running` status.
        Destructive statements (DROP, TRUNCATE, DELETE/UPDATE without WHERE) require a
        two-step confirmation: first request returns `requiresConfirm=true`, re-send with
        `confirmed=true` to execute.

        **Note:** the confirmation is an in-band JSON flag, not a separate auth channel.
        It guards against accidental destructive queries, not against a leaked API key.
        Treat your API key as you would a database password.

        Blocked: COPY FROM/TO, pg_read_file, pg_ls_dir, and other superuser functions.
      tags: [Databases]
      security:
        - bearerAuth: []
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: "#/components/schemas/SQLRequest"
            examples:
              select:
                summary: SELECT
                value:
                  sql: "SELECT * FROM users LIMIT 5"
              dropConfirmed:
                summary: Destructive statement (confirmed)
                value:
                  sql: "DROP TABLE old_data"
                  confirmed: true
      responses:
        "200":
          description: SQL result (or confirmation prompt for destructive statements)
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/SQLResponse"
        "400":
          description: Empty SQL or invalid request
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "401":
          $ref: "#/components/responses/Unauthorized"
        "403":
          description: Statement blocked by security policy
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "503":
          description: Database not running
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /v1/resources/{id}/stop:
    post:
      operationId: stopDatabase
      summary: Stop a database
      description: Scales the Kubernetes deployment to 0. The database is paused but data is preserved.
      tags: [Databases]
      security:
        - bearerAuth: []
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
      responses:
        "200":
          description: Stop initiated
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Success"
        "401":
          $ref: "#/components/responses/Unauthorized"
        "404":
          description: Database not found
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /v1/resources/{id}/start:
    post:
      operationId: startDatabase
      summary: Start a stopped database
      description: Scales the Kubernetes deployment back to 1. Data is preserved from before the stop.
      tags: [Databases]
      security:
        - bearerAuth: []
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
      responses:
        "200":
          description: Start initiated
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Success"
        "401":
          $ref: "#/components/responses/Unauthorized"
        "404":
          description: Database not found
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /v1/resources/{id}:
    delete:
      operationId: deleteDatabase
      summary: Delete a database
      description: |
        Permanently deletes the database, its Kubernetes resources, DNS record, and all data.
        This action is irreversible. All backups are retained until they expire.
        Requires `?confirm=true` query param to prevent accidental deletion.
      tags: [Databases]
      security:
        - bearerAuth: []
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
        - name: confirm
          in: query
          required: true
          schema:
            type: boolean
          description: Must be true to confirm deletion
      responses:
        "200":
          description: Deleted
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Success"
        "400":
          description: confirm=true not provided
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "401":
          $ref: "#/components/responses/Unauthorized"
        "404":
          description: Database not found
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  # ── Backup endpoints ─────────────────────────────────────────────────────────

  /v1/resources/{id}/backups:
    get:
      operationId: listBackups
      summary: List backups
      description: Returns all non-expired backups for a database, newest first.
      tags: [Backups]
      security:
        - bearerAuth: []
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
      responses:
        "200":
          description: List of backups
          content:
            application/json:
              schema:
                type: object
                properties:
                  status:
                    type: string
                    enum: [success]
                  data:
                    type: array
                    items:
                      $ref: "#/components/schemas/Backup"
        "401":
          $ref: "#/components/responses/Unauthorized"
        "404":
          description: Database not found
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "503":
          description: Backup storage not configured
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

    post:
      operationId: triggerBackup
      summary: Trigger a manual backup
      description: |
        Triggers an immediate pg_dump backup via the wal-shipper sidecar.
        Returns 202 immediately — the backup completes in ~1–2 minutes and will
        then appear in listBackups. The database must be in Running status.
      tags: [Backups]
      security:
        - bearerAuth: []
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
      responses:
        "202":
          description: Backup triggered
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Success"
        "401":
          $ref: "#/components/responses/Unauthorized"
        "404":
          description: Database not found
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "409":
          description: Database not in Running status
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "503":
          description: Backup storage not configured
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  /v1/resources/{id}/backups/{backupId}/restore:
    post:
      operationId: restoreBackup
      summary: Restore a backup
      description: |
        Restores a backup into the live database using pg_restore.
        **Warning:** This overwrites the current database contents. The database must be Running.
        Returns 202 immediately; restore runs in background (takes ~1–5 minutes depending on size).
      tags: [Backups]
      security:
        - bearerAuth: []
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
        - name: backupId
          in: path
          required: true
          schema:
            type: string
      responses:
        "202":
          description: Restore started
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Success"
        "401":
          $ref: "#/components/responses/Unauthorized"
        "404":
          description: Database or backup not found
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"
        "503":
          description: Backup storage not configured
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

  # ── Metrics endpoint ─────────────────────────────────────────────────────────

  /v1/resources/{id}/metrics:
    get:
      operationId: getDatabaseMetrics
      summary: Get database metrics
      description: |
        Returns the latest collected metrics: active connections, memory usage, CPU usage,
        storage consumed, and query statistics. Collected every 30 seconds by the metrics sidecar.
      tags: [Metrics]
      security:
        - bearerAuth: []
      parameters:
        - name: id
          in: path
          required: true
          schema:
            type: string
      responses:
        "200":
          description: Latest metrics
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Metrics"
              example:
                status: success
                data:
                  connActive: 3
                  connIdle: 2
                  connTotal: 5
                  connMax: 100
                  memUsageBytes: 134217728
                  memLimitBytes: 268435456
                  cpuUsageUsec: 45000
                  storageBytes: 52428800
                  recordedAt: "2025-01-01T12:00:00Z"
        "401":
          $ref: "#/components/responses/Unauthorized"
        "404":
          description: Database not found
          content:
            application/json:
              schema:
                $ref: "#/components/schemas/Error"

tags:
  - name: Ephemeral
    description: No-auth throwaway databases. Perfect for quick experiments and CI pipelines.
  - name: Databases
    description: Persistent databases tied to your account. Require an API key.
  - name: Backups
    description: pg_dump backups stored in S3. 7-day retention by default.
  - name: Metrics
    description: Real-time connection, memory, CPU, and storage metrics.
