Supabase — Database Trigger Checklist

Run this checklist every time you create a database trigger or trigger function.

1. Decision — Should This Be a Trigger?

USE A TRIGGER WHEN:
[ ] An action must always happen after a database event — no exceptions
[ ] The action is too low-level to put in application code (audit logging, timestamps)
[ ] The action must apply even if data is modified by a migration script or admin

DO NOT USE A TRIGGER WHEN:
[ ] The action involves external services (email, webhooks) — use an Edge Function
[ ] The logic is complex business logic — put it in an RPC function instead
[ ] The trigger would create circular dependencies
[ ] The trigger would cause N+1 database calls

2. Trigger Function

3. Trigger Definition

— BEFORE: to validate or modify the row before it is written

— AFTER: to react to a change (create related records, audit log)

(avoids trigger firing on every unrelated update)

4. Common Trigger Patterns

Automatic Timestamps

CREATE OR REPLACE FUNCTION trigger_fn_set_timestamps()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF TG_OP = 'INSERT' THEN
    NEW.created_at := now();
  END IF;
  NEW.updated_at := now();
  RETURN NEW;
END;
$$;

CREATE TRIGGER set_timestamps
BEFORE INSERT OR UPDATE ON projects
FOR EACH ROW EXECUTE FUNCTION trigger_fn_set_timestamps();

Audit Log

CREATE OR REPLACE FUNCTION trigger_fn_audit_log()
RETURNS TRIGGER
LANGUAGE plpgsql
SECURITY DEFINER  -- needs to write audit_logs even if RLS blocks the user
AS $$
BEGIN
  INSERT INTO audit_logs (action, table_name, record_id, old_data, new_data, actor_id)
  VALUES (
    TG_OP,
    TG_TABLE_NAME,
    COALESCE(NEW.id, OLD.id),
    CASE WHEN TG_OP = 'DELETE' THEN row_to_json(OLD) ELSE NULL END,
    CASE WHEN TG_OP != 'DELETE' THEN row_to_json(NEW) ELSE NULL END,
    auth.uid()
  );
  RETURN NEW;
END;
$$;

5. Performance Rules

6. Testing

7. Documentation

Done When