Appropriate bodies 'portal'

[peteryates]

Introduction

The 'AB Portal' is an application that's part of DQT (database of qualified teachers), which is being decommissioned in 2025. We need a way for Appropriate Bodies (ABs) to submit ECT data to DfE so we have an up to date record of their induction.

Data flow

flowchart TD
    AB[Appropriate Bodies] --ECT data (via UI, CSV upload, API)--> ECF2
    AB -.ECT data.-> TP[Third party services\ne.g., ECT Manager, Mosaic]

    SA[School admins] -.ECT data.-> TP[Third parties]
    ECF2 --Induction data--> DFE[DFE consumers]
    TRS --Induction statuses--> DFE

    TRS --Eligibility statuses--> ECF2
    TRS --Eligibility statuses--> TP

    ECF2 --Status updates-->TRS

    TP --ECT data--> ECF2

    style ECF2 stroke:#333,stroke-width:4px

Data

Here's an example of a CSV file that appropriate bodies/third parties supply to DFE via:

• the bulk upload feature of the existing AB portal
• uploads to a SFTP folder which are batch processed overnight

Establishment ID,TRN,Surname,First Name,Date of Birth,Transition Arrangements Eligible,Induction Programme Type,Induction Outcome,Induction Period Start Date,Induction Period End Date,Induction Period Number Of Terms,Induction Period Extended Number Of Terms
212/2107,1234567,Cage,Nicholas,01/01/1999,,Full Induction Programme,In Progress,01/09/2023,,,
212/2107,7654321,Hayek,Salma,02/02/1988,,Full Induction Programme,In Progress,01/09/2023,,,

It's easier to read as a transposed table:

Column name	Row 1	Row 2
Establishment ID	212/2107	212/2107
TRN	1234567	7654321
Surname	Cage	Hayek
First Name	Nicholas	Salma
Date of Birth	01/01/1999	02/02/1988
Transition Arrangements Eligible
Induction Programme Type	Full Induction Programme	Full Induction Programme
Induction Outcome	In Progress	In Progress
Induction Period Start Date	01/09/2023	01/09/2023
Induction Period End Date
Induction Period Number Of Terms
Induction Period Extended Number Of Terms

Observations on the data

• Establishment ID is used in place of URN, in the GIAS dataset it's a composite key made from the LA (Code) (Local Authority code) and EstablishmentNumber.
• An induction period is provided in two parts, one with an Induction Period Start Date and one with both a Induction Period Start Date and an Induction Period End Date. We need to be careful if a closing submission is made where the other fields (i.e. Induction Programme Type) differs from the opening one
• We don't have enough context to calculate/validate the Induction Period Number Of Terms because there are many variables that could affect it (some teachers are part time, induction delayed by illness, etc)
• Dates are notoriously tricky and we really struggled with this in Register from what I remember, as they tend to get reformatted automatically by Excel or equivalents and then fail on validation
• Identifiers that are long can also be painful, as long numbers are rounded in some Excel or equivalents too
• I know it's just an example, but is establishment ID required?
• There are separate fields for Surname and First Name but elsewhere in CPD we just hold the full name

Mapping the data to our schema

The initial schema (#188) contains entities to which the data provided in the CSV belongs:

• the appropriate body:
  • Establishment ID
• the ECT:
  • TRN
  • Surname
  • First Name
  • Date of Birth
  • Induction Outcome
• an induction period:
  • Induction Programme Type
  • Induction Period Start Date
  • Induction Period End Date
  • Induction Period Number Of Terms

Additionally there are two fields that we need to think about:

• Transition Arrangements Eligible (@emily-prudence-dfe doesn't think it's necessary!)
• Induction Period Extended Number Of Terms (how this works is confusing, there must be a better way of recording this)

Tasks

• [x] #204
• [x] Create the staging area for new induction data, this will hold data while we check it against TRS and allow us to report problems to ABs before we persist anything
• [x] Create a user interface that allows AB users to view/create/update induction data
• [x] Ensure the UI is protected behind DfE Sign-in (see #43)
• [ ] Add bulk uploading (via CSV) to the UI
• [ ] Create an API that allows ECT data to be submitted by appropriate bodies/third parties
• [ ] Create an API client for TRS that lets us check eligibility statuses
• [ ] Update the service rules appropriately

[claire-hughez]

Vague thoughts!

which is being decommissioned in 2026. Isn't the goal 2025?

The diagram states 'induction tutors', but I feel like 'schools' is probably more accurate. I'm not sure the induction tutor is the one submitting admin data like this. Also, the schools would interact directly with ECF2 too.

Is there a key for what the dotted versus opaque line means?

I would add to the data observations:

• dates are notoriously tricky and we really struggled with this in Register from what I remember, as they tend to get reformatted automatically by Excel or equivalents and then fail on validation
• identifiers that are long can also be painful, as long numbers are rounded in some Excel or equivalents too
• I know it's just an example, but is establishment ID required?

Why does TRN sit under the appropriate body in the schema? Not sure that make sense to me?

I would agree with Emily P that transition stuff is unnecessary.

[claire-hughez]

Example of Register trainee teachers bulk upload CSV. This is fake data.

[peteryates]

which is being decommissioned in 2026. Isn't the goal 2025?

The 2026 date was from this document but I think it's shifted since, I've updated the description above.

Is there a key for what the dotted versus opaque line means?

Dotted was meant to indicate this happens outside of DfE and we don't have any influence over it.

Why does TRN sit under the appropriate body in the schema? Not sure that make sense to me?

Oops when I transposed the table the first row became headers, it's meant to be Establishment ID. Fixed.

but is establishment ID required?

This confused me too, it's the AB's identifier. So long as we drop SFTP uploads we don't need it as all the other avenues for the data to come in will require them to authenticate first.

[claire-hughez]

This confused me too, it's the AB's identifier. So long as we drop SFTP uploads we don't need it as all the other avenues for the data to come in will require them to authenticate first. Agreed this is preferable, and also how we handled it when we took over from the ITT portal in Register trainee teachers.

[peteryates]

Actually thinking about it we still might need it if we let third parties push data in via the API. I'd rather use URN though.

[claire-hughez]

Actually thinking about it we still might need it if we let third parties push data in via the API. I'd rather use URN though.

I'm surprised appropriate bodies have URNs? URNs are for schools, right?

[peteryates]

Good point. Some will but you're right - probably not all. Sigh 😓