Database Schema

This document outlines the database schema for the Coastal Element AI SDR backend, which is defined using Prisma and uses MongoDB.

Overview

• Database: MongoDB
• ORM: Prisma

---

Entity Relationship Diagram

---

Enums

CampaignStatus

Defines the possible statuses for a campaign.

• DRAFT
• ACTIVE
• PAUSED
• COMPLETED
• ARCHIVED

---

Authentication Tables

These tables are responsible for handling user authentication, sessions, and accounts.

User

Core user model.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
name	String
email	String	@unique
emailVerified	Boolean
image	String?
createdAt	DateTime
updatedAt	DateTime
role	String?
banned	Boolean?
banReason	String?
banExpires	DateTime?
twoFactorEnabled	Boolean?
sessions	Session[]	Relation to Session model
accounts	Account[]	Relation to Account model
twofactors	TwoFactor[]	Relation to TwoFactor model
profile	UserProfile?	Relation to UserProfile model

Session

User sessions.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
expiresAt	DateTime
token	String	@unique
createdAt	DateTime
updatedAt	DateTime
ipAddress	String?
userAgent	String?
userId	String	Foreign key to User
user	User	Relation to User model
impersonatedBy	String?

Account

User accounts from different providers.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
accountId	String
providerId	String
userId	String	Foreign key to User
user	User	Relation to User model
accessToken	String?
refreshToken	String?
idToken	String?
accessTokenExpiresAt	DateTime?
refreshTokenExpiresAt	DateTime?
scope	String?
password	String?
createdAt	DateTime
updatedAt	DateTime

Verification

Stores verification tokens for actions like email verification.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
identifier	String
value	String
expiresAt	DateTime
createdAt	DateTime?
updatedAt	DateTime?

TwoFactor

Stores two-factor authentication settings for users.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
secret	String
backupCodes	String
userId	String	Foreign key to User
user	User	Relation to User model

---

Application Tables

These tables handle the core application logic, including user profiles, brands, campaigns, and leads.

AppSettings

Global configurations for the application.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
maintenance_mode	Boolean	@default(false)
maintenance_message	String?
updatedAt	DateTime	@default(now()) @updatedAt

UserProfile

Extended user profile information.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
preferences	Json	@default("{}")
brand	Brand[]	Relation to Brand model
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt
user	User?	Relation to User model
userId	String?	@db.ObjectId @unique

Brand

Represents a user-owned brand.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
name	String
profileId	String	@db.ObjectId
profile	UserProfile	Relation to UserProfile model
leads	Leads[]	Relation to Leads model
campaigns	Campaigns[]	Relation to Campaigns model
info	BrandInformation	Relation to BrandInformation model
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt
brandInformationId	String	@db.ObjectId

BrandInformation

AI configurations for a brand.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
personality	String
voice	String
audience	String
idealClientProfile	String?
biggestChallenges	String?
documentUrl	String?
emails	Json	@default("[]")
socialAccounts	Json	@default("{}")
phone	String?
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt
Brand	Brand[]	Relation to Brand model

Leads

Groups or containers for contacts.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
name	String
description	String?
totalContacts	Int	@default(0)
isDeleted	Boolean	@default(false)
tags	Json	@default("[]")
brandId	String	@db.ObjectId
brand	Brand	Relation to Brand model
campaigns	Campaigns[]	Relation to Campaigns model
contacts	Contacts[]	Relation to Contacts model
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt

Contacts

Detailed individual leads.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
firstName	String?
lastName	String?
company	String?
email	String
phone	String?
linkedin	String?
facebook	String?
xProfile	String?
website	String?
location	String?
tags	Json	@default("[]")
metadata	Json	@default("{}")
status	String	@default("new")
stage	String	@default("prospect")
bucketType	String	@default("free")
traits	Json	@default("[]")
followUpDate	DateTime?
convertedAt	DateTime?
sourceFile	String?
isEnriched	Boolean	@default(false)
enrichedAt	DateTime?
enrichmentSource	String	@default("manual")
lastEngagementAt	DateTime?
totalLinksClicked	Int	@default(0)
totalEmailsOpened	Int	@default(0)
totalReplies	Int	@default(0)
engagementScore	Int	@default(0)
isDeleted	Boolean	@default(false)
importedAt	DateTime	@default(now())
leads	Leads?	Relation to Leads model
leadsId	String?	@db.ObjectId
campaignLeads	CampaignLeads[]	Relation to CampaignLeads model
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt
Conversations	Conversations[]	Relation to Conversations model

Campaigns

Manages marketing campaigns.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
name	String
description	String?
type	String
status	CampaignStatus	@default(DRAFT)
targetLeadCount	Int	@default(0)
totalLeadsEnrolled	Int	@default(0)
totalResponses	Int	@default(0)
appointmentsSet	Int	@default(0)
pipelineValue	Float	@default(0.0)
avgResponseTime	Float	@default(0.0)
launchedAt	DateTime?
pausedAt	DateTime?
isActive	Boolean	@default(false)
steps	CampaignSteps[]	Relation to CampaignSteps model
campaignLeads	CampaignLeads[]	Relation to CampaignLeads model
connectLeads	Leads	Relation to Leads model
connectLeadsId	String	@db.ObjectId
brandId	String	@db.ObjectId
brand	Brand	Relation to Brand model
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt

CampaignSteps

Sequence of activities in a campaign.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
type	String
data	Json	@default("{}")
is_active	Boolean	@default(true)
execution	StepExecutions	Relation to StepExecutions model
campaignId	String	@db.ObjectId
campaign	Campaigns	Relation to Campaigns model
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt
stepExecutionsId	String	@db.ObjectId

StepExecutions

Tracks the execution of each campaign step.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
errorMessage	String?
responseReceived	Boolean	@default(false)
executedAt	DateTime?
scheduledAt	DateTime?
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt
CampaignSteps	CampaignSteps[]	Relation to CampaignSteps model

CampaignLeads

Metrics for each lead within a campaign.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
lastContactAt	DateTime?
optedOut	Boolean	@default(false)
hasReplied	Boolean	@default(false)
firstReplyAt	DateTime?
totalReplies	Int	@default(0)
totalOpens	Int	@default(0)
totalClicks	Int	@default(0)
lastEngagement	DateTime?
campaignId	String	@db.ObjectId
campaign	Campaigns	Relation to Campaigns model
contact	Contacts?	Relation to Contacts model
contactId	String?	@db.ObjectId
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt

Conversations

Inbox threads for each contact.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
campaignId	String?	@db.ObjectId
status	String	@default("active")
mode	String	@default("auto")
handoffAt	DateTime?
tag	String	@default("medium")
messages	Messages[]	Relation to Messages model
contactId	String	@db.ObjectId
contact	Contacts	Relation to Contacts model
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt

Messages

Individual messages within a conversation.

Field	Type	Notes
id	String	@id @default(auto()) @map("_id") @db.ObjectId
content	String
message_type	String
scheduled_at	DateTime?
sent_at	DateTime?
delivered_at	DateTime?
opened_at	DateTime?
open_count	Int	@default(0)
metadata	Json	@default("{}")
conversationId	String	@db.ObjectId
conversation	Conversations	Relation to Conversations model
createdAt	DateTime	@default(now())
updatedAt	DateTime	@default(now()) @updatedAt