Access to Sunbird datasets via APIs

Business Requirement

Sunbird provides the capability to generate various datasets for reporting purposes. Some of these can be downloaded by users via the UI (the Course progress exhaust, for example), whereas others are used by the Sunbird portal to generate reports and charts, which can then be viewed by users.

There is a requirement for making all such datasets that are generated by SB to be made accessible via APIs. This will permit these datasets to be pulled via APIs, for usage in whatever additional manner they see fit - and enable them to create custom reports/ visualizations of their own.

Currently, following are the list of Datasets supported within SB:

Druid based Datasets/Reports (created via HE/APIs)
Custom datasets created as data products and configured manually
1. Ex: VDN reports, WFS, Collection Summary dataset
On-Demand Datasets

Sunbird Obsrv’s Data Service allows creation of reusable custom datasets and an ability to submit requests for generation of such reports. The datasets that are currently configured are course progress exhausts, the user information exhausts and the assessment response exhaust reports. These reports are are run on preset frequency or on-demand basis.
Sunbird Obsrv’s Report Service allows creation of datasets that can be published as reports and charts that can be viewed from Sunbird Portal’s Admin Dashboard web UI. These reports are generated based on the frequency configured while publishing the report.
Other custom data files such as Consent files and master data files (geo data, for example).

Sunbird Obsrv Data Service

Sunbird Obsrv Report Service

Dataset download considerations:

Sunbird should provide an ability to provision for data security and allow access to datasets based on the nature of the data they contain. Consequently, a dataset that is not considered sensitive may be made available for public access, whereas any sensitive data may require to be protected.

In order to enable different types of access, SB will support configurations that can make the datasets ‘PUBLIC’ or ‘PROTECTED’. The type of access will determine whether a dataset can be accessed publicly using the API without any additional authorization, or whether it needs to validate the user before providing access.

Current Implementation

Currently, Sunbird Obsrv supports Reports Service API to list and access all published reports. However, only metadata of a report is accessible through the Report Service API. The data files and associated data are not currently accessible through the Report Service API.
The Sunbird portal allows access to the configured datasets using the Sunbird portal backend APIs. However, the APIs make use of the authorization through the Sunbird portal’s session logins with the access controls that are controlled by different roles in the Sunbird portal such as REPORT_ADMIN, REPORT_VIEWER and ORG_ADMIN.
Data isolation is supported by performing a slug or org based validation so that the access to a specific tenant’s data is controlled using the user’s login context.
The Sunbird user’s login context is used for parameterized reports where the parameters are injected from the user’s login context. The list of supported parameters are:
- $slug
- $channel
- $state
- $board

Below diagram explains the high level working flow on how the reports are rendered on the Portal Admin Dashboard.

Roles which are supported in Admin Dashboard workflow
- Report_Admin
- Report_Viewer
- Super_Admin - INSTANCE_ADMIN

Pages	REPORT_ADMIN <SUPER_ADMIN>	State REPORT_ADMIN	REPORT_VIEWER

Pages

REPORT_ADMIN <SUPER_ADMIN>

State REPORT_ADMIN

REPORT_VIEWER

List Page

Can see all Reports.
If report is parameterized, can see all the resolved parameterized versions of that report.
Can see all live, retired and draft reports

Can see reports + parameterized reports from only the resolved parameter value.
For Example - if Report is parameterized by state. Then REPORT_ADMIN belonging to Rajasthan can see only the Rajasthan report version, not others.
Can see all live, retired and draft reports.
Can see datasets tab.

Can see only live reports .

Report Detail Page

Can Publish, Retire, Add Report/Chart Summaries for all the reports + their parameterized versions.

Switch b/w other parameter versions.

Can publish, Retire, Add Report/Chart Summaries for that report.

Can view only live reports
Cannot perform any other action.

Current Report Config

reportid varchar(40) NOT NULL PRIMARY KEY,
title text NOT NULL,
description text NOT NULL,
authorizedroles jsonb NOT NULL,
status varchar(8) NOT NULL CHECK (status IN ('live', 'draft', 'retired')) DEFAULT 'draft',
type varchar(8) NOT NULL CHECK (type in ('public', 'private')) DEFAULT 'private',
reportaccessurl text NOT NULL UNIQUE,
createdon timestamptz NOT NULL DEFAULT now(),
updatedon timestamptz NOT NULL DEFAULT now(),
createdby varchar(50) NOT NULL,
reportconfig jsonb NOT NULL,
templateurl text,
slug varchar(10) NOT NULL,
reportgenerateddate timestamptz NOT NULL DEFAULT now(),
reportduration jsonb NOT NULL DEFAULT jsonb_build_object('startDate', now()::timestamptz, 'endDate', now()::timestamptz),
tags jsonb NOT NULL,
updatefrequency text NOT NULL
reportType varchar(8) NOT NULL DEFAULT 'report'

New Column Additions

visibility
- type - varchar(10)
- enum - [public, private, protected]
- Details - Link
accessPath
- type - jsonb
- Details - Link
visibilityFlags
- type- jsonb
- Description - Whether to show/hide the report on specific pages.

Access Control Spec

Controls who can access a report based on certain rules.
This can be achieved using two attributes visibility and accessPath.

Visibility

It can be defined both at the report level or it's children (i.e table, chart, map etc ) within a report.

Visibility	Access

Visibility

Access

public (default)

Accessible by all users. Anyone can discover and consume these Reports.

protected

Accessible only to a limited set of users based on a criteria defined by the access path attribute.

Default can be user with REPORT_VIEWER role belonging to the same channel or tenant.

private

Similar to “protected” - accessible only by the creator of the report.

AccessPath

This attribute is applicable for Reports with “protected” or “private” visibility only. This attribute can be used to restrict the access based on or more of the following criteria: organisation, role, group, user id, and location.

AccessPath interface is as follows :-
_{**Note - All keys are optional. Also very fine grained accessPath may not be required in this case. Replicating the actual schema defined for ActionSets as is and to be re-used for datasets as well.}

interface IAccessPath {
  organisation: Array<string> | string;
  role: Array<string> | string;
  channel: Array<string> | string;
  group: Array<string> | string;
  userType: Array<string> | string;
  framework: Array<string> | string;
  isSuperAdmin: Array<string> | string;
  board: Array<string> | string;
  userId: Array<string> | string;
  userLocation: {
    state: Array<string> | string;
    district: Array<string> | string;
    block: Array<string> | string;
  };
  ...anyOtherAttribute: any
}

Current API Structure

Report Service Documentation

This API is associated with viewing and reading out the specific report on the Sunbird Platform. It returns only the metadata information for the report.

Response

{
  "id": "string",
  "ver": "string",
  "params": {
    "resmsgid": "string",
    "msgid": "string",
    "status": "success",
    "err": "string",
    "errmsg": "string"
  },
  "responseCode": "OK",
  "result": {
    "reports": [
      {
        "reportid": "string",
        "title": "string",
        "description": "string",
        "authorizedroles": [
          "string"
        ],
        "status": "string",
        "type": "string",
        "reportaccessurl": "string",
        "createdon": "string",
        "updatedon": "string",
        "createdby": "string",
        "reportconfig": {
          "id": "string",
          "label": "string",
          "table": [
            {
              "name": "string",
              "valuesExpr": "string",
              "columnsExpr": "string",
              "config": {}
            }
          ],
          "title": "string",
          "charts": [
            {
              "colors": [
                {
                  "borderColor": "string",
                  "backgroundColor": "string"
                }
              ],
              "options": {
                "title": {
                  "text": "string",
                  "display": true,
                  "fontSize": 16
                },
                "legend": {
                  "display": false
                },
                "scales": {
                  "xAxes": [
                    {
                      "scaleLabel": {
                        "display": true,
                        "labelString": "string"
                      }
                    }
                  ],
                  "yAxes": [
                    {
                      "scaleLabel": {
                        "display": true,
                        "labelString": "string"
                      }
                    }
                  ]
                },
                "tooltips": {
                  "mode": "string",
                  "intersect": false,
                  "bodySpacing": 5,
                  "titleSpacing": 5
                },
                "responsive": true
              },
              "datasets": [
                {
                  "label": "string",
                  "dataExpr": "string"
                }
              ],
              "chartType": "string",
              "labelsExpr": "string",
              "dataSource": {
                "ids": [
                  "parameterizedPath"
                ],
                "commonDimension": "string"
              }
            }
          ],
          "dataSource": [
            {
              "id": "parameterizedPath",
              "path": "/reports/$state/abc.json"
            },
            {
            "id": "nonParameterizedPath",
            "path": "/reports/tn/abc.json"
            }
          ],
          "description": "string",
          "downloadUrl": "string"
        },
        "slug": "string",
        "reportgenerateddate": "string",
        "reportduration": {
          "enddate": "string",
          "startdate": "string"
        },
        "tags": [
          "string"
        ],
        "updatefrequency": "string",
        "parameters": [
          "string"
        ],
        "report_type": "string"
      }
    ],
    "count": 1
  }
}

Datasource Schema

interface IDatasource {
  id: string
  path: string
}

id: Job id for the dataset

path: Endpoint for downloading the dataset file(s) - the path can be both parameterized and non parameterized . Portal backend populates the parameters using logged in user context details and downloads the respective file.

Proposed Solution

API to get materialized data Files

There is a need to create API in report service that will provide access to the meta data as well as the report data files that are used to generate the reports in the 'Admin dashboards' page on the Sunbird portal with certain access controls.

Proposed API Structure to get the metadata + datasets.

METHOD - GET

URL: /report/datasets/get/:reportId?from=<>&to=<>&since=<>

Proposed response structure - to get meta + datasets.

Success Scenario - Status Code 200

{
    "id": "api.report.read",
    "ver": "string",
    "ts": "timestamp",
    "params": {
      "resmsgid": "string",
      "msgid": "string",
      "status": "success",
      "err": "string",
      "errmsg": "string"
    },
    "responseCode": "OK",
    "result": {
        "metadata": {... similar to above API},
        "datasets": [
            {
                "dataset_id": "dataset_id_1"
                "isParameterized": true,
                "parameters": ["$state"],
                "data": {
                    "rj": {
                        "signedUrl": "url"
                    },
                    "tn": {
                        "signedUrl": "url"
                    },
                    "...otherParameters": {
                        "signedUrl": "url"
                    }
                }
            },
            {
                "dataset_id": "dataset_id_2"
                "isParameterized": false,
                "parameters": null,
                "data": {
                    "default": {
                        "signedUrl": "url"
                    }
                }
            }
        ]
    }
}

Explanation -

If the report dataset file path is parameterized then
- isParameterized - true
- parameters - parameter attribute
- data object will contain all resolved parameter values along with the signed url to download the file
Else
- data object will have default key with the signed url to download the file.

Error Response

Unauthorized Access - Status Code 401

{
    "id": "api.report.read",
    "ver": "v1",
    "ts": "timestamp",
    "params": {
        "resmsgid": null,
        "msgid": "string",
        "err": "UNAUTHORIZED_USER",
        "status": "UNAUTHORIZED_USER",
        "errmsg": "You are not authorized."
    },
    "responseCode": "UNAUTHORIZED",
    "result": {}
}

Internal Server Error - Status Code - 500

{
  "id": "api.report.read",
  "ver": "v1",
  "ts": "timestamp",
  "params": {
    "resmsgid": null,
    "msgid": "string",
    "err": "string",
    "status": "SERVER_ERROR",
    "errmsg": "string"
  },
  "responseCode": "SERVER_ERROR",
  "result": {}
}

Invalid Report Id - Status Code 404

{
  "id": "api.report.read",
  "ver": "1.0.0",
  "params": {
    "resmsgid": "string",
    "msgid": null,
    "status": "failed",
    "err": null,
    "errmsg": "no report found"
  },
  "responseCode": "string",
  "result": {}
}

Search Report API

This API is associated with Searching Reports on the Sunbird Platform.

METHOD - POST

URL - /report/list

Example of Request payload with visibility and accessPath

{
    "request": {
        "filters": {
            "visibility": "protected",
            "accessPath": {
                "organisation": [
                    "ORG_001"
                ],
                "tenant": "tn",
                "role": [
                    "REPORT_ADMIN",
                    "ORG_ADMIN"
                ]
            }
        }
    }
}

Success Response - Status Code 200

{
  "id": "api.report.list",
  "ver": "1.0.0",
  "params": {
    "resmsgid": "string",
    "msgid": "string",
    "status": "string",
    "err": "string",
    "errmsg": "string"
  },
  "responseCode": "string",
  "result": {
    "reports": [
      {
        ...metadata
      }
    ],
    "count": 1
  }
}

Error Response - Status code 500

{
  "id": "api.report.list",
  "ver": "v1",
  "ts": "timestamp",
  "params": {
    "resmsgid": null,
    "msgid": "string",
    "err": "string",
    "status": "SERVER_ERROR",
    "errmsg": "string"
  },
  "responseCode": "SERVER_ERROR",
  "result": {}
}

Sunbird Design