Photo Social App with FlutterFlow and Supabase · Course book
Chapter 27: Chats and Members
The chat pages are designed; now they need data. A conversation is more than a list of messages: it is a group of people, even if the group has only two members, and the app must know which conversations each user belongs to. This chapter creates the chat tables in Supabase, a chats table and a chat members intermediate table, writes the action that creates a conversation or reuses an existing one, shows a user's conversations on the chat groups page, and secures it all so that people only ever see chats they belong to. It also works out by hand how many conversations and member rows an app can accumulate.
Modelling conversations
A conversation can be stored in several ways, and the choice shapes everything that follows. A tempting shortcut gives each conversation two columns, user one and user two, but that cannot represent group chats and makes "all chats for this user" awkward, since the user might be in either column. A more flexible design separates the conversation from its members.
| Table | Columns | Purpose |
|---|---|---|
| chats | id, created_at, name (optional, for groups), last_message_at | One row per conversation |
| chat_members | chat_id, user_id, joined_at, last_read_at; unique together | Who belongs to each conversation |
| messages (next part) | id, chat_id, sender_id, content, created_at | What was said, in which chat, by whom |
chat_members is another intermediate table, linking users and chats in a many-to-many relationship: a user can be in many chats, and a chat can have many users. A one-to-one conversation is simply a chat with two member rows; a group chat has more. Finding a user's conversations is one filter on chat_members by user_id, and finding a chat's members is one filter by chat_id. The unique constraint on the pair stops anyone being added to the same chat twice, and cascading deletes from chats remove members and messages together.
Two convenience columns earn their place. last_message_at on the chat, updated whenever a message is sent, lets the chat groups page sort conversations by recent activity without examining every message. And last_read_at on each member row records when that person last opened the chat, so the app can show a small unread marker when the chat's latest message is newer.
Storing last_message_at on the chat is a small, deliberate copy of information that also lives in the messages table, the newest message's time. Like the like and follow counts, it is best maintained by a trigger on messages, which updates the chat's value whenever a message is inserted, so the app never has to remember. Without it, sorting the chat list would require finding the newest message of every conversation each time the page opens, a query that grows slower as conversations accumulate.
Group chats raise a few more design questions, which are easiest to settle while the tables are new. Who may add or remove members? A role column on chat_members, such as "owner" or "member", can record who created the group and has the right to manage it. What happens when someone leaves? Deleting their member row removes their access to future messages, while earlier messages remain in the conversation for the others. And how large may a group be? A limit, enforced in the database function that adds members, keeps groups manageable and protects the app from misuse.
How many conversations?
Estimating growth helps confirm that the design scales. Among n users, the number of possible one-to-one conversations is the number of pairs, n × (n − 1) / 2, because a conversation between Maya and Alex is the same as one between Alex and Maya.
user_id and chat_id.Creating a chat, or reusing one
A conversation is started from another user's profile with a "Message" button, or from the new-chat button on the chat groups page. The action must not create a second conversation between the same two people; if one already exists, the app should simply open it. So creating a chat has two steps.
- Look for an existing chat that has exactly two members, the viewer and the other person. A small database function is the cleanest way: it finds a chat id that appears in
chat_membersfor both users and has no other members, and returns it, or nothing. - If none exists, create one: insert a row into
chats, take its new id, and insert two rows intochat_members, one for each person. Then navigate to the chat page with the chat's id as a parameter.
Wrapping both steps in a single database function, called from FlutterFlow, has a further benefit: the check and the creation happen together in one transaction, so two quick taps, or both people starting a chat at the same moment, cannot create duplicate conversations. The function returns the chat id either way, and the app navigates to it.
Group chats follow the same pattern with more members, chosen from a list of people, and an optional name stored on the chat. Since every conversation, one-to-one or group, uses the same tables, the chat page and the list need no special cases. Starting with one-to-one chats and adding groups later is entirely possible, because nothing in the tables has to change to support them; only the screens for choosing several people and naming the group are new.
Showing a user's conversations
The chat groups page needs, for each conversation the viewer belongs to, the other member's name and avatar, the latest message preview and its time. Gathering that from three tables in the app would take many requests, so a view does the joining: it starts from the viewer's chat_members rows, joins the chat, joins the other member's user row for the name and avatar, and adds the latest message, ordered by last_message_at descending. The page queries the view, filtered to the signed-in user, and generates one row component for each result.
Until messages exist, the preview is empty, so the row can show a friendly "Say hello" prompt instead of a blank line. The unread marker compares the chat's last_message_at with the viewer's last_read_at, and opening a chat updates last_read_at to now. Tapping a row navigates to the chat page with the chat id.
Passing the chat id, rather than the other person's details, keeps the chat page general: it can load the members and messages for any conversation, one-to-one or group, and it can be opened from a notification or a link later, since those can only carry a short id. For a quick first impression, the list can also pass the other member's name and avatar so the app bar appears instantly while the messages load. If a chat has been deleted, or the viewer has been removed from it, the chat page's query returns nothing because of the policies, and the page should say the conversation is no longer available rather than showing an empty screen.
Keeping chats private
Chats are the most private data in the app, so their policies deserve the most care. On chat_members, a user may read rows only for chats they belong to, which a policy expresses as "there is a member row for this chat with my user id". On chats, the same condition applies to reading, and messages, in the next part, will use it for both reading and sending. Inserting members directly from the app should be restricted, with membership created only through the database function, so nobody can add themselves to someone else's conversation.
Testing with three accounts proves the rules: A starts a chat with B; both see it in their lists; C sees nothing and cannot read its rows even by querying the table directly; A tapping "Message" on B's profile again opens the same chat rather than a new one. The next part adds messages, so the conversation can finally begin.
Practice tasks
- How many possible one-to-one conversations exist among 2,500 users?
- If 2,500 users each take part in 12 one-to-one chats, how many chats and member rows are there?
- Explain why storing chats with two user columns is less flexible than a members table.
- Describe the two steps of creating a chat, and why they belong in one database function.
- Write, in words, the policy that lets users read only the member rows of chats they belong to.
- How does the app decide whether to show an unread marker on a conversation?
This course book is available to read online only at tech-academy.chrysolites.org.