Prisma Schema Reference
This document is the authoritative reference for the Kloyst database schema. All models are defined in prisma/schema.prisma and managed via prisma migrate.
Migration rule: Always use
prisma migrate deployin staging and production. Never usedb pushoutside of local development.
Enums
| Enum | Values |
|---|---|
PlanType | SHARED, DEDICATED |
Role | SUPERADMIN, ADMIN, STAFF, USER |
UserStatus | ONBOARDING_BUSINESS_PENDING, ONBOARDING_META_PENDING, ACTIVE, SUSPENDED |
MessageStatus | QUEUED, SENT, DELIVERED, READ, FAILED |
MessageDirection | INBOUND, OUTBOUND |
OutboxStatus | PENDING, PROCESSED, FAILED |
CampaignStatus | DRAFT, QUEUED, PROCESSING, COMPLETED, FAILED |
TransactionType | CREDIT_RECHARGE, DEBIT_MESSAGE, REFUND |
MergeStrategy | SKIP_DUPLICATES, MERGE_LABELS, UPDATE_METADATA, FULL_REPLACE |
ContactSource | IMPORT, MANUAL, API, SYNC |
ImportStatus | UPLOADED, SCANNING, QUARANTINED, HEADERS_EXTRACTED, AWAITING_MAPPING, MAPPING_CONFIRMED, QUEUED, PROCESSING, PENDING_REVIEW, PARTIAL_SUCCESS, COMPLETED, FAILED, CANCELLED |
TemplateCategory | MARKETING, UTILITY, AUTHENTICATION, AUTHENTICATION_INTERNATIONAL |
WhatsAppTemplateCategory | MARKETING, UTILITY, AUTHENTICATION |
TemplateStatus | DRAFT, PENDING, APPROVED, REJECTED, PAUSED, ARCHIVED |
TemplateLanguage | en |
QuotationStatus | DRAFT, ISSUED, EXPIRED, SUPERSEDED |
AudienceType | LABEL, CONTACT_LIST, FILTER_QUERY, SAVED_AUDIENCE |
FeeType | PERCENTAGE, FLAT, HYBRID |
PdfStatus | PENDING, GENERATING, READY, FAILED |
BusinessType | ECOMMERCE, AGENCY, EDTECH, EDUCATION, EVENT, OTHER |
Volume | VOL_0_1K, VOL_1K_10K, VOL_10K_50K, VOL_50K_100K, VOL_100K_PLUS |
TokenType | EMAIL_VERIFY, PASSWORD_RESET |
WabaStatus | PENDING, VERIFIED, FAILED |
Models
Vendor (Tenant Root)
id UUID PK
name String(255)
planType PlanType (default: SHARED)
businessType BusinessType (default: OTHER)
monthlyVolume Volume (default: VOL_0_1K)
settings Json?
dedicatedWabaId UUID? → WabaAccount (SetNull on delete)
createdAt DateTime
updatedAt DateTime
deletedAt DateTime? ← soft delete
Indexes: planType
Relations: users, contacts, campaigns, messageLogs, wallet, webhooks, templates, apiLogs, deadLetterQueues, importJobs, labels, contactAuditLogs, quotations
User
id UUID PK
email String(255) UNIQUE
passwordHash String
role Role (default: USER)
status UserStatus (default: ONBOARDING_BUSINESS_PENDING)
firstName String(100)?
lastName String(100)?
isActive Boolean (default: true)
isEmailVerified Boolean (default: false)
vendorId UUID? → Vendor (Cascade)
createdAt DateTime
updatedAt DateTime
deletedAt DateTime? ← soft delete
Indexes: vendorId, email, [vendorId, createdAt]
Relations: verificationTokens, refreshTokens
WabaAccount (WhatsApp Business Account)
id UUID PK
wabaId String(255) UNIQUE ← Meta's WABA ID
isShared Boolean (default: true)
businessName String(255)?
systemUserToken String?
qualityRating String(50)?
status WabaStatus (default: PENDING)
createdAt DateTime
updatedAt DateTime
Indexes: isShared, wabaId
Relations: dedicatedVendors (Vendor[]), phoneNumbers, templates, messageLogs
PhoneNumber
id UUID PK
phoneNumberId String(255) UNIQUE ← Meta's Phone Number ID
displayNumber String(20)
verifiedName String(255)?
qualityRating String(50)?
status String(50)?
wabaId UUID → WabaAccount (Cascade)
createdAt DateTime
updatedAt DateTime
Indexes: wabaId, phoneNumberId
Relations: messageLogs
Contact
id UUID PK
mobile String ← Plaintext or AES-encrypted ciphertext
mobileHash String? ← Deterministic HMAC hash of E.164 number (for dedup)
countryCode String(10)?
nationalNumber String? ← Plaintext or encrypted
name String? ← Plaintext or encrypted
normalizedName String? ← Encrypted or null
attributes Json? ← Arbitrary custom fields
source ContactSource (default: MANUAL)
lastImportedAt DateTime?
vendorId UUID → Vendor (Cascade)
createdAt DateTime
updatedAt DateTime
deletedAt DateTime? ← soft delete
Unique: [vendorId, mobileHash] — prevents duplicate contacts per tenant
Indexes: vendorId, mobileHash, [vendorId, createdAt], deletedAt, source
Relations: messageLogs, labels, auditLogs
MessageLog
id UUID PK
waMessageId String(255)? UNIQUE ← Meta's message ID
status MessageStatus (default: QUEUED)
direction MessageDirection
error String?
payload Json? ← Full Meta API payload
pricing Json? ← Meta pricing response
content String?
messageType String(50)?
recipientMobile String(20)
source String(50)?
priority Int (default: 0)
sentAt DateTime?
deliveredAt DateTime?
readAt DateTime?
failedAt DateTime?
retryCount Int (default: 0)
nextRetryAt DateTime?
expiresAt DateTime?
idempotencyKey String(255)?
vendorId UUID → Vendor (Cascade)
phoneNumberId UUID → PhoneNumber (Restrict)
wabaId UUID? → WabaAccount
contactId UUID? → Contact (SetNull)
campaignId UUID? → Campaign (SetNull)
createdAt DateTime
updatedAt DateTime
Unique: [vendorId, idempotencyKey]
Indexes: vendorId, phoneNumberId, wabaId, contactId, campaignId, status, createdAt, waMessageId, recipientMobile, source, priority, [vendorId, status, createdAt], [campaignId, status], [status, priority, nextRetryAt], and more
Campaign
id UUID PK
name String(255)
status CampaignStatus (default: DRAFT)
scheduledAt DateTime?
description String?
templateId UUID? → Template (SetNull)
templateVariables Json?
audienceType String(50)?
audienceIds Json?
totalRecipients Int (default: 0)
validRecipients Int (default: 0)
estimatedCost Decimal(12,4)?
quotationId UUID? → Quotation (SetNull)
vendorId UUID → Vendor (Cascade)
createdAt DateTime
updatedAt DateTime
Indexes: vendorId, status, scheduledAt, [vendorId, status], [vendorId, scheduledAt], [vendorId, createdAt]
Wallet
id UUID PK
balance Decimal(12,4) (default: 0) ← Available balance
reserved Decimal(12,4) (default: 0) ← Held for in-flight sends
vendorId UUID UNIQUE → Vendor (Cascade) ← One wallet per vendor
createdAt DateTime
updatedAt DateTime
Relations: transactions
Transaction
id UUID PK
type TransactionType
amount Decimal(12,4)
reference String(255)?
description String?
walletId UUID → Wallet (Cascade)
createdAt DateTime
Indexes: walletId, type, createdAt
Template
id UUID PK
name String(255)
content String
language TemplateLanguage (default: en)
category WhatsAppTemplateCategory
metaTemplateId String(255)?
status TemplateStatus (default: DRAFT)
rejectionReason String?
variables Json?
usedCount Int (default: 0)
vendorId UUID → Vendor (Cascade)
wabaId UUID? → WabaAccount (SetNull)
createdAt DateTime
updatedAt DateTime
Unique: [vendorId, metaTemplateId]
Indexes: vendorId, wabaId, status, [vendorId, status], [vendorId, category]
ContactImportJob
id UUID PK
vendorId UUID → Vendor (Cascade)
fileName String
filePath String? ← Temp disk path during processing
fileSize Int
fileHash String(64) ← SHA-256 checksum
status ImportStatus (default: UPLOADED)
mergeStrategy MergeStrategy (default: MERGE_LABELS)
totalRows Int?
validRows Int (default: 0)
invalidRows Int (default: 0)
warningRows Int (default: 0)
newContactsInserted Int (default: 0)
existingContactsUpdated Int (default: 0)
contactsMerged Int (default: 0)
duplicatesSkipped Int (default: 0)
headers Json?
headerChecksum String(255)?
columnMapping Json?
createdAt DateTime
updatedAt DateTime
Indexes: vendorId, status, createdAt, [fileHash, vendorId]
Relations: chunks (ContactImportChunk[])
ContactImportChunk
id UUID PK
jobId UUID → ContactImportJob (Cascade)
chunkIndex Int (default: 0)
status String(50) (default: "PENDING") ← PENDING | PROCESSED | FAILED
rawPayload Json ← Up to 5000 rows
validData Json? ← Cleaned rows ready for DB upsert
errors Json? ← Row-level validation errors
createdAt DateTime
updatedAt DateTime
Indexes: jobId, status
OutboxEvent
id UUID PK
type String(50)
payload Json
status OutboxStatus (default: PENDING)
error String?
createdAt DateTime
updatedAt DateTime
Indexes: [status, createdAt] — optimized for FOR UPDATE SKIP LOCKED polling
DeadLetterQueue
id UUID PK
payload Json
reason String?
messageLogId UUID?
vendorId UUID → Vendor (Cascade)
campaignId UUID? → Campaign (SetNull)
createdAt DateTime
updatedAt DateTime
Indexes: vendorId, createdAt
Pricing Models
| Model | Purpose |
|---|---|
PricingRegion | Country → region group mapping (e.g., India → South Asia) |
PricingRate | Base rate per message per category per region |
PricingTier | Volume-based discount tiers |
PlatformFeeRule | Kloyst markup on top of Meta cost (% or flat or hybrid) |
PricingSnapshot | Immutable snapshot of rates/fees/tiers at quote creation time |
Quotation | Pre-campaign cost estimate document (with PDF generation) |
QuotationItem | Per-country breakdown row within a Quotation |
Auth Models
| Model | Fields | Purpose |
|---|---|---|
RefreshToken | userId, tokenHash, userAgent, ipAddress, expiresAt | Server-stored refresh token |
VerificationToken | userId, tokenHash, type, expiresAt, usedAt | Email verify + password reset |
Audit & Observability Models
| Model | Purpose |
|---|---|
Label | Contact grouping tags per vendor |
ContactAuditLog | Immutable log of contact data changes (name, labels) per import |
ApiLog | Per-request log: endpoint, method, statusCode, duration |
Webhook | Vendor-configured outbound webhook subscriptions |