Segment Models
This document covers the segmentation data models for the CRM & Marketing Platform.
CustomerSegment
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 Types
| Type | Description | Use Case |
|---|---|---|
| STATIC | Manual membership | VIP lists, board members |
| DYNAMIC | Rule-based, auto-computed | Active golfers, high engagement |
| SMART | ML-powered | Churn prediction, recommendations |
| IMPORT | External import | CSV uploads |
Criteria Format
{
"combinator": "AND",
"rules": [
{
"field": "membershipStatus",
"operator": "eq",
"value": "ACTIVE"
},
{
"field": "engagementScore",
"operator": "gte",
"value": 70
},
{
"field": "emailOptIn",
"operator": "eq",
"value": true
}
]
}
Nested Groups
{
"combinator": "AND",
"rules": [
{
"field": "membershipStatus",
"operator": "eq",
"value": "ACTIVE"
},
{
"combinator": "OR",
"rules": [
{ "field": "membershipTier", "operator": "eq", "value": "Gold" },
{ "field": "membershipTier", "operator": "eq", "value": "Platinum" }
]
}
]
}
CustomerSegmentMembership
Segment membership junction table.
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])
}
Usage
Static Segments: Members are manually added/removed.
await prisma.customerSegmentMembership.create({
data: {
tenantId: "tenant_123",
segmentId: "vip-segment",
customerId: "customer_456",
addedBy: "admin_user",
}
});
Dynamic Segments: Members are computed during refresh.
// On segment refresh
await prisma.customerSegmentMembership.deleteMany({
where: { segmentId }
});
await prisma.customerSegmentMembership.createMany({
data: matchingProfiles.map(p => ({
tenantId,
segmentId,
customerId: p.id,
matchedAt: new Date(),
}))
});
System Segments
Pre-defined segments managed by the system.
| Name | Type | Criteria |
|---|---|---|
| All Customers | STATIC | All active profiles |
| Active Members | DYNAMIC | membershipStatus = "ACTIVE" |
| High Engagement | DYNAMIC | engagementScore >= 70 |
| At Risk | DYNAMIC | churnRiskScore >= 70 |
| Email Subscribers | DYNAMIC | emailOptIn = true |
| SMS Subscribers | DYNAMIC | smsOptIn = true |
| New This Month | DYNAMIC | createdAt >= startOfMonth |
| Inactive 30d | DYNAMIC | lastActivityAt < 30 days ago |
// System segments have isSystem = true
// Cannot be deleted by users
// Criteria managed by platform
Index Strategy
| Query Pattern | Index |
|---|---|
| List segments by tenant | (tenantId, type, isActive) |
| Filter by club | (tenantId, clubId) |
| Find segment by name | (tenantId, name) UNIQUE |
| List members of segment | (segmentId, customerId) UNIQUE |
| Find segments for customer | (customerId) |