Sync & Audit Models

This document covers the sync tracking and audit data models for the CRM & Marketing Platform.

---

SyncLog

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

Sync Status

Status	Description
PENDING	Awaiting processing
PROCESSING	Currently being processed
SUCCESS	Successfully processed
FAILED	Processing failed
SKIPPED	Intentionally skipped

Usage

On event received:

const syncLog = await prisma.syncLog.create({
  data: {
    tenantId: event.tenantId,
    sourceService: 'scl',
    eventType: event.type,
    eventId: event.id,
    eventPayload: event.payload,
    status: 'PENDING',
  }
});

On success:

await prisma.syncLog.update({
  where: { id: syncLog.id },
  data: {
    status: 'SUCCESS',
    processedAt: new Date(),
    customerId: profile.id,
    activityId: activity?.id,
  }
});

On failure:

await prisma.syncLog.update({
  where: { id: syncLog.id },
  data: {
    status: 'FAILED',
    attempts: { increment: 1 },
    error: error.message,
  }
});

Idempotency

The unique constraint on (sourceService, eventId) ensures events are not processed twice:

const existing = await prisma.syncLog.findUnique({
  where: {
    sourceService_eventId: {
      sourceService: event.source,
      eventId: event.id,
    }
  }
});

if (existing?.status === 'SUCCESS') {
  return; // Already processed
}

---

CrmAuditLog

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

Audit Actions

Action	Description
CREATE	Entity created
UPDATE	Entity modified
DELETE	Entity deleted (soft)
MERGE	Profiles merged
IMPORT	Data imported
EXPORT	Data exported
ACCESS	Data accessed (for sensitive ops)

Changes Format

{
  "email": {
    "old": "john@example.com",
    "new": "john.smith@example.com"
  },
  "tags": {
    "old": ["vip"],
    "new": ["vip", "golf-enthusiast"]
  }
}

Usage

await prisma.crmAuditLog.create({
  data: {
    tenantId,
    entityType: 'CustomerProfile',
    entityId: profile.id,
    action: 'UPDATE',
    changes: {
      email: { old: oldEmail, new: newEmail }
    },
    performedBy: userId,
    ipAddress: req.ip,
    userAgent: req.headers['user-agent'],
  }
});

---

DuplicateCandidate

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

Match Types

Type	Description	Score Range
email	Same email	95-100
phone	Same phone	85-95
email_phone	Both match	99-100
name_fuzzy	Similar names	60-80
name_email_domain	Name + same domain	70-85

Match Fields Format

{
  "email": {
    "profileA": "john@example.com",
    "profileB": "john@example.com",
    "match": "exact"
  },
  "firstName": {
    "profileA": "John",
    "profileB": "Jon",
    "match": "fuzzy",
    "similarity": 0.85
  }
}

Resolution Flow

PENDING → MERGED (profiles combined)
        → NOT_DUPLICATE (false positive)
        → IGNORED (defer decision)

Detection

// After creating/updating a profile
async detectDuplicates(profileId: string): Promise<void> {
  const profile = await this.getProfile(profileId);

  // Find potential matches
  const candidates = await this.findSimilarProfiles(profile);

  for (const candidate of candidates) {
    const matchResult = this.calculateMatchScore(profile, candidate);

    if (matchResult.score >= 60) {
      await prisma.duplicateCandidate.create({
        data: {
          tenantId: profile.tenantId,
          profileAId: profile.id,
          profileBId: candidate.id,
          matchType: matchResult.type,
          matchScore: matchResult.score,
          matchFields: matchResult.fields,
        }
      });
    }
  }
}

---

Index Strategy

Query Pattern	Index
Sync by status	(tenantId, status)
Sync by service	(tenantId, sourceService, eventType)
Event deduplication	(sourceService, eventId) UNIQUE
Recent sync logs	(createdAt)
Audit by entity	(tenantId, entityType, entityId)
Audit by time	(tenantId, performedAt)
Audit by action	(tenantId, action)
Pending duplicates	(tenantId, resolution)
High-confidence duplicates	(tenantId, matchScore)

---

Retention Policies

Table	Retention	Reason
SyncLog	90 days	Debugging, idempotency
CrmAuditLog	7 years	Compliance, GDPR
DuplicateCandidate	Until resolved	Manual review

Cleanup Job

// Run daily
async cleanupOldSyncLogs(): Promise<void> {
  const cutoff = subDays(new Date(), 90);

  await prisma.syncLog.deleteMany({
    where: {
      createdAt: { lt: cutoff },
      status: { in: ['SUCCESS', 'SKIPPED'] }
    }
  });
}

---

Related Documents

• Specification: Technical
• Events: Overview
• Implementation: Phase 2