Database Normalization and Design Review
Summary​
The current schema is normalized for the core transactional model while intentionally using JSON fields for flexible external data and metadata.
| Area | Status | Notes |
|---|---|---|
| 1NF | Pass | Core attributes are scalar; JSON is used for flexible metadata and external payloads |
| 2NF | Pass | All models use single-column UUID primary keys |
| 3NF | Pass | WABA, phone number, wallet, transaction, and campaign data are separated |
| BCNF | Pass | Candidate keys and unique constraints are explicit |
| Multi-tenancy | Pass | Tenant-owned data is scoped by vendorId |
| Shared WABA design | Pass | Shared WABAs are platform resources; message tenant isolation is in MessageLog.vendorId |
| Dedicated WABA design | Pass | Vendor.dedicatedWabaId references WabaAccount; exclusivity is application-enforced |
| Message auditability | Pass | recipientMobile, phoneNumberId, wabaId, payload, pricing, and lifecycle timestamps are retained |
JSON Field Justification​
| Field | Purpose |
|---|---|
| Vendor.settings | Flexible tenant configuration |
| Contact.attributes | Tenant-defined contact metadata |
| MessageLog.payload | Meta/API debug payload |
| MessageLog.pricing | Billing data from provider |
| Template.variables | Template placeholder metadata |
These fields do not replace core relational structure.
Tenant Isolation​
Tenant-owned models:
- User
- Contact
- Campaign
- MessageLog
- Wallet
- Template
- Webhook
- ApiLog
Indirectly scoped models:
- Transaction through Wallet
- PhoneNumber through WabaAccount usage and MessageLog routing
- WabaAccount as a platform resource
Idempotency​
Message idempotency is scoped per tenant:
@@unique([vendorId, idempotencyKey])
This allows different vendors to reuse the same external idempotency key without conflicts.
WABA Normalization​
WabaAccount is a single unified model for shared and dedicated WABAs:
isShared Boolean @default(true)
Shared WABAs are not directly tied to vendors. Dedicated assignment is represented by:
Vendor.dedicatedWabaId
The application layer enforces dedicated assignment rules.
MessageLog Design​
MessageLog intentionally duplicates some trace data:
| Field | Reason |
|---|---|
| phoneNumberId | Required routing and sender audit |
| wabaId | Analytics without joining through PhoneNumber |
| recipientMobile | Immutable recipient identity |
| payload | Debugging provider/API data |
| pricing | Billing reconciliation |
This is controlled denormalization for high-volume operational queries.