Supabase database functions and triggers let you inject imperative logic directly into your PostgreSQL database, making it a more active participant in your application’s data flow than a simple data store.
Let’s see what that actually looks like. Imagine we have a profiles table. When a new user signs up, we want to automatically create a corresponding profile for them. We’ll use a Supabase function for this.
-- First, create the function
CREATE OR REPLACE FUNCTION public.create_profile_for_new_user()
RETURNS TRIGGER AS $$
BEGIN
INSERT INTO public.profiles (user_id, username, created_at)
VALUES (NEW.id, NEW.raw_user_meta_data->>'full_name', NOW());
RETURN NEW;
END;
$$ LANGUAGE plpgsql;
-- Then, attach it to a trigger on the auth.users table
CREATE TRIGGER new_user_profile_trigger
AFTER INSERT ON auth.users
FOR EACH ROW
EXECUTE FUNCTION public.create_profile_for_new_user();
This CREATE OR REPLACE FUNCTION statement defines a new PostgreSQL function named create_profile_for_new_user. It’s designed to run after a new row is inserted into the auth.users table (which is where Supabase Auth stores user information). The RETURNS TRIGGER part tells PostgreSQL this function is intended to be used as a trigger. LANGUAGE plpgsql specifies the procedural language we’re using, which is PostgreSQL’s built-in procedural language.
Inside the BEGIN...END block is the core logic. NEW is a special variable in trigger functions that refers to the row being inserted or updated. Here, we’re INSERTing into our profiles table. We’re taking the id from the newly created user (NEW.id), a full name from the user’s metadata (NEW.raw_user_meta_data->>'full_name'), and the current timestamp (NOW()) to populate the user_id, username, and created_at columns of the new profile record. Finally, RETURN NEW is required for AFTER INSERT triggers on row-level to indicate that the operation should proceed.
The CREATE TRIGGER statement then hooks this function up. AFTER INSERT ON auth.users means this trigger will fire after any new row is successfully inserted into the auth.users table. FOR EACH ROW indicates that the trigger should execute once for every individual row affected by the INSERT statement. EXECUTE FUNCTION public.create_profile_for_new_user() is the command that actually runs our defined function.
This pattern is incredibly powerful. You can enforce data integrity, automate data transformations, or even trigger external events (though that’s more advanced and often involves pg_net or similar extensions). For instance, you could have a trigger that automatically updates a last_active_at timestamp on a users table every time a related record is modified, or a function that calculates a derived field before a record is saved.
The key mental model to build is that your database isn’t just a passive repository. With functions and triggers, it becomes an active agent that can react to data changes, enforce complex business rules, and maintain data consistency at the source. This can offload significant logic from your application server, simplifying your backend code and potentially improving performance by keeping operations close to the data.
Consider also that BEFORE triggers can actually modify the NEW or OLD row data before it’s written to disk. This is different from AFTER triggers, which only observe the changes. A BEFORE INSERT trigger, for example, could automatically set a default value for a column if it’s not provided, or sanitize input data before it’s stored.
The most surprising thing about Postgres functions and triggers is how much power you can wield with just SQL and PL/pgSQL, allowing for complex, stateful operations that feel almost like a mini-application running inside your database itself. You can even call other SQL functions, perform complex queries, and manage transactions within a single trigger.
The next logical step is exploring how to manage these functions and triggers using Supabase’s tooling, especially for version control and deployment.