Reporting API Technical Article | HealthRota Help Centre

1. Overview and architecture

The Reporting API is a read-only (GET-only) external JSON API that gives third-party systems — BI tools (Power BI, Excel), data warehouses and integrations — a paginated, filterable view of a trust's core HealthRota data. All data is scoped to a single trust.

Property	Detail
Protocol	HTTPS, JSON
Methods	GET only — all endpoints are read-only
Page size	5,000 records per page (fixed)
Pagination	Cursor-based (`after` / `before`)
Trust scoping	Every query is scoped to the `X-TRUST-ID` trust
Auth	AWS Cognito JWT, validated at the API Gateway

2. Environments and base URLs

Path prefix for every endpoint: /api/external/reporting/v1/{resource}.

Environment	API base URL	Cognito App Client ID
Production	`https://api.healthrota.co.uk`	`52hlmlduhi5r5kue8hgrsj9vfi`
Preprod	`https://api-preprod.healthrota.co.uk`	`5nltpqk42t74n1tk29uuffp4i3`
Staging	`https://api-staging.healthrota.co.uk`	`5nltpqk42t74n1tk29uuffp4i3`
Sandbox	`https://api-sandbox.healthrota.co.uk`	`5nltpqk42t74n1tk29uuffp4i3`
Testing	`https://api-testing.healthrota.co.uk`	`5nltpqk42t74n1tk29uuffp4i3`
Temp	`https://api-temp.healthrota.co.uk`	`5nltpqk42t74n1tk29uuffp4i3`

All non-prod environments share one Cognito user pool and App Client. Cognito lives in region eu-west-2; the token endpoint is https://cognito-idp.eu-west-2.amazonaws.com/.

3. Authentication with AWS Cognito

Callers authenticate with a Cognito username/password, receive a JWT (the IdToken), and send it as a Bearer token on every request. The API Gateway validates it and derives the trust.

3.1 Getting access (one-time setup)

See our article Setting up the Reporting API for more information on this.

3.2 Step 1 — get an IdToken (InitiateAuth)

POST https://cognito-idp.eu-west-2.amazonaws.com/
X-Amz-Target: AWSCognitoIdentityProviderService.InitiateAuth
Content-Type: application/x-amz-json-1.1

{
  "AuthFlow": "USER_PASSWORD_AUTH",
  "ClientId": "example_client_ID",
  "AuthParameters": { "USERNAME": "[email protected]", "PASSWORD": "your-password" }
}

Response (abbreviated):

{
  "AuthenticationResult": {
    "AccessToken": "ey...",
    "IdToken":     "ey...",
    "RefreshToken":"ey...",
    "ExpiresIn":   300,
    "TokenType":   "Bearer",
    "NewDeviceMetadata": { "DeviceKey": "eu-west-2_...", "DeviceGroupKey": "..." }
  }
}

If the response is a NEW_PASSWORD_REQUIRED challenge, the user still has a temporary password and must set a permanent one first.

3.3 Token lifetimes

Token	Used for	Expiry
IdToken	The one you send to the API as the Bearer token	1 day
AccessToken	Cognito self-service operations (e.g. confirm device)	5 minutes
RefreshToken	Getting a fresh IdToken without re-entering credentials	30 days

3.4 Step 2 — call the API with the IdToken

GET /api/external/reporting/v1/work_items 
Host: api-preprod.healthrota.co.uk 
Authorization: Bearer THE_ID_TOKEN 
Accept: application/json

Do you send X-TRUST-ID yourself? In deployed environments, no — the API Gateway injects it from your token's trust claim, and any value you send is overwritten.

3.5 Refreshing the IdToken

Device tracking is enabled, so refreshing needs the DeviceKey returned at first login (confirm the device once via ConfirmDevice):

POST https://cognito-idp.eu-west-2.amazonaws.com/
X-Amz-Target: AWSCognitoIdentityProviderService.InitiateAuth
Content-Type: application/x-amz-json-1.1

{
  "AuthFlow": "REFRESH_TOKEN_AUTH",
  "ClientId": "example_client_ID",
  "AuthParameters": { "REFRESH_TOKEN": "ey...", "DEVICE_KEY": "eu-west-2_..." }
}

3.6 Password reset / hosted login links

Non-prod:api-auth-non-prod.healthrota.co.uk/login
Production:api-auth-production.healthrota.co.uk/login

4. Swagger / OpenAPI documentation

Item	Value
Spec file (in repo)	`swagger/external/reporting/v1/swagger.json`
OpenAPI version	3.1.0
API title / version	HealthRota Reporting API / v1.0.1
Hosted Swagger UI	`{env}/api-docs` — e.g. healthrota.co.uk/api-docs, staging.healthrota.co.uk/api-docs

The Swagger UI is behind HTTP Basic auth in every non-development environment. Please ask Support for access to this resource.

5. Request basics

Headers

Header	Required	Purpose
`Authorization`	Yes	Bearer plus the Cognito IdToken. Validated by the API Gateway.
`X-TRUST-ID`	Yes (data endpoints)	Scopes the query to a trust. Injected by the Gateway from the token; set manually only when calling Rails directly. Missing/invalid gives a 400. The `meta` endpoint does not require it.
`Accept`	Recommended	`application/json`

Response envelope

Every list response is wrapped in a data object with cursor fields and the item collection:

{
  "errors": [],
  "data": {
    "previous_page": null,
    "next_page": "eyJpZCI6NTAwMH0=",
    "items": [ { }, { } ]
  }
}

next_page is null on the last page; previous_page is null on the first. (The updates endpoint returns items as an object keyed by entity type — see section 10.)

6. Pagination

Cursor-based, fixed 5,000 records per page. Pass at most one of:

Param	Meaning
`after`	Return the page after this cursor (forward). Use the `next_page` value from the previous response.
`before`	Return the page before this cursor (backward). Use the `previous_page` value.

Omit both to get the first page. Keep following next_page until it is null.

7. The where filter

Most endpoints accept an optional where query parameter: a single expression string of one or more clauses joined with AND. (It is not where[field]=... bracket params.) Each endpoint only allows a fixed set of fields; an unknown field gives a 400.

Operators

Syntax	Meaning	Example
field = value	Equality	status = "booked"
field != value	Inequality	status != "booked"
field < / <= / > / >= value	Comparison	date >= DateTime(2026-01-01)
field [v1,v2]	IN list	ids [101,102,103]
field in [v1,v2]	IN list (explicit)	type in [WorkItem,TimeSheetItem]
field [>= min,<= max]	Inclusive range	date [>= DateTime(2026-01-01),<= DateTime(2026-12-31)]
field.Contains("s")	Substring match	name.Contains("ward")
field.StartsWith("s")	Prefix match	name.StartsWith("A")
field.EndsWith("s")	Suffix match	name.EndsWith("rota")

Date literals

DateTime(YYYY-MM-DD) — date only, e.g. DateTime(2026-03-15)
DateTime(YYYY-MM-DDTHH:MM:SSZ) — datetime, e.g. DateTime(2026-03-15T09:00:00Z)

Combining and encoding

where = date [>= DateTime(2026-01-01),<= DateTime(2026-01-31)] AND updated_at > DateTime(2026-01-15T00:00:00Z)

The whole where value must be URL-encoded when placed in a query string (spaces, brackets, comparison signs, commas). Most HTTP clients do this for you when you pass it as a query-parameter value.

8. Endpoints

16 endpoints, all GET, all under /api/external/reporting/v1/. "Date filter" indicates whether date is a supported where filter.

Resource (URL)	Model type	Common where filters	Date filter
`meta`	none	none (no `X-TRUST-ID` needed)	—
`organisation_units`	OrganisationUnitType	ids, updated_at	No
`rotas`	RotaType	ids, updated_at	No
`rota_status_periods`	RotaStatusPeriodType	rota_id, updated_at	No
`sub_types`	SubTypeType	ids, updated_at, date	Yes
`users`	UserType	ids, updated_at	Yes
`rota_members`	RotaMemberType	ids, updated_at	Yes
`shift_requirements`	ShiftRequirementType	ids, updated_at, date	Yes
`not_needed_shift_requirements`	NotNeededShiftRequirementType	ids, updated_at, date	Yes
`work_items`	WorkItemType	ids, updated_at, date	Yes
`locum_offer_shifts`	LocumOfferShiftType	ids, updated_at, date, status	Yes
`time_sheet_items`	TimeSheetItemType	ids, updated_at, date, status	Yes
`exception_reports`	ExceptionReportType	ids, updated_at, date	Yes
`job_plans`	JobPlanType	ids, updated_at, date	Yes
`job_plan_activities`	JobPlanActivityType	ids, updated_at, date	Yes
`updates`	UpdateType	from (required), type	Yes

The meta endpoint returns this same directory at runtime (page_fields plus a tables map of name to {url, type, has_date_filter}), so consumers can discover entities dynamically. This is exactly what the Power BI connector consumes.

9. Worked examples

Examples use the preprod host and show the where value unencoded for clarity — URL-encode it in practice.

Discover the entities (meta)

GET https://api-preprod.healthrota.co.uk/api/external/reporting/v1/meta
Authorization: Bearer THE_ID_TOKEN 
Accept: application/json

Work items for January 2026

GET https://api-preprod.healthrota.co.uk/api/external/reporting/v1/work_items     ?where=date [>= DateTime(2026-01-01),<= DateTime(2026-01-31)] 
Authorization: Bearer THE_ID_TOKEN 
Accept: application/json

Follow pagination

GET https://api-preprod.healthrota.co.uk/api/external/reporting/v1/work_items     ?after=eyJpZCI6NTAwMH0=        (the next_page cursor from the previous response) 
Authorization: Bearer THE_ID_TOKEN

Incremental sync (updates)

Poll updates with the timestamp of your last successful sync. It returns the latest create/update/destroy per changed record, grouped by entity type. Filter type to the entities you care about.

GET https://api-preprod.healthrota.co.uk/api/external/reporting/v1/updates     ?where=from >= DateTime(2026-01-01T00:00:00Z) AND type in [WorkItem,TimeSheetItem] 
Authorization: Bearer THE_ID_TOKEN

{
  "errors": [],
  "data": {
    "previous_page": null,
    "next_page": null,
    "items": {
      "WorkItem":      [ { "id": 1, "operation": "update", "operation_at": "2026-01-02T10:00:00Z", "operation_by": 42 } ],
      "TimeSheetItem": [ { "id": 9, "operation": "create", "operation_at": "2026-01-02T11:00:00Z", "operation_by": 42 } ]
    }
  }
}

10. Entities and fields

Each entity below lists its purpose, supported filters and its response fields. All datetimes are ISO 8601 (YYYY-MM-DDTHH:MM:SSZ); time-only fields (e.g. sub-type / timesheet start_time) are HH:MM strings. Nested collections are documented inside the same expand.

Organisation Units — organisation_units

The trust's organisational hierarchy (teams, departments, groupings). Filters: ids, updated_at.

Field	Type	Description
id	integer	Record ID
name	string	Display name
parent_id	integer / null	Parent unit (null for root)
resource_id	integer / null	ID of the linked resource
resource_type	string / null	e.g. Rota, Department
location_type	string / null	Location type of the backing location (e.g. Trust, Hospital)
created_at, updated_at	datetime	ISO 8601

Rotas — rotas

All rotas for the trust. Filters: ids, updated_at.

Field	Type	Description
id	integer	Record ID
name	string	Rota name
organisation_unit_id	integer / null	Owning organisation unit
archived_date	date / null	Null if active
specialty	string / null	Specialty name
created_at, updated_at	datetime	ISO 8601

Rota Status Periods — rota_status_periods

Each rota's status timeline as contiguous periods. A rota has many periods; the same status may recur. Ends are open — the earliest (archived) period has a null start_date, the latest (planning) period a null end_date. Filters: rota_id, updated_at.

Field	Type	Description
rota_id	integer	The rota this period belongs to
rota_name	string / null	Name of the rota
status	string	One of planning, draft, live, work_locked, locked, archived
start_date	date / null	First date; null = open-ended in the past
end_date	date / null	Last date; null = open-ended in the future
updated_at	datetime	ISO 8601

Status	Meaning
planning	Being set up for the future; not yet a working rota for staff.
draft	Being filled in by schedulers; not yet open for staff to self-roster.
live	Published and in use; staff can self-roster and structure is fixed.
work_locked	Shift assignments settled; leave/absence can still be recorded.
locked	Fully closed — neither shifts nor leave/absence can change.
archived	Historical; excluded from day-to-day scheduling.

Sub Types — sub_types

Shift/event sub-types (RotaEventSubType) — the template for a type of shift, leave or availability event. Filters: ids, updated_at, date.

Field	Type	Description
id	integer	Record ID
type	string / null	Parent event type (e.g. Shift, Leave)
short_name, name	string	Short code / display name
description	string / null	Free text
start_time, end_time	string	24h HH:MM
breaks	integer	Default break minutes
color	string	HTML hex colour
work_type	string	e.g. none, plain_time, enhanced
composite	string	Composite mode
esr_absence_type, esr_absence_reason	string / null	ESR absence type / reason
is_work, is_on_call, is_dcc, can_overlap, is_wli, is_induction, is_resident_on_call, is_study_leave, is_allowed_on_weekends, is_lieu_time, is_discretionary_leave, is_allowed_on_bank_holidays, is_absence, is_available	boolean	Behavioural flags (contracted work, on-call, DCC, overlap, WLI, induction, resident on-call, study leave, weekend/bank-holiday bookable, lieu time, discretionary leave, absence, availability)
created_at, updated_at	datetime	ISO 8601

Users — users

User accounts with a trust membership in the trust. Filters: ids, updated_at.

Field	Type	Description
id	integer	Record ID
first_name, last_name	string
suffix	string / null	e.g. Jr.
email	string
registration_number	string / null	GMC / NMC number etc.
registration_status, registration_body	string / null	Registration status / body name
workflow_state	string	e.g. active, invited
trust_memberships	array	See below

trust_memberships item: start_date, end_date (date/null); esr_absence_type_id (int/null); esr_person_number, local_employee_number, notes, default_locum_assignment_number, agency (string/null).

Rota Members — rota_members

Membership records linking users to rotas. Filters: ids, updated_at.

Field	Type	Description
id	integer	Record ID
rota_id, user_id	integer
job	string / null	Job/grade name
org_unit_id	integer / null	Org unit derived from department
start_date, end_date	date / null	Membership span
color	string	HTML hex colour on the rota
confirmed_by	string / null	Full name of confirming user
confirmed_at	datetime / null	When confirmed
location	string / null	Location name
location_organisation_unit_id	integer / null	Org unit of the member's location
team	string / null	Team name

Shift Requirements — shift_requirements

Rules describing which shifts need filling and by whom. Filters: ids, updated_at, date.

Field	Type	Description
id	integer	Record ID
sub_type_id	integer / null	Sub-type of the linked shift
sub_type_type	string	Always Shift
min_rota_job, max_rota_job	string / null	Min / max job-grade display name
rota_id	integer
days, locum_days	array	ISO weekdays the requirement / locum-fill applies (1=Mon...7=Sun)
should_enforce_max_job, allow_self_rostering, should_enforce_specialties	boolean	Rule flags
locations, specialties	array of string	Location / specialty names
specialty_ids, organisation_unit_ids	array of integer	Specialty IDs / org units (derived from the shift's locations)
mode	string	Requirement mode
start_date, end_date	date / null
created_at, updated_at	datetime	ISO 8601

Not Needed Shift Requirements — not_needed_shift_requirements

Marks a shift requirement as not needed on a specific date. Filters: ids, updated_at, date.

Field	Type	Description
id	integer	Record ID
rota_id	integer
shift_requirement_id	integer	The requirement being cancelled
date	date	The date it is not needed
created_by_id	integer	Who created it
explanation	string / null	Free-text reason
organisation_unit_ids	array of integer	Org units from the linked requirement's locations
created_at, updated_at	datetime	ISO 8601

Work Items — work_items

The core attendance/scheduling record: one person's assignment for one shift on one date. Filters: ids, updated_at, date.

Field	Type	Description
id, user_id	integer	Record / user
type, sub_type, sub_type_id	string/null, string/null, int/null	Event type, sub-type name and id
rota_id	integer / null
date	date
start_time, end_time	datetime	Actual start / end
planned_start_time, planned_end_time	datetime	Planned
contracted_start_time, contracted_end_time	datetime	Contracted
shift_requirement_id	integer / null
color, color_override	string, boolean	Hex colour; true if it differs from the shift default
work_type	string	Humanised (e.g. Plain time)
has_overtime, overtime_duration	boolean, int/null	Overtime flag / minutes
vacant, locum, absence, on_call, resident_on_call	boolean	Slot state flags
overrides_shift_req_rules	boolean	Force-assigned, ignoring requirement rules
additional_time, breaks	int/null, integer	Additional / break minutes
activity_type, activity_session, activity_category	string / null	Activity metadata
activity_location, activity_organisation_unit_id	string/null, int/null	Activity location and its org unit
locations, organisation_unit_ids	array of string, array of int	From the shift requirement
additional_locations, additional_organisation_unit_ids	array of string, array of int	Overrides on the work item itself
comments	array	See below
training_types	array of string	Training type names
created_at, updated_at	datetime	ISO 8601

comments item: body (string), user_id (int), user_name (string), created_at (datetime).

Locum Offer Shifts — locum_offer_shifts

Per-date line of a locum offer (a shift advertised externally). Filters: ids, updated_at, date, status.

Field	Type	Description
id, locum_group_id	integer	Record / parent LocumOffer
external_id, venue	string / null	External ref / venue name
urgent, filled	boolean	Urgent flag / booked
date	date	Shift date
start_time, end_time	datetime
breaks	integer	Minutes
sub_type_start_time, sub_type_end_time	datetime	Sub-type template times on this date
day_of_week	integer	ISO day (1=Mon...7=Sun)
grades_offer_sent_to	array of string	Grade names offered to
reason, cancellation_reason	string / null
sub_type	string / null	Shift/sub-type name
shift_requirement_id, rota_id	integer / null
location, organisation_unit_ids	array of string, array of int	Locations / org units
offered_user_id	integer / null	Staff the offer was made to
cost_centre	string / null
entered_by, entered_on	int/null, datetime/null	Creator / created at
locum_user_id	integer / null	Booked locum
total_value	decimal / null	Total value
locum_applied_timestamp	datetime / null
booked_by, booked_on	int/null, datetime/null
requested_by, requested_on	int/null, datetime/null
applications	array	See below
status	string / null	Workflow state

applications item: user_id (int), grade_id (int), workflow_state (string), created_at (datetime).

Time Sheet Items — time_sheet_items

Timesheets submitted by locums after a shift. Filters: ids, updated_at, date, status.

Field	Type	Description
id	integer	Record ID
locum_offer_id, locum_group_id	integer	LocumOfferShift / parent LocumOffer
start_time, end_time	string	24h HH:MM
breaks	integer	Minutes
total_value	decimal	Rate value
notes, venue	string / null
cost_centre, cost_centre_code	string / null
authorised_by, authorised_on	int/null, datetime/null
rate_group	string / null
is_enhanced	boolean / null	Enhanced rate applied
wtd, employer_ni, employer_pension, total	decimal / null	WTD supplement / employer NI / pension / total incl. on-costs
status	string / null	Workflow state

Exception Reports — exception_reports

Reports raised by junior doctors when actual hours differ from contracted hours. Filters: ids, updated_at, date.

Field	Type	Description
id, user_id	integer	Record / submitting doctor
supervisor_id	integer / null	Supervising doctor
work_item_id	integer / null	Related work item
exception_report_reason	string / null	Reason name
rota_id, sub_type_id	integer / null
date	date
safety_concern	boolean / null	Safety concern raised
payment_type	string / null	Agreed payment type
explanation	string / null	Free text from the doctor
planned_start_time, planned_end_time	datetime
start_time, end_time	datetime	Actual
missed_breaks, additional_time	integer	Minutes
work_schedule_changed	boolean
status	string / null	Workflow state
created_at, updated_at	datetime	ISO 8601

Job Plans — job_plans

Job plans for consultant/SAS doctors: contracted hours (PAs), leave allowances and sign-offs. Filters: ids, updated_at, date.

Field	Type	Description
id, user_id	integer
team, specialty, directorate, place_of_work	string / null	Org context
contract_type, sub_contract_type, contract_period	string / null	Contract
job_title, status	string / null
start_date, end_date	date / null
working_weeks	integer / null
ewtd_opt_in	boolean / null	EWTD opt-in
total_hours_pas, clinical_hours_pas, dcc_hours_pas, spa_hours_pas	decimal / null	Programmed Activities: total / clinical / DCC / SPA
annual_leave, study_leave, al_allowance, sl_allowance	decimal / null	Leave allowances
on_call_rota, on_call_supplement	string/null, decimal/null
activity_plan	string / null	Activity plan name
archived	boolean
comments	string / null	Free text
sign_offs	array	See below
created_at, updated_at	datetime	ISO 8601

sign_offs item: user_id (int), title (string/null), signed_off (boolean/null), signed_off_at (date/null).

Job Plan Activities — job_plan_activities

Individual activity lines within job plans, including schedule patterns. Filters: ids, updated_at, date.

Field	Type	Description
id, job_plan_id, user_id	integer
fixed_flexible_type	string	Fixed or Flexible
activity_category_type, activity_category, activity_type	string, string, string/null	Category type / name / activity type
description	string / null
schedule	array	See below
resource_name	string / null	Rota name if linked
shift_event	string / null	Shift event type (humanised)
location, organisation_unit_id	string/null, int/null	Activity location and org unit
specialty, rate_group	string / null
rota_id, sub_type_id, sub_type_type	int/null, int/null, string/null
start_date, end_date	date / null
start_time, end_time	string / null	Time string
created_at, updated_at	datetime	ISO 8601

schedule item: week (int), day (int, 1=Mon...7=Sun), days_worked (float), weeks_worked (float), count (float), adjusted_duration (float).

Updates — updates

A change log for incremental sync: the latest create/update/destroy per changed record across all reporting entity types. Poll it with a from timestamp to fetch only what changed since your last sync.

Filter (inside where)	Required	Description
from	Yes	e.g. from >= DateTime(2026-01-01T00:00:00Z) — records with operation_at at or after from
type	No	e.g. type in [WorkItem,TimeSheetItem]. Valid values are every entity type except Update and RotaStatusPeriod (the latter is a derived timeline with no per-record audit log). Omit for all types.

data.items is an object keyed by entity type, each value an array of that type's normal fields plus:

Field	Type	Description
operation	string	create, update or destroy
operation_at	datetime	When the change occurred
operation_by	integer / null	User who made the change

Deduplication: when several audit rows exist for the same entity in the window, only the most recent change per entity is returned. Remapping: LocumOffer / LocumOfferApplication audit rows are remapped to their LocumOfferShift so callers always get shift-level granularity.

11. Error responses

Status	When
400 Bad Request	Missing/invalid X-TRUST-ID, unknown where field, invalid type, malformed cursor, validation failure
401 Unauthorized	Missing/expired Cognito token (returned by the API Gateway)
500 Internal Server Error	Unexpected server error

{
  "errors": [
    { "code": "INVALID_FIELD", "title": "HealthRota Reporting API", "detail": "Human-readable description" }
  ]
}

12. Power BI custom connector

The reporting_connector/ directory is a Power BI custom connector (Power Query M) that consumes this API. It authenticates to Cognito with username/password, calls meta to discover entities, and maps each to a type in the generated ModelTypes.pqm. Download here: Power BI Custom Connector.