By this point we’ve decided two important things.
- First, we’re using Slack as our support interface.
- Second, we’re using Server-Sent Events instead of WebSockets because we’re adults capable of making responsible life choices.
Now we have a much less glamorous problem. Where do we actually put everything?
It turns out that the data model is the part of the system you’ll spend the least time showing off and the most time being thankful you got right. Nobody ever gathers around your desk to admire a beautifully normalized database. They will, however, gather around your desk when your support team discovers customer replies are randomly showing up in someone else’s conversation.
That tends to get everyone’s attention.
Start With Conversations, Not Messages
It’s tempting to begin by designing a messages table. After all, you’re building chat. Chat has messages. Seems obvious.
It’s also backwards. Messages don’t exist in isolation. They belong to conversations. A conversation has a beginning. It has participants. It has state. It has history. Messages are simply things that happened within that conversation.
Design the conversation first. The messages become obvious afterwards.
Every Conversation Needs an Identity
One of the first questions you’ll eventually ask yourself is surprisingly simple. “When another message arrives… how do I know where it belongs?”
The answer should never involve guessing. Every conversation gets its own durable identifier the moment it’s created.
Conversation
------------
conversation_id (UUID)
created_date
status
visitor_name
visitor_emailEvery message references that conversation.
Message
-------
message_id (UUID)
conversation_id
sender
message
created_dateNow everything has a home. You aren’t searching for “the latest conversation from Bob.” You’re asking for conversation 8F6A....
Computers like identifiers. Humans like names. Don’t confuse the two.
Why I Chose UUIDv7
This is usually where someone says: “UUIDs are terrible for indexes,” and for a long time, they were right. Random UUIDv4 values are effectively inserted at random locations throughout a B-tree. That means page splits, fragmented indexes, and databases quietly questioning your life choices.
UUIDv7 fixes most of that. Instead of being completely random, UUIDv7 begins with a timestamp. New identifiers naturally sort in roughly the order they’re created, while still remaining globally unique. That means inserts tend to happen near the end of the index instead of scattering across it like database confetti.
It’s one of those improvements that’s wonderfully boring. You still get globally unique identifiers. You still avoid exposing sequential IDs. You can generate IDs anywhere without coordinating with a database sequence. But now your indexes behave much more like they do with an auto-incrementing integer.
The best part is that this isn’t some experimental proposal anymore. UUIDv7 is supported, or rapidly becoming supported, across the database ecosystem. PostgreSQL 18 includes native UUIDv7 generation, SQL Server has added support, MySQL implementations are appearing, and libraries exist for just about every programming language you’d realistically use.
That means you’re no longer choosing between “good identifiers” and “good indexes.”
You can have both.
Will UUIDv7 outperform a perfectly sequential integer? No. Physics still exists. But for systems that benefit from globally unique identifiers, distributed ID generation, or externally visible IDs, the gap has become small enough that it’s rarely the deciding factor anymore.
Sometimes the right question isn’t “Are UUIDs slower?” It’s “Are they slow enough to matter?” For most business applications, the answer is no.
If you’ve mentally filed UUIDs under “slow and terrible,” it’s time to update that opinion. UUIDv7 is pretty badass.
Slack Doesn’t Care About Your IDs
Unfortunately, Slack has never heard of your conversation IDs. Slack has its own identifiers.
The first message you post creates a thread. Slack returns something that looks like this:
ts = 1751637712.948372That timestamp becomes the identity of the Slack thread. So now your conversation table grows slightly.
Conversation
------------
conversation_id
slack_channel
slack_thread_ts
statusThat’s the bridge. One identifier belongs to your application. The other belongs to Slack. Your application is responsible for remembering how they relate. Never try to recreate that relationship later. Store it immediately. Future You will thank you.
Messages Should Be Immutable
Treat messages like entries in an accounting ledger, not rows in a spreadsheet. One of the easiest traps to fall into is treating chat messages like documents. Don’t.
Once a message exists, leave it alone. If someone edits a message, that’s another event. If someone deletes a message, that’s another event. The original message should still exist.
Support conversations often become part of customer history. Sometimes they’re used to explain why a decision was made. Sometimes they’re used to answer awkward questions. Sometimes they’re used because someone confidently insists, “I never said that.”
Your database should be politely prepared to disagree while you grin like a maniac with proof.
The Browser Doesn’t Matter
This one took me longer than I’d like to admit. Browsers reconnect. Users refresh pages. Laptops die. People close tabs. Someone’s VPN decides to reconnect every twelve minutes because apparently chaos is a networking strategy.
None of those things should create a new conversation. The browser is temporary. The conversation isn’t. Your conversation model should survive browser refreshes without even noticing.
If reconnecting creates a brand new conversation, you’ve accidentally designed a browser session database instead of a messaging system.
Status Is More Valuable Than You Think
Conversations have a lifecycle.
- New.
- Active.
- Waiting.
- Closed.
- Archived.
Whatever states make sense for your application. The important thing is that the status belongs to the conversation, not the messages.
Individual messages don’t become “closed.” Conversations do.
This sounds obvious until you start writing queries. Trust me. It’s much nicer to write:
SELECT *
FROM conversation
WHERE status = 'active'than attempting to infer conversation state from the last seventeen messages. Databases are remarkably good at storing facts. They’re considerably worse at reading your mind.
Think Like an Auditor
One exercise I like to do is pretend an auditor walks into the room. They ask, “Show me everything that happened in this conversation.”
Can you do it? Can you show:
- Every customer message?
- Every support reply?
- Every timestamp?
- Who said what?
- Whether the message came from the browser or Slack?
- When the conversation was closed?
Without joining twelve unrelated tables? If not, your data model probably still needs work.
Don’t Store What You Can Rebuild
This sounds contradictory after everything I just said. It’s not. Store facts. Don’t store conclusions. For example:
Good:
- Conversation created
- Message received
- Slack thread ID
- Author
- Timestamp
Less good:
- Number of unread messages
- Current response time
- Conversation duration
- Average reply speed
Those can all be calculated. Persisting calculated values means they eventually become wrong. Usually on a Friday afternoon. Before a long weekend.
The Real Goal
The goal of the data model isn’t to make today’s feature easy. It’s to make next year’s feature possible.
- Want to add AI summarization? You’ve got every message.
- Want analytics? You’ve got timestamps.
- Want supervisor reporting? You’ve got ownership.
- Want full conversation export? You’ve already stored everything you need.
Good data models quietly unlock future features. Bad ones quietly prevent them. The difference usually isn’t obvious until six months later.
The Biggest Lesson
The biggest lesson from this project wasn’t anything specific to Slack. It was realizing that messaging systems are really event history systems. Every message is simply another event in the life of a conversation. Once I started thinking that way, the schema practically designed itself.
Almost.
There was still plenty of Red Bull involved.
Next Time
Before we write a single line of ColdFusion, we need to prepare the Slack side of the integration. We’ll create the Slack application, configure a bot, set up permissions, understand threads, and collect everything our application will need before making its first API call.
Because spending fifteen minutes getting the plumbing right beats spending three hours wondering why channel_not_found is ruining your afternoon.