Pinpoint Database Design

账号相关

SystemUsers

• id
• email
• password
• firstName
• middleName
• lastName
• createdAt
• updatedAt

OrganizationUsers

• id
• organizationId (References Organzations table id column)
• email
• password
• firstName
• middleName
• lastName
• phone
• createdAt
• updatedAt

MemberUsers

• id
• organizationId (References Organizations table id column)
• email
• password
• active (Used to determine whether can login, Default false)
• firstName
• middleName
• lastName
• phone
• memberTypeId (References MemberTypes table id column)
• createdAt
• updatedAt

LeoAccounts

• id
• username
• plainPassword
• eventId
• active
  • Type: boolean (If event ends, this value set to be false)
• createdAt
• updatedAt

Organization 相关

Organizations

• id
• name
• features
  • Type: string array
• contactEmail
• contactName
• city
• state
• country
• contactPhone
• mapFile
• createdAt
• updatedAt

DepartmentAliases

• id
• parentId
• mpath
• level (Start from 0, 0 means the single root for organization)
• organizationId
• alias
• createdAt
• updatedAt

Departments

• id
• parentId
• mpath
• level (Start from 0, 0 means the single root for organization)
• organizationId
• name
• createdAt
• updatedAt

Member 相关

MemberTypes

• id
• organizationId (References Organizations table id column)
• name
• createdAt
• updatedAt

MemberGroups

• id
• organizationId (References Organizations table id column)
• name
• createdAt
• updatedAt

MemberUsers_MemberGroups

• memberUserId (References MemberUsers table id column)
• memberGroupId (References MemberGroups table id column)

Event 相关

EventTemplates

• id
• organizationId
• name
• instructionForMember
• instructionForAdmin
• icon
• whiteIcon
• createdAt
• updatedAt

Events

• id
• organizationId
• initiatorId
• initiatorType
  • Type: enum
  • Values: "system_user", "organization_user", "member_user"
• terminatorId
• terminatorType
  • Type: enum
  • Values: "system_user", "organization_user", "member_user"
• eventTemplateId
• startDate
• endDate
• type
  • Type: enum
  • Values: drill, emergency
• name
• instructionForMember
• instructionForAdmin
• map
• createdAt
• updatedAt

Events_MemberTypes

• eventId
• memberTypeId

HelpTypes

• id
• name
• createdAt
• updatedAt

EventCheckins

• id
• eventId
• memberUserId
• smsStatus
  • Type: enum
  • Values: "not_sent", "sent", "failed"
• emailStatus
  • Type: enum
  • Values: "not_sent", "sent", "failed"
• status
  • Type: enum
  • Values: "unacknowledged", "acknowledged", "safe", "need_help", "not_in_area"
• statusDate
• departmentId
• customDepartmentName (If departmentId is null, set this value)
• nearbyPeopleCount
• reportedById (member user who report)
• helpTypeId
• createdAt
• updatedAt

Broadcast 相关

Broadcasts

• id
• organizationId
• creatorId
• creatorType
  • Type: enum
  • Values: "system_user", "organization_user", "member_user"
• content
• createdAt
• updatedAt

BroadCasts_MemberTypes

• broadcastId
• memberTypeId

BroadcastReceivers

• id
• broadcastId
• memberUserId
• smsStatus
  • Type: "not_sent", "sent", "failed"
• emailStatus:
  • Type: "not_sent", "sent", "failed"
• createdAt
• updatedAt

Message 相关

Messages

• id
• eventId
• senderId
• senderUserType
• content （如果是图片，content为图片的文件名）
• isImage
• createdAt
• updatedAt

MessageReceivers

• id
• messageId
• receiverId
• receiverUserType
• watchedAt
• createdAt
• updatedAt

Form 相关

FormTemplates

• id
• name
• userDataSchema(表单数据提交的JSON Schema)
• customizedVisualDataSchema(表单自定义显示JSON Schema)
• customizedBusinessDataSchema(表单业务自定义JSON schema)
• createdAt
• updatedAt

Forms

• id
• organizationId
• formTemplateId
• name
• customizedVisualData(替换logo之类的自定义数据)
• customizedBusinessData(替换表单业务自定义的数据)
• createdAt
• updatedAt

FormUserData

• id
• organizationId
• formId
• memberUserId
• data (type: jsonb)
• createdAt
• updatedAt