Campaign Models

This document covers the campaign data models for the CRM & Marketing Platform.

---

Campaign

Marketing campaign definition.

model Campaign {
  id          String          @id @default(uuid())
  tenantId    String
  clubId      String?

  name        String
  description String?
  type        CampaignType    @default(ONE_TIME)
  status      CampaignStatus  @default(DRAFT)

  // Targeting
  segmentId   String?
  segment     CustomerSegment? @relation(fields: [segmentId], references: [id])

  // Channels enabled
  channels    Channel[]

  // Schedule
  scheduledAt DateTime?
  startedAt   DateTime?
  completedAt DateTime?
  timezone    String?         @default("UTC")

  // For recurring campaigns
  recurPattern String?        // Cron expression
  recurEndAt   DateTime?

  // Content references (per channel - IDs from Messaging service)
  emailTemplateId   String?
  smsTemplateId     String?
  pushTemplateId    String?

  // Stats (denormalized)
  targetCount      Int  @default(0)
  sentCount        Int  @default(0)
  deliveredCount   Int  @default(0)
  openedCount      Int  @default(0)
  clickedCount     Int  @default(0)
  convertedCount   Int  @default(0)
  unsubscribedCount Int @default(0)
  bouncedCount     Int  @default(0)

  // Computed rates
  deliveryRate   Float?
  openRate       Float?
  clickRate      Float?
  conversionRate Float?

  // Audit
  createdAt   DateTime        @default(now())
  updatedAt   DateTime        @updatedAt
  createdBy   String
  deletedAt   DateTime?

  // Relations
  interactions CampaignInteraction[]
  socialPosts  SocialPost[]
  journey      Journey?

  @@index([tenantId, status])
  @@index([tenantId, type])
  @@index([tenantId, scheduledAt])
  @@index([tenantId, clubId])
}

Campaign Types

Type	Description	Schedule
ONE_TIME	Single send	Once at scheduledAt
RECURRING	Repeating	Cron pattern
TRIGGERED	Event-based	On event match
JOURNEY	Multi-step	Managed by Journey

Campaign Status Flow

DRAFT → SCHEDULED → ACTIVE → COMPLETED
           ↓          ↓
        (cancel)   PAUSED → ACTIVE
                      ↓
                  CANCELLED

Stats Calculation

// After campaign completion
const stats = await calculateCampaignStats(campaignId);

await prisma.campaign.update({
  where: { id: campaignId },
  data: {
    deliveryRate: stats.deliveredCount / stats.sentCount,
    openRate: stats.openedCount / stats.deliveredCount,
    clickRate: stats.clickedCount / stats.openedCount,
    conversionRate: stats.convertedCount / stats.clickedCount,
  }
});

---

CampaignInteraction

Individual interaction with a campaign.

model CampaignInteraction {
  id          String          @id @default(uuid())
  tenantId    String
  campaignId  String
  campaign    Campaign        @relation(fields: [campaignId], references: [id], onDelete: Cascade)
  customerId  String
  customer    CustomerProfile @relation(fields: [customerId], references: [id], onDelete: Cascade)

  channel     Channel
  type        CampaignInteractionType

  // Tracking
  messageId   String?         // Messaging.MessageLog.id
  linkId      String?         // Specific link clicked
  linkUrl     String?

  // Context
  deviceType  String?         // mobile, desktop, tablet
  userAgent   String?
  ipAddress   String?
  location    Json?           // { city, country, etc. }

  occurredAt  DateTime        @default(now())

  @@index([campaignId, type])
  @@index([customerId, occurredAt])
  @@index([tenantId, occurredAt])
}

Interaction Types

Type	Trigger	Updates
SENT	Message queued	sentCount++
DELIVERED	Delivery confirmed	deliveredCount++
BOUNCED	Delivery failed	bouncedCount++
OPENED	Email opened	openedCount++
CLICKED	Link clicked	clickedCount++
CONVERTED	Conversion tracked	convertedCount++
UNSUBSCRIBED	Opt-out	unsubscribedCount++
COMPLAINED	Spam report	Handle as complaint
REPLIED	SMS reply	Track as engagement

Tracking Flow

1. Campaign sends message
   └→ CampaignInteraction(type: SENT)

2. Messaging confirms delivery
   └→ CampaignInteraction(type: DELIVERED)

3. Customer opens email
   └→ CampaignInteraction(type: OPENED)

4. Customer clicks link
   └→ CampaignInteraction(type: CLICKED, linkUrl: "...")

5. Customer converts
   └→ CampaignInteraction(type: CONVERTED)

---

Data Dictionary

Campaign Fields

Field	Type	Description
type	Enum	ONE_TIME, RECURRING, TRIGGERED, JOURNEY
status	Enum	DRAFT → SCHEDULED → ACTIVE → COMPLETED
segmentId	String	Target segment
channels	Array	Enabled channels (EMAIL, SMS, etc.)
scheduledAt	DateTime	When to execute
recurPattern	String	Cron expression for recurring
emailTemplateId	String	Messaging template reference
targetCount	Int	Total in segment at send time
sentCount	Int	Successfully queued

Interaction Fields

Field	Type	Description
channel	Enum	EMAIL, SMS, PUSH, etc.
type	Enum	SENT, DELIVERED, OPENED, etc.
messageId	String	Reference to Messaging.MessageLog
linkId	String	Which link was clicked
deviceType	String	Device category

---

Index Strategy

Query Pattern	Index
List campaigns by status	(tenantId, status)
List campaigns by type	(tenantId, type)
Find scheduled campaigns	(tenantId, scheduledAt)
Filter by club	(tenantId, clubId)
Interactions by campaign	(campaignId, type)
Interactions by customer	(customerId, occurredAt)

---

Related Documents

• Specification: Campaigns
• API: Campaigns
• Implementation: Phase 4