Chrysolites Tech Academy

Building Social Media Apps · Course book

Chapter 3: A Community App on Supabase

Pairs with Lesson 3: A Community App with FlutterFlow and Supabase

Not every app needs to be written line by line. Visual builders such as FlutterFlow let you design screens by dragging widgets, connect them to data and generate a real Flutter app for phones and the web, while a hosted back end such as Supabase provides a PostgreSQL database, authentication, storage and real-time updates. Together they can produce a production-ready app far faster than hand-coding, but only if the data is designed carefully. This chapter follows the build of a community discussion app, where people join communities, post, vote and comment, and explains the database schema, security policies, recommendation logic, voting rules, moderation, chat and notifications, with the key queries and calculations worked through by hand.

Designing the schema

The first step happens in Supabase, not in the app builder: designing the tables. Every later feature depends on getting them right. A community app needs to know who the users are, which communities exist, who belongs to which, what has been posted and what each person thinks of each post and comment.

TableKey columnsNotes
profilesid, username, avatarid matches the authenticated user's id
communitiesid, name, description, created_byA topic people can join
community_memberscommunity_id, user_id, roleJoins users to communities; role can mark moderators
postsid, community_id, author_id, title, body, locked, created_atlocked stops new comments
commentsid, post_id, author_id, parent_id, bodyparent_id points to the comment being replied to
post_votes and comment_votesuser_id, post_id or comment_id, valuevalue is +1 or −1; one row per user per item

Two design patterns appear here. Join tables such as community_members represent many-to-many relationships: a user can join many communities and a community has many members. Self-references such as a comment's parent_id create threads: a top-level comment has no parent, and a reply points to the comment it answers, so a thread of any depth needs only one table. Adding sample data, a few users, communities, posts and votes, before building screens makes it possible to see and test each feature as soon as it is designed.

Security with row level security

In Supabase the app talks to the database directly from the user's device, so the database itself must decide who may read or change each row. PostgreSQL's row level security (RLS) does this with policies, rules attached to each table for each kind of operation. Once RLS is enabled on a table, nothing is allowed unless a policy permits it.

alter table post_votes enable row level security;

create policy "Anyone signed in can read votes"
  on post_votes for select
  to authenticated
  using (true);

create policy "Users manage only their own votes"
  on post_votes for insert
  to authenticated
  with check (auth.uid() = user_id);

The function auth.uid() returns the id of the signed-in user making the request, so the second policy lets people insert a vote only in their own name. Similar policies allow users to update or delete only their own posts, comments and votes, while moderators get extra rights in communities where their role says so. Policies are the reason a modified copy of the app on someone's phone still cannot change other people's data: the rules live on the server.

Authentication, recommendations and the home page

FlutterFlow connects to the Supabase project with its address and public key, and its built-in authentication actions create the sign-up, sign-in and password screens. After sign-up, a matching row in profiles stores the username and avatar.

The public key is safe to ship inside the app precisely because of row level security: it only identifies the project, and every request is still checked against the policies using the signed-in user's identity. The separate service key, which bypasses all policies, must never be placed in an app or a web page; it belongs only on trusted servers. In FlutterFlow, screens are then connected to data with backend queries, which load rows from a table or view when a page or widget appears, and with actions, which run when the user taps a button, for example inserting a row, navigating to another page or refreshing a query. Most features in this chapter are a combination of a query that shows the current state and an action that changes it.

The home page shows recommended communities. A simple recommendation engine, written as a database function or view and called from the app, can suggest communities the user has not joined, ranked by how active or popular they are. Doing this in the database rather than in the app keeps the logic in one place and avoids downloading every community to the phone just to filter them.

Feeds commonly rank posts by combining their score with their age, so that new posts get a chance to rise and old ones fade. One widely used style of formula divides the score by a power of the age; the example below uses score / (hours + 2)1.5.

35.412.54.41.90.75 02035 06121824 age of post (hours)ranking value, score 100
Figure 3.1 — An example time-decay ranking. A post's ranking value halves within its first hour or so, so fresh posts can outrank older ones with higher scores.

Under this formula, a 2-hour-old post scoring 40 has a ranking value of 40 / 41.5 = 40 / 8 = 5.0, beating a 10-hour-old post scoring 120, which has 120 / 121.5 = 2.89, and a 30-hour-old post scoring 300, at 1.66. The exponent controls how quickly posts fade and is a design choice, tuned to how active a community is.

Votes, comments and threads

Voting looks simple but has several cases. Each user may hold at most one vote per post, enforced with a unique constraint on (user_id, post_id). Clicking a button then means one of four things.

Worked example. A post has three votes: +1, +1 and −1, so its score is 1. A user with no vote clicks upvote: insert +1, score becomes 2. Clicking upvote again removes the vote: delete the row, score back to 1. If instead the user holding the −1 clicks upvote, the vote changes from −1 to +1: update the row, and the score jumps by 2, from 1 to 3. Clicking downvote while holding a downvote removes it, raising the score by 1. In short: no vote means insert, the same vote means delete, and the opposite vote means update.

The app shows the user's own vote by highlighting the arrow, which means each post needs to know both its total score and the current user's vote. Computing these in a database view, rather than counting rows in the app, keeps the screens fast and the logic consistent. The same rules apply to comment votes, and bugs in these cases, such as a score that changes by 1 instead of 2 when a vote flips, are exactly the kind of issue to test carefully with the sample data.

The post view shows the post and its comment thread. Top-level comments are those with no parent; each reply is shown beneath the comment whose id matches its parent_id, usually indented, with deeper replies indented further so the conversation's structure is visible at a glance. A reply button on each comment opens a box that creates a new comment with parent_id set to that comment.

Communities, moderation, chat and notifications

A community view lists a community's posts and shows a join button. The button must reflect the current state, showing Join or Leave depending on whether a community_members row exists for this user, and toggle it when pressed. A create post screen lets members post into a community they belong to, with policies ensuring non-members cannot.

Moderation gives users marked as moderators in community_members extra powers within that community, such as removing posts or comments and locking a post, which sets its locked column to true so that the policy for inserting comments rejects new ones. The interface should also hide the comment box on locked posts, but the policy is what actually enforces the rule.

Chat stores messages in a table and uses Supabase's real-time feature, which pushes inserted rows to subscribed apps instantly, so conversations update without refreshing. Notifications, for replies and other activity, are rows in a notifications table, often created automatically by a database trigger when a reply is inserted, and shown in a list with unread ones highlighted. Wrapping up, a production-ready app also needs testing on real devices, sensible empty states such as "No posts yet", and clear error messages when the network fails.

Practice tasks

  1. A post's votes are +1, +1, +1, −1 and −1. Give the score after each of these in turn: the first downvoter switches to upvote; a new user downvotes; the first upvoter removes their vote.
  2. Using the example ranking formula, compare a 1-hour-old post scoring 20 with a 6-hour-old post scoring 70.
  3. Write an RLS policy that lets users delete only their own comments.
  4. Explain how a single comments table with a parent_id column can store threads of any depth.
  5. Why should the locked-post rule be enforced by a database policy rather than only by hiding the comment box?
  6. Describe a database trigger that creates a notification when someone replies to a comment.

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.