Go back Building a Slick Blog with Supabase, React, Astro, and Cloudflare – Part 2: Fine-Tuning Your Database with Policies, Functions, and Triggers /* by Tirth Bodawala - August 17, 2024 */ Tech Update Welcome back! Now that we’ve got our UserProfile and Posts tables set up, it’s time to add some serious firepower to our backend. In this part, we’re going to explore how to use functions, triggers, and Row-Level Security (RLS) policies in Supabase to automate tasks, secure your data, and ensure your blog runs like a well-oiled machine. Let’s jump in and make your blog backend as smart and secure as possible! Table of Contents Automating with Functions and Triggers Generating URL-Friendly Slugs Creating Unique Post IDs Auto-Assigning Authors Managing Timestamps and Soft Deletes Securing Your Data with Row-Level Security (RLS) Policies Enabling RLS Creating Policies Allowing Any Authenticated User to Insert Posts Updating and Deleting Posts—Only by the Author Controlling Access to Published Content Preventing Updates to Unique IDs Preventing Unique ID Updates Applying the Unique ID Check Ensuring Email Consistency in User Profiles Copying the User’s Email Applying the Email Copy Function Optimizing Your Database with Indexes 1. Automating with Functions and Triggers Why do things manually when you can automate? In this section, we’ll create SQL functions and triggers to handle repetitive tasks like generating slugs, creating unique IDs, and managing timestamps. Generating URL-Friendly Slugs Every post needs a clean, SEO-friendly URL. Instead of generating slugs manually, let’s create a function that does it for us: -- Enable the unaccent extension (run this once in your database) CREATE EXTENSION IF NOT EXISTS unaccent; -- Function to generate a URL-friendly slug from a title CREATE OR REPLACE FUNCTION generate_slug(title text) RETURNS text AS $$ DECLARE slug text; BEGIN -- Convert title to lower case, remove accents, special characters, and replace spaces with hyphens slug := regexp_replace(lower(unaccent(title)), '[^a-z0-9]+', '-', 'g'); -- Remove leading and trailing hyphens slug := trim('-' FROM slug); RETURN slug; END; $$ LANGUAGE plpgsql; This function turns any title into a slug that’s perfect for URLs. Automatically Setting the Slug with a Trigger We don’t want to forget to generate a slug when inserting or updating a post, so let’s automate it with a trigger: CREATE OR REPLACE FUNCTION set_slug() RETURNS TRIGGER AS $$ BEGIN -- Generate the slug only if it hasn't been provided or if the title changes IF NEW.slug IS NULL OR NEW.title IS DISTINCT FROM OLD.title THEN NEW.slug := generate_slug(NEW.title); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER before_insert_update_set_slug BEFORE INSERT OR UPDATE ON Posts FOR EACH ROW EXECUTE FUNCTION set_slug(); Now, every time you insert or update a post, the slug is generated automatically. Creating Unique Post IDs Let’s make sure every post has a unique identifier. This function generates a 10-character random string for the unique_id field: CREATE OR REPLACE FUNCTION generate_unique_id() RETURNS text AS $$ DECLARE new_unique_id text; exists boolean; BEGIN LOOP -- Generate a random string of 10 characters new_unique_id := substr(md5(random()::text), 1, 10); -- Check if this unique_id already exists in the Posts table SELECT EXISTS (SELECT 1 FROM Posts WHERE unique_id = new_unique_id) INTO exists; -- Exit the loop if the unique_id does not exist EXIT WHEN NOT exists; END LOOP; RETURN new_unique_id; END; $$ LANGUAGE plpgsql; Automatically Setting Unique IDs and Assigning Authors We’ll use another trigger to set the unique_id and assign the author based on the current authenticated user: CREATE OR REPLACE FUNCTION set_unique_id_on_insert() RETURNS TRIGGER AS $$ BEGIN -- Generate the unique_id only if it is NULL (on insert) IF NEW.unique_id IS NULL THEN NEW.unique_id := generate_unique_id(); END IF; -- Automatically assign the current user as the author IF NEW.author IS NULL THEN NEW.author := auth.uid(); END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER before_insert_set_unique_id BEFORE INSERT ON Posts FOR EACH ROW EXECUTE FUNCTION set_unique_id_on_insert(); Managing Timestamps and Soft Deletes Keep your timestamps accurate and handle soft deletes (where records are marked as deleted but not actually removed) with these functions and triggers: Automatically Update the updated_at Field: CREATE OR REPLACE FUNCTION update_updated_at_column() RETURNS TRIGGER AS $$ BEGIN NEW.updated_at = now(); -- Sets the updated_at field to the current timestamp RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER update_posts_updated_at BEFORE UPDATE ON Posts FOR EACH ROW EXECUTE FUNCTION update_updated_at_column(); Handle Soft Deletes by Setting the deleted_at Field: CREATE OR REPLACE FUNCTION soft_delete_post() RETURNS TRIGGER AS $$ BEGIN NEW.deleted_at = now(); -- Sets the deleted_at field to the current timestamp RETURN NEW; END; $$ LANGUAGE plpgsql; CREATE TRIGGER set_deleted_at BEFORE DELETE ON Posts FOR EACH ROW EXECUTE FUNCTION soft_delete_post(); 2. Securing Your Data with Row-Level Security (RLS) Policies Now, let’s make sure your data is secure with Row-Level Security (RLS). This is where we control who can insert, update, delete, or view posts. Enabling RLS First, we need to enable RLS on our tables: ALTER TABLE UserProfile ENABLE ROW LEVEL SECURITY; ALTER TABLE Posts ENABLE ROW LEVEL SECURITY; Creating Policies Let’s create a policy that ensures UserProfile data is only accessible when an email filter is applied: CREATE POLICY allow_selecting_user_profile ON UserProfile FOR SELECT USING ( true ); This policy allows users to query UserProfile records Allowing Any Authenticated User to Insert Posts CREATE POLICY insert_any_authenticated_user ON Posts FOR INSERT WITH CHECK (auth.uid() IS NOT NULL); This policy ensures that only authenticated users can create new posts. Updating and Deleting Posts—Only by the Author We’ll enforce that only the author of a post can update or delete it: CREATE POLICY update_own_posts ON Posts FOR UPDATE USING (auth.uid() = author); CREATE POLICY delete_own_posts ON Posts FOR DELETE USING (auth.uid() = author); Controlling Access to Published Content Finally, we want to ensure that only published posts are visible to the public, while authenticated users can see everything: CREATE POLICY select_published_posts ON Posts FOR SELECT USING ( auth.role() = 'authenticated' OR current_date >= publish_date ); This policy allows anyone to view posts that have been published, but only authenticated users can see drafts or scheduled posts. 3. Preventing Updates to Unique IDs Once a post is created, its unique_id should never change. To enforce this, we’ll create a function that raises an exception if someone tries to update the unique_id and a trigger to apply this function before any update operation. Function: Preventing Unique ID Updates Here’s the function that checks whether the unique_id is being altered: CREATE OR REPLACE FUNCTION prevent_unique_id_update() RETURNS TRIGGER AS $$ BEGIN -- Prevent the unique_id from being updated after creation IF NEW.unique_id IS DISTINCT FROM OLD.unique_id THEN RAISE EXCEPTION 'Unique ID cannot be updated'; END IF; RETURN NEW; END; $$ LANGUAGE plpgsql; Trigger: Applying the Unique ID Check Now, let’s attach this function to the Posts table using a trigger: CREATE TRIGGER before_update_prevent_unique_id_change BEFORE UPDATE ON Posts FOR EACH ROW EXECUTE FUNCTION prevent_unique_id_update(); With this in place, any attempt to modify the unique_id will be blocked, ensuring the integrity of your posts. 4. Ensuring Email Consistency in User Profiles When a new user profile is created, we want to automatically copy their email from the auth.users table to the UserProfile table. Let’s create a function and trigger to handle this. Function: Copying the User’s Email CREATE OR REPLACE FUNCTION set_user_email() RETURNS TRIGGER AS $$ BEGIN -- Set the email field based on the user_id from auth.users SELECT email INTO NEW.email FROM auth.users WHERE id = NEW.user_id; RETURN NEW; END; $$ LANGUAGE plpgsql; Trigger: Applying the Email Copy Function CREATE TRIGGER before_insert_set_email BEFORE INSERT ON UserProfile FOR EACH ROW EXECUTE FUNCTION set_user_email(); With this setup, every time a new UserProfile is created, the user’s email is automatically populated from the auth.users table, ensuring consistency across your data. 5. Optimizing Your Database with Indexes To keep everything running fast, let’s optimize the slug field with an index: CREATE INDEX idx_slug ON Posts(slug); This index speeds up queries that search for posts by their slug—perfect for those clean, SEO-friendly URLs. Wrapping Up Part 2 We’ve just covered a lot of ground! Your blog backend is now fortified with essential functions, triggers, and RLS policies. These enhancements will keep your data secure, consistent, and efficient. In Part 3, we’ll switch gears and focus on building out the frontend using React, Astro, and Tailwind CSS to bring all this backend goodness to life. Download the consolidated SQL File by clicking here Next Up: Part 3 – Building the Frontend with React and AstroNow that your backend is locked and loaded, it’s time to bring your blog to life with a beautiful and responsive frontend. Get ready to dive into Astro, React, and Tailwind CSS! Further Reading Want to dive deeper into Supabase’s capabilities? Check out the Supabase Documentation to explore more about Row-Level Security, triggers, and advanced SQL functions.