123eworld Knowledge Hub → SMS API → Page 313
SMS API Database Design: Storing Messages, Attempts, DLRs and Audit Logs
A practical developer reference designed to solve real implementation and production problems around sms api database design: storing messages, attempts, dlrs and audit logs.
Core entities
A practical SMS database commonly needs logical messages, delivery attempts, provider events, webhook deliveries, credentials, sender profiles, routing configuration and audit records.
Logical message table
Store tenant, application, recipient reference, sender profile, message state, timestamps, segment information and client reference. Avoid storing unnecessary plaintext message content.
Attempt table
Each provider submission attempt should have its own ID, provider, route, status, timestamps and diagnostic references.
Event table
Provider receipts and internal lifecycle events should be immutable records. This supports reconciliation and auditing.
Indexes
Indexes should support tenant-scoped message lookup, status filtering, date-range reporting and provider attempt reconciliation.
Partitioning
High-volume systems may partition message and event data by time or tenant strategy. Partitioning should be driven by measured access patterns.
Retention
Define retention separately for operational state, detailed events and analytical aggregates.
Idempotency
Persist idempotency keys with appropriate uniqueness constraints.
Audit logs
Administrative changes such as sender, route and credential changes require immutable audit evidence.
Consistency
Use transactions carefully around message creation and idempotency. Do not hold long database transactions while calling providers.
Privacy
Encrypt sensitive fields where appropriate and restrict access.
Reference architecture
Operational database → event stream/queue → analytical store, with controlled synchronization between them.
Practical implementation guidance
Design the public API around a stable logical message ID and keep provider-specific complexity behind internal adapters. Every asynchronous step should be durable, observable and safe to retry.
Security and privacy
Treat phone numbers, message content, credentials and delivery evidence as sensitive. Apply tenant authorization before data access and avoid unnecessary values in logs, traces and reports.
Developer-first principle
A useful reference page should tell developers not only what a feature is, but how to implement it safely, how to troubleshoot it and what failure cases to expect.
Related 123eworld guides
Explore the 123eworld SMS & WhatsApp Knowledge Hub for related API, routing, reliability and developer guides.
Message lifecycle storage
The logical message record should be optimized for current-state queries. Event and attempt records should be append-oriented. Separating these patterns reduces contention between high-volume writes and dashboard reads.
Provider attempt model
Store one attempt per provider submission. Include attempt number, provider, route, submission state, timestamps and safe diagnostic codes. Never overwrite an earlier attempt merely because a later provider was selected.
Event ingestion
Receipt ingestion should append an event and then update the logical state using controlled transition logic. This prevents loss of evidence when state rules change later.
Indexes and queries
Common indexes include tenant plus created time, tenant plus client reference, logical message ID and provider message ID. Add indexes only for measured query patterns because high-write tables can become expensive to maintain.
Archiving
Older detailed records can move to lower-cost storage while keeping aggregated reporting data readily available. The archive process must preserve tenant isolation and auditability.
Transactions
Create the idempotency record and logical message atomically where necessary. Do not keep a database transaction open while waiting for a provider HTTP response.
Concurrency
Multiple workers may update the same message after retries or late receipts. Use optimistic versioning or carefully defined locking to prevent lost updates.
Audit data
Administrative configuration changes belong in an immutable audit stream rather than in the message table.
Analytics separation
For high volume, stream operational events into a separate analytics store. Reporting queries should not compete with message submission transactions.
Backup and restore
Test restoration of both operational state and configuration. A backup that cannot restore routing and sender configuration is incomplete.
Schema evolution
Version database migrations carefully. Adding nullable fields is often safer than changing the meaning of existing columns. Event schemas should also be versioned so downstream analytics does not break silently.
High-write tables
Message and event tables can become write hotspots. Use appropriate partitioning, batching and connection management after measuring actual load. Avoid premature complexity, but design for the fact that every SMS can generate multiple records.
Reconciliation indexes
Provider message ID and attempt ID need efficient lookup because uncertain submissions and late receipts often depend on these keys. Missing indexes can turn reconciliation into an expensive scan.
Deletion and privacy
When retention expires, remove or anonymize data according to policy while preserving the minimum aggregate evidence required for business reporting. Deletion jobs should be monitored so they do not affect live traffic.
Write amplification
A single logical SMS can create a message record, attempt record, provider receipt event, state update and webhook delivery records. Database capacity planning should model this write amplification rather than only the number of SMS requests.
Read replicas
If status and reporting queries become heavy, read replicas can protect the primary write database. Be explicit about replica lag so customers do not mistake a slightly stale report for a delivery failure.
Event retention
Keep immutable provider events long enough to support reconciliation and dispute analysis. Aggregated metrics can usually be retained longer at lower storage cost.
Migration safety
Large schema migrations should avoid locking high-volume message tables for long periods. Use online or phased migration techniques supported by the chosen database.
Database checklist
Review indexes against real queries, measure write amplification, test concurrent state updates, verify retention jobs and rehearse restoration. Keep transactional writes separate from large analytical queries.
Reference principle
The SMS database should make the current state fast to query while preserving enough immutable evidence to explain every important state transition.
Advanced production guidance
The database design should explicitly separate customer-facing state from provider evidence. The message table can contain the current state required by the status API, while the event table preserves the evidence that led to that state. Attempt records connect provider submissions to the logical message. This structure makes failover and reconciliation much easier because an engineer can see exactly which provider was contacted and what happened. It also avoids a common design mistake: overwriting one provider attempt with another and losing the evidence needed to explain a duplicate or uncertain submission. As volume grows, this separation supports partitioning, archival and analytical pipelines without changing the public message model.
Reference architecture detail
Database capacity planning should include both storage growth and query growth. Every SMS can generate multiple events, and reporting queries may scan several years of data. Partitioning, archival and analytical replication should be introduced based on measured growth. The operational database should remain optimized for current message processing rather than becoming the permanent data warehouse. This separation is especially important when enterprise customers request large exports during the same periods when transactional traffic is high.
Final production checklist
Keep database migrations reversible where practical and rehearse rollback procedures. A migration that succeeds technically but leaves message processing unable to write new events can become a major availability incident. Schema changes should therefore be tested under realistic write load.
Advanced reference note
When message volume grows, database architecture should evolve before operational queries begin affecting live sending. A read-heavy dashboard can exhaust connections or CPU on a database that also handles message-state writes. Separating analytical workloads, adding appropriate replicas and moving historical events to an analytical store can protect the transactional path. The migration should be gradual, with comparison checks between old and new reporting results. This allows the platform to improve scale without changing customer-facing message semantics.
Final reference guidance
Document which fields are authoritative for each use case. The current message state is authoritative for status queries, event history is authoritative for evidence, and analytical aggregates are authoritative for reporting within their documented freshness window. This prevents different teams from choosing conflicting data sources.
Implementation safeguard
Keep analytical queries from modifying operational records. Reporting pipelines should consume events or read replicas rather than running corrective business logic against the transactional database. This separation reduces the chance that a reporting job changes live message state accidentally.
Operational control
Database health checks should include connection utilization, replication lag, write latency and storage growth. These indicators often reveal capacity problems before message processing begins to fail.