Define new data model

[briri]

We need to define the data model for the new DMSP system. It is assumed that we will need to add additional columns to tables as we begin to build out the workflows for the UI and discover information that will need to be persisted to support the UI.

Outstanding questions: Should we host everything in DynamoDB or leave registered DMPs in DynamoDB and then all of these tables in a MySQL or Postgres DB on RDS?

The structure of the records/entries will be significantly different in Dynamo vs RDS. For example, the Customization table would look like the following:

• RDS: A single table with an entry for each Org+Question. This requires more trips to the DB but isn’t particularly onerous.
• DynamoDB: A single record per Org. Each record contains the customizations for all of the Questions. DynamoDB’s caching mechanism would reduce the number of calls to the DB in this approach. The Rails app though would need to fetch and then parse the JSON. (Might want to consider implementing a shared Redis cache for this)

Regardless of which DB technology we use, we will make heavy use of JSON data structures.

One benefit to using JSON data structures is from the perspective of making the code open source friendly and more reusable. Our UI implementation for example may have a need to store data that is only relevant to the UI between pages and/or sessions. By using a schema less JSON data type, we remove the tight connection between the front end and the back end.

Notes: All of the tables below are assumed to have created and updated timestamps. They will also have their own internal id If we decide to host the tables in a relational DB, this can just be the auto increment field.

If we opt to store this data in DynamoDB, then any table below with a family_id will use that value as the PK and will use the SK to support versioning like we do with DMP IDs.

If we use DynamoDb we will want to update the DMP ID record so that it has a registered timestamp and put logic into place to only trigger EZID registration once the DMP is ready. We may also want to just merge the Drafts and Dmps into the same table and have a flag to indicate the difference.

Table Definitions:

Tags (formerly Themes)

• Tags are used to group Questions together into logical sections, they are also used to connect generic guidance to logical sections
• Structure:
  • Name - STRING
  • UiLabel - String
  • Description - TEXT

TagGuidance

• Guidance is helpful information that will be presented to a user when they are filling out their DMP. It uses the Tag to connect it to logical groups of Questions. The current user’s Org affiliation is used to find the appropriate Guidance
• Structure
  • Tag - FKey
  • Org - FKey
  • FamilyId - GUID (used to group versions together)
  • Published - Boolean
  • Text - Text

QuestionType

• All components of a DMP can be broken down into individual questions. This table represents data that will help the UI display the question for the user.
• Structure
  • Name - String
  • UiLabel - String
  • Description - Text
  • WidgetDefinition - JSON Object (TBD … instructions about the HTML type e.g. checkbox, text area, typeahead with API endpoint, etc.)

Question

• Question is the core component of the DMPTool system.
• Structure
  • Tag - FKey
  • Org - FKey
  • Type - FKey
  • FamilyId - GUID (used to group versions together)
  • Published - Boolean
  • Text - Text
  • Repeatable - Boolean (indicates to the UI if the user can supply multiple responses … research outputs, collaborators, costs, etc.)
  • Guidance - Text (guidance specific to the question)
  • ExampleAnswer - Text
  • DmpIdTarget - JSON Object (instructs the Rails backend about how the answer to the question should be mapped to the DMP ID JSON)
  • Options - JSON Array (an array of options/answers if the question is a checkbox, selectbox, or radio button)

Overlay (formerly Customization)

• A customization is an Overlay for a particular question. This allows an Org to replace or add an addendum to elements of a question that will only apply to their Users.
• Structure
  • Question - FKey
  • Org - FKey
  • Outdated - Boolean (gets triggered when the associated question is versioned. Meant to alert the owner)
  • Published - Boolean
  • Refinements - JSON Object (this object can contain various “overlays” to the associated question. For example, additional guidance, recommendations for specific repositories, etc.)

Template

• The template is an organizing structure for questions. It is used to determine the order in which Tags are presented to the User and also defines any conditional logic. The TagOrder and Conditions can be copied from one template to another (e.g. I want to use the same questions/ordering/conditions of the ‘DataPreservation’ tag that NIH has defined)
• Structure
  • Org - FKey
  • FamilyId - GUID (used to group versions together)
  • Title - String
  • Description - Text
  • Published - Boolean
  • TagOrder - JSON Array (an array of JSON Objects, each containing a Tag and a JSON Array used to indicate the ordering of Question)
  • Conditions - JSON Object (containing Tag, Question FKeys, the action and the target(s) which can be other Tag or Question)

Draft

• A store for DMPs that have not yet been registered (tests, in progress DMPs). If we use DynamoDB this will not need to be separate. The structure of the JSON data is identical to the registered DMP ID
• Structure
  • DraftId - GUID
  • DmpId - FKey (The registered version)
  • DraftData - Data that support UI between pages and session
  • RDA Common Standard JSON Object (containing UI helper data)
  • Comments - JSON Array (containing JSON objects that include a timestamp, the User, the comment, and a pointer to the part of the DMP ID that was commented on (e.g. datasets, collaborators, preservation statement, etc.)

DMP

• Follows the RDA common standard (with some DMPTool specific fields) and currently resides in the DynamoDB
• Structure (JSON)
  • PK - String (The DMP ID: PK#doi.org.11.22222/A3D4E5)
  • SK - String (The version: VERSION#latest or VERSION#2023-01-01T01:02:03)
  • RDA Common standard elements

Org

• An institution, organization or funding agency. The table will be synced with ROR on a monthly basis. It can also contain entries that are not in ROR.
• Structure
  • Id - GUID (internal identifier)
  • RorId - String (the ROR identifier)
  • FundrefId - String (the Crossref Funder Id)
  • Name - String
  • Url - String
  • ApiTarget - String (URL of the API endpoint to search for projects)
  • ApiGuidance - Text (info to display in UI when user is searching the API)
  • ApiQueryFields - JSON Object (describes the fields the UI should provide for search)
  • ApiAuthTarget - String (URL of the authentication endpoint if applicable)
  • Metadata - JSON Object (essentially the rest of the ROR record)
  • Notification - JSON Object (similar to the GlobalNotification table but targeted at users associated with this Org)

User

• A user record. Each user is associated with an Org. The structure of this table will differ depending on whether or not we use Cognito for Auth.
• Structure
  • Org - FKey
  • Id - GUID (possibly from Cognito)
  • Username - String (If not using Cognito)
  • FirstName - String
  • LastName - String
  • Email - String
  • Password - String (If not using Cognito)
  • Orcid - String
  • Role - String (super admin, admin, or researcher)
  • ApiClientId
  • ApiClientSecret
  • DismissedNotifications - JSON Array (array of notifications that they have dismissed)

Notification

• Administrative messages that get displayed within the UI. These are currently only for super admins to broadcast global messages about system maintenance. The new Org table has a place where an Org admin can define a notification that targets their own users. Super Admins can also target groups of Orgs with messaging. In that instance, the info is stored here AND copied to the Org records
• Structure
  • StartOn - Timestamp (when to start displaying the notification)
  • EndOn - Timestamp (when to stop displaying the notification)
  • Level - String (info, warn)
  • Dismissable - Boolean (whether or not the user can dismiss the message)
  • TargetedOrgs - JSON Array (can be empty, or include the Org ids that are targeted)

[briri]

Use Cases and the queries needed: Oauth Grant exchange for Token

• OAuth User Query - Cognito -- Single record (PK: code)

User Authentication (OAuth callback target)

• User Query - RDS (PK: email/username) -- Single record

Typeahead / Search Queries

• Public DMPs - Open Search (Keys: multiple criteria/facets) -- Multiple records
• Orgs - Open Search (Key: name/homepage) -- Multiple records
• Funders - Open Search (Key: name/hompage) -- Multiple records
• Repositories - Open Search (Key: name/homepage) -- Multiple records
• Metadata Standards - Open Search (Key: name/homepage) -- Multiple records
• Licenses - RDS (Key: name) -- Multiple records
• Awards - External API (Keys: multiple criteria) -- Multiple records

Canned

• Research Output Types - RDS
• Research Output Access Levels - Rails enum
• Research Domains - RDS
• User Roles - Rails enum
• DMP visibilities - Rails enum
• Contributor Roles - RDS

Public Plans

• DMPs - Open Search (Keys: multiple criteria / facets) -- multiple records
• Orgs - Typeahead (for faceting)
• Funders - Typeahead (for faceting)

Dashboard

• Admin Query - Open Search (Key: org) -- Multiple records (admins get all DMPs for the Org)
• Researcher Query - Open Search (Key: user) -- Multiple records (researchers just get their DMPs)
• Org - RDS (Key: org_id) -- Single record (needed for name, logo, links, notifications, etc.)

Authoring DMP

• DMP - Dynamo (Key: id) -- Single record (only applicable if updating)
• DMP visibilities - Rails enum
• Template - RDS (Key: template_family_id) -- Single record
• Comments - RDS (Key: DMP ID) -- Multiple records
• Project Details
  • Orgs - Typeahead
  • Research Domains - RDS
  • Funders - Typeahead
  • Template - RDS (Key: multiple criteria) -- Mutliple records
• Contributor(s)
  • Orgs - Typeahead
  • ORCIDs - External API (PK: multiple criteria e.g. name and ror) -- Multiple records
  • Roles - RDS
• Research Output(s)
  • Research Output Types - RDS
  • Repositories - Typeahead/Search
  • Metadata Standards - Typeahead/Search
  • Licenses - RDS
  • Research Output Access Levels - Rails enum
• Questions - RDS (Keys: template_family + tag_id)
  • Guidance - RDS (Keys: org_id + tag_id)
  • Overlays - RDS (Keys: org_id + question_family_id) - Single record
• History - Dynamo (Key: DMP ID)

Org Admin

• Org - RDS + Dynamo (Key: org_id) -- single record
• DMPs - Open Search (Keys: multiple criteria / facets) -- multiple records
  • DMP - Dyanmo (Key: DMP ID)
• Users - RDS (Key: multiple criteria e.g. email, name) -- multiple records
  • User - RDS (key: email/username)
• Templates - RDS (Key: org_id)
  • Template - RDS (Key: template_family_id)
    • History - RDS (Key: template_family_id)
    • Tags - RDS (Key: tag_id)
    • Questions - RDS (Key: question_family_id)
      • History - RDS (Key: question_family_id)
      • Question - RDS (Key: question_id)
• Guidance - RDS (Keys: org_id + tag_id)
• Notifications - RDS (Key: org_id)

Super Admin (Same query structures as Org Admin but ability to see ALL and DEFINE DEFAULT/GLOBAL)

• Orgs - Typeahead
• Open Search (Keys: multiple criteria / facets) -- multiple records
• Users - Open Search (Keys: multiple criteria e.g. email, name, org) -- multiple records
• Templates - RDS (Key: org_id)
• Guidance - RDS (Key: org_id)
• Notifications - RDS (Key: GLOBAL)
• Tags - RDS (Key: name)
• Api Clients - Cognito (Key: org_id) + RDS (Key: org_id)

[mariapraetzellis]

We'll be addressing this for each section of the application so closing this ticket