Model SMS as four things: a contact, a conversation, a message and an opt-out state. Keep the phone number as the identity of the thread, keep status on the message, and keep consent on the contact. Most data-model regrets come from collapsing those into one table.
This post is for engineers adding texting to a CRM or vertical SaaS product. It covers the tables, the keys that stop duplicate threads, how to store message status, and where opt-outs and segment counts belong. The schema below is an example we'd start from, not a description of Ready's internal tables.
What are the core entities?
You need a tenant, a contact, a conversation and a message. That's the minimum, and each one answers a different question.
- Tenant: which customer of yours owns this data. On Ready, each customer is a sub-account with its own numbers and opt-outs, so your tenant id maps one to one.
- Contact: a person your customer texts. Holds a normalized phone number in E.164 form and the consent state.
- Conversation: the thread between one of your customer's numbers and one contact.
- Message: one inbound or outbound text, with direction, body, status and segment count.
Here's a starting schema in Postgres terms:
CREATE TABLE contacts (
id bigserial PRIMARY KEY,
tenant_id bigint NOT NULL,
phone_e164 text NOT NULL,
opted_out_at timestamptz,
consent_note text,
UNIQUE (tenant_id, phone_e164)
);
CREATE TABLE conversations (
id bigserial PRIMARY KEY,
tenant_id bigint NOT NULL,
contact_id bigint NOT NULL REFERENCES contacts(id),
our_number text NOT NULL,
last_message_at timestamptz,
UNIQUE (tenant_id, contact_id, our_number)
);
CREATE TABLE messages (
id bigserial PRIMARY KEY,
conversation_id bigint NOT NULL REFERENCES conversations(id),
direction text NOT NULL,
body text NOT NULL,
status text NOT NULL,
segments int,
provider_id text,
created_at timestamptz NOT NULL DEFAULT now()
); Why is the phone number the thread key?
Because it's the only identifier the carrier gives you on an inbound text. A reply arrives with a sender number and a recipient number, and nothing else. If your thread lookup depends on a CRM contact id, an inbound text from a number you've never stored has nowhere to land.
So the lookup order on inbound is: find the tenant by the number that received the text, find or create the contact by (tenant_id, phone_e164), then find or create the conversation by (tenant_id, contact_id, our_number). The unique constraints make that a safe upsert even when two webhooks arrive at once.
Normalize before you store. Convert every number to E.164 (+12125550199) at the edge, and reject or flag anything that won't parse. Ready's send route treats a 10-digit US number as +1, but your database shouldn't rely on someone else's defaults.
Include our_number in the conversation key. A customer with two numbers can end up with two threads to the same person, and merging them hides which number the person actually replied to.
How should you store message status?
Store it as a small state machine on the message row, and never as a free-text copy of whatever the provider sent. A text moves forward through states, and webhooks can arrive late or twice.
| Status | Meaning | Final? |
|---|---|---|
| queued | Accepted by your app, not yet handed over | No |
| sent | Handed to the messaging provider | No |
| delivered | Provider reports handset delivery | Yes |
| failed | Provider or carrier reports it didn't arrive | Not always |
| refused | Your pre-send checks stopped it | Yes |
| received | Inbound text | Yes |
On Ready, some failures are resent for you and can still end delivered under the same id; see Failures Ready resends for you.
Two rules keep this table honest. First, only allow forward moves, so a late "sent" event can't overwrite "delivered". Second, store the provider's own message id in provider_id and make it unique, because that's how you match a webhook to a row. Deduplicate on it.
Keep refused separate from failed. A refusal means a pre-send gate (an opt-out, a quiet-hours block, a banned number) stopped the text before it left, and nothing was charged. A failure means it left and didn't arrive. Support teams ask about both, and they need different answers. For the error codes you'll map into these states, see SMS API error codes and troubleshooting.
Where does opt-out state belong?
On the contact, as a timestamp, and checked before every send. A boolean tells you the person opted out. A timestamp tells you when, and lets you handle a later START from the same number.
Don't treat your copy as the only source of truth. On Ready, an inbound STOP is honored automatically and that number can't be messaged again, whether or not your table knows yet. Your database should still mirror it so your UI can grey out the compose box. The pattern is covered in handling SMS opt-outs, and GET /v1/opt-outs/:phone lets you check one number before a send from another tool.
One caveat: GET /v1/opt-outs lists opted-out contacts on that account only. A STOP from a contact you deleted, or one held on another account in the same family, won't appear. So don't build a nightly sync that assumes the list is complete, and check the single number when it matters.
Consent is a different field. Ready doesn't store a per-contact consent record: the recipient's opt-in time, wording and page aren't kept for you. The sender keeps that, and your CRM is the natural place. Add a consent source and timestamp to the contact, or a separate consent table if one person can consent in several ways.
How do you handle segments and long messages?
Store the segment count on the message, because billing is per segment, not per text. A plain message holds 160 characters in one segment, and longer ones split into 153-character parts. One emoji drops the limit to 70 characters, or 67 per part, so a 140-character text with one emoji is 3 segments.
Put that on the row rather than recomputing it. It lets you show customers an accurate usage figure and reconcile against the bill, where each segment costs $0.0075 plus a $0.0045 carrier fee. Inbound replies are billed too, at the same rate with a $0.003 carrier fee in place of $0.0045, so count those too.
What about indexes, retention and tenancy?
Index for the three screens people actually open: the inbox, a thread and a contact. In practice that means (tenant_id, last_message_at DESC) on conversations and (conversation_id, created_at) on messages.
Put tenant_id in every query path. If a row can be reached by an id alone, a bug in one handler can show one customer another customer's thread. Row-level security or a repository layer that always injects the tenant is cheap insurance.
For retention, decide per tenant. Message bodies can contain personal data, and a deleted contact should take its thread with it. Don't put health details in texts at all, since Ready isn't HIPAA compliant and doesn't sign BAAs.
The practical takeaway
Keep identity in the phone number, status on the message, consent and opt-out on the contact, and tenancy on every row. That schema handles inbound replies from strangers, late webhooks and a customer with several numbers without special cases.
If you're deciding how to receive the events that feed these tables, read receiving SMS via API: inbound webhooks, replies and opt-outs. The API quickstart walks through a first send and webhook.