Create SubStudies table (SQL tables, server APIs, SDK and integration tests)

• studyId [PK=studyId/ID]
• ID (either an int or a string)
• name/label
• externalIdAssigned - if true, must successfully find and assign an external ID if one is provided on creation or update. If false, simply create an associative record without using external ID. Admin accounts always operate as if this were false (they don't need an external ID). This is open to adjustment. For example, we might create a random external ID if desired.

Create v4 ExternalIds table (SQL tables, server APIs, SDK and integration tests)

This is an associative table with the additional metadata of an external ID that defines there relationship for lookup. It is possible for this to exist without an associated user, as when we import external IDs to validate them when accounts are created. It's also possible to have the association without an external ID (Table might be better called SubStudyAccounts). Users can be in more than one sub-study.

• accountId [FK] - optional
• studyId [FK] - required
• subStudyId [FK] - required
• externalId - optional. developers/admins wouldn't have one

Migrate all the existing external IDs from DynamoDb to SQL tables and adjust all the existing APIs to use the new external Ids table. This means adjusting some APIs to look up users through this association rather than the DDB table.

Add a user's sub-study association to AccountSummary, StudyParticipant, and the UserSession.

Participant APIs should only include sub-studies that the caller belongs to. Admins might break this rule.

Users can be associated to a sub-study in one of several ways:

• devs/researchers can create an association without an external ID
• normal users can be associated by providing an external ID during create/update
• Signing the consent for a subpopulation can associate a user to a sub-study, without an external ID (other behaviors can be implemented as needed).

Uploads need to be tagged with the ID of a user's sub-studies (all of them). Hence the need to have this available in the StudyParticipant record and the UserSession.

Consider removing "unmanaged" external IDs as this adds some complexity to the logic of external IDs and I'm not sure this is used at all at this point, or will be.

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.

I would also start by cleaning up accounts to remove GenericAccount and HibernateAccount and the copying between the two of them (subsequent changes will heavily involve the AccountsDao so it would help to simplify first).