Read-only reporting access

Provider Reporting Data Model

The tables your reporting login can read, how they link together, and what each field means. Use it to plan the reports you build in your own tools.

How the data connects

Every link uses a UUID: a fixed identifier that never changes, even if a name or email does. Join tables on these, never on names.

Filtered to your organisations Course catalogue, the same for every provider

erDiagram
    po_organisations ||--o{ po_students : "enrols"
    po_programme_aims |o--o{ po_students : "assigned to"
    po_students ||--o{ po_student_course_progress : "has"
    po_students ||--o{ po_student_topic_progress : "has"
    po_students ||--o{ po_initial_assessment_attempts : "sits"
    po_students ||--o{ po_mock_exam_attempts : "sits"
    po_courses ||--o{ po_topic_areas : "contains"
    po_topic_areas ||--o{ po_topics : "contains"
    po_courses ||--o{ po_student_course_progress : "tracked on"
    po_courses ||--o{ po_mock_exam_attempts : "for"
    po_topics ||--o{ po_student_topic_progress : "tracked on"

    po_organisations {
        uuid uuid PK
        text name
    }
    po_students {
        uuid user_uuid
        uuid organisation_uuid FK
        uuid programme_aim_uuid FK
        text learning_status
    }
    po_programme_aims {
        uuid uuid PK
        text name
    }    po_student_course_progress {
        uuid user_uuid FK
        uuid course_uuid FK
        decimal course_progress
    }
    po_student_topic_progress {
        uuid user_uuid FK
        uuid topic_uuid FK
        text topic_status
    }
    po_initial_assessment_attempts {
        uuid attempt_uuid PK
        uuid user_uuid FK
        decimal calculated_level
    }
    po_mock_exam_attempts {
        uuid attempt_uuid PK
        uuid user_uuid FK
        uuid course_uuid FK
        decimal score
    }
    po_courses {
        uuid uuid PK
        text title
    }
    po_topic_areas {
        uuid topic_area_uuid PK
        uuid course_uuid FK
    }
    po_topics {
        uuid topic_uuid PK
        uuid topic_area_uuid FK
    }

The diagram shows key fields only. Every field is listed below. Read a line as "one of this, many of that": one organisation enrols many students, and one course contains many topic areas.

Before you build a report

Count students with DISTINCT

A student in two of your organisations appears once per organisation in po_students. Use COUNT(DISTINCT user_uuid) for headcounts.

The catalogue is everything

Courses, topic areas and topics list every active course we offer, not just the ones your students take. Start from a student table to see only your learners' courses.

Topic progress has no course column

To group topic progress by course, go topic → topic area → course.

Removed records are left out

Deleted students and organisations, and inactive courses, are left out of every table automatically.

It is read-only and live

You can query but not change anything. Figures reflect the platform shortly after they change.

Your organisations and students

Filtered to you

`po_organisations`

The organisations your login can see.

One row per organisation.

Field	Type	Meaning
uuidKEY	uuid	Organisation identifier. Matches `po_students.organisation_uuid`.
name	text	Organisation name.
website	text	Website, if one was given.
created_at	datetime	When the organisation was set up.
updated_at	datetime	When its details last changed.

`po_students`

Students in your organisations.

One row per student per organisation.

Field	Type	Meaning
user_uuidKEY	uuid	Student identifier. Links to every progress and attempt table.
organisation_uuid	uuid	The organisation this row belongs to.
programme_aim_uuid	uuid, optional	The student's programme aim. Empty if none is set.
readable_identifier	text	The student's email address, or their username if they log in without email.
email_enabled	1 / 0	1 if the student logs in with an email address.
learning_status	text	`active`, `on_a_break` or `archived`.
created_at	datetime	When the student account was created.
last_login	datetime, optional	Most recent login. Empty if they have never logged in.

`po_student_course_progress`

How each student is doing on each course they are enrolled on.

One row per student per course.

Field	Type	Meaning
user_uuid	uuid	The student.
course_uuid	uuid	The course. Matches `po_courses.uuid`.
course_plan	text	`Premium` or `Free`.
course_progress	percentage	How much of the whole course is complete, 0 to 100.
learning_plan_progress	percentage	How much of the student's recommended learning plan is complete, 0 to 100.
learning_plan_items	number	Recommended topics still to do.
initial_level	number	The level the student started the course at.
current_level	number	The level the student is working at now.
time_spent_on_course	seconds	Total time recorded answering questions on this course.
planned_end_date	date, optional	The planned end date for this enrolment, if one was set.
past_planned_end_date	1 / 0	1 if the planned end date has passed.
marked_ready_for_exam	1 / 0	1 if the student has been marked ready for their exam.

`po_student_topic_progress`

Each student's progress through individual topics.

One row per student per topic they have started.

Field	Type	Meaning
user_uuid	uuid	The student.
topic_uuid	uuid	The topic. Matches `po_topics.topic_uuid`.
topic_status	text	Where the student is with this topic.
revision_completion_date	datetime, optional	When the student finished the revision material.
test_completion_date	datetime, optional	When the student completed the topic test.
test_score	number, optional	Score on that topic test.
exam_completion_date	datetime, optional	When the student completed the topic exam questions.
exam_score	number, optional	Score on those exam questions.

`po_initial_assessment_attempts`

Initial assessments students have started, and the level each one placed them at.

One row per attempt.

Field	Type	Meaning
attempt_uuidKEY	uuid	Identifier for this attempt.
user_uuid	uuid	The student.
initial_assessment_uuid	uuid	Which initial assessment was taken. Use it to group attempts of the same assessment.
title	text	Name of the initial assessment.
started_at	datetime	When the attempt began.
completed_at	datetime, optional	When it was finished. Empty if still in progress.
total_elapsed_time	seconds, optional	Time spent answering questions.
calculated_level	number, optional	The level the assessment placed the student at, to 2 decimal places.

`po_mock_exam_attempts`

Mock exams students have sat.

One row per attempt.

Field	Type	Meaning
attempt_uuidKEY	uuid	Identifier for this attempt.
user_uuid	uuid	The student.
course_uuid	uuid	The course the mock exam belongs to.
exam_uuid	uuid	Which mock exam was taken. Use it to group attempts of the same exam.
exam_title	text	Name of the mock exam.
score	number	Score achieved.
hurdle	number, optional	The score needed to pass this mock exam, where one is set.
completed_at	datetime, optional	When it was finished. Empty if still in progress.

Course catalogue

Same for every provider

Lookup tables for turning UUIDs into readable names, and for grouping progress by course and topic area.

`po_courses`

One row per active course.

Field	Type	Meaning
uuidKEY	uuid	Course identifier.
title	text	Course name.

`po_topic_areas`

One row per topic area. A topic area is a section of a course.

Field	Type	Meaning
topic_area_uuidKEY	uuid	Topic area identifier.
topic_area_title	text	Topic area name.
course_uuid	uuid	The course it belongs to.

`po_topics`

One row per topic.

Field	Type	Meaning
topic_uuidKEY	uuid	Topic identifier.
topic_title	text	Topic name.
topic_area_uuid	uuid	The topic area it belongs to.

`po_programme_aims`

One row per programme aim.

Field	Type	Meaning
uuidKEY	uuid	Programme aim identifier. Matches `po_students.programme_aim_uuid`.
name	text	Programme aim name.

Example queries

Average course progress per organisation and course

SQL

SELECT o.name AS organisation,
       c.title AS course,
       COUNT(DISTINCT p.user_uuid) AS students,
       ROUND(AVG(p.course_progress), 1) AS avg_progress
FROM po_student_course_progress p
JOIN po_students s ON s.user_uuid = p.user_uuid
JOIN po_organisations o ON o.uuid = s.organisation_uuid
JOIN po_courses c ON c.uuid = p.course_uuid
GROUP BY o.name, c.title;

Topic completion grouped by course and topic area

SQL

SELECT c.title AS course,
       ta.topic_area_title,
       COUNT(*) AS topics_started,
       SUM(tp.test_completion_date IS NOT NULL) AS tests_done
FROM po_student_topic_progress tp
JOIN po_topics t ON t.topic_uuid = tp.topic_uuid
JOIN po_topic_areas ta ON ta.topic_area_uuid = t.topic_area_uuid
JOIN po_courses c ON c.uuid = ta.course_uuid
GROUP BY c.title, ta.topic_area_title;