Requirements

These were collected from a meeting we held with Larsson, Abhi, Mike, Brian, and Thaneer, and from a follow-up interview with Dan.

1. We have partners who contribute participants to our studies while being part of another study, that is, participants who download and use our app, and thus are participating in the larger population of participants that are recruited from multiple locations and also a specific sub-study (we propose to call these partners "sub-study partners");
2. Sub-study partners may include their participants in (at least) a couple of ways:
   1. They may assign them an external ID, that in essence should identify the participant's sub-study membership as well;
   2. They may ask existing participants to join the study, such that signing a consent in Bridge should enroll that participant in the sub-study;
   3. Sub-study partners will also create user accounts to manage their own users and external identifiers (only)
3. Sub-study participants do not need to have an external ID; management accounts and users enrolled through a consent may not have an external ID. We may assign an external ID as an optional feature of enrolling through signing a consent (but it wouldn't be necessary);
4. Sub-study participants may receive schedules (and thus, tasks or surveys) that are unique to their sub-study, but we believe these changes will be additive to the main study. However, Sage Bionetworks will be responsible for incorporating such changes so that they don't break the main study;
5. Sub-study partners may access their list of participants and/or external identifiers, and can probably do anything an existing researcher can do with those entities... but only those that are in the sub-study. They cannot see participants or external IDs in other sub-studies (or no sub-study);
6. The client app should know the sub-study memberships of a participant in case this is important to deliver the correct UI/behavior;
7. Sub-study membership needs to be exported with all data exported to Synapse. The team that processes the data will use this membership information to create specific repositories for those sub-study partners;
8. All users can be in multiple sub-studies (both participants, and administrative users). It's not clear what the requirements are for this (e.g. should a researcher see all participants in all the sub-studies they are a member of at once, or only one at a time with a mechanism to select their current sub-study?), but in a client app, the behavior would have to be additive, so again, Sage would want to vet and implement this to ensure no sub-study breaks and the app is useable.
   1. it follows that users may have multiple external IDs;
   2. in studies where users authenticate with external ID + phone/email, all the external IDs should be usable to authenticate;
   3. membership should not leak, i.e. if I am a researcher in sub-study A, and a participant is in sub-studies A and B, B should not be included in the API, BSM, etc.;
9. As before, users can belong to no sub-studies:
   1. Researchers and developers would see everything in the study (no sub-studies means no filtering of study participants, etc.);
   2. Participants would not match criteria based on sub-studies (schedules/consents may or may not be filtered out for those participants);
   3. Research data is exported to Synapse with out sub-study tags; based on our agreements with sub-study partners, this data might or might not be included in their data set.
10. For developers, this feature should not require multiple builds or multiple server study configurations.

Database Schema

Other possibilities listed at the end of this document

This seems best given Dwayne's comments below... manage external IDs separately, and in a transaction, mark when they are used and add them as an attribute to the AccountSubStudies table record, which is a true associative table.

Tasks

Clean up accounts to remove GenericAccount and HibernateAccount, and the copying between the two classes (subsequent changes heavily involve the AccountsDao, so it would help to simplify first).

Create Sub-Studies

SQL:

CREATE TABLE `SubStudies` (
    `studyId` VARCHAR(60) NOT NULL,
    `id` VARCHAR(15) NOT NULL,
    `label` VARCHAR(255) NULL,
    `createdOn` BIGINT NOT NULL,
    `modifiedOn` BIGINT NOT NULL,
    `deleted` BOOLEAN NOT NULL DEFAULT false,
    PRIMARY KEY (`studyIid`, `id`)
)

SubStudiesService {
    listSubStudies(studyId, includeDeleted)
    createSubStudy(subStudy)
    getStubStudy(studyId, id)
    updateSubStudy(subStudy)
    deleteStubStudy(studyId, id)
    deleteStubStudyPermanently(studyId, id)
    // These would add an associate record between Account and SubStudy without assigning an external ID
    addUserToSubStudy(studyId, id, participant)
    removeUserFromSubStudy(studyId, id, userId)
}

API (always in the study of the caller, unless we need worker APIs eventually):

    GET /v3/substudies?includeDeleted=boolean [list]
    POST /v3/substudies [create]
    GET /v3/substudies/:id [read]
    POST /v3/substudies/:id [update]
    DELETE /v3/substudies/:id?physical=boolen [delete]

Create AccountSubStudies

CREATE TABLE `AccountSubStudies` {
    `studyId` VARCHAR(25) NOT NULL,
    `subStudyId` VARCHAR(15) NOT NULL,
    `accountId` VARCHAR(255) NOT NULL,
    `externalId` VARCHAR(255) NULL, // not required for the association
    PRIMARY KEY (`studyId`, `subStudyId`,`accountId`)
    INDEX `AccountId-Index` (`accountId` ASC)
    CONSTRAINT `AccountsFK` FOREIGN KEY (`accountId`) REFERENCES `Accounts` (`id`) ON DELETE CASCADE
    CONSTRAINT `SubStudiesFK` FOREIGN KEY (`studyId`, `subStudyId`) REFERENCES `SubStudies` (`studyId`, `id`) ON DELETE CASCADE
}

Create ExternalIds

We could leave this in DDB but there might be more consistency errors. Unless we can execute DDB code as part of a SQL transaction and only commit the transaction if the DDB updates succeed.

CREATE_TABLE `ExternalIds` {
    `studyId` VARCHAR(25) NOT NULL,
    `subStudyId` VARCHAR(15) NOT NULL,
    `identifier` VARCHAR(255) NOT NULL,
    `accountId` VARCHAR(255) NULL // do not delete the external ID record if the user is deleted
    PRIMARY KEY (`studyId`,`identifier`) // externalId must be unique across all sub-studies
    CONSTRAINT `SubStudiesFK` FOREIGN KEY (`studyId`, `subStudyId`) REFERENCES `SubStudies` (`studyId`, `id`) ON DELETE CASCADE
}

// 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)
}

API (always in the study of the caller, unless we need worker APIs)

    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]

Add sub-study to existing exernal IDs API. Make it possible to associate external ID record with sub-study

Write to both external ID tables, read from new table before old one. However at this time, you can only belong in one sub-study/only have one external ID.

Backfill the older external ID table with sub-study IDs

Switch to looking up users via external ID by quering for the record (not looking in Accounts table)

Join tables when retrieving user to get external IDs

- at this point the externalId column in the Accounts table should not be in use

Add substudies to AccountSummary, StudyParticipant, add substudies to UserSession

Add sub-study filtering to the getAccountSummaries() and Iterator calls. A sub-study must be selected if the user has sub-studies, and it must belong in their set of sub-studies, or the request is an error. Otherwise, the records are filtered only to those accounts that have the sub-study ID.

Remove older external IDs API (maintaining it would be very difficult, if it has to be maintained, switch it over to be a special case of calling the new API)

Update sub-populations so that signing the consent of a sub-population will assign a user to one ore more sub-studies, without an external ID (additional behaviors can be implemented as needed).

Uploads need to be tagged with the set of sub-study ids assigned to a user

Add the ability to filter by sub-studies using the Criteria object. Like tags, you should be able to add a set of sub-studies, at least one of which should match, or a set where none may match. The main use for this would be to schedule different sub-studies differently, in the context of an overarching multi-study design.

Other Database Options

The external IDs table is an associative table between accounts and sub-studies, but they are also entities and can exist even if an account is not associated to the ExternalId record. (The sub-study relationship is always required). This is the simplest but requires some additional indexes.

External Ids are managed as entities separately from the association (FK to sub-study). Then accounts are associated to external IDs, but only one per study (enforced in code). Queries for accounts in a sub-study will need to join two tables; we'd have to create a dummy external ID to associate people to a stub-study who otherwise weren't assigned an external ID as part of the study design.

We could create a separate associative table for sub-study membership. Simpler to query, doesn't require an external ID, does require constraints in code (user can't be associated to an external ID without also being associated to a sub-study, so we'd add one when we add the other, remove). We decided instead to join these tables.