Requirements
These were collected from a meeting we held with Larsson, Abhi, Mike, Brian, and Thaneer, and from a follow-up interview with Dan.
...
CREATE TABLE `SubStudyAccounts` {
`studyId` VARCHAR(60) NOT NULL,
`id` VARCHAR(60) NOT NULL,
`subStudyId` VARCHAR(15) NOT NULL,
`accountId` `healthCode` VARCHAR(255) NULL,
`externalId` VARCHAR(255) NULL,
PRIMARY KEY (`studyId`,`id`) // ID must be unique in a study so we can unambiguously determine the sub-study membership
UNIQUE INDEX `StudyId-SubStudyId-Index` (`studyId` ASC, `id` ASC), / /to retrieve all external IDs in a sub-study
INDEX `AccountId`HealthCode-Index` (`accountId` `healthCode` ASC) // to retrieve external IDs for an account
UNIQUE INDEX 'StudyId-SubStudyId-ExternalId-Index (`studyId` ASC,`subStudyId` ASC, `id` ASC) // for security, we will include the substudy in the query, though external ID is unique to study
CHECK `AccountId`HealthCode-Or-ExternalId-Required` (`externalId` IS NOT NULL OR `accountId` `healthCode` IS NOT NULL)
}
// This replaces the external ID service
ExternalIdsServiceV2 {
listExternalIds(studyId, subStudyId, offsetBy, pageSize, includeDeleted)
createExternalId(externalIdObj)
getExternalId(studyId, subStudyId, externalId)
updateExternalId(externalIdObj)
deleteExternalId(studyId, subStudyId, externalId)
deleteExternalIdPermanently(studyId, subStudyId, externalId)
// These would update an add an associate record between Account and SubStudy with an account ID
assignExternalId(studyId, subStudyId, externalId, accountId)
unassignExternalId(studyId, subStudyId, externalId)
}
...
GET /v3/substudies/:subStudyId/externalids [list]
POST /v3/substudies/:subStudyId/externalids [create] <-- could take a list for batch creates
GET /v3/substudies/:subStudyId/externalids/:id [read]
POST /v3/substudies/:subStudyId/externalids/:id [update]
DELETE /v3/substudies/:subStudyId/externalids/:id [delete]
Write to both external ID systems, and read first from the new external ID database. In this period, user can only have one external identifier:
- Add subStudyId to the existing ExternalIdService APIs; to use them, a sub-study will need to be provided (this may not be functional at first)
- Write to both the new and old tables when assigning or changing external ID.
- Backfill sub-study from the new to the old table
- Add method to ExternalIdService to find external ID by health code. Use this in preference to the value stored in the Accounts table, so you can first look at the join with new table.
AccountDao - mostly calls through to the ExternalIdService
...