Discuss your project
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

  1. Automating with Functions and Triggers
    • Generating URL-Friendly Slugs
    • Creating Unique Post IDs
    • Auto-Assigning Authors
    • Managing Timestamps and Soft Deletes
  2. 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
  3. Preventing Updates to Unique IDs
    • Preventing Unique ID Updates
    • Applying the Unique ID Check
  4. Ensuring Email Consistency in User Profiles
    • Copying the User’s Email
    • Applying the Email Copy Function
  5. 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 Astro
Now 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.