Core Models

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

---

CustomerProfile

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

Data Dictionary

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

---

IdentityAlias

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

Use Cases

• Customer has multiple email addresses
• Customer has multiple phone numbers
• Track which is primary
• Track verification status

---

Activity

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

Activity Types by Category

Category	Types
MEMBERSHIP	MEMBER_CREATED, MEMBER_UPDATED, TIER_CHANGED
BOOKING	TEETIME_BOOKED, TEETIME_COMPLETED, TEETIME_CANCELLED
COMMUNICATION	MESSAGE_SENT, MESSAGE_DELIVERED
ENGAGEMENT	EMAIL_OPENED, EMAIL_CLICKED, SMS_REPLIED
FINANCIAL	PAYMENT_RECEIVED, PAYMENT_FAILED
GOLF	HANDICAP_UPDATED, COMPETITION_ENTERED, SCORE_POSTED
MARKETING	CAMPAIGN_SENT, JOURNEY_ENROLLED
SUPPORT	NOTE_ADDED
SOCIAL	SOCIAL_POST_PUBLISHED
SYSTEM	PROFILE_MERGED, PROFILE_IMPORTED

---

CustomerNote

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

Note Types

Type	Use Case
GENERAL	General notes
SUPPORT	Support interactions
COMPLAINT	Customer complaints
FEEDBACK	Customer feedback
FOLLOWUP	Follow-up reminders
INTERNAL	Internal staff notes
MARKETING	Marketing-related notes
SALES	Sales-related notes

---

Related Documents

• Specification: Core Concepts
• API: Customers
• Implementation: Phase 1