Chrysolites Tech Academy

Photo Social App with FlutterFlow and Supabase · Course book

Chapter 28: Messages

Pairs with Lesson 28: Sending and Showing Messages

Conversations exist; now people need to talk in them. This chapter adds messages to the photo app: a messages table linked to chats and senders, a send action that stores each message and keeps the chat's latest activity up to date, a chat page that displays messages in order with the right bubbles, and the debugging and testing that a two-person feature always needs. It also estimates by hand how fast a messages table grows, with a graph, because chat produces far more rows than any other feature in the app.

The messages table

Each message belongs to one chat and has one sender. The table therefore holds two foreign keys, the text and a time.

ColumnTypeRulePurpose
idint8Primary keyIdentifies the message; also orders messages sent in the same instant
chat_idmatches chats.idForeign key; required; cascade on deleteThe conversation
sender_iduuidForeign key to users; requiredWho wrote it
contenttextRequired; between 1 and 2,000 charactersThe message
created_attimestamptzDefault now()Ordering and time stamps

An index on (chat_id, created_at) is the single most important performance decision for chat: every chat page asks for the newest messages of one conversation, and that index answers the question directly however large the table grows. Cascading deletes from chats mean a deleted conversation takes its messages with it.

Policies are strict, because messages are private. A user may read a message only if they are a member of its chat, a condition checked against chat_members. A user may insert a message only if they are a member of that chat and the sender_id is their own id, so nobody can post into a conversation they do not belong to, or pretend to be someone else. Deleting can be limited to the sender's own messages, if the app offers deletion at all.

These policies refer to another table, chat_members, to decide whether a row may be read, which is a common and powerful pattern. The condition reads, in words, "there exists a member row for this message's chat whose user is the person asking". Because policies run on every query, the membership lookup should be fast, which the index on chat_members provides. For more complex rules, Supabase recommends wrapping the lookup in a small function marked as secure, which keeps the policy readable and avoids repeating the same subquery in several policies on several tables.

Messages can carry more than text. A photo message would add an optional column for an image address, with the file uploaded to a private bucket whose policies also check chat membership, so that only the members can view it. Reactions, replies to a particular message and "seen" markers are other common extensions; each fits as an extra column or a small linked table, without changing the core design built here.

How fast messages accumulate

Chat generates rows quickly, because people send many short messages. Estimating the growth shows why the index and paging matter.

Worked example. Suppose 1,000 active users each send 30 messages a day. That is 1,000 × 30 = 30,000 messages a day, and over a year 30,000 × 365 = 10,950,000 messages, almost 11 million rows. A row holds an 8-byte id, an 8-byte chat id, a 16-byte sender id, an 8-byte time and an average of perhaps 80 characters of text, plus about 24 bytes of overhead, roughly 150 bytes. A year of messages therefore occupies about 10,950,000 × 150 = 1.6 billion bytes, about 1.6 GB, before indexes. Yet a chat page that shows the newest 30 messages of one conversation reads only those 30 rows through the index, whether the table holds a thousand rows or eleven million.
0.41 GB0.82 GB1.23 GB1.64 GB 00.821.64 036912 monthsmessages table size (GB, before indexes)
Figure 28.1 — A year of chat for 1,000 users sending 30 messages a day. The table grows steadily; the index keeps each chat page's query small.

Sending a message

The send button on the chat page runs a short chain of actions. First, trim the text and stop if it is empty. Second, insert a row into messages with the chat id from the page parameter, the signed-in user's id as sender and the text. Third, on success, clear the input and refresh the message list so the new message appears at the bottom. On failure, keep the text and show a short message, such as "Message not sent. Tap to retry", so nothing is lost.

Sending also changes the conversation's place in the chat list, which is ordered by last_message_at. A trigger on messages updates the chat's last_message_at to the new message's time after every insert, so the conversation moves to the top of both members' lists automatically, without the app making a second request. Because the trigger runs inside the same transaction, the message and the timestamp always change together.

Disabling the send button while the insert runs prevents double sends from an impatient double tap. For a snappier feel, the message can be shown immediately in a lighter "sending" style and confirmed when the insert succeeds, the same optimistic approach used for likes, with a retry option if it fails.

Messages that fail to send deserve special care, because losing someone's words is far worse in chat than elsewhere. A failed message can stay in the conversation, marked with a small warning icon, so the user can tap it to retry or delete it. Keeping such messages in page state until they succeed means a brief loss of signal, common on phones, never silently drops what the user typed.

Displaying messages

The chat page's message list queries the messages for its chat, newest first, limited to a page of perhaps 30, and the list is displayed reversed so that the newest message sits just above the input bar. Scrolling up loads older pages. Each message is drawn with the bubble component designed earlier, whose "sent by me" parameter is true when the message's sender_id equals the signed-in user's id: true gives a right-aligned bubble in the accent colour, false a left-aligned grey one.

Time stamps follow the grouping rule from the design: a centred time appears only when more than five minutes separate a message from the one before it. That comparison needs each message's neighbour, which a small custom function can perform, or which a view can provide by adding the previous message's time to each row. In a group chat, the sender's name and avatar appear above the first of their consecutive messages, using the sender id to look them up through a view that joins messages with users.

Opening the chat page should also mark the conversation as read, by updating the viewer's last_read_at in chat_members to the current time, which clears the unread marker on the chat list.

Conversations with long histories should never load everything at once. The first page of 30 newest messages is enough to fill the screen and lets the chat open instantly; when the user scrolls to the top of what has loaded, the next 30 older messages are fetched using the oldest loaded message's time as the starting point, so pages never overlap or skip messages even while new ones arrive. A small spinner at the top while older messages load tells the user more is coming, and when the very first message of the conversation is reached, a quiet note such as "Start of your conversation with Maya" marks the beginning.

Debugging and testing

Chat bugs show up most clearly with two people. If a message sends but never appears, check that the list's query filters by the page's chat id and that the list refreshes after sending. If the other person never sees it, check the read policy and that both are genuinely members of the chat. If every bubble appears on the same side, the "sent by me" comparison is probably comparing the wrong ids. If inserts fail with a permission error, the insert policy's membership check, or the sender id, is the likely cause. Watching the messages table in the Supabase editor while sending shows at once whether rows are being written.

A thorough test uses two accounts, ideally on two devices: send from each side, check alignment and order, check the chat list's preview and ordering on both sides, check unread markers, send a long message and one with emoji, and try sending with the network off. One limitation will be obvious: the other person only sees a new message after refreshing. The next part completes the chat feature, and a later extra part uses Supabase Realtime so messages arrive the instant they are sent.

Practice tasks

  1. Estimate a year of messages for 5,000 users sending 20 messages a day, at 150 bytes per row.
  2. Why is an index on chat id and creation time the most important index for chat?
  3. Write, in words, the insert policy for the messages table.
  4. Explain what the trigger on messages does and why it belongs in the database.
  5. All bubbles appear on the left. What is the likely cause?
  6. Describe how opening a chat clears its unread marker.

Install Chrysolites Tech Academy

Android / Chrome / Edge: open the browser menu and choose Install app or Add to Home screen.

iPhone / iPad (Safari): tap the Share button, then Add to Home Screen.