Database Schema - ThriveSend B2B2G

Database Schema - ThriveSend B2B2G

Version: 0.1.4 Last Updated: September 25, 2025 Status: Production ORM: Prisma Database: PostgreSQL 16 Total Models: 28

---

Overview

The ThriveSend B2B2G database schema is designed for multi-tenant B2B2G operations with complete data isolation, POPIA compliance, and government-grade security. The schema uses Prisma ORM with PostgreSQL 16 and implements 28 interconnected models.

---

Entity Relationship Diagram (ERD)

Core B2B2G Architecture

erDiagram
    Organization ||--o{ OrganizationUser : "has many"
    Organization ||--o{ Client : "serves"
    Organization ||--o{ Campaign : "creates"
    Organization ||--o{ Content : "owns"
    Organization ||--o{ Template : "uses"
    Organization ||--o{ Project : "manages"
    Organization ||--o{ Invitation : "sends"
    Organization ||--o{ AuditLog : "generates"
    Organization ||--o{ Subscription : "has"
    Organization ||--o{ ApiKey : "owns"
    Organization ||--o{ Webhook : "configures"
    Organization ||--o{ ComplianceConfiguration : "configures"
    Organization ||--o{ SecurityConfiguration : "configures"

    Client ||--o{ ClientAccess : "grants"
    Client ||--o{ Campaign : "receives"
    Client ||--o{ Project : "participates"
    Client ||--o{ Analytics : "generates"

    OrganizationUser ||--o{ ClientAccess : "has access"
    OrganizationUser ||--o{ Campaign : "manages"
    OrganizationUser ||--o{ Content : "creates"
    OrganizationUser ||--o{ ContentApproval : "approves"
    OrganizationUser ||--o{ AuditLog : "performs actions"
    OrganizationUser ||--o{ Invitation : "receives"

    Campaign ||--o{ Content : "contains"
    Campaign ||--o{ Analytics : "tracks"

    Content ||--o{ ContentVersion : "versioned"
    Content ||--o{ ContentApproval : "requires approval"
    Content ||--o{ MediaAsset : "includes"
    Content ||--o{ EmailTracking : "tracks delivery"

    Subscription ||--o{ Invoice : "generates"

    Webhook ||--o{ WebhookDelivery : "delivers"

    Organization ||--o{ DataProcessingRecord : "maintains"
    Organization ||--o{ PopiaConsentRecord : "tracks"
    Organization ||--o{ DataSubjectRequest : "handles"
    Organization ||--o{ IpWhitelist : "maintains"
    Organization ||--o{ NotificationPreferences : "configures"

    Invitation ||--o{ InvitationAuditLog : "logs"

    Organization {
        string id PK "Clerk org ID"
        string name
        string slug "Unique URL slug"
        enum serviceType "B2B, B2G, B2B2G"
        jsonb settings
        datetime createdAt
        datetime updatedAt
    }

    Client {
        string id PK
        string organizationId FK
        string name
        enum sector "GOVERNMENT, BUSINESS"
        enum clientType "SME, LARGE_CORP, etc"
        enum securityClearance "BASIC, ENHANCED, CONFIDENTIAL"
        jsonb contactInfo
        datetime createdAt
    }

    OrganizationUser {
        string id PK
        string clerkUserId "Clerk user ID"
        string organizationId FK
        enum role "SERVICE_PROVIDER_ADMIN, etc"
        enum securityClearance
        jsonb permissions
        datetime createdAt
    }

    ClientAccess {
        string id PK
        string userId FK
        string clientId FK
        enum accessLevel "READ, WRITE, ADMIN"
        datetime grantedAt
    }

    Campaign {
        string id PK
        string organizationId FK
        string clientId FK
        string name
        enum status "DRAFT, ACTIVE, COMPLETED"
        enum campaignType
        datetime startDate
        datetime endDate
        jsonb targeting
        datetime createdAt
    }

    Content {
        string id PK
        string organizationId FK
        string campaignId FK
        string title
        text body
        enum status "DRAFT, APPROVED, PUBLISHED"
        enum dataClassification "PUBLIC, CONFIDENTIAL"
        int version
        datetime createdAt
    }

    ContentVersion {
        string id PK
        string contentId FK
        int versionNumber
        text body
        string createdBy
        datetime createdAt
    }

    ContentApproval {
        string id PK
        string contentId FK
        string approverId FK
        enum status "PENDING, APPROVED, REJECTED"
        text comments
        datetime approvedAt
    }

    MediaAsset {
        string id PK
        string organizationId FK
        string contentId FK
        string fileName
        string fileUrl
        string mimeType
        int fileSize
        enum classification
        datetime uploadedAt
    }

    Analytics {
        string id PK
        string organizationId FK
        string campaignId FK
        string clientId FK
        jsonb metrics
        date reportDate
        datetime createdAt
    }

    Template {
        string id PK
        string organizationId FK
        string name
        enum category "EMAIL, SMS, SOCIAL"
        text content
        jsonb variables
        datetime createdAt
    }

    Project {
        string id PK
        string organizationId FK
        string clientId FK
        string name
        enum status "ACTIVE, COMPLETED"
        date startDate
        date endDate
        datetime createdAt
    }

    AuditLog {
        string id PK
        string organizationId FK
        string userId FK
        string action
        string resource
        jsonb details
        string ipAddress
        datetime timestamp
    }

    DataProcessingRecord {
        string id PK
        string organizationId FK
        string dataSubject
        string processingPurpose
        string legalBasis
        date retentionDate
        datetime createdAt
    }

    Invitation {
        string id PK
        string organizationId FK
        string email
        enum role
        string token "Secure invite token"
        enum status "PENDING, ACCEPTED, EXPIRED"
        datetime expiresAt
        datetime createdAt
    }

    InvitationAuditLog {
        string id PK
        string invitationId FK
        string action
        jsonb details
        datetime timestamp
    }

    PopiaConsentRecord {
        string id PK
        string organizationId FK
        string dataSubject
        string consentType
        boolean consentGiven
        datetime consentDate
        datetime expiryDate
    }

    DataSubjectRequest {
        string id PK
        string organizationId FK
        string requester
        enum requestType "ACCESS, CORRECTION, DELETION"
        enum status "PENDING, COMPLETED"
        datetime requestDate
        datetime completedDate
    }

    EmailTracking {
        string id PK
        string contentId FK
        string recipientEmail
        enum status "SENT, DELIVERED, OPENED, CLICKED"
        datetime sentAt
        datetime deliveredAt
    }

    Subscription {
        string id PK
        string organizationId FK
        enum plan "FREE, PROFESSIONAL, ENTERPRISE"
        enum status "ACTIVE, CANCELLED"
        date startDate
        date endDate
        decimal amount
    }

    Invoice {
        string id PK
        string subscriptionId FK
        string invoiceNumber
        decimal amount
        enum status "PAID, PENDING, OVERDUE"
        date issueDate
        date dueDate
    }

    ApiKey {
        string id PK
        string organizationId FK
        string key "Encrypted API key"
        string name
        jsonb permissions
        datetime expiresAt
        datetime createdAt
    }

    Webhook {
        string id PK
        string organizationId FK
        string url
        string[] events
        string secret
        boolean active
        datetime createdAt
    }

    WebhookDelivery {
        string id PK
        string webhookId FK
        string event
        jsonb payload
        int statusCode
        datetime attemptedAt
    }

    IpWhitelist {
        string id PK
        string organizationId FK
        string ipAddress
        string description
        boolean active
        datetime createdAt
    }

    NotificationPreferences {
        string id PK
        string organizationId FK
        string userId
        jsonb preferences
        datetime updatedAt
    }

    ComplianceConfiguration {
        string id PK
        string organizationId FK
        boolean popiaEnabled
        string dataResidency "ZA"
        int retentionPeriodDays
        jsonb settings
        datetime updatedAt
    }

    SecurityConfiguration {
        string id PK
        string organizationId FK
        boolean mfaRequired
        boolean ipWhitelistEnabled
        int sessionTimeoutMinutes
        jsonb settings
        datetime updatedAt
    }

---

Model Categories

1. Core B2B2G Entities (4 Models)

Organization

Purpose: Multi-tenant service provider organizations

Field	Type	Description
id	String (PK)	Clerk organization ID
name	String	Organization name
slug	String (Unique)	URL-friendly identifier
serviceType	Enum	B2B, B2G, or B2B2G
settings	JSON	Organization configuration
createdAt	DateTime	Creation timestamp
updatedAt	DateTime	Last update timestamp

Relationships:

• Has many: OrganizationUsers, Clients, Campaigns, Content, Templates, Projects
• Has many: Invitations, AuditLogs, Subscriptions, ApiKeys, Webhooks
• Has one: ComplianceConfiguration, SecurityConfiguration

Client

Purpose: B2B2G clients (government and business)

Field	Type	Description
id	String (PK)	Unique client ID
organizationId	String (FK)	Parent organization
name	String	Client name
sector	Enum	GOVERNMENT or BUSINESS
clientType	Enum	SME, LARGE_CORP, GOVERNMENT_NATIONAL, etc.
securityClearance	Enum	BASIC, ENHANCED, CONFIDENTIAL
contactInfo	JSON	Contact details
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization
• Has many: ClientAccess, Campaigns, Projects, Analytics

OrganizationUser

Purpose: Users within service provider organizations

Field	Type	Description
id	String (PK)	Unique user ID
clerkUserId	String	Clerk authentication ID
organizationId	String (FK)	Parent organization
role	Enum	SERVICE_PROVIDER_ADMIN, ACCOUNT_MANAGER, etc.
securityClearance	Enum	BASIC, ENHANCED, CONFIDENTIAL
permissions	JSON	Role-based permissions
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization
• Has many: ClientAccess, Campaigns, Content, ContentApprovals, AuditLogs

ClientAccess

Purpose: User access control per client

Field	Type	Description
id	String (PK)	Unique access ID
userId	String (FK)	Organization user
clientId	String (FK)	Target client
accessLevel	Enum	READ, WRITE, ADMIN
grantedAt	DateTime	Access grant timestamp

Relationships:

• Belongs to: OrganizationUser, Client

---

2. Content & Campaign (5 Models)

Campaign

Purpose: Marketing campaigns with sector-specific compliance

Field	Type	Description
id	String (PK)	Unique campaign ID
organizationId	String (FK)	Owner organization
clientId	String (FK)	Target client
name	String	Campaign name
status	Enum	DRAFT, ACTIVE, COMPLETED, CANCELLED
campaignType	Enum	EMAIL, SMS, SOCIAL, MULTI_CHANNEL
startDate	DateTime	Campaign start
endDate	DateTime	Campaign end
targeting	JSON	Targeting criteria
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization, Client, OrganizationUser (manager)
• Has many: Content, Analytics

Content

Purpose: Content entities with government approval workflows

Field	Type	Description
id	String (PK)	Unique content ID
organizationId	String (FK)	Owner organization
campaignId	String (FK)	Parent campaign
title	String	Content title
body	Text	Content body
status	Enum	DRAFT, PENDING_APPROVAL, APPROVED, PUBLISHED
dataClassification	Enum	PUBLIC, COMMERCIAL, CONFIDENTIAL, RESTRICTED
version	Int	Current version number
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization, Campaign, OrganizationUser (creator)
• Has many: ContentVersions, ContentApprovals, MediaAssets, EmailTracking

ContentVersion

Purpose: Version control for content

Field	Type	Description
id	String (PK)	Unique version ID
contentId	String (FK)	Parent content
versionNumber	Int	Sequential version
body	Text	Version content
createdBy	String	User ID
createdAt	DateTime	Version timestamp

Relationships:

• Belongs to: Content

ContentApproval

Purpose: Approval workflow tracking

Field	Type	Description
id	String (PK)	Unique approval ID
contentId	String (FK)	Target content
approverId	String (FK)	Approving user
status	Enum	PENDING, APPROVED, REJECTED
comments	Text	Approval comments
approvedAt	DateTime	Approval timestamp

Relationships:

• Belongs to: Content, OrganizationUser (approver)

MediaAsset

Purpose: Media file management with POPIA classification

Field	Type	Description
id	String (PK)	Unique asset ID
organizationId	String (FK)	Owner organization
contentId	String (FK)	Associated content (optional)
fileName	String	Original filename
fileUrl	String	Storage URL
mimeType	String	File MIME type
fileSize	Int	Size in bytes
classification	Enum	PUBLIC, CONFIDENTIAL, RESTRICTED
uploadedAt	DateTime	Upload timestamp

Relationships:

• Belongs to: Organization, Content (optional)

---

3. Analytics & Templates (3 Models)

Analytics

Purpose: Performance metrics and reporting

Field	Type	Description
id	String (PK)	Unique analytics ID
organizationId	String (FK)	Owner organization
campaignId	String (FK)	Target campaign (optional)
clientId	String (FK)	Target client (optional)
metrics	JSON	Performance data
reportDate	Date	Report date
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization, Campaign (optional), Client (optional)

Template

Purpose: Content templates with sector-specific options

Field	Type	Description
id	String (PK)	Unique template ID
organizationId	String (FK)	Owner organization
name	String	Template name
category	Enum	EMAIL, SMS, SOCIAL, DOCUMENT
content	Text	Template content
variables	JSON	Variable definitions
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization

Project

Purpose: Project management for clients

Field	Type	Description
id	String (PK)	Unique project ID
organizationId	String (FK)	Owner organization
clientId	String (FK)	Target client
name	String	Project name
status	Enum	ACTIVE, COMPLETED, ON_HOLD
startDate	Date	Project start
endDate	Date	Project end
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization, Client

---

4. POPIA Compliance (7 Models)

AuditLog

Purpose: Comprehensive audit trails for POPIA compliance

Field	Type	Description
id	String (PK)	Unique log ID
organizationId	String (FK)	Owner organization
userId	String (FK)	Acting user
action	String	Action performed
resource	String	Affected resource
details	JSON	Action details
ipAddress	String	User IP address
timestamp	DateTime	Action timestamp

Relationships:

• Belongs to: Organization, OrganizationUser

DataProcessingRecord

Purpose: Data processing records for POPIA

Field	Type	Description
id	String (PK)	Unique record ID
organizationId	String (FK)	Owner organization
dataSubject	String	Data subject identifier
processingPurpose	String	Purpose of processing
legalBasis	String	Legal basis (consent, contract, etc.)
retentionDate	Date	Data retention until
createdAt	DateTime	Record creation

Relationships:

• Belongs to: Organization

Invitation

Purpose: Custom POPIA-compliant invitation system

Field	Type	Description
id	String (PK)	Unique invitation ID
organizationId	String (FK)	Inviting organization
email	String	Invitee email
role	Enum	Invited role
token	String	Secure invite token
status	Enum	PENDING, ACCEPTED, EXPIRED, CANCELLED
expiresAt	DateTime	Expiration timestamp
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization
• Has many: InvitationAuditLogs

InvitationAuditLog

Purpose: Invitation audit trail

Field	Type	Description
id	String (PK)	Unique log ID
invitationId	String (FK)	Parent invitation
action	String	Action performed
details	JSON	Action details
timestamp	DateTime	Action timestamp

Relationships:

• Belongs to: Invitation

PopiaConsentRecord

Purpose: Consent tracking and management

Field	Type	Description
id	String (PK)	Unique consent ID
organizationId	String (FK)	Owner organization
dataSubject	String	Data subject identifier
consentType	String	Type of consent
consentGiven	Boolean	Consent status
consentDate	DateTime	Consent timestamp
expiryDate	DateTime	Consent expiry (optional)

Relationships:

• Belongs to: Organization

DataSubjectRequest

Purpose: Data subject rights handling

Field	Type	Description
id	String (PK)	Unique request ID
organizationId	String (FK)	Owner organization
requester	String	Requester identifier
requestType	Enum	ACCESS, CORRECTION, DELETION, PORTABILITY
status	Enum	PENDING, IN_PROGRESS, COMPLETED, REJECTED
requestDate	DateTime	Request timestamp
completedDate	DateTime	Completion timestamp (optional)

Relationships:

• Belongs to: Organization

EmailTracking

Purpose: Email delivery tracking

Field	Type	Description
id	String (PK)	Unique tracking ID
contentId	String (FK)	Associated content
recipientEmail	String	Recipient address
status	Enum	SENT, DELIVERED, OPENED, CLICKED, BOUNCED
sentAt	DateTime	Send timestamp
deliveredAt	DateTime	Delivery timestamp (optional)

Relationships:

• Belongs to: Content

---

5. Billing & Integrations (7 Models)

Subscription

Purpose: Subscription management

Field	Type	Description
id	String (PK)	Unique subscription ID
organizationId	String (FK)	Owner organization
plan	Enum	FREE, PROFESSIONAL, ENTERPRISE
status	Enum	ACTIVE, CANCELLED, EXPIRED
startDate	Date	Subscription start
endDate	Date	Subscription end
amount	Decimal	Monthly amount

Relationships:

• Belongs to: Organization
• Has many: Invoices

Invoice

Purpose: Invoice history

Field	Type	Description
id	String (PK)	Unique invoice ID
subscriptionId	String (FK)	Parent subscription
invoiceNumber	String	Invoice reference
amount	Decimal	Invoice amount
status	Enum	PAID, PENDING, OVERDUE, CANCELLED
issueDate	Date	Issue date
dueDate	Date	Due date

Relationships:

• Belongs to: Subscription

ApiKey

Purpose: API key management

Field	Type	Description
id	String (PK)	Unique key ID
organizationId	String (FK)	Owner organization
key	String	Encrypted API key
name	String	Key name/description
permissions	JSON	Key permissions
expiresAt	DateTime	Expiration timestamp (optional)
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization

Webhook

Purpose: Webhook configuration

Field	Type	Description
id	String (PK)	Unique webhook ID
organizationId	String (FK)	Owner organization
url	String	Webhook endpoint URL
events	String[]	Subscribed events
secret	String	Webhook signing secret
active	Boolean	Active status
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization
• Has many: WebhookDeliveries

WebhookDelivery

Purpose: Webhook delivery logs

Field	Type	Description
id	String (PK)	Unique delivery ID
webhookId	String (FK)	Parent webhook
event	String	Event type
payload	JSON	Event payload
statusCode	Int	HTTP status code
attemptedAt	DateTime	Attempt timestamp

Relationships:

• Belongs to: Webhook

IpWhitelist

Purpose: IP whitelist management

Field	Type	Description
id	String (PK)	Unique whitelist ID
organizationId	String (FK)	Owner organization
ipAddress	String	Whitelisted IP
description	String	Description
active	Boolean	Active status
createdAt	DateTime	Creation timestamp

Relationships:

• Belongs to: Organization

NotificationPreferences

Purpose: Notification settings

Field	Type	Description
id	String (PK)	Unique preference ID
organizationId	String (FK)	Owner organization
userId	String	User ID (optional)
preferences	JSON	Notification settings
updatedAt	DateTime	Last update

Relationships:

• Belongs to: Organization

---

6. Configuration (2 Models)

ComplianceConfiguration

Purpose: POPIA compliance settings

Field	Type	Description
id	String (PK)	Unique config ID
organizationId	String (FK)	Owner organization
popiaEnabled	Boolean	POPIA compliance enabled
dataResidency	String	Data residency (default: “ZA”)
retentionPeriodDays	Int	Data retention period
settings	JSON	Compliance settings
updatedAt	DateTime	Last update

Relationships:

• Belongs to: Organization

SecurityConfiguration

Purpose: Security settings

Field	Type	Description
id	String (PK)	Unique config ID
organizationId	String (FK)	Owner organization
mfaRequired	Boolean	MFA requirement
ipWhitelistEnabled	Boolean	IP whitelist enabled
sessionTimeoutMinutes	Int	Session timeout
settings	JSON	Security settings
updatedAt	DateTime	Last update

Relationships:

• Belongs to: Organization

---

Database Indexes

Performance Indexes

-- Organization lookups
CREATE INDEX idx_organization_slug ON Organization(slug);

-- User lookups
CREATE INDEX idx_organization_user_clerk_id ON OrganizationUser(clerkUserId);
CREATE INDEX idx_organization_user_org_id ON OrganizationUser(organizationId);

-- Client lookups
CREATE INDEX idx_client_org_id ON Client(organizationId);
CREATE INDEX idx_client_sector ON Client(sector);

-- Campaign lookups
CREATE INDEX idx_campaign_org_id ON Campaign(organizationId);
CREATE INDEX idx_campaign_client_id ON Campaign(clientId);
CREATE INDEX idx_campaign_status ON Campaign(status);

-- Content lookups
CREATE INDEX idx_content_campaign_id ON Content(campaignId);
CREATE INDEX idx_content_status ON Content(status);

-- Audit log queries
CREATE INDEX idx_audit_log_org_id ON AuditLog(organizationId);
CREATE INDEX idx_audit_log_user_id ON AuditLog(userId);
CREATE INDEX idx_audit_log_timestamp ON AuditLog(timestamp);

-- Invitation lookups
CREATE INDEX idx_invitation_email ON Invitation(email);
CREATE INDEX idx_invitation_token ON Invitation(token);
CREATE INDEX idx_invitation_status ON Invitation(status);

---

POPIA Compliance Features

Data Protection

• Encryption at Rest: All sensitive fields encrypted
• Data Residency: South African data center requirement
• Audit Trail: Complete audit logging for all operations
• Consent Management: PopiaConsentRecord tracking
• Data Subject Rights: DataSubjectRequest handling

Retention Policies

• Audit Logs: 5+ years retention
• Client Data: Configurable per organization
• Campaign Data: 3 years default
• Consent Records: Indefinite retention

---

Schema Evolution

Migration Strategy

1. Development: Test migrations in dev environment
2. Staging: Validate migrations with production-like data
3. Production: Apply migrations during maintenance window
4. Rollback: Maintain rollback scripts for critical changes

Version History

• v0.1.0: Initial 28-model schema
• v0.1.1: Added invitation audit logging
• v0.1.2: Enhanced security clearance fields
• v0.1.3: Added email tracking
• v0.1.4: Production optimization indexes

---

Performance Characteristics

Query Performance

• Organization lookups: <10ms (indexed slug)
• User authentication: <15ms (indexed Clerk ID)
• Campaign queries: <50ms (indexed org + client)
• Audit log queries: <100ms (indexed timestamp)

Scalability

• Multi-tenant isolation: Complete data separation
• Read replicas: Supported for analytics queries
• Connection pooling: Prisma connection management
• Caching layer: Redis for frequently accessed data

---

Related Documentation

• System Design
• Data Flow

---

Last Updated: September 25, 2025 Verified: Production v0.1.4