---
# System prepended metadata

title: '[CLS] Database design'
tags: [Collidescope]

---

# [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
}
```

<!--
## Channel

- sample channelRefId for each platfrom
  - FB Page Id: `110094217232613`
  - IG Profile Id: `17841449377935979`
  - YT Channel Id: `UC_x5XG1OV2P6uZZ5FSM9Ttw`
  - Twitch Channel Id: `44322889`

```typescript=
{
  channelId: UUID
  channelName: string
  platform: 'facebook' | 'instagram' | 'youtube' | 'twitch'
  channelRefId: string
  pageToken: string | null
}
```
-->

<!--
## UserSocialAccount

```typescript=
{
  userSocialAccountId: UUID
  userId: UUID
  socialAccountId: UUID
}
```
-->

## SocialAccount

```typescript=
{
  socialAccountId: UUID
  userId: UUID
  accountName: string
  platform: 'facebook' | 'instagram' | 'youtube' | 'twitch'
  accessToken: string
  refreshToken: string | null
}
```

<!--
## SocialAccountChannel

```typescript=
{
  socialAccountChannelId: UUID
  socialAccountId: UUID
  channelId: UUID
  accessToken: string | null
}
```
-->

<!-- ## UserChannel

```typescript=
{
  userChannelId: UUID
  userId: UUID
  channelId: UUID
  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
