Define Schema for Sections

[briri]

We need to review the wireframes related to Template Sections and define the initial schema, queries and logic that the backend will need to provide.

Related to #33

[briri]

Section

• id Int!
• displayOrder Int!
• name String!
• introduction String
• requirements String
• isDirty boolean! (default: false) (indicates whether or not the section has changed since the template was last published)
• created DateTime! (default current timestamp)
• createdById The id of the User who created the record
• modified DateTime! (default current timestamp)
• modifiedById The id of the User who last modified the record

Tags

• id Int!
• name String!
• description String

SectionTags

• sectionId Int!
• tagId Int!

VersionedSection

A "published" section (or when saved as a draft) is essentially just a snapshot of a Section at a point in time that the parent Template was published (or saved as a draft). Versioned sections are immutable!

• id Int!
• versionedTemplateId Int! (Id of the parent VersionedTemplate)
• sectionId Int! (Id of the underlying Section that this is a snapshot of)
• displayOrder Int!
• name String!
• introduction String
• requirements String
• created DateTime! (default current timestamp)
• createdById The id of the User who created the record
• modified DateTime! (default current timestamp)
• modifiedById The id of the User who last modified the record

Queries

• Get the sections for a template sections(templateId: number): Promise<Section[] | PublishedSection[]>
• Get a specific section section(sectionId: number): Promise<Section | PublishedSection>
• Search for sections publishedSections(term: string): Promise< PublishedSection[]>

Mutations

• Create a section addSection(templateId: number, name: string, copyFromSectionId: number): Promise<Section>
• Update a section updateSection(sectionId: number, name: string): Promise<Section> (errors included)
• Delete a section removeSection(sectionId: number): Promise<boolean>

Other Logic

• isDirty flags cascade up. Meaning that if a section is edited/added/removed the related Template's isDirty flag should be set to true (this logic should be added to the TemplateService)

Versioning

• Versioning occurs when the parent template is published. The Template's Sections are published automatically in the backend.

[fraserclark]

@briri Depending on how you see the data being stored but "section display order" might be important when we return the sections for a template

[jupiter007]

That's a good point @fraserclark. Maybe we could store the order in a TemplateSections table, which we will probably need to map template ids to sections ids.

[jupiter007]

@briri for the section tags, would it make more sense to create a SectionTags table, where we include the section id and the tag id rather than having an Int[] array of tags in each section record?

[briri]

Good points all around. I have updated the definition above.

• added 'displayOrder' to the sections table
• added a 'tags' and 'sectionTags' table

I don't think we want a TemplateSections join table. A template has sections, but sections do not have templates. When someone selects an existing section from the library to add to their template, we will clone the section and attach that new copy to the template. This allows them to be modified independently within each template.

[jupiter007]

Thanks @briri. Should I update the above documentation with "displayOrder" and remove "tags" from "Sections" above? Or should we keep the final schema documentation in another location?

Also, Just to double check - a section is unique to just one template and cannot be shared across multiple templates? The reason I am asking is that in the wireframe, I see the below page. If a section can belong to multiple templates, then would it make sense to have a TemplateSections table?

Also, in the "previously created sections", are the sections listed just the ones that the user created before, or ones that were used by Funder or org? I'm trying to figure out how we will associate the sections with the user or funder/org.

[jupiter007]

@briri are we excluding "Section guidance" field? I can't remember. The wireframe includes it (see below), so if we need it we should add a "guidance" field to "Section" above.

[briri]

I updated the definition above with those changes.

I don't think it's a good idea to have a single section belong to multiple templates (at least from a database perspective). Unless I am misunderstanding the way sections will work in the redesigned system. This is my understanding of how they work (note that this is clouded by how it currently works 🙃):

• I create a 'Roles and Responsibilities' section for my first template called 'Example University Generic template'.
• I then create another template called 'Example University Medical Center template'.
• I add a new section to that 'Example University Medical Center template' and select 'Roles and Responsibilities' from my library do I don't have to retype everything.
• A month later I decide that my first template needs an additional question in the 'Roles and Responsibilities' section.
  • What happens at this point? Does my change update the section for both templates?
• What about if I added the section from my library and immediately changed some of the wording in the requirements section.
  • What happens? Does the change apply to both templates?

[jupiter007]

@briri I see what you are saying. So when a user selects an existing section for a new template, do we create a separate section id based off of the info from the existing template?

[fraserclark]

@briri @jupiter007

I have always assumed the previously created sections are "locked in time" - an exact clone of that section, at the time of creation - zero linking or push/pulling of content. So whatever the sections content (that is published) of the template you are "duplicating it from"

This applies to the DMP best practice sections too, they are only a starter kit, once you have selected the section, you have full ownership of it.

So, in your example where you add a new question to Roles and Responsibilities for template 3, you would need to go back and update template 1 and template 2 with your new question.

However, it does bring up an interesting thought, which was never fully decided/thought through.

If you have 3 templates, you might have 3 "Roles and Responsibilities" in your "previously created sections" listed and it is hard to determine which one belongs to which previously created template.

I'd be reluctant in this "phase" of work to allow them to manage their "library of previous created sections” as it's a nice-to-have and we have more important things to work on.

I’d suggest we just add some text to suggest the "template" it is 'associated' with

[briri]

Yes 💯 we can just include the title to help them select the correct section from the library.

@jupiter007 yes, we would have a new id for the "copied" section. The MySQL DB uses an auto-increment number, so it will handle id assignment.

I think it makes sense to let the backend handle tasks like copying/cloning a section/template/question. We could have a GraphQL mutation called cloneSection(newTemplateId: number, sectionToCloneId: number, displayPosition: number): Section Then the backend would copy the original section, attach the new templateId to it, change the name to Copy of ..., set the created timestamp and the createdById from the JWT, etc. Then save it which would assign an id, and return it to the front end.