Business rules

Entity relationships:

1. A programme membership (PM) may be linked to one or more placements [legacy: zero or more].

2. A placement will be linked to exactly one PM [legacy: could be unlinked to any PM].

3. Given 1 and 2 above, for legacy records, a PM may have no related placements, and a placement may exist independently of any PM. This would allow legacy (unlinkable) data to be retained without particular exception-handling, and existing data bulk upload / editing functionality to be unaffected. In future this could be tightened-up if appropriate.

4. There may be circumstances where a placement is currently linked to more than one PM, but this should be corrected.

5. Some historic placements / PMs may need to remain unlinked (dirty data, ambiguity).

6. Over the duration of a PM, there is expected to be one or more placements active at all times.

7. A placement should not extend before the beginning of its linked PM, but some dirty data may be expected (e.g. induction periods / pre-start before the PM-proper begins).

8. A placement may extend after the end of its linked PM, particularly in the case of e.g. LTFT when the placement has been extended but the PM record has not been adjusted to match.

9. Which placements are eligible to be linked to a PM are not necessarily those for the same trainee where the placement post is the same as the programme post (see Note 1 below).

10. Likewise, specialty is not a useful way of filtering possible linkages (see Note 2 below).

11. Start and end dates should be used to limit options for linkage [may need some fuzziness on start-date, and more on end-date].

    1. If placement start-date is within the PM, then it should be linked to that PM.

    2. If placement start date > 13 days before start of PM, and placement end-date is within PM, then link to this PM.

12. Can also consider how many PMs they had at the point where the placement was created (this may require some fuzziness on dates, again). If there was only one, then link to that.

13. No additional details need to be recorded against a linkage. However, it may be useful to record metadata such as

    1. when the linkage was made and

    2. who made it, or whether it was automatically made and

    3. if auto-linked, the results of the conditions that were evaluated, and the assessed probability.

14. When evaluating auto-linkages, it will be useful to segment the data as follows, and potentially to implement differentially across the 4 quadrants:

    1. Placements / PMs with a start date prior to the roll-out of TIS, i.e. 2018 (check exact date) are LEGACY, the remainder are RECENT.

    2. Placements / PMs related to active trainees (those with ongoing or upcoming PMs / placements) are ACTIVE and those related to non-active trainees are INACTIVE.

    3. Priority for RECENT-ACTIVE placements will be highest, and that of LEGACY-INACTIVE the lowest.

Data ownership:

1. The linking of placements to programme memberships will be managed by LOs, not trainees.

2. In TIS Admin, the users who manage placement and PM data will be able to add and remove the linkages between the records they manage. There is no obvious reason to keep a record of linkages after they are removed.

Data model: TIS

1. A new TCS table PlacementProgrammeMembership containing FK fields programmeMembershipUuid, placementId, and any metadata fields (timestamp when added, by whom, matching conditions, probability).

2. Deletions of placements or PMs should cascade delete related records in this table.

3. The PK for this table could be a composite (programmeMembershipUuid + placementId) or a synthetic id field, but (programmeMembershipUuid + placementId) should in any event be unique.

Data model: TSS

1. TraineeProfile: add linkedProgrammeMembership[]: {uuid, when, how, metrics} to placement subdocument. The actual details of the PM are already held in the programmeMembership[] subdocuments.

2. tis-trainee-sync: add placementProgrammeMembership collection to be synced from TIS, and triggering re-enrichment of related placements.

Notes:

1. Post linkage (not reliable):
   Sometimes placements ‘borrow’ post from another programme because otherwise post management becomes laborious, e.g. Emergency Medicine Higher → EM run-through; GP using Obstetrics etc. in hospital placement.

Even if this were a solid way of filtering programmes, there is some ambiguity in this linkage, as can be seen here: https://build.tis.nhs.uk/metabase/question/notebook#eyJkYXRhc2V0X3F1ZXJ5Ijp7InR5cGUiOiJuYXRpdmUiLCJuYXRpdmUiOnsicXVlcnkiOiJzZWxlY3QgcGwuaWQsIGNvdW50KCopIFxyXG5GUk9NIHRjcy5Qcm9ncmFtbWVNZW1iZXJzaGlwIHBtIFxyXG5KT0lOIHRjcy5Qcm9ncmFtbWUgcHJnIE9OIHBtLnByb2dyYW1tZUlkID0gcHJnLmlkXHJcbkpPSU4gdGNzLlByb2dyYW1tZVBvc3QgcHAgT04gcHJnLmlkID0gcHAucHJvZ3JhbW1lSWRcclxuSk9JTiB0Y3MuUGxhY2VtZW50IHBsIE9OIHBsLnBvc3RJZCA9IHBwLnBvc3RJZCBBTkQgcG0ucGVyc29uSWQgPSBwbC50cmFpbmVlSWRcclxuV0hFUkUgcG0ucHJvZ3JhbW1lU3RhcnREYXRlID4gJzIwMjQtMDEtMDEnXHJcbkdST1VQIEJZIHBsLmlkXHJcbkhBVklORyBjb3VudCgqKSA-IDE7IiwidGVtcGxhdGUtdGFncyI6e319LCJkYXRhYmFzZSI6MzZ9LCJkaXNwbGF5IjoidGFibGUiLCJ2aXN1YWxpemF0aW9uX3NldHRpbmdzIjp7fX0=
select pl.id, count()
FROM tcs.ProgrammeMembership pm
JOIN tcs.Programme prg ON pm.programmeId = prg.id
JOIN tcs.ProgrammePost pp ON prg.id = pp.programmeId
JOIN tcs.Placement pl ON pl.postId = pp.postId AND pm.personId = pl.traineeId
WHERE pm.programmeStartDate > '2024-01-01'
GROUP BY pl.id
HAVING count() > 1;

2. Specialty linkage (not reliable):
   This doesn’t work because e.g. GPs have to 12 months of 'hospital' placements which will not have the specialty of General Practice.

Even if this were a solid way of filtering programmes, there is still some ambiguity in this linkage, as can be seen here:
https://build.tis.nhs.uk/metabase/question/notebook#eyJkYXRhc2V0X3F1ZXJ5Ijp7InR5cGUiOiJuYXRpdmUiLCJuYXRpdmUiOnsicXVlcnkiOiJzZWxlY3QgaWQsIGNvdW50KCopXHJcbkZST00gKFxyXG5TRUxFQ1QgcGwuaWQsIHBtLnV1aWQgRlJPTVxyXG50Y3MuUHJvZ3JhbW1lTWVtYmVyc2hpcCBwbSBcclxuSk9JTiB0Y3MuQ3VycmljdWx1bU1lbWJlcnNoaXAgY20gT04gcG0udXVpZCA9IGNtLnByb2dyYW1tZU1lbWJlcnNoaXBVdWlkXHJcbkpPSU4gdGNzLkN1cnJpY3VsdW0gYyBPTiBjbS5jdXJyaWN1bHVtSWQgPSBjLmlkXHJcbkpPSU4gdGNzLlByb2dyYW1tZSBwcmcgT04gcG0ucHJvZ3JhbW1lSWQgPSBwcmcuaWRcclxuSk9JTiB0Y3MuUHJvZ3JhbW1lUG9zdCBwcCBPTiBwcmcuaWQgPSBwcC5wcm9ncmFtbWVJZFxyXG5KT0lOIHRjcy5Qb3N0U3BlY2lhbHR5IHBzIE9OIHBwLnBvc3RJZCA9IHBzLnBvc3RJZCBBTkQgYy5zcGVjaWFsdHlJZCA9IHBzLnNwZWNpYWx0eUlkXHJcbkpPSU4gdGNzLlBsYWNlbWVudCBwbCBPTiBwbC5wb3N0SWQgPSBwcC5wb3N0SWQgQU5EIHBtLnBlcnNvbklkID0gcGwudHJhaW5lZUlkXHJcbldIRVJFIHBtLnByb2dyYW1tZVN0YXJ0RGF0ZSA-ICcyMDI0LTAxLTAxJ1xyXG5HUk9VUCBCWSBwbC5pZCwgcG0udXVpZFxyXG4pIHhcclxuR1JPVVAgQlkgaWRcclxuSEFWSU5HIGNvdW50KCopID4gMSIsInRlbXBsYXRlLXRhZ3MiOnt9fSwiZGF0YWJhc2UiOjM2fSwiZGlzcGxheSI6InRhYmxlIiwidmlzdWFsaXphdGlvbl9zZXR0aW5ncyI6e319
select id, count()
FROM (
SELECT pl.id, pm.uuid FROM
tcs.ProgrammeMembership pm
JOIN tcs.CurriculumMembership cm ON pm.uuid = cm.programmeMembershipUuid
JOIN tcs.Curriculum c ON cm.curriculumId = c.id
JOIN tcs.Programme prg ON pm.programmeId = prg.id
JOIN tcs.ProgrammePost pp ON prg.id = pp.programmeId
JOIN tcs.PostSpecialty ps ON pp.postId = ps.postId AND c.specialtyId = ps.specialtyId
JOIN tcs.Placement pl ON pl.postId = pp.postId AND pm.personId = pl.traineeId
WHERE pm.programmeStartDate > '2024-01-01'
GROUP BY pl.id, pm.uuid
) x
GROUP BY id
HAVING count() > 1