Last Week

Data layer for the app is refactored. Additionally, I added 1 Supabase Edge Function, 12 Supabase Postgres Functions, and 11 Supabase Postgres Triggers.

What does it mean in English?

Imagine you have a digital filing cabinet (that’s your database) where you store all kinds of important information for your business or project. Now, Supabase is like a friendly assistant that helps you manage this filing cabinet without needing to learn all the technical details.

What are Postgres Functions? Think of Postgres functions as little helpers that live inside your filing cabinet. These helpers can:

  • Do repetitive tasks automatically: Instead of manually calculating totals or updating multiple records every time something changes, you can create a function to do it for you.
  • Apply consistent rules: For example, if you always need to format phone numbers in a certain way, a function can ensure this happens correctly every time.
  • Bundle complex operations: Some tasks might require several steps. A function lets you package these steps together so you just need to call one thing instead of remembering all the individual parts.

What are Postgres Triggers? Triggers are like automatic switches that activate your functions when certain events happen. For example:

  • When new information is added: A trigger could automatically send a welcome email when a new customer signs up.
  • When information is changed: If someone updates their address, a trigger could automatically update related shipping information.
  • When information is deleted: A trigger might create a backup copy before permanently removing something.

Nerdy Details

I’ll provide some practical code examples showing how Postgres functions and triggers work together in Supabase. Let me walk through a realistic business scenario with code samples.

Example: Customer Loyalty Points System

Let’s imagine you’re running an e-commerce store and want to automatically award loyalty points when customers make purchases.

Step 1: Create a Function to Calculate Loyalty Points

First, we’ll create a function that calculates loyalty points based on order amount:

-- Function to calculate loyalty points based on order amount
CREATE OR REPLACE FUNCTION calculate_loyalty_points(order_amount DECIMAL)
RETURNS INTEGER AS
$$
DECLARE
  points INTEGER;
BEGIN
  -- Basic rule: $1 = 1 point, with bonus points for larger orders
  points := FLOOR(order_amount);  -- Base points
  
  -- Bonus points for larger orders
  IF order_amount >= 100 THEN
    points := points + 25;  -- $25 bonus for orders over $100
  ELSIF order_amount >= 50 THEN
    points := points + 10;  -- $10 bonus for orders over $50
  END IF;
  
  RETURN points;
END;
$$ LANGUAGE plpgsql;

Step 2: Create a Function to Update Customer Points

Now we’ll create a function that updates the customer’s loyalty points:

-- Function to update customer points when an order is placed
CREATE OR REPLACE FUNCTION update_customer_loyalty_points()
RETURNS TRIGGER AS
$$
DECLARE
  earned_points INTEGER;
BEGIN
  -- Calculate points for this order
  earned_points := calculate_loyalty_points(NEW.total_amount);
  
  -- Update the customer's total points
  UPDATE customers
  SET 
    loyalty_points = loyalty_points + earned_points,
    updated_at = NOW()
  WHERE id = NEW.customer_id;
  
  -- Store the points earned for this specific order
  NEW.points_earned := earned_points;
  
  -- Log the points activity
  INSERT INTO points_history (customer_id, order_id, points_earned, created_at)
  VALUES (NEW.customer_id, NEW.id, earned_points, NOW());
  
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

Step 3: Create a Trigger to Automatically Run the Function

Finally, we’ll create a trigger that runs our function whenever a new order is added:

-- Create a trigger that fires when a new order is inserted
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
EXECUTE FUNCTION update_customer_loyalty_points();

How It All Works Together

  1. When a customer places an order, the order details are inserted into the orders table
  2. The after_order_insert trigger automatically fires
  3. This trigger calls the update_customer_loyalty_points() function
  4. Inside that function, it:
    • Calls the calculate_loyalty_points() function to determine points based on order amount
    • Updates the customer’s total points in the customers table
    • Records the activity in a points history table
    • Updates the order record with the points earned

Next Week

Prototype a simple web front end, preferably with JAMStack.