Chrysolites Tech Academy

Photo Social App with FlutterFlow and Supabase · Course book

Chapter 27: Chats and Members

Pairs with Lesson 27: Creating Chats in Supabase

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.

TableColumnsPurpose
chatsid, created_at, name (optional, for groups), last_message_atOne row per conversation
chat_memberschat_id, user_id, joined_at, last_read_at; unique togetherWho belongs to each conversation
messages (next part)id, chat_id, sender_id, content, created_atWhat 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.

Worked example. With 1,000 users there are 1,000 × 999 / 2 = 499,500 possible pairs; with 100,000 users, almost 5 billion. In practice each person chats with a handful of others: if each user takes part in 20 one-to-one conversations, there are 1,000 × 20 / 2 = 10,000 chats for 1,000 users, since each chat involves two people, and 20,000 member rows, two per chat. For 100,000 users that becomes 1 million chats and 2 million member rows. At around 64 bytes per member row, 2 million rows occupy about 128 MB, comfortably handled with indexes on user_id and chat_id.
499,5005 billion 10,0001 million possible pairs actual chats (20 per user) 1041061081010 1,00010,000100,000 users (log scale)conversations (log scale)
Figure 27.1 — Possible conversations grow with the square of the users; real ones grow in proportion to users, so only a tiny share of pairs ever chat.

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.

  1. 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_members for both users and has no other members, and returns it, or nothing.
  2. If none exists, create one: insert a row into chats, take its new id, and insert two rows into chat_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

  1. How many possible one-to-one conversations exist among 2,500 users?
  2. If 2,500 users each take part in 12 one-to-one chats, how many chats and member rows are there?
  3. Explain why storing chats with two user columns is less flexible than a members table.
  4. Describe the two steps of creating a chat, and why they belong in one database function.
  5. Write, in words, the policy that lets users read only the member rows of chats they belong to.
  6. How does the app decide whether to show an unread marker on a conversation?

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.