Data Model

Prisma schema design and database structure.

Schema Configuration

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

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

Core Models

CustomerProfile

The central entity representing a single customer.

Field	Type	Description
id	UUID	Primary key
tenantId	String	Multi-tenant isolation
authUserId	String?	IDP user ID
email	String?	Normalized email
phoneNumber	String?	E.164 format
sclMemberId	Int?	SCL Member ID
teetimePlayerId	String?	TeeTime Player ID
messagingContactId	String?	Messaging Contact ID
status	Enum	ACTIVE, INACTIVE, MERGED
lifecycleStage	Enum	SUBSCRIBER → ADVOCATE
engagementScore	Int?	0-100
churnRiskScore	Int?	0-100

Activity

Timeline events for a customer.

Field	Type	Description
id	UUID	Primary key
customerId	UUID	Foreign key
type	String	Event type
category	Enum	MEMBERSHIP, BOOKING, etc.
title	String	Human-readable
sourceService	String	scl, teetime, messaging
occurredAt	DateTime	When it happened

CustomerSegment

Segment definitions.

Field	Type	Description
id	UUID	Primary key
tenantId	String	Multi-tenant
name	String	Display name
type	Enum	STATIC, DYNAMIC, SMART
rules	JSON	Rule definition
refreshFrequency	Enum	MANUAL, HOURLY, DAILY
memberCount	Int	Denormalized count

Campaign Models

Campaign

Field	Type	Description
id	UUID	Primary key
tenantId	String	Multi-tenant
name	String	Display name
type	Enum	ONE_TIME, RECURRING, etc.
status	Enum	DRAFT, ACTIVE, etc.
segmentId	UUID?	Target segment
scheduledAt	DateTime?	When to send

Journey

Field	Type	Description
id	UUID	Primary key
name	String	Display name
status	Enum	DRAFT, ACTIVE, PAUSED
triggerType	String	Event type
triggerConditions	JSON	Entry conditions

Social Models

SocialConnection

Field	Type	Description
id	UUID	Primary key
tenantId	String	Multi-tenant
platform	Enum	FACEBOOK, INSTAGRAM, TWITTER
accountName	String	Page/account name
accessToken	String	Encrypted token
tokenExpiresAt	DateTime?	Token expiry

SocialPost

Field	Type	Description
id	UUID	Primary key
connectionId	UUID	Which account
content	String	Post text
status	Enum	DRAFT, SCHEDULED, PUBLISHED
scheduledAt	DateTime?	When to publish
platformPostId	String?	ID from platform

Design Principles

1. Multi-Tenancy

All tables include tenantId. Queries must filter by tenant.

2. Soft Deletes

Use deletedAt for reversible deletions.

3. Audit Trail

Major tables have createdAt, updatedAt, createdBy, updatedBy.

4. Denormalization

Frequently queried data stored on CustomerProfile:

• emailOptIn, smsOptIn (from Messaging)
• handicap, bookingCount30d (from TeeTime)
• membershipTier, membershipStatus (from SCL)

5. JSON for Flexibility

Use JSON for evolving schemas:

• Segment rules
• Journey step config
• External IDs

Index Strategy

Query Pattern	Index
Find by email	(tenantId, email) UNIQUE
Find by phone	(tenantId, phoneNumber)
Filter by status	(tenantId, status)
Timeline query	(customerId, occurredAt)
Scheduled campaigns	(tenantId, scheduledAt)

Migrations

# Create migration
cd libs/prisma/crm-client
npx prisma migrate dev --name <name>

# Deploy to production
npx prisma migrate deploy

# Generate client
npx prisma generate