Chrysolites Tech Academy

Photo Social App with FlutterFlow and Supabase · Course book

Chapter 19: Triggers and Like Counts

Pairs with Lesson 19: Like Counts with Supabase Triggers

Every post shows how many likes it has, and that number must always be right. One way to get it is to count the rows in the likes table each time a post is displayed; another is to store the count on the post and keep it up to date. This chapter takes the second approach using a database trigger, a small function the database runs automatically whenever rows are inserted or deleted. It explains what triggers are, how to write one in Supabase that adds or subtracts one from a post's like count, how to debug triggers when they misbehave, how the count is reflected in the FlutterFlow post card, and why a stored count is so much cheaper than counting, worked out by hand with a graph.

What triggers are

A trigger attaches a function to a table and an event. When the event happens, an insert, update or delete, the database calls the function, either before the change, so it can modify or reject the new row, or after it, so it can react to it. Because triggers run inside the database, they fire for every change, whatever made it: the app, the Supabase dashboard, a script or another trigger. That makes them ideal for keeping related data consistent, such as counts, timestamps or audit records.

In PostgreSQL, a trigger has two parts. The trigger function, written in the PL/pgSQL language, contains the logic; inside it, the special variables NEW and OLD hold the row being inserted and the row being deleted. The trigger itself binds the function to a table and event, and says whether it runs once for each row changed. Supabase lets you create both in the SQL editor, and its dashboard lists functions and triggers so you can see what is in place.

Triggers are powerful, and that calls for restraint. Because they run invisibly, a project with many triggers can become hard to understand: a single insert might set off a chain of updates that nobody remembers adding. Good practice is to keep each trigger small and focused on one job, name it after what it does, such as on_like_change, and record in the project notes which tables have triggers and why. Triggers also run inside the same transaction as the change that fired them, so a slow trigger slows every insert, and a failing trigger makes the insert itself fail. That is a feature for consistency, since a like and its count change together or not at all, but it means trigger code must be simple and well tested.

Concurrency is handled for you. If two people like the same post at the same instant, both triggers run like_count = like_count + 1, and PostgreSQL applies the two updates one after the other, so the count rises by exactly two. Reading the count in the app, adding one and writing it back, by contrast, could lose one of the likes if both phones read the old value at the same moment. Doing arithmetic inside a single database update, as the trigger does, avoids that race entirely.

ApproachHow the feed gets a countCost per feed loadRisk
Count on every readCount likes rows for each post shownGrows with the number of likesSlow pages as the app grows
Stored count kept by a triggerRead the post's like_count columnConstant: one number per postCount drifts if the trigger is wrong or missing

Writing the like count trigger

The posts table already has a like_count column with a default of zero. The trigger function adds one to the matching post when a like is inserted and subtracts one when a like is deleted.

create or replace function public.update_like_count()
returns trigger
language plpgsql
security definer
set search_path = public
as $$
begin
  if (tg_op = 'INSERT') then
    update posts set like_count = like_count + 1 where id = new.post_id;
    return new;
  elsif (tg_op = 'DELETE') then
    update posts set like_count = greatest(like_count - 1, 0) where id = old.post_id;
    return old;
  end if;
  return null;
end;
$$;

create trigger on_like_change
after insert or delete on public.likes
for each row execute function public.update_like_count();

The function checks tg_op, which names the operation that fired it, and uses NEW for inserts and OLD for deletes, since a deleted row has no new version. Using greatest(…, 0) stops the count ever going below zero if something unexpected happens. The trigger runs after each row is inserted or deleted, so it only adjusts the count for likes that were actually stored; an insert rejected by the unique constraint never reaches the trigger, so double taps cannot inflate the number.

The security definer setting makes the function run with the permissions of its owner, which is needed because an ordinary user is not allowed to update other people's posts directly, yet liking someone's post must change that post's count. Combined with a fixed search_path, this is the standard safe pattern: users can change the count only indirectly, through the likes table and its policies, and only by exactly one per like.

Why a stored count is cheaper

Counting likes on every read means asking the database to count rows in the likes table for each post on screen. Even with an index on post_id, counting must visit an entry for every like, so the work grows with how popular the posts are.

Worked example. A feed page shows 20 posts. If each has 50 likes, counting reads 20 × 50 = 1,000 index entries per page load. If the posts are popular, with 2,000 likes each, it reads 40,000. With a stored count, the page reads 20 numbers, one per post, however many likes there are. The trigger moves the work to the moment of liking: each like or unlike updates one number, a tiny cost paid once, instead of a counting cost paid by every viewer every time. If 1,000 people view a page of 20 posts with 2,000 likes each, counting would read 40 million entries in total; the stored counts are read 20,000 times, and only as fast as the page is viewed.
20,00040,000 count likes on every read stored count: 20 reads 020k40k 05001,0001,5002,000 average likes per postentries read per page of 20 posts
Figure 19.1 — Work per feed page. Counting grows with popularity; a stored count kept by a trigger costs the same however popular posts become.

The trade-off is that the stored count is a copy of information that also exists in the likes table, so it can drift if the trigger was added after some likes already existed, or was briefly disabled. A one-off query that sets each post's like_count to the real number of its likes fixes any drift, and running it once after creating the trigger brings existing posts into line.

Debugging triggers

Triggers run silently, so problems show up only as wrong numbers or failing actions. A few checks find most issues. If liking now fails entirely, the trigger function probably has an error, such as a misspelt column, and the database rejects the whole insert; the error message returned to the app, or shown when inserting a like manually in the SQL editor, names the problem. If likes are saved but the count does not change, check that the trigger exists on the likes table, that it fires on both insert and delete, and that it updates the right column. If the count changes for the wrong post, the function is probably using the like's own id instead of its post_id.

Testing a trigger directly in the SQL editor is the fastest approach: insert a like row by hand, select the post to see its count rise, delete the row, and see it fall. Only once that works is it worth testing through the app, where caching and policies add other possible causes.

Reflecting the count in the app

Because the count is now a column on the post, the posts-with-authors view can include it, and the post card can show it in the caption area: "1,204 likes", formatted compactly as "1.2K" for large numbers and in the singular for exactly one like. No extra query is needed.

The displayed count must also respond instantly when the viewer taps the heart, before the next refresh. The card can show the stored count plus one if the viewer has just liked the post in this session, or minus one if they have just unliked it, using the same liked list in app state that fills the heart. When the feed next refreshes, the stored count, now updated by the trigger, takes over. Testing with two accounts confirms the real behaviour: a like from one account raises the number the other account sees after refreshing.

Practice tasks

  1. A page shows 15 posts averaging 800 likes each. How many index entries does counting read, and how many values does a stored count read?
  2. Explain the roles of NEW, OLD and tg_op in the trigger function.
  3. Why is security definer needed, and what limits keep it safe here?
  4. Write, in words, the one-off query that corrects every post's like count.
  5. Likes are saved but counts never change. List three things to check.
  6. How should the card show the count immediately after the viewer taps the heart?

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.