###### tags: `Projects` `CMS` `ERD`

# [CMS] Entity Relation Diagram

## Architecture Diagram

database "SQL" {
    [DB SQL] --|> SQLTCP
}

database "NoSQL" {
    [DB NoSQL] --|> NoSQLTCP
}

node "Repo API" {
    [Repo API] --|> Repo.proto
    SQLTCP <-- [Repo API]
    NoSQLTCP <-- [Repo API]
}

node "Auth API" {
    [Auth API] --|> Auth.proto
    Repo.proto <-- [Auth API]
}

node "User API" {
    [User API] --|> User.proto
    Repo.proto <-- [User API]
    Auth.proto <-- [User API]
    User.proto <-- [Auth API]
}


node "Record API" {
    [Record API] --|> Record.proto
    Repo.proto <-- [Record API]
    User.proto <-- [Record API]
    Auth.proto <-- [Record API]
}

## SQL Database Relation

note "modifiedBy = {\n\tID: int\n\tas: string \n\tfullName: string\n}" as modifiedBy

entity ClientType {
    ID: smallint <<PK>>
    --
    name: varchar(255)
    createdAt: datetime
    updatedAt: datetime
    modifiedBy: JSON
    version: bigint
}

entity ClientAuth {
    ID: smallint <<PK>>
    --
    typeID: smallint
    clientID: varchar(8)
    credential: JSON
    metadata: JSON
    createdAt: datetime
    updatedAt: datetime
    modifiedBy: JSON
}

note right
credential = {
    secret
    lifetime
}
end note

entity AuthProvider {
    ID: smallint <<PK>>
    --
    slug: varchar(255) <<UNIQUE>>
    metadata: JSON
    createdAt: datetime
    updatedAt: datetime
    modifiedBy: JSON
    version: bigint    
}

note left
metadata = {
    saltRounds
    providerURL
    environment
}
end note


entity User {
    ID: bigint <<PK>>
    --
    fullName: varchar(255)
    avatarURL: varchar(511) <<NULLABLE>>
    metadata: JSON
    createdAt: datetime
    updatedAt: datetime
    modifiedBy: JSON
    version: bigint
}

note right
metadata = {
    email
    DOB
    phoneNumber
}
end note

entity UserAuth {
    ID: bigint <<PK>>
    --
    extID: varchar(12) <<UNIQUE>>
    userID: bigint <<FK>>
    authProviderID: smallint <<FK>>
    username: varchar(128) <<UNIQUE>>
    credential: JSON
    statusID: smallint
    metadata: JSON
    createdAt: datetime
    updatedAt: datetime
    modifiedBy: JSON
    version: bigint
}

note right
credential = {
    secret
}
end note

entity UserHasRole {
    ID: bigint <<PK>>
    --
    userID: bigint <<FK>>
    roleID: smallint <<FK>>
    createdAt: datetime
}

entity Role {
    ID: smallint <<PK>>
    --
    name: varchar(255)
    createdAt: datetime
    updatedAt: datetime
    modifiedBy: JSON
    version: bigint
}

entity RecordHeader {
    ID: bigint <<PK>>
    --
    slug: varchar(255) <<UNIQUE>>
    userID: bigint <<FK>>
    tableColumn: JSON
    createdAt: datetime
    updatedAt: datetime
    modifiedBy: JSON
    version: bigint
}

note right
columnType = {
    string: 1
    int: 2
    float: 3
    bool: 4
    file: 5
    string[]: 6
    int[]: 7
    float[]: 8
    bool[]: 9
    file[]: 10
}

tableColumn = map[string][columnType]

Example:
tableColumn = {
    Rontgen: 5
    CTScan: 5
    Analysis: 1
}
end note

entity RecordData {
    ID: bigint <<PK>>
    --
    headerID: bigint <<FK>>
    tableRow: JSON
    createdAt: datetime
    updatedAt: datetime
    modifiedby: JSON
    version: bigint
}

entity RecordHeaderTemplate {
    ID: smallint
    --
    slug: varchar(255) <<UNIQUE>>
    tableColumn: JSON
    statusID: smallint
    createdAt: datetime
    updatedAt: datetime
    modifiedBy: JSON
    version: bigint
}

User "ID"||--||"userID" UserAuth
UserAuth "AuthProviderID"||--||"ID" AuthProvider

User "ID"||--|{"userID" UserHasRole
Role "ID"||--|{"roleID" UserHasRole

User "ID"||--|{"userID" RecordHeader
RecordHeader "ID"||--|{"headerID" RecordData
    
ClientAuth "typeID"||--||"ID"ClientType