Schema Management - ThriveSend B2B2G

Schema Management - ThriveSend B2B2G

Last Updated: January 26, 2026 Status: Production Developer Guide: Database Layer

---

Overview

This guide covers the Prisma schema design for ThriveSend B2B2G, including the 28-model database architecture, B2B2G-specific patterns, relationships, and schema management best practices.

Schema File: prisma/schema.prisma Models: 28 (Core: 4, Content: 5, POPIA: 7, Analytics: 3, Billing: 7, Config: 2)

---

Schema Architecture

B2B2G Multi-Tenant Structure

graph TD
    Org[Organization] --> Client[Client]
    Org --> User[OrganizationUser]
    Org --> Campaign[Campaign]
    Org --> Content[Content]

    Client --> Campaign
    Client --> Content
    Client --> Analytics[Analytics]

    Campaign --> Content
    Campaign --> Analytics

    User --> ClientAccess[ClientAccess]
    User --> ContentApproval[ContentApproval]

    Org --> AuditLog[AuditLog]
    Org --> PopiaConsent[PopiaConsentRecord]
    Org --> Invitation[Invitation]

Core Entity Relationships

Parent	Child	Relationship Type	On Delete
Organization	Client	One-to-Many	Cascade
Organization	OrganizationUser	One-to-Many	Cascade
Organization	Campaign	One-to-Many	Cascade
Organization	Content	One-to-Many	Cascade
Organization	AuditLog	One-to-Many	Cascade
Client	Campaign	One-to-Many	SetNull
Campaign	Content	One-to-Many	SetNull
OrganizationUser	ClientAccess	One-to-Many	Cascade

---

Core B2B2G Models

Organization (Service Provider)

The top-level entity representing a digital marketing agency or service provider.

model Organization {
  id                  String           @id @default(cuid())
  clerkOrganizationId String           @unique // Clerk organization ID
  name                String
  slug                String           @unique
  type                OrganizationType
  country             String           @default("ZA") // Data locality for POPIA
  dataResidency       String           @default("ZA") // Enforce SA data residency
  subscription        SubscriptionPlan
  settings            Json? // Organization-level settings
  createdAt           DateTime         @default(now())
  updatedAt           DateTime         @updatedAt

  // Relationships
  clients          Client[]
  users            OrganizationUser[]
  campaigns        Campaign[]
  content          Content[]
  auditLogs        AuditLog[]
  invitations      Invitation[]
  popiaConsents    PopiaConsentRecord[]

  @@map("organizations")
}

Key Fields:

• clerkOrganizationId: Links to Clerk authentication
• dataResidency: Enforces South African data residency (POPIA)
• subscription: Subscription plan (FREE, BASIC, PRO, ENTERPRISE)

Usage Example:

const organization = await prisma.organization.create({
  data: {
    clerkOrganizationId: 'org_clerk123',
    name: 'Marketing Solutions SA',
    slug: 'marketing-solutions-sa',
    type: 'SERVICE_PROVIDER',
    subscription: 'PRO',
    dataResidency: 'ZA'
  }
})

Client (Government/Business)

Represents clients served by the organization - either government entities or businesses.

model Client {
  id             String       @id @default(cuid())
  organizationId String // Service Provider Organization ID
  name           String
  slug           String
  type           ClientType
  status         ClientStatus @default(ACTIVE)

  // Government-specific fields for SA compliance
  governmentLevel  GovernmentLevel?
  municipalityCode String? // SA municipal codes
  department       String? // Government department

  // Political organization-specific fields for IEC compliance
  politicalLevel        PoliticalLevel?
  iecRegistrationNumber String? // IEC registration number for political parties
  electoralConstituency String? // Electoral constituency/ward
  partyAffiliation      String? // Political party name/affiliation

  // Contact Information
  contactEmail String // Required for POPIA compliance
  contactPhone String?
  address      Json?

  // Data Locality & Compliance
  dataResidency String    @default("ZA")
  popiaConsent  Boolean   @default(false)
  consentDate   DateTime?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  // Relationships
  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  campaigns    Campaign[]
  content      Content[]
  analytics    Analytics[]

  @@unique([organizationId, slug])
  @@map("clients")
}

Client Types (Enum):

enum ClientType {
  // Government
  LOCAL_MUNICIPALITY
  PROVINCIAL_GOVERNMENT
  NATIONAL_GOVERNMENT
  STATE_OWNED_ENTERPRISE

  // Business
  SMALL_BUSINESS
  MEDIUM_BUSINESS
  LARGE_CORPORATION
  STARTUP
  NON_PROFIT

  // Political
  POLITICAL_PARTY
  INDEPENDENT_CANDIDATE
}

Usage Example:

// Government client
const govClient = await prisma.client.create({
  data: {
    organizationId: 'org_123',
    name: 'City of Cape Town',
    slug: 'city-of-cape-town',
    type: 'LOCAL_MUNICIPALITY',
    governmentLevel: 'MUNICIPAL',
    municipalityCode: 'CPT',
    contactEmail: 'info@capetown.gov.za',
    popiaConsent: true,
    dataResidency: 'ZA'
  }
})

// Business client
const businessClient = await prisma.client.create({
  data: {
    organizationId: 'org_123',
    name: 'TechCorp Solutions',
    slug: 'techcorp-solutions',
    type: 'LARGE_CORPORATION',
    contactEmail: 'info@techcorp.co.za',
    popiaConsent: true,
    dataResidency: 'ZA'
  }
})

OrganizationUser (Multi-Role Users)

Users within an organization with role-based access and security clearance.

model OrganizationUser {
  id             String       @id @default(cuid())
  clerkUserId    String // Clerk user ID
  organizationId String
  email          String
  firstName      String?
  lastName       String?
  role           UserRole
  permissions    Permission[]
  status         UserStatus   @default(ACTIVE)

  // SA Government Clearance (if applicable)
  securityClearance SecurityClearanceLevel?
  clearanceExpiry   DateTime?

  // Client Access Control
  clientAccess ClientAccess[]

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  auditLogs    AuditLog[]
  contentApprovals ContentApproval[]

  @@unique([clerkUserId, organizationId])
  @@map("organization_users")
}

User Roles (Enum):

enum UserRole {
  SERVICE_PROVIDER_ADMIN
  ACCOUNT_MANAGER
  CONTENT_CREATOR
  COMPLIANCE_OFFICER
  GOVERNMENT_LIAISON
  ANALYST
  VIEWER
}

Security Clearance (Enum):

enum SecurityClearanceLevel {
  BASIC      // Public information access
  ENHANCED   // Sensitive information access
  CONFIDENTIAL // Classified information access
}

Usage Example:

const user = await prisma.organizationUser.create({
  data: {
    clerkUserId: 'user_clerk123',
    organizationId: 'org_123',
    email: 'john@agency.co.za',
    firstName: 'John',
    lastName: 'Smith',
    role: 'ACCOUNT_MANAGER',
    permissions: ['VIEW_CLIENTS', 'EDIT_CAMPAIGNS'],
    securityClearance: 'BASIC',
    clearanceExpiry: new Date('2027-12-31')
  }
})

ClientAccess (Access Control)

Manages which users can access specific clients.

model ClientAccess {
  id          String             @id @default(cuid())
  userId      String
  clientId    String
  accessLevel ClientAccessLevel
  permissions ClientPermission[]
  grantedAt   DateTime           @default(now())
  grantedBy   String?
  expiresAt   DateTime?

  user OrganizationUser @relation(fields: [userId], references: [id], onDelete: Cascade)

  @@unique([userId, clientId])
  @@map("client_access")
}

Usage Example:

// Grant user access to specific client
await prisma.clientAccess.create({
  data: {
    userId: 'user_123',
    clientId: 'client_456',
    accessLevel: 'FULL_ACCESS',
    permissions: ['VIEW', 'EDIT', 'PUBLISH'],
    grantedBy: 'admin_user_789'
  }
})

---

Content & Campaign Models

Campaign

Marketing campaigns for government or business clients.

model Campaign {
  id             String         @id @default(cuid())
  organizationId String
  clientId       String?
  name           String
  slug           String
  description    String?
  type           CampaignType
  status         CampaignStatus @default(DRAFT)

  // B2B2G Sector Classification
  sector            String  @default("BUSINESS") // GOVERNMENT or BUSINESS
  governmentLevel   String? // MUNICIPAL, PROVINCIAL, NATIONAL
  securityClearance String? // PUBLIC, CONFIDENTIAL, SECRET

  // Campaign Configuration
  objectives     String[] // Array of campaign objectives
  targetAudience Json?
  budget         Float?
  spent          Float    @default(0)

  // Timeline
  startDate   DateTime?
  endDate     DateTime?
  launchedAt  DateTime?
  completedAt DateTime?

  // Performance Metrics (cached from Analytics)
  reach           Int   @default(0)
  engagement      Float @default(0)
  conversions     Int   @default(0)
  complianceScore Int   @default(0)

  // Government Approval Workflow
  approvalStatus   ApprovalStatus @default(PENDING)
  approvalWorkflow Json?
  approvedBy       String?
  approvedAt       DateTime?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  client       Client?      @relation(fields: [clientId], references: [id], onDelete: SetNull)
  content      Content[]
  analytics    Analytics[]

  @@unique([organizationId, slug])
  @@index([sector])
  @@index([status])
  @@map("campaigns")
}

Campaign Types (Enum):

enum CampaignType {
  PUBLIC_AWARENESS
  LEAD_GENERATION
  BRAND_AWARENESS
  PRODUCT_LAUNCH
  EVENT_PROMOTION
  CRISIS_COMMUNICATION
  TENDER_PROMOTION
}

Usage Example:

const campaign = await prisma.campaign.create({
  data: {
    organizationId: 'org_123',
    clientId: 'client_456',
    name: 'Water Conservation Campaign',
    slug: 'water-conservation-2026',
    type: 'PUBLIC_AWARENESS',
    status: 'ACTIVE',
    sector: 'GOVERNMENT',
    governmentLevel: 'MUNICIPAL',
    objectives: ['Reduce water consumption by 20%', 'Educate 100,000 citizens'],
    budget: 50000,
    startDate: new Date('2026-02-01'),
    endDate: new Date('2026-03-31')
  }
})

Content

Content entities with approval workflows and versioning.

model Content {
  id             String        @id @default(cuid())
  organizationId String
  clientId       String?
  campaignId     String?
  title          String
  description    String?
  type           ContentType
  format         ContentFormat
  status         ContentStatus @default(DRAFT)

  // Content Data
  content  Json // Rich content structure
  metadata Json?
  media    MediaAsset[]

  // Publishing
  publishedAt  DateTime?
  scheduledFor DateTime?
  platforms    Platform[]

  // Government Approval Workflow
  approvalWorkflow Json?
  approvalStatus   ApprovalStatus @default(PENDING)
  reviewNotes      String?

  createdAt DateTime @default(now())
  updatedAt DateTime @updatedAt

  organization Organization      @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  client       Client?           @relation(fields: [clientId], references: [id], onDelete: SetNull)
  campaign     Campaign?         @relation(fields: [campaignId], references: [id], onDelete: SetNull)
  approvals    ContentApproval[]
  versions     ContentVersion[]

  @@map("content")
}

Content Types (Enum):

enum ContentType {
  ARTICLE
  SOCIAL_POST
  EMAIL
  PRESS_RELEASE
  NEWSLETTER
  VIDEO
  INFOGRAPHIC
  BANNER
}

---

POPIA Compliance Models

AuditLog

Comprehensive audit logging for POPIA compliance.

model AuditLog {
  id             String   @id @default(cuid())
  organizationId String?
  userId         String?
  action         String // CREATE, READ, UPDATE, DELETE
  resourceType   String // Client, Campaign, Content, etc.
  resourceId     String?
  details        Json? // Operation details
  ipAddress      String?
  userAgent      String?
  timestamp      DateTime @default(now())

  organization Organization? @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@index([organizationId])
  @@index([userId])
  @@index([timestamp])
  @@index([action])
  @@map("audit_logs")
}

Usage Example:

await prisma.auditLog.create({
  data: {
    organizationId: 'org_123',
    userId: 'user_456',
    action: 'CLIENT_CREATED',
    resourceType: 'Client',
    resourceId: 'client_789',
    details: {
      clientName: 'City of Cape Town',
      clientType: 'LOCAL_MUNICIPALITY'
    },
    ipAddress: '41.185.23.45',
    userAgent: 'Mozilla/5.0...',
    timestamp: new Date()
  }
})

PopiaConsentRecord

Tracks POPIA consent for data processing.

model PopiaConsentRecord {
  id                    String    @id @default(cuid())
  invitationId          String?
  userId                String?
  email                 String
  organizationId        String
  consentType           String    @default('INVITATION_ACCEPTANCE')
  dataProcessingConsent Boolean
  marketingConsent      Boolean   @default(false)
  analyticsConsent      Boolean   @default(false)
  consentVersion        String    @default('1.0')
  consentLanguage       String    @default('en')
  ipAddress             String
  userAgent             String
  consentTimestamp      DateTime
  withdrawnAt           DateTime?
  withdrawalReason      String?
  auditMetadata         Json      @default({})
  createdAt             DateTime  @default(now())
  updatedAt             DateTime  @updatedAt

  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  invitation   Invitation?  @relation(fields: [invitationId], references: [id], onDelete: SetNull)

  @@unique([invitationId])
  @@index([email])
  @@index([organizationId])
  @@map("popia_consent_records")
}

Invitation

POPIA-compliant invitation system with consent tracking.

model Invitation {
  id                 String             @id @default(cuid())
  token              String             @unique
  email              String
  organizationId     String
  role               InvitationRole
  securityClearance  SecurityClearanceLevel
  clientAccess       String[]
  invitedBy          String
  status             InvitationStatus   @default(PENDING)
  emailSentAt        DateTime?
  emailOpenedAt      DateTime?
  acceptedAt         DateTime?
  expiredAt          DateTime
  popiaConsentGiven  Boolean            @default(false)
  consentMetadata    Json?
  auditMetadata      Json               @default({})
  customMessage      String?
  ipAddress          String?
  userAgent          String?
  createdAt          DateTime           @default(now())
  updatedAt          DateTime           @updatedAt

  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)
  consent      PopiaConsentRecord?

  @@index([token])
  @@index([email])
  @@index([organizationId])
  @@index([status])
  @@map("invitations")
}

---

Schema Best Practices

Multi-Tenant Isolation

Pattern: Always include organizationId in core models.

model Campaign {
  id             String @id @default(cuid())
  organizationId String // REQUIRED for multi-tenancy

  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@unique([organizationId, slug]) // Scoped uniqueness
}

POPIA Compliance Fields

Pattern: Include data residency and consent fields.

model Client {
  // Data Locality & Compliance
  dataResidency String    @default("ZA") // South Africa
  popiaConsent  Boolean   @default(false)
  consentDate   DateTime?
}

Indexes for Performance

Pattern: Index frequently queried fields.

model Client {
  id             String @id @default(cuid())
  organizationId String
  contactEmail   String

  @@index([organizationId]) // Multi-tenant queries
  @@index([contactEmail]) // Lookups
  @@unique([organizationId, slug]) // Scoped uniqueness
}

Cascade Deletes

Pattern: Use appropriate cascade behavior.

model Client {
  organization Organization @relation(
    fields: [organizationId],
    references: [id],
    onDelete: Cascade // Delete clients when org deleted
  )
}

model Campaign {
  client Client? @relation(
    fields: [clientId],
    references: [id],
    onDelete: SetNull // Keep campaign if client deleted
  )
}

Enums for Type Safety

Pattern: Use enums instead of strings for fixed sets.

enum UserRole {
  SERVICE_PROVIDER_ADMIN
  ACCOUNT_MANAGER
  CONTENT_CREATOR
}

model OrganizationUser {
  role UserRole // Type-safe, validated
}

---

Schema Modification Workflow

Adding a New Field

1. Edit Schema:

model Client {
  // Existing fields...

  // NEW: Add industry field
  industry String?
}

2. Create Migration:

npx prisma migrate dev --name add_industry_to_clients

3. Update TypeScript Types:

npx prisma generate

4. Update Application Code:

const client = await prisma.client.create({
  data: {
    // ...existing fields
    industry: 'Technology' // New field
  }
})

Adding a New Model

1. Define Model in Schema:

model EmailTemplate {
  id             String   @id @default(cuid())
  organizationId String
  name           String
  subject        String
  body           String
  createdAt      DateTime @default(now())
  updatedAt      DateTime @updatedAt

  organization Organization @relation(fields: [organizationId], references: [id], onDelete: Cascade)

  @@map("email_templates")
}

2. Update Organization Model:

model Organization {
  // ...existing fields
  emailTemplates EmailTemplate[]
}

3. Create Migration:

npx prisma migrate dev --name add_email_templates

Changing Field Type

model Campaign {
  // Before
  budget Int?

  // After
  budget Float? // Changed to support decimals
}

npx prisma migrate dev --name change_budget_to_float

Warning: Type changes may require data transformation in migration SQL.

---

Schema Documentation

Model Comments

/// Service Provider Organization (Digital Agency)
/// Represents marketing agencies serving B2B2G clients
model Organization {
  id   String @id @default(cuid())

  /// Clerk organization ID for authentication integration
  clerkOrganizationId String @unique

  /// Legal business name
  name String

  /// URL-friendly identifier
  slug String @unique

  /// Data residency for POPIA compliance (default: South Africa)
  dataResidency String @default("ZA")
}

Field Comments

model Client {
  /// SA municipal code (e.g., "CPT" for Cape Town)
  municipalityCode String?

  /// IEC registration number for political parties
  iecRegistrationNumber String?

  /// POPIA consent for data processing
  popiaConsent Boolean @default(false)
}

---

Common Schema Patterns

Soft Delete Pattern

model Client {
  id        String       @id @default(cuid())
  status    ClientStatus @default(ACTIVE)
  deletedAt DateTime?

  // Query active clients only
  // where: { deletedAt: null }
}

Versioning Pattern

model ContentVersion {
  id        String   @id @default(cuid())
  contentId String
  version   Int
  content   Json
  createdBy String
  createdAt DateTime @default(now())

  @@unique([contentId, version])
  @@index([contentId])
}

JSON Metadata Pattern

model Campaign {
  // Flexible metadata storage
  settings  Json? // { theme: "dark", customFields: {...} }
  targeting Json? // { demographics: {...}, segments: [...] }
}

---

Next Steps

• ← Querying Data - Query patterns and best practices
• Prisma Setup - Database configuration
• Migrations - Migration creation and running

---

Related Documentation:

• Service Architecture - Using Prisma in services
• POPIA Compliance - Data protection requirements

---

Last Updated: January 26, 2026