Social Models

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

---

SocialConnection

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

Platforms

Platform	Account Types	Permissions
FACEBOOK	page	pages_manage_posts, pages_read_engagement
INSTAGRAM	business	instagram_basic, instagram_content_publish
TWITTER	profile	tweet.read, tweet.write
LINKEDIN	company	w_member_social (future)

Token Security

• accessToken - Encrypted with AES-256
• refreshToken - Stored separately, encrypted
• Never exposed in API responses
• Decrypted only at publish time

---

SocialPost

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

Post Status Flow

DRAFT → SCHEDULED → PUBLISHING → PUBLISHED
  ↓        ↓            ↓
 Edit   Cancel      (auto)     → Sync metrics

                   FAILED ←── On error

Content Guidelines

Platform	Max Length	Media
Facebook	63,206 chars	Images, Video
Instagram	2,200 chars	Required image/video
Twitter	280 chars	Optional images

---

EventPromotion

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

Event Source Types

Type	Source	Trigger Event
COMPETITION	TeeTime	competition.published
CLUB_EVENT	Club Events	club_event.published
SPECIAL	Manual	Manual creation

Reminder Schedule

{
  "reminderDays": [7, 3, 1]
}

Creates reminders:

• 7 days before event
• 3 days before event
• 1 day before event

---

Data Dictionary

SocialConnection Fields

Field	Type	Description
platform	Enum	FACEBOOK, INSTAGRAM, TWITTER, etc.
accountId	String	Platform's page/account ID
accessToken	String	Encrypted OAuth token
refreshToken	String	Encrypted refresh token
tokenExpiry	DateTime	When token expires
permissions	Array	Granted OAuth permissions
isActive	Boolean	Connection usable

SocialPost Fields

Field	Type	Description
content	String	Post text
mediaUrls	Array	Image/video URLs
status	Enum	DRAFT → PUBLISHED
platformPostId	String	ID from platform after publish
likes	Int	Synced engagement

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

---

Index Strategy

Query Pattern	Index
List connections by platform	(tenantId, platform, isActive)
Filter connections by club	(tenantId, clubId)
List posts by status	(tenantId, status)
Find scheduled posts	(tenantId, scheduledAt)
Posts for connection	(connectionId)
Posts for campaign	(campaignId)
Active promotions	(tenantId, isActive)
Promotions by club	(tenantId, clubId)

---

Related Documents

• Specification: Social Integration
• Specification: Event Promotion
• API: Social
• Implementation: Phase 6