CRM & Marketing Platform Data Model

Version: 2.0 Date: December 2024 Status: Draft

Overview
Schema Design Principles
Complete Prisma Schema
Data Dictionary
Indexes Strategy
Migration Notes

Overview

This document defines the complete data model for the CRM & Marketing Platform, including:

Core CRM: CustomerProfile, Activity, Notes, Segments
Campaign Management: Campaign, CampaignInteraction
Marketing Automation: Journey, JourneyStep, JourneyEnrollment
Social Integration: SocialConnection, SocialPost
Event Promotion: EventPromotion
Sync & Audit: SyncLog, AuditLog, DuplicateCandidate

Messaging service owns consent management (GDPR/POPIA compliance). CRM only stores denormalized opt-in flags on CustomerProfile for query convenience. These flags are synced from Messaging via contactPreference.updated events and are READ-ONLY in CRM.

Removed from this schema (owned by Messaging):

MarketingConsent - Use Messaging.ContactPreference
Consent audit trail - Use Messaging.ConsentChangeLog

Schema Design Principles

1. Multi-Tenancy

All tables include tenantId for data isolation.

2. Soft Deletes

Use deletedAt for reversible deletions and GDPR compliance.

3. Audit Trail

All major tables have createdAt, updatedAt, createdBy, updatedBy.

4. Denormalization for Performance

Frequently queried data (opt-in flags, activity counts) denormalized onto CustomerProfile.

5. JSON for Flexibility

Use JSON fields for metadata, rules, and external IDs that may evolve.

Complete Prisma Schema

// ============================================================================
// GENERATOR & DATASOURCE
// ============================================================================

generator client {
  provider        = "prisma-client-js"
  output          = "../../../../node_modules/@prisma/crm"
  previewFeatures = ["fullTextSearch"]
}

datasource db {
  provider = "postgresql"
  url      = env("CRM_DATABASE_URL")
}

// ============================================================================
// ENUMS - CORE
// ============================================================================

enum CustomerStatus {
  ACTIVE
  INACTIVE
  MERGED
  DELETED
}

enum LifecycleStage {
  SUBSCRIBER      // Email only
  LEAD            // Showed interest
  PROSPECT        // Engaged
  CUSTOMER        // Has transacted
  MEMBER          // Active member
  ADVOCATE        // Refers others
  CHURNED         // Lapsed
}

enum IdentityAliasType {
  EMAIL
  PHONE
  AUTH_USER_ID
  EXTERNAL_ID
}

enum ActivityCategory {
  MEMBERSHIP       // Tier changes, signup
  BOOKING          // Tee times, facilities
  COMMUNICATION    // Messages sent/received
  ENGAGEMENT       // Opens, clicks
  FINANCIAL        // Payments
  GOLF             // Handicap, competitions
  MARKETING        // Campaign interactions
  SUPPORT          // Notes, tickets
  SOCIAL           // Social media
  SYSTEM           // Merges, imports
}

enum NoteType {
  GENERAL
  SUPPORT
  COMPLAINT
  FEEDBACK
  FOLLOWUP
  INTERNAL
  MARKETING
  SALES
}

// ============================================================================
// ENUMS - SEGMENTS
// ============================================================================

enum SegmentType {
  STATIC           // Manual membership
  DYNAMIC          // Rule-based
  SMART            // ML-powered
  IMPORT           // External import
}

enum SegmentRefreshFrequency {
  MANUAL
  HOURLY
  DAILY
  WEEKLY
}

// ============================================================================
// ENUMS - CAMPAIGNS
// ============================================================================

enum CampaignType {
  ONE_TIME         // Single send
  RECURRING        // Scheduled repeats
  TRIGGERED        // Event-based
  JOURNEY          // Multi-step automation
}

enum CampaignStatus {
  DRAFT
  SCHEDULED
  ACTIVE
  PAUSED
  COMPLETED
  CANCELLED
}

enum Channel {
  EMAIL
  SMS
  PUSH
  WHATSAPP
  IN_APP
  SOCIAL
}

enum CampaignInteractionType {
  SENT
  DELIVERED
  BOUNCED
  OPENED
  CLICKED
  CONVERTED
  UNSUBSCRIBED
  COMPLAINED
  REPLIED
}

// ============================================================================
// ENUMS - JOURNEYS
// ============================================================================

enum JourneyStatus {
  DRAFT
  ACTIVE
  PAUSED
  ARCHIVED
}

enum JourneyStepType {
  TRIGGER          // Entry point
  SEND             // Send message
  WAIT             // Time delay
  CONDITION        // Branch logic
  SPLIT            // A/B test
  UPDATE           // Update profile
  END              // Exit journey
}

enum JourneyEnrollmentStatus {
  ACTIVE
  COMPLETED
  EXITED
  FAILED
}

// ============================================================================
// ENUMS - SOCIAL
// ============================================================================

enum SocialPlatform {
  FACEBOOK
  INSTAGRAM
  TWITTER
  LINKEDIN
  TIKTOK
}

enum SocialPostStatus {
  DRAFT
  SCHEDULED
  PUBLISHING
  PUBLISHED
  FAILED
}

// ============================================================================
// ENUMS - EVENT PROMOTION
// ============================================================================

enum EventSourceType {
  COMPETITION      // TeeTime tournament
  CLUB_EVENT       // General club event
  SPECIAL          // Special promotion
}

// ============================================================================
// ENUMS - SYNC & AUDIT
// ============================================================================

enum SyncStatus {
  PENDING
  PROCESSING
  SUCCESS
  FAILED
  SKIPPED
}

enum DuplicateResolution {
  PENDING
  MERGED
  NOT_DUPLICATE
  IGNORED
}

// ============================================================================
// CORE MODELS
// ============================================================================

/// Unified customer profile aggregating data from SCL, TeeTime, and Messaging
model CustomerProfile {
  id        String   @id @default(uuid())
  tenantId  String

  // ─────────────────────────────────────────────────────────────────────────
  // IDENTITY KEYS (at least one required)
  // ─────────────────────────────────────────────────────────────────────────
  authUserId    String?   @unique  // IDP user ID (strongest link)
  email         String?            // Normalized lowercase
  phoneNumber   String?            // E.164 format

  // ─────────────────────────────────────────────────────────────────────────
  // CROSS-SERVICE LINKS
  // ─────────────────────────────────────────────────────────────────────────
  sclMemberId        Int?       // SCL.Member.id
  teetimePlayerId    String?    // TeeTime.Player.id (UUID)
  messagingContactId String?    // Messaging.Contact.id

  // ─────────────────────────────────────────────────────────────────────────
  // EXTERNAL IDS (JSON for flexibility)
  // ─────────────────────────────────────────────────────────────────────────
  externalIds Json?  // { mcaV1ZaId, mcaV1UkId, golfRsaId, ... }

  // ─────────────────────────────────────────────────────────────────────────
  // PROFILE DATA
  // ─────────────────────────────────────────────────────────────────────────
  firstName     String?
  lastName      String?
  displayName   String?   // Computed or overridden
  dateOfBirth   DateTime?
  gender        String?   @db.VarChar(10)
  avatarUrl     String?

  // Address
  addressLine1  String?
  addressLine2  String?
  city          String?
  state         String?
  postalCode    String?
  country       String?   @db.VarChar(2)  // ISO 3166-1 alpha-2

  // ─────────────────────────────────────────────────────────────────────────
  // CRM STATUS
  // ─────────────────────────────────────────────────────────────────────────
  status         CustomerStatus   @default(ACTIVE)
  lifecycleStage LifecycleStage   @default(SUBSCRIBER)
  tags           String[]         @default([])

  // ─────────────────────────────────────────────────────────────────────────
  // GOLF DATA (synced from TeeTime)
  // ─────────────────────────────────────────────────────────────────────────
  handicap           Decimal?  @db.Decimal(4, 1)
  handicapProvider   String?   @db.VarChar(32)
  handicapUpdatedAt  DateTime?
  homeClubId         String?
  homeClubName       String?

  // ─────────────────────────────────────────────────────────────────────────
  // MEMBERSHIP DATA (synced from SCL)
  // ─────────────────────────────────────────────────────────────────────────
  membershipTier     String?
  membershipStatus   String?
  memberSince        DateTime?
  membershipExpiry   DateTime?

  // ─────────────────────────────────────────────────────────────────────────
  // MARKETING CONSENT (synced from Messaging - READ ONLY)
  // Messaging owns consent. These are denormalized for query convenience.
  // ─────────────────────────────────────────────────────────────────────────
  emailOptIn         Boolean   @default(false)
  emailOptInAt       DateTime?
  smsOptIn           Boolean   @default(false)
  smsOptInAt         DateTime?
  pushOptIn          Boolean   @default(false)
  pushOptInAt        DateTime?
  whatsappOptIn      Boolean   @default(false)
  whatsappOptInAt    DateTime?

  // ─────────────────────────────────────────────────────────────────────────
  // ENGAGEMENT SCORES (computed)
  // ─────────────────────────────────────────────────────────────────────────
  engagementScore    Int?      // 0-100
  engagementScoreAt  DateTime?
  churnRiskScore     Int?      // 0-100
  churnRiskScoreAt   DateTime?

  // ─────────────────────────────────────────────────────────────────────────
  // FINANCIAL (computed from SCL)
  // ─────────────────────────────────────────────────────────────────────────
  lifetimeValue      Decimal?  @db.Decimal(12, 2)  // Cents
  lifetimeValueAt    DateTime?
  predictedLtv       Decimal?  @db.Decimal(12, 2)

  // ─────────────────────────────────────────────────────────────────────────
  // ACTIVITY METRICS (denormalized for queries)
  // ─────────────────────────────────────────────────────────────────────────
  lastActivityAt     DateTime?
  lastActivityType   String?
  activityCount30d   Int       @default(0)
  activityCount90d   Int       @default(0)
  bookingCount30d    Int       @default(0)
  bookingCount90d    Int       @default(0)
  lastBookingAt      DateTime?
  lastEmailOpenAt    DateTime?
  lastEmailClickAt   DateTime?

  // ─────────────────────────────────────────────────────────────────────────
  // MERGE TRACKING
  // ─────────────────────────────────────────────────────────────────────────
  mergedIntoId       String?
  mergedAt           DateTime?

  // ─────────────────────────────────────────────────────────────────────────
  // AUDIT
  // ─────────────────────────────────────────────────────────────────────────
  createdAt          DateTime  @default(now())
  updatedAt          DateTime  @updatedAt
  deletedAt          DateTime?
  createdBy          String?
  updatedBy          String?

  // ─────────────────────────────────────────────────────────────────────────
  // RELATIONS
  // ─────────────────────────────────────────────────────────────────────────
  aliases              IdentityAlias[]
  activities           Activity[]
  notes                CustomerNote[]
  segmentMemberships   CustomerSegmentMembership[]
  campaignInteractions CampaignInteraction[]
  journeyEnrollments   JourneyEnrollment[]

  // ─────────────────────────────────────────────────────────────────────────
  // INDEXES
  // ─────────────────────────────────────────────────────────────────────────
  @@unique([tenantId, email])
  @@unique([tenantId, phoneNumber])
  @@unique([tenantId, sclMemberId])
  @@unique([tenantId, teetimePlayerId])
  @@unique([tenantId, messagingContactId])
  @@index([tenantId, status])
  @@index([tenantId, lifecycleStage])
  @@index([tenantId, membershipStatus])
  @@index([tenantId, engagementScore])
  @@index([tenantId, lastActivityAt])
  @@index([tenantId, emailOptIn])
  @@index([tenantId, tags], type: Gin)
}

/// Additional identity aliases for a customer (multiple emails/phones)
model IdentityAlias {
  id          String           @id @default(uuid())
  tenantId    String
  customerId  String
  customer    CustomerProfile  @relation(fields: [customerId], references: [id], onDelete: Cascade)

  type        IdentityAliasType
  value       String           // Normalized value
  isPrimary   Boolean          @default(false)
  verifiedAt  DateTime?
  source      String?          // Where this alias came from

  createdAt   DateTime         @default(now())
  updatedAt   DateTime         @updatedAt

  @@unique([tenantId, type, value])
  @@index([customerId])
}

/// Timeline event for a customer
model Activity {
  id          String          @id @default(uuid())
  tenantId    String
  customerId  String
  customer    CustomerProfile @relation(fields: [customerId], references: [id], onDelete: Cascade)

  // Event classification
  type        String          // Specific event type (e.g., TEETIME_BOOKED)
  category    ActivityCategory
  channel     String?         // EMAIL, SMS, IN_PERSON, etc.

  // Content
  title       String          // Human-readable summary
  description String?
  metadata    Json?           // Event-specific data

  // Source tracking
  sourceService String        // scl, teetime, messaging, crm
  sourceId      String?       // Original record ID in source
  sourceEventId String?       // Event ID for deduplication

  // Timing
  occurredAt  DateTime
  processedAt DateTime        @default(now())

  @@unique([tenantId, sourceService, sourceEventId])
  @@index([customerId, occurredAt])
  @@index([tenantId, category, occurredAt])
  @@index([tenantId, type, occurredAt])
}

/// Staff note on a customer
model CustomerNote {
  id          String          @id @default(uuid())
  tenantId    String
  customerId  String
  customer    CustomerProfile @relation(fields: [customerId], references: [id], onDelete: Cascade)

  type        NoteType        @default(GENERAL)
  content     String
  isPinned    Boolean         @default(false)
  isPrivate   Boolean         @default(false)  // Only visible to creator

  createdAt   DateTime        @default(now())
  updatedAt   DateTime        @updatedAt
  createdBy   String
  updatedBy   String?

  @@index([customerId, isPinned, createdAt])
  @@index([tenantId, type])
}

// ============================================================================
// SEGMENT MODELS
// ============================================================================

/// Customer segment definition
model CustomerSegment {
  id          String          @id @default(uuid())
  tenantId    String
  clubId      String?         // Optional club-specific segment

  name        String
  description String?
  type        SegmentType     @default(DYNAMIC)

  // Rules (for DYNAMIC segments)
  criteria    Json?           // Array of rule objects
  combinator  String          @default("AND")  // AND, OR

  // Refresh settings
  refreshFrequency SegmentRefreshFrequency @default(DAILY)
  lastRefreshedAt  DateTime?
  memberCount      Int         @default(0)

  // Status
  isActive    Boolean         @default(true)
  isSystem    Boolean         @default(false)  // System-managed segments

  // Messaging sync
  messagingSegmentId String?  // Linked Messaging.Segment.id
  lastSyncedAt       DateTime?

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

  // Relations
  memberships CustomerSegmentMembership[]
  campaigns   Campaign[]

  @@unique([tenantId, name])
  @@index([tenantId, type, isActive])
  @@index([tenantId, clubId])
}

/// Segment membership junction
model CustomerSegmentMembership {
  id          String          @id @default(uuid())
  tenantId    String
  segmentId   String
  segment     CustomerSegment @relation(fields: [segmentId], references: [id], onDelete: Cascade)
  customerId  String
  customer    CustomerProfile @relation(fields: [customerId], references: [id], onDelete: Cascade)

  // For static segments
  addedBy     String?
  addedAt     DateTime        @default(now())

  // For dynamic segments
  matchedAt   DateTime?
  matchScore  Float?          // Relevance score

  @@unique([segmentId, customerId])
  @@index([customerId])
  @@index([tenantId, segmentId])
}

// ============================================================================
// CAMPAIGN MODELS
// ============================================================================

/// Marketing campaign
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])
}

/// 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])
}

// ============================================================================
// JOURNEY MODELS (Marketing Automation)
// ============================================================================

/// Automated multi-step journey
model Journey {
  id          String          @id @default(uuid())
  tenantId    String
  clubId      String?

  name        String
  description String?
  status      JourneyStatus   @default(DRAFT)

  // Trigger
  triggerType   String        // event type or MANUAL
  triggerConfig Json?         // Additional trigger conditions

  // Optional campaign link
  campaignId    String?       @unique
  campaign      Campaign?     @relation(fields: [campaignId], references: [id])

  // Settings
  allowReentry  Boolean       @default(false)
  maxEnrollments Int?         // Limit concurrent enrollments
  timezone      String        @default("UTC")

  // Stats
  totalEnrolled  Int          @default(0)
  totalCompleted Int          @default(0)
  totalExited    Int          @default(0)

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

  // Relations
  steps       JourneyStep[]
  enrollments JourneyEnrollment[]

  @@unique([tenantId, name])
  @@index([tenantId, status])
  @@index([tenantId, triggerType])
}

/// Individual step in a journey
model JourneyStep {
  id          String          @id @default(uuid())
  journeyId   String
  journey     Journey         @relation(fields: [journeyId], references: [id], onDelete: Cascade)

  stepNumber  Int
  type        JourneyStepType
  name        String?

  // Configuration (varies by type)
  config      Json
  // TRIGGER: { eventType, conditions }
  // SEND: { channel, templateId }
  // WAIT: { duration, until }
  // CONDITION: { rules, combinator }
  // SPLIT: { variants, weights }
  // UPDATE: { field, value }

  // Branching
  nextStepId      String?     // Default next step
  nextStepYesId   String?     // For CONDITION: if true
  nextStepNoId    String?     // For CONDITION: if false

  createdAt   DateTime        @default(now())
  updatedAt   DateTime        @updatedAt

  // Relations
  enrollmentSteps JourneyEnrollmentStep[]

  @@unique([journeyId, stepNumber])
  @@index([journeyId])
}

/// Customer enrollment in a journey
model JourneyEnrollment {
  id          String          @id @default(uuid())
  tenantId    String
  journeyId   String
  journey     Journey         @relation(fields: [journeyId], references: [id], onDelete: Cascade)
  customerId  String
  customer    CustomerProfile @relation(fields: [customerId], references: [id], onDelete: Cascade)

  status      JourneyEnrollmentStatus @default(ACTIVE)

  // Progress tracking
  currentStepId     String?
  currentStepNumber Int       @default(0)
  waitingUntil      DateTime? // For WAIT steps

  // Timing
  enrolledAt   DateTime       @default(now())
  completedAt  DateTime?
  exitedAt     DateTime?
  exitReason   String?

  // Context
  triggerData  Json?          // Event data that triggered enrollment

  // Relations
  steps        JourneyEnrollmentStep[]

  @@unique([journeyId, customerId])  // Prevent duplicate (unless allowReentry)
  @@index([tenantId, journeyId, status])
  @@index([customerId])
  @@index([waitingUntil])
}

/// Track which steps a customer has completed
model JourneyEnrollmentStep {
  id           String            @id @default(uuid())
  enrollmentId String
  enrollment   JourneyEnrollment @relation(fields: [enrollmentId], references: [id], onDelete: Cascade)
  stepId       String
  step         JourneyStep       @relation(fields: [stepId], references: [id], onDelete: Cascade)

  status       String            @default("PENDING")  // PENDING, IN_PROGRESS, COMPLETED, FAILED, SKIPPED

  enteredAt    DateTime          @default(now())
  completedAt  DateTime?
  result       Json?             // Step-specific result data

  @@unique([enrollmentId, stepId])
  @@index([enrollmentId])
}

// ============================================================================
// SOCIAL MODELS
// ============================================================================

/// OAuth connection to a social platform
model SocialConnection {
  id          String          @id @default(uuid())
  tenantId    String
  clubId      String?

  platform    SocialPlatform
  accountId   String          // Platform's account/page ID
  accountName String          // Display name
  accountType String?         // page, profile, business

  // OAuth tokens (encrypted at rest)
  accessToken   String
  refreshToken  String?
  tokenExpiry   DateTime?

  // Permissions granted
  permissions   String[]      @default([])

  // Status
  isActive      Boolean       @default(true)
  lastUsedAt    DateTime?
  lastErrorAt   DateTime?
  lastError     String?

  createdAt   DateTime        @default(now())
  updatedAt   DateTime        @updatedAt
  connectedBy String

  // Relations
  posts       SocialPost[]

  @@unique([tenantId, platform, accountId])
  @@index([tenantId, platform, isActive])
  @@index([tenantId, clubId])
}

/// Social media post
model SocialPost {
  id          String           @id @default(uuid())
  tenantId    String

  connectionId String
  connection   SocialConnection @relation(fields: [connectionId], references: [id], onDelete: Cascade)

  // Optional campaign link
  campaignId   String?
  campaign     Campaign?        @relation(fields: [campaignId], references: [id])

  // Content
  content      String
  mediaUrls    String[]         @default([])
  linkUrl      String?
  hashtags     String[]         @default([])

  // Schedule & Status
  status       SocialPostStatus @default(DRAFT)
  scheduledAt  DateTime?
  publishedAt  DateTime?

  // Platform response
  platformPostId String?        // ID returned by platform
  platformUrl    String?        // Direct link to post
  error          String?

  // Engagement metrics (synced from platform)
  likes           Int           @default(0)
  comments        Int           @default(0)
  shares          Int           @default(0)
  reach           Int           @default(0)
  impressions     Int           @default(0)
  engagementSyncedAt DateTime?

  createdAt    DateTime         @default(now())
  updatedAt    DateTime         @updatedAt
  createdBy    String

  @@index([tenantId, status])
  @@index([tenantId, scheduledAt])
  @@index([connectionId])
  @@index([campaignId])
}

// ============================================================================
// EVENT PROMOTION MODELS
// ============================================================================

/// Configuration for auto-promoting events/tournaments
model EventPromotion {
  id          String          @id @default(uuid())
  tenantId    String
  clubId      String

  // Source event
  sourceType  EventSourceType
  sourceId    String          // TeeTime.Competition.id or similar

  // Settings
  autoPromote Boolean         @default(true)
  channels    Channel[]

  // Timing
  announceAt      DateTime?   // When to first announce
  reminderDays    Int[]       @default([7, 3, 1])  // Days before event

  // Targeting
  segmentId       String?     // Custom segment (null = all members)
  targetRadius    Int?        // km from club for geo-targeting

  // Content
  emailTemplateId String?
  smsTemplateId   String?
  socialContent   String?     // Custom social post text

  // Status
  isActive        Boolean     @default(true)
  lastPromotedAt  DateTime?

  createdAt   DateTime        @default(now())
  updatedAt   DateTime        @updatedAt
  createdBy   String

  @@unique([tenantId, sourceType, sourceId])
  @@index([tenantId, clubId])
  @@index([tenantId, isActive])
}

// ============================================================================
// SYNC & AUDIT MODELS
// ============================================================================

/// Log of events processed from external services
model SyncLog {
  id          String          @id @default(uuid())
  tenantId    String

  // Event source
  sourceService String        // scl, teetime, messaging
  eventType     String        // Full event type
  eventId       String        // Original event ID
  eventPayload  Json?         // Original payload (for debugging)

  // Processing
  status        SyncStatus    @default(PENDING)
  processedAt   DateTime?
  attempts      Int           @default(0)
  error         String?

  // Result
  customerId    String?       // CustomerProfile created/updated
  activityId    String?       // Activity created

  createdAt   DateTime        @default(now())

  @@unique([sourceService, eventId])
  @@index([tenantId, status])
  @@index([tenantId, sourceService, eventType])
  @@index([createdAt])
}

/// Audit log for CRM operations
model CrmAuditLog {
  id          String          @id @default(uuid())
  tenantId    String

  // Target
  entityType  String          // CustomerProfile, Segment, Campaign, etc.
  entityId    String
  action      String          // CREATE, UPDATE, DELETE, MERGE, etc.

  // Changes
  changes     Json?           // { field: { old, new } }
  metadata    Json?           // Additional context

  // Actor
  performedBy String
  performedAt DateTime        @default(now())

  // Context
  ipAddress   String?
  userAgent   String?

  @@index([tenantId, entityType, entityId])
  @@index([tenantId, performedAt])
  @@index([tenantId, action])
}

/// Potential duplicate profiles flagged for review
model DuplicateCandidate {
  id          String          @id @default(uuid())
  tenantId    String

  // The two profiles
  profileAId  String
  profileBId  String

  // Match details
  matchType   String          // email, phone, name_fuzzy, etc.
  matchScore  Float           // 0-100 confidence
  matchFields Json            // Which fields matched

  // Resolution
  resolution  DuplicateResolution @default(PENDING)
  resolvedBy  String?
  resolvedAt  DateTime?
  notes       String?

  createdAt   DateTime        @default(now())

  @@unique([profileAId, profileBId])
  @@index([tenantId, resolution])
  @@index([tenantId, matchScore])
}

Data Dictionary

CustomerProfile Fields

Field	Type	Description
`id`	UUID	CRM's unique identifier
`tenantId`	String	Multi-tenant isolation key
`authUserId`	UUID	IDP user ID (strongest identity link)
`email`	String	Normalized email (lowercase, trimmed)
`phoneNumber`	String	E.164 format phone
`sclMemberId`	Int	Link to SCL.Member
`teetimePlayerId`	UUID	Link to TeeTime.Player
`messagingContactId`	UUID	Link to Messaging.Contact
`externalIds`	JSON	External system IDs (MCA, GolfRSA, etc.)
`status`	Enum	ACTIVE, INACTIVE, MERGED, DELETED
`lifecycleStage`	Enum	SUBSCRIBER → ADVOCATE progression
`emailOptIn`	Boolean	Synced from Messaging (READ ONLY)
`engagementScore`	Int	0-100 calculated engagement
`churnRiskScore`	Int	0-100 churn probability
`lifetimeValue`	Decimal	Total spend in cents
`tags`	String[]	Quick classification tags

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
`sentCount`	Int	Denormalized stats

Journey Fields

Field	Type	Description
`triggerType`	String	Event type or MANUAL
`triggerConfig`	JSON	Additional trigger conditions
`allowReentry`	Boolean	Can customer re-enter journey
`status`	Enum	DRAFT, ACTIVE, PAUSED, ARCHIVED

JourneyStep Config Examples

SEND Step:

{
  "channel": "EMAIL",
  "templateId": "welcome-email-v2"
}

WAIT Step:

{
  "duration": "3 days",
  "until": null
}

CONDITION Step:

{
  "rules": [
    { "field": "lastEmailOpenAt", "operator": "isNotNull" }
  ],
  "combinator": "AND"
}

SocialConnection Fields

Field	Type	Description
`platform`	Enum	FACEBOOK, INSTAGRAM, TWITTER, etc.
`accountId`	String	Platform's page/account ID
`accessToken`	String	Encrypted OAuth token
`permissions`	Array	Granted OAuth permissions

EventPromotion Fields

Field	Type	Description
`sourceType`	Enum	COMPETITION, CLUB_EVENT, SPECIAL
`sourceId`	String	TeeTime.Competition.id
`autoPromote`	Boolean	Enable auto-promotion
`channels`	Array	Channels to promote on
`reminderDays`	Int[]	Days before event to send reminders

Indexes Strategy

Primary Query Patterns

Query	Index
Find customer by email	`(tenantId, email)` UNIQUE
Find customer by phone	`(tenantId, phoneNumber)` UNIQUE
List active customers	`(tenantId, status)`
Filter by membership	`(tenantId, membershipStatus)`
Sort by engagement	`(tenantId, engagementScore)`
Timeline for customer	`(customerId, occurredAt)`
Activities by type	`(tenantId, type, occurredAt)`
Campaigns by status	`(tenantId, status)`
Scheduled campaigns	`(tenantId, scheduledAt)`
Journeys by trigger	`(tenantId, triggerType)`
Waiting enrollments	`(waitingUntil)`
Social posts scheduled	`(tenantId, scheduledAt)`

GIN Indexes (Arrays/JSON)

Field	Purpose
`CustomerProfile.tags`	Filter by customer tags

Migration Notes

Changes from v1.0

Removed models (owned by Messaging):

MarketingConsent - Use Messaging.ContactPreference
MarketingList / MarketingListMember - Use Segment + Campaign

Removed models (consolidated):

AttributionEvent - Tracked in Activity metadata

Added models:

Campaign - Full campaign management
Journey / JourneyStep / JourneyEnrollment / JourneyEnrollmentStep - Marketing automation
SocialConnection / SocialPost - Social integration
EventPromotion - Auto-promotion config

Initial Migration

cd libs/prisma/crm-client
npx prisma migrate dev --name init

Seed System Segments

Consider seeding common segments:

"All Customers"
"Active Members"
"High Engagement"
"At Risk (Churn)"

Table of Contents​

Overview​

Key Design Decision: Consent Ownership​

Schema Design Principles​

1. Multi-Tenancy​

2. Soft Deletes​

3. Audit Trail​

4. Denormalization for Performance​

5. JSON for Flexibility​

Complete Prisma Schema​

Data Dictionary​

CustomerProfile Fields​

Campaign Fields​

Journey Fields​

JourneyStep Config Examples​

SocialConnection Fields​

EventPromotion Fields​

Indexes Strategy​

Primary Query Patterns​

GIN Indexes (Arrays/JSON)​

Migration Notes​

Changes from v1.0​

Initial Migration​

Seed System Segments​

Table of Contents