reporting-api-v1.yaml

openapi: 3.0.3
info:
  title: Aam Digital - Reporting API
  description: |-
    API to manage reports that provide data calculated based on any entities of the Aam Digital system
    and offer notifications when data of such reports changes.
  version: 1.0.0
servers:
  - url: https://{customerId}.aam-digital.net/api/v1/reporting
    description: Developer Instance for testing
    variables:
      customerId:
        default: dev
        description: Customer ID assigned by the service provider
tags:
  - name: reporting
    description: Access reports and their results and trigger one-time report calculations.
  - name: notifications
    description: Subscribe to continuous notification events whenever a report's result data changes due to users changing records in the Aam Digital application.

paths:
  /report:
    get:
      security:
        - development:
            - reporting_read
      tags:
        - reporting
      summary: Return list of available Reports
      responses:
        200:
          description: List of all available Reports the requester has access permissions to, empty array if no reports are available
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Report'
        401:
          description: If no valid access token
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        403:
          description: The access token lacks the client scope required for this endpoint
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

  /report/{reportId}:
    get:
      security:
        - development:
            - reporting_read
      tags:
        - reporting
      summary: Return report metadata by ID
      parameters:
        - in: path
          name: reportId
          schema:
            type: string
          required: true
      responses:
        200:
          description: Get details of a report, including details of the data structure (schema) this specific report's data has
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Report'
        403:
          description: The access token lacks the client scope required for this endpoint
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        404:
          description: If the Report does not exist
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        401:
          description: If the access token does not grant permission to this Report
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

  /report-calculation/report/{reportId}:
    get:
      security:
        - development:
            - reporting_read
      tags:
        - reporting
      summary: Return all report calculations for a report
      parameters:
        - in: path
          name: reportId
          schema:
            type: string
          required: true
      responses:
        200:
          description: List of metadata of all calculations triggered by any user (pending and completed)
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/ReportCalculation'
        401:
          description: If the access token does not grant permission to this Report
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        403:
          description: The access token lacks the client scope required for this endpoint
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        404:
          description: If report does not exist
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

    post:
      security:
        - development:
            - reporting_write
      tags:
        - reporting
      summary: Trigger a new report calculation run.
      description: Trigger a new report calculation run. Check status of the asynchronous calculation via the /report-calculation endpoint
        As an alternative to triggering a single report calculation, you can subscribe to receive
        a notification event (which includes an automatically created calculationId) whenever the report's
        results change (see /webhook/{webhookId}/subscribe/report).
      parameters:
        - in: path
          name: reportId
          schema:
            type: string
          required: true
        - in: query
          name: from
          description: start date for report period
          required: false
          schema:
            type: string
            format: date
        - in: query
          name: to
          description: end date for report period
          required: false
          schema:
            type: string
            format: date
      responses:
        200:
          description: Return calculation identifier, to be used to check status and result data
          content:
            application/json:
              schema:
                type: object
                properties:
                  id:
                    type: string
        400:
          description: If the report is not an SQL report or its configuration is invalid
            (e.g. a reportDefinition entry without a query). errorMessage names the invalid entry.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        403:
          description: The access token lacks the client scope required for this endpoint
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        404:
          description: If the report does not exist
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        401:
          description: If the access token does not grant permission to this Report
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

  /report-calculation/{calculationId}:
    get:
      security:
        - development:
            - reporting_read
      tags:
        - reporting
      summary: Return metadata for a report calculation
      parameters:
        - in: path
          name: calculationId
          schema:
            type: string
          required: true
      responses:
        200:
          description: Status and details of the given report calculation run
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ReportCalculation'
        401:
          description: If the access token does not grant permission to this Report
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        403:
          description: The access token lacks the client scope required for this endpoint
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        404:
          description: If the calculation identifier does not exist
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

  /report-calculation/{calculationId}/data:
    get:
      security:
        - development:
            - reporting_read
      tags:
        - reporting
      summary: Fetch actual report data for a specific calculation
      parameters:
        - in: path
          name: calculationId
          schema:
            type: string
          required: true
      responses:
        200:
          description: The actual data that has been calculated by the calculation run. 
            Note that if the report was calculated successfully but has an empty result, this is still returned here, not as an 404 error.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ReportMetadata'
        403:
          description: The access token lacks the client scope required for this endpoint
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        404:
          description: report data is not available yet (when either the calculation id is not valid or the calculation is still running).
            Note that if the report calculation was successfully completed but the result is empty (because no data matched the report's query)
            then this is not returning a 404 error but a 200 with an empty array.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        401:
          description: If the access token does not grant permission to this Report
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

  /report-calculation/{calculationId}/data-stream:
    get:
      security:
        - development:
            - reporting_read
      tags:
        - reporting
      summary: Fetch just the report data for a specific calculation as stream
      parameters:
        - in: path
          name: calculationId
          schema:
            type: string
          required: true
      responses:
        200:
          description: >-
            The actual data that has been calculated by the calculation run. Served as a
            stream (request with an octet-stream Accept header), but the response body is
            the JSON report data.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/ReportData'
        403:
          description: The access token lacks the client scope required for this endpoint
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        404:
          description: report data is not available yet (when either the calculation id is not valid or the calculation is still running).
            Note that if the report calculation was successfully completed but the result is empty (because no data matched the report's query)
            then this is not returning a 404 error but a 200 with an empty array.
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        401:
          description: If the access token does not grant permission to this Report
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

  # notification

  /webhook:
    get:
      security:
        - development:
            - reporting_read
      tags:
        - notifications
      summary: Return list of existing Webhooks for your authorized client
      responses:
        200:
          description: List of all available Webhooks the requester has created, empty array if no Webhooks are available for the client authorized in the given access token
          content:
            application/json:
              schema:
                type: array
                items:
                  $ref: '#/components/schemas/Webhook'
        401:
          description: If no valid access token
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        403:
          description: No permissions to access the resource
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

    post:
      security:
        - development:
            - reporting_write
      tags:
        - notifications
      summary: Subscribe to events for a specific Report
      description: Register a webhook to be called for one or more notification subscriptions. To receive events, make calls to your webhookId's /subscribe endpoint.
      requestBody:
        required: true
        content:
          application/json:
            schema:
              $ref: '#/components/schemas/WebhookConfiguration'
      responses:
        200:
          description: Webhook registered successfully, you can now register subscriptions
          content:
            application/json:
              schema:
                type: object
                properties:
                  id:
                    type: string
        401:
          description: If no valid access token
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        403:
          description: The access token lacks the client scope required for this endpoint
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

  /webhook/{webhookId}:
    get:
      security:
        - development:
            - reporting_read
      tags:
        - notifications
      summary: Return a specific Webhook
      parameters:
        - in: path
          name: webhookId
          schema:
            type: string
          required: true
      responses:
        200:
          description: Get details of a webhook, including details of subscribed events and configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Webhook'
        401:
          description: If no valid access token
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        403:
          description: No permissions to access the resource
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        404:
          description: Resource not found
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

  /webhook/{webhookId}/subscribe/report/{reportId}:
    post:
      security:
        - development:
            - reporting_write
      tags:
        - notifications
      summary: Subscribe to events for a specific Report
      description: If you subscribe to a reportId, Aam Digital will continuously monitor the changes users
        make in the application and check if they affect the results calculated by that report.
        Whenever this is the case, a new report-calculation is automatically triggered for you and once
        the calculation is completed you receive an event containing that calculationId. You can use
        this to immediately fetch to available report data from the /report-calculation/{calculationId}/data
        endpoint. Upon subscribing to a report, you will always receive an initial event with a
        calculationId through which you can access the current state of the report's data.
      externalDocs:
        url: https://github.com/Aam-Digital/query-backend/blob/main/README.md#subscribe-to-continuous-changes-of-a-report
      parameters:
        - in: path
          name: webhookId
          schema:
            type: string
          required: true
        - in: path
          name: reportId
          schema:
            type: string
          required: true
      responses:
        200:
          description: Report added to webhook subscription
        401:
          description: If no valid access token
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        403:
          description: No permissions to access the resource
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        404:
          description: Resource not found
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

    delete:
      security:
        - development:
            - reporting_write
      tags:
        - notifications
      summary: Remove subscription for a specific Report
      parameters:
        - in: path
          name: webhookId
          schema:
            type: string
          required: true
        - in: path
          name: reportId
          schema:
            type: string
          required: true
      responses:
        200:
          description: Report deleted from webhook subscription
        401:
          description: If no valid access token
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        403:
          description: No permissions to access the resource
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        500:
          description: On internal server errors or broken server configuration
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'
        502:
          description: If the Aam Digital backend API is not enabled for that system
          content:
            application/json:
              schema:
                $ref: '#/components/schemas/Error'

components:
  schemas:
    # report
    Report:
      type: object
      properties:
        id:
          type: string
        name:
          type: string
          description: |
            The title of the ReportConfig. If the title is configured in several languages, this is
            its "en-US" text, else its "en" text, else the first non-blank text of any language.
            Empty if the ReportConfig has no title.
          example: report_all_course_members
        schema:
          type: object
          nullable: true
          description: The data structure (column names) of this report's result data.
          properties:
            fields:
              type: array
              items:
                type: string

    ReportCalculation:
      type: object
      properties:
        id:
          type: string
        report:
          type: object
          properties:
            id:
              type: string
        status:
          type: string
          description: Current status of the run
          enum:
            - PENDING
            - RUNNING
            - FINISHED_SUCCESS
            - FINISHED_ERROR
        startDate:
          type: string
          description: The Date, the calculation starts processing the query
          example: date
          nullable: true
        endDate:
          type: string
          description: The Date, the calculation finished processing and the data is available
          example: date
          nullable: true
        args:
          type: object
          description: >-
            Input arguments injected into the report's SQL query placeholders (for example
            $startDate and $endDate). A free-form map of string keys to string values.
            The canonical date-range keys are "startDate" and "endDate"; for backwards
            compatibility the legacy keys "from" and "to" may also appear.
          additionalProperties:
            type: string
        data:
          type: object
          nullable: true
          properties:
            contentType:
              type: string
              example: application/octet-stream
            hash:
              type: string
              example: md5-pWo9Wj3ZZ27RqJUoh8C6VA==
            length:
              type: number
              example: 368
        errorDetails:
          type: string
          nullable: true
          description: >-
            Details of the failure when the calculation finished with status FINISHED_ERROR.
            If the report's query is invalid, this is the query service's explanation of why,
            so it can be shown to the author of the report; for any other failure it is "Unknown error".
          example: 'near "FROM": syntax error'

    ReportMetadata:
      type: object
      properties:
        id:
          type: string
          example: ReportCalculation:56f22a52-5863-4d24-9da6-239a34ea83ba_data.json
        report:
          type: object
          properties:
            id:
              type: string
              example: ReportConfig:example
        calculation:
          type: object
          properties:
            id:
              type: string
              example: ReportCalculation:56f22a52-5863-4d24-9da6-239a34ea83ba
        dataHash:
          type: string
          description: Hash of the calculated data, identical to the stored attachment digest.
            Use it to detect whether a report's result actually changed between two calculations.
          example: md5-pWo9Wj3ZZ27RqJUoh8C6VA==
        data:
          $ref: '#/components/schemas/ReportData'



    # notification

    Webhook:
      type: object
      properties:
        id:
          type: string
        label:
          type: string
          example: wh_aam_digital_reporting
        target:
          $ref: '#/components/schemas/WebhookTarget'
        authentication:
          type: object
          properties:
            type:
              type: string
              example: API_KEY
        owner:
          $ref: '#/components/schemas/WebhookOwner'
        reportSubscriptions:
          description: The reports for which the webhook will receive a notification event whenever their results change due to users managing data in the Aam Digital system.
          type: array
          items:
            type: string
          example: [ "report_id_1", "report_id_2", "report_id_3" ]
        createdAt:
          description: When the webhook was registered. Absent for webhooks created before this field was introduced.
          type: string
          format: date-time
          nullable: true

    ReportData:
      type: array
      description: |-
        The calculated report result. Its top-level structure depends on the report definition:

        * Single-query report: `data` is the flat list of result rows, each row a JSON object of
          column name -> value, e.g.
          `[ { "name": "Alice", "privateSchool": 1 }, { "name": "Bob", "privateSchool": 0 } ]`.

        * Report with multiple queries and/or groups: `data` holds one entry per top-level report
          item, in definition order, where
            - a query item contributes an array of its result rows (`[ { ... }, { ... } ]`), and
            - a group item contributes a single-key object `{ "<groupTitle>": [ ... ] }` whose value
              is, recursively, the list of that group's item results.

        A successful calculation that matched no data returns an empty array (`[]`).
      items:
        oneOf:
          - type: object
            description: >-
              Either a single result row (column name to value) for single-query reports,
              or a group node keyed by its group title, of the form { "groupTitle": [ ... ] }.
          - type: array
            description: The result rows of one query; used when the report has multiple items.
            items:
              type: object
      example: |-
        [
          [
            { "New students": 6 }
          ],
          {
            "Students gender": [
              [ { "male": 6 } ],
              [ { "female": 4 } ]
            ]
          },
          [
            { "count": 3, "project": "FOO", "school": "SchoolA" },
            { "count": 1, "project": "BAR", "school": "SchoolB" }
          ]
        ]

    WebhookConfiguration:
      type: object
      description: Request body to register a new webhook.
      properties:
        label:
          type: string
          example: wh_aam_digital_reporting
        target:
          $ref: '#/components/schemas/WebhookTarget'
        authentication:
          type: object
          properties:
            type:
              type: string
              enum:
                - API_KEY
            apiKey:
              type: string

    WebhookTarget:
      type: object
      properties:
        method:
          type: string
          enum:
            - GET
            - POST
        url:
          type: string
          example: https://example.com/hook/{reportId}

    WebhookOwner:
      type: object
      properties:
        creator:
          type: string
        users:
          type: array
          items:
            type: string
        groups:
          type: array
          items:
            type: string
        roles:
          type: array
          items:
            type: string

    Event:
      type: object
      description: Representation of the payload we will send to the webhook
      properties:
        id:
          type: string
        eventName:
          type: string
          enum:
            - NEW_REPORT_DATA_AVAILABLE
        reportId:
          type: string
        calculationId:
          type: string
        created_at:
          type: string
          format: date

    # global

    Error:
      type: object
      properties:
        errorCode:
          type: string
          description: Machine-readable error code — either a business code
            (e.g. NOT_FOUND, INTERNAL_SERVER_ERROR) or, for framework-level
            auth errors, the HTTP status code as a string (e.g. "401").
        errorMessage:
          type: string

  securitySchemes:
    development:
      type: oauth2
      description: |-
        This API uses OAuth2 with the Client Credentials Flow.
        The API client needs the listed client scopes assigned as "Default" client scopes in Keycloak,
        so that they are included in its access tokens.
        Users of the Aam Digital frontend app are authorized without these scopes.
      flows:
        clientCredentials:
          tokenUrl: https://keycloak.aam-digital.net/realms/TolaData/protocol/openid-connect/token
          scopes:
            reporting_read: Read Report and ReportCalculation
            reporting_write: Trigger new ReportCalculation and configure webhook notifications