[CLS] Database design

# [CLS] Database design ###### tags: `Collidescope` ## Table of Content - [Common fields](#Common-fields) - [ResetPasswordRequest](#ResetPasswordRequest) - [User](#User) - [UserConsent](#UserConsent) - [UserInvitation](#UserInvitation) - [AccessToken](#AccessToken) - [Admin](#Admin) - [AdminRole](#AdminRole) - [Campaign](#Campaign) - [CampaignGoal](#CampaignGoal) - [CampaignPartner](#CampaignPartner) - [CampaignPost](#CampaignPost) - [Post](#Post) - [AccountProfileChannel](#AccountProfileChannel) - [AccountProfile](#AccountProfile) - [AccountProfileUser](#AccountProfileUser) - [Note](#Note) ## Common fields ```typescript= { createdBy: UUID createdAt: Date updatedBy: UUID | null updatedAt: Date deletedBy: UUID | null deletedAt: Date | null } ``` ## ResetPasswordRequest ```typescript= { resetPasswordRequestId: UUID userId: UUID token: string expiredAt: Date usedAt: Date | null } ``` ## User ```typescript= { userId: UUID email: string password: string firstName: string lastName: string } ``` ## UserConsent ```typescript= { userConsentId: UUID userId: UUID consentVersion: number consentDate: Date } ``` ## UserInvitation - รับทันทีที่กด accept จากในหน้า invite ```typescript= { userInvitationId: UUID accountProfileUserId: UUID token: string acceptedAt: Date | null } ``` ## AccessToken ```typescript= { accessTokenId: UUID userId: UUID expirdAt: Date } ``` ## Admin ```typescript= { adminId: UUID userId: UUID adminRoleId: UUID /* role: 'editor' | 'viewer' */ } ``` ## AdminRole ```typescript= { adminRoleId: UUID roleName: 'editor' | 'viewer' } ``` ## Campaign ```typescript= { campaignId: UUID campaignName: string description: string startDate: Date endDate: Date status: 'draft' | 'published' donationGoal: number | null engagementGoal: number | null reachGoal: number | null impressionGoal: number | null } ``` ## CampaignPartner ```typescript= { campaignPartnerId: UUID campaignId: UUID accountProfileId: UUID } ``` ## CampaignPost ```typescript= { campaignPostId: UUID campaignId: UUID postId: UUID } ``` ## Post - sample postRefId for each platfrom - FB Post Id: `110094217232613_380516090190423` - IG Post Id: `17950326643510054` - YT Video Id: `-biOGdYiF-I` - Twitch Clip Id: `AmazonianEncouragingLyrebirdAllenHuhu` ```typescript= { postId: UUID channelId: UUID postedDate: Date postUrl: string postRefId: string message: string thumbnailUrl: string | null } ``` ## AccountProfileChannel ```typescript= { accountProfileChannelId: UUID accountProfileId: UUID socialAccountChannelId: UUID } ```   ## SocialAccount ```typescript= { socialAccountId: UUID userId: UUID accountName: string platform: 'facebook' | 'instagram' | 'youtube' | 'twitch' accessToken: string refreshToken: string | null } ```   ## AccountProfile ```typescript= { accountProfileId: UUID profileName: string type: 'brand' | 'npo' | 'influencer' level: 'parent' | 'child' parentId: UUID | null firstName: string | null lastName: string | null email: string | null phoneNumber: string | null instagramAccount: string | null preferredContactMethod: 'email' | 'sms' | 'instagram' } ``` ## AccountProfileUser ```typescript= { accountProfileUserId: UUID accountProfileId: UUID userId: UUID status: 'active' | 'inactive' | 'pending' } ``` ## CampaignDonation ```typescript= { campaignDonationId: UUID campaignId: UUID value: number date: Date } ``` ## ChannelMetric ```typescript= { channelMetricId: UUID channelId: UUID metricName: string value: number date: Date } ``` ## PostMetric ```typescript= { postMetricId: UUID postId: UUID metricName: string value: number date: Date } ``` ## Note - Ref สำหรับเรื่อง consent/policy - https://www.iubenda.com/en/help/6469-consent-solution-getting-started - เหมือนเค้าจะแยก legal เป็น 2 types แบบที่ Facebook ทำ + Cookie consent อีก 1 อัน - Privacy Policy - Terms and Conditions / Terms of service - Cookie Consent