Database Schema

flowchart LR
%% https://mermaid-js.github.io/mermaid/#/flowchart
%% https://mermaid-js.github.io/mermaid-live-editor/edit#pako:eNqNVstu2zgU_RVBQLuyg9qxW9uLAk2aAAMkteG0syi0YaRrmVOJ1PDRjGP43-eSEkVKtuOuRJ57dEjeF7mPU55BvIg3BX9Jt0So6GGdsIS9exf9xRSIDUlBJuyGSPiGzP3ejQ6HhN2xnORQAlP7vR93LdHwahg9EJZrBAL4fa3P5JZWHk7YPS2aldzI6LmxVftKBaSKix2KGEPz-RuEpBz3vgJBeUbTNVRc4Na6cyPXRazoNyIEUfQ3NJjRZISllBQtshI8FyBlDSTsdovnAgm4Rjs08u3EKH82v_2DG67RNfyrQarGx2socFFuvSAN9kOCiIbD6BfjL8y5DYHPrQ89h4ucMPpqBSxlGQCeVvDUUx6aiVkrkI9AbRkveK7rxe7aqV8W3VYfxPKDABt-EMEO71lnOdScGzvEXHJQhGHkIguMaws4ytqZL53UK2qGERSSFN-hrNC14BMH_zKT3v5KKJ-tm9owPVqkJT06QqRlQzSONTJhkg-jFASUnO0s5baZhG7u0ovTsT1NXmFhMjwXZgluK9MpZfkjZFSX9c69-ZxCxaqeG87wrLxyDjFj64oGbOwgAwJ9NnIBRzNZQUo3FLKnVNBKaVEv_OOEYYWVVFetF5DOuIYNOpK59dqf1qaSjG9P9RCjQC0eROsc0Xy5-ANiyjVTYrfcLAXNab-YTjszF_xFbVcY6rdSUDRNCK0Xm1A_6YJ_-82p3-Mc-VJFCM6V77CG1M46vErQkojdw-nuEpYYEb9AYcZepnK1BeEs8jwPM6jI1pA7sXs_7-q9MFx3-Xb3qG-X5vawJyPC9bXaP_aSCQ1dpwQejVIB2HWym12nUwQnjzaaZbipL6nNqHqVDtSlZ7AhulD3b524GxffDI6bw6pjdNyQd8QhmFU235ojtl7p8UquzzD-oIMHzIpj8S43t5wp0jQi58auE6JXzsA74yfOjihZE6VApiWfsfvHjiurCoTahZdzRRmD5t5quL3_8BSyKUsu3Z0YlGfTSi0S9lrH6vfcy8SVJ0VSQR37Jxx45qlNSF2aKu50kKca6-VV6h81Z980oQe6_PZJ1HkdnVA9cjbg6Xza3LmZJ2hGfhNakGdaUNXUXQfqxyblpcnn71us1TqKtyGCO-wR6qq2WdKmyTGnBkK9hMWDuARREprhG3ufsChKYuxxJSTxAocZtsYkTtgBeUQr_rRjabxQQsMg1lWGneQrxfojZbzYkEIiipc-buSxfrTbt_sgrgiLF_v4v3gxHE8_XU2ms-n1aPZxPp6P5h8H8Q7x0WR6Nf_wYTqZz2bj6_H8MIhfOUfZ0dV4PJ2NJ5NP0_Fsdj0fjazeT2s0Gzn8D5BiQvk


%% Interfaces
BaseNode{{BaseNode}}
Engagement{{Engagement}}
Engagement -.- LanguageEngagement & InternshipEngagement
FileNode{{FileNode}}
FileNode -.- Directory & File & FileVersion
PeriodicReport{{PeriodicReport}}
PeriodicReport -.- NarrativeReport & FinancialReport & ProgressReport
Changeset{{Changeset}}
Changeset -.-> ProjectChangeRequest

%% Relationships

User -- knownLanguage --> Language
User -- organization --> Organization
User -- location --> Location

Language -- ethnologue --> EthnologueLanguage

Project -- engagement --> Engagement

Project -- budget --> Budget
Budget -- record --> BudgetRecord
BudgetRecord -- organization --> Organization
Budget -- universalTemplateFileNode --> File

Project -- member --> ProjectMember
ProjectMember -- user --> User

Engagement -- ceremony --> Ceremony

LanguageEngagement -- language --> Language
LanguageEngagement -- PartnershipProducingMedium --> Partnership
LanguageEngagement -- pnpNode --> File
LanguageEngagement -- product --> Product
Product -- produces --> Producible
Product -- unspecifiedScripture --> UnspecifiedScripturePortion
Product -- scriptureReferences --> ScriptureRange

InternshipEngagement -- intern --> User
InternshipEngagement -- mentor --> User
InternshipEngagement -- countryOfOrigin --> Location
LanguageEngagement -- growthPlanNode --> File

Project -- report --> NarrativeReport & FinancialReport
Engagement -- report --> ProgressReport
PeriodicReport -- reportFileNode --> File

Project -- rootDirectory --> Directory
Project -- primaryLocation --> Location
Project -- marketingLocation --> Location
Project -- otherLocations --> Location
Project -- fieldRegion --> FieldRegion
Project -- owningOrganization --> Organization

FileVersion -- parent --> File
File -- parent ---> Directory
FileNode -- createdBy --> User

Location -- fundingAccount --> FundingAccount
Location -- defaultFieldRegion --> FieldRegion

Project -- partnership --> Partnership
Partnership -- partner --> Partner
Partnership -- agreementNode ---> File
Partnership -- mouNode ---> File
Partner -- organization --> Organization
Partner -- pointOfContact --> User

FieldRegion -- zone --> FieldZone
FieldRegion -- director --> User
FieldZone -- director --> User

BaseNode --> Property

User -- pinned --> BaseNode

BaseNode -- post --> Post

ProgressReport -- progress --> ProductProgress
Product -- progress --> ProductProgress
ProductProgress -- step --> StepProgress

ProgressReport -- summary --> ProgressSummary

Project -- changeset --> ProjectChangeRequest
BaseNode -- changeset --> Changeset
Changeset -- changeset --> Property

User -- education --> Education
User -- unavailability --> Unavailability

BaseNode -- commentThread --> CommentThread
CommentThread -- creator ---> User
CommentThread -- comment --> Comment

Database Engine

Cord Platform uses the Neo4j graph database. Graph databases are usually classified as a type of NoSQL database as most use a schema-on-read paradigm, while SQL is schema-on-write. Graph DBs value relationships as first-class citizens, and when the engine's native data structure is a graph, as is Neo4j's, can provide very low cost traversals. Traversing a dozen nodes in a query is not even remotely a problem, but doing a dozen joins in SQL would greatly affect query performance.

The decision to use a graph database was based on the ever-increasing inter-connectedness of our data. As Cord seeks to be used by more and more organizations, we expect the inter-connectedness to increase.

Concepts

History in Schema

We don't have a traditional schema. Normally, a node represents some sort of entity in your model and the node's properties are the fields of that entity. Because we would like to keep a history of all changes to our data in the schema itself and not just a log facility, we have chosen to represent an entity using a :BaseNode node for the entity itself, and multiple :Property nodes to hold the properties of the :BaseNode. Whenever a :Property is updated, the relationship between the :BaseNode and old :Property is set to active = false and a new :Property node is created with a relationship set to active = true. This schema lets us track and revert all changes to the graph.

Field Level Permissions

Full page: Field Level Permission

Stakeholders of Cord would like to have granular control over who can read/write each field on an entity under their administrative control. In order to facilitate this, we use role definitions in the API's authorization service to grant permissions on a per-property basis.