~[wc:commonscripts]
~[wc:admin_header_css] ~[text:psx.html.admin_reports.runreports.start_page] > PowerSchool Customization ~[wc:admin_navigation_css] ~[SetPostValue:tab=api]

# PowerSchool Customization

~[x:insertfile;tabs.html]

## API - General

- API - Application Programming Interface
- Provides a gateway to read and write data to/from the PS database
- External Access - Third-party applications accessing/modifying PowerSchool data via the API (OAuth)
- Internal Access - Use JavaScript within PowerSchool pages to interact with the API (session cookies and permission mappings)

This page is intended to serve as a high-level introduction to the PowerSchool API. For in-depth documentation on API features, visit the PowerSchool Developer Site.

### Resources (Endpoints)

API resources (also known as endpoints) are URLs that can be accessed via HTTP requests to perform API operations.
Endpoints are listed on the PowerSchool Developer site: PowerSchool API Endpoints
There are several endpoints available in the PS API. From a customization perspective, two of the most common endpoints are used for accessing PowerQueries and extended tables.

Endpoint | HTTP Method | Function
--- | --- | ---
/ws/schema/query/[queryName] | POST | Retrieve the results of a PowerQuery
/ws/schema/query/[queryName]/count | POST | Retrieve the count of results of a PowerQuery
/ws/schema/table/[tableName] | GET | Retrieve multiple rows from an extended table
/ws/schema/table/[tableName]/count | GET | Retrieve row count from an extended table
/ws/schema/table/[tableName]/[id] | GET | Retrieve a single row from an extended table
/ws/schema/table/[tableName]/[id] | POST | Insert a single row into an extended table
/ws/schema/table/[tableName]/[id] | PUT | Update a single row in an extended table
/ws/schema/table/[tableName]/[id] | DELETE | Delete a single row from an extended table

### External Authentication (OAuth)

PowerSchool uses open authentication (OAuth) to secure API access for outside software systems.

1. The third party is supplied with a client id and secret
2. The third party submits the client id and secret to the PS API
3. The API responds with an access token
4. The third party must provide this access token with each API call

Client ID and Secret
When a plugin is installed with a plugin.xml file that includes a <oauth/> tag, PowerSchool will generate a client id and secret. These can be retrieved by clicking the plugin name in the Plugin Management Console and then clicking the Data Provider Configuration link at the bottom of the screen. Having a valid client id and secret will not give a third party unbridled access to all resources in the API.
Plugins that include OAuth must also include Data Access Requests.
These data access requests define what resources (fields and PowerQueries) can be accessed by a system using the associated credentials.
To see what data access requests are included in a plugin, click on the plugin name from the Plugin Management Console, and then expand the Data Access Requests section.

### Internal Authentication

When a page within the PowerSchool application makes a call to the API, OAuth is not required. Instead, API access is determined based on the user's security in combination with permission mappings.
Permission mappings are defined in a plugin XML file.
plugin_folder/permissions_root/[filename].permission_mappings.xml

`<permission_mappings> <permission name='/admin/home.html'> <implies allow="post">/ws/schema/table/U_Table_Name</implies> <implies allow="put">/ws/schema/table/U_Table_Name/#</implies> <implies allow="delete">/ws/schema/table/U_Table_Name/#</implies> <implies allow="get">/ws/schema/table/U_Table_Name/#</implies> <implies allow="get">/ws/schema/table/U_Table_Name/count</implies> <implies allow="get">/ws/schema/table/U_Table_Name</implies> </permission> </permission_mappings>`

<permission_mappings> - Root element.
<permission name='/admin/[pagename].html'> - Users with access to the identified page will have the implied permissions. There may be multiple <permission> elements in a document.
<implies allow='HTTP_Method'>Endpoint</implies> - Identifies an API endpoint and the allowed HTTP operations.

## API - Example

For the examples below, assume we have the following one-to-many extension of the students table.

U_Lib_Checkout

Column Name | Data Type
--- | ---
ID | Integer
StudentsDcid | Integer
Book_Id | Integer
Checkout_Date | Date
Due_Date | Date
Return_Date | Date
Fine | Double
Fine_Reason | String (varchar2)

Note: Extended tables will also include audit fields (whocreated, whomodified, whencreated, whenmodified). These fields will be maintained by the API automatically.
API access to perform all read/write functions for this table:

`<permission_mappings> <permission name='/admin/home.html'> <implies allow="post">/ws/schema/table/U_Lib_Checkout</implies> <implies allow="put">/ws/schema/table/U_Lib_Checkout/#</implies> <implies allow="delete">/ws/schema/table/U_Lib_Checkout/#</implies> <implies allow="get">/ws/schema/table/U_Lib_Checkout/#</implies> <implies allow="get">/ws/schema/table/U_Lib_Checkout/count</implies> <implies allow="get">/ws/schema/table/U_Lib_Checkout</implies> </permission> </permission_mappings>`

### Retrieve Record Count

`$j.ajax({ "url":'/ws/schema/table/u_lib_checkout/count', "method":"GET" }).done(function(response) { //work with the response });`
AngularJS Version
`$http({ "url": "/ws/schema/table/u_lib_checkout/count", "method": "GET" }).then(function(response){ //work with the response });`

Sample Response

`{"count":9496}`

### Retrieve Multiple Records

`$j.ajax({ "url":'/ws/schema/table/u_lib_checkout', "method":"GET", "data":{ "projection":"id,studentsdcid,checkout_date,due_date,return_date,fine,fine_reason", "pagesize":100, "page":1 } }).done(function(response) { //work with the response });`
AngularJS Version
`$http({ "url": "/ws/schema/table/u_lib_checkout", "method": "GET", "params": { "projection": "id,studentsdcid,checkout_date,due_date,return_date,fine,fine_reason", "page": 1, "pagesize": 100 } }).then(function(response){ //work with the response });`

- projection - comma-delimited list of column names or * for all
- pagesize - number of records per page
- page - page number

Maximum records per page defaults to 100. To retrieve more than 100 records from a table, multiple requests must be made with incrementing page numbers. If a page is requested, but the table does not hold enough records to have results for the requested page, the API will return and empty array.
See the developer site documentation for pagination for more information: Pagination Documentation
Sample Response

`{ "name": "U_Lib_Checkout", "record": [ { "id": 278640, "tables": { "u_lib_checkout": { "studentsdcid": "3", "due_date": "2019-06-28", "id": "278640", "checkout_date": "2019-06-14" } } },{ "id": 279879, "tables": { "u_lib_checkout": { "fine": "4.5", "studentsdcid": "3", "due_date": "2019-07-21", "id": "279879", "fine_reason": "late Return", "checkout_date": "2019-07-11", "return_date": "2019-07-28" } } } ] }`

### Searching

A query expression may be passed to the API to filter results.

`$j.ajax({ "url":'/ws/schema/table/u_lib_checkout', "method":"GET", "data":{ "projection":"id,studentsdcid,checkout_date,due_date,return_date,fine,fine_reason", "q":"studentsdcid==166" } }).done(function(response) { //work with the response });`
AngularJS Version
`$http({ "url": "/ws/schema/table/u_lib_checkout", "method": "GET", "params": { "projection": "id,studentsdcid,checkout_date,due_date,return_date,fine,fine_reason", "q":"studentsdcid==166" } }).then(function(response){ //work with the response });`

The "q" property in the data object provides a search parameter for the request.
Find more information on the Developer Site: Searching

### Retrieve a Specific Record

`$j.ajax({ "url":'/ws/schema/table/u_lib_checkout/71960', "method":"GET", "data":{ "projection":"id,studentsdcid,checkout_date,due_date,return_date,fine,fine_reason" } }).done(function(response) { //work with the response });`

Sample Response

`{ "id": 71960, "name": "U_Lib_Checkout", "tables": { "u_lib_checkout": { "fine": "5", "studentsdcid": "2", "due_date": "2018-11-27", "fine_reason": "Don't like this kid", "checkout_date": "2018-11-13", "return_date": "2019-03-20" } } }`

### Insert a Record

`var newRecord = { "tables":{ "u_lib_checkout":{ "studentsdcid":"3", "book_id":"7", "checkout_date":"2019-07-11", "due_date":"2019-07-21", "return_date":"2019-07-28", "fine":"4.50", "fine_reason":"late Return" } } }; $j.ajax({ "url":"/ws/schema/table/u_lib_checkout", "method":'POST', "data":JSON.stringify(newRecord), "headers":{ "accept":"application/json", "Content-Type":"application/json" } }).done(function(response) { if(response.result[0].status == 'SUCCESS') { //work with the response } else { //handle error } });`
AngularJS Version
`$http({ "url": "/ws/schema/table/u_lib_checkout", "method": 'POST', "data": newRecord, "headers": { "Accept": "application/json", "Content-Type": "application/json" } }).then(function(response){ if(response.data.result[0].status == 'SUCCESS') { //work with the response } else { //handle error } });`

Everything is a string

- Numbers - "##.##"
- Dates - "YYYY-MM-DD"
- Booleans - "false" or "true"

Sample Response

`{ "insert_count": 1, "update_count": 0, "delete_count": 0, "result": [{ "status": "SUCCESS", "action": "INSERT", "success_message": { "id": 279893, "ref": "https://dev1.mba-link.com/ws/schema/table/u_lib_checkout/279893" } }] }`

### Update a Record

`var updateRecord = { "tables":{ "u_lib_checkout":{ "checkout_date":"2018-07-5", "due_date":"2018-07-21", "return_date":"2018-07-28", "fine":"4.55", "fine_reason":"late Return" } } }; $j.ajax({ "url":"/ws/schema/table/u_lib_checkout/71960", "method":"PUT", "data":JSON.stringify(updateRecord), "headers":{ "accept":"application/json", "Content-Type":"application/json" } }).done(function(response) { if(response.result[0].status == 'SUCCESS') { //work with the response } else { //handle error } });`
AngularJS Version
`$http({ "url": "/ws/schema/table/u_lib_checkout/71960", "method": "PUT", "data": updateRecord, "headers": { "Accept": "application/json", "Content-Type": "application/json" } }).then(function(response){ if(response.data.result[0].status == 'SUCCESS') { //work with the response } else { //handle error } });`

Important - Do not include the primary key or foreign key in the update record. The id (primary key) is included in the URL and cannot be modified.

Sample Response

`{ "insert_count": 0, "update_count": 1, "delete_count": 0, "result": [{ "status": "SUCCESS", "action": "UPDATE", "success_message": { "id": 71960, "ref": "https://dev1.mba-link.com/ws/schema/table/u_lib_checkout/71960" } }] }`

Note that the response includes the id of the new record

### Delete a Record

`$j.ajax({ "url":"/ws/schema/table/u_lib_checkout/84244", "method":"DELETE", "headers":{ "accept":"application/json", "Content-Type":"application/json" } }).done(function(response) { //No response for delete });`
AngularJS Version
`$http({ "url": "/ws/schema/table/u_lib_checkout/84244", "method": "DELETE", "headers": { "Accept": "application/json", "Content-Type": "application/json" } }).then(function(response){ //No response for delete });`

There is no response content for a delete.
If the provided id is found, the response status code will be 204 (No Content).
If the provided id is not found, the response status code will be 404 (Not Found).

## No Extra Charge

For brevity, the examples above do not include proper error handling.
Thorough code should also manage potential failures of API calls.

jQuery

`$j.ajax({... }).done(function(response) { //Handle Success }).fail(function(){ //Handle Error });`

AngularJS

`$http({... }).then(function mySuccess(response) { //Handle Success }, function myError(response) { //Handle Error });`

Helpful Functions

`//@param {string} dt (mm/dd/yyyy) //@return {string} (yyyy-mm-dd) function formatDateForApi(dt){ var dateParts = dt.split('/'); var m = dateParts[0]; var d = dateParts[1]; var y = dateParts[2]; return y + '-' + m + '-' + d; }; //@param {string} dt (yyyy-dd-mm) //@return {string} (mm/dd/yyyy) function formatDateFromApi(dt){ var dateParts = dt.split('-'); var y = dateParts[0]; var m = dateParts[1]; var d = dateParts[2]; return m + '/' + d + '/' + y; };`
~[x:insertfile;footer.html] ~[wc:admin_footer_css]