Database Design - WhatsApp SaaS Platform

Overview

This schema supports a multi-tenant WhatsApp SaaS platform with vendor-based isolation, shared and dedicated WABA accounts, message routing, campaigns, billing, templates, webhooks, and API audit logging.

Architecture Principles

Area	Design
Tenant root	Vendor
Tenant isolation	vendorId on tenant-owned models
Shared WABA	WabaAccount.isShared = true, selected at runtime
Dedicated WABA	Vendor.dedicatedWabaId, enforced by application logic
Routing identity	Required MessageLog.phoneNumberId
Analytics trace	Optional direct MessageLog.wabaId
Recipient audit	Required MessageLog.recipientMobile
Idempotency	@@unique([vendorId, idempotencyKey])
Queue readiness	status, priority, retryCount, nextRetryAt, expiresAt
Migration rule	prisma migrate deploy in staging/prod. Never db push.
Staging DB	kloyst_staging — fully isolated from production

Core Models

Vendor

Vendor is the tenant root. Users, contacts, campaigns, messages, wallet, templates, webhooks, and API logs are scoped from it.

Field	Type	Notes
id	UUID	Primary key
name	VARCHAR(255)	Tenant name
planType	PlanType	SHARED or DEDICATED
settings	Json?	Flexible tenant settings
dedicatedWabaId	UUID?	Optional dedicated WABA reference
createdAt / updatedAt	DateTime	Audit timestamps

WabaAccount

WabaAccount is the unified WABA model for both shared and dedicated Meta WhatsApp Business Accounts.

Field	Type	Notes
id	UUID	Primary key
wabaId	VARCHAR(255)	Unique Meta WABA ID
isShared	Boolean	Shared vs dedicated account marker
businessName	VARCHAR(255)?	Meta business name
systemUserToken	String?	Meta API credential
qualityRating	VARCHAR(50)?	Meta quality rating
createdAt / updatedAt	DateTime	Audit timestamps

Dedicated WABA exclusivity is intentionally not enforced with a database unique constraint on Vendor.dedicatedWabaId. The application layer validates assignment rules.

PhoneNumber

PhoneNumber remains separate because a WABA can have many sender numbers and messages must always preserve the sender/routing number.

Field	Type	Notes
id	UUID	Primary key
phoneNumberId	VARCHAR(255)	Unique Meta phone number ID
displayNumber	VARCHAR(20)	Visible phone number
verifiedName	VARCHAR(255)?	WhatsApp verified name
qualityRating	VARCHAR(50)?	Meta quality rating
status	VARCHAR(50)?	Meta number status
wabaId	UUID	Parent WABA
createdAt / updatedAt	DateTime	Audit timestamps

MessageLog

MessageLog is the high-volume audit and queue table. It is directly tenant-scoped by vendorId.

Field	Type	Notes
id	UUID	Primary key
waMessageId	VARCHAR(255)?	Unique Meta message ID
status	MessageStatus	QUEUED, SENT, DELIVERED, READ, FAILED
direction	MessageDirection	INBOUND or OUTBOUND
error	String?	Failure details
payload	Json?	Debug request/response payload
pricing	Json?	Billing data
content	String?	Message content
messageType	VARCHAR(50)?	text, image, document, etc.
recipientMobile	VARCHAR(20)	Immutable recipient identity
source	VARCHAR(50)?	API, CAMPAIGN, WEBHOOK, RETRY, etc.
priority	Int	Queue priority
sentAt / deliveredAt / readAt / failedAt	DateTime?	Lifecycle timestamps
retryCount	Int	Retry attempts
nextRetryAt	DateTime?	Retry worker schedule
expiresAt	DateTime?	Queue/message expiry
idempotencyKey	VARCHAR(255)?	Per-vendor idempotency key
vendorId	UUID	Tenant isolation
phoneNumberId	UUID	Required routing identity
wabaId	UUID?	Direct WABA trace
contactId	UUID?	Optional contact reference
campaignId	UUID?	Optional campaign reference

Campaign (Updated)

Campaigns now include cost estimation linkage and template variable support.

Field	Type	Notes
id	UUID	Primary key
name	VARCHAR(255)	Campaign name
status	CampaignStatus	DRAFT, QUEUED, PROCESSING, COMPLETED, FAILED
scheduledAt	DateTime?	Optional send schedule
templateId	UUID?	Linked WhatsApp template (SetNull on delete)
templateVariables	Json?	Variable values for the template
audienceType	VARCHAR(50)?	LABEL, CONTACT_LIST, FILTER_QUERY, SAVED_AUDIENCE
audienceIds	Json?	Selected audience IDs
totalRecipients	Int	Total count before validation
validRecipients	Int	Count after dedup + validation
estimatedCost	Decimal(12,4)?	Pre-send cost estimate
quotationId	UUID?	Linked quotation document (SetNull on delete)
vendorId	UUID	Tenant isolation

---

Pricing Engine Models

The pricing engine models support per-country rate cards, volume discounts, platform fees, and immutable quotation snapshots.

Model	Purpose
PricingRegion	Country code → region group mapping (e.g., IN → South Asia)
PricingRate	Base rate per message per TemplateCategory per region group
PricingTier	Volume-based discount tiers (discountPct applied to base rate)
PlatformFeeRule	Kloyst markup: PERCENTAGE, FLAT, or HYBRID; per-vendor overrides
PricingSnapshot	Immutable JSON snapshot of rates + fees at quote time
Quotation	Pre-campaign cost estimate with PDF generation lifecycle
QuotationItem	Per-country breakdown row within a Quotation

Quotation

Field	Type	Notes
id	UUID	Primary key
quoteNumber	VARCHAR(50)	Human-readable unique ref (e.g. KQ-00042)
vendorId	UUID	Tenant isolation
status	QuotationStatus	DRAFT, ISSUED, EXPIRED, SUPERSEDED
category	TemplateCategory	Campaign message category
totalRecipients	Int	Full list size
validRecipients	Int	After dedup and validation
estimatedMetaCost	Decimal(12,4)	Sum of Meta charges
platformFee	Decimal(12,4)	Kloyst markup
estimatedTotal	Decimal(12,4)	Meta + platform fee
walletBalance	Decimal(12,4)?	Balance at time of quote
walletSufficient	Boolean?	Whether wallet covers total
pdfStatus	PdfStatus	PENDING → GENERATING → READY → FAILED
pdfPath	String?	File path to generated PDF
validUntil	DateTime	Quote expiry timestamp
snapshotId	UUID	Linked PricingSnapshot (immutable)

---

Labels & ContactAuditLog

Label

Labels allow vendors to tag contacts for audience targeting.

Field	Type	Notes
id	UUID	Primary key
vendorId	UUID	Tenant isolation
name	VARCHAR(255)	Label name
color	VARCHAR(50)?	UI display color

Unique: [vendorId, name] — label names are unique per vendor.

ContactAuditLog

Immutable history of contact data mutations (name changes, label assignments) per import job.

Field	Type	Notes
id	UUID	Primary key
vendorId	UUID	Tenant isolation
contactId	UUID	Contact that was modified
action	VARCHAR(50)	CREATED, METADATA_UPDATED, LABELS_MERGED
source	ContactSource	IMPORT, MANUAL, API, SYNC
importJobId	UUID?	Linked import job
previousName	VARCHAR(255)?	Before update
newName	VARCHAR(255)?	After update
previousLabels	Json?	Before label state
newLabels	Json?	After label state

---

Auth Models

RefreshToken

Server-side refresh token storage (hashed). Enables token rotation and per-device revocation.

Field	Type	Notes
id	UUID	Primary key
userId	UUID	Owning user (Cascade delete)
tokenHash	VARCHAR(255)	Unique HMAC hash of token value
userAgent	String?	Browser/device info
ipAddress	String?	Client IP at issue time
expiresAt	DateTime	Token expiry

VerificationToken

One-time tokens for email verification and password reset.

Field	Type	Notes
id	UUID	Primary key
tokenHash	VARCHAR(255)	Unique hash (never store plaintext)
userId	UUID	Owning user (Cascade delete)
type	TokenType	EMAIL_VERIFY or PASSWORD_RESET
expiresAt	DateTime	Expiry
usedAt	DateTime?	Set when consumed (prevents reuse)

---

WABA Architecture

Wallet is one-to-one with Vendor. Transaction is scoped through walletId.

Model	Purpose
Wallet	Stores current balance with Decimal(12,4)
Transaction	Stores credit, debit, and refund ledger events

Templates

Template supports both vendor-level and WABA-level templates.

Scope	Representation
Vendor-level	vendorId set, wabaId = null
WABA-level	vendorId set, wabaId points to WabaAccount

Webhooks and ApiLog

Webhook stores tenant event subscriptions. ApiLog stores API audit and analytics events.

WABA Architecture

Message Lifecycle

Queue Processing

Workers can select messages with:

• status
• priority
• nextRetryAt
• expiresAt
• vendorId
• phoneNumberId

The supporting index is:

@@index([status, priority, nextRetryAt])

Security and Isolation

• All tenant-owned reads must include vendorId.
• Shared WABA data is never treated as tenant-owned.
• Message isolation is enforced through MessageLog.vendorId.
• Contact deletion does not erase recipient identity because recipientMobile is immutable on MessageLog.

Related Documentation

• ER Diagram
• Schema Diagram
• UML Diagram
• Schema Reference
• Full Prisma Schema
• Prisma Model Reference
• Staging Deployment