Database Schema Reference
Tables Summary​
| Table | Tenant Scope | Key Indexes | Purpose |
|---|---|---|---|
| Vendor | Root | planType | Tenant account |
| User | vendorId | vendorId, email | Platform users |
| WabaAccount | Platform | isShared, wabaId | Shared and dedicated Meta WABA accounts |
| PhoneNumber | WABA | wabaId, phoneNumberId | WhatsApp sender numbers |
| Contact | vendorId | vendorId, mobile | Tenant contacts |
| MessageLog | vendorId | vendorId, phoneNumberId, wabaId, campaignId, status, createdAt | Message audit, queue, and analytics |
| Campaign | vendorId | vendorId, status, scheduledAt | Bulk messaging campaigns |
| Wallet | vendorId | vendorId | Tenant balance |
| Transaction | walletId | walletId, type, createdAt | Billing ledger |
| Template | vendorId, optional wabaId | vendorId, wabaId, metaTemplateId, status | Vendor and WABA templates |
| Webhook | vendorId | vendorId, eventType | Event subscriptions |
| ApiLog | vendorId | vendorId, endpoint, createdAt | API audit log |
Enum Reference​
| Enum | Values |
|---|---|
| PlanType | SHARED, DEDICATED |
| Role | ADMIN, STAFF, USER |
| MessageStatus | QUEUED, SENT, DELIVERED, READ, FAILED |
| MessageDirection | INBOUND, OUTBOUND |
| CampaignStatus | DRAFT, QUEUED, PROCESSING, COMPLETED, FAILED |
| TransactionType | CREDIT_RECHARGE, DEBIT_MESSAGE, REFUND |
Unique Constraints​
| Constraint | Scope | Purpose |
|---|---|---|
| User.email | Platform | Login identity |
| WabaAccount.wabaId | Platform | Meta WABA identity |
| PhoneNumber.phoneNumberId | Platform | Meta phone number identity |
| Contact(vendorId, mobile) | Vendor | One contact number per tenant |
| MessageLog.waMessageId | Platform | Meta message identity |
| MessageLog(vendorId, idempotencyKey) | Vendor | Per-tenant idempotent requests |
| Wallet.vendorId | Platform | One wallet per vendor |
| Template.metaTemplateId | Platform | Meta template identity |
Foreign Keys​
Cascade Deletes​
Vendor -> User, Contact, Campaign, MessageLog, Template, Webhook, Wallet
WabaAccount -> PhoneNumber
Wallet -> Transaction
Set Null Deletes​
Vendor.dedicatedWabaId -> WabaAccount.id
Template.wabaId -> WabaAccount.id
MessageLog.contactId -> Contact.id
MessageLog.campaignId -> Campaign.id
Restricted / Default Deletes​
MessageLog.phoneNumberId -> PhoneNumber.id uses Restrict
MessageLog.wabaId -> WabaAccount.id uses default behavior
ApiLog.vendorId -> Vendor.id uses default behavior
MessageLog Query Indexes​
| Index | Use |
|---|---|
| vendorId | Tenant isolation |
| phoneNumberId | Phone-specific history |
| wabaId | WABA analytics |
| campaignId | Campaign delivery analytics |
| status | State filters |
| createdAt | Time-range scans |
| waMessageId | Meta webhook lookup |
| recipientMobile | Recipient audit lookup |
| recipientMobile, vendorId | Tenant recipient history |
| source | Source analytics |
| priority | Queue ordering |
| vendorId, createdAt | Tenant timeline |
| vendorId, status, createdAt | Tenant status timeline |
| phoneNumberId, createdAt | Sender timeline |
| wabaId, createdAt | WABA timeline |
| campaignId, status | Campaign status counts |
| nextRetryAt | Retry worker pickup |
| status, priority, nextRetryAt | Queue worker scan |
Common Queries​
SELECT *
FROM message_log
WHERE vendor_id = $1
ORDER BY created_at DESC
LIMIT 100;
SELECT *
FROM message_log
WHERE vendor_id = $1
AND idempotency_key = $2;
SELECT status, COUNT(*)
FROM message_log
WHERE campaign_id = $1
GROUP BY status;
SELECT *
FROM message_log
WHERE status = 'QUEUED'
AND (next_retry_at IS NULL OR next_retry_at <= NOW())
ORDER BY priority DESC, created_at ASC
LIMIT 500;